 SQL. 18   
 


   SQL-     .  18    : IN, EXISTS, NOT EXISTS,  ,   SELECT  FROM,  ,  , -join   .     SQLite,     .





 

 SQL. 18   





  





  


     ,      ,    .

    :    ,   ,   ,     .

       .         ,     .




   


     ,   .  ,    ,    .

  :    .        .

    SQLite.     SQL,      PostgreSQL, MySQL   .




  


1.       IN (#task-1)

2.      (#task-2)

3.      NOT EXISTS (#task-3)

4.     SELECT (#litres_trial_promo)

5.      (#litres_trial_promo)

6.       EXISTS (#litres_trial_promo)

7.      (#litres_trial_promo)

8.       (#litres_trial_promo)

9.   NOT IN  NULL (#litres_trial_promo)

10.       (#litres_trial_promo)

11.       (#litres_trial_promo)

12.     (#litres_trial_promo)

13.     (#litres_trial_promo)

14.    - (#litres_trial_promo)

15.      (#litres_trial_promo)

16.      (#litres_trial_promo)

17.        (#litres_trial_promo)

18.      (#litres_trial_promo)




 





 1.       IN



 

   ,        .




CREATE TABLE clients(id INTEGER, name TEXT);

CREATE TABLE orders(id INTEGER, client_id INTEGER, status TEXT);

INSERT INTO clients VALUES (1,''),(2,''),(3,''),(4,'');

INSERT INTO orders VALUES (101,1,'paid'),(102,1,'cancelled'),(103,3,'paid');




SELECT id, name

FROM clients

WHERE id IN (

SELECT client_id

FROM orders

WHERE status = 'paid'

)

ORDER BY id;


 

[[1, ""], [3, ""]]


 

   client_id    .

   ,  id    .

    ,     :        paid.


  

   ,         : IN     .


 

  104     paid.     ?

:    ,   ,   id     .




 2.     



 

   ,        .




CREATE TABLE orders(id INTEGER, total INTEGER);

INSERT INTO orders VALUES (1,300),(2,900),(3,600),(4,200);




SELECT id, total

FROM orders

WHERE total > (

SELECT AVG(total)

FROM orders

)

ORDER BY total DESC;


 

[[2, 900], [3, 600]]


 

    :  .

  500,      500.

        .


  

     .     ,  total > (...)  .


 

 ,   4   200  800?

:   650,    2  4;  3  600    .




 3.      NOT EXISTS



 

  ,    .       .




CREATE TABLE products(sku TEXT, name TEXT);

CREATE TABLE sales(id INTEGER, sku TEXT, qty INTEGER);

INSERT INTO products VALUES ('A1',''),('B2',''),('C3',''),('D4','-');

INSERT INTO sales VALUES (1,'A1',2),(2,'C3',1),(3,'A1',1);




SELECT p.sku, p.name

FROM products p

WHERE NOT EXISTS (

SELECT 1

FROM sales s

WHERE s.sku = p.sku

)

ORDER BY p.sku;


 

[["B2", ""], ["D4", "-"]]


 




  .


   .

   ,     (https://www.litres.ru/book/raznoe/podzaprosy-sql-18-zadach-dlia-analitika-74419484/)  .

      Visa, MasterCard, Maestro,    ,   ,     ,  PayPal, WebMoney, ., QIWI ,       .


