Academy Articles Videos Tools Dashboards Resources Templates ES

SQL & data Quiz

12 questions. Score 70% or more to pass this rank's quiz.

1. What is the difference between WHERE and HAVING? HAVING filters rows before grouping; WHERE filters groups WHERE filters rows before grouping; HAVING filters groups after aggregation They are interchangeable HAVING only works with JOINs

Use HAVING for conditions on aggregates, e.g. HAVING SUM(quantity) > 100; WHERE cannot reference aggregates.

2. Sunshine Grocers has 102 employees; 12 store managers have manager_id = NULL. How many rows does SELECT * FROM employees e INNER JOIN employees m ON e.manager_id = m.employee_id return? 102 12 114 90

NULL never equals anything, so the 12 managers drop out of the INNER JOIN; a LEFT JOIN would keep all 102.

3. stores has 12 rows and products has 27 rows. How many rows does SELECT * FROM stores CROSS JOIN products return? 27 39 12 324

A CROSS JOIN pairs every row with every row: 12 × 27 = 324 (one per store–product combination, like the inventory table).

4. What does SELECT COUNT(manager_id) FROM employees return if 12 of the 102 rows have NULL manager_id? 102 NULL 90 12

COUNT(column) ignores NULLs, while COUNT(*) counts every row (102).

5. Which query finds customers who have never placed an order? SELECT c.* FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL SELECT c.* FROM customers c INNER JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id IS NULL SELECT c.* FROM customers c LEFT JOIN orders o ON o.customer_id = c.customer_id WHERE o.order_id = NULL SELECT * FROM orders WHERE customer_id IS NULL

The LEFT JOIN keeps every customer and fills order columns with NULL when there is no match; IS NULL then keeps only those (= NULL never matches).

6. Scores 90, 90, 80 are ranked with RANK() and DENSE_RANK() (ORDER BY score DESC). What are the results? RANK: 1, 1, 3 — DENSE_RANK: 1, 1, 2 RANK: 1, 2, 3 — DENSE_RANK: 1, 1, 2 RANK: 1, 1, 2 — DENSE_RANK: 1, 1, 3 Both: 1, 2, 3

Both give ties the same rank; RANK then skips numbers, DENSE_RANK does not (ROW_NUMBER would give 1, 2, 3).

7. What is the difference between UNION and UNION ALL? UNION removes duplicate rows; UNION ALL keeps them all UNION joins columns side by side UNION ALL removes duplicates; UNION keeps them There is no difference

UNION ALL is faster because it skips the de-duplication step; use it when duplicates are impossible or wanted.

8. In order_items, which expression gives total net sales? SUM(quantity * unit_price - discount) SUM(quantity) * SUM(unit_price) * (1 - AVG(discount)) SUM(quantity * unit_price * (1 - discount)) SUM(unit_price) - SUM(discount)

Discount is a rate (0.05 = 5%), so net sales are computed per line and then summed.

9. Why does SELECT * FROM stores WHERE region = NULL return no rows even if some regions are missing? NULL values are deleted automatically The query has a syntax error region must be cast to text first Comparing with = NULL is never true; you must use IS NULL

In SQL, NULL means unknown, so NULL = NULL evaluates to unknown (not true), and the row is filtered out.

10. In SQLite, what does SELECT 7 / 2; return? 3.5 3 4 An error

Dividing two integers performs integer division; write 7.0 / 2 or CAST(7 AS REAL) / 2 to get 3.5.

11. Why does SELECT net_sales * 2 AS doubled FROM t WHERE doubled > 100 fail in most databases? Multiplication is not allowed in SELECT AS is not valid SQL WHERE is evaluated before SELECT, so the alias does not exist yet Aliases cannot contain letters

Logical order is FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY; repeat the expression or use a CTE/subquery.

12. What does a query that starts with WITH monthly AS (SELECT ...) define? An index on the monthly column A common table expression (CTE): a named temporary result used by the main query A permanent table called monthly A stored procedure

A CTE exists only for that statement and makes multi-step queries easier to read than nested subqueries.

More quizzes

Excel Power BI & DAX BI concepts & tools Python & AI BI career

DashCoreHub

Learn BI + AI: Excel, Power BI, SQL & Python.

hello@dashcorehub.com

Articles

Excel
Power BI
BI Tools & Concepts
SQL & Data
Python & AI
Career

Explore

Academy
Videos
SQL Playground
Cheat sheets
Practice data
Glossary
Learning Path
Tools
Dashboards
Templates
About
Contact

Legal

Privacy Policy
Terms of Use
Affiliate Disclosure
Disclaimer

As an Amazon Associate we earn from qualifying purchases. Some links are affiliate links. Microsoft, Excel, Power BI, Tableau, Data Studio and other product names are trademarks of their owners; DashCore Hub is an independent educational site and is not affiliated with them.