SQL  . 18   
 


     SQL-      .  18    : , JOIN, GROUP BY, HAVING, COUNT DISTINCT, NULL, , CTE, , UNION,  ,    .     SQLite,     .





 

SQL  . 18   





  





   SQL-  


        .  ,     , ,     .

    .        : , ,  ,     .

  ,  .     ,     SQL-  .




 


   .    ,            .

   ,    :     , NULL,    .

   SQLite.        ,      .




  


1.    (#task-1)

2.      (#task-2)

3.     (#task-3)

4.   email (#litres_trial_promo)

5.    (#litres_trial_promo)

6.      (#litres_trial_promo)

7.     CTE (#litres_trial_promo)

8.  NULL- (#litres_trial_promo)

9.      NOT EXISTS (#litres_trial_promo)

10.     (#litres_trial_promo)

11.    (#litres_trial_promo)

12.    (#litres_trial_promo)

13.     (#litres_trial_promo)

14.     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 COUNT(*) AS paid_orders

FROM orders

WHERE status = 'paid';


 

[[2]]


 

WHERE    paid.

    .

COUNT(*)    .


  

COUNT(*)  WHERE    ,    .


 

    900       ?

:   cancelled,        paid.




 2.     



 

       ,    .




CREATE TABLE orders(id INTEGER, total INTEGER);

INSERT INTO orders VALUES (1,500),(2,900),(3,300),(4,700);




SELECT total

FROM orders

ORDER BY total DESC

LIMIT 1 OFFSET 1;


 

[[700]]


 

ORDER BY total DESC     .

OFFSET 1   900.

LIMIT 1    700.


  

   ,      :      .


 

    ,        900?

:       900,      700.




 3.    



 

    ,         .




CREATE TABLE clients(id INTEGER, name TEXT);

CREATE TABLE orders(id INTEGER, client_id INTEGER);

INSERT INTO clients VALUES (10,''),(20,''),(30,'');

INSERT INTO orders VALUES (1,10),(2,10),(3,30);




SELECT c.id, c.name

FROM clients c

LEFT JOIN orders o ON o.client_id = c.id

WHERE o.id IS NULL;


 

[[20, ""]]


 

LEFT JOIN   .

  20   .

WHERE o.id IS NULL     .




  .


   .

   ,     (https://www.litres.ru/book/raznoe/sql-dlia-sobesedovaniia-18-zadach-s-otvetami-74419723/)  .

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


