12.04.2011 - Exercise1 - Unit5

12.04.2011-Exercise1-Unit5.sql

SET SCHEMA MOVIES;

--Да се напише заявка, която извежда имената и годините на всички филми, които са по-дълги
--от 120 минути и са снимани преди 1990 г. Ако дължината на филма е неизвестна (NULL),
--името и годината на този филм също да се изведат.

SELECT TITLE, YEAR, LENGTH
  FROM MOVIE
  WHERE LENGTH > 120 OR LENGTH IS NULL AND YEAR < 1990;

--Да се напише заявка, която извежда имената на най-младия актьор (мъж).
--Забележка: полът на актьора може да бъде 'M' или 'F'.

SELECT NAME
  FROM MOVIESTAR
  WHERE BIRTHDATE >= ALL (SELECT MOVIESTAR.BIRTHDATE
                    FROM MOVIESTAR
                    WHERE MOVIESTAR.GENDER = 'M') AND GENDER = 'M';

SET SCHEMA SHIPS;
--Да се напише заявка, която извежда държавата и броят на потъналите кораби за тази
--държава. Държави, които нямат кораби или имат кораб, но той не е участвал в битка,
--също да бъдат изведени.
SELECT DISTINCT COUNTRY, (SELECT COUNT(1)
                            FROM
                                 CLASSES JOIN SHIPS ON CLASSES.CLASS = SHIPS.CLASS JOIN OUTCOMES ON OUTCOMES.SHIP = SHIPS.NAME
                            WHERE OUTCOMES.RESULT = 'sunk' AND CLASSES.COUNTRY = C1.COUNTRY)
  FROM CLASSES AS C1;

SET SCHEMA PC;

--Да се изведат различните модели компютри, чиято цена е по-ниска от най-евтиния лаптоп,
--произвеждан от същия производител.

SELECT DISTINCT PR1.MODEL
  FROM PRODUCT AS PR1 JOIN PC ON PR1.MODEL = PC.MODEL
  WHERE PC.PRICE <= ALL (SELECT PC.PRICE
                           FROM LAPTOP JOIN PRODUCT ON LAPTOP.MODEL = PRODUCT.MODEL
                           WHERE PR1.MODEL = LAPTOP.MOD0EL);
                           


--Един модел компютри може да се предлага в няколко разновидности с евентуално различна
--цена. Да се изведат тези модели компютри, чиято средна цена (на различните му
--разновидности) е по-ниска от най-евтиния лаптоп.
                           

SELECT MODEL
  FROM PC AS PC1
  GROUP BY MODEL
  HAVING AVG(PRICE) < ALL (SELECT LAPTOP.PRICE
                             FROM LAPTOP
                             WHERE LAPTOP.PRICE <= ALL (
                                       SELECT LAPTOP.PRICE
                                         FROM LAPTOP));
                                         
                                         
SELECT DISTINCT MODEL
  FROM PC AS PC1
  WHERE (SELECT AVG(PRICE)
           FROM PC
           WHERE MODEL = PC1.MODEL) < ALL (
               SELECT PRICE
                 FROM LAPTOP
                 WHERE PRICE <= ALL (SELECT LAPTOP.PRICE
                                       FROM LAPTOP));
                                       
--13.04.2011--

SET SCHEMA MOVIES;

--Да се напише заявка, която извежда имената и пола на всички актьори, чието име
--започва с 'J' и са родени след 1948 година. Резултатът да бъде подреден по име
--в намаляващ ред.
SELECT NAME, GENDER
  FROM MOVIESTAR
  WHERE NAME LIKE 'J%' AND BIRTHDATE > '1948-1-1';

SET SCHEMA SHIPS;

--Да се напише заявка, която извежда име и държава на всички кораби,
--които никога не са потъвали в битка (може и да не са участвали).
SELECT S1.NAME, CLASSES.COUNTRY
  FROM SHIPS AS S1 JOIN CLASSES ON S1.CLASS = CLASSES.CLASS
  WHERE (SELECT COUNT(1)
   FROM OUTCOMES
   WHERE SHIP = S1.NAME AND RESULT <> 'sunk') = 0;

SET SCHEMA PC;

--Да се напише заявка, която извежда всички модели лаптопи, за които се предлагат
--както разновидности с 15" екран, така и с 11" екран.
SELECT MODEL
  FROM LAPTOP AS L1
  WHERE (SELECT COUNT(1)
   FROM LAPTOP
   WHERE L1.MODEL = MODEL AND SCREEN = 15) <> 0 AND SCREEN = 11;

SET SCHEMA SHIPS;

--Да се напише заявка, която извежда имената на всички кораби без повторения, които са
--участвали в поне една битка и чиито имена започват с буквите C или K.
SELECT SHIP
  FROM OUTCOMES
  WHERE SHIP LIKE 'C%' OR SHIP LIKE 'K%';

SET SCHEMA MOVIES;

--Да се напише заявка, която извежда името на филма, годината и броят на актьорите участвали
--в този филм, за тези филми с повече от 2-ма актьори.
SELECT *
  FROM
       MOVIE AS M1 FULL OUTER JOIN STARSIN ON STARSIN.MOVIETITLE = M1.TITLE AND STARSIN.MOVIEYEAR = M1.YEAR
  WHERE (SELECT COUNT(1)
   FROM STARSIN
   WHERE MOVIETITLE = M1.TITLE AND MOVIEYEAR = M1.YEAR) > 2;

SET SCHEMA PC;

--Да се напише заявка, която извежда за всеки компютър: код на продукта (code); производител;
--брой компютри, които имат цена, по-голяма или равна на неговата.
SELECT PRODUCT.MAKER, T1.CODE
FROM PRODUCT
JOIN PC AS PC1 ON PC1.MODEL = PRODUCT.MODEL
JOIN
(SELECT P1.CODE, COUNT(P2.CODE)
	FROM PC AS P1
	JOIN PC AS P2 ON P1.CODE <> P2.CODE
	GROUP BY P1.CODE) AS T1 ON PC1.CODE = T1.CODE;

--Един модел компютри може да се предлага в няколко разновидности с евентуално различна цена.
--Да се изведат тези модели компютри, чиято сума от цените (на различните му разновидности)
--е по-ниска от сумата на цените на лаптопите.
SELECT MODEL
  FROM PC
  GROUP BY MODEL
  HAVING SUM(PRICE) < (SELECT SUM(PRICE)
                FROM LAPTOP);