Top 25 SQL Interview Questions for ZS Associates

SQL, or Structured Query Language, is a programming language used for managing and manipulating relational databases. As a data analyst or data engineer at ZS Associates, proficiency in SQL is crucial. In this article, we’ll explore some of the most commonly asked SQL interview questions to help you prepare for your next interview.

1. What is SQL? Explain its different types.

SQL is a language designed for managing and manipulating relational databases. It allows users to create, modify, and retrieve data from databases. There are several types of SQL:

• Data Definition Language (DDL): Used for defining database schemas, tables, indexes, and other database objects.
• Data Manipulation Language (DML): Used for inserting, updating, and deleting data within database tables.
• Data Query Language (DQL): Used for retrieving data from databases, typically using the SELECT statement.
• Data Control Language (DCL): Used for controlling access to data within a database, such as granting or revoking permissions.

2. What is the difference between SQL and MySQL?

SQL is a programming language used for managing and manipulating relational databases, while MySQL is an open-source relational database management system (RDBMS) that uses SQL as its query language. In other words, SQL is the language, and MySQL is a specific database software that implements SQL.

3. Explain the difference between INNER JOIN and OUTER JOIN.

• INNER JOIN: Returns rows from both tables where the join condition is met. It only includes records that have matching values in both tables.
• OUTER JOIN: Returns rows from both tables, even if the join condition is not met. There are three types of outer joins:
  • LEFT OUTER JOIN: Returns all rows from the left table and matching rows from the right table.
  • RIGHT OUTER JOIN: Returns all rows from the right table and matching rows from the left table.
  • FULL OUTER JOIN: Returns all rows from both tables, regardless of whether the join condition is met.

4. What is the difference between TRUNCATE TABLE and DROP TABLE statements?

• TRUNCATE TABLE removes all the data from a table but keeps the table structure intact. It also resets the auto-increment counter for the table.
• DROP TABLE removes the entire table structure from the database, including all data, indexes, and constraints.

Generally, TRUNCATE TABLE is faster than deleting all rows individually, but DROP TABLE is used when you want to remove the table completely.

5. Explain the difference between UNION and UNION ALL operators.

Both UNION and UNION ALL operators are used to combine the result sets of two or more SELECT statements, but they differ in how they handle duplicate rows:

• UNION removes duplicate rows from the combined result set.
• UNION ALL includes all rows from the combined result sets, including duplicates.

In general, UNION is slower than UNION ALL because it requires an extra step to remove duplicates.

6. What is a subquery in SQL? Provide an example.

A subquery is a SELECT statement nested within another SQL statement, such as SELECT, INSERT, UPDATE, or DELETE. Subqueries are used to retrieve data from multiple tables or perform complex calculations. Here’s an example:

SELECT employee_name, department_nameFROM employees eJOIN departments d ON e.department_id = d.department_idWHERE d.department_id = (    SELECT department_id    FROM departments    WHERE department_name = 'Sales');

In this example, the subquery (SELECT department_id FROM departments WHERE department_name = 'Sales') retrieves the department_id for the ‘Sales’ department, and the outer query selects the employee names and department names for employees in the ‘Sales’ department.

7. What is a correlated subquery? Provide an example.

A correlated subquery is a subquery that references a column from the outer query. It is executed once for each row in the outer query. Here’s an example:

SELECT employee_name, department_name, (    SELECT COUNT(*) + 1    FROM employees e2    WHERE e2.department_id = e1.department_id        AND e2.salary > e1.salary) as higher_paid_employeesFROM employees e1JOIN departments d ON e1.department_id = d.department_id;

In this example, the correlated subquery (SELECT COUNT(*) + 1 FROM employees e2 WHERE e2.department_id = e1.department_id AND e2.salary > e1.salary) is executed once for each employee in the outer query. It counts the number of employees in the same department who have a higher salary than the current employee, plus one (for the current employee).

8. What is a self-join? Provide an example.

A self-join is a join operation where a table is joined with itself. This is useful when dealing with hierarchical data or when you need to compare rows within the same table. Here’s an example:

SELECT e1.employee_name AS employee, m1.employee_name AS managerFROM employees e1JOIN employees m1 ON e1.manager_id = m1.employee_id;

In this example, the employees table is joined with itself to retrieve the employee name and the name of their manager. The manager_id column in the employees table is used to match employees with their managers.

9. What is the difference between WHERE and HAVING clauses?

