SQL from Learning SQL = Ch3 Flashcards
(15 cards)
What is the difference between WHERE and HAVING clauses?
WHERE filters rows before grouping; HAVING filters after GROUP BY.
Why might SELECT DISTINCT produce unexpected results when used with multiple columns?
Because DISTINCT applies to the entire row, not individual columns.
How does SQL determine which rows to return when multiple tables are listed in the FROM clause without a JOIN condition?
It returns a Cartesian product of all combinations of rows from the listed tables.
In what order are SQL clauses logically evaluated in a SELECT statement?
FROM, WHERE, GROUP BY, HAVING, SELECT, ORDER BY
How can you alias a column so that the alias can be used in the ORDER BY clause but not in WHERE?
By defining it in the SELECT clause and referencing it in ORDER BY; WHERE is evaluated earlier.
Why can’t you use an alias defined in the SELECT clause within the WHERE clause?
Because WHERE is evaluated before SELECT.
How would SQL handle this query: SELECT name, price FROM products WHERE price = NULL?
It would return no rows because comparisons with NULL must use IS NULL.
What is the behavior of the BETWEEN operator when used with NULL values?
It returns NULL (treated as unknown) if either boundary or the test value is NULL.
Why is SELECT * considered dangerous in production code?
Because it can return more data than needed, break applications if schema changes, and reduce performance.
What happens if you alias a column without using the AS keyword?
It still works in most databases, but readability suffers.
What’s the difference between WHERE col != NULL and WHERE col IS NOT NULL?
The first always returns false; the second is correct for filtering out NULLs.
How would you select the second highest salary from an employees table?
SELECT MAX(salary) FROM employees WHERE salary < (SELECT MAX(salary) FROM employees);
How does SQL treat an ORDER BY clause with a column index rather than a name?
It sorts by the specified column position in the SELECT list.
How can using functions in a WHERE clause impact performance?
It may prevent the use of indexes, leading to full table scans.
What’s the implication of using SELECT DISTINCT with ORDER BY on large datasets?
It may require SQL to sort and compare all rows, increasing processing time.
