[DB/P6] Solved some of the exercises

1 files changed, 103 insertions, 0 deletions

diff --git a/Year 3/Databases/Practice06.sql b/Year 3/Databases/Practice06.sql
new file mode 100644
index 0000000..8747a63
--- /dev/null
+++ b/Year 3/Databases/Practice06.sql
@@ -0,0 +1,103 @@
+-- 1. За всяка филмова звезда да се изведе името, рождената дата и с кое
+--    студио е записвала най-много филми. (Ако има две студиа с еднакъв
+--    брой филми, да се изведе кое да е от тях)
+use movies;
+
+
+-- 2. Да се изведат всички производители, за които средната цена на
+--    произведените компютри е по-ниска от средната цена на техните лаптопи.
+select p.maker, AVG(pc.price) from product p join pc on pc.model = p.model group by p.maker having AVG(pc.price) < (select AVG(l.price) from laptop l join product pr on pr.model = l.model where pr.maker = p.maker);
+-- 3. Един модел компютри може да се предлага в няколко конфигурации
+--    с евентуално различна цена. Да се изведат тези модели компютри,
+--    чиято средна цена (на различните му конфигурации) е по-ниска
+--    от най-евтиния лаптоп, произвеждан от същия производител.
+select p.model from product p join pc on pc.model = p.model group by p.model, p.maker having AVG(pc.price) < (select MIN(l.price) from laptop l join product pr on pr.model = l.model where pr.maker = p.maker);
+-- 4. Битките, в които са участвали поне 3 кораба на една и съща страна.
+select o.BATTLE from OUTCOMES o join SHIPS s on s.NAME = o.SHIP join CLASSES c on c.CLASS = s.CLASS group by c.COUNTRY having COUNT(s.NAME) >= 3;
+-- 5. За всеки кораб да се изведе броят на битките, в които е бил увреден.
+--    Ако корабът не е участвал в битки или пък никога не е бил
+--    увреждан, в резултата да се вписва 0.
+select s.NAME, COUNT(o.BATTLE) from SHIPS s left outer join OUTCOMES o on o.SHIP = s.NAME and o.RESULT = "damaged" group by s.NAME;
+-- 6. За всеки клас да се изведе името, държавата и първата година, в която
+--    е пуснат кораб от този клас
+select c.CLASS, c.COUNTRY, (select s.LAUNCHED from SHIPS s where s.CLASS = c.CLASS order by s.LAUNCHED asc LIMIT 1) from CLASSES c;
+-- 7. За всяка държава да се изведе броят на корабите и броят на потъналите
+--    кораби. Всяка от бройките може да бъде и нула.
+
+-- 8. Намерете за всеки клас с поне 3 кораба броя на корабите от
+--    този клас, които са с резултат ok.
+
+-- 9. За всяка битка да се изведе името на битката, годината на
+--    битката и броят на потъналите кораби, броят на повредените
+--    кораби и броят на корабите без промяна.
+
+-- 10. Да се изведат имената на корабите, които са участвали в битки в
+--     продължение поне на две години.
+
+-- 11. За всеки потънал кораб колко години са минали от пускането му на вода
+--     до потъването.
+
+-- 12. Имената на класовете, за които няма кораб, пуснат на вода след 1921 г.,
+--     но имат пуснат поне един кораб.
+
+
+-- 1. Всички филми, чието заглавие съдържа едновременно думите 'Star' и 'Trek'
+--    (не непременно в този ред). Резултатите да се подредят по година (първо
+--    най-новите филми), а филмите от една и съща година - по азбучен ред.
+select * from MOVIE m where m.TITLE like "%Star%Trek%" or m.TITLE like "%Trek%Star%" order by m.YEAR, m.TITLE;
+-- 2. Заглавията и годините на филмите, в които са играли звезди, родени между
+--    1.1.1970 и 1.7.1980
+select distinct m.TITLE, m.YEAR from MOVIE m join STARSIN si on si.MOVIETITLE = m.TITLE join MOVIESTAR ms on ms.NAME = si.STARNAME where ms.BIRTHDATE > "19700101" and ms.BIRTHDATE < "19800701";
+-- 3. За всеки актьор/актриса изведете броя на различните студиа, с които са
+--    записвали филми, като не се интересуваме от тези, за които не е ясно
+--    в кои филми са се снимали.
+select si.STARNAME, COUNT(DISTINCT m.STUDIONAME) from STARSIN si join MOVIE m on m.TITLE = si.MOVIETITLE group by si.STARNAME;
+-- 4. За всеки актьор/актриса изведете броя на различните студиа, с които са
+--    записвали филми, като се интересуваме и от тези, за които не е ясно
+--    в кои филми са се снимали (0 студиа).
+select ms.NAME, COUNT(DISTINCT m.STUDIONAME) from MOVIESTAR ms left outer join STARSIN si on si.STARNAME = ms.NAME left outer join MOVIE m on m.TITLE = si.MOVIETITLE group by ms.NAME;
+-- 5. Изведете имената на актьорите, участвали в поне 2 филма след 1978 г.
+select si.STARNAME from STARSIN si join MOVIE m on m.TITLE = si.MOVIETITLE where si.MOVIEYEAR > 1978 group by si.STARNAME having COUNT(si.MOVIETITLE) >= 2;
+-- 6. Изведете най-дългия филм (ако са няколко с еднаква максималната дължина -
+--    да се изведат всичките.
+select * from MOVIE where LENGTH = (select MAX(LENGTH) from MOVIE);
+-- 7. Заглавията и годините на всички филми (без повторения), заснети преди 1982,
+--    в които е играл поне един актьор/актриса, чието име не съдържа нито буквата
+--    'k', нито 'b'. Първо да се изведат най-старите филми.
+select distinct m.TITLE, m.YEAR from MOVIE m join STARSIN si on si.MOVIETITLE = m.TITLE where YEAR < 1982 and si.STARNAME not like "%K%" and si.STARNAME not like "%B" group by m.TITLE order by m.YEAR asc;
+-- 8. Заглавието, годината и дължината на всички филми, които са от същото студио,
+--    от което е и филма Terms of Endearment от 1983г., но дължината им е по-малка
+--    от неговата или неизвестна.
+select m.TITLE, m.YEAR, m.LENGTH from MOVIE m join (select * from MOVIE where TITLE = "Terms of Endearment" and YEAR = 1983) mm on m.LENGTH < mm.LENGTH or m.LENGTH is NULL;
+-- 9. Имената на всички продуценти, които са и филмови звезди и са играли в поне
+--    един филм преди 1980г. и поне един след 1985г.
+select me.NAME from MOVIEEXEC me join STARSIN si1 on si1.STARNAME = me.NAME and si1.MOVIEYEAR < 1980 join STARSIN si2 on si2.STARNAME = me.NAME and si2.MOVIEYEAR > 1985;
+-- 10. Всички черно-бели филми, записани преди най-стария цветен филм
+--     (InColor='y'/'n') на същото студио.
+
+-- 11. Имената и адресите на студиата, които са работили с по-малко от 4 различни
+--     филмови звезди. Студиа, за които няма посочени филми или има, но не се знае
+--     кои актьори са играли в тях, също да бъдат изведени.
+--     Първо да се изведат студиата, работили с най-много звезди. Например, ако
+--     студиото има два филма, като в първия са играли A, B и C, а във втория -
+--     C, D и Е, то студиото е работило с 5 звезди общо.
+
+-- 12. Компютрите, които са по-евтини от всеки лаптоп на същия производител.
+
+-- 13. Компютрите, които са по-евтини от всеки лаптоп и принтер на същия производител.
+
+-- 14. Да се изведат различните модели компютри, подредени по цена на най-скъпия
+--     конкретен компютър от даден модел.
+
+-- 15. Всички модели компютри, за които разликата в размера на най-големия и
+--     най-малкия харддиск е поне 20 GB.
+
+-- 16. За производителите на поне 2 лазерни принтера - броя на произвежданите PC-та
+
+-- 17. Да се изведат всички производители, за които средната цена на
+-- произведените компютри е по-ниска от средната цена на техните лаптопи
+
+-- 18. Един модел компютри може да се предлага в няколко конфигурации
+--     с евентуално различна цена. Да се изведат тези модели компютри,
+--     чиято средна цена (на различните му конфигурации) е по-ниска
+--     от най-евтиния лаптоп, произвеждан от същия производител.