sql-ex.ru (част 2- от 11 до 20 )

11.зад. Да се намери средната скорост на PC-тата
Отг. Select avg(speed) as speed FROM PC
12. Намерете средната скорост на Лаптопите които струват повече от 1000 долар.
Отг. SELECT avg(speed) as avg_speed FROM Laptop WHERE price>100013. Намерете средната скорост на PC-тата произведени от А
Отг.SELECT avg(PC.speed) as avg_speed FROM PC INNER JOIN Product ON PC.model=Product.model WHERE maker=’A’

14. Намерете само maker-ите, които произвеждат само модели от един и същи тип и броя на тези модели е повече от един.
Колони: maker, type
Тази задача определено доста ме затрудни. Може би от всичко GROUP BY ми е бил винаги най-труден за използване.  Голямата трудност е да се намерят производителите произвеждащи само един и същи тип продукти и никога нямаше да се сетя да ползвам DISTINCT по този начин ако не го бях видял в интернет:
Select maker FROM product
group by maker
having count(DISTINCT type)=1
След като вече знаем кои са производителите селектираме само тези производители които имат повече от един модел или:
SELECT maker,type FROM Product
GROUP BY maker,type
HAVING
maker IN
(SELECT maker FROM product GROUP BY maker HAVING count( DISTINCT TYPE ) =1)
AND
count( * ) >1

Трябва да се запомни че HAVING СЕ ИЗПОЛЗВА ЗА ДА СЕ ФИЛТРИРАТ СТОЙНОСТИТЕ ПО ГРУПИ ИЛИ СТРОГО ПОГЛЕДНАТО ПО КОЛОНИ, ДОКАТО WHERE СЕ ИЗПОЛЗВА ЗА ФИЛТРИРАНЕ НА СТОЙНОСТИТЕ ПО РЕДОВЕ. Самата GROUP BY клауза извършва групиране на колоните по зададената стойност като визуалният резултат е първият ред от всеки срещнат тип, като зад този ред можем виртуално да си представим че стоят останалите срещания на този като редове.

15. Намерете размера на хард дисковете, който е еднакъв за два и повече компютъра.
Колони: hd
Отг:
Select hd From PC Group by hd Having count(hd)>=2
16.зад.Намерете двойките компютри имащи същата скорост и RAM. Като резултат всяка двойка трябва да се покаже само веднъж , с други думи (х,у), но не (у,х).
Отг. SELECT DISTINCT p1.model, p2.model, p1.speed, p1.ram
FROM PC p1, PC p2
WHERE p1.speed=p2.speed AND p1.ram=p2.ram AND p1.model>p2.model
order by p1.model ASC
Тук главното е че за да използваме една и съща таблица за сравнение и от двете страни на условние то трябва тя да съществува два пъти в паметта и това става като се използват превдоними.

17. Намерете моделите на лаптопи, които имат скорост по-малка от на всички PC-тa.
Колони:type, model , speed
Отг:
Select DISTINCT p.type ,l.model, l.speed
from laptop as l INNER JOIN product as p ON l.model=p.model
WHERE l.speed< ALL (SELECT speed FROM PC)
18. зад. Да  се намерят производителите на най евтиният цветен принтер
Колони:maker, price
Отг:
SELECT DISTINCT pro.maker, pri.price
FROM product as pro INNER JOIN printer as pri ON pri.model=pro.model
where pri.price= ANY (SELECT min(price) from printer Group by color having color=’y’) and color=’y’
19. зад. За вски производител намерете средният размер на екраните на лаптопи, които се произвеждат от него.
Колони: maker, average screen size
SELECT p.maker, avg(l.screen)
FROM product as p INNER JOIN laptop as l ON l.model=p.model
GROUP by p.maker

20. да се намерят производителите на PC -та с поне три различни модела компютри
Колониt: maker, number of models.
SELECT maker , count(model)
FROM product
where type=’PC’
group by maker
having count(DISTINCT model)>2


Posted on 24 May, 2012, in Programing and tagged . Bookmark the permalink. Leave a comment.

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

%d bloggers like this: