Reporting (Querying) Flashcards
(18 cards)
What is the art of querying and what is the standard structure for SQL queries?
Querying is the art of retrieving specific data from your database. Structure: SELECT… FROM… WHERE… GROUP BY… HAVING… ORDER BY… LIMIT…
What’s the key difference between the syntactic order and execution order of SQL clauses?
Syntactic order: SELECT, FROM, WHERE, GROUP BY, HAVING, ORDER BY, LIMIT. Execution order: All other clauses execute first in syntactic order, then SELECT executes LAST after the table has been processed
Explain the conceptual process: why does SELECT execute last and what happens before it?
First we choose a table, then do whatever we need (filter rows, group, order, etc.) that affects the table as a whole. Finally, we inject the processed result into SELECT to retrieve specific columns, apply functions, etc.
What can the SELECT clause retrieve and provide examples of each type?
Can retrieve: 1. Columns (column_name), 2. Variables, 3. Expressions, 4. Aggregate functions (AVG(column_name), SUM(column_name)). Example: SELECT name, salary, AVG(salary) FROM employees;
What does DISTINCT do and write a query to get unique departments from an employees table.
DISTINCT removes duplicate values from results. Query: SELECT DISTINCT department FROM employees;
What is the purpose of AS and write a query renaming columns for better readability.
AS gives temporary names (aliases) to columns or values - like allocating to variables. Example: SELECT name AS employee_name, salary AS employee_salary FROM employees;
Why is the FROM clause essential even when the table name is obvious?
The RDBMS doesn’t automatically know where to get data from - FROM acts as a pointer to the data origin. Without it, the system can’t locate the specified tables.
When do functions execute in the query process and what do they evaluate?
Functions execute after the table is transformed by other clauses, then the result is input into SELECT. They evaluate based on the processed output at that stage and return values at their position.
What does WHERE do and write examples of the 4 main types of conditions it supports.
WHERE filters rows by keeping only those where conditions are true. Types: 1. Comparison (salary > 50000), 2. Logic (dept=’Sales’ AND salary>60000), 3. IN (dept IN (‘Sales’,’Marketing’)), 4. BETWEEN (salary BETWEEN 50000 AND 70000)
Write a WHERE clause that finds employees with no manager but who do have a department assigned.
WHERE manager_id IS NULL AND department_id IS NOT NULL
How should you think about GROUP BY and what happens to subsequent clauses?
Think of GROUP BY as separating the table into different sub-tables for each group. Any subsequent clause (including SELECT) is applied to each group individually.
When GROUP BY uses multiple columns, how are rows grouped?
Rows are grouped based on having similar values for ALL mentioned columns - they must match on every specified column to be in the same group.
What’s the key difference between HAVING and WHERE clauses?
WHERE filters individual rows before grouping. HAVING filters groups after GROUP BY creates them - the condition must relate to the grouped columns or aggregate functions.
What happens when you use HAVING without GROUP BY?
It treats the entire table as one big group - essentially works like WHERE but is applied after other processing.
Write a query that orders employees by department ascending, then by salary descending.
SELECT * FROM employees ORDER BY department ASC, salary DESC;
How does ORDER BY work with multiple columns?
Rows are sorted by the first column, then if there are ties (similar values), those tied rows are sorted by the second column, and so on.
Write a query to get employees 11-20 (skip first 10, get next 10) from a salary-ordered list.
SELECT * FROM employees ORDER BY salary DESC LIMIT 10 OFFSET 10; (or LIMIT 10, 10 in some SQL dialects)
Write a query to find departments with average salary > 60000, showing department and avg salary, ordered by avg salary descending.
SELECT department, AVG(salary) AS avg_salary FROM employees GROUP BY department HAVING AVG(salary) > 60000 ORDER BY avg_salary DESC;
