SQL `SELECT` Query

This guide covers various SELECT query techniques used for retrieving, filtering, sorting, and aggregating data efficiently.

Comments in SQL
SELECT All Columns
SELECT Specific Columns
WHERE Clause (Filtering Rows)
ORDER BY (Sorting Results)
GROUP BY (Aggregating Data)
HAVING (Filtering Groups)
DISTINCT (Unique Values)
TOP (Limiting Rows)
Putting It All Together
“Cool Stuff” – Additional SQL Features

PDF Slides

Open PDF

1. Comments in SQL

Comments help you document your queries so you (and others) understand what they do later.

Single-line and Multi-line Comments

/* ==============================================================================
   COMMENTS
=============================================================================== */

-- This is a single-line comment.

/* This
   is
   a multiple-line
   comment
*/

Use -- for single-line comments (very common).
Use /* ... */ for multi-line comments, useful for big explanations or temporarily disabling blocks of code.

💡 Try it: Add a comment above any query describing what you expect it to return in plain English.

2. SELECT All Columns

Use SELECT * when you want all columns from a table (often okay for quick exploration, but not ideal in production).

/* ==============================================================================
   SELECT ALL COLUMNS
=============================================================================== */

-- Retrieve All Customer Data
SELECT *
FROM customers

-- Retrieve All Order Data
SELECT *
FROM orders

SELECT * FROM customers → returns every column and every row from customers.
SELECT * FROM orders → same idea for orders.

🧠 Question: When might SELECT * be dangerous or inefficient in a real application?

3. SELECT Specific Columns

Often you only need a few columns. That’s where explicit column lists shine.

/* ==============================================================================
   SELECT FEW COLUMNS
=============================================================================== */

-- Retrieve each customer's name, country, and score.
SELECT 
    first_name,
    country, 
    score
FROM customers

This returns just what you need: first_name, country, score.
Clearer, safer, and often faster than SELECT *.

💡 Try it: Modify this query to also return the id column.

4. WHERE Clause (Filtering Rows)

WHERE filters rows before they are returned (or grouped, aggregated, etc.).

/* ==============================================================================
   WHERE
=============================================================================== */

-- Retrieve customers with a score not equal to 0
SELECT *
FROM customers
WHERE score != 0

-- Retrieve customers from Germany
SELECT *
FROM customers
WHERE country = 'Germany'

-- Retrieve the name and country of customers from Germany
SELECT
    first_name,
    country
FROM customers
WHERE country = 'Germany'

Key ideas:

score != 0 → keep all rows where score is not zero.
country = 'Germany' → keep rows where country is exactly 'Germany'.

🧠 Question: How would you write a query to get all customers from 'USA' with a score greater than 500?

5. ORDER BY (Sorting Results)

ORDER BY lets you sort results by one or more columns.

/* ==============================================================================
   ORDER BY
=============================================================================== */

/* Retrieve all customers and 
   sort the results by the highest score first. */
SELECT *
FROM customers
ORDER BY score DESC

/* Retrieve all customers and 
   sort the results by the lowest score first. */
SELECT *
FROM customers
ORDER BY score ASC

/* Retrieve all customers and 
   sort the results by the country. */
SELECT *
FROM customers
ORDER BY country ASC

/* Retrieve all customers and 
   sort the results by the country and then by the highest score. */
SELECT *
FROM customers
ORDER BY country ASC, score DESC

/* Retrieve the name, country, and score of customers 
   whose score is not equal to 0
   and sort the results by the highest score first. */
SELECT
    first_name,
    country,
    score
FROM customers
WHERE score != 0
ORDER BY score DESC

ASC = ascending (smallest → largest, A → Z).
DESC = descending (largest → smallest, Z → A).
You can sort by multiple columns: ORDER BY country ASC, score DESC.

💡 Try it: Change one of the queries to sort by first_name instead of score. What changes in the output?

6. GROUP BY (Aggregating Data)

