Tasks

Complete the task of the chosen difficulty level for your variant number.

The main DBMS is SQLite. The initial level uses sqlite3; the basic level adds relationships and reports; the advanced level implements domain data access through SQLAlchemy 2.0 and requires pytest tests. Alembic is an optional exploration, not a replacement for the required work. Pass the path to a separate learning database file as an argument. The menu must add, display, update, and delete records by identifier, confirming the result of each operation. Use only this lab's database for destructive actions. Store monetary amounts as integer kopiykas and dates in validated ISO format. Personal data is fictional. All functions must have annotations.

Variants

Variant 1. Library

1. Initial level. Create a sqlite3 console application with the table books(id,title). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema books(id,title); readers(id,name); loans(id,book_id,reader_id,due,returned). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: open loans with due before the ISO date passed as an argument; returned is 0 or 1. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema books(id,title); readers(id,name); loans(id,book_id,reader_id,due,returned). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: open loans with due before the ISO date passed as an argument; returned is 0 or 1. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 2. Store

1. Initial level. Create a sqlite3 console application with the table products(id,name,cents). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema products(id,name,cents); customers(id,name); sales(id,product_id,customer_id,quantity). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: revenue by product as the sum of quantity*cents; quantity is positive. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema products(id,name,cents); customers(id,name); sales(id,product_id,customer_id,quantity). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: revenue by product as the sum of quantity*cents; quantity is positive. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 3. Outpatient clinic

