SQL  . 60      
 


  ,    SELECT        . 60     SQLite- : , , ,    .

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

          .      Python 3    sqlite3.  ,      .  ;   ,     .





 

SQL  . 60      



SQL  

60      

 

SQLite   

   2026

      Python 3   sqlite3.        .  ,         .




  :  JOIN   



  

   SELECT,       ?        .         ,      .  Python 3    sqlite3  .

       .        ,      .     ,   JOIN     . SQL  :       .

 :

: 1; : paid;  , ?: 1000;  : 2.

: 2; : paid;  , ?: 600;  : 1.

: 3; : cancelled;  , ?: 900;  : 1.

     ,       .  : 1000 + 600 = 1600 ?.    .     ;   ,     .


  

       join_demo.py   UTF-8.          ,    ,  :          .      Python-   .       ;    .

   ,   ,   python join_demo.py.  Python     python3  py,    .         .       .

import sqlite3



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

db.execute("PRAGMA foreign_keys = ON")

db.executescript("""

CREATE TABLE demo_orders (

order_id INTEGER PRIMARY KEY,

status TEXT NOT NULL,

total_rub INTEGER NOT NULL CHECK (total_rub >= 0)

);

CREATE TABLE demo_items (

order_id INTEGER NOT NULL REFERENCES demo_orders(order_id),

line_no INTEGER NOT NULL,

quantity INTEGER NOT NULL CHECK (quantity > 0),

unit_price_rub INTEGER NOT NULL CHECK (unit_price_rub >= 0),

PRIMARY KEY (order_id, line_no)

);

INSERT INTO demo_orders VALUES

(1, 'paid', 1000), (2, 'paid', 600), (3, 'cancelled', 900);

INSERT INTO demo_items VALUES

(1, 1, 1, 400), (1, 2, 2, 300),

(2, 1, 1, 600), (3, 1, 1, 900);

""")



wrong_sql = """

SELECT SUM(o.total_rub) AS paid_total_rub

FROM demo_orders AS o

JOIN demo_items AS i ON i.order_id = o.order_id

WHERE o.status = 'paid';

"""



correct_sql = """

SELECT COALESCE(SUM(o.total_rub), 0) AS paid_total_rub

FROM demo_orders AS o

WHERE o.status = 'paid'

AND EXISTS (

SELECT 1 FROM demo_items AS i

WHERE i.order_id = o.order_id

);

"""



print("  JOIN:", db.execute(wrong_sql).fetchone()[0])

print("  :", db.execute(correct_sql).fetchone()[0])

db.close()

 :

  JOIN: 2600

  : 1600


   

      JOIN   :    1     2.   1000 + 1000 + 600.

       ,      . EXISTS   ,         .       ,     .    , SUM  NULL; COALESCE    .

     SUM  SUM(DISTINCT o.total_rub).      .        1000 ?   ,    2600 ?,  SUM(DISTINCT ...)   1600 ?:   ,  .


 

    print       :

db.execute("INSERT INTO demo_orders VALUES (4, 'paid', 1000)")

db.execute("INSERT INTO demo_items VALUES (4, 1, 1, 1000)")

    3600,   2600.   ,      ,      ,    db.close().

         :  ,  , ,    .   60   , , , , , CTE   .       ;       .




   


    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

       .




  .


   .

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

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


