Slip 7
Online Editor :-https://www.mycompiler.io/new/sql
Q3. Consider the following entities and their relationships. Create a RDB in 3 NF with
appropriate data types and Constraints. [15 Marks]
Room (roomno, desc, rate)
Guest (gno, gname, no_of_days)
The relationship between Room and Guest is One to One.
Constraint: Primary key, no of days should be > 0.
Consider the above tables and execute the following queries:
1. Update the rate of room to 5000 whose type is “AC”.
2. Display the name of guest who is staying 2 days in roomno 101.
Solution:-
SQL>Create table Room (rno int primary key, desc varchar(10),rate
int);
SQL>Insert into Room values(101 ,’AC’,2000);
SQL>Select * from Room;
SQL>COMMIT;
SQL>Create table Guest(gno int primary key ,gname
varchar(15),no_of_days int check(no_of_days>0));
SQL>Insert into Guest values(101,’Asha’,5);
SQL>Select * from Guest;
SQL>COMMIT;
Note:- HOW TO JOIN TWO TABLES ?
Select ( Field names) from First Table , Second Table where First Table.primary key = Second
Table.primary key
1. Update the rate of room to 5000 whose type is “AC”.
SQL>Update room set rate=5000 where desc='AC';
2. Display the name of guest who is staying 2 days in roomno 101.
SQL>Select gname from guest,room where guest.gno=room.rno
and no_of_days=2 and rno=(select rno from room where rno=101);
Q4. Consider the above tables and execute the
following queries: [25 Marks]
1. Display room details according to its rates in ascending order.
SQL>Select desc,rate from room
order by desc ASC;
2. Display the roomno in which “Nisha” is staying for 7 days.
SQL> Select * from guest,room where guest.gno=room.rno and
no_of_days=7 and gname=(select gname from guest where gname='nisha');
3. Find no. of AC rooms.
SQL>select count(rno) from room where desc=’AC’;
4. 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);
5. Display guest wise days.
SQL>Select gname from guest order by no_of_days Asc;
