---

База данных "Компьютерная фирма".

Схема БД состоит из четырех таблиц:

Название таблицы	Поля таблицы
Product	(maker, model, type)
PC	(code, model, speed, ram, hd, cd, price)
Laptop	(code, model, speed, ram, hd, price, screen)
Printer	(code, model, color, type, price)

Таблица Product представляет
производителя (maker),
номер модели (model)
и тип ('PC' - ПК, 'Laptop' - ноутбук или 'Printer' - принтер).
Предполагается, что номера моделей в таблице Product уникальны для всех производителей и типов продуктов.

В таблице PC для каждого ПК, однозначно определяемого
уникальным кодом – code, указаны
модель – model (внешний ключ к таблице Product),
скорость - speed (процессора в мегагерцах),
объем памяти - ram (в мегабайтах),
размер диска - hd (в гигабайтах),
скорость считывающего устройства - cd (например, '4x') и
цена - price (в долларах).

Таблица Laptop аналогична таблице РС за исключением того, что вместо скорости CD содержит
размер экрана - screen (в дюймах).

В таблице Printer для каждой модели принтера указывается,
является ли он цветным - color ('y', если цветной),
тип принтера - type (лазерный – 'Laser', струйный – 'Jet' или матричный – 'Matrix')
и цена - price.

Задание 1:

Найдите номер модели, скорость и размер жесткого диска для всех ПК стоимостью менее 500 дол.
Вывести: model, speed и hd.

SQL-запрос 1:

SELECT model, speed, hd FROM PC WHERE price < 500

Задание 2:

Найдите производителей принтеров.
Вывести: maker

SQL-запрос 2:

SELECT DISTINCT maker FROM Product WHERE type = 'Printer'

Задание 3:

Найдите номер модели, объем памяти и размеры экранов ноутбуков, цена которых превышает 1000 дол.

SQL-запрос 3:

SELECT model, ram, screen FROM Laptop WHERE price > 1000

Задание 4:

Найдите все записи таблицы Printer для цветных принтеров.

SQL-запрос 4:

SELECT * FROM Printer WHERE color='y'

Задание 5:

Найдите номер модели, скорость и размер жесткого диска ПК, имеющих 12x или 24x CD и цену менее 600 дол.

SQL-запрос 5:

SELECT model, speed, hd FROM PC WHERE (cd='12x' OR cd='24x') AND price<600

Задание 6:

Для каждого производителя, выпускающего ноутбуки c объёмом жесткого диска не менее 10 Гбайт, найти скорости таких ноутбуков.
Вывод: производитель, скорость.

SQL-запрос 6:

SELECT DISTINCT Product.Maker, Laptop.speed FROM Product
INNER JOIN Laptop ON Product.model=Laptop.model
WHERE Laptop.hd>=10 AND Product.type='Laptop'

Задание 7:

Найдите номера моделей и цены всех имеющихся в продаже продуктов (любого типа) производителя B (латинская буква).

SQL-запрос 7:

SELECT PC.model, PC.price FROM PC
JOIN Product ON PC.model=Product.model WHERE Product.maker = 'B'
UNION
SELECT Laptop.model, Laptop.price FROM Laptop
JOIN Product ON Laptop.model=Product.model WHERE Product.maker = 'B'
UNION
SELECT Printer.model, Printer.price FROM Printer
JOIN Product ON Printer.model=Product.model WHERE Product.maker = 'B' 

Задание 8:

Найдите производителя, выпускающего ПК, но не ноутбуки.
Вывести: maker

SQL-запрос 8:

SELECT DISTINCT maker FROM product
WHERE type = 'PC' AND maker IN
(SELECT maker FROM product WHERE maker NOT IN
(SELECT DISTINCT maker FROM product WHERE type = 'Laptop'))

SELECT DISTINCT maker FROM product
WHERE type = 'PC' AND maker IN
(SELECT maker FROM product
EXCEPT
SELECT DISTINCT maker FROM product WHERE type = 'Laptop')

Задание 9:

Найдите производителей ПК с процессором не менее 450 Мгц.
Вывести: Maker

SQL-запрос 9:

SELECT DISTINCT maker FROM product WHERE model IN
(SELECT model FROM PC WHERE speed>=450)

Задание 10:

Найдите модели принтеров, имеющих самую высокую цену.
Вывести: model, price

SQL-запрос 10:

SELECT model, price FROM printer WHERE price IN
(SELECT MAX(price) FROM printer)

Задание 11:

Найдите среднюю скорость ПК.

SQL-запрос 11:

