SQL Interview Questions

Are you gearing up for an SQL interview and feeling a bit anxious? Fear not! With the right preparation and a solid understanding of key SQL concepts, you can walk into your interview with confidence. In this blog, we will break down the essential SQL concepts that you should know to excel in your SQL interview. Let's dive right in.

1. Aggregate Functions (SUM(), COUNT(), AVG(), MIN(), MAX())

Aggregate functions are essential when you need to summarize data in your SQL queries. Understanding how to use functions like SUM(), COUNT(), AVG(), MIN(), and MAX() is crucial for performing calculations on your data and extracting valuable insights.

For example, SELECT SUM(sales) FROM transactions can be used to calculate the total sales in a given table.

2. GROUP BY

The GROUP BY clause is used to group rows that have the same values in specified columns into summary rows. It is often used in conjunction with aggregate functions to produce summary statistics.

For instance, SELECT department, AVG(salary) FROM employees GROUP BY department groups employees by department and calculates the average salary for each department.

3. CASE WHEN Statement

The CASE WHEN statement is handy for creating conditional logic within your SQL queries. It allows you to define conditions and perform different actions based on those conditions.

For example, SELECT name, CASE WHEN age < 18 THEN 'Minor' ELSE 'Adult' END AS age_group FROM people categorizes individuals as minors or adults based on their age.

4. WHERE and HAVING

WHERE and HAVING clauses are used to filter data. WHERE is used to filter rows before they are grouped or aggregated, while HAVING is used to filter grouped or aggregated data.

For example, SELECT name FROM employees WHERE salary > 50000 retrieves names of employees with a salary greater than $50,000.

Now, you want to retrieve the names of employees with a salary greater than $50,000 and then further filter those names based on the number of characters in their names using the HAVING clause.

Here's the SQL query to achieve this:

SELECT name

FROM employees

WHERE salary > 50000

HAVING LENGTH(name) > 5;

In this query:

• The WHERE clause filters employees with a salary greater than $50,000, resulting in a subset of employees who meet this salary condition.

• The HAVING clause is used to filter the result set obtained from the WHERE clause. It checks the length of the name column and retains only those names with more than 5 characters.

So, the final result will include the names of employees with salaries over $50,000 and names that have more than 5 characters.

5. JOINs

Understanding different types of joins (INNER JOIN, LEFT JOIN, RIGHT JOIN, FULL JOIN) is crucial for combining data from multiple tables based on a related column. It allows you to fetch data from multiple sources in a meaningful way.

Let's explore the different types of joins—INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL JOIN—using an example with two sample tables: employees and departments. The employees table contains information about employees, including their employee_id, name, and department_id. The departments table includes details about departments, such as department_id and department_name.

Here's a brief explanation of each join type along with examples:

INNER JOIN:

An INNER JOIN returns only the rows that have matching values in both tables. It retrieves the intersection of the two tables.

SELECT employees.name, departments.department_name

FROM employees

INNER JOIN departments ON employees.department_id = departments.department_id;

In this example, you'll get a list of employees and their corresponding department names, but only for employees who are assigned to a department (i.e., they have a matching department_id in both tables).

LEFT JOIN (or LEFT OUTER JOIN):

A LEFT JOIN returns all rows from the left table (the first table specified) and the matched rows from the right table (the second table specified). If there are no matches in the right table, NULL values are returned.

SELECT employees.name, departments.department_name

FROM employees

LEFT JOIN departments ON employees.department_id = departments.department_id;

This query will give you a list of all employees and their corresponding department names. If an employee is not assigned to a department, the department_name will be NULL.

In this example, let's assume we have the following sample data in our tables:

Employees Table: 

RIGHT JOIN (or RIGHT OUTER JOIN):

A RIGHT JOIN is similar to a LEFT JOIN, but it returns all rows from the right table and the matched rows from the left table. If there are no matches in the left table, NULL values are returned.

sql

Copy code

SELECT employees.name, departments.department_name

FROM employees

RIGHT JOIN departments ON employees.department_id = departments.department_id;

This query retrieves a list of all departments and the employees assigned to those departments. If there are departments without employees, the name of the employees will be NULL.

FULL JOIN (or FULL OUTER JOIN):

A FULL JOIN returns all rows from both tables, with NULL values in the columns where there are no matches.

sql

Copy code

SELECT employees.name, departments.department_name

FROM employees

FULL JOIN departments ON employees.department_id = departments.department_id;

This query provides a complete list of employees and departments, including those without matches. If an employee has no assigned department or a department has no assigned employees, you'll see NULL values in the respective columns.

These examples should help you understand how each type of join works and when to use them based on your specific data retrieval needs.

6. UNION and UNION ALL

The UNION operator is used to combine the result sets of two or more SELECT statements into a single result set, removing duplicate rows. UNION ALL retains duplicate rows.

7. Subqueries

Subqueries, also known as nested queries, are queries placed within another query. They are used to retrieve data needed for the main query. Subqueries can appear in the SELECT, FROM, WHERE, or HAVING clause of a SQL statement.

8. Joins and Self-Joins

Self-joins occur when you join a table to itself. This is useful when dealing with hierarchical data structures, such as organizational charts or social networks.

9. Window Functions

Window functions allow you to perform calculations across a set of table rows related to the current row. They are used for tasks like calculating moving averages or finding rank within a group.

10. CTEs (Common Table Expressions)

CTEs are a way to create temporary result sets that you can reference within your main query. They are particularly useful for breaking down complex queries into more manageable parts.

11. GROUP BY Extensions (ROLLUP, CUBE, GROUPING SETS)

These extensions to the GROUP BY clause provide advanced grouping and summarization options. They are especially valuable when you need to create multi-level summary reports or pivot tables.

In your SQL interview preparation, make sure to practice these concepts by writing SQL queries and testing them on sample databases. Additionally, brush up on the specifics of the SQL dialect used by the company you're interviewing with, as SQL implementations can vary.

Remember, the key to mastering SQL and acing your interview is practice and understanding these fundamental concepts. Good luck, and go rock that SQL interview!