<SYSTEM_TASK:>
Given the following text description, write Python code to implement the functionality described below step by step
<END_TASK>
<USER_TASK:>
Description:
 Лекция 1 Базовый SQL
Step1:  Давайте создадим таблицу, заполним ее и сделаем какой-нибудь запрос!
Step2:  Посмотрим на полученную таблицу.
Step3:  Немного SQL терминологии
имя таблицы - product.
Каждая строка таблицы называется строкой или кортеж. 
Заметьте, что все кортежи имеют поля или атрибуты.
Количество строк называет мощностью, в то время как количество атрибутов арностью 
Соглашения схемы
Схема таблицы продукта может быть описана следующим образом
Step4:  Посмотрим на примеры проекции, то есть получим только несколько атрибутов запроса.
Step5:  На выходе все еще таблица и ее схема -
Answer(pname, price, manufacturer)
Можно объединять выборку и проекцию
Step6:  На выходе запроса к таблице - снова таблица
Step7:  Небольшие детали
Некоторые элементы регистро-независимые
Step8:  LIKE
Опертоор LIKE нужен для поиска строк
Step9:  Убрать дубли
Дубли не всегда хорошо, и иногда их стоит убирать
Помните, что таблицы - мультимножества!
Step10:  Сортировка результатов
Так как Таблица - это мультимножество, то порядок вывода строк не гарантирован. Иногда необходимо выводить строки в определенном порядке
Step11:  Соединения - JOIN
Рассмотрим таблицу компаний.
company(<u>cname</u>, stockprice, country)
Step12:  Внешние ключи
Допустим мы ходим добавить таблицу продуктов
Product(pname, price, category, manufacturer)
Может возникнуть следующая ситуация
Step13:  Внешние ключи являются ограничениями таблицы 
Что случится при вставке компании, которой не в таблице company?
Step14:  Внешние ключи и удаление
Что произойдет, если мы удалим компанию ? Несколько вариантов
Step15:  Второй вариант
Step16:  Другой вариант написания запроса.
Step17:  Примечания
Есть множество логических вариантов написать один и тот же запрос
Этот факт будет использоваться для оптимизации
Дубли после соединения
Замечание - могут возникнуть дубли после соединения...
<ASSISTANT_TASK:>
Python Code:
%load_ext sql
%sql sqlite://
Explanation: Лекция 1 Базовый SQL
End of explanation
%%sql drop table if exists product;
create table product(
       pname        varchar primary key, -- имя продукта
       price        money,               -- цена продукта
       category     varchar,             -- категория
       manufacturer varchar NOT NULL     -- производитель
);
insert into product values('Пряник', 19.99, 'Еда', 'Тульский пряник');
insert into product values('Тетрадь', 29.99, 'Канцелярия', 'Эксмо');
insert into product values('Печенье', 149.99, 'Еда', 'WagonWiels');
insert into product values('Планшет', 203.99, 'Техника', 'Zua');
Explanation: Давайте создадим таблицу, заполним ее и сделаем какой-нибудь запрос!
End of explanation
%sql select * from product;
Explanation: Посмотрим на полученную таблицу.
End of explanation
%%sql
SELECT * from Product 
WHERE  category = 'Канцелярия' and manufacturer = 'Эксмо'
Explanation: Немного SQL терминологии
имя таблицы - product.
Каждая строка таблицы называется строкой или кортеж. 
Заметьте, что все кортежи имеют поля или атрибуты.
Количество строк называет мощностью, в то время как количество атрибутов арностью 
Соглашения схемы
Схема таблицы продукта может быть описана следующим образом:
product(<u>pname</u>, price, category, manufacturer)
Подчеркнутое множество атрибутов формирует  ключ.
В этом случае ключ - pname. Если имя продукта было бы только уникальным для указнного производителя мы бы написали:
product(<u>pname</u>, price, category, <u>manufacturer</u>)
Описание таблиц
Кортеж = запись
Ограничение: все атрибуты - атомарного типа
Существует множество атомарных типов для SQL, например, по postgresQL можно посмотреть здесь
  .
Таблица = мультимножество кортежей 
Мультимножество похоже на список
... Но по определению оно не упорядочено: 
нет first(), нет next(), нет last().
Итог
Создана база данных  -- сделано!
Простые запросы -- сейчас!
Запрос с более чем одной таблицей -- далее!
Простые запросы
Вспомним основы SQL на примере
В интернет очень много SQL туториалов, сейчас мы всего лишь вспомним базовые вещи
SELECT <Множество атрибутов><br>
FROM <список таблиц и условие на соединение><br>
WHERE <список условий>
Это простейший SELECT-FROM-WHERE (SFW) блок. Давайте посмотрим на примерах!
End of explanation
%%sql 
SELECT Pname, Price, Manufacturer
FROM Product;
Explanation: Посмотрим на примеры проекции, то есть получим только несколько атрибутов запроса.
End of explanation
%%sql
SELECT Pname, Price, Manufacturer
FROM Product
WHERE category='Еда';
Explanation: На выходе все еще таблица и ее схема -
Answer(pname, price, manufacturer)
Можно объединять выборку и проекцию
End of explanation
%%sql
SELECT
    p.manufacturer, p.pname, p.price
FROM 
    (SELECT distinct p0.category
     FROM Product p0
     WHERE p0.price < 20.00) cp, -- this is a nested query!
    Product p
WHERE 
    p.category = cp.category and p.price > 20.00
