notebook.community

Edit and run



In [5]:

    
%load_ext sql
%sql sqlite://









    



The sql extension is already loaded. To reload it, use:
  %reload_ext sql






    Out[5]:





'Connected: None@None'



In [6]:

    
# Create tables & insert some random numbers
# Note: in Postgresql, try the generate_series function...
%sql DROP TABLE IF EXISTS R; DROP TABLE IF EXISTS S; DROP TABLE IF EXISTS T;
%sql CREATE TABLE R (A int); CREATE TABLE S (A int); CREATE TABLE T (A int);
for i in range(1,6):
    %sql INSERT INTO R VALUES (:i)
for i in range(1,10,2):
    %sql INSERT INTO S VALUES (:i)
for i in range(1,11,3):
    %sql INSERT INTO T VALUES (:i)









    



5 rows affected.
5 rows affected.
5 rows affected.
5 rows affected.
5 rows affected.
5 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.



In [7]:

    
%%sql
drop table if exists product; -- This needs to be dropped if exists, see why further down!
drop table if exists company;
pragma foreign_keys = ON; -- WARNING by default off in sqlite
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 ('ToyWorks', 25.0, 'USA');
insert into company values ('ToyFriends', 65.0, 'China');
insert into company values ('ToyCo', 15.0, 'China');

create table product(
       pname varchar, -- name of the product
       price money, -- price of the product
       category varchar, -- category
       manufacturer varchar, -- manufacturer
       primary key (pname, manufacturer),
       foreign key (manufacturer) references company(cname));
insert into product values('Pikachu', 19.99, 'Toy', 'ToyWorks');
insert into product values('Pikachu', 19.99, 'Toy', 'ToyFriends');
insert into product values('Pokeball', 29.99, 'Electronic', 'ToyCo');
insert into product values('Bulbasaur', 149.99, 'Toy', 'ToyFriends');
insert into product values('Charizard', 203.99, 'Toy', 'ToyCo');
insert into product values('PokeCamera', 19.99, 'Electronic', 'ToyWorks');









    



1 rows affected.
1 rows affected.
Done.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.
1 rows affected.






    Out[7]:





[]

Лабораторная 2-2

Многотабличный запросы

Задание 1 #1:

Для 3 таблиц $R,S,T$, у которых есть атрибут $A$:

R = {1,2,3,4,5}
S = {1,3,5,7,9}
T = {1,4,7,10}

Напишите запрос для выбора $R \cap (S \cup T)$- Другими словами элементы $R$ и либо в $S$, либо в $T$?



In [ ]:

Теперь протестируйте ваш запрос для случая, где $S = \emptyset$- Что произошло и почему?

Выполните запрос снизу, и еще раз выполните верхний запрос



In [4]:

    
%%sql
delete from S;









    



5 rows affected.






    Out[4]:





[]

Задание #2

Схема аналогичная той, что в лекции

Product (pname, price, category, manufacturer)
Company (cname, stockPrice, country)

Найти все категорри продуктов, сделанные китайскими компаниями

Напишите ваш запрос здесь:



In [ ]:



In [ ]: