Базы данных и связи, SQL-запросы

1.

Python для продвинутых
специалистов (Django)
Тема №5. Базы данных и связи,
SQL-запросы.
Преподаватель: Панченко Игорь
Валентинович

2.

Что такое база данных

3.

Основы баз данных
База данных – это информационная модель
внешнего мира, отражающая информацию из
некоторой предметной области.
БД отражают связи тех сущностей,
информацию о которых они содержат.

4.

Основные компоненты БД
Система БД состоит из двух компонентов: базы
данных и системой управления базами данных
(СУБД).
Большинство систем обработки данных
включают также системы обработки данных,
обращающимся к БД через СУБД
Прикладное ПО
СУБД
База
данных

5.

CRUD - операции
• Чаще всего, для описания операций при
работе с данными используется акроним,
обозначающий 4 базовые функции: CRUD
(сокр. от англ. create, read, update, delete —
«создать, прочесть, обновить, удалить»)
• создание;
• чтение;
• редактирование;
• удаление.

6.

Правила целостности
Это набор правил, которым
должны удовлетворять значения
элементов данных.
Бывают явные (включаются в
структуру БД с помощью
специальных инструментов) и
неявные (определяются самой
структурой данных).

7.

Правила целостности
Также делятся на статические и динамические.
Статические правила отражают неизменность
информационных объектов, например:
уникальность и неизменность ИНН
налогоплательщика
Динамические правила отражают тот факт, что
данные должны принимать корректные
значения, например: значение поля-счетчика в
БД не может уменьшаться)

8.

Реляционные БД

9.

Что такое реляционная БД
Реляционная база данных – это набор
данных с предопределенными связями
между ними. Эти данные организованны в
виде набора таблиц, состоящих из столбцов и
строк.
В каждом столбце таблицы
хранится определенный тип
данных, в каждой ячейке –
значение атрибута

10.

ACID
Транзакции в реляционных БД соответствуют 4 правилам акронима ACID:
Атомарность - транзакция неделима.
Либо выполняется полностью, либо не выполняется
совсем.
Целостность– записываемые данные должны
соответствовать всем правилам и ограничениям
Изолированность гарантирует, что во избежание
путаницы транзакция не повлияет на другие элементы до
окончательного сохранения изменений.
Неизменность - внесенные в базу данных изменения на
момент успешного завершения транзакции считаются
постоянными

11.

Типы связей

12.

Многие ко многим
Задача: создать БД которая хранит данные о
работниках и их должностях. Существует
таблица работников (Employee) и таблица
должностей (Position). Работник может иметь
одну и более должностей. На одной
должности может работать несколько
сотрудников.

13.

Многие ко многим
Пример: БД хранит данные о сотрудниках
компании. Набор должностей стандартный.
• Работник может иметь одну и более
должностей (по совместительству)
• На одной должности может работать
несколько работников (2 админа и т.д.)
Employee – Таблица, описывающая
работника
Position – таблица, описывающая должность

14.

Многие ко многим
Чтобы создать нужную БД, нужно установить
связь между Employee и position.
Данная таблица-посредник связывает ID
работника и ID Должности
Вывод: нужен посредник для реализации.
Можно рассматривать с обеих сторон:
работник- позиция; позиция-работник

15.

Один ко многим
Задача: Нужно создать БД, которая ведёт
учет данных о пользователях. У
пользователя есть: имя, фамилия, возраст,
телефон(может быть несколько номеров
телефона)
Пользователь может иметь много номеров
телефона, но каждый телефон может
принадлежать только одному пользователю:
один пользователь, много телефонов

16.

Один ко многим
Таблица Phone
содержит номер
телефона, ID Владельца
и ID номера телефона
Person содержит
информацию о
пользователе

17.

Один ко многим
Таблица на 3 номера телефона:
PhoneId
PersonId
PhoneNumber
1
5
+7(777)999-88-66
2
5
+7(123)456-21-23
3
17
+7(481)555-74-38
Номера телефонов с ID 1 и 2 принадлежат
человеку с ID 5, телефон ID5 принадлежит
человеку с ID17
Если бы у таблицы Phone было больше
атрибутов, мы бы добавили их в эту же
таблицу рядом с полем PhoneNumber

18.

Один к одному
Пример: есть БД в которой есть только записи
об имени, возрасте и номере телефона
работника. В ней 1000 записей.
Возникла необходимость добавить данные о
том, может ли человек получить пособие из-за
плохого здоровья (True – плохое, False – хорошее)
Если добавить новый столбец в общую таблицу
– это будет долго и всего 2% людей получат
значение True в поле о плохом здоровье

19.

Один к одному
Для решения задачи выгоднее создать новую
таблицу, которая будет хранить только ID Тех
работников, у которых плохое здоровье
UnhealthyPersonId
UniqueEmployeeId
1
159
2
752
Важно, чтобы ID работника включалось в
таблицу только один раз, т.е. в этом поле
принимались только уникальные значения
(чтобы один работник не был вписан дважды)
Вывод: один к одному разделяет одну и ту же
таблицу на две

20.

База по SQL

21.

Справочник по функциям SQL
2SQL.ru - Информационный ресурс о языке SQL
На этом сайте собран необходимый минимум
функций SQL с примерами применения.

22.

Практика
Cоздадим базу данных с такой структурой:
users
orders
userid
int
orderid
int
fname
text
date
date
lname
text
userid
int
gender
text
total
int
Users – родительская таблица, на которую
будет ссылаться таблица Orders через поле
userid

23.

Подготовка
Импорт, настройка, подключение

24.

Создание таблицы
Создание таблицы
Создайте вторую таблицу самостоятельно

25.

Создание таблицы
Далее, необходимо добавить данные в
таблицу. Это делается также при помощи
объекта cursor

26.

Вставка данных
Также можно добавлять данные из
переменных Python. Все значения заменены
на знаки вопроса, и указан объект, откуда
нужно брать данные

27.

Вставка данных
Данные подаются в SQLite в виде кортежа,
либо списка с набором кортежей. Если
подаётся список с набором кортежей,
применяется функция executemany вместо
execute:

28.

Вставка данных
Дозагрузите эти данные в таблицы:
customers = [ ('00005', 'Stephanie', 'Stewart', 'female'), ('00006', 'Sincere', 'Sherman', 'female'), ('00007', 'Sidney', 'Horn', 'male'), ('00008', 'Litzy', 'Yates', 'female'), ('00009', 'Jaxon', 'Mills',
'male'), ('00010', 'Paul', 'Richard', 'male'), ('00011', 'Kamari', 'Holden', 'female'), ('00012', 'Gaige', 'Summers', 'female'), ('00013', 'Andrea', 'Snow', 'female'), ('00014', 'Angelica', 'Barnes',
'female'), ('00015', 'Leah', 'Pitts', 'female'), ('00016', 'Dillan', 'Olsen', 'male'), ('00017', 'Joe', 'Walsh', 'male'), ('00018', 'Reagan', 'Cooper', 'male'), ('00019', 'Aubree', 'Hogan', 'female'),
('00020', 'Avery', 'Floyd', 'male'), ('00021', 'Elianna', 'Simmons', 'female'), ('00022', 'Rodney', 'Stout', 'male'), ('00023', 'Elaine', 'Mcintosh', 'female'), ('00024', 'Myla', 'Mckenzie', 'female'),
('00025', 'Alijah', 'Horn', 'female'), ('00026', 'Rohan', 'Peterson', 'male'), ('00027', 'Irene', 'Walters', 'female'), ('00028', 'Lilia', 'Sellers', 'female'), ('00029', 'Perla', 'Jefferson', 'female'),
('00030', 'Ashley', 'Klein', 'female') ]
orders = [ ('00001', '2020-01-01', '00025', '178'), ('00002', '2020-01-03', '00025', '39'), ('00003', '2020-01-07', '00016', '153'), ('00004', '2020-01-10', '00015', '110'), ('00005', '2020-01-11',
'00024', '219'), ('00006', '2020-01-12', '00029', '37'), ('00007', '2020-01-14', '00028', '227'), ('00008', '2020-01-18', '00010', '232'), ('00009', '2020-01-22', '00016', '236'), ('00010', '2020-01-26',
'00017', '116'), ('00011', '2020-01-28', '00028', '221'), ('00012', '2020-01-31', '00021', '238'), ('00013', '2020-02-02', '00015', '177'), ('00014', '2020-02-05', '00025', '76'), ('00015', '2020-02-08',
'00022', '245'), ('00016', '2020-02-12', '00008', '180'), ('00017', '2020-02-14', '00020', '190'), ('00018', '2020-02-18', '00030', '166'), ('00019', '2020-02-22', '00002', '168'), ('00020', '2020-0226', '00021', '174'), ('00021', '2020-02-29', '00017', '126'), ('00022', '2020-03-02', '00019', '211'), ('00023', '2020-03-05', '00030', '144'), ('00024', '2020-03-09', '00012', '112'), ('00025', '202003-10', '00006', '45'), ('00026', '2020-03-11', '00004', '200'), ('00027', '2020-03-14', '00015', '226'), ('00028', '2020-03-17', '00030', '189'), ('00029', '2020-03-20', '00004', '152'), ('00030',
'2020-03-22', '00026', '239'), ('00031', '2020-03-23', '00012', '135'), ('00032', '2020-03-24', '00013', '211'), ('00033', '2020-03-27', '00030', '226'), ('00034', '2020-03-28', '00007', '173'),
('00035', '2020-03-30', '00010', '144'), ('00036', '2020-04-01', '00017', '185'), ('00037', '2020-04-03', '00009', '95'), ('00038', '2020-04-06', '00009', '138'), ('00039', '2020-04-10', '00025', '223'),
('00040', '2020-04-12', '00019', '118'), ('00041', '2020-04-15', '00024', '132'), ('00042', '2020-04-18', '00008', '238'), ('00043', '2020-04-21', '00003', '50'), ('00044', '2020-04-25', '00019', '98'),
('00045', '2020-04-26', '00017', '167'), ('00046', '2020-04-28', '00009', '215'), ('00047', '2020-05-01', '00014', '142'), ('00048', '2020-05-05', '00022', '173'), ('00049', '2020-05-06', '00015', '80'),
('00050', '2020-05-07', '00017', '37'), ('00051', '2020-05-08', '00002', '36'), ('00052', '2020-05-10', '00022', '65'), ('00053', '2020-05-14', '00019', '110'), ('00054', '2020-05-18', '00017', '36'),
('00055', '2020-05-21', '00008', '163'), ('00056', '2020-05-24', '00024', '91'), ('00057', '2020-05-26', '00028', '154'), ('00058', '2020-05-30', '00022', '130'), ('00059', '2020-05-31', '00017', '119'),
('00060', '2020-06-01', '00024', '137'), ('00061', '2020-06-03', '00017', '206'), ('00062', '2020-06-04', '00013', '100'), ('00063', '2020-06-05', '00021', '187'), ('00064', '2020-06-09', '00025',
'170'), ('00065', '2020-06-11', '00011', '149'), ('00066', '2020-06-12', '00007', '195'), ('00067', '2020-06-14', '00015', '30'), ('00068', '2020-06-16', '00002', '246'), ('00069', '2020-06-20', '00028',
'163'), ('00070', '2020-06-22', '00005', '184'), ('00071', '2020-06-23', '00022', '68'), ('00072', '2020-06-27', '00013', '92'), ('00073', '2020-06-30', '00022', '149'), ('00074', '2020-07-04', '00002',
'65'), ('00075', '2020-07-05', '00017', '88'), ('00076', '2020-07-09', '00007', '156'), ('00077', '2020-07-13', '00010', '26'), ('00078', '2020-07-16', '00008', '55'), ('00079', '2020-07-20', '00019',
'81'), ('00080', '2020-07-22', '00011', '78'), ('00081', '2020-07-23', '00026', '166'), ('00082', '2020-07-27', '00014', '65'), ('00083', '2020-07-30', '00021', '205'), ('00084', '2020-08-01', '00026',
'140'), ('00085', '2020-08-05', '00006', '236'), ('00086', '2020-08-06', '00021', '208'), ('00087', '2020-08-07', '00021', '169'), ('00088', '2020-08-08', '00004', '157'), ('00089', '2020-08-11',
'00017', '71'), ('00090', '2020-08-13', '00025', '89'), ('00091', '2020-08-16', '00014', '249'), ('00092', '2020-08-18', '00012', '59'), ('00093', '2020-08-19', '00013', '121'), ('00094', '2020-08-20',
'00025', '179'), ('00095', '2020-08-22', '00017', '208'), ('00096', '2020-08-26', '00024', '217'), ('00097', '2020-08-28', '00004', '206'), ('00098', '2020-08-30', '00017', '114'), ('00099', '2020-0831', '00017', '169'), ('00100', '2020-09-02', '00022', '226') ]

29.

Выборка данных
Для выборки данных из таблицы также можно
применить SQL –запрос:

30.

Выборка данных
Получаем все записи

31.

Удаление данных
Для удаления любого пользователя с
фамилией “Parker”, используется такая
команда:

32.

Соединение данных
Соединение данных
Из таблицы orders выбираются такие строчки
где user.userid совпадает с orders.userid и к
таблице Order слева приклеивается
соответствующее значение из таблицы Users

33.

Организация связи между
таблицами
Чтобы организовать связь –отношение между
таблицами, нужно указать в таблице orders что
поле group id будет использовать внешний
ключ – то же поле, но из другой таблицы:

34.

Задачи по SQL

35.

Данные для следующих задач
Структура таблицы:
tuples=[(101, 'Neena', 'Kochhar', 'NKOCHHAR', '515.123.4568', '6/18/1987', 'AD_VP', 17000, 'NULL', 100.0, 90), (), (103, 'Alexander', 'Hunold', 'AHUNOLD', '590.423.4567', '6/20/1987', 'IT_PROG', 9000, 'NULL', 102.0, 60), (104,
'Bruce', 'Ernst', 'BERNST', '590.423.4568', '6/21/1987', 'IT_PROG', 6000, 'NULL', 103.0, 60), (105, 'David', 'Austin', 'DAUSTIN', '590.423.4569', '6/22/1987', 'IT_PROG', 4800, 'NULL', 103.0, 60), (106, 'Valli', 'Pataballa', 'VPATABAL',
'590.423.4560', '6/23/1987', 'IT_PROG', 4800, 'NULL', 103.0, 60), (107, 'Diana', 'Lorentz', 'DLORENTZ', '590.423.5567', '6/24/1987', 'IT_PROG', 4200, 'NULL', 103.0, 60), (108, 'Nancy', 'Greenberg', 'NGREENBE', '515.124.4569',
'6/25/1987', 'FI_MGR', 12000, 'NULL', 101.0, 100), (109, 'Daniel', 'Faviet', 'DFAVIET', '515.124.4169', '6/26/1987', 'FI_ACCOUNT', 9000, 'NULL', 108.0, 100), (110, 'John', 'Chen', 'JCHEN', '515.124.4269', '6/27/1987', 'FI_ACCOUNT',
8200, 'NULL', 108.0, 100), (111, 'Ismael', 'Sciarra', 'ISCIARRA', '515.124.4369', '6/28/1987', 'FI_ACCOUNT', 7700, 'NULL', 108.0, 100), (), (113, 'Luis', 'Popp', 'LPOPP', '515.124.4567', '6/30/1987', 'FI_ACCOUNT', 6900, 'NULL', 108.0,
100), (114, 'Den', 'Raphaely', 'DRAPHEAL', '515.127.4561', '7/1/1987', 'PU_MAN', 11000, 'NULL', 100.0, 30), (115, 'Alexander', 'Khoo', 'AKHOO', '515.127.4562', '7/2/1987', 'PU_CLERK', 3100, 'NULL', 114.0, 30), (116, 'Shelli', 'Baida',
'SBAIDA', '515.127.4563', '7/3/1987', 'PU_CLERK', 2900, 'NULL', 114.0, 30), (117, 'Sigal', 'Tobias', 'STOBIAS', '515.127.4564', '7/4/1987', 'PU_CLERK', 2800, 'NULL', 114.0, 30), (118, 'Guy', 'Himuro', 'GHIMURO', '515.127.4565',
'7/5/1987', 'PU_CLERK', 2600, 'NULL', 114.0, 30), (119, 'Karen', 'Colmenares', 'KCOLMENA', '515.127.4566', '7/6/1987', 'PU_CLERK', 2500, 'NULL', 114.0, 30), (120, 'Matthew', 'Weiss', 'MWEISS', '650.123.1234', '7/7/1987',
'ST_MAN', 8000, 'NULL', 100.0, 50), (121, 'Adam', 'Fripp', 'AFRIPP', '650.123.2234', '7/8/1987', 'ST_MAN', 8200, 'NULL', 100.0, 50), (122, 'Payam', 'Kaufling', 'PKAUFLIN', '650.123.3234', '7/9/1987', 'ST_MAN', 7900, 'NULL', 100.0,
50), (123, 'Shanta', 'Vollman', 'SVOLLMAN', '650.123.4234', '7/10/1987', 'ST_MAN', 6500, 'NULL', 100.0, 50), (124, 'Kevin', 'Mourgos', 'KMOURGOS', '650.123.5234', '7/11/1987', 'ST_MAN', 5800, 'NULL', 100.0, 50), (125, 'Julia',
'Nayer', 'JNAYER', '650.124.1214', '7/12/1987', 'ST_CLERK', 3200, 'NULL', 120.0, 50), (126, 'Irene', 'Mikkilinen', 'IMIKKILI', '650.124.1224', '7/13/1987', 'ST_CLERK', 2700, 'NULL', 120.0, 50), (127, 'James', 'Landry', 'JLANDRY',
'650.124.1334', '7/14/1987', 'ST_CLERK', 2400, 'NULL', 120.0, 50), (128, 'Steven', 'Markle', 'SMARKLE', '650.124.1434', '7/15/1987', 'ST_CLERK', 2200, 'NULL', 120.0, 50), (129, 'Laura', 'Bissot', 'LBISSOT', '650.124.5234', '7/16/1987',
'ST_CLERK', 3300, 'NULL', 121.0, 50), (130, 'Mozhe', 'Atkinson', 'MATKINSO', '650.124.6234', '7/17/1987', 'ST_CLERK', 2800, 'NULL', 121.0, 50), (131, 'James', 'Marlow', 'JAMRLOW', '650.124.7234', '7/18/1987', 'ST_CLERK', 2500,
'NULL', 121.0, 50), (132, 'TJ', 'Olson', 'TJOLSON', '650.124.8234', '7/19/1987', 'ST_CLERK', 2100, 'NULL', 121.0, 50), (133, 'Jason', 'Mallin', 'JMALLIN', '650.127.1934', '7/20/1987', 'ST_CLERK', 3300, 'NULL', 122.0, 50), (134, 'Michael',
'Rogers', 'MROGERS', '650.127.1834', '7/21/1987', 'ST_CLERK', 2900, 'NULL', 122.0, 50), (135, 'Ki', 'Gee', 'KGEE', '650.127.1734', '7/22/1987', 'ST_CLERK', 2400, 'NULL', 122.0, 50), (136, 'Hazel', 'Philtanker', 'HPHILTAN',
'650.127.1634', '7/23/1987', 'ST_CLERK', 2200, 'NULL', 122.0, 50), (137, 'Renske', 'Ladwig', 'RLADWIG', '650.121.1234', '7/24/1987', 'ST_CLERK', 3600, 'NULL', 123.0, 50), (138, 'Stephen', 'Stiles', 'SSTILES', '650.121.2034', '7/25/1987',
'ST_CLERK', 3200, 'NULL', 123.0, 50), (139, 'John', 'Seo', 'JSEO', '650.121.2019', '7/26/1987', 'ST_CLERK', 2700, 'NULL', 123.0, 50), (140, 'Joshua', 'Patel', 'JPATEL', '650.121.1834', '7/27/1987', 'ST_CLERK', 2500, 'NULL', 123.0, 50),
(141, 'Trenna', 'Rajs', 'TRAJS', '650.121.8009', '7/28/1987', 'ST_CLERK', 3500, 'NULL', 124.0, 50), (142, 'Curtis', 'Davies', 'CDAVIES', '650.121.2994', '7/29/1987', 'ST_CLERK', 3100, 'NULL', 124.0, 50), (143, 'Randall', 'Matos', 'RMATOS',
'650.121.2874', '7/30/1987', 'ST_CLERK', 2600, 'NULL', 124.0, 50), (144, 'Peter', 'Vargas', 'PVARGAS', '650.121.2004', '7/31/1987', 'ST_CLERK', 2500, 'NULL', 124.0, 50), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (), (),
(), (), (), (), (), (), (), (), (), (), (178, 'Kimberely', 'Grant', 'KGRANT', '011.44.1644.', '9/3/1987', 'SA_REP', 7000, 'NULL', 0.15, 149), (), (180, 'Winston', 'Taylor', 'WTAYLOR', '650.507.9876', '9/5/1987', 'SH_CLERK', 3200, 'NULL', 120.0, 50),
(181, 'Jean', 'Fleaur', 'JFLEAUR', '650.507.9877', '9/6/1987', 'SH_CLERK', 3100, 'NULL', 120.0, 50), (182, 'Martha', 'Sullivan', 'MSULLIVA', '650.507.9878', '9/7/1987', 'SH_CLERK', 2500, 'NULL', 120.0, 50), (183, 'Girard', 'Geoni',
'GGEONI', '650.507.9879', '9/8/1987', 'SH_CLERK', 2800, 'NULL', 120.0, 50), (184, 'Nandita', 'Sarchand', 'NSARCHAN', '650.509.1876', '9/9/1987', 'SH_CLERK', 4200, 'NULL', 121.0, 50), (185, 'Alexis', 'Bull', 'ABULL', '650.509.2876',
'9/10/1987', 'SH_CLERK', 4100, 'NULL', 121.0, 50), (186, 'Julia', 'Dellinger', 'JDELLING', '650.509.3876', '9/11/1987', 'SH_CLERK', 3400, 'NULL', 121.0, 50), (187, 'Anthony', 'Cabrio', 'ACABRIO', '650.509.4876', '9/12/1987', 'SH_CLERK',
3000, 'NULL', 121.0, 50), (188, 'Kelly', 'Chung', 'KCHUNG', '650.505.1876', '9/13/1987', 'SH_CLERK', 3800, 'NULL', 122.0, 50), (189, 'Jennifer', 'Dilly', 'JDILLY', '650.505.2876', '9/14/1987', 'SH_CLERK', 3600, 'NULL', 122.0, 50), (190,
'Timothy', 'Gates', 'TGATES', '650.505.3876', '9/15/1987', 'SH_CLERK', 2900, 'NULL', 122.0, 50), (191, 'Randall', 'Perkins', 'RPERKINS', '650.505.4876', '9/16/1987', 'SH_CLERK', 2500, 'NULL', 122.0, 50), (192, 'Sarah', 'Bell', 'SBELL',
'650.501.1876', '9/17/1987', 'SH_CLERK', 4000, 'NULL', 123.0, 50), (193, 'Britney', 'Everett', 'BEVERETT', '650.501.2876', '9/18/1987', 'SH_CLERK', 3900, 'NULL', 123.0, 50), (194, 'Samuel', 'McCain', 'SMCCAIN', '650.501.3876',
'9/19/1987', 'SH_CLERK', 3200, 'NULL', 123.0, 50), (195, 'Vance', 'Jones', 'VJONES', '650.501.4876', '9/20/1987', 'SH_CLERK', 2800, 'NULL', 123.0, 50), (196, 'Alana', 'Walsh', 'AWALSH', '650.507.9811', '9/21/1987', 'SH_CLERK', 3100,
'NULL', 124.0, 50), (197, 'Kevin', 'Feeney', 'KFEENEY', '650.507.9822', '9/22/1987', 'SH_CLERK', 3000, 'NULL', 124.0, 50), (198, 'Donald', 'OConnell', 'DOCONNEL', '650.507.9833', '9/23/1987', 'SH_CLERK', 2600, 'NULL', 124.0, 50),
(199, 'Douglas', 'Grant', 'DGRANT', '650.507.9844', '9/24/1987', 'SH_CLERK', 2600, 'NULL', 124.0, 50), (200, 'Jennifer', 'Whalen', 'JWHALEN', '515.123.4444', '9/25/1987', 'AD_ASST', 4400, 'NULL', 101.0, 10), (201, 'Michael',
'Hartstein', 'MHARTSTE', '515.123.5555', '9/26/1987', 'MK_MAN', 13000, 'NULL', 100.0, 20), (202, 'Pat', 'Fay', 'PFAY', '603.123.6666', '9/27/1987', 'MK_REP', 6000, 'NULL', 201.0, 20), (203, 'Susan', 'Mavris', 'SMAVRIS',
'515.123.7777', '9/28/1987', 'HR_REP', 6500, 'NULL', 101.0, 40), (204, 'Hermann', 'Baer', 'HBAER', '515.123.8888', '9/29/1987', 'PR_REP', 10000, 'NULL', 101.0, 70), (205, 'Shelley', 'Higgins', 'SHIGGINS', '515.123.8080', '9/30/1987',
'AC_MGR', 12000, 'NULL', 101.0, 110), (206, 'William', 'Gietz', 'WGIETZ', '515.123.8181', '10/1/1987', 'AC_ACCOUNT', 8300, 'NULL', 205.0, 110)]

