mysql> SELECT FROM Classes | class | type | country numGuns bore displacement |
ID: 3705569 • Letter: M
Question
mysql> SELECT FROM Classes | class | type | country numGuns bore displacement | | Bismarck | Iowa | Kongo | North Carolina | bb USIA | Renowrn | Revenge | Tennessee | Yamato 15 16 14 16 15 15 14 9 |18 42000 | 46000 I 32000 I 37000 I 32000 | 29000 | 32000 I 65000 I | bb Germany bb USA | bc Japan bc Gt. Britain | | bb Gt. Britain | | bb|USA | bb Japan 12 8 rows in set (0.00 sec) mysql> SELECT FROM Battles | name | date | Denmark Strait | 5/24-27/41 | | Guadalcanal 11/15/42I | North Cape | Surigao Strait | 10/25/44I | 12/26/43 I 4 rows in set (0.00 sec) mysql> SELECT FROM Outcomes; I ship | battle | result |I | Arizona | Bismarck | California | Duke of YorkNorth Cape | Pearl HarborsunkI | Denmark Strait | sunk | Surigao Strait | ok ok Fuso | Hood | King George VDenmark Strait | ok | Kirishima | Prince of Wales | Denmark Strait | damaged | | Rodney | Scharnhorst | South Dakota Guadalcana ok l Tennessee | Washington | West Virginia Surigao Strait | ok | Yamashiro | Surigao Strait | sunkI | Denmark Strait | sunkI | Guadalcanal sunk I | Denmark Strait | ok | North Cape sunk | Surigao Strait | ok | Guadalcanal ok | Surigao Strait | sunk| 16 rows in set (0.00 sec)Explanation / Answer
To select the name and launched date, battle name and battle date of the ship that was sunk in the battle :
Outcomes table may contain ships that are not present in Ships table, so to get the record for ships that was sunk in battle having a launched date we use below query:
select o.ship,s.launched,o.battle,b.date from outcomes o, ships s, battles b where o.result = "sunk" and o.ship = s.name and o.battle = b.name
To get ships that was sunk in the table but the ship's record not present in Ships table we use the below query:
select o.ship,"unknown",o.battle,b.date from outcomes o, battles b,ships s where o.result = "sunk" and o.ship not in (select name from ships) and o.battle = b.name ;
By combining both the queries, we get all ships name, launched date if present else unknown , battle name and battle date for ships that was sunk in the battle:
DISTINCT removes the duplicate records.
select o.ship,s.launched,o.battle,b.date from outcomes o, ships s, battles b where o.result = "sunk" and o.ship = s.name and o.battle = b.name
UNION DISTINCT
select o.ship,"unknown",o.battle,b.date from outcomes o, battles b,ships s where o.result = "sunk" and o.ship not in (select name from ships) and o.battle = b.name ;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.