SQL  . 60      
 


  ,    SELECT        . 60     SQLite- : , , ,    .

 : , , JOIN, , CTE   .   ;   , ,      .    ,     ,          .

          .      Python 3    sqlite3.  ,      .  ;   ,     .





 

SQL  . 60      



SQL  

60      

 

SQLite   

   2026

      Python 3   sqlite3.        .  ,         .




   


    SQL     -.   60 :        .   ,          .

   ,        SELECT.        .        ,         .

 , ,      .         .       .     ;    ,          .

     .       answer.sql       .         .

    SQLite.   PostgreSQL, MySQL           .  60    SQLite 3.53.1.    ,       .




  

   

  

 



 

  

  CTE

 

    

   

   

    






  


     .        create_shoplab.py   UTF-8.        :       print.       .       .       Python    :       .

 ,    ,   .     Python   python3  py,     .

python create_shoplab.py

  shoplab.sqlite3        .    ,      .        .

customers  30 .

products  18 .

orders  72 .

order_items  144 .

payments  60 .

Python     SQLite    .             check_sqlite.py.         [(1,)].

import sqlite3

db = sqlite3.connect(":memory:")

print(db.execute("SELECT row_number() OVER ()").fetchall())

db.close()

 Python  ,     Python 3    python.org.       OVER,   SQLite     .      ,       .


 

 query.py   .   answer.sql      :

SELECT COUNT(*) AS customers_count

FROM customers;

python query.py answer.sql

  customers_count,  30   : 1.    answer.sql  .       ;     .

      .         .        .     ,       ;    .     .




   



customers

. customer_id   , customer_name   , city  , segment  retail, pro  premium, signup_date   . email_opt_in  1      0 .


products

. product_id  , product_name  , category  office, home, electronics  sport. price_rub      , stock_qty   , is_active    0  1.


orders

. order_id  , customer_id   , order_date  . status  paid, shipped, delivered  cancelled. channel  market, site  partner.


order_items

 .   order_id  product_id      . quantity  , unit_price_rub        .       .


payments

. payment_id  ;  order_id       . paid_at  , amount_rub     , method  card, sbp  invoice.

:     ;           .       .      .

       ;   . , ,     .                .     .

     ,  .          .           .

     2026 ;  .        .     30     ,    .




 1  


   . WHERE   ,  ,  SELECT   . ORDER BY ,   ;     .

 LIMIT   .        ,      . NULL   IS NULL,    .

          .       ;     .       ,       .


 1 1

   : , ,   .    ,     .

: product_id, product_name, category, price_rub.


 1 2

     .  ,   ,     .

: customer_name, city, segment.


 1 3

  , ,        2026 .   .      .

: order_id, customer_id, order_date, status.


 1 4

       .

: city.


 1 5

     .

: product_name, category, price_rub.


 1 6

     10 ,   .

: product_id, product_name, stock_qty.


 1 7

 ,          .  product_id, product_name  price_rub,   product_id.

: product_id, product_name, price_rub.


 1 8

      site.

: order_id, customer_id, order_date.


 1 9

    :   500   low;  500   1000    mid;  1000   high.  ,   ,     product_id.

: product_name, price_rub, price_band.


 1 10

 ,    email-     1  2026 .

: customer_id, customer_name, signup_date.




 2 


GROUP BY   :         . WHERE   , HAVING    .

 SUM ,   :   ,     . COUNT(*)  , COUNT()  NULL.    100.0,    .

          .       ;     .       ,       .


 2 1

     .

: city, customers_count.


 2 2

       ,    .

: category, avg_price_rub.


 2 3

    .

: status, orders_count.


 2 4

       .

: method, payments_count, revenue_rub.


 2 5

 ,    quantity       8.       :   ,    .

: product_id, product_name, units_sold.


 2 6

       quantity ? unit_price_rub.   .     ,   order_id.

: order_id, order_total_rub.


 2 7

 ,      .

: customer_id, customer_name, orders_count.


 2 8

       .

: category, min_price_rub, max_price_rub.


 2 9

          .

: cancellation_rate_pct.


 2 10

      .

: average_payment_rub.




 3  


    . INNER JOIN  , LEFT JOIN       NULL ,   .

  .     ,        .      ,   .       .

          .       ;     .       ,       .


 3 1

        .

: order_id, order_date, status, customer_name, city.


 3 2

   : , , ,    .

: order_id, product_name, quantity, unit_price_rub, line_total_rub.


 3 3

 ,       .

: customer_id, customer_name, city.


 3 4

 ,       .

: product_id, product_name, category.


 3 5

    .       .

: order_id, status, method, amount_rub.


 3 6

      .         ;      .       .

: category, revenue_rub.


 3 7

      ,     .

: customer_id, customer_name, paid_rub.


 3 8

    ,       amount_rub.     .   order_id.

: order_id, items_total_rub, amount_rub.


 3 9

         .

: channel, orders_count, paid_rub.


 3 10

 -10       .




  .


   .

   ,     (https://www.litres.ru/book/raznoe/sql-na-praktike-60-zadach-s-resheniiami-na-dannykh-magazina-74413483/)  .

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


