SQL Tutorial for Beginners 4: SELECT - Retrieving Records from Your Database

Q: How does `SELECT * FROM people` differ from selecting specific columns?

`SELECT * FROM people` returns every column in the `people` table for all matching rows (in the example, all 20 records). When the query lists columns explicitly—`SELECT first name, last name FROM people`—it still returns all rows, but only the requested fields (`first name` and `last name`).

Q: Why are quotes used in `WHERE last name = 'Doe'`?

The `last name` column contains string (text) values, so the literal being matched must be written as a quoted string. Without quotes, the database would not treat `Doe` as a text value to compare against the column.

Q: What changes when `WHERE` conditions use `OR` vs `AND`?

With `OR`, either condition being true is enough: `WHERE last name = 'Doe' OR last name = 'Smith'` returns rows matching either last name. With `AND`, both conditions must be true: `WHERE last name = 'Doe' AND age < 30` returns only rows where the last name matches and the age is under 30. Swapping `AND` for `OR` can increase results because one condition alone can qualify a row.

Q: How does `ORDER BY` determine the sort direction?

`ORDER BY age` sorts from lowest to highest by default (youngest to oldest). To sort the opposite way, add `DESC`: `ORDER BY age DESC` sorts from highest to lowest (oldest to youngest). `ASC` can be used explicitly for ascending order, though it’s the default.

Q: How can sorting on multiple columns fix tie situations?

Sorting on one column may leave ties unresolved. For example, `ORDER BY first name` orders by `first name` but still needs a rule when multiple rows share the same first name (like `John Smith` and `John Doe`). Adding a second key—`ORDER BY first name, last name`—orders by `first name` first, then by `last name` to produce a deterministic order.

TL;DR

Use `SELECT * FROM people` to retrieve all columns, or list specific columns like `SELECT first name, last name` to limit output.

Briefing Cornell Notes

Briefing

Retrieving specific data from a database starts with the SQL `SELECT` statement, and the practical path is: choose which columns to return, filter rows with `WHERE`, combine conditions with `AND`/`OR`, and then sort the results with `ORDER BY`. Using a sample `people` table (with columns `ID`, `first name`, `last name`, `age`, and `occupation`), the tutorial demonstrates how each clause changes what comes back.

The first step is pulling data from the table. `SELECT * FROM people` returns all columns for all 20 sample records. If only certain fields are needed, the query can list them explicitly—`SELECT first name, last name FROM people`—which returns the same set of rows but only those two columns. Once the right columns are selected, filtering narrows the rows. Adding a `WHERE` clause lets results match a condition, such as `WHERE last name = 'Doe'`. Because `last name` contains text, string values are wrapped in quotes.

Filtering becomes more flexible when multiple conditions are combined. Using `OR` expands the match criteria: `WHERE last name = 'Doe' OR last name = 'Smith'` returns rows where either last name matches. Using `AND` tightens the requirements: `WHERE last name = 'Doe' AND age < 30` returns only rows where both the last name and age condition are satisfied. A key takeaway is how `AND` vs `OR` changes the outcome—switching `OR` in place of `AND` can dramatically increase the number of returned rows because either condition alone becomes sufficient.

After rows are filtered, sorting determines the order they appear in. `ORDER BY` sorts by a chosen column; for example, ordering by `age` sorts the results from youngest to oldest by default. To reverse that direction, the tutorial uses `ORDER BY age DESC`, where `DESC` means descending order (oldest to youngest). Ascending order is the default, though it can be made explicit with `ASC`.

The sorting section also shows multi-column ordering. `ORDER BY first name` sorts primarily by `first name`, but ties remain unresolved when multiple people share the same first name (e.g., `John Doe` vs `John Smith`). Adding a second sort key—`ORDER BY first name, last name`—breaks ties by ordering first by `first name` and then by `last name`, producing the expected sequence. Overall, the workflow demonstrated—`SELECT` for columns, `WHERE` for row filtering, `AND`/`OR` for logic, and `ORDER BY` for ordering—forms the foundation for writing useful database queries.

Cornell Notes

The core skill is using SQL `SELECT` to retrieve data, then refining results with `WHERE` and sorting them with `ORDER BY`. A sample `people` table is used to show `SELECT *` for all columns and explicit column lists like `SELECT first name, last name`. Filtering uses `WHERE last name = 'Doe'`, and logic expands with `OR` (either condition) and tightens with `AND` (both conditions). Sorting uses `ORDER BY age` (youngest to oldest by default) and `ORDER BY age DESC` for oldest to youngest. Multi-column sorting with `ORDER BY first name, last name` resolves ties when first names match.

How does `SELECT * FROM people` differ from selecting specific columns?

`SELECT * FROM people` returns every column in the `people` table for all matching rows (in the example, all 20 records). When the query lists columns explicitly—`SELECT first name, last name FROM people`—it still returns all rows, but only the requested fields (`first name` and `last name`).

Why are quotes used in `WHERE last name = 'Doe'`?

The `last name` column contains string (text) values, so the literal being matched must be written as a quoted string. Without quotes, the database would not treat `Doe` as a text value to compare against the column.

What changes when `WHERE` conditions use `OR` vs `AND`?

With `OR`, either condition being true is enough: `WHERE last name = 'Doe' OR last name = 'Smith'` returns rows matching either last name. With `AND`, both conditions must be true: `WHERE last name = 'Doe' AND age < 30` returns only rows where the last name matches and the age is under 30. Swapping `AND` for `OR` can increase results because one condition alone can qualify a row.

How does `ORDER BY` determine the sort direction?

`ORDER BY age` sorts from lowest to highest by default (youngest to oldest). To sort the opposite way, add `DESC`: `ORDER BY age DESC` sorts from highest to lowest (oldest to youngest). `ASC` can be used explicitly for ascending order, though it’s the default.

How can sorting on multiple columns fix tie situations?

Sorting on one column may leave ties unresolved. For example, `ORDER BY first name` orders by `first name` but still needs a rule when multiple rows share the same first name (like `John Smith` and `John Doe`). Adding a second key—`ORDER BY first name, last name`—orders by `first name` first, then by `last name` to produce a deterministic order.

Review Questions

Write a query that returns only `first name` and `occupation` for people whose `age` is under 30.
Given `WHERE last name = 'Doe' AND age < 30`, what would happen to the results if you replaced `AND` with `OR`?
How would you sort results by `last name` first and then by `first name`?

Key Points

1
Use `SELECT * FROM people` to retrieve all columns, or list specific columns like `SELECT first name, last name` to limit output.
2
Add a `WHERE` clause to filter rows, such as `WHERE last name = 'Doe'` for text matches.
3
Combine conditions in `WHERE` with `OR` (either condition qualifies) or `AND` (both conditions must qualify).
4
Remember that string comparisons in `WHERE` require quotes around the literal value.
5
Sort filtered results with `ORDER BY`, and use `DESC` for descending order (oldest to youngest).
6
`ASC` is optional because ascending order is the default, but it can be stated explicitly.
7
For consistent ordering when values tie, sort on multiple columns: `ORDER BY first name, last name`.

Highlights

`SELECT` controls which columns come back; `WHERE` controls which rows come back.

Switching `AND` to `OR` in `WHERE` can significantly change the number of returned records.

`ORDER BY age` defaults to ascending order, while `ORDER BY age DESC` flips the direction.

Multi-column sorting (`ORDER BY first name, last name`) resolves ties that single-column sorting can’t.

Topics

SQL SELECT
WHERE Filtering
AND vs OR
ORDER BY Sorting
Multi-Column Ordering

Mentioned

ASC
DESC