05.04.2011 - Exercise1 - Unit4

05.04.2011-Exercise1-Unit4.sql

SET SCHEMA PC;

--Намерете производителите на персонални компютри със скорост поне 700 MHz.
SELECT MAKER
  FROM PRODUCT
  WHERE MODEL IN ((SELECT MODEL
             FROM PC
             WHERE SPEED > 700));

SELECT DISTINCT PRODUCT.MAKER
  FROM PRODUCT JOIN PC ON PC.MODEL = PRODUCT.MODEL
  WHERE PC.SPEED > 700;

SELECT DISTINCT PRODUCT.MAKER
  FROM PC, PRODUCT
  WHERE PC.MODEL = PRODUCT.MODEL AND PC.SPEED > 700;

SELECT MAKER
  FROM PRODUCT
  WHERE EXISTS (SELECT 1
                  FROM PC
                  WHERE SPEED > 700 AND PRODUCT.MODEL = MODEL);

--Намерете принтерите с най-високата цена.
SELECT CODE
  FROM PRINTER
  WHERE PRICE >= ALL (SELECT PRICE
                        FROM PRINTER);

SELECT CODE
  FROM PRINTER
  WHERE PRICE >= (SELECT MAX(PRICE)
            FROM PRINTER);

--Намерете лаптопите, чиято честота е по-ниска от тази на някое PC.
SELECT CODE
  FROM LAPTOP
  WHERE SPEED < ANY (SELECT SPEED
                       FROM PC);

SELECT DISTINCT CODE
  FROM LAPTOP
  WHERE EXISTS (SELECT 1
                  FROM PC
                  WHERE SPEED > LAPTOP.SPEED);

--Намерете производителя на цветни принтери с най-ниска цена.   
SELECT DISTINCT MAKER
  FROM PRODUCT
  WHERE MODEL IN ((SELECT MODEL
             FROM PRINTER
             WHERE COLOR = 'y'
              AND PRICE <= ALL (SELECT PRICE
                          FROM PRINTER
                          WHERE COLOR = 'y')));

SELECT MAKER
  FROM PRODUCT JOIN PRINTER ON PRINTER.MODEL = PRODUCT.MODEL
  WHERE PRINTER.COLOR = 'y'
    AND PRINTER.PRICE <= ALL (
    SELECT PRICE
      FROM PRINTER
      WHERE COLOR = 'y');

SELECT DISTINCT PRODUCT.MAKER
  FROM PRODUCT JOIN PRINTER ON PRINTER.MODEL = PRODUCT.MODEL
  WHERE PRINTER.PRICE = ((SELECT MIN(PRICE)
    FROM PRINTER
    WHERE COLOR = 'y')) AND PRINTER.COLOR = 'y';

SET SCHEMA SHIPS;

--Намерете страните, които произвеждат кораби с най-голям брой оръдия.
SELECT COUNTRY
  FROM CLASSES
  WHERE BORE >= ALL (SELECT BORE
                       FROM CLASSES);

SELECT COUNTRY
  FROM CLASSES
  WHERE BORE IN ((SELECT MAX(BORE)
            FROM CLASSES));

--Намерете класовете кораби, с поне един потънал в битка кораб.
SELECT DISTINCT CLASS
  FROM SHIPS JOIN OUTCOMES ON SHIPS.NAME = OUTCOMES.SHIP
  WHERE OUTCOMES.RESULT = 'sunk';

SELECT DISTINCT CLASS
  FROM SHIPS
  WHERE NAME IN ((SELECT SHIP
            FROM OUTCOMES
            WHERE RESULT = 'sunk'));

--Намерете имената на корабите с 16-инчов калибър на оръдието.
SELECT NAME
  FROM SHIPS
  WHERE CLASS IN ((SELECT CLASS
             FROM CLASSES
             WHERE BORE = 16));

SELECT NAME
  FROM SHIPS JOIN CLASSES ON CLASSES.CLASS = SHIPS.CLASS
  WHERE CLASSES.BORE = 16;

--Намерете битките, в които са участвали кораби от класа Конго.
SELECT BATTLE
  FROM OUTCOMES JOIN SHIPS ON SHIPS.NAME = OUTCOMES.SHIP
  WHERE SHIPS.CLASS = 'Kongo';

SELECT BATTLE
  FROM OUTCOMES
  WHERE SHIP IN ((SELECT NAME
            FROM SHIPS
            WHERE CLASS = 'Kongo'));