Essential SQL Interview Questions Quiz

Test your knowledge of SQL fundamentals with these 15 easy questions covering common SQL interview topics such as JOINs, ranking functions, filtering, and aggregation. Ideal for candidates preparing for SQL interviews and anyone wanting to boost their SQL basics.

1. Identifying Duplicates

   Which SQL clause would you use to find duplicate values in a column called 'email' within a table 'users'?

   1. GROUP BY email HAVING COUNT(email) u003E 1
   2. ORDER BY email ASC
   3. WHERE COUNT(email) u003E 1
   4. DISTINCT email

   Explanation: Using GROUP BY groups the rows by email and HAVING COUNT(email) u003E 1 filters only those with duplicates. WHERE COUNT(email) u003E 1 is incorrect because WHERE cannot use aggregate functions this way; that's what HAVING is for. ORDER BY just sorts the emails, and DISTINCT email removes duplicates but does not identify them.

2. Second Highest Salary

   What query can you use to retrieve the second highest salary from an 'employees' table?

   1. SELECT MAX(salary) FROM employees WHERE salary u003C (SELECT MAX(salary) FROM employees)
   2. SELECT salary FROM employees ORDER BY salary DESC LIMIT 1
   3. SELECT MIN(salary) FROM employees
   4. SELECT salary FROM employees WHERE salary = (SELECT MIN(salary) FROM employees)

   Explanation: To find the second highest salary, you filter out the highest and use MAX on the rest. The DESC LIMIT 1 syntax returns the top salary, not the second highest. MIN finds the lowest salary. Using WHERE salary = (SELECT MIN(salary)) gets the minimum salary, not the second highest.

3. Understanding RANK()

   How does the RANK() window function behave when there are ties in the partitioned column?

   1. Tied rows receive the same rank and the following rank(s) are skipped
   2. Tied rows are ignored
   3. It always ranks sequentially without skipping
   4. Tied rows receive different ranks

   Explanation: RANK() gives identical ranks to tied values, but skips the next rank(s). DENSE_RANK() does not skip ranks. Assigning different ranks to ties or ranking sequentially without skipping is incorrect for RANK(). Tied rows are included in the results; they are not ignored.

4. WHERE vs. HAVING

   What is the key difference between the WHERE and HAVING clauses in SQL?

   1. WHERE can only be used with GROUP BY; HAVING cannot
   2. WHERE and HAVING can always be used interchangeably
   3. HAVING filters before aggregation; WHERE filters after
   4. WHERE filters rows before aggregation; HAVING filters groups after aggregation

   Explanation: WHERE is used to filter individual rows before any groupings or aggregations, while HAVING filters groups after aggregation. The second option is wrong because WHERE is not exclusive to GROUP BY. The third option reverses their true behavior. WHERE and HAVING are not interchangeable in all scenarios.

5. Calculating a Moving Average

   Which SQL window function helps you calculate a moving average over a column 'price' ordered by 'date'?

   1. MAX(price)
   2. AVG(price) OVER (ORDER BY date ROWS BETWEEN 2 PRECEDING AND CURRENT ROW)
   3. COUNT(price)
   4. SUM(price) GROUP BY date

   Explanation: The AVG window function with an OVER clause and window frame specification calculates the moving average. COUNT only gives the number of rows. MAX returns the highest price, not an average. SUM with GROUP BY does sums per date, not a moving average.

6. Eliminating Duplicate Rows

   Which keyword removes duplicate rows from the result set in a basic SELECT query?

   1. DIFFERENT
   2. UNIQUEE
   3. DISTINCT
   4. DISTINC

   Explanation: DISTINCT is the correct syntax to remove duplicates in SQL. UNIQUEE and DISTINC are common typos. DIFFERENT is not a SQL keyword; it has no effect in a query.

7. JOINS: Combining Data

   If you want to get all records that exist in both table A and table B, which type of JOIN should you use?

   1. INNER JOIN
   2. RIGHT JOIN
   3. OUTER JOIN
   4. LEFT JOIN

   Explanation: INNER JOIN returns only rows with matching values in both tables. LEFT JOIN and RIGHT JOIN return all records from one side and matches from the other, including non-matching rows. OUTER JOIN isn't valid SQL syntax; the proper term is FULL OUTER JOIN, which includes all records from both tables.

