SQL EX 4-5 (12-18,19-25,26-1)

--EXERCISE 4
--EX 4

--moe

SELECT *
FROM CLASSES,ships
where CLASSES.class=ships.CLASS
--sushtoto kato gornoto
select c.*,s.*,o.*
from ships s
join CLASSES c on s.class=c.CLASS
join OUTCOMES o on s.NAME=o.SHIP

SELECT *
FROM CLASSES
      JOIN SHIPS ON CLASSES.CLASS = SHIPS.CLASS--ne pokazva tezi za koito nqma suvpadashti klasove

SELECT *
FROM CLASSES
    LEFT OUTER JOIN SHIPS ON CLASSES.CLASS = SHIPS.CLASS--na tezi koito nqmat suvpadashti klasove im pishe null
    LEFT OUTER JOIN OUTCOMES ON SHIPS.NAME = OUTCOMES.SHIP





-- пример за full join
-- за всеки актьор и/или продуцент да се изведе
-- името му, рождената му дата и networth
use movies;
--moe nishto ne pokazva zashtoto nikoi movieexec ne suvpada s moviestar
select ms.name, me.name, ms.birthdate, me.networth
from moviestar ms
join movieexec me on ms.name = me.name;

-- неудобен начин:
select ms.name, me.name, ms.birthdate, me.networth
from moviestar ms
full join movieexec me on ms.name = me.name;
-- хубав начин:
select coalesce(ms.name, me.name) as name, ms.birthdate, me.networth
from moviestar ms
full join movieexec me on ms.name = me.name;

-- 1.1.
select title, year, name, address
from movie
join studio on studioname = name
where length > 120;

--ili:
select title,year,s.name,s.address
from movie
where LENGTH>120 and studioname in (select name from studio s)

-- 1.2.
--moe
select m.*,s.*
from movie m ,starsin s

--istinsko
select distinct studioname, starname
from movie
join starsin on title = movietitle and year = movieyear
order by studioname;
--
--1.3
-- ot tuk nagledno vijdame che harrison ford uchastva samo v empire strikes back i star wars s
--producer#=555 koito v moviexec suvpada s George Lucas; distinct polzvame za da ne se povtarq george lucas 2
--puti za 2ta filma
select *
from movieexec
select*
from movie
select *
from starsin

select distinct name
from movieexec me
join movie m on PRODUCERC#=CERT#--GRESHNO: me.cert#=m.producer#
join starsin s on title=movietitle
where starname='Harrison Ford'

--1.4
select * from starsin
select * from movie

select starname
from starsin
join moviestar on name=STARNAME
join movie m on movietitle=title
 where studioname ='MGM' and gender='F'

 --1.5
 select * from MOVIEEXEC
 select * from movie

 select name,title
 from movieexec me
 join movie on PRODUCERC#=CERT#
 where name in (select name from MOVIEEXEC where CERT# in( select PRODUCERC#
 from movie where title='Gone With the Wind'))

-- 1.6. Всички актьори, които не са играли във филми
-- (т.е. нямаме информация в кои филми са играли)
select * from starsin
select * from moviestar

select *
from moviestar
left join starsin on name = starname

--oficialno
select name
from moviestar
left join starsin on name = starname
where starname is null;
-- OR:
select name
from moviestar
where name not in (select starname from starsin);

-- 2.1.
use pc;

select maker, p.model, type
from product p
left join  (select model from pc
            union all
            select model from laptop
            union all
            select model from printer) t
    on p.model = t.model
where t.model is null;
-- OR:
select maker, model, type
from product
where model not in (select model from pc)
    and model not in (select model from laptop)
    and model not in (select model from printer);

use ships;
--3.1
select * from ships,CLASSES
--select * from ships where class='Bismarck'

select name,ships.class,country,bore,launched
from ships
join CLASSES c on ships.class=c.class


-- 3.3.
select distinct ship
from outcomes
join battles on battle=name
where year(date) = 1942;

-- 3.4.
select country, name
from classes
join ships on classes.class = ships.class
left join outcomes on name = ship
where outcomes.ship is null;
-- OR:
select country, name
from outcomes
right join ships on ship = name
join classes on classes.class = ships.class
where outcomes.ship is null;