Explanation: На выходе запроса к таблице - снова таблица
End of explanation
%sql select * from product;
Explanation: Небольшие детали
Некоторые элементы регистро-независимые:
Одно и то же: SELECT  Select  select
Одно и то же: Product   product
Разные: ‘Seattle’  ‘seattle’
Константы (одинарные кавычки)
‘abc’  - да
“abc” - нет
End of explanation
%%sql
SELECT *  FROM product
where category LIKE '%да%'
Explanation: LIKE
Опертоор LIKE нужен для поиска строк:
SELECT *
FROM Products
WHERE pname like '%gizmo%'
Like - "сильно урезанный" regexp
* % - сколько угодно символов
* _ ровно один символ
* оператор LIKE - регистрозависимый
End of explanation
%sql SELECT category from product;
%%sql 
-- чтобы убрать дубли используйте слово DISTINCT
SELECT DISTINCT category from product;
Explanation: Убрать дубли
Дубли не всегда хорошо, и иногда их стоит убирать
Помните, что таблицы - мультимножества!
End of explanation
%%sql
-- сортировка результатов
-- сортировка по умолчанию - ascending
SELECT   pname, price, manufacturer
FROM     Product
WHERE    price > 50
ORDER BY  price, pname
%%sql
-- сортировка результатов
-- тип сортировки каждого компонента определяется индивидуально
SELECT   price, manufacturer
FROM     Product
ORDER BY   manufacturer ASC, price DESC
Explanation: Сортировка результатов
Так как Таблица - это мультимножество, то порядок вывода строк не гарантирован. Иногда необходимо выводить строки в определенном порядке
End of explanation
%%sql
drop table if exists product; -- This needs to be dropped if exists, see why further down!
drop table if exists company;
create table company (
    cname varchar primary key, -- company name uniquely identifies the company.
    stockprice money, -- stock price is in money 
    country varchar); -- country is just a string
insert into company values ('Тульский пряник', 25.0, 'Россия');
insert into company values ('Эксмо', 65.0, 'Россия');
insert into company values ('Zua', 15.0, 'Китай');
%sql select * from company;
Explanation: Соединения - JOIN
Рассмотрим таблицу компаний.
company(<u>cname</u>, stockprice, country)
End of explanation
%%sql drop table if exists product;
pragma foreign_keys = ON; -- WARNING by default off in sqlite
create table product(
       pname varchar primary key, -- name of the product
       price money, -- price of the product
       category varchar, -- category
       manufacturer varchar, -- manufacturer
       foreign key (manufacturer) references company(cname));
insert into product values('Пряник', 19.99, 'Еда', 'Тульский пряник');
insert into product values('Тетрадь', 29.99, 'Канцелярия', 'Эксмо');
insert into product values('Печенье', 149.99, 'Еда', 'Тульский пряник');
insert into product values('Планшет', 203.99, 'Техника', 'Zua');
Explanation: Внешние ключи
Допустим мы ходим добавить таблицу продуктов
Product(pname, price, category, manufacturer)
Может возникнуть следующая ситуация: есть компания, продающая какие-то продукты, но при этом она отсутствует в таблице компаний.
Чтобы избежать это, воспользуемя внешними ключами 
Назвнание компании в product ссылается на поле cnma из таблицы company:
foreign key (manufacturer) references company(cname)
Замечание: cname <u>должно быть</u> ключом в  company!
End of explanation
try:
    %sql insert into product values('MultiTouch', 203.99, 'Household', 'Google');
except Exception as e:
    print (e)
    print ("Rejected!")
%%sql
-- the update is rejected!
select * from product;
Explanation: Внешние ключи являются ограничениями таблицы 
Что случится при вставке компании, которой не в таблице company?
End of explanation
try:
    %sql delete from company where cname = 'Zua';
except Exception as e:
    print (e)
    print ("Disallowed!")
Explanation: Внешние ключи и удаление
Что произойдет, если мы удалим компанию ? Несколько вариантов:
Запретить удаление (default)
Удалить все продукты (добавьте "on delete cascade")
Замена на  NULL или DEFAULT
Первый вариант (default)- Удаление запрещено
End of explanation
%%sql
SELECT pname, price
FROM product, company
where manufacturer=cname and country='Россия' and price <= 200;
Explanation: Второй вариант: удалить все продукты, принадлежащие компании, которую мы удаляем
Необходимо изменить опеределение в create table:
foreign key (manufacturer) references company(cname) on delete cascade
Теперь, когда строка компании удалена, все продукты с указанной компанией, будут также удалены.
Соединения
Product (<u>pname</u>,  price, category, manufacturer)<br>
Company (<u>cname</u>, stockPrice, country)
Мы хотим ответить на вопрос
Найти все продукты меньшие 200$, произведенные в России
Возвратить их имена и цену.
End of explanation
%%sql -- Часть 1: выбрать все компании из России
SELECT distinct cname -- нужна ли нам уникальность?
from company where country='Россия';
%%sql -- Part 2: Продукты до 200
select distinct pname, price, manufacturer
from product
where price <= 200;
%%sql -- Объединение как cross join
SELECT * 
FROM 
  (SELECT DISTINCT pname, price, manufacturer
   FROM product
   WHERE price <= 200) CheapProducts,
  (SELECT DISTINCT cname
   FROM company
   WHERE country='Россия') JapaneseProducts;
%%sql
-- Фильтруем cross join
SELECT DISTINCT pname, price
FROM 
  (SELECT DISTINCT pname, price, manufacturer
   FROM product
   WHERE price <= 200) CheapProducts,
  (SELECT distinct cname
   FROM company
   WHERE country='Россия') JapaneseProducts
WHERE cname = manufacturer;
Explanation: Другой вариант написания запроса.
End of explanation
%%sql -- duplicate answer
SELECT Country
FROM Product, Company
WHERE  Manufacturer=CName AND Category='Еда';
Explanation: Примечания
Есть множество логических вариантов написать один и тот же запрос
Этот факт будет использоваться для оптимизации
Дубли после соединения
Замечание - могут возникнуть дубли после соединения...
End of explanation
<END_TASK>