Slip10
Consider the following Entities and Relationships
Sailor(sid, sname, age)
Boats(bid, bname, color)
Relation between Sailer and Boats is Many to Many with day as descriptive attribute.
Constraint: Primary key, age should be > 0.
Solution:-
Create a Database in 3NF & write queries for following.
•Display details of all boats sailed by sailor ‘Ram’.
SQL>select * from sailor,boats,sailor_boats where sailor.sid=sailor_boats.sid and boats.bid=sailor_boats.bid and sailor.sname=’Ram’;
•Display Sailor names working on blue boat.
SQL> select * from sailor,boats,sailor_boats where sailor.sid=sailor_boats.sid and boats.bid=sailor_boats.bid and color=’blue’;
•Count number of boats sailed by each sailor.
SQL>select count(boats.bname) ,sname from sailor,boats,sailor_boats where sailor.sid=sailor_boats.sid and boats.bid=sailor_boats.bid group by sname;
•Find the name of sailor who sailed the boat on both Tuesday & Friday.
SQL>Select sailor.sname from sailor,boats,sailor_boats where day =’ Tueday’ and day=’Friday’ and sailor.sid=sailor_boats.sid and boats.bid=sailor_boats.bid ;
SQL> Select sailor.sname from sailor,boats,sailor_boats where sailor.sid=sailor_boats.sid and boats. bid=sailor_boats.bid and sailor_boats.day='Tueday' and sailor_boats.day='Friday';
•Displaydetails of the boats which is sailed maximum times on Sundays.
SQL>select count(sailor_boats.day ) from boats,sailor_boats where boats.bid=sailor_boats.bid and sailor_boats.day='Sunday';