8. Filtering Null Values

   How would you select all rows where the 'end_date' column is missing a value in SQL?

   1. WHERE end_date NOT NULL
   2. WHERE ISNULL(end_date)
   3. WHERE end_date IS NULL
   4. WHERE end_date = NULL

   Explanation: IS NULL checks for missing values. Using = NULL doesn't work in SQL. ISNULL is a function in some SQL dialects but is not valid in WHERE clauses this way. WHERE end_date NOT NULL is incorrect syntax.

9. Limiting Results

   Which clause in SQL limits the number of rows returned by a SELECT statement?

   1. COUNT
   2. SHORT
   3. RESTRICT
   4. LIMIT

   Explanation: LIMIT restricts the number of rows outputted by a query. RESTRICT and SHORT are not SQL keywords. COUNT calculates the number of rows but doesn't limit returned results.

10. Ordering Results

    How do you sort the results by 'created_at' in descending order in a SQL query?

    1. ORDER BY created_at DESC
    2. SORT BY created_at DOWN
    3. ORDER DESC created_at
    4. ORDER created_at DESC

    Explanation: ORDER BY column DESC is the proper way to sort in descending order. The other options are invalid SQL syntax, either missing the BY keyword or using incorrect commands.

11. Aggregate Function Example

    Which aggregate function would you use to count the total number of users in a 'users' table?

    1. COUNTT(*)
    2. COUNT(*)
    3. AVG(user_id)
    4. SUM(users)

    Explanation: COUNT(*) counts all rows, giving the total number of users. SUM is used for addition, not counting. AVG gives the average but does not count. COUNTT is a typo and not a valid function.

12. Selecting Distinct Values

    How can you retrieve a list of all unique cities from a 'customers' table?

    1. SELECT DIFF city FROM customers
    2. SELECT UNIQUE city FROM customers
    3. SELECT city FROM customers GROUPED
    4. SELECT DISTINCT city FROM customers

    Explanation: SELECT DISTINCT retrieves unique city values. UNIQUE and DIFF are not SQL keywords. GROUPED is incorrect syntax and will not return unique cities.

13. GROUP BY Usage

    If you want to count how many orders each user has made, which clause should you use?

    1. LIMIT user_id
    2. ORDER BY order_id
    3. HAVING user_id u003E 5
    4. GROUP BY user_id

    Explanation: GROUP BY user_id groups the rows so you can aggregate order counts per user. ORDER BY only sorts. LIMIT restricts output; HAVING user_id u003E 5 filters groups but does not count them.

14. Basic SQL Syntax

    Which command retrieves all columns from the 'products' table?

    1. SELECT * FROM products
    2. RETRIEVE * FROM products
    3. SELECT ALL products
    4. GET ALL FROM products

    Explanation: SELECT * FROM products is the correct command for returning all columns. RETRIEVE and GET ALL are not recognized SQL commands. SELECT ALL products is not valid SQL syntax.

15. Understanding Aliases

    Which syntax assigns a temporary name 'total_orders' to a sum of orders in a result?

    1. SELECT SUM(orders) NAMED total_orders
    2. SELECT SUM(orders) TO total_orders
    3. SELECT total_orders = SUM(orders)
    4. SELECT SUM(orders) AS total_orders

    Explanation: AS assigns an alias in SQL. TO, =, and NAMED are not valid SQL aliasing syntax. The correct method is to use AS between the calculated field and the alias.

16. Filtering with Multiple Conditions

    Which clause is used to filter results with more than one condition, for example, age u003E 25 and city = 'Miami'?

    1. FILTER age u003E 25 WITH city = 'Miami'
    2. LIMIT age u003E 25, city = 'Miami'
    3. WHERE age u003E 25 OR city = 'Miami'
    4. WHERE age u003E 25 AND city = 'Miami'

    Explanation: The WHERE clause with AND allows filtering on multiple conditions. LIMIT and FILTER are not used this way. OR returns rows where either condition is true, not both.