DBMS-Slip4

Slip4

Quesions

Consider the following Entities and Relationships                                      

Room (roomno, desc, rate)

Guest (gno, gname, no_of_days)

Relation between Room and Guest is One to One.

Constraint: Primary key, no of days should be > 0.

Solution:-

Create a Database in 3NF & write queries for following.

• Display room details according to its rates  in ascending order.

SQL>Select desc,rate from room  order by desc ASC;

• Find the names of guest who has allocated room for more than 3 days.

SQL>Select  gname  from guest,room where guest.gno=room.rno and   no_of_days>3;

• Find no. of AC rooms.

SQL>select count(rno) from room where desc=’AC’;

• Display total amount for NON-AC rooms.

SQL>Select Sum(rate) from room where desc=’Non-AC’;

• Find names of guest with maximum room charges.

SQL>select gname from room,guest where guest.gno=room.rno  and rate = (select max(rate) from room);