SQL Practice — Beginner (Problems 1-30)

P1 — Select All Employees  ·  Beginner

Retrieve all columns from the employees table.

SQL

SELECT *
FROM employees;

Output (computed against this section's live 9-table dataset)

(showing 10 of 40 rows)

Explanation

SELECT * asks the database for every column it knows about on employees, in whatever order they were defined in the schema, for every row that exists — there's no WHERE clause, so nothing is filtered out. In this dataset that returns all 40 employees, from the CEO Arvind Krishnan down to the newest individual contributor. SELECT * is convenient while you're exploring a table for the first time, but in application code it's usually better to name columns explicitly (as the next problem does) — a SELECT * breaks silently if someone adds a column later and your code wasn't expecting it.

P2 — Select Specific Employee Columns  ·  Beginner

Display each employee's first name, last name, and salary.

SQL

SELECT first_name, last_name, salary
FROM employees;

Output (computed against this section's live 9-table dataset)

(showing 10 of 40 rows)

Explanation

Naming first_name, last_name, salary instead of * returns only the three columns the caller actually needs, in that exact order — the database doesn't have to read or serialize the email, department_id, manager_id, hire_date, or city columns for this query at all. The result set has the same 40 rows as Problem 1, just narrower. This is the pattern you want in real applications: it documents intent, and it means adding a new column to employees later can never silently change what this query returns.

P3 — Find Employees in Bengaluru  ·  Beginner

Find all employees whose city is Bengaluru.

SQL

SELECT *
FROM employees
WHERE city = 'Bengaluru';

Output (computed against this section's live 9-table dataset)

(7 rows total)

Explanation

The WHERE city = 'Bengaluru' clause is evaluated once per row, before anything is returned — a row survives only if its city column is exactly 'Bengaluru' (SQL string comparisons are case-sensitive by default). Of the 40 employees, 7 are based in Bengaluru, including the CEO Arvind Krishnan and his direct report Meera Nair. Because there's no index on city in this simple schema, the engine has to scan every row and test the predicate — on a real production table you'd add an index on city if this filter ran often.

P4 — Employees With Salary Above 60000  ·  Beginner

Find employees earning more than 60,000.

SQL

SELECT first_name, last_name, salary
FROM employees
WHERE salary > 60000;

Output (computed against this section's live 9-table dataset)

(showing 10 of 30 rows)

Explanation

salary > 60000 is a strict numeric inequality, so an employee earning exactly 60,000 would not qualify — only rows strictly above the threshold pass. 30 of the 40 employees clear that bar, which tells you most of the workforce (managers and above, plus a majority of the senior ICs) sits comfortably above ₹60k, while the newer, junior hires cluster below it.

P5 — Employees Hired After 2023  ·  Beginner

Find employees hired after January 1, 2023.

SQL

SELECT *
FROM employees
WHERE hire_date > '2023-01-01';

Output (computed against this section's live 9-table dataset)

(showing 10 of 18 rows)

Explanation

Dates in SQL can be compared with the same operators as numbers (>, <, >=, BETWEEN) once they're stored as a proper DATE type, and the string '2023-01-01' is implicitly cast to a date for the comparison. 18 employees were hired after that cutoff — this dataset's newer joiners, mostly individual contributors brought on through 2023-2025 as the company grew, while the founding leadership team (hired 2019-2020) is correctly excluded.

P6 — Sort Employees by Salary  ·  Beginner

Display employees from highest salary to lowest.

SQL

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC;

Output (computed against this section's live 9-table dataset)

(showing 10 of 40 rows)

Explanation

ORDER BY salary DESC sorts the full 40-row result set from the highest salary down, entirely independent of the SELECT list — you can sort by a column even without displaying every column used to sort. Arvind Krishnan (₹285,000, the CEO) leads, followed by Meera Nair and Anil Bhatt; sorting doesn't remove any rows, it only changes the order they're returned in.

P7 — Top 5 Highest Paid Employees  ·  Beginner

Return the five employees with the highest salaries.

SQL

SELECT first_name, last_name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Output (computed against this section's live 9-table dataset)

(5 rows total)

Explanation

This combines the ORDER BY salary DESC from the previous problem with LIMIT 5, which is applied after sorting — so it keeps only the first five rows of the already-sorted result, not five arbitrary high earners. The result is exactly the five most senior salaries: Arvind (₹285,000), Meera (₹215,000), Anil Bhatt (₹205,000), Karan Malhotra (₹198,000), and the fifth-highest department head. Without the preceding ORDER BY, LIMIT would just return whichever five rows the engine happened to read first, which is not a meaningful answer.

P8 — Customers From India  ·  Beginner

Find all customers whose country is India.

SQL

SELECT customer_id, customer_name, city
FROM customers
WHERE country = 'India';

Output (computed against this section's live 9-table dataset)

(showing 10 of 20 rows)

Explanation

The filter country = 'India' runs against the customers table this time, and returns 20 of the 30 customers — matching this dataset's deliberate mix of mostly-Indian customers with a handful of international ones (USA, UK, Canada, Germany, Australia, Singapore, UAE) sprinkled in for variety in later problems like P25's GROUP BY country.

P9 — Products Above ₹1000  ·  Beginner

Find products whose price is greater than 1000.

SQL

SELECT product_name, price
FROM products
WHERE price > 1000;

Output (computed against this section's live 9-table dataset)

(showing 10 of 21 rows)

Explanation

price > 1000 filters the 28-row products table down to 21 products — everything except the handful of budget items (a ₹799 mouse, a ₹899 yoga mat, a couple of sub-₹1000 books and kitchen basics). The comparison works the same way for DECIMAL prices as it does for integer salaries in earlier problems.

P10 — Delivered Orders  ·  Beginner

Find all orders that have been delivered.

SQL

SELECT *
FROM orders
WHERE status = 'Delivered';

Output (computed against this section's live 9-table dataset)

(showing 10 of 88 rows)

Explanation

Matching status = 'Delivered' returns 88 of the 148 orders in the table — the largest single bucket, since the dataset was generated with roughly 55% of orders landing in Delivered, 15% Shipped, 10% Pending, and 20% Cancelled (deliberately high enough to make cancellation-rate and revenue-exclusion problems later on meaningful).

P11 — Unique Customer Countries  ·  Beginner

List every unique country represented in the customers table.

SQL

SELECT DISTINCT country
FROM customers;

Output (computed against this section's live 9-table dataset)

(8 rows total)

Explanation

DISTINCT runs after the rows are selected and collapses duplicate values in the country column down to one row per unique value — instead of returning all 30 customer rows, it returns just the 8 distinct countries represented among them (India, UAE, USA, UK, Canada, Germany, Australia, Singapore). It answers 'what values exist here', not 'how many rows have each value' — for that you'd add COUNT(*) and GROUP BY, as Problem 25 does.

P12 — Products in Electronics  ·  Beginner

Find all products in the Electronics category.

SQL

SELECT product_name, price
FROM products
WHERE category = 'Electronics';

Output (computed against this section's live 9-table dataset)

(8 rows total)

Explanation

This is the same filter pattern as Problem 3 and Problem 9, just applied to the category column of products. Electronics is this dataset's largest category by design (8 of the 28 products), spanning the ₹799 wireless mouse up to the ₹24,999 4K monitor.

P13 — Salary Range  ·  Beginner

Find employees earning between 40,000 and 80,000.

SQL

SELECT first_name, last_name, salary
FROM employees
WHERE salary BETWEEN 40000 AND 80000;

Output (computed against this section's live 9-table dataset)

(showing 10 of 15 rows)

Explanation

BETWEEN 40000 AND 80000 is inclusive on both ends — DuckDB (and standard SQL) treats it as shorthand for salary >= 40000 AND salary <= 80000, so an employee earning exactly 40,000 or exactly 80,000 would still match. 15 employees fall in that band, mostly the individual contributors hired over the last two years, since the company's more senior managers and department heads all clear ₹100k+.

P14 — Customers From Selected Cities  ·  Beginner

Find customers from Bengaluru or Mumbai.

SQL

SELECT customer_name, city
FROM customers
WHERE city IN ('Bengaluru', 'Mumbai');

Output (computed against this section's live 9-table dataset)

(6 rows total)

Explanation

IN ('Bengaluru', 'Mumbai') is exactly equivalent to city = 'Bengaluru' OR city = 'Mumbai', just more compact and, for longer lists, easier for the query planner to optimize. 6 customers are based in one of those two cities out of the 30 total.

P15 — Names Starting With A  ·  Beginner

Find customers whose names start with the letter A.

SQL

SELECT customer_name
FROM customers
WHERE customer_name LIKE 'A%';

Output (computed against this section's live 9-table dataset)

(2 rows total)

Explanation

LIKE 'A%' is a pattern match: % stands for 'zero or more of any character', so this matches any customer_name that starts with the literal letter A (case-sensitive in DuckDB's default collation) — it would match 'Amit Bhatia' but not 'alok bose' if that were lowercase, and it would not match a name where 'A' appears in the middle. Only 2 of the 30 customers happen to start with A in this dataset: Amit Bhatia and Alok Bose.

P16 — Products With Low Stock  ·  Beginner

Find products with fewer than 10 units in stock.

SQL

SELECT product_name, stock_quantity
FROM products
WHERE stock_quantity < 10;

Output (computed against this section's live 9-table dataset)

(2 rows total)

Explanation

stock_quantity < 10 finds products at real risk of stocking out — a strict inequality, so a product sitting at exactly 10 units would not be flagged yet. Only 2 products qualify here: the Smartwatch (8 units) and the Cycling Helmet (9 units) — not coincidentally, these are also the two products this dataset deliberately keeps out of every order in Problem 35, since a slow-moving, low-stock item is a realistic reason a product might never sell.

P17 — Cancelled Orders  ·  Beginner

Find all cancelled orders and display their IDs and amounts.

SQL

SELECT order_id, total_amount
FROM orders
WHERE status = 'Cancelled';

Output (computed against this section's live 9-table dataset)

(showing 10 of 25 rows)

Explanation

Filtering on status = 'Cancelled' surfaces exactly the orders that never generated revenue — 25 of the 148 orders (roughly the 20% cancellation rate baked into this dataset), each shown with its total_amount even though that amount was never actually collected. This is the same set of rows Problem 24 and Problem 106 both have to explicitly exclude or measure.

P18 — Orders Above 5000  ·  Beginner

Find orders whose total amount exceeds 5000.

SQL

SELECT order_id, customer_id, total_amount
FROM orders
WHERE total_amount > 5000;

Output (computed against this section's live 9-table dataset)

(showing 10 of 119 rows)

Explanation

total_amount > 5000 is a simple numeric filter over orders, matching 119 of the 148 rows — most orders in this dataset clear ₹5,000 because each one bundles 1-4 line items (see order_items), so only the smallest single-item orders fall under that line.

P19 — Employees in Two Departments  ·  Beginner

Find employees belonging to department 1 or department 2.

SQL

SELECT employee_id, first_name, department_id
FROM employees
WHERE department_id IN (1, 2);

Output (computed against this section's live 9-table dataset)

(showing 10 of 16 rows)

Explanation

department_id IN (1, 2) is the numeric-column version of Problem 14's city filter — it matches employees in Engineering (department_id = 1) or Sales (department_id = 2), 16 employees combined, without needing two separate OR conditions.

P20 — Employees Without Managers  ·  Beginner

Find employees who do not have a manager.

SQL

SELECT employee_id, first_name, last_name
FROM employees
WHERE manager_id IS NULL;

Output (computed against this section's live 9-table dataset)

(1 row total)

Explanation

manager_id IS NULL is the only correct way to test for a missing value in SQL — manager_id = NULL would never match anything, because NULL represents 'unknown', and unknown = unknown itself evaluates to unknown (not true). Exactly one employee has no manager: Arvind Krishnan, the CEO — everyone else in the 40-person hierarchy ultimately reports up to him, which is exactly what makes the recursive-hierarchy problems in Chapter 4 (P126, P127) work.

P21 — Count All Employees  ·  Beginner

Find the total number of employees.

SQL

SELECT COUNT(*) AS employee_count
FROM employees;

Output (computed against this section's live 9-table dataset)

(1 row total)

Explanation

COUNT(*) counts rows, not values in a particular column, so it correctly returns 40 regardless of whether any individual column (like manager_id, which is NULL for the CEO) has missing data. This is the simplest possible aggregate query: one row in, one row out, summarizing the whole table.

P22 — Average Employee Salary  ·  Beginner

Calculate the average salary of all employees.

SQL

SELECT AVG(salary) AS average_salary
FROM employees;

Output (computed against this section's live 9-table dataset)

(1 row total)

Explanation

AVG(salary) sums every employee's salary and divides by the row count (40) in a single pass — it returns roughly ₹102,073, which sits well above the median individual-contributor salary because it's pulled upward by the CEO's ₹285,000 and the five-figure salaries of the department heads and managers. This is exactly why later problems (P44, P134) also compute the median — averages can be misleading when a dataset has a few very large outliers.

P23 — Highest and Lowest Salary  ·  Beginner

Find the maximum and minimum employee salary.

SQL

SELECT
    MAX(salary) AS highest_salary,
    MIN(salary) AS lowest_salary
FROM employees;

Output (computed against this section's live 9-table dataset)

(1 row total)

Explanation

MAX and MIN can be computed in the same SELECT because each is an independent aggregate over the same column — the engine only needs one pass over the table to produce both. The spread here is enormous: ₹285,000 (the CEO) down to roughly ₹38,195 (this dataset's most junior individual contributor), a nearly 7.5x gap that reflects the org's multi-level hierarchy from CEO down to ICs.

P24 — Total Order Revenue  ·  Beginner

Calculate the total value of all orders.

SQL

SELECT SUM(total_amount) AS total_revenue
FROM orders
WHERE status <> 'Cancelled';

Output (computed against this section's live 9-table dataset)

(1 row total)

Explanation

SUM(total_amount) alone would double-count revenue that was never actually earned, since 25 of the 148 orders are Cancelled — the WHERE status <> 'Cancelled' clause runs before the aggregation, so those rows are excluded from the sum entirely. The result, roughly ₹2.7 million, is this dataset's real non-cancelled order revenue; contrast this with what you'd get by summing every row regardless of status, which would overstate revenue by the value of every cancelled order.

P25 — Count Customers by Country  ·  Beginner

Show how many customers belong to each country.

SQL

SELECT country, COUNT(*) AS customer_count
FROM customers
GROUP BY country
ORDER BY customer_count DESC;

Output (computed against this section's live 9-table dataset)

(8 rows total)

Explanation

GROUP BY country collapses the 30 customer rows into one row per distinct country, and COUNT(*) counts how many original rows fell into each group. India dominates with 20 customers, while the seven international countries have 1-2 each — ORDER BY customer_count DESC then puts the largest group first so the concentration is immediately visible.

P26 — Average Salary by Department  ·  Beginner

Calculate the average salary for each department.

SQL

SELECT department_id, AVG(salary) AS average_salary
FROM employees
GROUP BY department_id;

Output (computed against this section's live 9-table dataset)

(6 rows total)

Explanation

Grouping by department_id and averaging salary within each group shows the org's pay structure department by department: Engineering averages the highest (₹138,589, pulled up by two senior heads plus several well-paid ICs), while HR sits lowest (₹74,451). Unlike Problem 22's single company-wide average, this reveals that the 'average salary' varies a lot depending on which team you're looking at.

P27 — Product Count by Category  ·  Beginner

Count products in every category.

SQL

SELECT category, COUNT(*) AS product_count
FROM products
GROUP BY category
ORDER BY product_count DESC;

Output (computed against this section's live 9-table dataset)

(5 rows total)

Explanation

Grouping products by category and counting rows per group shows the catalog's composition: Electronics leads with 8 products, Sports and Home & Kitchen follow with 6 each, and Clothing and Books round out the remaining 8. ORDER BY product_count DESC puts the best-stocked category first.

P28 — Departments With More Than 5 Employees  ·  Beginner

Find departments having more than five employees.

SQL

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id
HAVING COUNT(*) > 5;

Output (computed against this section's live 9-table dataset)

(5 rows total)

Explanation

HAVING is the equivalent of WHERE but for aggregated results — it can't run until after GROUP BY department_id and COUNT(*) have already produced one row per department, because COUNT(*) > 5 isn't a condition any single raw employee row could satisfy on its own. Five of this dataset's six departments clear that bar (only HR, the smallest team, falls at or under 5 employees), which is exactly why HAVING — not WHERE — has to be used here.

P29 — Categorize Employees by Salary  ·  Beginner

Classify employees as High, Medium, or Low salary.

SQL

SELECT
    first_name,
    salary,
    CASE
        WHEN salary >= 80000 THEN 'High'
        WHEN salary >= 50000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level
FROM employees;

Output (computed against this section's live 9-table dataset)

(showing 10 of 40 rows)

Explanation

CASE WHEN ... THEN ... ELSE ... END evaluates each employee's salary against the conditions in order and stops at the first one that matches — a salary of ₹198,000 hits the >= 80000 branch and is labeled 'High' before the engine ever checks the >= 50000 branch, and anyone under 50,000 falls through to 'Low'. Because the org's leadership salaries are so far above 80,000, a large share of this 40-row result comes back labeled 'High', with 'Medium' and 'Low' concentrated among the more recently hired individual contributors.

P30 — Basic Employee Department Join  ·  Beginner

Display each employee's name and department name.

SQL

SELECT
    e.first_name,
    e.last_name,
    d.department_name
FROM employees e
JOIN departments d
    ON e.department_id = d.department_id;

Output (computed against this section's live 9-table dataset)

(showing 10 of 40 rows)

Explanation

A plain JOIN (shorthand for INNER JOIN) matches each employees row to the departments row whose department_id equals the employee's own department_id — since every employee in this dataset has a valid department_id, all 40 employees appear, now with a human-readable department_name (like 'Engineering' or 'Sales') standing in for the raw numeric foreign key. This is the foundational join pattern every later multi-table problem in this section builds on.