1 Add new column sailor name and rating to sailors table 2 Add new column boat

1 add new column sailor name and rating to sailors

This preview shows page 3 - 7 out of 20 pages.

1. Add new column sailor name and rating to sailors table. 2. Add new column boat name and color to Boats table. 3. Add a primary key to sailors table after table creation 4. Add a primary key to Boats table after table creation 5. Remove a Primary Key from Sailors table 6. Remove a Primary Key from Boats table 7. Add a not null constrain on Sailors, Boats Table 8. Drop not null constraints from Sailors, Boats Table. 9. Add a constraint check to the rating column 10. Drop the above check constraint from rating column. 11. Add a primary key constraint on Sailors, Boats tables. 12. Drop primary key constraints from sailors ,Boats tables. 13. Add foreign key constraints to reserves table. 14. Drop foreign key constraints from reserve table
Background image
4 15. Write SQL statements to insert following date into above relations. Sailors Sid Sname Rating Age 22 Dustin 7 45 29 Brutus 1 33 31 Lubber 8 55.5 32 Andy 8 25.5 58 Rusty 10 35 64 Horatio 7 35 71 Zorba 10 16 74 Horatio 9 35 85 Art 3 25.5 95 Bob 3 63.5 Reserves SID BID Day 22 101 10-10-1998 22 102 10-10-1998 22 103 10-08-1998 22 104 10-07-1998 31 102 10-11-1998 31 103 06-11-1998 31 104 11-12-1998 64 101 09-05-1998 64 102 09-08-1998 74 103 09-08-1998 Boats Bid Bname Color 101 Interlake blue 102 Interlake red 103 Clipper green 104 Marine red
Background image
5 Experiment 3Queries (along with sub Queries) using ANY, ALL, IN, EXISTS, NOTEXISTS, UNION, INTERSET, Constraints Create the following relations (tables) and write the following queries. Sailors Sid Sname Rating Age 22 Dustin 7 45 29 Brutus 1 33 31 Lubber 8 55.5 32 Andy 8 25.5 58 Rusty 10 35 64 Horatio 7 35 71 Zorba 10 16 74 Horatio 9 35 85 Art 3 25.5 95 Bob 3 63.5 Q1.Find the names of sailors who have reserved a red or a green boat. Q2. Find the names of sailors who have reserved a red and a green boat. Q3. Find the names of sailors who have reserved a red but not green boats. Q4.Find all sids of sailors who have a rating of 10 or reserved boat 104. Q5.Find the names of sailors who have reserved boat 103 using independent nested query. Q6.Find the names of sailors who have reserved a red boat. Q7.Find the names of sailors who have not reserved a red boat. Q8.Find the names of sailors who have reserved boat number 103 using correlated nested query. Q9.Find sailors whose rating is better than some sailor called ‘Horatio’. Q10.Find the sailors with the highest rating. Q11.Find the names of sailors who have reserved both a red and a green boat using nested queries. Q12.Find the names of sailors who have reserved all boats. Reserves SID BID Day 22 101 10-10-1998 22 102 10-10-1998 22 103 10-08-1998 22 104 10-07-1998 31 102 10-11-1998 31 103 06-11-1998 31 104 11-12-1998 64 101 09-05-1998 64 102 09-08-1998 74 103 09-08-1998 Boats Bid Bname Color 101 Interlake blue 102 Interlake red 103 Clipper green 104 Marine red
Background image
6 Experiment 4 Queries using Aggregate Functions (COUNT, SUM, AVG, MAX and MIN), GROUP BY, HAVING and Creation and Dropping of Views Create the following relations (tables) and write the following queries.
Background image
Image of page 7

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture