Task 1 (ships)

Short database description "Ships":

The database of naval ships that took part in World War II is under consideration. The database has the following relations:
Classes(class, type, country, numGuns, bore, displacement)
Ships(name, class, launched)
Battles(name, date)
Outcomes(ship, battle, result)
Ships in classes are arranged to a single project. A class is normally assigned the name of the first ship in the class under consideration (head ship); otherwise, the class name does not coincide with any ship name in the database.
The Classes relation includes the class name, type (bb for a battle ship, or bc for a battle cruiser), country where the ship was built, number of main guns, gun caliber (diameter of the gun barrel, in inches), and displacement (weight in tons). The Ships relation includes the ship name, its class name, and launch year. The Battles relation covers the name and date of a battle the ships participated; while the result of their participation in the battle (sunk, damaged, or unharmed – OK) is in the Outcomes relation. Note: the Outcomes relation may include the ships not included in the Ships relation.

 
Task: Point out the battles in which at least three ships from the same country took part.
 
Clumsy database!!! Clumsy solution 😦
 

select

distinct Battle from

(

select battle from

(

select class name, country, battle, 1 cnt from classes c

inner

join Outcomes o1 on c.class = o1.ship

union

select

name, country, battle, 1 cnt from Classes c

inner

join ships s on s.class = c.class

inner

join Outcomes o2 on o2.ship = s.name) x

group

by battle, country

having

SUM(cnt) > 2) x

 
 

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s