SQL-ex Computer firm.txt

=====================================================================================================================

Short database description "Computer firm":

The database scheme consists of four tables:
Product(maker, model, type)
PC(code, model, speed, ram, hd, cd, price)
Laptop(code, model, speed, ram, hd, screen, price)
Printer(code, model, color, type, price)
The Product table contains data:
- maker
- model number
- type of product ('PC', 'Laptop', or 'Printer').
It is assumed that model numbers in the Product table are unique for all makers and product types.
Each personal computer in the PC table is unambiguously identified by:
- unique code
- additionally characterized by its model (foreign key referring to the Product table)
- processor speed (in MHz) – speed field
- RAM capacity (in Mb) - ram
- hard disk drive capacity (in Gb) – hd
- CD-ROM speed (e.g, '4x') - cd
- price.
The Laptop table is similar to the PC table, except that instead of the CD-ROM speed, it contains the screen size (in inches) – screen.
For each printer model in the Printer table, its output type (‘y’ for color and ‘n’ for monochrome) – color field,
printing technology ('Laser', 'Jet', or 'Matrix') – type, and price are specified.

Database Schema: https://www.sql-ex.ru/help/select13.php#db_1

=====================================================================================================================

Exercise: 1
Find the model number, speed and hard drive capacity for all the PCs with prices below $500.
Result set: model, speed, hd.

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

---------------------------------------------------------------------------------------------------------------------

Exercise: 2
List all printer makers. Result set: maker.

SELECT DISTINCT maker
FROM Product
WHERE type ='Printer';

---------------------------------------------------------------------------------------------------------------------

Exercise: 3
Find the model number, RAM and screen size of the laptops with prices over $1000.

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

---------------------------------------------------------------------------------------------------------------------

Exercise: 4
Find all records from the Printer table containing data about color printers.

SELECT *
FROM Printer
WHERE color = 'y';

---------------------------------------------------------------------------------------------------------------------

Exercise: 5
Find the model number, speed and hard drive capacity of PCs cheaper than $600 having a 12x or a 24x CD drive.

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

---------------------------------------------------------------------------------------------------------------------

Exercise: 6
For each maker producing laptops with a hard drive capacity of 10 Gb or higher, find the speed of such laptops. Result set: maker, speed.

1v
SELECT Product.maker, Laptop.speed
FROM Product, Laptop
WHERE Laptop.model = Product.model
AND Laptop.hd>=10
GROUP BY maker, speed;

2v
SELECT DISTINCT maker, speed
FROM Product
JOIN Laptop
ON Laptop.model = Product.model
WHERE hd>=10;

---------------------------------------------------------------------------------------------------------------------

Exercise: 7
Get the models and prices for all commercially available products (of any type) produced by maker B.

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

---------------------------------------------------------------------------------------------------------------------

Exercise: 8
Find the makers producing PCs but not laptops.

1v
SELECT DISTINCT maker
FROM Product
WHERE type = 'PC'
AND maker NOT IN 
(SELECT DISTINCT maker
FROM Product
WHERE type = 'Laptop');

2v
SELECT maker
FROM Product
WHERE type = 'PC'
AND maker NOT IN 
(SELECT maker
FROM Product
WHERE type = 'Laptop')
GROUP BY maker;

3v
SELECT maker
FROM Product
WHERE type = 'PC'
EXCEPT
SELECT maker
FROM Product
WHERE type = 'Laptop';

---------------------------------------------------------------------------------------------------------------------

Exercise: 9
Find the makers of PCs with a processor speed of 450 MHz or more. Result set: maker.

1v
SELECT Product.maker
FROM Product, PC
WHERE Product.model = PC.model
AND speed >= 450
GROUP BY Maker;

2v.SELECT Product.maker
FROM Product
JOIN PC
ON Product.model = PC.model
WHERE speed >= 450
GROUP BY Maker;

---------------------------------------------------------------------------------------------------------------------

Exercise: 10
Find the printer models having the highest price. Result set: model, price.

SELECT model, price 
FROM Printer
WHERE price = (SELECT MAX(price) FROM Printer);

---------------------------------------------------------------------------------------------------------------------

Exercise: 11
Find out the average speed of PCs.

SELECT AVG(speed)
FROM PC;

---------------------------------------------------------------------------------------------------------------------

Exercise: 12
Find out the average speed of the laptops priced over $1000.

SELECT AVG(speed)
FROM Laptop
WHERE price > 1000;

---------------------------------------------------------------------------------------------------------------------

Exercise: 13
Find out the average speed of the PCs produced by maker A.

1v
SELECT AVG(PC.speed)
FROM PC, Product
WHERE Product.model = PC.model
AND Product.maker = 'A';

2v
SELECT AVG(speed)
FROM PC
JOIN Product
ON Product.model = PC.model
WHERE Product.maker = 'A';

---------------------------------------------------------------------------------------------------------------------

Exercise: 15
Get hard drive capacities that are identical for two or more PCs.
Result set: hd.

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

---------------------------------------------------------------------------------------------------------------------

Exercise: 16
Get pairs of PC models with identical speeds and the same RAM capacity. Each resulting pair should be displayed only once, i.e. (i, j) but not (j, i).
Result set: model with the bigger number, model with the smaller number, speed, and RAM.

SELECT DISTINCT PC1.model, PC2.model, PC1.speed, PC1.ram
FROM PC PC1, PC PC2
WHERE PC1.speed = PC2.speed
AND PC1.ram = PC2.ram
AND PC1.model > PC2.model;

