База данных "Компьютерная фирма".
Схема БД состоит из четырех таблиц:
Название таблицы | Поля таблицы |
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