--moe
select * from ships
select * from CLASSES
select * from OUTCOMES

select *
from ships s
join classes c on s.class=c.class
full join outcomes o on name=ship
where battle is NULL


-- За всеки клас британски кораби да се изведат
-- имената им (на класовете) и имената на всички битки,
-- в които са участвали кораби от този клас.
-- Ако даден клас няма кораби или има, но те не са
-- участвали в битка, също да се изведат (със стойност
-- NULL за име на битката).

--moe
select * from ships
select * from CLASSES order by country
select * from OUTCOMES order by ship

select c.class,ship,battle
from classes c
join ships s on s.class=c.class
full join outcomes o on name=ship
where country ='Gt.Britain'

--oficialno
select distinct classes.class, battle
from outcomes
join ships on ship = name
right join classes on ships.class = classes.class
where country = 'Gt.Britain';

-- нека пробваме да "обърнем" заявката, както направихме в 3.4:
-- грешно:
select distinct classes.class, battle
from classes
left join ships on classes.class = ships.class
join outcomes on name = ship
where country = 'Gt.Britain';

-- все още е грешно:
select distinct classes.class, battle
from classes
left join ships on classes.class = ships.class
left join outcomes on ship = name
where country = 'Gt.Britain';

-- Когато в една заявка има повече от един join
-- и поне един от тях е outer join, редът на join-ване
-- има значение. Ако са само inner join - няма.

-- ако държим на left join, може така:
select distinct classes.class, battle
from classes
left join (select *
            from ships
            join outcomes on ship = name) t
                on classes.class = t.class
where country = 'Gt.Britain';


-- важен пример: при outer join има разлика дали
-- дадено условие ще бъде в ON или WHERE (при inner
-- няма, понеже се свежда до подмножество на декартово
-- произведение):

-- За всеки клас да се изведат името му, държавата
-- и имената на всички негови кораби, пуснати през 1916 г.
select classes.class, country, name
from classes
join ships on classes.class = ships.class
where launched = 1916;

-- може да се напише и така, макар и безсмислено:
select classes.class, country, name
from classes
join ships
    on classes.class = ships.class and launched = 1916;

-- Да допълним горната задача, като добавим и класовете,
-- които нямат нито един кораб от 1916 - с/у тях да
-- пише NULL.

-- грешно:
select classes.class, country, name
from classes
left join ships on classes.class = ships.class
where launched = 1916;
--moe greshno
select *
from classes
full join ships on classes.class = ships.class
where launched = 1916;
-- изпускаме например класовете, които нямат кораби
-- при тях launched има стойност null

-- все още грешно:
select classes.class, country, name
from classes
left join ships on classes.class = ships.class
where launched = 1916 or launched is null;

-- липсват класове, които имат кораби, но нито един
-- от тези кораби не е от 1916

-- друг проблем: хващаме излишни редове, ако
-- launched по принцип позволява null

-- вярно:
select classes.class, country, name
from classes
left join ships
    on classes.class = ships.class and launched = 1916;

-- друго вярно, макар и излишно усложнено:
select classes.class, country, name
from classes
left join (select *
            from ships
            where launched = 1916) ships1916
on classes.class = ships1916.class;



-- Общи задачи

use movies;
--1
-- Да се изведат заглавията и годините на всички филми, чието заглавие
-- съдържа едновременно като поднизове "the" и "w" (не непременно в този ред).
-- Резултатът да бъде сортиран по година (първо най-новите), а филми от
-- една и съща година да бъдат подредени по азбучен ред.
select title, year
from movie
where title like '%the%' and title like '%w%'
order by year desc, title;

use ships;
--2
-- Държавите, които имат класове с различен калибър (bore)
-- (напр. САЩ имат клас с bore=14 и класове с bore=16,
-- докато Великобритания има само класове с 15)
select distinct c1.country
from classes c1
join classes c2 on c1.country = c2.country
where c1.bore <> c2.bore;
-- оптимизация: c1.bore < c2.bore - ще улесни работата на distinct по-горе
--ili moe:

