Slip4
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.
Create a Database in 3NF & write queries for following.
Solution:-
Step 1 :-
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;
Step 2 :- HOW TO JOIN TWO TABLES ?
Select ( Field names) from First Table , Second Table where
First Table.primary key = Second Table.primary key
•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);
