 SQL. 18    
 


     SQL-     .  18    :  ,   JOIN, LEFT JOIN    WHERE, COUNT  NULL,  , HAVING, UNION, NOT IN,  , ,      .     SQLite,     .





 

 SQL. 18    





  





        


SQL    ,  -  .    ,       .

    :  JOIN  , WHERE   ,    , COUNT  NULL.

        ,      .




   


         .     ,        .

     ,     : ,  , ,   .

    SQLite.         ,       .




  


1.      (#task-1)

2.     JOIN (#task-2)

3.   LEFT JOIN  WHERE (#task-3)

4.   COUNT(*)  COUNT(email) (#litres_trial_promo)

5.    - NULL- (#litres_trial_promo)

6.      (#litres_trial_promo)

7.      (#litres_trial_promo)

8.       (#litres_trial_promo)

9.    -   (#litres_trial_promo)

10.     NOT IN  NULL (#litres_trial_promo)

11.     UNION (#litres_trial_promo)

12.       (#litres_trial_promo)

13.   WHERE  HAVING (#litres_trial_promo)

14.      INNER JOIN (#litres_trial_promo)

15.       (#litres_trial_promo)

16.       (#litres_trial_promo)

17.      (#litres_trial_promo)

18.     (#litres_trial_promo)




 





 1.     



 

               .




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

INSERT INTO orders VALUES

(1,'paid',500),

(2,'new',700),

(3,'paid',300),

(4,'cancelled',900);




SELECT SUM(total) AS paid_revenue

FROM orders

WHERE status = 'paid';


 

[[800]]


 

WHERE    .

   500  300.

new  cancelled     .


  

SUM(total)     2400     ,     .


 

     ,    status = paid?

:    900  ,       .




 2.     JOIN



 

         ,     .




CREATE TABLE orders(id INTEGER, status TEXT);

CREATE TABLE items(order_id INTEGER, sku TEXT);

INSERT INTO orders VALUES (1,'paid'),(2,'paid'),(3,'new');

INSERT INTO items VALUES (1,'A'),(1,'B'),(2,'C'),(3,'D');




SELECT COUNT(DISTINCT o.id) AS paid_orders

FROM orders o

JOIN items i ON i.order_id = o.id

WHERE o.status = 'paid';


 

[[2]]


 

 1  JOIN    -  .

COUNT(DISTINCT o.id)   1  .

 paid   3.


  

COUNT(*)  JOIN   3,        .


 

 COUNT(*)        items?

:       ,  JOIN    .




 3.   LEFT JOIN  WHERE



 

        ,    .




CREATE TABLE categories(name TEXT);

CREATE TABLE sales(category TEXT, status TEXT, total INTEGER);

INSERT INTO categories VALUES ('books'),('courses'),('templates');

INSERT INTO sales VALUES ('books','paid',300),('books','new',500),('courses','paid',700);




  .


   .

   ,     (https://www.litres.ru/book/raznoe/oshibki-sql-18-zadach-na-otladku-otchiotov-74419354/)  .

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


