SELECT o.ship, displacement, numGuns FROM
(SELECT name AS ship, displacement, numGuns
FROM Ships s JOIN Classes c ON c.class=s.class
UNION
SELECT class AS ship, displacement, numGuns
FROM Classes c) AS a
RIGHT JOIN Outcomes o
ON o.ship=a.ship
WHERE battle = 'Guadalcanal'
Найдите названия всех кораблей в базе данных, состоящие из трех и более слов (например, King George V). Считать, что слова в названиях разделяются единичными пробелами, и нет концевых пробелов.
select name from ships
where name like '% % %'
union
select ship from outcomes
where ship like '% % %'
select name from Ships
where name LIKE 'R%'
UNION
SELECT Ship From Outcomes
where Ship LIKE 'R%'
select name
from battles
where year(date) not in
(select launched
from ships
where launched is not null)
SELECT ship, battle FROM Outcomes WHERE result = 'sunk'
Для ПК с максимальным кодом из таблицы PC вывести все его характеристики (кроме кода) в два столбца: - название характеристики (имя соответствующего столбца в таблице PC); - значение характеристики
select fields,A from
(
Select
cast(model as NVARCHAR(10)) as model
, cast (speed as NVARCHAR(10)) as speed
, cast(ram as NVARCHAR(10)) as ram
,cast(hd as NVARCHAR(10)) as hd
, cast(cd as NVARCHAR(10)) as cd
, cast(price as NVARCHAR(10)) as price from PC
where code = (Select max(code) from PC)
) as t
unpivot
(
A for fields in (model, speed, ram, hd, cd, price)
) as unpvt
Select Classes.class, Ships.name, Classes.country
from Ships inner join Classes
on Ships.class = Classes.class
where Classes.numGuns > = 10
SELECT s.class, s.name, c.country
FROM ships s
LEFT JOIN classes c ON s.class = c.class
WHERE c.numGuns >= 10
WITH b_s AS
(SELECT o.ship, b.name, b.date, o.result
FROM outcomes o
LEFT JOIN battles b ON o.battle = b.name )
SELECT DISTINCT a.ship FROM b_s a
WHERE UPPER(a.ship) IN
(SELECT UPPER(ship) FROM b_s b
WHERE b.date < a.date AND b.result = 'damaged')
SELECT country
FROM classes
GROUP BY country
HAVING COUNT(DISTINCT type) = 2