36.

Задачи SELECT
1. Создайте файл базы данных по этой
структуре
2. Выберите из таблицы 2 столбца First_name
и Last_name
3. Получите список с уникальными
значениями из столбца department_id
4. Получите данные обо всех работниках и
отсортируйте их по фамилии в порядке
убывания

37.

Задачи SELECT
5. Получите из таблицы данные столбцов
first_name, last_name, salary и создайте новый
столбец, pension_fund, значения в котором
вычисляются как salary * 0.10
6. Получите из таблицы значения столбцов ID,
first_name, last_name, salary и отсортируйте в
порядке возрастания зарплаты
7. Посчитайте общую зарплату,
выплачиваемую всем сотрудникам
8. Найдите минимальную и максимальную
зарплату в таблице

38.

Задачи SELECT
9. Вычислите количество сотрудников
10. Вычислите среднюю зарплату
11.Вычислите количество уникальных вакансий в
таблице
12.Выпишите список имен сотрудников, все
ИМЕНА должны быть в ВЕРХНЕМ регистре
13.Выпишите список первых трёх букв имен
сотрудников
14.Напишите запрос, создающий новый столбце
full_name, в котором при помощи оператора
конкатенции || объединяются значения из
столбцов first_name и last_name

39.

Задачи SELECT
15. Напишите команду, которая выбирает
первые 10 строк из таблицы, используя ф-ю
LIMIT
16. Напишите запрос, который выбирает из
таблицы столбцы first_name и last_name, и
вычисляет значения месячной зарплаты
(salary/12), округленной до 2 знаков после
запятой, помещая данные в столбце Monthly
salary

