Схемы и SQL код / Лабораторная №5 ИБД Поиск сложных записей

mysql -u root -p

USE order;
SELECT products.p_name from products;

SELECT deals.deal_ID, clients.c_surname
FROM deals, clients
WHERE deals.deal_client_ID = clients.client_ID
ORDER BY deals.deal_ID;


SELECT products.p_name
FROM products,deals,clients
WHERE deal_time='2023-01-10 13:41:05'
AND clients.client_ID = deal_client_ID
AND products.product_ID = deal_product_ID;


SELECT d2.deal_number
FROM deals d1 , deals d2
WHERE d1.deal_number=10
AND d1.deal_ID=d2.deal_ID;

SELECT deals.deal_ID, clients.c_surname
FROM deals JOIN clients
ON deals.deal_client_ID=clients.client_ID
ORDER BY deals.deal_ID;

SELECT products.p_name, COUNT(deal_ID)
FROM products JOIN deals ON products.product_ID=deals.deal_product_ID
GROUP BY deals.deal_product_ID;

SELECT clients.c_name, clients.c_surname,
COUNT(deals.deal_ID) AS total
FROM clients JOIN deals ON clients.client_ID=deals.deal_client_ID
GROUP BY clients.client_ID
ORDER BY total DESC;

SELECT clients.c_name, clients.c_surname,
COUNT(deals.deal_ID) AS total
FROM clients LEFT JOIN deals ON clients.client_ID=deals.deal_client_ID
GROUP BY clients.client_ID
ORDER BY total DESC;

SELECT deal_number, deal_time FROM deals
WHERE deal_product_ID = (SELECT product_ID FROM products WHERE p_name = 'Apple')
ORDER BY deal_time;

SELECT p_name FROM products
WHERE product_ID=(SELECT deal_product_ID FROM deals
WHERE deal_number=(SELECT MAX(deal_number) FROM deals));

SELECT p_name FROM products
WHERE product_ID IN (SELECT deal_product_ID FROM deals GROUP BY deal_product_ID);

SELECT c_name,c_surname FROM clients
WHERE client_ID=ANY(SELECT deal_client_ID FROM deals);

SELECT deal_number,deal_time FROM deals
WHERE deal_number>ALL(SELECT AVG(deal_number) FROM deals
GROUP BY deal_product_ID);

SELECT c_name, c_surname FROM clients
WHERE EXISTS (SELECT * FROM deals
WHERE deals.deal_client_ID=clients.client_ID);