  SQL. 18     
 


        SQL.  18    :  ,  SKU, , , , , ,      .     SQLite.





 

  SQL. 18     





  





    


  ,    - , , ,     .

SQL    ,   ,  ,    .

      ,        .




  


 ,    : , , ,   .

 ,    : , , ,   .

      SQLite,       .




  


1.      (#task-1)

2.     (#task-2)

3.    (#task-3)

4.    (#litres_trial_promo)

5.    (#litres_trial_promo)

6.     (#litres_trial_promo)

7.    (#litres_trial_promo)

8.   (#litres_trial_promo)

9.     (#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.     



 

    ,        .




CREATE TABLE orders(order_id INTEGER, revenue INTEGER, cost INTEGER);

INSERT INTO orders VALUES (1,900,420),(2,500,250),(3,300,360);




SELECT order_id, revenue - cost AS gross_profit

FROM orders

ORDER BY order_id;


 

[[1, 480], [2, 250], [3, -60]]


 

    revenue  cost,  .

     ,         SQLite.

 ORDER BY      ,        .


  

       ,    .


 

    ?        .

:  3: 300  360   60.




 2.    



 

 ,  SKU   ,       .




CREATE TABLE order_items(sku TEXT, revenue INTEGER, cost INTEGER);

INSERT INTO order_items VALUES ('A1',500,200),('A1',700,300),('B2',400,380),('C3',300,350);




SELECT sku, SUM(revenue - cost) AS gross_profit

FROM order_items

GROUP BY sku

ORDER BY gross_profit DESC, sku;


 

[["A1", 700], ["B2", 20], ["C3", -50]]


 

SUM(revenue - cost)      SKU.

     ,         SQLite.

 ORDER BY      ,        .


  

          ,   .


 

 SKU  ?        .

: A1,      700.




 3.   



 

  ,      ,    .




CREATE TABLE order_items(sku TEXT, revenue INTEGER, cost INTEGER);

INSERT INTO order_items VALUES ('A1',500,200),('B2',400,450),('B2',100,120),('C3',300,260);




SELECT sku, SUM(revenue - cost) AS gross_profit

FROM order_items

GROUP BY sku

HAVING SUM(revenue - cost) < 0

ORDER BY sku;


 

[["B2", -70]]


 

HAVING        .

     ,         SQLite.

 ORDER BY      ,        .


  

WHERE revenue - cost < 0     ,     SKU.




  .


   .

   ,     (https://www.litres.ru/book/raznoe/marzha-v-sql-18-zadach-dlia-prodavtsa-i-analitika-74419897/)  .

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


