Chandigarh College of Engineering
and Technology
(Degree Wing)
Government Institute under Chandigarh (UT)
Administration, Affiliated to Panjab University, Chandigarh
Sector-26, Chandigarh, Pin code: 160019
Bachelor of Engineering
in
COMPUTER SCIENCE AND ENGINEERING
Database Systems (CS 352)
Hostel Database (Assignment-2)
Submitted By: Submitted To:
Mohd Tarique Dr. Dheerendra Singh
(CO21344) Professor, CSE
OBJECTIVE
Draw the E-R diagram for the database project assigned to you. Show all the relations with
their attribute values. After that write down 20 SQL queries using different concepts based
on database project assigned to you.
Introduction:
This Hostel Management System is developed in favor of the hostel management team
of “CCET BOYS HOSTEL (DEGREE WING)” which helps them to save the records
of the students about their rooms and other things.
It helps them from the manual work from which it is very difficult to find the record of
the students and the information about those ones who had left the hostel years before.
This solution is developed on the plight of the hostel management team, through this they
cannot require so efficient person to handle and manage the affairs of the students in the
hostel, all you need to do is to login as administrator and you can see the information of
all the students who have obtained and registered their hostel form, click verify to
ascertain their eligibility and allocate them to the available hostel.
Identification of the problems of the existing hostel management leads to the
development of computerized solution that will be compatible to the existing hostel
management with the solution which is more users friendly and more GUI oriented. We
can improve the efficiency of the hostel management, thus overcome the drawbacks of
the existing management.
Hostel_allotment:
SNo Roll No. Name Branch Phone No. Year Room No
1 CO21344 Mohd Tarique CSE 9568926309 3 32
2 CO21367 Varun Goyal CSE 7719559242 3 32
3 CO21563 Shivang Dwivedi ECE 8319096593 3 32
4 MCO21582 Shubham Ranjan ECE 8765677432 3 32
5 CO20322 Himanshu Saha CSE 7255093556 4 33
6 CO20367 Vishal Singh CSE 9478077269 4 33
7 CO20319 Gopa Jeshwanth CSE 7981872262 4 33
Krishna
8 CO20350 Shivam Kumar CSE 9199932637 4 33
9 CO20546 Parikshit ECE 7876272610 4 34
10 CO21210 Ashish Kumar CIVIL 8529717479 3 34
11 CO21102 Aditya Anand MECH 6205109094 3 34
12 CO21215 Gulshan Kumar CIVIL 7033682669 3 34
13 CO20119 Kuldeep Rathi MECH 9352634637 4 35
14 CO20515 Deepanshu ECE 6378735107 4 35
15 CO20236 Rahul Chatta CIVIL 9649854823 4 35
16 CO20567 Vishal Singh ECE 9334635338 4 35
17 CO20201 Abhishek Yadav CIVIL 9935328147 4 36
18 CO20535 Mithilesh Kumar ECE 8102824636 4 36
19 CO20102 Abhijeet Kumar MECH 9821154149 4 36
20 CO20231 Om Prakash CIVIL 9508537727 4 36
21 CO20534 Jatin ECE 8765678453 4 37
22 CO20505 Chetan ECE 9548446151 4 37
23 CO20334 Snehil Bhardwaj CSE 9876785465 4 37
24 CO22546 Tushar ECE 8799715053 4 37
25 MCO20164 Aditya Motla MECH 7668056829 4 38
26 LCO20185 Rishabh Sharma MECH 9817763968 4 38
27 LCO20377 Vyoam Yadav CSE 97604 04444 4 38
28 LCO20166 Anmol Sharma MECH 8725846263 4 38
29 CO22350 Prateek Batra CSE 9988770627 2 39
30 LCO21270 Khalid Farooq CIVIL 6005885778 3 39
31 CO22352 Raj Kanwar Singh CSE 8568879517 2 39
32 CO22113 Sourav Gotra Mech 9622910381 2 40
33 CO22377 Yuvraj Redhu CSE 9306911750 2 40
34 CO22112 Vijay Kumar Mech 8899693653 2 40
35 CO21359 Sovan Chakma CSE 9863976424 3 41
36 CO21503 Abhijeet Rana ECE 8877546871 3 41
37 CO21151 Sameer Gupta MECH 7852858932 3 41
38 CO21247 Vaibhav Salatri CIVIL 8905527404 3 41
39 CO21356 Shaurya Harsh CSE 6200580485 3 42
40 CO21512 Aman Kumar ECE 7634966583 3 42
41 CO21532 Gopesh Kishore ECE 9305772266 3 42
42 CO21237 Rishav Bharti CIVIL 9389570956 3 42
43 CO21361 Tarayan Aggarwal CSE 9877028817 3 43
44 CO21148 Robin Kumar MECH 7876280000 3 43
45 CO21165 Kamal Kishore MECH 9103256778 3 43
46 LCO21254 Akhil Civil 7807346294 3 43
47 CO22521 Harshit Pal ECE 8076540274 2 44
48 CO22108 Owais Qamer MECH 9096566897 2 44
49 CO22211 Ishant Chohan CIVIL 7814567961 2 44
50 CO22218 Ritik Maheswari CIVIL 9636674236 2 44
51 CO22378 Chandan Sharma CSE 7006535993 2 45
52 CO22201 Aawan Khan CIVIL 9858716666 2 45
53 CO22213 Kartik Bahri Civil 7814878973 2 45
54 CO22225 Sudhir Sharma Civil 6006527918 2 45
55 CO20238 Rajnish Bansal CIVIL 8360642600 4 46
56 CO20370 Vikrant Sharma CSE 7006435833 4 46
57 LCO20272 Pranav Kathuria CIVIL 9736518484 4 46
58 CO20247 Sunny Kumar CIVIL 8969868044 4 46
59 CO22532 Nikhil ECE 9350577043 2 47
60 CO22347 Nipun Singh CSE 6283882519 2 47
61 CO22327 Divyansh Manro CSE 8360799948 2 47
62 CO22304 Aditya Yadav CSE 8786345123 2 47
63 CO20141 Tejas Sunil Ghadage MECH 8529151506 4 48
64 CO20109 Atikant Rajpoot MECH 8795379518 4 48
65 CO20140 Syed Mohd Aashir MECH 9318296633 4 48
66 CO20121 MD. Danish Jamal MECH 9939268896 4 48
67 CO20124 Prince Kumar MECH 7970875920 4 49
68 CO20206 Ankit Kumar CIVIL 7061110572 4 49
69 CO20560 Shitij Bhat ECE 9650096763 4 49
70 CO22322 Deepak Kumar Jha CSE 9508881750 2 50
71 CO22547 Subrajeet Paul ECE 8822100971 2 50
72 CO22520 Harshdeep dey ECE 8597389168 2 50
Fresher_allotment:
Sr. No NAME BRANCH PHONE YEAR ROOM NO QUO
NUMBER TA
1 PIYUSH KHERA CIVIL 9915925475 1 51 AIQ
2 PRIYANSHU MECH 8960182517 1 51 AIQ
TIWARI
3 SPARSH BISHT ECE 7818913338 1 51 AIQ
4 RAVI KANT MECH 9462963088 1 51 AIQ
SHARMA
5 NAVJOT SINGH ECE 7009967925 1 52 CHQ
6 DHRUV JOSHI CSE 8607718158 1 52 CHQ
7 ANUJ KUMAR CIVIL 8949091244 1 52 CHQ
YADAV
8 NIL NIL NIL NIL 52 NIL
9 ARYAN DEV CIVIL 6230246332 1 53 AIQ
SINGH RANA
10 SHIVAM CHOUBEY ECE 8787097818 1 53 AIQ
11 RAHUL ECE 9368279293 1 53 AIQ
CHAUDHARY
12 NARESH KUMAR ECE 8003213530 1 53 AIQ
RAJORIYA
13 RANJEET KUMAR CSE 6203111124 1 54 AIQ
14 VISHAL CSE 7206717262 1 54 AIQ
GAHLAWAT
15 SACHIN VERMA CSE 9878117104 1 54 AIQ
16 MUKUND THAKUR CSE 9340449412 1 54 AIQ
17 ABHINAV MECH 7807728946 1 55 AIQ
SHARMA
18 ABHISHEK CIVIL 9991076478 1 55 AIQ
19 VANSH SHARMA CIVIL 7085143822 1 55 AIQ
20 ABHINAV MECH 7807728946 1 55 AIQ
SHARMA
21 Vanshit Kamboj MECH 6375695008 1 56 AIQ
22 KABEER NAPHRI MECH 9878976574 1 56 AIQ
23 LAKSH SEYADA CIVIL 9079990631 1 56 AIQ
24 AMAN SINHA ECE 6287145926 1 56 AIQ
25 AASHISH MECH 8708497527 1 57 AIQ
26 NARESH KUMAR ECE 8003213530 1 57 AIQ
RAJORIYA
27 HITESH MECH 9518659242 1 57 AIQ
28 Garv Bahal CSE 9251727777 1 57 AIQ
29 SUJAL SHARMA ECE 8219673759 1 58 AIQ
30 SHOBHIT MISHRA MECH 7591065751 1 58 AIQ
31 SHUBHAM KUMAR MECH 8340574577 1 58 AIQ
32 RAVI KANT MECH 9462963088 1 58 AIQ
SHARMA
33 MUKUND THAKUR CSE 9340449412 1 60 AIQ
34 SANSKAR CSE 9910071157 1 60 AIQ
SHARMA
35 RISHIT SHARMA CSE 9417480430 1 60 AIQ
Item_issued:
FLOOR ROOM BED TABLE CHAIR FAN BULBS TUBE OTHE
NO. NO LIGHT R
ITEM
G.F 32 4 4 4 3 0 2 NULL
G.F 33 4 4 4 2 0 2 NULL
G.F 34 4 4 4 2 0 4 NULL
G.F 35 4 4 4 2 0 2 NULL
G.F 36 4 4 4 2 0 4 NULL
G.F 37 4 4 4 2 0 4 NULL
G.F 38 4 4 4 2 0 3 NULL
G.F 39 3 3 3 1 0 2 NULL
G.F 40 3 3 2 1 0 1 NULL
F.F. 41 4 4 4 2 0 2 NULL
F.F. 42 4 4 4 2 0 3 NULL
F.F. 43 4 4 4 2 0 4 NULL
F.F. 44 4 4 2 2 0 4 NULL
F.F. 45 4 4 4 2 0 3 NULL
F.F. 46 4 2 1 2 0 4 NULL
F.F. 47 4 4 4 2 0 3 NULL
F.F. 48 4 4 3 2 0 2 NULL
F.F. 49 3 3 1 1 0 1.1 NULL
F.F. 50 3 3 0 1 0 1.1 NULL
S.F. 51 2 4 4 2 0 2 NULL
S.F. 52 2 4 4 2 0 2 NULL
S.F. 53 3 4 4 2 0 2 NULL
S.F. 54 4 4 4 2 0 2 NULL
S.F. 55 2 4 2 2 0 2 NULL
S.F. 56 4 2 1 2 0 2 NULL
S.F. 57 1 1 1 2 0 4 NULL
S.F. 58 1 1 1 3 0 4 NULL
S.F. 59 2 0 3 1 0 2 NULL
S.F. 60 3 3 3 1 0 1 NULL
CCA_Hostel:
SN NAME ROLL NO. Year Quota Branch City State
1 Snehil CO20357 4th AIQ CSE Muzaffarpu Bihar
Bhardwaj r
2 Gaurav MCO22386 2nd AIQ CSE Patna Bihar
Prakash
3 Deepinder LCO21380 3rd AIQ CSE Rupnagar Punjab
Singh
4 Shivam MCO22391 2nd AIQ CSE Delhi Delhi
5 Samridh Raj LCO22226 2nd AIQ Civil Una H.P.
Singh
6 Sahil Deep MCO22240 2nd AIQ Civil Mohali Punjab
Singh
7 Tanuj Goyal LCO22241 2nd AIQ Civil Sangrur Punjab
8 Siddhant LCO22120 2nd AIQ mech Morinda Punjab
Kumar
(LEET)
SQL Queries
Query 1 :Write a SQL query to show Roll number of students with total dues
equal to 2400 .
Solution :
Query :
SELECT `Roll_no` FROM `fee_details` WHERE Total_Dues = 2400;
Output:
Query 2: Write a SQL query to show roll number of students with total dues
greater than 2400 .
Solution :
Query :
SELECT `Roll_no` FROM `fee_details` WHERE Total_Dues > 2400;
Output:
Query 3: Write a SQL query to show details of students with total dues equal
to 2400 .
Solution :
Query :
SELECT * FROM student_info NATURAL JOIN fee_details
WHERE Total_Dues = 2400;
Output :
Ques 4: Write a SQL query to show fee details of all fourth year student.
Solution :
Query:
SELECT * FROM fee_details NATURAL JOIN student_info
WHERE year = 4;
Output:
Query 5: Write a SQL Query to show details of all 2nd year students.
Solution :
Query:
SELECT * FROM `student_info` WHERE year = 2 ;
Output:
Query 6: Write a SQL Query to retrieve name of all students with total dues
greater than 5000 .
Solution:
Query
SELECT * FROM fee_details NATURAL JOIN student_info
WHERE total_dues >5000 ;
Output:
Query 7: Write a SQL Query to delete the record of student named ‘Aryan
Mittal ’.
Solution:
Query:
DELETE FROM `student_info` WHERE student_name = 'Aryan Mittal' ;
Output:
WARNING before deletion:
Confirmation:
Query 8: Write a SQL Query to update mobile number of ‘krishana Singla’ to
‘9998887770’ .
Solution :
Query:
UPDATE `student_info` SET `Mobile_number`='9998887770' WHERE
`Student_name`='Krishana Singla' ;
Output:
Comformation:
Contact information updated:
Query 9: Write a SQL Query to insert details of a new student(dummy) in
the student info table.
Solution :
Query:
INSERT INTO `student_info`(`Roll_no`, `Student_name`, `Mobile_number`,
`E-mail`, `Sex`, `Year`, `Branch`) VALUES ('[value-1]','[value-2]','[value-
3]','[value-4]','[value-5]','[value-6]','[value-7]')
Output:
Comformation:
Output:
Sample data added in the table
Query 10: Write a SQL Query to show total dues of all 3rd year CSE
students.
Solution:
Query:
SELECT `Roll_no`,`Student_name` , `Total_Dues` FROM fee_details
NATURAL JOIN student_info WHERE YEAR = '3' AND Branch = 'CSE' ;
Output:
Query 11: Write a SQL Query to show student name who gave poor feedback
.
Solution:
Query:
SELECT `Roll_no`, `Student_name` FROM student_info NATURAL JOIN
feedback WHERE feedback = 'poor' ;
Output:
(1-25) (26-50) (51-75) (76-79)
Query 12: Write a SQL Query to retrieve name, mobile number of those
students who last paid fee before August (.i.e. July/ May…).
Solution:
Query:
SELECT `Student_name`, `Mobile_number` FROM student_info NATURAL
JOIN fee_details WHERE Paid_till <> 'August';
Output:
Query 13: Write a SQL Query to retrieve student with maximum pending fee.
Solution :
Query:
SELECT `student_name`,`mobile_number`
FROM `student_info`
WHERE
Roll_no = ( SELECT `Roll_no`
FROM `fee_details`
WHERE Total_Dues = (SELECT MAX(Total_Dues) FROM
fee_details) )
Output:
Query 14: Write a SQL Query to retrieve details of student who paid till the
month of August using nested query.
Solution:
Query:
SELECT * FROM `student_info`
WHERE Roll_no IN ( SELECT Roll_no FROM fee_details WHERE
paid_till = 'August');
Output:
(1-25) (26-50)
(51-60)
Query 15: Write a SQL Query to retrieve data of those students who are not
male.
Solution:
Query:
SELECT * FROM `student_info` WHERE Sex < > 'M' ;
Output:
Output is NULL because no Non-Male student is there in the databse.
Query 16: Write a SQL Query of student with poor feedback and more than
Rs 3000 fees pending .
Solution :
Query:
SELECT * FROM student_info
WHERE Roll_no IN (SELECT fee_details.Roll_no FROM fee_details ,
feedback WHERE total_dues > 3000 AND feedback = 'poor' );
Output:
Query 17 : Find out the Roll_No, Name and Pending fee from:
SELECT Roll_no, Student_name, (SELECT
fee_details.Pending_fee
FROM fee_details WHERE fee_details.Roll_no =
student_info.Roll_no )AS Pending_Fee FROM student_info
WHERE 1;
Output
Query 18: Find all the fee_details and branch of all the students.
SELECT * ,(SELECT student_info.branch FROM student_info
WHERE fee_details.Roll_no
= student_info.Roll_no )AS
Pending_Fee FROM fee_details
WHERE 1;
Output
----------------------------------------------------------------------------------------------------------------------------- --------------
Query 19: Find out the Roll_No, Name and Pending fee from:
SELECT Roll_no, Student_name, (SELECT
fee_details.Pending_fee
FROM fee_details WHERE fee_details.Roll_no =
student_info.Roll_no )AS Pending_Fee FROM student_info
WHERE 1;
Output
Query 20: Find all the fee_details and branch of all the students.
SELECT * ,(SELECT student_info.branch FROM student_info
WHERE fee_details.Roll_no
= student_info.Roll_no )AS
Pending_Fee FROM fee_details
WHERE 1;
Output
Advantages of Boys Hostel Database:
Hostel management systems can help institutions manage inventory and supplies.
The system can generate alerts when items need to be restocked, and it can also provide a
detailed record of every hosteller.
This feature can help institutions keep track of what supplies they have and what they
may need sooner.
It streamlines and automates everyday hostel activities including admissions, fees, room
assignments, mess allocation, inventory management, room swapping, transfers,
vacations, and ins and outs of students.
It also generates reports for efficient transactions.
26 | P r a c t i c a l 6
