ex 2

SELECT * FROM MOVIE;
SELECT * FROM STUDIO;

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
WHERE INCOLOR = 'y';

SELECT m1.title
FROM MOVIE m1, Movie m2
WHERE m2.TITLE = 'Star Wars'
    AND m1.LENGTH > m2.LENGTH;



SELECT name, title
FROM MOVIESTAR
JOIN STARSIN ON name = STARNAME
JOIN MOVIE ON MOVIETITLE = TITLE AND MOVIEYEAR = YEAR
WHERE GENDER = 'F' AND STUDIONAME = 'MGM';


(SELECT title, YEAR
FROM MOVIE
WHERE INCOLOR = 'y')
UNION
(SELECT movietitle, MOVIEYEAR
FROM STARSIN
WHERE STARNAME = 'Harrison Ford');


(SELECT name, address
FROM MovieStar
WHERE gender = 'F')
INTERSECT
(SELECT name, address
FROM MovieExec
WHERE networth > 10000000);


(SELECT name, address
FROM MovieStar
WHERE gender = 'F')
EXCEPT
(SELECT name, address
FROM MovieExec
WHERE networth > 10000000);


(SELECT name, address
FROM MovieStar)
EXCEPT
(SELECT name, address
FROM MovieExec)
ORDER BY address;

-- Own Movies
-- Напишете заявка, която извежда имената на актрисите, участвали в Terms of Endearment

SELECT DISTINCT TITLE, STARSIN.STARNAME, MOVIESTAR.GENDER
FROM MOVIE
JOIN STARSIN
ON STARSIN.STARNAME = STARNAME
JOIN MOVIESTAR
ON MOVIESTAR.GENDER = GENDER
WHERE TITLE = 'Terms of Endearment' AND MOVIESTAR.GENDER = 'F';

-- Напишете заявка, която извежда имената на филмовите звезди, участвали във филми на студио MGM през 1995 г.

SELECT DISTINCT TITLE, STUDIO.NAME, STARSIN.STARNAME, YEAR
FROM MOVIE
JOIN STUDIO
ON STUDIO.NAME = STUDIONAME
JOIN STARSIN
ON STARSIN.STARNAME = STARNAME
WHERE STUDIO.NAME = 'MGM' AND YEAR = 1995;

-- laptop/pc stuff

SELECT maker, laptop.model, price
FROM laptop
JOIN product
ON Laptop.model = product.model
WHERE screen = 15;

-- 1. Напишете заявка, която извежда производителя и честотата на процесора на лаптопите с размер на харддиска поне 9 GB.

-- 2. Напишете заявка, която извежда номер на модел и цена на всички продукти, произведени от производител с име ‘B’.
-- Сортирайте резултата така, че първо да се изведат най-скъпите продукти.

SELECT product.model, price
FROM product
JOIN pc ON product.model = pc.model
WHERE maker = 'B'

UNION

SELECT product.model, price
FROM product
JOIN printer ON product.model = printer.model
WHERE maker = 'B'

ORDER BY price DESC;

-- 3. Напишете заявка, която извежда размерите на тези харддискове, които се предлагат в поне два компютъра.

SELECT DISTINCT p1.hd
FROM pc p1
JOIN pc p2 ON p1.hd = p2.hd
WHERE p1.code <> p2.code;


-- 4. Напишете заявка, която извежда всички двойки модели на компютри, които имат еднаква честота на процесора и памет.
-- Двойките трябва да се показват само по веднъж, например ако вече е изведена двойката (i, j), не трябва да се извежда (j, i)

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;


-- 5. Напишете заявка, която извежда производителите на поне два различни компютъра с честота на процесора поне 1000 MHz.

SELECT DISTINCT p1.maker
FROM pc pc1
JOIN product p1 ON pc1.model = p1.model
JOIN product p2 ON p1.maker = p2.maker
JOIN pc pc2 ON p2.model = pc2.model
WHERE pc1.speed >= 500 AND pc2.speed >= 500;

-- Ships tasks

SELECT battle
FROM Outcomes
WHERE RESULT = 'sunk';


SELECT DISTINCT battle
FROM Outcomes
WHERE RESULT = 'sunk';

-- Own

SELECT NAME, displacement
FROM SHIPS
JOIN CLASSES
ON displacement = DISPLACEMENT
WHERE displacement > 35000;


SELECT SHIPS.NAME, TYPE, numguns, BATTLES.NAME
FROM SHIPS
JOIN CLASSES
ON TYPE = TYPE AND numguns = NUMGUNS
JOIN BATTLES
ON BATTLES.NAME = BATTLES.NAME
WHERE BATTLES.NAME = 'Guadalcanal';

-- 3.3 wrong

SELECT SHIPS.NAME, TYPE, COUNTRY
FROM SHIPS
JOIN CLASSES
ON TYPE = TYPE AND COUNTRY = COUNTRY
WHERE TYPE = 'bb'

UNION

SELECT SHIPS.NAME, TYPE, COUNTRY
FROM SHIPS
JOIN CLASSES
ON TYPE = TYPE AND COUNTRY = COUNTRY
WHERE TYPE = 'bc'

-- right 3.3
SELECT COUNTRY
FROM CLASSES
WHERE TYPE = 'bb'

INTERSECT

SELECT COUNTRY
FROM CLASSES
WHERE TYPE = 'bc'

-- 3.4

SELECT OUTCOMES.SHIP
FROM OUTCOMES, OUTCOMES AS OUTCOMES2
WHERE OUTCOMES.RESULT = 'damaged' AND OUTCOMES.SHIP = OUTCOMES2.SHIP AND OUTCOMES.BATTLE <> OUTCOMES2.BATTLE;

-- another 3.4

SELECT DISTINCT o1.ship
FROM OUTCOMES O1
JOIN BATTLES B1 ON O1.BATTLE = B1.NAME
JOIN OUTCOMES O2 ON O1.SHIP = O2.SHIP
JOIN BATTLES B2 ON O2.BATTLE = B2.NAME
WHERE B1.DATE < B2.DATE AND O1.RESULT = 'damaged'


SELECT * FROM OUTCOMES
ORDER BY SHIP;