40.

Задачи на фильтрацию и
сортировку
1. Сделайте запрос, выбирающий из таблицы
first_name, last_name и salary всех
работников, зарплата которых лежит в
диапазоне от 9 до 14 тысяч
2. Сделайте запрос, выбирающий из таблицы
first_name, last_name и department_id всех
работников, employee_id которых лежит в
диапазоне от 120 до 140 и отсортируйте по
возрастанию department_id

41.

Задачи на фильтрацию и
сортировку
3. Напишите запрос, чтобы отобразить имена,
фамилии и зарплаты работников, с
зарплатами в диапазоне от 12 до 20 тысяч из
департаментов 30-100
4. Напишите запрос для выбора всех
работников, у которых в имени есть буквы d,a
5. Выберите из таблицы всех работников с
профессией IT_PROG или ST_CLERK и
зарплатой не равной 4500, 10000 или 15000

42.

Доп.задачи (GROUP_BY, HAVING)
1. Напишите запрос, который вычисляет
максимальную, минимальную, среднюю и суммарную
зарплату всех сотрудников фирмы
2. Напишите запрос, который составляет список из 2
столбцов: job_id и количество людей, работающих по
этой профессии из столбца job_id
3. Напишите запрос, находящий в таблице ID
сотрудника на должности ST_MAN с самой маленькой
зарплатой
4. Напишите запрос, выводящий список всех
department_id и суммарную ЗП, которую получили
сотрудники этого департамента
5. Напишите запрос, вычисляющий среднюю зарплату
всех департаментов, в которых работает более 10
человек

43.

Ссылки на материалы
• 14. Типы ограничений целостности, основные
типы данных, основные операции
реляционной модели данных. (studfile.net)
• SQLite Foreign Key: Enforce Relationships
Between Tables (sqlitetutorial.net)
• https://zametkinapolyah.ru/zametki-omysql/page/10
• https://proglib.io/p/upravlenie-dannymi-spomoshchyu-python-sqlite-i-sqlalchemy-202010-21