The WHERE clause is used to filter rows before any group operations are performed, while the HAVING clause is used to filter groups after group operations have been performed.

• WHERE clause: Filters individual rows based on a condition.
• HAVING clause: Filters groups of rows based on a condition, typically used with aggregate functions like SUM, COUNT, AVG, etc.

Here’s an example:

SELECT department_id, AVG(salary) AS avg_salaryFROM employeesWHERE salary > 50000GROUP BY department_idHAVING AVG(salary) > 60000;

In this example, the WHERE clause filters out employees with a salary less than 50,000 before the group operation, and the HAVING clause filters out department groups with an average salary less than or equal to 60,000 after the group operation.

10. What is the purpose of the DISTINCT keyword in SQL?

The DISTINCT keyword is used to remove duplicate rows from the result set of a SELECT statement. It ensures that each row in the result set is unique.

For example:

SELECT DISTINCT department_nameFROM departments;

This query will return a list of unique department names from the departments table, eliminating any duplicates.

11. Explain the difference between INTERSECT and MINUS operators.

• INTERSECT operator: Returns the rows that are common to both the result sets of two SELECT statements.
• MINUS operator: Returns the rows that are present in the result set of the first SELECT statement but not in the second SELECT statement.

Here’s an example:

-- INTERSECTSELECT employee_id FROM employeesINTERSECTSELECT employee_id FROM payroll;-- MINUSSELECT employee_id FROM employeesMINUSSELECT employee_id FROM payroll;

The first query using INTERSECT will return employee IDs that are present in both the employees and payroll tables, while the second query using MINUS will return employee IDs that are present in the employees table but not in the payroll table.

12. What is the purpose of the CASE statement in SQL?

The CASE statement in SQL is used to perform conditional logic within a query. It allows you to evaluate a list of conditions and return a value based on the first condition that evaluates to true.

Here’s an example:

SELECT employee_name,    CASE        WHEN salary < 50000 THEN 'Low'        WHEN salary BETWEEN 50000 AND 100000 THEN 'Average'        ELSE 'High'    END AS salary_rangeFROM employees;

In this example, the CASE statement evaluates the salary column and assigns a salary range label (‘Low’, ‘Average’, or ‘High’) based on the specified conditions.

13. Explain the difference between COMMIT and ROLLBACK statements.

• COMMIT statement: Used to save the changes made to the database permanently. Once committed, the changes cannot be undone.
• ROLLBACK statement: Used to undo the changes made to the database since the last COMMIT or ROLLBACK statement. It reverts the database to the previous state.

These statements are typically used in transaction management to ensure data integrity and consistency.

14. What is the purpose of the ORDER BY clause?

The ORDER BY clause is used to sort the result set of a SELECT statement in ascending or descending order based on one or more columns.

Here’s an example:

SELECT employee_name, salaryFROM employeesORDER BY salary DESC, employee_name ASC;

In this example, the result set will be sorted in descending order based on the salary column, and if there are any ties in the salary column, the rows will be further sorted in ascending order based on the employee_name column.

15. What is a view in SQL? Provide an example.

A view is a virtual table that is created based on the result set of a SELECT statement. It does not store any data but provides a way to present data from one or more tables in a specific format.

Here’s an example:

CREATE VIEW employee_details ASSELECT employee_name, department_name, salaryFROM employees eJOIN departments d ON e.department_id = d.department_id;

In this example, the employee_details view is created by joining the employees and departments tables and selecting the employee_name, department_name, and salary columns. Once created, this view can be queried like a regular table.

16. What is the purpose of the EXISTS operator in SQL?

The EXISTS operator is used to test for the existence of rows in a subquery. It returns true if the subquery returns at least one row, and false otherwise.

Here’s an example:

SELECT employee_nameFROM employees eWHERE EXISTS (    SELECT 1    FROM projects p    WHERE p.project_manager_id = e.employee_id);

In this example, the query selects employee names from the employees table where the employee is a project manager for at least one project in the projects table.

17. What is a trigger in SQL? Provide an example.

A trigger is a special kind of stored procedure that is automatically executed when a specific event occurs, such as an INSERT, UPDATE, or DELETE operation on a table.

Here’s an example of a trigger that logs changes to the employees table:

CREATE TRIGGER log_employee_changesAFTER UPDATE ON employeesFOR EACH ROWINSERT INTO employee_logs (employee_id, old_salary, new_salary, change_date)VALUES (OLD.employee_id, OLD.salary, NEW.salary, CURRENT_DATE);

