Skip to content

exercises.md

Latest commit

 

History

History
211 lines (119 loc) · 5.99 KB

exercises.md

File metadata and controls

211 lines (119 loc) · 5.99 KB

Section 1 — Insert and Query Data

What you'll practice: putting rows into a table with INSERT, getting them back out with SELECT, aliasing columns, sorting with ORDER BY, and removing duplicates with DISTINCT. This is the first section where you'll feel like you're actually using SQL.

Learning objectives

  • Write INSERT INTO statements for single and multiple rows
  • Write SELECT queries that pull specific columns, all columns, or computed expressions
  • Give columns friendlier names with AS (column aliases)
  • Sort results with ORDER BY (ascending and descending)
  • Remove duplicate values with SELECT DISTINCT

How to run these exercises

Connect to your practice database:

psql -U postgres -d sql_exercise

Load the schema and seed data from your terminal (not from inside psql):

psql -U postgres -d sql_exercise \
     -f 02-sql-fundamentals/01-insert-and-query-data/schema.sql

psql -U postgres -d sql_exercise \
     -f 02-sql-fundamentals/01-insert-and-query-data/seed.sql

Back inside psql, point your session at the section's schema:

SET search_path TO sqlf_insert_query;

Note: SET search_path only lasts for your current psql session. If you quit and come back, run it again.

Quick check that the seed loaded:

SELECT COUNT(*) FROM person;
-- Expected: 40

Exercises

Exercise 1 — Select everything

Write a query that returns every row and every column from person.

Verify: you should get 40 rows back.

Hint: the star * means "all columns".

Exercise 2 — Select just the names

Return only first_name and last_name for every person.

Question: why would you ever prefer this over SELECT * in real application code?

Exercise 3 — Insert a new person

Add yourself (or a fictional person of your choosing) to the person table. Use id 41. Fill in every column — make phone_number or email NULL if you want.

Verify:

SELECT * FROM person WHERE id = 41;

Exercise 4 — Insert three more people in a single statement

Add ids 42, 43, and 44 in one INSERT statement.

Hint: INSERT INTO person (...) VALUES (...), (...), (...); — one comma-separated row tuple per person.

Verify: SELECT COUNT(*) FROM person; should now show 44.

Exercise 5 — Insert a row with a column subset

Insert a person with id 45 where you only supply id, first_name, last_name, gender, dob, and country_of_origin. Omit email and phone_number entirely.

Question: why does this work? What value ends up in the omitted columns?

Verify:

SELECT id, first_name, email, phone_number FROM person WHERE id = 45;

Exercise 6 — Alias a column

Write a query that returns first_name and last_name, but rename them to given_name and family_name in the output.

Hint: SELECT first_name AS given_name, ....

Exercise 7 — Concatenate into a full name

Return a single column called full_name that contains first_name and last_name joined with a space.

Hint: Postgres uses || as the string concatenation operator: first_name || ' ' || last_name.

Verify: the output should look like a list of Ahmed Khan, Aisha Ali, etc.

Exercise 8 — Order by last name ascending

Return first_name, last_name, and country_of_origin, sorted alphabetically by last_name.

Hint: ORDER BY last_name (ASC is the default).

Exercise 9 — Order by date of birth descending

Return first_name, last_name, and dob, sorted so the youngest person is first.

Question: is the youngest person the one with the largest or smallest dob?

Exercise 10 — Order by two columns

Return first_name, last_name, and country_of_origin, sorted first by country_of_origin ascending, then by last_name ascending within each country.

Hint: ORDER BY country_of_origin, last_name.

Exercise 11 — Distinct countries

Return the list of unique countries in the person table — no duplicates.

Hint: SELECT DISTINCT ....

Question: how many distinct countries are there? (Use COUNT(*) in a follow-up query or eyeball it.)

Exercise 12 — Distinct (country, gender) pairs

Return every unique combination of country_of_origin and gender, sorted by country.

Verify: each country should appear at most twice (once for M, once for F) — unless the seed only has one gender from that country.

Exercise 13 — Select with a computed column

Return first_name, last_name, and a computed column called initials that contains the first letter of first_name, a dot, the first letter of last_name, and another dot — e.g. A.K. for Ahmed Khan.

Hint: combine || with Postgres' LEFT(string, 1) function, or with SUBSTRING.

Exercise 14 — Top 5 oldest people

Return the 5 oldest people (by dob) — show first_name, last_name, and dob.

Hint: ORDER BY dob ASC LIMIT 5. (LIMIT is covered properly in section 10, but it's fine to peek ahead.)

Exercise 15 — Compare an inserted row with the seed

Run:

SELECT id, first_name, last_name FROM person ORDER BY id DESC LIMIT 5;

Question: are your inserts from exercises 3, 4, and 5 there? If you re-run schema.sql + seed.sql, what happens to those rows?

Bonus challenges

Bonus 1 — Handle a duplicate id

Try inserting another person with id 1. What error do you get? (There's no PK constraint yet, so actually it'll succeed — and that's a problem you'll fix in section 14.)

Bonus 2 — Shouty output

Write a query that returns everyone's first_name and last_name in ALL UPPERCASE, aliased as first_name_upper and last_name_upper. (Google UPPER().)

Bonus 3 — Two ORDER BY directions

Sort by country_of_origin ascending, then by dob descending within each country.

Cleanup

When you're done with the section:

DROP SCHEMA sqlf_insert_query CASCADE;