import sqlite3
conn = sqlite3.connect('people.db')
cursor_obj = conn.cursor()
cursor_obj.execute('''
select * from people_main
limit 20;
''')
cursor_obj.fetchall()
# You can get the column names using description
cursor_obj.description
for k in cursor_obj.description:
print(k[0])
cursor_obj.execute('''
select name, age, occupation from people_main
where occupation in ('Librarians', 'Chefs')
order by age desc;
''')
cursor_obj.fetchall()[1:10]
cursor_obj.execute('''
SELECT name FROM sqlite_master WHERE type='table';
''')
cursor_obj.fetchall()
cursor_obj.execute('''
SELECT count(ID) FROM people_main;
''')
cursor_obj.fetchall()
cursor_obj.execute('''
SELECT count(ID), occupation FROM people_main
GROUP BY occupation;
''')
cursor_obj.fetchall()[:10]
cursor_obj.execute('''
SELECT * FROM people_main
JOIN people_likes on people_main.ID = people_likes.ID1
ORDER BY people_main.ID
LIMIT 10;
''')
cursor_obj.fetchall()
cursor_obj.execute('''
SELECT occupation, count(*) FROM people_main
GROUP BY occupation
having count(*) > 5;
''')
cursor_obj.fetchall()
# Write a SQL statement that returns the name and number of people that he likes.
# Results should be ordered by count (descending), and then by name (A-Z).
cursor_obj.execute('''
select name, count(*) from people_main
join people_likes
on people_main.id = people_likes.id1
group by people_main.id
order by count(*) desc, name;
''')
cursor_obj.fetchall()[:15]
# Write a SQL statement to find pairs (A, B) such that person A and B are friends
cursor_obj.execute('''
with tmp as (
select * from people_main
join people_likes
on people_main.ID = people_likes.ID1
)
select tmp.name, people_main.name from tmp
join people_main on tmp.ID2 = people_main.ID
''')
cursor_obj.fetchall()[:10]
#Write a SQL statement that returns the occupation and and how many people like that occupation.
#Results should be ordered by popularity from high to low.
cursor_obj.execute('''
select occupation, count(*) as count from people_main
join people_likes
on people_main.id = people_likes.id2
group by people_main.occupation
order by count(*) desc
''')
cursor_obj.fetchall()[:10]
#Write a SQL statement that returns the name and occupation of all people who have more than 3 friends.
#Results should be ordered by name (A-Z).
cursor_obj.execute('''
select name, occupation, count(*) from people_main
join people_friends
on people_main.id = people_friends.id1
group by people_main.id
having count(*) >= 3;
''')
cursor_obj.fetchall()[:10]
#Write a SQL statement that returns the distinct name and age of all people who are liked by anyone younger than them.
#Results should be ordered by name (A-Z)
cursor_obj.execute('''
with tmp as (
select * from people_main
join people_likes
on people_main.id = people_likes.id1
)
select tmp.name, tmp.age, people_main.name, people_main.age from tmp
join people_main
on tmp.id2 = people_main.id
where tmp.age > people_main.age
''')
cursor_obj.fetchall()[:10]
#Write a SQL statement to find pairs (A, B) such that person A likes person B, but A is not friends with B.
#Results should be ordered by ID1 (ascending), then ID2 (ascending).
cursor_obj.execute('''
select distinct people_likes.* from people_likes
left join people_friends
on people_likes.id1 = people_friends.id1
order by people_likes.id1;
''')
cursor_obj.fetchall()[:10]
# Practice questions
conn = sqlite3.connect('computer.db')
cursor_obj = conn.cursor()
cursor_obj.execute('''
CREATE TABLE Manufacturers (
Code INTEGER PRIMARY KEY NOT NULL,
Name CHAR(50) NOT NULL
);
''')
cursor_obj.execute('''
CREATE TABLE Products (
Code INTEGER PRIMARY KEY NOT NULL,
Name CHAR(50) NOT NULL ,
Price REAL NOT NULL ,
Manufacturer INTEGER NOT NULL
CONSTRAINT fk_Manufacturers_Code REFERENCES Manufacturers(Code)
);
''')
cursor_obj.execute('''
INSERT INTO Manufacturers(Code,Name) VALUES (1,'Sony'),
(2,'Creative Labs'),
(3,'Hewlett-Packard'),
(4,'Iomega'),
(5,'Fujitsu'),
(6,'Winchester');
''')
cursor_obj.execute('''
INSERT INTO Products(Code,Name,Price,Manufacturer) VALUES
(1,'Hard drive',240,5),
(2,'Memory',120,6),
(3,'ZIP drive',150,4),
(4,'Floppy disk',5,6),
(5,'Monitor',240,1),
(6,'DVD drive',180,2),
(7,'CD drive',90,2),
(8,'Printer',270,3),
(9,'Toner cartridge',66,3),
(10,'DVD burner',180,2);
''')
cursor_obj.execute('''
select * from Products;
''')
cursor_obj.fetchall()
# 1. Select the names and the prices of all the products in the store.
cursor_obj.execute('''
select name, price from products;
''')
cursor_obj.fetchall()
# 2. Select the name of the products with a price less than or equal to $200.
cursor_obj.execute('''
select name from products
where price <= 200;
''')
cursor_obj.fetchall()
# 3. Select the name and price in cents (i.e., the price must be multiplied by 100).
cursor_obj.execute('''
select name, price*100 from products;
''')
cursor_obj.fetchall()
# 4. Compute the average price of all the products.
cursor_obj.execute('''
select avg(price) from products;
''')
cursor_obj.fetchall()
# 5. Select the name and price of all products with a price larger than or equal to $180, and sort first by price
# (in descending order), and then by name (in ascending order).
cursor_obj.execute('''
select name, price from products
where price >= 180
order by price desc, name;
''')
cursor_obj.fetchall()
# 6. Select the name and price of the cheapest product.
cursor_obj.execute('''
SELECT name,price
FROM Products
ORDER BY price ASC
LIMIT 1
''')
cursor_obj.fetchall()
# 7. Select the average price of each manufacturer's products, showing the manufacturer's name.
cursor_obj.execute('''
SELECT avg(products.price), manufacturers.name from Manufacturers
join products on manufacturers.code = products.code
group by Manufacturers.name
''')
cursor_obj.fetchall()