abdul

-- create tables
CREATE TABLE Hostel (hno integer,hname text, gen text);
CREATE TABLE Menu (hno integer, day text, breakfast text, lunch text , dinner text);
CREATE TABLE Warden (wname text, qual text, hno integer);
CREATE TABLE Student (sid integer, sname text, gender text, year integer, hno integer);

-- Hostel Details
INSERT INTO Hostel VALUES (001, 'Block A', 'Boys');
INSERT INTO Hostel VALUES (002, 'Block B', 'Boys');
INSERT INTO Hostel VALUES (003, 'Block C', 'Girls');

-- Warden Details
INSERT INTO Warden VALUES ('Ariharan', 'BE CSE',001);
INSERT INTO Warden VALUES ('Arun', 'BE ECE',001);
INSERT INTO Warden VALUES ('Deepan', 'BE CSE',001);
INSERT INTO Warden VALUES ('sundar', 'BE CSE',002);
INSERT INTO Warden VALUES ('Dinesh', 'BE CSE',002);
INSERT INTO Warden VALUES ('kural', 'BE CSE',003);

-- Menu Details
-- hostel no 001
INSERT INTO Menu VALUES (0001,'Monday', 'Idly','Sambar','Dosa');
INSERT INTO Menu VALUES (0001,'Tuesday', 'Dosa','Full Meals','Poratta');
INSERT INTO Menu VALUES (0001,'Wednesday', 'Poori','Chicken Rice','Chapathi');
INSERT INTO Menu VALUES (0001,'Thursday', 'Idly','Sambar','Dosa');
INSERT INTO Menu VALUES (0001,'Friday', 'Poori','Chicken Rice','Chapathi');
-- hostel no 002
INSERT INTO Menu VALUES (0002,'Monday', 'Idly','Sambar','Dosa');
INSERT INTO Menu VALUES (0002,'Tuesday', 'Poori','Chicken Rice','Chapathi');
INSERT INTO Menu VALUES (0002,'Wednesday', 'Poori','Sambar','Dosa');
INSERT INTO Menu VALUES (0002,'Thursday', 'Idly','Sambar','Dosa');
INSERT INTO Menu VALUES (0002,'Friday', 'Poori','Chicken Rice','Chapathi');
-- hostel no 003
INSERT INTO Menu VALUES (0003,'Monday', 'Idly','Sambar','Dosa');
INSERT INTO Menu VALUES (0003,'Tuesday', 'Idly','Sambar','Dosa');
INSERT INTO Menu VALUES (0003,'Wednesday', 'Poori','Chicken Rice','Chapathi');
INSERT INTO Menu VALUES (0003,'Thursday', 'Idly','Sambar','Dosa');
INSERT INTO Menu VALUES (0003,'Friday', 'Poori','Chicken Rice','Chapathi');

-- Student Details
INSERT INTO Student VALUES (7881,'Mano','Male',2,001);
INSERT INTO Student VALUES (7882,'Abdul rahaman','Male',3,001);
INSERT INTO Student VALUES (7883,'Syed','Male',2,002);
INSERT INTO Student VALUES (7884,'Arun','Male',3,002);
INSERT INTO Student VALUES (7885,'Maha','Female',2,003);
INSERT INTO Student VALUES (7886,'Reena','Female',2,003);

-- Display the total number of girls and boys hostel in the college
SELECT COUNT(*) FROM Student ; 

-- Display the menu in the hostel ‘x’ on Tuesday.
SELECT hno,breakfast,lunch,dinner FROM menu WHERE day='Tuesday';

-- Display the number of wardens for each hostel.
SELECT hno,COUNT(*) FROM Warden WHERE hno=001;
SELECT hno,COUNT(*) FROM Warden WHERE hno=002;
SELECT hno,COUNT(*) FROM Warden WHERE hno=003;