Final Review: Practice Questions

For each snippet, write the output. If it prints nothing, write “No Output”.

Database

Every problem starts with a fresh copy of this SQLite database. NULL denotes a SQL NULL value. Rows are shown in id order.

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    age INTEGER
);
CREATE TABLE messages (
    id integer primary key,
    sender_id integer not null,
    message text not null,
    created_at timestamp not null default current_timestamp
    );

Initial rows:

users(id, username, password, age):
1 | Trump | Trump | 76
2 | Biden | Biden | 79
3 | Evan | correct horse battery staple | 4
4 | Isaac | soccer | 1
5 | Aaron | guaguagua | 0
6 | Teal | 524euTjrWm6uK2C5iw8mC6aNgX1JI78o | 35
7 | Kristen | Possible-Rich-Absolute-Battle | NULL

messages(id, sender_id, message, created_at):
1 | 1 | I'm a baby | 2021-11-14 14:30:00
2 | 2 | I'm a baby | 2021-11-14 14:30:00
3 | 3 | I'm a baby | 2021-11-14 14:33:01
4 | 4 | I'm a baby | 2021-11-15 14:35:45
5 | 3 | I'm actually a toddler | 2021-11-16 14:35:45
6 | 6 | Today in 1918, the Armistice that effectively ended WWI came into effect. | 2021-11-11 11:00:00
7 | 6 | I'm an adult | 2021-11-17 14:35:45
8 | 6 | SQL is the best!! | 2021-11-17 14:35:45
9 | 7 | I'm an adult | 2021-11-17 14:35:45
10 | 7 | WTF is SQL?!  I thought you liked the snake thing. | 2021-11-17 14:35:45

Question 1

Use the database above.

import sqlite3

con = sqlite3.connect('twitter_clone.db')
cur = con.cursor()
sql = """
SELECT count(*) FROM users WHERE password LIKE '%a%';
"""
cur.execute(sql)
for row in cur.fetchall():
    print('row[0]=', row[0])

Answer

row[0]= 4

Question 2

Use the database above.

import sqlite3

con = sqlite3.connect('twitter_clone.db')
cur = con.cursor()

sql = """
INSERT INTO users (password, username) VALUES ('example', 'password');
"""
cur.execute(sql)
con.commit()

sql = """
SELECT password,username FROM users WHERE username='password';
"""
cur.execute(sql)
for row in cur.fetchall():
    print('row[0]=', row[0])
    print('row[1]=', row[1])

Answer

row[0]= example
row[1]= password

Question 3

Use the database above.

import sqlite3

con = sqlite3.connect('twitter_clone.db')
cur = con.cursor()

username = "example', 'password'), ('example2"
password = 'password'
sql = (
    """
INSERT INTO users (username, password) VALUES ('"""
    + username
    + """', '"""
    + password
    + """');
"""
)
cur.execute(sql)
con.commit()

sql = """
SELECT id,password FROM users WHERE username LIKE 'example%' ORDER BY id;
"""
cur.execute(sql)
for row in cur.fetchall():
    print('row[0]=', row[0])
    print('row[1]=', row[1])

Answer

row[0]= 8
row[1]= password
row[0]= 9
row[1]= password

Question 4

Use the database above.

import sqlite3

con = sqlite3.connect('twitter_clone.db')
cur = con.cursor()

username = "example', 'password'), ('example2"
password = 'password'
sql = """
INSERT INTO users (username, password) VALUES (?, ?);
"""
cur.execute(sql, [username, password])
con.commit()

sql = """
SELECT id,password FROM users WHERE username='example';
"""
cur.execute(sql)
for row in cur.fetchall():
    print('row[0]=', row[0])
    print('row[1]=', row[1])

Answer

No Output

Question 5

Use the database above.

import sqlite3

con = sqlite3.connect('twitter_clone.db')
cur = con.cursor()

username = "Teal' OR username='TRUMP"
sql = (
    """
SELECT id,password FROM users WHERE username='"""
    + username
    + """';
"""
)
cur.execute(sql)
user_ids = []
for row in cur.fetchall():
    user_ids.append(row[0])

for user_id in user_ids:
    sql = """
    SELECT count(*) FROM messages WHERE sender_id=?;
    """
    cur.execute(sql, [user_id])
    for row in cur.fetchall():
        print('row[0]=', row[0])

Answer

row[0]= 3