06.04.2011 - Exercise1 - Unit5

06.04.2011-Exercise1-Unit5.sql

--natural join - връзва две таблици по атрибутите им с едно и също име
--inner join == join - връзва ги по общия атрибут, който се задава експлицитно
--outer join - прави същото като inner + (left, right и full)
--при left добавя всички атрибути на първата, там където може да
--направи връзка между атрибутите - ОК, където не, вдясно слага null
--full -> комбинация между 2-те

SET SCHEMA MOVIES;

--Напишете заявка, която извежда името на продуцента и имената на филмите, 
--продуцирани от продуцента на ‘Star Wars’
SELECT MOVIE.TITLE, MOVIEEXEC.NAME
  FROM MOVIE JOIN MOVIEEXEC ON MOVIEEXEC.CERT# = MOVIE.PRODUCERC#
  WHERE MOVIEEXEC.CERT# IN ((SELECT PRODUCERC#
                       FROM MOVIE
                       WHERE TITLE = 'Star Wars'));

--Напишете заявка, която извежда имената на продуцентите на филмите на ‘Harrison Ford’
SELECT MOVIE.TITLE, MOVIEEXEC.NAME
  FROM MOVIE JOIN MOVIEEXEC ON MOVIE.PRODUCERC# = MOVIEEXEC.CERT#
  WHERE MOVIE.TITLE IN ((SELECT MOVIETITLE
                   FROM STARSIN
                   WHERE STARNAME = 'Harrison Ford'));

--Напишете заявка, която извежда името на студиото и имената на актьорите  участвали
--във филми произведени от това студио, подредени по име на студио.
SELECT MOVIE.STUDIONAME, STARSIN.STARNAME
  FROM MOVIE JOIN STARSIN ON STARSIN.MOVIETITLE = MOVIE.TITLE;

--Напишете заявка, която извежда имената на актьора (актьорите) участвали във  филми
--на най-голяма стойност
SELECT STARSIN.STARNAME, M.TITLE
  FROM
       STARSIN JOIN (
      SELECT TITLE, YEAR
        FROM MOVIE
        WHERE PRODUCERC# IN ((SELECT CERT#
                  FROM MOVIEEXEC
                  WHERE NETWORTH >= ALL (SELECT NETWORTH
                                           FROM MOVIEEXEC)))) AS M ON M.TITLE = STARSIN.MOVIETITLE AND M.YEAR = STARSIN.MOVIEYEAR;

--Напишете заявка, която извежда имената на актьорите не участвали в нито
--един  филм. (Използвайте съединение!)
SELECT DISTINCT MOVIESTAR.NAME
  FROM MOVIESTAR LEFT OUTER JOIN STARSIN ON STARSIN.STARNAME = MOVIESTAR.NAME
  WHERE MOVIETITLE IS NULL;

SET SCHEMA PC;

--Напишете заявка, която извежда производител, модел и тип на продукт за тези  производители, за които съответния продукт
--не се продава (няма го в таблиците  PC, лаптоп или принтер)
SELECT PRODUCT.MODEL, PRODUCT.MAKER, PRODUCT.TYPE
  FROM PRODUCT LEFT OUTER JOIN PRINTER ON PRODUCT.MODEL = PRINTER.MODEL
  WHERE PRINTER.CODE IS NULL
UNION
SELECT PRODUCT.MODEL, PRODUCT.MAKER, PRODUCT.TYPE
  FROM PRODUCT LEFT OUTER JOIN PC ON PRODUCT.MODEL = PC.MODEL
  WHERE PC.CODE IS NULL
UNION
SELECT PRODUCT.MODEL, PRODUCT.MAKER, PRODUCT.TYPE
  FROM PRODUCT LEFT OUTER JOIN LAPTOP ON PRODUCT.MODEL = LAPTOP.MODEL
  WHERE LAPTOP.CODE IS NULL;

SET SCHEMA SHIPS;

--Напишете заявка, която извежда цялата налична информация за всеки кораб, включително
--и данните за неговия клас. В резултата не трябва да се включват  тези класове, които нямат кораби.
SELECT *
  FROM SHIPS LEFT OUTER JOIN CLASSES ON CLASSES.CLASS = SHIPS.CLASS;

--Повторете горната заявка като този път включите в резултата и класовете, които нямат
--кораби, но съществуват кораби със същото име като тяхното.
SELECT *
  FROM SHIPS LEFT OUTER JOIN CLASSES ON CLASSES.CLASS = SHIPS.CLASS
  UNION
SELECT *
  FROM SHIPS RIGHT  JOIN CLASSES ON CLASSES.CLASS = SHIPS.NAME;

--За всяка страна изведете имената на корабите, които никога не са участвали в битка.
SELECT S1.NAME
  FROM CLASSES
  JOIN SHIPS AS S1 ON CLASSES.CLASS = S1.CLASS
  WHERE (SELECT COUNT(1) FROM OUTCOMES WHERE OUTCOMES.SHIP = S1.NAME) = 0;