List every Product(maker, model, type and speed if it is a Laptop, or PC null if its a printer)

Knowledge of SQL is essential for any back-end developer, regardless of the programming language. This issue shows a list of options for solving problems of the popular sql-ex.ru website.

Before performing tasks, you need to understand the structure of the sql query:

The HAVING clause allows you to filter groups, that is, determines which groups will be included in the output.

Using HAVING is much the same as using WHERE. Only if WHERE is used to filter rows, then HAVING is used to filter groups.

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

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

SELECT maker FROM Product WHERE type = 'Printer' GROUP BY maker

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

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

Select * from printer where color = 'y'

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 = '12x' or cd = '24x') and price < 600

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.

Select distinct p.maker as maker, l.speed as speed from laptop l join product p on l.model = p.model where l.hd >= 10

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

Select distinct p.model, pc.price from product p join pc on p.model = pc.model where maker = 'B' union Select distinct p.model, l.price from product p join laptop l on p.model = l.model where maker = 'B' union Select distinct p.model, pr.price from product p join printer pr on p.model = pr.model where maker = 'B'

8. Find the makers producing PCs but not laptops.

select maker from product where type = 'pc' except select maker from product where type = 'laptop' или select distinct maker from product where type = 'pc' and maker not in (SELECT maker from product where type = 'laptop')

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

Select distinct p.maker from product p join pc pc on p.model = pc.model where pc.speed >= '450'

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

SELECT DISTINCT model, price FROM printer where price = (SELECT MAX(price) FROM printer)

11. Find out the average speed of PCs.

SELECT AVG(speed) FROM PC

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

Select AVG(speed) from laptop where price > '1000'

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

Select avg(pc.speed) from pc join product p on pc.model = p.model where maker = 'A'

14. For the ships in the Ships table that have at least 10 guns, get the class, name, and country.

Select s.class, s.name, c.country from classes c join ships s on c.class = s.class where numguns >= '10'

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(model) >= 2

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 A.model AS model, B.model AS model, A.speed As speed, A.ram As ram FROM PC AS A, PC B WHERE A.speed = B.speed AND A.ram = B.ram AND A.model > B.model

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

SELECT DISTINCT type, model, speed
FROM Laptop, (SELECT type FROM Product) AS Prod(type) WHERE speed < ALL (SELECT speed FROM PC) and type = 'laptop' или SELECT DISTINCT p.type, l.model, l.speed from laptop l join product p on p.model = l.model where l.speed < ALL (select speed from pc)

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

Select distinct p.maker, pr.price from product p join printer pr on p.model = pr.model where pr.price = (SELECT MIN(price)
FROM printer where color = 'y') and pr.color = 'y'

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 p.maker, AVG(l.screen) from product p join laptop l on p.model = l.model group by p.maker

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

Select maker, count(model) as Count_Model from product WHERE type = 'pc' group by maker having count(model) >= 3

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

Select p.maker, max(pc.price) as max_price from product p join pc pc on p.model = pc.model group by maker

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) from pc where speed > '600' group by speed

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

Select p.maker from product p join pc pc on p.model = pc.model where pc.speed >= '750' intersect Select p.maker from product p join laptop l on p.model = l.model where l.speed >= '750'

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

WITH all_model AS (SELECT model, price FROM pcUNION ALLSELECT model, price FROM printerUNION ALLSELECT model, price FROM laptop )SELECT distinct model

FROM all_model WHERE price = ALL ( SELECT max(price) FROM all_model)