GROUP BY groups rows that share the same value(s) in one or more columns, so you can apply aggregate functions (like SUM, COUNT, AVG) per group.

/* ==============================================================================
   GROUP BY
=============================================================================== */

-- Find the total score for each country
SELECT 
    country,
    SUM(score) AS total_score
FROM customers
GROUP BY country

/* This will not work because 'first_name' is neither part of the GROUP BY 
   nor wrapped in an aggregate function. SQL doesn't know how to handle this column. */
SELECT 
    country,
    first_name,
    SUM(score) AS total_score
FROM customers
GROUP BY country

-- Find the total score and total number of customers for each country
SELECT 
    country,
    SUM(score) AS total_score,
    COUNT(id) AS total_customers
FROM customers
GROUP BY country

Important rules:

Every selected column must be:
- either in the GROUP BY, or
- inside an aggregate function (SUM, COUNT, AVG, etc.).
The “invalid” example demonstrates this common error.

🧠 Question: Why is it ambiguous to select first_name when grouping only by country?

7. HAVING (Filtering Groups)

HAVING filters groups after aggregation. Think of it as WHERE for grouped data.

/* ==============================================================================
   HAVING
=============================================================================== */

/* Find the average score for each country
   and return only those countries with an average score greater than 430 */
SELECT
    country,
    AVG(score) AS avg_score
FROM customers
GROUP BY country
HAVING AVG(score) > 430

/* Find the average score for each country
   considering only customers with a score not equal to 0
   and return only those countries with an average score greater than 430 */
SELECT
    country,
    AVG(score) AS avg_score
FROM customers
WHERE score != 0
GROUP BY country
HAVING AVG(score) > 430

WHERE filters rows before grouping.
HAVING filters groups after grouping.

💡 Try it: Change the query to only show countries where the average score is less than 300. Which part do you change?

8. DISTINCT (Unique Values)

DISTINCT removes duplicate rows in the result set.

/* ==============================================================================
   DISTINCT
=============================================================================== */

-- Return Unique list of all countries
SELECT DISTINCT country
FROM customers

If multiple customers are from the same country, DISTINCT country returns that country only once.

🧠 Question: What is the difference between SELECT country FROM customers and SELECT DISTINCT country FROM customers?

9. TOP (Limiting Rows)

TOP (in SQL Server and similar dialects) limits how many rows are returned.

/* ==============================================================================
   TOP
=============================================================================== */

-- Retrieve only 3 Customers
SELECT TOP 3 *
FROM customers

-- Retrieve the Top 3 Customers with the Highest Scores
SELECT TOP 3 *
FROM customers
ORDER BY score DESC

-- Retrieve the Lowest 2 Customers based on the score
SELECT TOP 2 *
FROM customers
ORDER BY score ASC

-- Get the Two Most Recent Orders
SELECT TOP 2 *
FROM orders
ORDER BY order_date DESC

Without ORDER BY, TOP just gives you some N rows (not well-defined).
With ORDER BY, you can say “top N by score” or “latest N by date”.

💡 Try it: Adjust one query to return the Top 5 customers by score instead of 3.

10. Putting It All Together

Here’s a more complex query combining WHERE, GROUP BY, HAVING, and ORDER BY.

/* ==============================================================================
   All Together
=============================================================================== */

/* Calculate the average score for each country 
   considering only customers with a score not equal to 0
   and return only those countries with an average score greater than 430
   and sort the results by the highest average score first. */
SELECT
    country,
    AVG(score) AS avg_score
FROM customers
WHERE score != 0
GROUP BY country
HAVING AVG(score) > 430
ORDER BY AVG(score) DESC

Step-by-step (logical execution order):

FROM customers
WHERE score != 0 → filter rows.
GROUP BY country → group remaining rows by country.
AVG(score) → compute average for each country.
HAVING AVG(score) > 430 → keep only groups with high averages.
SELECT country, AVG(score) AS avg_score
ORDER BY AVG(score) DESC → show highest averages first.

