SQL-ex practice.txt

/*
Найдите номер модели, скорость и размер жесткого диска для всех ПК стоимостью менее 500 дол. Вывести: model, speed и hd
*/

SELECT 
  product.model, 
  speed, 
  hd 
FROM 
  product 
  LEFT JOIN pc ON product.model = pc.model 
WHERE 
  price < 500;

/*
Найдите номер модели, объем памяти и размеры экранов ПК-блокнотов, цена которых превышает 1000 дол.
*/

SELECT 
  model, 
  ram, 
  screen 
FROM 
  laptop 
WHERE 
  price > 1000;


/*
Найдите все записи таблицы Printer для цветных принтеров.
*/

SELECT 
  * 
FROM 
  printer 
WHERE 
  color IN ('y');


/*
Найдите номер модели, скорость и размер жесткого диска ПК, имеющих 12x или 24x CD и цену менее 600 дол.
*/

SELECT 
  model, 
  speed, 
  hd 
FROM 
  pc 
WHERE 
  cd IN ('12x', '24x') 
  AND price < 600;



/*
Найдите номера моделей и цены всех имеющихся в продаже продуктов (любого типа) производителя B (латинская буква).
*/

SELECT 
  DISTINCT product.model, 
  laptop.price 
FROM 
  product 
  INNER JOIN laptop ON product.model = laptop.model 
WHERE 
  maker IN ('b') 

UNION 

SELECT 
  DISTINCT product.model, 
  pc.price 
FROM 
  product 
  INNER JOIN pc ON product.model = pc.model 
WHERE 
  maker IN ('b') 

UNION 

SELECT 
  DISTINCT product.model, 
  printer.price 
FROM 
  product 
  INNER JOIN printer ON product.model = printer.model 
WHERE 
  maker IN ('b');



/*
Найдите производителей ПК с процессором не менее 450 Мгц. Вывести: Maker
*/

SELECT 
  DISTINCT maker 
FROM 
  pc 
  INNER JOIN product ON pc.model = product.model 
WHERE 
  speed >= 450;



/*
Найдите модели принтеров, имеющих самую высокую цену. Вывести: model, price
*/

SELECT 
  model, 
  price 
FROM 
  printer 
WHERE 
  price IN (
    SELECT 
      max(price) 
    FROM 
      printer
  );


/*
Найдите среднюю скорость ПК.
*/

SELECT 
  AVG(speed) 
FROM 
  pc;

/*
Найдите среднюю скорость ПК-блокнотов, цена которых превышает 1000 дол.
*/

SELECT 
  AVG(speed) 
FROM 
  laptop 
WHERE 
  price > 1000;


/*
Найдите среднюю скорость ПК, выпущенных производителем A.
*/

SELECT 
  AVG(speed) 
FROM 
  pc 
  INNER JOIN product ON pc.model = product.model 
WHERE 
  maker IN ('A');


/*
Найдите класс, имя и страну для кораблей из таблицы Ships, имеющих не менее 10 орудий.
*/

SELECT 
  Classes.class, 
  Ships.name, 
  Classes.country 
FROM 
  Classes 
  INNER JOIN Ships ON Classes.class = Ships.class 
WHERE 
  numGuns >= 10;


/*
Найдите размеры жестких дисков, совпадающих у двух и более PC. Вывести: HD
*/

SELECT 
  hd 
FROM 
  pc 
GROUP BY 
  hd 
HAVING 
  COUNT(model) >= 2;


/*
Найдите пары моделей PC, имеющих одинаковые скорость и RAM. В результате каждая пара указывается только один раз, т.е. (i,j), но не (j,i), Порядок вывода: модель с большим номером, модель с меньшим номером, скорость и RAM.
*/

SELECT 
  DISTINCT p1.model, 
  p2.model, 
  p1.speed, 
  p1.ram 
FROM 
  pc p1, 
  pc p2 
WHERE 
  p1.speed = p2.speed 
  AND p1.ram = p2.ram 
  AND p1.model > p2.model;


/*
Найдите модели ПК-блокнотов, скорость которых меньше скорости каждого из ПК.
Вывести: type, model, speed
*/

SELECT 
  DISTINCT product.type, 
  laptop.model, 
  laptop.speed 
FROM 
  laptop 
  INNER JOIN product ON laptop.model = product.model 
WHERE 
  speed < (
    SELECT 
      MIN(speed) 
    FROM 
      pc
  );


/*
Найдите производителей самых дешевых цветных принтеров. Вывести: maker, price
*/

SELECT 
  DISTINCT maker, 
  price 
FROM 
  product 
  JOIN printer ON product.model = printer.model 
