SQL for Analysts

1. Question 1
   • SELECT * FROM weather WHERE city = 'San Francisco';
   • SELECT city, temp_lo, date FROM weather WHERE city LIKE San Francisco;
   • SELECT * FROM weather HAVING city = 'San Francisco';
   • SELECT * FROM weather WHERE city = San Francisco;
2. Question 2
   • The second column in the select list, which is last_name
   • A column named 2 in the employees table
   • The second column in the employees table
   • The second row in the result set
3. Question 3
   • It is always the best practice for production queries because it saves typing.
   • It only retrieves the first three columns of a table for performance reasons.
   • It retrieves all columns from all tables in the FROM clause and is stable even if the table schema changes.
   • It is widely considered bad style in production code because adding a column to the table changes the results.
4. Question 4
   • Sort the result set in ascending order
   • Return only the first row of each unique group
   • Eliminate duplicate rows from the result set, considering all selected columns
   • Remove rows with NULL values from the result set
5. Question 5
   • It is a required keyword for sorting results
   • It provides an alias or label for the output column (temp_hi + temp_lo) / 2
   • It renames the table in the FROM clause
   • It converts the data type of the result to a string
6. Question 6
   • It sorts by the combination of city and temp_lo as a single key
   • It sorts by city only, ignoring temp_lo
   • It sorts by temp_lo first, then by city
   • It sorts by city first, then by temp_lo within rows that have the same city
7. Question 7
   • SELECT, ORDER BY, FROM, WHERE
   • SELECT, FROM, WHERE, ORDER BY
   • FROM, SELECT, ORDER BY, WHERE
   • SELECT, WHERE, ORDER BY, FROM
8. Question 8
   • SELECT max(temp_lo) FROM weather;
   • SELECT * FROM weather;
   • SELECT city, (temp_hi + temp_lo)/2 AS temp_avg FROM weather;
   • SELECT * FROM weather JOIN cities ON city = name;
9. Question 9
   • It is a synonym for DISTINCT
   • It ensures that only one row is returned from the table
   • It limits the query to read only the first column of the table
   • It prevents the query from including rows from child tables in a table inheritance hierarchy
10. Question 10
    • It inserts the column's default value as defined in the table schema
    • It inserts a NULL value into the column
    • It causes an error if the column has no default
    • It inserts the value from the previous row
11. Question 11
    • They are JSON extraction operators: -> returns a JSON object, ->> returns text
    • They are string concatenation operators
    • They are arithmetic operators for absolute value
    • They are comparison operators for approximate equality
12. Question 12
    • All products with a price greater than 5, ordered by price descending.
    • The five products with the highest prices, sorted from highest to lowest price.
    • The first five products in the inventory table, sorted by price descending.
    • The five products with the lowest prices, sorted alphabetically by product name.
13. Question 13
    • SELECT * FROM sales WHERE region = 'West' AND product = 'Widget' ORDER BY revenue;
    • SELECT * FROM sales WHERE region = 'West' OR product = 'Widget';
    • SELECT * FROM sales WHERE region LIKE 'W%' AND product = 'Widget';
    • SELECT * FROM sales WHERE region = 'West' AND product = 'Widget';
14. Question 14
    • The third row in the result set.
    • The third column in the table, which is hire_date.
    • The column alias for hire_date.
    • The third column in the SELECT list, which is hire_date.
15. Question 15
    • Rows where status is 'active' regardless of region, plus rows where region is 'North or South'.
    • Rows where region is 'South', or rows where status is 'active' and region is 'North'.
    • Rows where status is 'active' and region is 'North', combined with rows where region is 'South'.
    • Rows where status is 'active' and region is either 'North' or 'South'.
16. Question 16
    • WHERE sale_amount > 100 AND region = 'West'
    • WHERE sale_amount > 100, region = 'West'
    • WHERE sale_amount > 100 OR region = 'West'
    • WHERE sale_amount > 100 & region = 'West'
17. Question 17
    • The value is an error that needs to be fixed
    • The column does not exist in the table
    • The value is zero or an empty string
    • The value is unknown or missing
18. Question 18
    • 15
    • 10
    • 95
    • 5
19. Question 19
    • The query will return all rows because NULL equals everything
    • Using = NULL will not return any rows; the correct syntax is WHERE manager_id IS NULL
    • There is no issue; the query works correctly in all databases
    • NULL should be written as 'NULL' (quoted) in the comparison
20. Question 20
    • Return 5 products sorted by name in descending order
    • Return all products sorted by price, then limit the output to 5 rows arbitrarily
    • Return the 5 cheapest products
    • Return the 5 most expensive products
21. Question 21
    • OFFSET makes the query faster because it tells the database to ignore rows early
    • A large OFFSET is inefficient because the database still has to compute and skip those rows
    • LIMIT and OFFSET guarantee consistent results across different executions even without ORDER BY
    • Using OFFSET without ORDER BY is recommended for performance
22. Question 22
    • WHERE quantity = 0 OR quantity = NULL
    • WHERE quantity = 0 OR quantity IS NULL
    • WHERE quantity IS 0 OR quantity IS NULL
    • WHERE quantity IN (0, NULL)
23. Question 23
    • WHERE product_name LIKE 'A%'
    • WHERE product_name = 'A%'
    • WHERE product_name IN ('A%')
    • WHERE product_name BEGINS 'A'
24. Question 24
    • It limits the number of rows returned to the specified value
    • It offsets the column names by the given number
    • It skips that many rows before starting to return rows
    • It returns rows that are offset from the mean value of a column
25. Question 25
    • WHERE status = 'shipped' OR 'delivered'
    • WHERE status = 'shipped' OR status = 'delivered'
    • WHERE status = 'shipped' AND status = 'delivered'
    • WHERE status LIKE 'ship%' OR status = 'delivered'
26. Question 26
    • WHERE price >= 10 AND price <= 20
    • WHERE price IN (10, 20)
    • WHERE price BETWEEN 10 AND 20
    • WHERE price >= 10 AND <= 20
27. Question 27
    • IS DISTINCT FROM only works with numeric values
    • IS DISTINCT FROM treats NULL as a comparable value (NULL IS DISTINCT FROM NULL is false)
    • IS DISTINCT FROM is a MySQL extension not available in PostgreSQL
    • There is no difference
28. Question 28
    • The 5 employees with the 6th through 10th highest salaries, sorted from highest to lowest.
    • The 5 employees with the lowest salaries, sorted from lowest to highest.
    • The 5 employees with the 5th through 9th highest salaries, sorted from highest to lowest.
    • The 5 employees with the highest salaries, sorted from highest to lowest.
29. Question 29
    • The condition status = 'cancelled' OR 'pending' is always true because 'pending' is evaluated as a boolean, returning all rows.
    • The query will throw a syntax error because OR cannot be used inside a WHERE clause without parentheses.
    • The OR operator cannot be used with string values; it only works with numeric comparisons.
    • The query will return only rows where status is exactly 'pending', ignoring the 'cancelled' condition.
30. Question 30
    • SELECT * FROM products WHERE discount <> 0;
    • SELECT * FROM products WHERE discount NOT IN (0);
    • SELECT * FROM products WHERE discount != 0;
    • SELECT * FROM products WHERE discount IS DISTINCT FROM 0;

Flashcards will appear here once they are ready.