Task 2 (ships)

In the Outcomes table, transform names of the ships containing more than one space, as follows:
replace all characters between the first and the last spaces (excluding these spaces) by symbols of an asterisk (*).
The number of asterisks must be equal to number of replaced characters.
Result set: the name of a ship, the transformed name of the ship



ship, substring(ship, 1, charindex(‘ ‘, ship, 1)) +

replicate(‘*’, DATALENGTH(ship) charindex(‘ ‘, ship, 1) charindex(‘ ‘, reverse(ship), 1)) + substring(ship, DATALENGTH(ship) charindex(‘ ‘, reverse(ship), 1) + 1, DATALENGTH(ship)) last from outcomes


charindex(‘ ‘, ship, charindex(‘ ‘ , ship, 1) + 1) > 0

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 😦


distinct Battle from


select battle from


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


join Outcomes o1 on c.class = o1.ship



name, country, battle, 1 cnt from Classes c


join ships s on s.class = c.class


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


by battle, country


SUM(cnt) > 2) x


row_number(), order by case

Пронумеровать уникальные пары {maker, type} из Product, упорядочив их следующим образом:
– имя производителя (maker) по возрастанию;
– тип продукта (type) в порядке PC, Laptop, Printer.
Если некий производитель выпускает несколько типов продукции, то выводить его имя только в первой строке;
остальные строки для ЭТОГО производителя должны содержать пустую строку символов (”).

–num maker type
–1 A PC
–2  Laptop
–3  Printer
–4 B PC
–5  Laptop
–6 C Laptop
–7 D Printer
–8 E PC
–9  Printer

select row_number() over (order by maker) num, maker2 maker, type from


select maker,



case row_number() over (partition by maker order by CASE

WHEN type = ‘PC’ THEN 1

WHEN type = ‘Laptop’ THEN 2

WHEN type = ‘Printer’ THEN 3



when 1 then maker



, type

from (select distinct maker, type from product) x) y