🧠 Question: What happens if you remove the WHERE score != 0 condition? How might that affect the averages?

11. “Cool Stuff” – Additional SQL Features

Here are a few extra tricks that are often useful in practice.

/* ============================================================================== 
   COOL STUFF - Additional SQL Features
=============================================================================== */

-- Execute multiple queries at once
SELECT * FROM customers;
SELECT * FROM orders;

Many SQL tools let you run multiple statements in one go (usually separated by ;).

/* Selecting Static Data */
-- Select a static or constant value without accessing any table
SELECT 123 AS static_number;

SELECT 'Hello' AS static_string;

You can query constants without referencing any table (useful for testing or checking expressions).

-- Assign a constant value to a column in a query
SELECT
    id,
    first_name,
    'New Customer' AS customer_type
FROM customers;

Here, every row gets a computed column customer_type with the constant value 'New Customer'.

💡 Try it: Change 'New Customer' to 'VIP' and add a WHERE filter for customers with high scores. You’ve just created a categorized list of VIP customers.

################################################

SQL SELECT Practice Workbook

1. Comments in SQL

✅ Exercises

Write a single-line comment that says: Get all German customers.

💡 Answer

    -- Get all German customers
    SELECT *
    FROM customers
    WHERE country = 'Germany';

Write a multi-line comment explaining that the next query will return the top 3 customers by score.

💡 Answer

/* 2) Multi-line comment
This query returns the top 3 customers
ordered by their score in descending order
*/
SELECT TOP 3 *
FROM customers
ORDER BY score DESC;

You want to temporarily disable a query without deleting it. How would you comment it out?

💡 Answer

    /* 3) Comment out the whole query
        SELECT *
        FROM customers
        WHERE score != 0;
        */

2. SELECT All Columns

✅ Exercises

Write a query to retrieve all columns from the customers table.
💡 Answer
```
 -- 1) All customers
 SELECT *
 FROM customers;
```
Write a query to retrieve all columns from the orders table.
💡 Answer
```
-- 2) All orders
SELECT *
FROM orders; 
```

3. SELECT Specific Columns

✅ Exercises

Write a query that returns only first_name and country from customers.
💡 Answer
```
SELECT
first_name,
country
FROM customers; 
```
Write a query that returns id, first_name, and score from customers.
💡 Answer
```
SELECT
    id,
    first_name,
    score
FROM customers;
```

4. WHERE Clause (Filtering Rows)

✅ Exercises

Retrieve all customers whose score is exactly 0.
💡 Answer
```
SELECT
    *
FROM customers
WHERE score = 0;
```

Retrieve all customers not from 'Germany'.

💡 Answer

SELECT
    *
FROM customers
WHERE country != 'Germany';

Retrieve all customers from 'USA' with a score greater than 500.

💡 Answer

SELECT
    *
FROM customers
WHERE country = 'USA'
  AND score > 500;

Retrieve only first_name and country for customers with score not equal to 0.
💡 Answer
```
SELECT
    first_name,
    country
FROM customers
WHERE score != 0;
```

5. ORDER BY (Sorting Results)

✅ Exercises

List all customers ordered by first_name alphabetically (A→Z).
💡 Answer
```
SELECT
    *
FROM customers
ORDER BY first_name ASC;
```
List all customers sorted by country (A→Z) and then by score from highest to lowest.
💡 Answer
```
SELECT
    *
FROM customers
ORDER BY country ASC,
         score DESC;
```
Show only first_name, country, score for customers where score != 0, sorted by score from highest to lowest.
💡 Answer
```
SELECT
    first_name,
    country,
    score
FROM customers
WHERE score != 0
ORDER BY score DESC;
```
Retrieve all customers ordered by score from lowest to highest.
💡 Answer
```
SELECT
    *
FROM customers
ORDER BY score ASC;
```

6. GROUP BY (Aggregating Data)