SELECT AVG(speed) FROM PC

Задание 12:

Найдите среднюю скорость ноутбуков, цена которых превышает 1000 дол.

SQL-запрос 12:

SELECT AVG(speed) FROM Laptop WHERE price > 1000

Задание 13:

Найдите среднюю скорость ПК, выпущенных производителем A.

SQL-запрос 13:

SELECT AVG(speed) FROM PC WHERE model IN
(SELECT model FROM product WHERE maker='A')

Задание 15:

Найдите размеры жестких дисков, совпадающих у двух и более PC.
Вывести: HD

SQL-запрос 15:

SELECT hd FROM PC GROUP BY hd HAVING COUNT(hd)>=2

Задание 16:

Найдите пары моделей PC, имеющих одинаковые скорость и RAM.
В результате каждая пара указывается только один раз, т.е. (i,j), но не (j,i).
Порядок вывода: модель с большим номером, модель с меньшим номером, скорость и RAM.

SQL-запрос 16:

SELECT DISTINCT P1.model, P2.model, P1.speed, P1.ram FROM PC AS P1, PC AS P2
WHERE P1.model > P2.model AND P1.speed=P2.speed AND P1.ram=P2.ram

Задание 17:

Найдите модели ноутбуков, скорость которых меньше скорости каждого из ПК.
Вывести: type, model, speed.

SQL-запрос 17:

SELECT DISTINCT P.type, L.model, L.speed FROM Laptop AS L, Product AS P
WHERE L.model=P.model AND L.speed<(SELECT MIN(speed) FROM PC)

Задание 18:

Найдите производителей самых дешевых цветных принтеров.
Вывести: maker, price.

SQL-запрос 18:

SELECT DISTINCT P.maker, T.price FROM Product P, Printer T
WHERE P.model=T.model AND T.color='y'
AND T.price=(SELECT MIN(price) FROM Printer WHERE color='y')

Задание 19:

Для каждого производителя, имеющего модели в таблице Laptop, найдите средний размер экрана выпускаемых им ноутбуков.
Вывести: maker, средний размер экрана.

SQL-запрос 19:

SELECT maker, AVG(screen) FROM
(SELECT maker, screen FROM Product P, Laptop L
WHERE P.model=L.model) AS M
GROUP BY maker

Задание 20:

Найдите производителей, выпускающих по меньшей мере три различных модели ПК.
Вывести: Maker, число моделей ПК.

SQL-запрос 20:

SELECT maker, COUNT(model) FROM Product
WHERE type='PC'
GROUP BY maker
HAVING COUNT(model)>=3

Задание 21:

Найдите максимальную цену ПК, выпускаемых каждым производителем, у которого есть модели в таблице PC.
Вывести: maker, максимальная цена.

SQL-запрос 21:

SELECT maker, MAX(price) FROM
(SELECT P.maker, price FROM Product P, PC K
WHERE P.model=K.model) AS M
GROUP BY maker

Задание 22:

Для каждого значения скорости ПК, превышающего 600 МГц, определите среднюю цену ПК с такой же скоростью.
Вывести: speed, средняя цена.

SQL-запрос 22:

SELECT speed, AVG(price) FROM
(SELECT speed, price FROM PC
WHERE speed>600) AS M
GROUP BY speed

Задание 23:

Найдите производителей, которые производили бы как ПК со скоростью не менее 750 МГц, так и ПК-блокноты со скоростью не менее 750 МГц.
Вывести: Maker.

SQL-запрос 23:

WITH DB AS (
    SELECT maker FROM product
    WHERE model IN (
        SELECT model FROM PC
        WHERE speed >= 750
    )
    INTERSECT
    SELECT maker FROM product
    WHERE model IN (
        SELECT model FROM Laptop
        WHERE speed >= 750
    )
)
SELECT DISTINCT maker FROM DB

Задание 24:

Перечислите номера моделей любых типов, имеющих самую высокую цену по всей имеющейся в базе данных продукции.

SQL-запрос 24:

SELECT DISTINCT M.model FROM
(SELECT model, price FROM PC
UNION
SELECT model, price FROM laptop
UNION
SELECT model, price FROM printer) as M
WHERE M.price IN
(SELECT MAX(dev.price) AS maxp FROM
(SELECT price FROM pc
UNION
SELECT price FROM laptop
UNION
SELECT price FROM printer) AS dev)

WITH MP AS (
SELECT model, price FROM PC
UNION
SELECT model, price FROM laptop
UNION
SELECT model, price FROM printer)
SELECT DISTINCT model FROM MP WHERE price IN
(SELECT MAX(price) FROM MP)