---------------------------------------------------------------------------------------------------------------------

Exercise: 17
Get the laptop models that have a speed smaller than the speed of any PC.
Result set: type, model, speed.

1v
SELECT DISTINCT Product.type, Product.model, Laptop.speed
FROM Product, Laptop
WHERE Product.model = Laptop.model
AND Laptop.speed < (SELECT MIN(speed) FROM PC);

2v
SELECT DISTINCT Product.type, Product.model, Laptop.speed
FROM Product, Laptop
WHERE Product.model = Laptop.model
AND Laptop.speed < ALL (SELECT speed FROM PC);

---------------------------------------------------------------------------------------------------------------------

Exercise: 18
Find the makers of the cheapest color printers.
Result set: maker, price.

1v
SELECT DISTINCT Product.maker, Printer.price
FROM Product, Printer
WHERE Product.model = Printer.model
AND Printer.color = 'y'
AND Printer.price = (SELECT MIN(price) FROM Printer WHERE color = 'y');

2v
SELECT maker, price
FROM Product
JOIN Printer
ON Product.model = Printer.model
WHERE Printer.color = 'y'
AND price = (SELECT MIN(price) FROM Printer WHERE color = 'y')
GROUP BY maker, price;

---------------------------------------------------------------------------------------------------------------------

Exercise: 19
For each maker having models in the Laptop table, find out the average screen size of the laptops he produces.
Result set: maker, average screen size.

SELECT maker, AVG(screen) 'average screen size'
FROM Product
JOIN Laptop
ON Product.model = Laptop.model
GROUP BY maker;

---------------------------------------------------------------------------------------------------------------------

Exercise: 20
Find the makers producing at least three distinct models of PCs.
Result set: maker, number of PC models.

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

---------------------------------------------------------------------------------------------------------------------

Exercise: 21
Find out the maximum PC price for each maker having models in the PC table. Result set: maker, maximum price.

SELECT maker, MAX(price) 'maximum price'
FROM Product
JOIN PC
ON Product.model = PC.model
GROUP BY maker;

---------------------------------------------------------------------------------------------------------------------

Exercise: 22
For each value of PC speed that exceeds 600 MHz, find out the average price of PCs with identical speeds.
Result set: speed, average price.

SELECT speed, AVG(price) 'average price'
FROM PC
WHERE speed > 600
GROUP BY speed;

---------------------------------------------------------------------------------------------------------------------
Exercise: 23
Get the makers producing both PCs having a speed of 750 MHz or higher and laptops with a speed of 750 MHz or higher.
Result set: maker

1v
SELECT maker
FROM Product
JOIN PC
ON Product.model = PC.model
WHERE PC.speed >= 750
INTERSECT
SELECT maker
FROM Product
JOIN Laptop
ON Product.model = Laptop.model
WHERE Laptop.speed >= 750;

2v
SELECT DISTINCT maker
FROM Product
JOIN PC
ON Product.model = PC.model
WHERE speed >= 750
AND maker IN (SELECT maker FROM Product JOIN Laptop ON Product.model = Laptop.model WHERE speed >= 750);

3v
SELECT Product.maker
FROM Product, PC
WHERE Product.model = PC.model
AND PC.speed >=750
AND maker IN (SELECT Product.maker FROM Product, Laptop WHERE Product.model = Laptop.model AND speed >= 750)
GROUP BY maker;

---------------------------------------------------------------------------------------------------------------------

Exercise: 24
List the models of any type having the highest price of all products present in the database.

SELECT model
FROM (
SELECT model, price FROM PC 
UNION
SELECT model, price FROM Laptop 
UNION
SELECT model, price FROM Printer
) "table 1"
WHERE price = (
SELECT MAX(price) 
FROM (
SELECT model, price FROM PC 
UNION
SELECT model, price FROM Laptop 
UNION
SELECT model, price FROM Printer
) "table 2"
);

---------------------------------------------------------------------------------------------------------------------

Exercise: 25
Find the printer makers also producing PCs with the lowest RAM capacity and the highest processor speed of all PCs having the lowest RAM capacity.
Result set: maker.

SELECT DISTINCT maker 
FROM Product
JOIN PC
ON Product.model = PC.model
WHERE maker IN (SELECT maker FROM Product WHERE type = 'Printer')
AND speed = (SELECT MAX(speed) FROM PC WHERE ram = (SELECT MIN(ram) FROM PC))
AND ram = (SELECT MIN(ram) FROM PC);

---------------------------------------------------------------------------------------------------------------------

Exercise: 26 
Find out the average price of PCs and laptops produced by maker A.
Result set: one overall average price for all items.

---------------------------------------------------------------------------------------------------------------------

Exercise: 27
Find out the average hard disk drive capacity of PCs produced by makers who also manufacture printers.
Result set: maker, average HDD capacity.

SELECT maker, AVG(hd) 'average HDD capacity'
FROM Product
JOIN PC
ON Product.model = PC.model
WHERE maker IN (SELECT DISTINCT maker from Product WHERE type ='Printer')
GROUP BY maker;

---------------------------------------------------------------------------------------------------------------------

Exercise: 28
Using Product table, find out the number of makers who produce only one model.

SELECT COUNT(maker) qty
FROM (SELECT maker FROM Product GROUP BY maker HAVING COUNT(model) = 1) "table"