Mastering Advanced SQL: Easy Interview Concepts Quiz Quiz

1. Question 1

   Which window function assigns a unique sequential number to each row, even when there are ties in the ranking column?

   1. ROW_NUMBER()
   2. RANK()
   3. DENSE_RANK()
   4. ROW_NAMBER()
   5. RANK_NUM()

2. Question 2

   If you want to find the second highest salary in an employees table, which SQL command would you use?

   1. SELECT MAX(salary) FROM employees WHERE salary u003C (SELECT MAX(salary) FROM employees);
   2. SELECT MIN(salary) FROM employees;
   3. SELECT salary FROM employees WHERE ROWNUM = 2;
   4. SELECT AVG(salary) FROM employees;
   5. SELECT salary FROM employees WHERE salary = SECOND_MAX(salary);

3. Question 3

   What is a Common Table Expression (CTE) most commonly used for in SQL?

   1. To define a temporary result set to simplify complex queries
   2. To create a new permanent table in the database
   3. To enforce referential integrity constraints
   4. To update multiple rows at once using a loop
   5. To create indexes on specific columns

4. Question 4

   Given a table with duplicate records, what query deletes duplicates but keeps the row with the lowest id?

   1. DELETE FROM employees WHERE id NOT IN (SELECT MIN(id) FROM employees GROUP BY name, department_id, salary);
   2. DELETE FROM employees WHERE name IN (SELECT name FROM employees GROUP BY name HAVING COUNT(*) u003E 1);
   3. DELETE FROM employees WHERE id IN (SELECT id FROM employees GROUP BY name HAVING COUNT(*) = 1);
   4. DELETE employees WHERE id = MIN(id);
   5. DELETE FROM employees GROUP BY name, department_id, salary;

5. Question 5

   Which description best defines an SQL window function?

   1. Performs a calculation across a set of rows related to the current row
   2. Checks if a value exists within a list of values
   3. Joins two or more tables based on a related column
   4. Completely removes a table from the database
   5. Encrypts column values for security

6. Question 6

   When pivoting data in SQL to count males and females per department, which technique is commonly used?

   1. CASE WHEN statements inside aggregate functions
   2. LEFT OUTER JOIN statements only
   3. UNION ALL without any conditionals
   4. COALESCE with NULL values
   5. ORDER BY with multiple columns

7. Question 7

   Which type of SQL join will include all rows from the left table and matching rows from the right table, filling NULLs for non-matches?

   1. LEFT JOIN
   2. RIGHT JOIN
   3. INNER JOIN
   4. FULL JOIN
   5. LEF JOIN

8. Question 8

   In SQL, which clause checks efficiently if a subquery returns any rows and is preferred in correlated subqueries for large datasets?

   1. EXISTS
   2. IN
   3. WHERE
   4. JOIN
   5. EXIST

9. Question 9

   What is the main purpose of a recursive CTE in SQL?

   1. To query hierarchical data like organizational charts
   2. To remove all records from a table
   3. To permanently store a summarized view
   4. To force a query to use an index
   5. To encrypt data within a table

10. Question 10

    Which of the following is a recommended step to optimize a slow SQL query?

    1. Use indexes on columns used in filters or joins
    2. Select all columns with SELECT *
    3. Avoid using any subqueries in all cases
    4. Create a backup of the table before every SELECT
    5. Always delete duplicate records before each query