Задание 25:

Найдите производителей принтеров,
которые производят ПК с наименьшим объемом RAM и
с самым быстрым процессором среди всех ПК, имеющих наименьший объем RAM.
Вывести: Maker.

SQL-запрос 25:

SELECT DISTINCT maker FROM Product
WHERE type='Printer' AND maker IN (
    SELECT maker FROM Product
    WHERE model IN (
         SELECT model FROM PC
         WHERE (
             speed=(SELECT MAX(speed) FROM (
                 SELECT speed FROM PC
                 WHERE ram=(SELECT MIN(ram) FROM PC)
             ) AS DB)
             AND ram=(SELECT MIN(ram) FROM PC)
         )
    )
)

Задание 26:

Найдите среднюю цену ПК и ПК-блокнотов, выпущенных производителем A (латинская буква).
Вывести: одна общая средняя цена.

SQL-запрос 26:

WITH K AS (
    SELECT model, price FROM PC
    UNION ALL
    SELECT model, price FROM Laptop
)
SELECT AVG(price) FROM (
    SELECT price FROM K, Product F
    WHERE K.model=F.model AND maker='A'
) AS DB

Задание 27:

Найдите средний размер диска ПК каждого из тех производителей, которые выпускают и принтеры.
Вывести: maker, средний размер HD.

SQL-запрос 27:

SELECT maker, AVG(hd) FROM Product F, PC K
WHERE F.model = K.model AND K.model IN (
    SELECT model FROM Product
    WHERE type='PC' AND maker IN (
        SELECT maker FROM Product
        WHERE type='Printer'
    )
)
GROUP BY maker

Задание 28:

Используя таблицу Product, определить количество производителей, выпускающих по одной модели.

SQL-запрос 28:

SELECT COUNT(maker) FROM (
    SELECT maker FROM Product
    GROUP BY maker
    HAVING COUNT(maker) = 1
) AS DB

Задание 35:

В таблице Product найти модели, которые состоят только из цифр или только из латинских букв (A-Z, без учета регистра).
Вывод: номер модели, тип модели.

SQL-запрос 35:

SELECT model, type FROM product
WHERE UPPER(model) NOT like '%[^0-9]%' OR model NOT like '%[^A-Z.]%'

NOT LIKE %[^0-9]% = найди все строки, кроме тех, в которых есть хотя бы один символ, отличный от цифры LIKE %[0-9]% = найди все строки, в которых есть хотя бы одна цифра.
Таким образом, первая команда вернёт только те строки, которые целиком состоят из цифр (если будет хоть одна не-цифра, тест не будет пройден), тогда как вторая вернёт все строки, в которых имеется хотя бы одна цифра, остальные символы могут быть любыми.
Обратите внимание,
NOT LIKE %[^0-9]% = REGEXP '^[0-9]+$',
однако REGEXP работает гораздо менее эффективно по сравнению с LIKE.

Задание 40:

Найти производителей, которые выпускают более одной модели, при этом все выпускаемые производителем модели являются продуктами одного типа.
Вывести: maker, type

SQL-запрос 40:

SELECT DISTINCT maker, type FROM Product
WHERE maker IN (
SELECT T1.maker FROM (
    SELECT maker, COUNT(model) AS CNT FROM Product
    GROUP BY maker
    HAVING COUNT(model) > 1) AS T1
JOIN (
    SELECT maker, COUNT(type) AS CNT FROM (
        SELECT DISTINCT maker, type FROM Product) AS T
        GROUP BY maker
        HAVING COUNT(type) = 1
    ) AS T2
ON T1.maker = T2.maker
)

Задание 41:

Для каждого производителя, у которого присутствуют модели хотя бы в одной из таблиц PC, Laptop или Printer, определить максимальную цену на его продукцию.
Вывод: имя производителя, если среди цен на продукцию данного производителя присутствует NULL, то выводить для этого производителя NULL, иначе максимальную цену.

SQL-запрос 41:

WITH M AS (
    SELECT maker, type, MAX(price) AS PR FROM (
        SELECT maker, F.type, price FROM product F, laptop L
        WHERE F.model = L.model
        UNION
        SELECT maker, F.type, price FROM product F, PC
        WHERE F.model = PC.model
        UNION
        SELECT maker, F.type, price FROM product F, Printer P
        WHERE F.model = P.model
    ) AS T
    GROUP BY type, maker
)
SELECT maker, MAX(PR) FROM M
GROUP BY maker