SQL Practice: Level 20
Hands-On SQL Training
I think it's high time to put everything you've learned in this level into practice - time for a real SQL battle! In this level you'll come across tasks from both the first database and the second one. We won't repeat their descriptions, we'll only remind you of the schemas. We believe in you, now-experienced SQL Jedi. Build the right queries, and the secrets of ancient battles will be revealed to you.
The queries in this assignment are a whole level harder than in the previous two levels. Note that almost every query will require JOIN, LEFT JOIN, or INNER JOIN, so review these topics once more before starting the practice.
Database Schemas "Ship Battles" and "Computer Firm"
1. Find the average price of PCs and laptops made by maker A (Latin letter). Output: a single overall average price.
2. List the ships sunk in battles in the North Atlantic (North Atlantic). Output: ship.
3. Find the class, name, and country for the ships in the Ships table that have at least 10 guns.
4. Find the names of the ships sunk in battles, and the name of the battle in which they were sunk.
5. Find the ship classes in which at least one ship was sunk in a battle.
6. Find the battles in which ships of the Kongo class from the Ships table took part.
7. List the names of the lead ships present in the database (take into account the ships in Outcomes).
8. Find the classes that include only one ship from the database (also take into account the ships in Outcomes).
9. Find the countries that have ever had classes of ordinary battleships ('bb') and have ever had classes of battlecruisers ('bc').
10. For each ship that took part in the Battle of Guadalcanal (Guadalcanal), output the name, displacement, and number of guns.
Take the time to really understand how the tables are structured before you start writing queries. Either way, come back to the schema whenever you need it
Consider a database of ships that took part in World War II. It has the following relations:
Classes (class, type, country, numGuns, bore, displacement)
Ships (name, class, launched)
Battles (name, date)
Outcomes (ship, battle, result)
Ships in a "class" are built to the same design, and the class is given either the name of the first ship built to that design, or, if the class is named after the design itself, a name that does not match any of the ships in the database. The ship that gave its name to the class is called the lead ship.
The Classes relation contains the class name, the type (bb for a battleship or bc for a battlecruiser), the country where the ship was built, the number of main guns, the gun caliber (the diameter of the gun barrel in inches), and the displacement (weight in tons).
The Ships relation records the name of the ship, the name of its class, and the year it was launched.
The Battles relation includes the name and date of the battle in which the ships took part, and the Outcomes relation records the result of the given ship's participation in the battle (sunk, damaged, or unharmed - OK).
Notes. 1) The Outcomes relation may include ships that are absent from the Ships relation. 2) A sunk ship does not take part in subsequent battles.