Description

Create a database in SQLite on managing the IIT Kanpur campus community. The members of the community include faculty members, staff, students (core members) as well as their family members.

The students stay in hostels (separate for boys and girls), while married students stay in special married hostels.

Design the different tables accordingly.

Specify the primary key and foreign key constraints while creating the tables.

2. (15 marks)

Create csv files with data. There should be at least 50 members of each type, at least 20 departments, and at least 10 hostels. Import data directly from these csv files.

3. (35 marks)

Using the above database, write SQL statements for the following queries:

(a) Find the number of students who are married.
(b) Find the number of students living in married hostels. Does it match with (a) above?
(c) Count the number of faculty members, staff, students and family members separately as well as the total.
(d) Count the average number of family members per faculty member.
(e) Find if any faculty member or staff is head of more than one departments.
(f) Find the percentage of UG students having an advisor.
(g) Find the number of faculty members advising students from other departments.