Backend Web Development: 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 | 78
2 | Biden | Biden | 81
3 | Evan | correct horse battery staple | 7
4 | Isaac | soccer | 4
5 | Aaron | guaguagua | 3
6 | Aurelia |  | 1
7 | Teal | 524euTjrWm6uK2C5iw8mC6aNgX1JI78o | 35
8 | 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 id,password FROM users WHERE username='Trump';
"""
cur.execute(sql)
for row in cur.fetchall():
    print('row[0]=', row[0])
    print('row[1]=', row[1])

Answer

row[0]= 1
row[1]= Trump

Question 2

Use the database above.

import sqlite3

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

Answer

row[0]= 9

Question 3

Use the database above.

import sqlite3

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

sql = """
INSERT INTO users (username, password) VALUES ('example', 'password');
"""
cur.execute(sql)
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

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

Question 4

Use the database above.

import sqlite3

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

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

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

Answer

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

Question 5

Use the database above.

import sqlite3

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

username = 'example'
password = 'password'
sql = (
    """
INSERT INTO users (username, password) VALUES ('"""
    + username
    + """', '"""
    + password
    + """');
"""
)
cur.execute(sql)
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

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

Question 6

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]= 9
row[1]= password
row[0]= 10
row[1]= password

Question 7

Use the database above.

import sqlite3

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

username = 'example'
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

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

Question 8

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 9

Use the database above.

import sqlite3

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

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

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

Answer

row[0]= 9
row[1]= example

Question 10

Use the database above.

import sqlite3

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

username = 'Teal'
sql = """
SELECT id,password FROM users WHERE username=?;
"""
cur.execute(sql, [username])
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]= 2

Question 11

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
    + """' ORDER BY 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]= 2
row[0]= 1

Question 12

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=?;
"""
cur.execute(sql, [username])
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

No Output

Question 13

Use the database above.

import sqlite3

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

username = 'Teal'
sql = (
    """
SELECT id 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 id, sender_id, message, created_at FROM messages WHERE sender_id="""
        + str(user_id)
        + """ ORDER BY id;
    """
    )
    cur.execute(sql)
    for row in cur.fetchall():
        print(
            'id=',
            row[0],
            'sender_id=',
            row[1],
            'message=',
            row[2],
            'created_at=',
            row[3],
        )

Answer

id= 9 sender_id= 7 message= I'm an adult created_at= 2021-11-17 14:35:45
id= 10 sender_id= 7 message= WTF is SQL?!  I thought you liked the snake thing. created_at= 2021-11-17 14:35:45

Question 14

Use the database above.

import sqlite3

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

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

for user_id in user_ids:
    sql = """
    SELECT id, sender_id, message, created_at FROM messages WHERE sender_id=? ORDER BY id;
    """
    cur.execute(sql, [user_id])
    for row in cur.fetchall():
        print(
            'id=',
            row[0],
            'sender_id=',
            row[1],
            'message=',
            row[2],
            'created_at=',
            row[3],
        )

Answer

id= 9 sender_id= 7 message= I'm an adult created_at= 2021-11-17 14:35:45
id= 10 sender_id= 7 message= WTF is SQL?!  I thought you liked the snake thing. created_at= 2021-11-17 14:35:45

Question 15

Use the database above.

import sqlite3

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

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

for user_id in user_ids:
    sql = (
        """
    SELECT id, sender_id, message, created_at FROM messages WHERE sender_id="""
        + str(user_id)
        + """ ORDER BY id;
    """
    )
    cur.execute(sql)
    for row in cur.fetchall():
        print(
            'id=',
            row[0],
            'sender_id=',
            row[1],
            'message=',
            row[2],
            'created_at=',
            row[3],
        )

Answer

id= 9 sender_id= 7 message= I'm an adult created_at= 2021-11-17 14:35:45
id= 10 sender_id= 7 message= WTF is SQL?!  I thought you liked the snake thing. created_at= 2021-11-17 14:35:45
id= 1 sender_id= 1 message= I'm a baby created_at= 2021-11-14 14:30:00