select country
from classes c1
where bore not in (select bore from classes c2 where c2.country!=c1.COUNTRY)

use pc;
--3
-- Компютрите, които са по-евтини от всеки лаптоп
-- и принтер на същия производител
select pc.*
from pc
join product p on pc.model = p.model
where price < all (select price
                    from laptop
                    join product p1 on laptop.model=p1.model
                    where p1.maker = p.maker)
    and price < all (select price
                    from printer
                    join product p1 on printer.model=p1.model
                    where p1.maker = p.maker);
-- ако подзаявката върне празен списък, условието
-- price < all (...) ще бъде true



use ships;
--4
-- Имената на всички кораби, за които едновременно са изпълнени следните
-- условия:
-- (1) участвали са в поне една битка и
-- (2) имената им (на корабите) започват с C или K.
select distinct ship
from outcomes
where ship like 'C%' or ship like 'K%';

--5
-- Името, държавата и калибъра (bore) на всички класове кораби с 6, 8 или 10
-- оръдия. Калибърът да се изведе в сантиметри (1 инч е приблизително 2.54 см).
select class, country, bore * 2.54 as bore_cm
from classes
where numguns in (6, 8, 10);

--6
-- (От държавен изпит)
-- Имената на класовете, за които няма кораб, пуснат на вода (launched) след
-- 1921 г. Ако за класа няма пуснат никакъв кораб, той също трябва да излезе
-- в резултата.

-- грешно:
select distinct class
from ships
where launched <= 1921;

-- вярно:--tezi koito ne sa launch-nati prez 1921 gi nqma v tablicata ships, a gi ima samo v classes
select class
from classes
where class not in (select class from ships where launched > 1921);

-- вярно:
SELECT c.class
FROM classes c
WHERE NOT EXISTS (SELECT 1
                FROM ships t
                WHERE t.class=c.class
                    AND t.launched > 1921);
--moe
select classes.*,s.*
from classes
left join ships s on classes.class = s.class and launched > 1921

-- вярно:
select classes.class
from classes
left join ships on classes.class = ships.class and launched > 1921
where name is null;



select *
from MovieExec m1, Movie m2
where m1.CERT#=m2.PRODUCERC#


select *
from MovieExec m1 join Movie m2
on m1.CERT#=m2.PRODUCERC#


select *
from MovieExec m1 inner JOIN Movie m2
ON m1.CERT#=m2.PRODUCERC#

select *
from MovieExec m1 LEFT OUTER JOIN Movie m2
ON m1.CERT#=m2.PRODUCERC#

select *
from MovieExec m1 RIGHT  JOIN Movie m2
ON m1.CERT#=m2.PRODUCERC#

select *
from MovieExec CROSS JOIN Movie


SELECT AVG (netWorth)
FROM MovieExec

SELECT COUNT(*)
FROM StarsIn

SELECT COUNT(DISTINCT starName)
FROM StarsIn

SELECT MAX(Quantity * UnitPrice * (1 - Discount))
FROM "Order Details"


SELECT studioName, SUM(length)
FROM Movie
GROUP BY studioName






                                                            --EXERCISE 5
--EXERCISE 5
use movies;

SELECT AVG(netWorth)
FROM MovieExec;

SELECT COUNT(*)
FROM StarsIn;

SELECT COUNT(DISTINCT starName)
FROM StarsIn;

select count(*), count(distinct starName)
from StarsIn;


SELECT studioName, SUM(length)
FROM Movie
GROUP BY studioName;

-- ако нямаше GROUP BY, можеше така:
select distinct studioname, (select sum(length)
                             from movie
                             where studioname = m.studioname)
from movie m;

-- най-дългият филм (ако са повече от един, да се
-- изведат всички):
-- грешно:
select *
from movie
where length = max(length);
-- вярно:
select *
from movie

select *
from movie
where length = (select max(length) from movie);
--NE RABOTI
USE movies
select TITLE
from movie
where length >= ALL(select length from movie)


-- Групиране по израз:
-- За всяка година да се изведе колко филмови
-- звезди са родени
select year(birthdate), count(*)
from moviestar
group by year(birthdate);

-- доказателство за реда, в който се изпълняват клаузите:
-- следната заявка е грешна:
select title as movietitle
from movie
where movietitle like 'Star %';
-- movietitle не е дефинирано

-- за всяка филмова звезда - броят на филмите, в които
-- се е снимала
-- ако за дадена звезда не знаем какви филми има,
-- за нея да се изведе 0
--MOE
select * from STARSIN

select STARNAME,COUNT(*)
FROM STARSIN
GROUP BY (STARNAME)
--OFICIALNO
select name, count(starname) as moviesCount
from moviestar
left join starsin on name = starname
group by name;

-- още за null:
select *
from movie
where length = (select max(length) from movie);
-- горното намира най-дългите филми измежду тези,
-- за които дължината е известна
select *
from movie
where length >= all (select length from movie);
-- горното не връща нищо, ако има филми с неизвестна
-- дължина

-- не може avg(count( и др. подобни, да не се смесват!
-- пример: да се изведе средният брой филми, в
-- които са се снимали актьорите
-- грешно:
-- select avg(count(...)) from ...
-- вярно:
select avg(moviescount)
-- или по-добре: select avg(convert(real, moviescount))
from (select count(movietitle) as moviescount
    from moviestar
    left join starsin on name = starname
    group by name) stat;

-- having може само ако има преди него group by

-- често срещана грешка:
select *
from movie
where length = max(select length from movie);

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

use pc;
-- 1.1.
select avg(speed)
from pc;

-- 1.2.
select maker, avg(screen)
from laptop
join product on laptop.model = product.model
group by maker;

-- 1.3.
select avg(speed)
from laptop
where price > 1000;

--TOVA E NA CENITE > 1000 PO OTDELNO

select PRICE,AVG(SPEED)
FROM LAPTOP
WHERE PRICE > 1000
GROUP BY PRICE

--1.4
select AVG(PRICE)
FROM PC
JOIN PRODUCT P ON PC.MODEL=P.MODEL
WHERE MAKER='A'

-- 1.5.
select avg(price)
from
(
    select price
    from product p join pc
    on p.model=pc.model
    where maker='B'

    union all

    select price
    from product p join laptop l
    on p.model=l.model
    where maker='B'
) AllPrices;

-- 1.6.
SELECT * FROM PC

select speed, avg(price)
from pc
group by speed;

-- 1.7.
select maker
from product
where type='PC'
group by maker
having count(*) >= 3;

-- 1.8.

SELECT * FROM PRODUCT P
JOIN PC ON P.MODEL=PC.MODEL
-
select distinct maker
from product
join pc on product.model = pc.model
where price = (select max(price) from pc);

-- 1.9.
select speed, avg(price)
from pc
where speed > 800
group by speed;
-- тук може и с having, но няма да е по-яко

-- 1.10.
select avg(hd)
from pc
join product p on p.model=pc.model
where maker in
    (select maker
    from product
    where type='Printer');

-- 1.11.
select screen, max(price) - min(price)
from laptop
group by screen;

use ships;
-- 2.1.
select * from classes

select count(*)
from classes;

--vseki klas kolko koraba ima
select * from ships
order by class

select class,count(*)
from  ships
group by class

-- 2.2.
select *
from classes c
join ships s on c.class = s.class;

select avg(numguns)
from classes c
join ships s on c.class = s.class;

-- 2.3.
select * from ships
select class
 from ships
group by class

select class, min(launched) as FirstYear,
            max(launched) as LastYear
from ships
group by class;

-- 2.4. (не се включват класове без
-- потънали кораби)
select *
from ships s
join outcomes o on s.name=o.ship
where o.result='sunk'
order by class

select class, count(*)
from ships s
join outcomes o on s.name=o.ship
where o.result='sunk'
group by class;

-- 2.5.
select * from ships order by class

select class
        from ships
        group by class
        having count(*)>4

select class, count (name)
from ships s
join outcomes o on s.name=o.ship
where class in
        (select class
        from ships
        group by class
        having count(*)>4)
group by class;

select class, count (name)
from ships s
join outcomes o on s.name=o.ship
where result='sunk' and class in
        (select class
        from ships
        group by class
        having count(*)>4)
group by class;

--2.6
select * from CLASSES

select country, avg(displacement)
from CLASSES
group by COUNTRY

-------------------------
-- Задачи - 2

use movies;
-- 1. За всеки актьор/актриса изведете броя на
-- различните студиа, с които са записвали филми.
select starname, count(distinct studioname)
from starsin
join movie on movietitle=title and movieyear=year
group by starname;

-- 2. За всеки актьор/актриса изведете броя на
-- различните студиа, с които са записвали филми,
-- включително и за тези, за които нямаме информация в
-- кои филми са играли.
select name, count(distinct studioname)
from movie
join starsin on movietitle=title and movieyear=year
right join moviestar on name=starname
group by name;

-- OR:
select name, count(distinct studioname)
from moviestar
left join starsin on name=starname
left join movie on movietitle=title and movieyear=year
group by name;

-- 3. Изведете имената на актьорите, участвали в поне
-- три филма след 1990 г.
select starname
from starsin
where movieyear>1990
group by starname
having count(*)>=3;

use pc;
-- 4. Да се изведат различните модели компютри,
-- подредени по цена на най-скъпия конкретен компютър
-- от даден модел.
select * from pc order by model

select model,max(price)
from pc
group by model
order by max(price);


-- 5. Изведете броя на потъналите американски кораби за
-- всяка проведена битка с поне един потънал американски
-- кораб.
select *
from outcomes
join ships s on name=ship
join classes c on s.class=c.class
where country='USA'

use ships;
--OFICIALNO
select battle, count(*)
from outcomes
join ships on ship = name
join classes on ships.class = classes.class
where result = 'sunk' and country = 'USA'
group by battle;

-- 6. Битките, в които са участвали поне 3 кораба на
-- една и съща страна.
select distinct battle
from outcomes
join ships on ship = name
join classes on ships.class = classes.class
group by battle, country
having count(*) >= 3;
-- distinct, понеже в една битка може да има примерно 4 кораба от САЩ и 4 кораба от Япония

-- За всеки клас да се изведе името му, държавата и първата година,
-- в която е пуснат кораб от този клас
select c.class, country, min(launched) as FirstYear
from classes c
join ships s on c.class = s.class
group by c.class, country;
-- или:
-- select c.class, min(country) as country, min(launched) as firstYear
-- from classes c
-- join ships on c.class = ships.class
-- group by c.class;

-- За всеки клас с кораби - името на класа, името на произволен кораб и брой кораби
select class, min(name), count(*)
from ships
group by class;

-- 7. Имената на класовете, за които няма кораб, пуснат
-- на вода след 1921 г., но имат пуснат поне един кораб.
select * from ships order by class

SELECT class
FROM ships
GROUP BY class
HAVING MAX(launched) <= 1921;

-- 8. За всеки кораб да се изведе броят на битките, в които е бил увреден.
-- Ако корабът не е участвал в битки или пък никога не е бил
-- увреждан, в резултата да се вписва 0.

-- грешно (за справка - упражнение 4):
select name, count(battle)
from ships
left join outcomes on name = ship
where result = 'damaged' or result is null
group by name;
-- губим корабите, които са участвали в битки, но не са били damaged

-- вярно:
select name, count(battle)
from ships
left join outcomes on name = ship and result = 'damaged'
group by name;

-- или (вярно):

... left join (select * from outcomes where result = 'damaged') d ...

-- или (дава възможности за много сложни заявки):

select name, (select count(*)
    from outcomes o
    where result = 'damaged'
        and o.ship = s.name)
from ships s;

-- 8.1. За всяка държава да се изведе броят на корабите и броят на
-- потъналите кораби.
-- Всяка от бройките може да бъде и нула.
select country, count(ships.name), count(outcomes.ship)
from classes
left join ships on classes.class = ships.class
left join outcomes on name = ship and result = 'sunk'
group by country;
-- понеже един кораб потъва най-много веднъж, няма нужда от count(distinct name)

-- 8.2. За всяка държава да се изведе броят на повредените кораби и броят на потъналите кораби.
-- Всяка от бройките може да бъде и нула.
select country, count(distinct damaged.ship) as damaged, count(distinct sunk.ship) as sunk
from classes
left join ships on classes.class = ships.class
left join outcomes damaged on name = damaged.ship and damaged.result = 'damaged'
left join outcomes sunk on name = sunk.ship and sunk.result = 'sunk'
group by country;
-- distinct е нужен, понеже заради декартовите произведения при join-овете един запис може да се дублира...
-- напр. ако кораб е бил два пъти damaged и веднъж sunk, ще има два реда в sunk.*, в които ще се споменава същият кораб

-- по-добре:
select country, (select count(distinct name)
                from classes
                join ships on classes.class = ships.class
                join outcomes on ship = name
                where result = 'damaged'
                    and country = c.country) as damaged,
                (select count(*)
                from classes
                join ships on classes.class = ships.class
                join outcomes on ship = name
                where result = 'sunk'
                    and country = c.country) as sunk
from classes c
group by country;

-- най-добре - с CASE (не е нужно да се учи за контролното)

-- 9. Намерете за всеки клас с поне 3 кораба броя на корабите от този клас,
-- които са с резултат ok.
select class, count(distinct ship) -- повторения има, ако даден кораб е бил ok в няколко битки
from ships
left join outcomes on name = ship and result = 'ok'
group by class
having count(distinct name) >= 3; -- повторения има, ако даден кораб е бил ok в няколко битки

use movies;
-- За всяка филмова звезда да се изведе името, рождената дата и с кое
-- студио е записвала най-много филми. (Ако има две студиа с еднакъв
-- брой филми, да се изведе кое да е от тях)
select name, birthdate, (select top 1 studioname
                         from starsin
                         join movie on movietitle = title and movieyear = year
                         where starname = name
                         group by studioname
                         order by count(*) desc) studioName
from moviestar;

use pc;
-- Намерете за всички производители на поне 2 лазерни
-- принтера броя на произвежданите от тях PC-та (конкретни конфигурации), евентуално 0.
select maker, count(pc.code)
from product p
left join pc on p.model = pc.model
where maker in (select maker
                from product
                join printer on product.model = printer.model
                where printer.type = 'Laser' -- в Product също има колона type
                group by maker
                having count(*) >= 2)
group by maker;
-- или в having:
select maker, count(pc.code)
from product p
left join pc on p.model = pc.model
group by maker
having maker in (select maker
                from product
                join printer on product.model = printer.model
                where printer.type = 'Laser' -- в Product също има колона type
                group by maker
                having count(*) >= 2);
-- OR
select maker, (select count(*)
            from product p1
            join pc on p1.model = pc.model
                and p1.maker = p.maker)
from product p
join printer on p.model = printer.model
where printer.type = 'Laser'
group by maker
having count(*) >= 2;

-- да се изведат всички производители,
-- за които средната цена на произведените компютри
-- е по-ниска от средната цена на техните лаптопи:
select maker
from product p
join pc on p.model = pc.model
group by maker
having avg(price) < (select avg(price)
                     from product
                     join laptop on product.model = laptop.model
                     where maker = p.maker); -- корелативна подзаявка в having

-- Един модел компютри може да се предлага в няколко конфигурации
-- с евентуално различна цена. Да се изведат тези модели компютри,
-- чиято средна цена (на различните му конфигурации) е по-ниска
-- от най-евтиния лаптоп, произвеждан от същия производител.
select pc.model
from pc
join product p on pc.model = p.model
group by pc.model, p.maker -- трябва да групираме и по maker, понеже
        -- го подаваме на корелативна подзаявка в having
having avg(price) < (select min(price)
                    from laptop
                    join product t
                    on laptop.model = t.model
                    where t.maker = p.maker);
-- или:
select pc.model
from pc join product p on pc.model=p.model
group by pc.model
having avg(price) < (select min(price)
        from laptop
        join product t
        on laptop.model = t.model
        where t.maker = min(p.maker)); -- този min не се изпълнява в where,
        -- а в having и подзаявката получава стойността, върната от min




-----------