1. Initial level. Create a sqlite3 console application with the table doctors(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema doctors(id,name); patients(id,name); visits(id,doctor_id,patient_id,start). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: a doctor's appointments on a given ISO date; start is an ISO date and time, duplicate doctor_id/start is prohibited; use fictional data only. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema doctors(id,name); patients(id,name); visits(id,doctor_id,patient_id,start). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: a doctor's appointments on a given ISO date; start is an ISO date and time, duplicate doctor_id/start is prohibited; use fictional data only. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 4. Auto repair shop

1. Initial level. Create a sqlite3 console application with the table cars(id,plate). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema cars(id,plate); services(id,name,cents); repairs(id,car_id,service_id,quantity). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the repair cost for each car as the sum of quantity*cents. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema cars(id,plate); services(id,name,cents); repairs(id,car_id,service_id,quantity). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the repair cost for each car as the sum of quantity*cents. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 5. Soccer league

1. Initial level. Create a sqlite3 console application with the table teams(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema teams(id,name); matches(id,home_id,away_id,home_goals,away_goals). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: team points: 3 for a win, 1 for a draw, 0 for a loss; two FKs reference teams, the teams in a match differ, and goals are nonnegative. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema teams(id,name); matches(id,home_id,away_id,home_goals,away_goals). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: team points: 3 for a win, 1 for a draw, 0 for a loss; two FKs reference teams, the teams in a match differ, and goals are nonnegative. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 6. Hotel

1. Initial level. Create a sqlite3 console application with the table rooms(id,number). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema rooms(id,number); guests(id,name); bookings(id,room_id,guest_id,start,end). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: available rooms for the interval [start,end), with ISO dates and start<end; reject overlapping bookings for a room. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema rooms(id,number); guests(id,name); bookings(id,room_id,guest_id,start,end). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: available rooms for the interval [start,end), with ISO dates and start<end; reject overlapping bookings for a room. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 7. Dean's office

1. Initial level. Create a sqlite3 console application with the table students(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema students(id,name); courses(id,title); grades(student_id,course_id,score). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: students' average grades; score is an integer 0..100, the student_id/course_id pair is unique, and a student without grades has NULL. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema students(id,name); courses(id,title); grades(student_id,course_id,score). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: students' average grades; score is an integer 0..100, the student_id/course_id pair is unique, and a student without grades has NULL. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 8. Warehouse

1. Initial level. Create a sqlite3 console application with the table products(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema products(id,name); movements(id,product_id,delta). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the balance of each product as the sum of signed delta values; initially 0, reject an operation producing a negative total. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema products(id,name); movements(id,product_id,delta). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the balance of each product as the sum of signed delta values; initially 0, reject an operation producing a negative total. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 9. Movie theater

1. Initial level. Create a sqlite3 console application with the table movies(id,title). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema movies(id,title); sessions(id,movie_id,capacity); tickets(id,session_id,seat). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the number of seats sold and remaining seats for a screening; capacity is positive, seat is within bounds, and the session_id/seat pair is unique. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema movies(id,title); sessions(id,movie_id,capacity); tickets(id,session_id,seat). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the number of seats sold and remaining seats for a screening; capacity is positive, seat is within bounds, and the session_id/seat pair is unique. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 10. Travel agency

1. Initial level. Create a sqlite3 console application with the table tours(id,title,cents). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema tours(id,title,cents); clients(id,name); purchases(id,tour_id,client_id,quantity). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the number of seats sold and revenue by tour. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema tours(id,title,cents); clients(id,name); purchases(id,tour_id,client_id,quantity). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the number of seats sold and revenue by tour. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 11. Cookbook

1. Initial level. Create a sqlite3 console application with the table dishes(id,title). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema dishes(id,title); ingredients(id,name); recipe(dish_id,ingredient_id,grams). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: dishes containing a given ingredient; grams is positive, and the key pair in recipe is unique. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema dishes(id,title); ingredients(id,name); recipe(dish_id,ingredient_id,grams). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: dishes containing a given ingredient; grams is positive, and the key pair in recipe is unique. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 12. Taxi service

1. Initial level. Create a sqlite3 console application with the table drivers(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema drivers(id,name); rides(id,driver_id,date,cents). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the number of rides and driver revenue for a given ISO date. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema drivers(id,name); rides(id,driver_id,date,cents). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the number of rides and driver revenue for a given ISO date. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 13. Veterinary clinic

1. Initial level. Create a sqlite3 console application with the table owners(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema owners(id,name); pets(id,owner_id,name); visits(id,pet_id,due). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: visits with due before a given ISO date; use fictional records only, without medical advice. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema owners(id,name); pets(id,owner_id,name); visits(id,pet_id,due). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: visits with due before a given ISO date; use fictional records only, without medical advice. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 14. Equipment rental

1. Initial level. Create a sqlite3 console application with the table equipment(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema equipment(id,name); clients(id,name); rentals(id,equipment_id,client_id,due,returned). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: unreturned rentals with overdue due dates; returned is 0 or 1. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema equipment(id,name); clients(id,name); rentals(id,equipment_id,client_id,due,returned). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: unreturned rentals with overdue due dates; returned is 0 or 1. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 15. Music service

1. Initial level. Create a sqlite3 console application with the table artists(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema artists(id,name); albums(id,artist_id,title); plays(id,album_id,date). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the number of album plays for a given month YYYY-MM. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema artists(id,name); albums(id,artist_id,title); plays(id,album_id,date). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the number of album plays for a given month YYYY-MM. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 16. Home accounting

1. Initial level. Create a sqlite3 console application with the table categories(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema categories(id,name); operations(id,category_id,date,cents). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the sum of signed kopiykas by category and month YYYY-MM. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema categories(id,name); operations(id,category_id,date,cents). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the sum of signed kopiykas by category and month YYYY-MM. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 17. School cafeteria

1. Initial level. Create a sqlite3 console application with the table dishes(id,name,cents). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema dishes(id,name,cents); pupils(id,name); orders(id,dish_id,pupil_id,quantity). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the cost of orders by pupil; quantity is positive, and prices are for learning purposes. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema dishes(id,name,cents); pupils(id,name); orders(id,dish_id,pupil_id,quantity). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the cost of orders by pupil; quantity is positive, and prices are for learning purposes. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 18. Scientific conference

1. Initial level. Create a sqlite3 console application with the table sections(id,title). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema sections(id,title); speakers(id,name); talks(id,section_id,speaker_id,start,end). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: a section's schedule, with start/end as integer minutes 0..1440 and start<end; reject overlaps in the same section. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema sections(id,title); speakers(id,name); talks(id,section_id,speaker_id,start,end). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: a section's schedule, with start/end as integer minutes 0..1440 and start<end; reject overlaps in the same section. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 19. Fitness club

1. Initial level. Create a sqlite3 console application with the table trainers(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema trainers(id,name); clients(id,name); attendance(id,trainer_id,client_id,date). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the number of visits by trainer for a given month. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema trainers(id,name); clients(id,name); attendance(id,trainer_id,client_id,date). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the number of visits by trainer for a given month. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 20. Human resources

1. Initial level. Create a sqlite3 console application with the table departments(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema departments(id,name); employees(id,department_id,name,cents). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the sample monthly payroll by department; cents is nonnegative. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema departments(id,name); employees(id,department_id,name,cents). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the sample monthly payroll by department; cents is nonnegative. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 21. Pharmacy inventory

1. Initial level. Create a sqlite3 console application with the table suppliers(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema suppliers(id,name); products(id,supplier_id,name,expires); sales(id,product_id,quantity). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: products with expires before a given ISO date and the number of sales; use fictional names and no dosages. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema suppliers(id,name); products(id,supplier_id,name,expires); sales(id,product_id,quantity). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: products with expires before a given ISO date and the number of sales; use fictional names and no dosages. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 22. Real estate agency

1. Initial level. Create a sqlite3 console application with the table agents(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema agents(id,name); properties(id,address); deals(id,agent_id,property_id,cents). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: an agent's total deals and a 2 percent commission rounded down to a kopiyka; one deal per property. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema agents(id,name); properties(id,address); deals(id,agent_id,property_id,cents). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: an agent's total deals and a 2 percent commission rounded down to a kopiyka; one deal per property. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 23. Logistics

1. Initial level. Create a sqlite3 console application with the table warehouses(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema warehouses(id,name); shipments(id,warehouse_id,due,delivered). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: shipments delivered late; delivered may be NULL, and dates are ISO. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema warehouses(id,name); shipments(id,warehouse_id,due,delivered). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: shipments delivered late; delivered may be NULL, and dates are ISO. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 24. Charitable foundation

1. Initial level. Create a sqlite3 console application with the table campaigns(id,title,target_cents). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema campaigns(id,title,target_cents); donors(id,name); donations(id,campaign_id,donor_id,cents). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the amount and percentage raised by campaign; target_cents and donations are positive. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema campaigns(id,title,target_cents); donors(id,name); donations(id,campaign_id,donor_id,cents). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the amount and percentage raised by campaign; target_cents and donations are positive. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 25. Board games

1. Initial level. Create a sqlite3 console application with the table games(id,title). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema games(id,title); genres(id,name); game_genres(game_id,genre_id); ratings(id,game_id,score). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the average rating of games in a given genre, with score 0..10; genre relationships are unique. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema games(id,title); genres(id,name); game_genres(game_id,genre_id); ratings(id,game_id,score). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the average rating of games in a given genre, with score 0..10; genre relationships are unique. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 26. Weather observations

1. Initial level. Create a sqlite3 console application with the table stations(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema stations(id,name); readings(id,station_id,date,temp). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the average finite temperature by station and month; temp is from -100 to 100. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema stations(id,name); readings(id,station_id,date,temp). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the average finite temperature by station and month; temp is from -100 to 100. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 27. Railway

1. Initial level. Create a sqlite3 console application with the table stations(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema stations(id,name); trips(id,name); stops(trip_id,station_id,position). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: trips from a given station A to B where position(A)<position(B); positions and stations are unique within a trip. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema stations(id,name); trips(id,name); stops(trip_id,station_id,position). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: trips from a given station A to B where position(A)<position(B); positions and stations are unique within a trip. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 28. Support service

1. Initial level. Create a sqlite3 console application with the table users(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema users(id,name); tickets(id,user_id,status); comments(id,ticket_id,text). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the number of tickets by status new, active, done and the number of comments on each ticket. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema users(id,name); tickets(id,user_id,status); comments(id,ticket_id,text). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the number of tickets by status new, active, done and the number of comments on each ticket. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 29. Farm

1. Initial level. Create a sqlite3 console application with the table fields(id,name,hectares). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema fields(id,name,hectares); crops(id,name); harvests(id,field_id,crop_id,year,kg). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: yield kg/hectares by field and year; hectares is positive, kg is nonnegative, and year is 2000..2100. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema fields(id,name,hectares); crops(id,name); harvests(id,field_id,crop_id,year,kg). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: yield kg/hectares by field and year; hectares is positive, kg is nonnegative, and year is 2000..2100. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Variant 30. Cat show

1. Initial level. Create a sqlite3 console application with the table breeds(id,name). Pass the SQLite file path as an argument and enter records through a menu. Implement adding, reading by id, updating, and deleting; id is INTEGER PRIMARY KEY, text names are nonempty, amounts are nonnegative integer kopiykas, dates are ISO, and other numeric fields are nonnegative. Output a list ordered by id and a rejection message. Use parameters, annotated functions, and explicit commit/rollback.

2. Basic level. Create a console application for SQLite with the schema breeds(id,name); cats(id,breed_id,name); scores(id,cat_id,judge,score). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the average score and breed winners, including all ties; score is 0..100, and the cat_id/judge pair is unique. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Use sqlite3, enable foreign keys before the transaction, use parameterized queries and one JOIN with an aggregate or subquery. Execute a related batch of changes atomically and demonstrate rollback on error.

3. Advanced level. Create a console application for SQLite with the schema breeds(id,name); cats(id,breed_id,name); scores(id,cat_id,judge,score). id is a PK, fields with the suffix _id are FKs to the corresponding entity; relationship keys without id are composite. Pass the database path as an argument and enter initial records through a menu. Implement CRUD and a report: the average score and breed winners, including all ties; score is 0..100, and the cat_id/judge pair is unique. Text names are nonempty, and amounts are integer kopiykas; specify NOT NULL, CHECK, and the required UNIQUE constraints. Output a detailed report with stable sorting. Implement access through SQLAlchemy 2.0: DeclarativeBase, Mapped, relationship, and select; the session and transaction must have an explicit owner, and relationships for the report must be loaded without unnecessary N+1 queries. Require pytest tests on a separate database for success, duplicates, an invalid FK/boundary, and batch rollback. Show the report SQL and check the result against a manual calculation.

Procedure

1. Draw the table schema with PKs, FKs, uniqueness, and constraints. Define the policy for deleting parent rows.
2. Create a new learning database. Check that foreign keys are actually enabled; show the schema in the CLI or DataGrip.
3. Implement parameterized operations and the report. For SQLAlchemy, use the 2.0 style, an explicit transaction owner, and a session.
4. Demonstrate a successful change, an invalid FK, a duplicate key, a CHECK rejection, and batch rollback. Verify that the state remains unchanged.
5. For the advanced level, run pytest with a new database per test; show the relationship queries and the absence of unnecessary N+1 queries for the report.
6. Submit a local Git repository: code, a README, the schema, test data, and dependencies. Do not include a production database with private data. During the defense, explain the SQL of one ORM query and the transaction boundary.