[go: up one dir, main page]
Scribd
Upload
60 views2 pages

PWD 2019 20 Labs 7 SQLite Exercise PDF

The document provides instructions for a SQLite tutorial lab exercise. It includes commands to create tables for authors, books, and sales data with sample data. It then lists queries to join the tables and retrieve sample data. Finally, it provides 19 questions to write SQLite queries to manipulate a users table, insert sample data, update, delete and retrieve records.

Uploaded by

Guille FK
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
60 views2 pages

PWD 2019 20 Labs 7 SQLite Exercise PDF

The document provides instructions for a SQLite tutorial lab exercise. It includes commands to create tables for authors, books, and sales data with sample data. It then lists queries to join the tables and retrieve sample data. Finally, it provides 19 questions to write SQLite queries to manipulate a users table, insert sample data, update, delete and retrieve records.

Uploaded by

Guille FK
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
You are on page 1/ 2

PwD 2019-20 Winter – Lab 7

• Download and run the SQLite.ext application


• Copy & paste the below commands to see the effect; query the application as you
see need to visualise the effects of the commands.
• Proceed to answer and run the questions below, both baseline code and a solution
sheet will be released.

SQLite Tutorial

CREATE TABLE AUTHORS (AUTHOR_ID TEXT PRIMARY KEY, AUTHOR_FIRSTNAME TEXT, AUTHOR_LASTNAME
TEXT, AUTHOR_COUNTRY TEXT, AUTHOR_GENDER TEXT);

INSERT INTO AUTHORS VALUES ('A1', 'William', 'Shakespeare','UK','M'),


('A2', 'Ernest', 'Hemingway ','US','M'),
('A3', 'Virginia', 'Woolf ','UK','F'),
('A4', 'Agatha', 'Christie ','UK','F');

CREATE TABLE BOOKS (BOOK_ID TEXT PRIMARY KEY, BOOK_TITLE TEXT,


BOOK_PUB_YEAR INTEGER, AUTHOR_ID TEXT,
FOREIGN KEY(AUTHOR_ID) REFERENCES AUTHORS(AUTHOR_ID));

INSERT INTO BOOKS VALUES ('B1','Sonnet 116', 1609, 'A1'),


('B2','Sonnet 130', 1609, 'A1'),
('B3','The old man and the sea', 1952, 'A2'),
('B4','The sun also rises', 1926, 'A2'),
('B5','Mrs Dalloway', 1925, 'A3'),
('B6','The Waves', 1931, 'A3'),
('B7','The A.B.C. Murders', 1936, 'A4'),
('B8','The Adventure of the Clapham Cook', 1924, 'A4');

SELECT * FROM AUTHORS INNER JOIN BOOKS ON BOOKS.AUTHOR_ID = AUTHORS.AUTHOR_ID;

SELECT * FROM AUTHORS, BOOKS WHERE BOOKS.AUTHOR_ID = AUTHORS.AUTHOR_ID AND


AUTHORS.AUTHOR_ID = 'A1';

CREATE TABLE SALES (SALE_ID TEXT PRIMARY KEY, BOOK_ID INTEGER, SALE_DATE DATE, FOREIGN
KEY(BOOK_ID) REFERENCES BOOKS(BOOK_ID));

INSERT INTO SALES VALUES ('S1','B1', 2019-10-15),


('S2','B2', 2019-10-17),
('S3','B2', 2019-10-20),
('S4','B7', 2019-10-23),
('S5','B8', 2019-10-25),
('S6','B5', 2019-10-25),
('S7','B7', 2019-10-29);

SELECT * FROM AUTHORS, BOOKS WHERE BOOKS.AUTHOR_ID = AUTHORS.AUTHOR_ID ;


SELECT * FROM SALES, BOOKS WHERE SALES.BOOK_ID = BOOKS.BOOK_ID ;

SELECT * FROM AUTHORS, BOOKS, SALES WHERE AUTHORS.AUTHOR_ID = BOOKS.AUTHOR_ID AND


BOOKS.BOOK_ID = SALES.BOOK_ID;

SELECT COUNT(AUTHORS.AUTHOR_ID),* FROM AUTHORS, BOOKS, SALES WHERE AUTHORS.AUTHOR_ID =


BOOKS.AUTHOR_ID AND BOOKS.BOOK_ID = SALES.BOOK_ID GROUP BY AUTHORS.AUTHOR_ID;
SQLite exercises

1. Create a new connection to the sqlite3 library and a new cursor to run queries

2. Create a table called users with the following structure


Column Data type Constraints
name
id Number (Integer) key
name Text
phone Text
email Text Unique values
password Text
city Text
salary Number (Integer)

3. Insert the following data:


names = ['Stelios','Mary','John','Andrew','George', 'Stefan']
phones = ['37288','34992','49228','32991','34643','39911']
emails =
['ste@dcs.com','mary@dcs.com','john@bbk.ac.uk','and@bbk.com','geo@bbk.com','stef@
bbk.com']
passwords = ['1234','4122','2455','4632','5995','9911']
cities = ['London','London','Paris','Paris','London','London']
salary = ['100', '120', '140', '175', '200', '220']

4. Insert the data from the lists (of question 3).


5. Print all data from the table users
6. Print name, email and phone for data in table users
7. Search and print name, email and phone for user Stelios from users table
8. Update the phone number of a user with user id 1 to 55555
9. Show the data from users table for user with id 1 to be sure that the update statement
has been executed correctly.
10. Delete the user with user id 1
11. Search for users with phone starting with 3
12. Search for users with phone starting with 3 and their name is Stefan
13. Search for users with email ending bbk.com
14. Search for users with email from bbk order by names in ascending
15. Count how many users live in each city
16. Count how many users live in each city and show the sum of the salary per city
17. Show the sum of the salaries per city for cities having sum of salaries greater than 350
18. Select name, email, phone where salary between 100,150
19. Select city, maximum salary, minimum salary, average salary, sum of salaries from
users grouping by city.

You might also like