SQL 2 EX

union,intersect,except,ALL
SELECT DISTINCT- nachin za mahane na povtoreniq, vrushtat se samo unikalnite redove

select speed from pc
intersect
select speed from laptop

2zad
USE ships
SELECT distinct BATTLE
from OUTCOMES
where RESULT='SUNK'

3zad
SELECT * FROM OUTCOMES WHERE BATTLE='Guadalcanal'
SELECT NAME,CLASS FROM SHIPS
SELECT CLASS,NUMGUNS, DISPLACEMENT FROM CLASSES

SELECT *
FROM CLASSES
 JOIN SHIPS ON CLASSES.CLASS=SHIPS.CLASS
 JOIN OUTCOMES ON SHIPS.NAME=OUTCOMES.SHIP
 WHERE BATTLE='Guadalcanal'

TAKA E :
SELECT SHIPS.NAME, CLASSES.NUMGUNS,CLASSES.DISPLACEMENT
FROM CLASSES
 JOIN SHIPS ON CLASSES.CLASS=SHIPS.CLASS
 JOIN OUTCOMES ON SHIPS.NAME=OUTCOMES.SHIP
 WHERE BATTLE='Guadalcanal'

po-kratko
SELECT s.NAME, c.NUMGUNS,c.DISPLACEMENT
FROM CLASSES AS c
 JOIN SHIPS s ON c.CLASS=s.CLASS
 JOIN OUTCOMES AS o ON s.NAME=o.SHIP
 WHERE BATTLE='Guadalcanal'

//kato excel-ska tablica
 SELECT *
 FROM CLASSES AS c
 FULL OUTER JOIN SHIPS s ON c.CLASS=s.CLASS
 FULL JOIN OUTCOMES AS o ON s.NAME=o.SHIP
 FULL JOIN BATTLES b ON b.NAME=o.BATTLE
 ORDER BY s.NAME


 SELECT *
 FROM CLASSES c
    JOIN SHIPS s ON c.CLASS=s.CLASS
 ORDER BY c.CLASS

ex2.1
USE movies
select distinct starname
from starsin
join movie on movietitle = title
    and movieyear = year
where studioname = 'MGM' and movieyear = 1995;


use pc;

-- Всички процесори,
-- които се срещат както в
-- PC-тата, така и в лаптопите.
-- Първо да се изведат процесорите
-- с най-висока честота:
select speed
from pc
intersect--intersect kudeto takuv speed se sreshta i v 2te ;except- samo v 1oto i go nqma vuv 2roto union-vseki speed koito se sreshta i v dvete
select speed
from laptop
order by speed desc;
-- order by не е към втората заявка, а към целия резултат

-- пример за distinct:
-- имената на всички битки, в които има потънал кораб:
use ships;
select distinct battle -- без distinct ще има повторения
from outcomes
where result = 'sunk';

use movies
--vsichki filmi koito ne sa ot studio fox
select *
from movie, studio
where STUDIONAME!='FOX' and  studioname = name

use movies;
-- Съединение на таблици:

-- Нека искаме да изведем за всеки цветен филм заглавието му, годината,
-- името на студиото и адреса на студиото.
-- започваме отдалече:
select * from movie; -- 10 реда
select * from studio; -- 5 реда
select * from movie, studio; -- 50 реда - декартово произведение

select *
from movie, studio
where studioname = name;

select title, year, studioname, address
from movie, studio
where studioname = name
    and incolor = 'y';

-- горното е напълно вярно, но въпреки това има недостатъци, затова
-- ще разгледаме друго решение:
select title, year, studioname, address
from movie
join studio on studioname = name--vmesto da pishem from movie studio
where incolor = 'y';


-- 1.1.
select name
from starsin
join moviestar on starname = name
where gender = 'M'
    and movietitle = 'Terms of Endearment';

--ili:
select starname
from STARSIN,MOVIESTAR
where  GENDER='M' and MOVIETITLE='Terms of Endearment' and STARNAME=NAME

-- 1.2.
select distinct starname
from starsin
join movie on movietitle = title
    and movieyear = year
where studioname = 'MGM' and movieyear = 1995;

--1.3?? koe e prezidenta

use ships
-- съединение на повече от 2 таблици:
-- 3.2.
from outcomes
join ships on ship = name
join classes on ships.class = classes.class
where battle = 'Guadalcanal';

use movies;
-- 1.4. -- ще дадем сложно решение, следващия път - много по-разбираемо
select m1.title
from movie m1, movie m2 -- поне едно от срещанията на movie трябва да има ново име
                        -- можеше и movie as m1, ...
where m2.title = 'Star Wars'
    and m1.length > m2.length;


--2.1
use pc
select maker, speed
from laptop,product
where hd>=9
--?
select maker, price
from laptop l
join PRODUCT om l.model =product.model
where hd>=9

use pc;
-- 2.2.
select p.model, price
from product p
join laptop on p.model = laptop.model
where maker = 'B'

union

select p.model, price
from product p
join pc on p.model = pc.model
where maker = 'B'

union

select p.model, price
from product p
join printer on p.model = printer.model
where maker = 'B'

order by price desc;

-- 2.3.
select distinct p1.hd
from pc p1
join pc p2 on p1.hd = p2.hd
    and p1.code <> p2.code -- <> = != za da se sreshta v pone 2 komjutura qsqno 20m i
    -- 40 se sreshtat samo v 1 pc sled kato ne se poqvqvat

-- 2.4.
select distinct p1.model, p2.model
from pc p1
join pc p2 on p1.speed = p2.speed and p1.ram = p2.ram
where p1.model < p2.model;


--2.5???
select distinct p1.model, p2.model
from pc p1,pc p2
join product on pc.model=product.model

use pc
select product.maker
from pc
    join product on pc.model=product.model
    where speed>1000


--3.1
use ships
select s.name
from ships s
join classes c on s.class=c.class
where c.displacement>4200
ORDER BY s.CLASS


--3.2
use ships
select ship,c.NUMGUNS,DISPLACEMENT
from outcomes
join ships s on s.name=outcomes.ship
join CLASSES  c on s.class=c.class
where battle='Guadalcanal'


use ships;
-- 3.3.
-- грешно решение:
select distinct country
from classes
where type = 'bb' and type = 'bc';

-- грешно решение:
select distinct country
from classes
where type = 'bb' or type = 'bc';
-- това е "...имат кораби от тип bb или от тип bc или от двата"

-- вярно решение:
select country
from classes
where type = 'bb'

intersect

select country
from classes
where type = 'bc';

-- още едно вярно решение:
select distinct c1.country
from classes c1
join classes c2 on c1.country = c2.country
where c1.type = 'bb' and c2.type = 'bc';

-- 3.4.
select distinct o1.ship,o1.BATTLE,o1.RESULT
from outcomes o1
join battles b1 on o1.battle = b1.name --purvite outcome-i, koito otgovarqt za 1ta bitka
join outcomes o2 on o1.ship = o2.ship --tova e za da stava duma za 1 i sushti korab
join battles b2 on o2.battle = b2.name --2rtie outcome-i koito otgovarqt za 2rata bitka
where o1.result = 'damaged' and b1.date < b2.date;


use movies
select s.*
from moviestar
join starsin s on moviestar.name=s.starname
where GENDER='M'