MYSQL

we will work with the Sailingdatabase. Download the sailingDB-JS.sql from and load it in MySQL.Submit your lab report as Word document.

1.Read the statements in sailingDB-JS.sql. For each type of statements, choose one example to explain what that statement does. Refer to the textbook or MySQL reference manual for information.

2. Describe briefly each relation in the Sailing database. Point out the entity relations and association relations and explain your rationale.

3. Queries A and B below demonstrate the impact of order when we use outer jointo connect three or more database tables. Execute each query, then compare the query results, and include in your lab report:

•A screenshot of the results for each query

•An explanation of where and why the results of the two queries differ

Query A: SELECT SA.sid, SA.sname AS Sailor_Name, CONCAT('S:',RA.sid,'-B:',RA.bid) AS Reserv_Track_id, BA.bid, BA.bname AS Boat_NameFROM reserves RA right outer join sailors SA on RA.sid=SA.sid right outer join boats BA on RA.bid=BA.bid;

Query B: SELECT SA.sid, SA.sname AS Sailor_Name, CONCAT('S:',RA.sid,'-B:',RA.bid) AS Reserv_Track_id, BA.bid, BA.bname AS Boat_NameFROM reserves RA right outer join boats BA on RA.bid=BA.bid right outer join sailors SA on RA.sid=SA.sid;

4.QueriesC, D, and E attempt to find the names of sailors who have reserved BOTH a red boat and a green boat. Execute each query, compare the results, and include in your lab report:

•The screenshot of results for each query

•A comparative analysis of the different results

•Explanation of why Query E does not work

Query C: SELECT S.snameFROM Sailors S, Reserves R1,Boats B1,ReservesR2,Boats B2WHERE S.sid=R1.sid AND R1.bid=B1.bidAND S.sid=R2.sid AND R2.bid=B2.bidAND B1.color='red' AND B2.color='green';

Query D:SELECT S.snameFROM Sailors S, Reserves R, Boats BWHERE S.sid=R.sid AND R.bid=B.bid AND B.color='red'AND S.sid IN ( SELECT S2.sidFROM Sailors S2, Boats B2, Reserves R2WHERE S2.sid=R2.sid AND R2.bid=B2.bidAND B2.color='green');

Query E:SELECT S.snameFROM Sailors S, Reserves R, Boats BWHERE S.sid=R.sid AND R.bid=B.bidAND (B.color='red'AND B.color='green');

Reference to database below: