QUESTION 3

Question 3
HOSTEL (H#, hname, haddr, total_capacity, warden)
ROOM (h#, r#, rtype, location, no_of_students, status)
CHARGES (h#, rtype, charges)
STUDENT (sid, sname, saddr, faculty, dept, class, h#, r#)
FEES (sid, fdate, famount)

The STATUS field tells us whether the room is occupied or vacant. The charges
represent the term fees to be paid half yearly. A student can pay either the annual fees at
one time or the half yearly fees twice a year.

CREATE TABLE

Hostel

create table hostel

(

          h# number(10) primary key,

          hname varchar2(20) NOT NULL,

          haddr varchar2(20) NOT NULL,

          total_capacity number(10),

          warden varchar2(20)

);

Room

create table room

(

          h# number(10) references hostel(h#),

          r# number(10) not null,

          rtype varchar2(10) not null,

          location varchar2(10) not null,

          no_of_students number(5),

          status varchar2(20),

          primary key(h#,r#)

);

Charges

create table charges

(

          h# number(10) references hostel(h#),

          rtype varchar2(10) not null,

          charges number(10),

          primary key(h#,rtype)

);

Student1

create table student1

(

          sid number(10) primary key,

          sname varchar2(10),

          saddr varchar2(10),

          faculty varchar2(10),

          dept varchar2(10),

          class varchar2(10),

          h# number(10) references hostel(h#),

          r# number(10)

);

Fees

create table fees

(

          sid number(10),

          fdate date,

          famount number(10),

          primary key(sid,fdate)

);

INSERT RECORDS

-- INSERTING INTO HOSTEL

insert into hostel values (1,'sardar patel','baroda',500,'vidhi');

insert into hostel values(2,'lala','baroda',100,'vidhi');

insert into hostel values(3,'jain chhatra','jamnagar',500,'kamini');

insert into hostel values(4,'krishana','bombay',500,'pooja');

-- INSERTING INTO ROOM

insert into room values(1,1,'s','haj',500,'vacant');

insert into room values(2,1,'f','haj',500,'vacant');

insert into room values(2,3,'s','haj',500,'vacant');

insert into room values(2,2,'t','haj',500,'vacant');

-- INSERTING INTO CHARGES

insert into charges values(1,'d',5000);

insert into charges values(2,'s',5000);

insert into charges values(3,'s',5000);

-- INSERTING INTO STUDENT

insert into student1 values(1,'ami','jam','antani sir',1,'mca1',1,2);

insert into student1 values(2,'pankaj','jam','bipin sir',2,'mca1',2,2);

insert into student1 values(3,'jigi','jam','seema mam',3,'mca1',3,2);

-- INSERTING INTO FEES

insert into fees values(1,'02-dec-2010',500);

insert into fees values(2,'12-dec-2010',500);

1. Add a check constraint to the room table so that the room type allows the following values only – ‘s’ for single, ‘d’ for double, ‘t’ for triple and ‘f’ for four-seater.
   
   alter table room add constraint t1 check (rtype in('s','d','t','f'));
2. Display the total number of rooms that are presently vacant.
   
   select count(*) from room where status='vacant';
   
3. Display number of students of each faculty and department wise staying in each hostel.
   
   select no_of_students,r.h#,s.faculty,s.dept from room r, student1 s where r.h#=s.h#;
   
   
   
4. Display hostels, which have at least one single-seated room.
   
   select rtype,b.h#,a.hname from room b, hostel a where rtype='s' and a.h#=b.h#;
   
5. Display the warden name and hostel address of students of Computer Science department.
   
   select hname,warden,sname,saddr from student1 a, hostel b where dept=2 and a.h#=b.h#;
   
6. Display those hostel details where single seated or double-seated rooms are vacant.
   
   select a.h#,r#,b.hname  from room a,hostel b where rtype='s' and status='vacant' and a.h#=b.h#;
   
7. Display details of hostels occupied by medical students.
   
   select a.h#,a.hname,a.warden from hostel a,student1 b where b.dept=3 and a.h#=b.h#;
   
   
8. Display hostels, which are totally occupied to its fullest capacity.
   
   select * from hostel where (h#,total_capacity)  in (select h#,count(sid) from student1 group by h#);
   
9. List details about students who are staying in the double-seated rooms of Chanakya Hostel.
   
   select c.r#,c.sname,c.saddr from student1 c  where (c.r#,c.h#) in ( select b.r#, a.h#  from hostel a, room b  where a.hname='lala' and  b.rtype='d' and a.h#=b.h#   );
   
10. Display the total number of students staying in each room type of each hostel.
    
    select s.h#,s.r#,count(sid) from student1 s, hostel h where h.h#=s.h#
    group by s.h#,s.r#;
    
11. Display details about students who have paid fees in the month of Nov. 2003.
    
    select s.sid,s.sname,s.saddr,s.dept from student1 s, fees f where s.sid=f.sid and to_char(fdate,'mon-yyyy')='nov-2003';
    
12. For those hostels where total capacity is more than 300, display details of students studying in Science faculty.
    
    select sid,sname,saddr from student1
    
    where h# in
    (
    select h# from hostel where total_capacity>300
    )
                and dept=3;
    
13. Display hostel details where there are at least 10 vacant rooms.
    
    select h#,hname,warden from hostel where h# in (select h# from room group by h# having count(status)>=3);
    
14. Display details of students who have still not paid fees.
    
    select sid,sname,saddr from student1 where sid not in (select sid from fees);
    
15. Display those hostels where single-seated room is the costliest.
    
    select c.h#,h.hname,h.haddr from charges c,hostel h where charges=(select max(charges) from charges where rtype='s') and h.h#=c.h#;