✅ Exercises

For each country, calculate the total score of its customers. Return country and total_score.

💡 Answer

-- 1) Total score per country
SELECT
    country,
    SUM(score) AS total_score
FROM customers
GROUP BY country;

For each country, show both:

the total score
the number of customers Return country, total_score, and total_customers.

💡 Answer

-- 2) Total score & count per country
SELECT
    country,
    SUM(score)  AS total_score,
    COUNT(id)   AS total_customers
FROM customers
GROUP BY country;

Spot the error: Why does this query fail?

SELECT
    country,
    first_name,
    SUM(score) AS total_score
FROM customers
GROUP BY country;

💡 Answer

-- 3) Explanation:
The query fails because 'first_name' is in the SELECT list but not:
  a) included in GROUP BY, or
  b) wrapped in an aggregate function.

SQL doesn't know which 'first_name' to show for each country group.

How would you fix it so it runs?

One possible fix (remove first_name):

💡 Answer

  SELECT
  country,
  SUM(score) AS total_score
  FROM customers
  GROUP BY country;

Another fix (include first_name in grouping, though that changes meaning):

💡 Answer

  SELECT
  country,
  SUM(score) AS total_score
  FROM customers
  GROUP BY country, first_name;

7. HAVING (Filtering Groups)

✅ Exercises

For each country, compute the average score, and only show countries with an average score greater than 400.

💡 Answer

-- 1) Average score per country > 400
SELECT
    country,
    AVG(score) AS avg_score
FROM customers
GROUP BY country
HAVING AVG(score) > 400;

Same as above, but ignore customers whose score is 0 when computing the average.

💡 Answer

-- 2) Ignore score = 0 rows first
SELECT
    country,
    AVG(score) AS avg_score
FROM customers
WHERE score != 0
GROUP BY country
HAVING AVG(score) > 400;

True/False (then justify):

“You can use HAVING instead of WHERE to filter individual rows before grouping.”

💡 Answer

-- 3) Answer: False.

Explanation:
- WHERE filters individual rows BEFORE grouping.
- HAVING filters groups AFTER grouping.
Using HAVING instead of WHERE for simple row filters is slower and conceptually wrong.

8. DISTINCT (Unique Values)

✅ Exercises

Get a list of all distinct countries that appear in the customers table.
💡 Answer
```
-- 1) Unique list of countries
SELECT DISTINCT country
FROM customers;
```

Explain the difference between:

SELECT country FROM customers;

and

SELECT DISTINCT country FROM customers;

💡 Answer

-- 2) Explanation:
SELECT country FROM customers;
  - returns one row per customer, so countries can repeat many times.

SELECT DISTINCT country FROM customers;
  - returns each country only once (duplicates removed).

Suppose you add another column in the future (e.g., city). What happens if you run SELECT DISTINCT country, city FROM customers;?

💡 Answer

-- 3) Explanation:
SELECT DISTINCT country, city FROM customers;
  - returns unique combinations of (country, city).
  - If the same city appears in the same country multiple times, it shows once.
  - If the same city name exists in multiple countries, each (country, city) pair appears separately.

9. TOP (Limiting Rows)

✅ Exercises

Write a query to retrieve only the first 3 rows from customers (no sorting).
💡 Answer
```
-- 1) First 3 customers (no guaranteed order)
SELECT TOP 3 *
FROM customers;
```

Retrieve the top 3 customers with the highest scores.

💡 Answer

-- 2) Top 3 by highest score
SELECT TOP 3 *
FROM customers
ORDER BY score DESC;

Retrieve the lowest 2 customers based on score.

💡 Answer

-- 3) Lowest 2 by score
SELECT TOP 2 *
FROM customers
ORDER BY score ASC;

Get the two most recent orders using order_date.

💡 Answer

-- 4) Two most recent orders
SELECT TOP 2 *
FROM orders
ORDER BY order_date DESC;

