SQL: Practice Questions

For each statement, write its output rows. If it returns no rows, write “No Output”.

Database

Each question starts with a fresh database created by these statements.

----------------------------------------
-- users
----------------------------------------

CREATE TABLE users (
    id INTEGER PRIMARY KEY,
    username TEXT NOT NULL UNIQUE,
    password TEXT NOT NULL,
    age INTEGER
);

INSERT INTO users (username, password, age) VALUES ('Trump', 'TRUMP', 76);
INSERT INTO users (username, password, age) VALUES ('Biden', '12345', 79);
INSERT INTO users (username, password, age) VALUES ('Evan', 'correct horse battery staple', 7);
INSERT INTO users (username, password, age) VALUES ('Isaac', 'soccer', 4);
INSERT INTO users (username, password, age) VALUES ('Aaron', 'guaguagua', 3);
INSERT INTO users (username, password, age) VALUES ('Aurelia', '', 2);
INSERT INTO users (username, password, age) VALUES ('Teal', '524euTjrWm6uK2C5iw8mC6aNgX1JI78o', 38);
INSERT INTO users (username, password) VALUES ('Kristen', 'Possible-Rich-Absolute-Battle');

----------------------------------------
-- messages
----------------------------------------

create table messages (
    id integer primary key,
    sender_id integer not null references users(id),
    message text not null,
    created_at timestamp not null default current_timestamp
);

insert into messages (sender_id,message,created_at) values
    (1, 'I''m a baby', '2022-11-14 14:30:00'),
    (2, 'I''m a baby', '2022-11-14 14:30:00'),
    (3, 'I''m a baby', '2022-11-14 14:33:01'),
    (4, 'I''m a baby', '2022-11-15 14:35:45');

insert into messages (sender_id,message,created_at) values
    (3, 'I''m actually a toddler', '2022-11-16 14:35:45');
insert into messages (sender_id,message,created_at) values
    (6, 'Today in 1918, the Armistice that effectively ended WWI came into effect.', '2022-11-11 11:00:00');
insert into messages (sender_id,message,created_at) values
    (6, 'I''m an adult', '2022-11-17 14:35:25'),
    (6, 'SQL is the best!!', '2022-11-17 15:52:45'),
    (7, 'I''m an adult', '2022-11-17 16:12:21'),
    (7, 'WTF is SQL?!  I thought you liked the snake thing.', '2022-11-17 15:53:47');

Question 1

Use the database above.

SELECT id FROM users WHERE username = 'Trump';

Answer

1

Question 2

Use the database above.

SELECT id FROM users WHERE username = 'trump';

Answer

No Output

Question 3

Use the database above.

SELECT id FROM users WHERE username LIKE 'trump';

Answer

1

Question 4

Use the database above.

SELECT id FROM users WHERE username LIKE 'TRUMP';

Answer

1

Question 5

Use the database above.

SELECT id FROM users WHERE username LIKE 'a%' ORDER BY id;

Answer

5
6

Question 6

Use the database above.

SELECT id FROM users WHERE username LIKE '%a';

Answer

6

Question 7

Use the database above.

SELECT id FROM users WHERE username LIKE '%a%' ORDER BY username;

Answer

5
6
3
4
7

Question 8

Use the database above.

SELECT id FROM users WHERE age IS NULL;

Answer

8

Question 9

Use the database above.

SELECT id FROM users ORDER BY username DESC;

Answer

1
7
8
4
3
2
6
5

Question 10

Use the database above.

SELECT id FROM users ORDER BY age ASC;

Answer

8
6
5
4
3
7
1
2

Question 11

Use the database above.

SELECT id FROM users ORDER BY username ASC;

Answer

5
6
2
3
4
8
7
1

Question 12

Use the database above.

SELECT count(*) FROM messages;

Answer

10

Question 13

Use the database above.

SELECT count(*) FROM messages WHERE sender_id=4;

Answer

1

Question 14

Use the database above.

SELECT count(*) FROM messages WHERE sender_id < 4;

Answer

4

Question 15

Use the database above.

DELETE FROM messages;
SELECT count(*) FROM messages;

Answer

0

Question 16

Use the database above.

DELETE FROM messages WHERE sender_id=1;
SELECT count(*) FROM messages;

Answer

9

Question 17

Use the database above.

DELETE FROM messages WHERE message LIKE '%a%';
SELECT count(*) FROM messages;

Answer

1

Question 18

Use the database above.

DELETE FROM messages WHERE sender_id=3 OR sender_id=0;
SELECT count(*) FROM messages;

Answer

8

Question 19

Use the database above.

UPDATE users SET password='@realdonaldtrump';
SELECT count(*) FROM users WHERE password LIKE '@%';

Answer

8

Question 20

Use the database above.

UPDATE users SET username='@realdonaldtrump' WHERE id=1;
SELECT count(*) FROM users WHERE username LIKE '@%';

Answer

1