WHERE 
  price = (
    SELECT 
      MIN(price) 
    FROM 
      printer 
    WHERE 
      color = 'y'
  ) 
  AND color = 'y';


/*
Для каждого производителя, имеющего модели в таблице Laptop, найдите средний размер экрана выпускаемых им ПК-блокнотов.
Вывести: maker, средний размер экрана.
*/

SELECT 
  maker, 
  AVG(screen) 
FROM 
  laptop 
  LEFT JOIN product ON laptop.model = product.model 
GROUP BY 
  maker;

/*
Найдите производителей, выпускающих по меньшей мере три различных модели ПК. Вывести: Maker, число моделей ПК.
*/

SELECT 
  maker, 
  COUNT(model) 
FROM 
  product 
WHERE 
  type = 'pc' 
GROUP BY 
  maker 
HAVING 
  COUNT(model)>= 3;


/*
Найдите максимальную цену ПК, выпускаемых каждым производителем, у которого есть модели в таблице PC.
Вывести: maker, максимальная цена.
*/

SELECT 
  maker, 
  MAX(price) AS amx_price 
FROM 
  pc 
  INNER JOIN product ON pc.model = product.model 
GROUP BY 
  maker 
ORDER BY 
  MAX(price) DESC;


/*
Для каждого значения скорости ПК, превышающего 600 МГц, определите среднюю цену ПК с такой же скоростью. Вывести: speed, средняя цена.
*/

SELECT 
  speed, 
  AVG(price) AS avg_price 
FROM 
  pc 
WHERE 
  speed > 600 
GROUP BY 
  speed;


/*
Найдите производителей, которые производили бы как ПК
со скоростью не менее 750 МГц, так и ПК-блокноты со скоростью не менее 750 МГц.
Вывести: Maker
*/

WITH pc_makers AS(
  SELECT 
    DISTINCT maker 
  FROM 
    product 
    INNER JOIN pc ON pc.model = product.model 
  WHERE 
    speed >= 750
), 
laptop_makers AS(
  SELECT 
    DISTINCT maker 
  FROM 
    product 
    INNER JOIN laptop ON product.model = laptop.model 
  WHERE 
    speed >= 750
) 

SELECT 
  pm.maker 
FROM 
  laptop_makers lm 
  INNER JOIN pc_makers pm ON lm.maker = pm.maker;


/*
Перечислите номера моделей любых типов, имеющих самую высокую цену по всей имеющейся в базе данных продукции.
*/

SELECT 
  model 
FROM 
  (
    SELECT 
      model, 
      price 
    FROM 
      pc 
    UNION 
    SELECT 
      model, 
      price 
    FROM 
      Laptop 
    UNION 
    SELECT 
      model, 
      price 
    FROM 
      Printer
  ) t1 
WHERE 
  price = (
    SELECT 
      MAX(price) 
    FROM 
      (
        SELECT 
          price 
        FROM 
          pc 
        UNION 
        SELECT 
          price 
        FROM 
          Laptop 
        UNION 
        SELECT 
          price 
        FROM 
          Printer
      ) t2
  );


/*
Найдите среднюю цену ПК и ПК-блокнотов, выпущенных производителем A (латинская буква). Вывести: одна общая средняя цена.
*/

SELECT 
  AVG(price) 
FROM 
  (
    SELECT 
      code, 
      price, 
      pc.model, 
      ram, 
      hd 
    FROM 
      pc 
    WHERE 
      model IN (
        SELECT 
          model 
        FROM 
          product 
        WHERE 
          maker = 'a'
      ) 
    UNION 
    SELECT 
      code, 
      price, 
      laptop.model, 
      ram, 
      hd 
    FROM 
      laptop 
    WHERE 
      model IN (
        SELECT 
          model 
        FROM 
          product 
        WHERE 
          maker = 'a'
      )
  ) a;



/*
Найдите средний размер диска ПК каждого из тех производителей, которые выпускают и принтеры. Вывести: maker, средний размер HD.
*/

SELECT 
  maker, 
  AVG(hd) 
FROM 
  pc 
  INNER JOIN product ON pc.model = product.model 
WHERE 
  maker IN(
    SELECT 
      DISTINCT maker 
    FROM 
      product 
    WHERE 
      type = 'printer'
  ) 
GROUP BY 
  maker;



/*
Используя таблицу Product, определить количество производителей, выпускающих по одной модели.
*/

SELECT 
  COUNT(maker) 
FROM 
  product 
WHERE 
  maker IN(
    SELECT 
      maker 
    FROM 
      product 
    GROUP BY 
      maker 
    HAVING 
      COUNT(model) = 1
  );



/*

*/