10. Putting It All Together

✅ Exercises

Using customers, write a query that:
- Ignores rows where score = 0,
- Groups by country,
- Computes AVG(score) as avg_score,
- Only keeps countries where avg_score > 430,
- Sorts results by avg_score from highest to lowest.
💡 Answer
```
-- 1) Full pipeline: WHERE + GROUP BY + HAVING + ORDER BY
SELECT
    country,
    AVG(score) AS avg_score
FROM customers
WHERE score != 0
GROUP BY country
HAVING AVG(score) > 430
ORDER BY AVG(score) DESC;
```

Modify the above query so it returns only the top 2 countries by avg_score.

💡 Answer

-- 2) Top 2 countries by avg_score
SELECT TOP 2
    country,
    AVG(score) AS avg_score
FROM customers
WHERE score != 0
GROUP BY country
HAVING AVG(score) > 430
ORDER BY AVG(score) DESC;

Describe in words the logical execution order of this query:

SELECT
    country,
    AVG(score) AS avg_score
FROM customers
WHERE score != 0
GROUP BY country
HAVING AVG(score) > 430
ORDER BY AVG(score) DESC;

💡 Answer

-- 3) Logical execution order (how SQL "thinks"):

1) FROM customers
2) WHERE score != 0          -- filter rows
3) GROUP BY country          -- group remaining rows by country
4) AVG(score)                -- compute average score per country
5) HAVING AVG(score) > 430   -- filter groups by average score
6) SELECT country, AVG(score) AS avg_score -- project output columns
7) ORDER BY AVG(score) DESC  -- sort resulting groups by avg_score

11. Cool Stuff – Additional Features

✅ Exercises

Write two queries that run together:

One selects all customers,
One selects all orders.

💡 Answer

-- 1) Two queries at once (many tools support this)
SELECT * FROM customers;
SELECT * FROM orders;

Select the constant number 999 and give it the alias magic_number.
💡 Answer
```
-- 2) Constant number with alias
SELECT 999 AS magic_number;
```

Select the constant string 'VIP' as customer_type alongside id and first_name from customers.

💡 Answer

-- 3) Constant string column with other columns
SELECT
    id,
    first_name,
    'VIP' AS customer_type
FROM customers;

Explain what the result looks like for:

SELECT
    id,
    first_name,
    'New Customer' AS customer_type
FROM customers;

💡 Answer

-- 4) Explanation:
The query

SELECT
    id,
    first_name,
    'New Customer' AS customer_type
FROM customers;

returns one row per customer with three columns:

- id             -> from the table
- first_name     -> from the table
- customer_type  -> always the text 'New Customer' for every row

So you effectively "tag" each row with a constant label.

Note Book

SQL `SELECT` Query

Table of Contents

PDF Slides

1. Comments in SQL

Single-line and Multi-line Comments

2. SELECT All Columns

3. SELECT Specific Columns

4. WHERE Clause (Filtering Rows)

5. ORDER BY (Sorting Results)

6. GROUP BY (Aggregating Data)

7. HAVING (Filtering Groups)

8. DISTINCT (Unique Values)

9. TOP (Limiting Rows)

10. Putting It All Together

11. “Cool Stuff” – Additional SQL Features

################################################

SQL SELECT Practice Workbook

1. Comments in SQL

✅ Exercises

2. SELECT All Columns

✅ Exercises

3. SELECT Specific Columns

✅ Exercises

4. WHERE Clause (Filtering Rows)

✅ Exercises

5. ORDER BY (Sorting Results)

✅ Exercises

6. GROUP BY (Aggregating Data)

✅ Exercises

7. HAVING (Filtering Groups)

✅ Exercises

8. DISTINCT (Unique Values)

✅ Exercises

9. TOP (Limiting Rows)

✅ Exercises

10. Putting It All Together

✅ Exercises

11. Cool Stuff – Additional Features

✅ Exercises

Keyboard shortcuts

Note Book