In this example, the log_employee_changes trigger is created to insert a new row into the employee_logs table every time an UPDATE operation is performed on the employees table. The trigger captures the old and new salary values, along with the employee ID and the current date.

18. What is the purpose of the LIKE operator in SQL?

The LIKE operator is used to perform pattern matching on string values. It allows you to search for records that match a specified pattern.

Here’s an example:

SELECT employee_nameFROM employeesWHERE employee_name LIKE 'A%';

In this example, the query selects employee names from the employees table where the name starts with the letter ‘A’. The wildcard % matches any sequence of characters.

19. Explain the difference between UNION and JOIN operations.

• UNION operation: Combines the result sets of two or more SELECT statements vertically by appending the rows from one result set to the other. It removes any duplicate rows from the combined result set.
• JOIN operation: Combines rows from two or more tables horizontally based on a related column between them. It allows you to retrieve data from multiple tables in a single query.

While UNION is used to combine the results of multiple queries, JOIN is used to combine data from multiple tables based on a relationship between them.

20. What is indexing in SQL? Explain its purpose.

Indexing in SQL is the process of creating an index on one or more columns of a table. An index is a data structure that stores a subset of data from the table in a way that allows for efficient data retrieval.

The primary purpose of indexing is to improve query performance by reducing the amount of data that needs to be scanned during a query execution. Indexes are particularly useful for columns that are frequently used in WHERE, ORDER BY, or JOIN clauses.

21. What is the purpose of the GROUP BY clause?

The GROUP BY clause is used to group rows with similar values into summary rows. It is often used in combination with aggregate functions like SUM, AVG, COUNT, and others to perform calculations on groups of rows.

Here’s an example:

SELECT department_id, AVG(salary) AS avg_salaryFROM employeesGROUP BY department_id;

In this example, the query groups the employees by their department_id and calculates the average salary (avg_salary) for each department.

22. Explain the difference between INNER JOIN and LEFT JOIN with examples.

• INNER JOIN: Returns only the rows where the join condition is met in both tables. It excludes any rows that do not have matching values in both tables.

Example:

SELECT e.employee_name, d.department_nameFROM employees eINNER JOIN departments d ON e.department_id = d.department_id;

This query will return employee names and their corresponding department names for employees who are associated with a department.

• LEFT JOIN: Returns all rows from the left table and the matching rows from the right table. If there is no match in the right table, the result will contain NULL values for the columns from the right table.

Example:

SELECT e.employee_name, d.department_nameFROM employees eLEFT JOIN departments d ON e.department_id = d.department_id;

This query will return all employee names, along with their department names if they are associated with a department. For employees who are not associated with any department, the department_name column will contain NULL values.

23. What is the purpose of the WITH clause in SQL?

The WITH clause, also known as a Common Table Expression (CTE), is used to define a temporary result set that can be referenced multiple times within a query. CTEs can simplify complex queries and improve readability by breaking the query into smaller, more manageable parts.

Here’s an example:

WITH top_employees AS (    SELECT employee_name, salary    FROM employees    ORDER BY salary DESC    LIMIT 10)SELECT *FROM top_employees;

In this example, the CTE top_employees is defined to select the top 10 employees with the highest salaries. The CTE result set is then used in the outer query to retrieve the employee names and salaries.

24. What is the purpose of the PIVOT and UNPIVOT operators in SQL?

• PIVOT operator: Used to rotate a table from rows to columns. It transforms unique values from one column into multiple columns in the output, and performs aggregations on the remaining column values.

• UNPIVOT operator: Used to rotate a table from columns to rows. It transforms multiple columns into row values, effectively inverting the operation of the PIVOT operator.

These operators are particularly useful for data analysis and reporting purposes, where you may need to restructure data from a row-oriented format to a column-oriented format, or vice versa.

25. Explain the difference between COALESCE and ISNULL functions.

• COALESCE function: Returns the first non-null value from the list of arguments passed to it. If all arguments are null, it returns null.

Example:

SELECT COALESCE(NULL, NULL, 'Default Value', NULL) AS result;

Output: result = 'Default Value'

• ISNULL function: Evaluates an expression and returns the second argument if the expression is null, otherwise, it returns the first argument.

Example:

SELECT ISNULL(NULL, 'Default Value') AS result;

Output: result = 'Default Value'

Both functions are useful for handling null values in SQL queries, but COALESCE allows you to provide multiple alternative values, while `