Most asked SQL Interview Questions

 [PART 1] → [Beginner — Intermediate]

SQL

## Question 1.

Difference between Truncate, Drop, Delete.

DROP statement can be used to remove any database objects like tables, views, functions, procedures, triggers etc.
Delete is a DML statement hence we will need to commit the transaction in order to save the changes to database. Whereas truncate and drop are DDL statements hence no commit is required.

For example: Below statement will delete only the records from employee table where the name is ‘Tanya’

DELETE FROM employee WHERE name = ‘Tanya’;
COMMIT;

Below statement will delete all records from the employee table.
DELETE FROM employee;
COMMIT;

Below statement will also delete all the records from the employee table. No commit is required here.

TRUNCATE TABLE employee;

## Question 2.

Difference between RANK, DENSE_RANK and ROW_NUMBER window function.

RANK() function will assign a rank to each row within each partitioned result set. If multiple rows have the same value then each of these rows will share the same rank. However the rank of the following (next) rows will get skipped. Meaning for each duplicate row, one rank value gets skipped.

DENSE_RANK() function will assign a rank to each row within each partitioned result set. If multiple rows have the same value then each of these rows will share the same rank. However the dense_rank of the following (next) rows will NOT get skipped. This is the only difference between rank and dense_rank. RANK() function skips a rank if there are duplicate rows whereas DENSE_RANK() function will never skip a rank.

ROW_NUMBER() function will assign a unique row number to every row within each partitioned result set. It does not matter if the rows are duplicate or not.

By using the managers table, let’s write a query to get the rank, dense rank and row number for each manager based on their salary.

SELECT *
, RANK() OVER(ORDER BY salary DESC) AS ranks
, DENSE_RANK() OVER(ORDER BY salary DESC) AS dense_ranks
, ROW_NUMBER() OVER(ORDER BY salary DESC) AS row_numbers
FROM managers;

Table Name: MANAGERS
Contains salary details of 5 different managers.

Manager Table

Result from above query

## Question 3.

Difference between Unique, primary keys, foreign keys.

Primary key, unique key and foreign key are constraints we can create on a table.

When you make a column in the table as primary key then this column will always have unique or distinct values. Duplicate values and NULL value will not be allowed in a primary key column. A table can only have one primary key. Primary key can be created either on one single column or a group of columns.

When you make a column in the table as unique key then this column will always have unique or distinct values. Duplicate values will not be allowed. However, NULL values are allowed in a column which has unique key constraint. This is the major difference between primary and unique key.

Foreign key is used to create a master child kind of relationship between two tables. When we make a column in a table as foreign key, this column will then have to be referenced from another column from some other table.

## Question 4.

Difference between “WHERE ” and “HAVING”clause.

WHERE clause is used to filter records from the table. We can also specify join conditions between two tables in the WHERE clause. If a SQL query has both WHERE and GROUP BY clause then the records will first get filtered based on the conditions mentioned in WHERE clause before the data gets grouped as per the GROUP BY clause.
Conditions specified in the WHERE clause are applied to individual rows in the table.

Whereas HAVING clause is used to filter records returned from the GROUP BY clause. So if a SQL query has WHERE, GROUP BY and HAVING clause then first the data gets filtered based on WHERE condition, only after this grouping of data takes place. Finally based on the conditions in HAVING clause the grouped data again gets filtered.
Conditions specified in the HAVING clause are applied to aggregated values, not individual rows.

## Question 5.

Difference between PARTITION BY and GROUP BY.

• The GROUP BY clause is used in combination with aggregate functions to group rows based on one or more columns.
• It is typically used in queries where you want to perform aggregate calculations (such as SUM, COUNT, AVG, etc.) on groups of rows that share common values in specified columns.
• The GROUP BY clause is applied before the SELECT clause in the query execution.

-- Using GROUP BY
SELECT department, AVG(salary) AS avg_department_salary
FROM employees
GROUP BY department;

Output:

| department | avg_department_salary |
|------------|-----------------------|
| HR         | 52500.00              |
| IT         | 65000.00              |

• The PARTITION BY clause is used with window functions, which are a set of functions that perform calculations across a specific range of rows related to the current row within the result set.
• PARTITION BY divides the result set into partitions to which the window function is applied separately. It doesn't group the rows in the same way as GROUP BY.
• The PARTITION BY clause is applied after the window function in the query execution.

-- Using PARTITION BY
SELECT employee_id, department, salary,
       AVG(salary) OVER (PARTITION BY department) AS avg_department_salary
FROM employees;

Output:

| employee_id | department | salary   | avg_department_salary |
|-------------|------------|----------|-----------------------|
| 1           | HR         | 50000.00 | 52500.00              |
| 2           | HR         | 55000.00 | 52500.00              |
| 3           | IT         | 60000.00 | 65000.00              |
| 4           | IT         | 65000.00 | 65000.00              |
| 5           | IT         | 70000.00 | 65000.00              |

## Question 6.

Imagine there is a FULL_NAME column in a table which has values like “Elon Musk“, “Bill Gates“, “Jeff Bezos“ etc. So each full name has a first name, a space and a last name. Which functions would you use to fetch only the first name from this FULL_NAME column? Give example.

SELECT
    SUBSTR(full_name, 1, POSITION(' ' IN full_name) - 1) as first_name
FROM
    your_table_name;

• SUBSTR(full_name, 1, POSITION(' ' IN full_name) - 1): This part of the query uses the SUBSTR function to extract a substring from the full_name column. The arguments are as follows:
• full_name: The source string from which the substring is extracted.
• 1: The starting position of the substring (in this case, from the beginning of the full_name).
• POSITION(' ' IN full_name) - 1: The length of the substring. It calculates the position of the space (' ') in the full_name column using the POSITION function and subtracts 1 to exclude the space itself.
• as first_name: This part of the query assigns the extracted substring an alias "first_name" for the result set.

## Question 7.

How can you convert a text into date format? Consider the given text as “31–01–2021“.

In SQL, the TO_DATE function is commonly used to convert a text representation of a date into an actual date format. The syntax of the TO_DATE function varies across database systems, but you provided an example that looks like it's intended for a system using the 'DD-MM-YYYY' format.

Here’s an explanation of the SQL query you provided:

SELECT TO_DATE('31-01-2023', 'DD-MM-YYYY') as date_value;

• TO_DATE('31-01-2021', 'DD-MM-YYYY'): This part of the query uses the TO_DATE function to convert the text '31-01-2021' into a date format. The first argument ('31-01-2021') is the text representation of the date, and the second argument ('DD-MM-YYYY') is the format of the date in the input text.
• as date_value: This part of the query assigns an alias 'date_value' to the result, which is the converted date.

## Question 8.

Why do we use CASE Statement in SQL? Give example

CASE statement is similar to IF ELSE statement from any other programming languages. We can use it to fetch or show a particular value based on certain condition.
CASE statement in SQL is used to perform conditional logic within a query.

Here’s a simple example of using the CASE statement in a SELECT query:

SELECT
    employee_name,
    salary,
    CASE
        WHEN salary > 50000 THEN 'High Salary'
        WHEN salary > 30000 THEN 'Medium Salary'
        ELSE 'Low Salary'
    END AS salary_category
FROM
    employees;

In this example, the CASE statement is used to categorize employees based on their salary. If the salary is greater than 50,000, the category is 'High Salary.' If the salary is between 30,000 and 50,000, the category is 'Medium Salary.' Otherwise, the category is 'Low Salary.'

## Question 9.

What is the difference between LEFT, RIGHT, FULL outer join and INNER join?

Joins

In order to understand this better, let’s consider two tables CONTINENTS and COUNTRIES as shown below. I shall show sample queries considering these two tables.

Table Name: CONTINENTS

Has data of 6 continents. Please note the continent “Antarctica” is intentionally missed from this table.

Table Name: COUNTRIES

Has data of one country from each continent. Please note that I have intentionally missed to add a country from Europe in this table.

INNER JOIN will fetch only those records which are present in both the joined tables. The matching of the records is only based on the columns used for joining these two tables. INNER JOIN can also be represented as JOIN in your SELECT query.

INNER JOIN Query
SELECT cr.country_name, ct.continent_name
FROM continents ct
INNER JOIN countries cr
ON ct.continent_code = cr.continent_code;

LEFT JOIN will fetch all records from the left table (table placed on the left side during the join) even if those records are not present in right table (table placed on the right side during the join). If your select clause has a column from the right table then for records which are not present in right table (but present in left table), SQL will return a NULL value. LEFT JOIN can also be represented as LEFT OUTER JOIN in your SELECT query.

LEFT JOIN Query
SELECT cr.country_name, ct.continent_name
FROM continents ct
LEFT JOIN countries cr
ON ct.continent_code = cr.continent_code;

RIGHT JOIN will fetch all records from the right table (table placed on the right side during the join) even if those records are not present in left table (table placed on the left side during the join). If your select clause has a column from the left table then for records which are not present in left table (but present in right table), SQL will return a NULL value. RIGHT JOIN can also be represented as RIGHT OUTER JOIN in your SELECT query.

*Note: LEFT and RIGHT join depends on whether the table is placed on the left side of the JOIN or on the right side of the JOIN.

RIGHT JOIN Query
SELECT cr.country_name, ct.continent_name
FROM continents ct
RIGHT JOIN countries cr
ON ct.continent_code = cr.continent_code;

FULL JOIN will fetch all records from both left and right table. It’s kind of combination of INNER, LEFT and RIGHT join. Meaning FULL JOIN will fetch all the matching records in left and right table + all the records from left table (even if these records are not present in right table) + all the records from right table (even if these records are not present in left table). FULL JOIN can also be represented as FULL OUTER JOIN in your SELECT query.

FULL OUTER JOIN Query
SELECT cr.country_name, ct.continent_name
FROM continents ct
FULL OUTER JOIN countries cr
on ct.continent_code = cr.continent_code;

Also check for what is SELF join, NATURAL join and CROSS join?

SELF JOIN is when you join a table to itself. There is no keyword like SELF when doing this join. We just use the normal INNER join to do a self join. Just that instead of doing an inner join with two different table, we inner join the same table to itself. Just that these tables should have different alias name. Other than this, SELF join performs similar to INNER join.

SELF JOIN Query
SELECT cr1.country_name
FROM countries cr1
JOIN countries cr2ON cr1.country_code = cr2.continent_code;

NATURAL JOIN is similar to INNER join but we do not need to use the ON clause during the join. Meaning in a natural join we just specify the tables. We do not specify the columns based on which this join should work. By default when we use NATURAL JOIN, SQL will join the two tables based on the common column name in these two tables. So when doing the natural join, both the tables need to have columns with same name and these columns should have same data type.

NATURAL JOIN Query
SELECT cr.country_name, ct.continent_name
FROM continents ct
NATURAL JOIN countries cr;

CROSS JOIN will join all the records from left table with all the records from right table. Meaning the cross join is not based on matching any column. Whether there is a match or not, cross join will return records which is basically number of records in left table multiplied by number of records in right table. In other words, cross join returns a Cartesian product.

CROSS JOIN Query
SELECT cr.country_name, ct.continent_name
FROM continents ct
CROSS JOIN countries cr;

## Question 10.

Can we use aggregate function as window function? If yes then how do we do it?

Yes, we can use aggregate function as a window function by using the OVER clause. Aggregate function will reduce the number of rows or records since they perform calculation of a set of row values to return a single value. Whereas window function does not reduce the number of records.

Now, let’s use the SUM function as a window function to calculate the running total salary within each department based on the order of salaries:

SELECT
    employee_id,
    employee_name,
    department,
    salary,
    SUM(salary) OVER (PARTITION BY department ORDER BY salary) AS running_total_salary
FROM
    employees;

Output:

| employee_id | employee_name | department | salary   | running_total_salary |
|-------------|---------------|------------|----------|-----------------------|
| 1           | John          | HR         | 50000.00 | 50000.00              |
| 3           | Bob           | HR         | 55000.00 | 105000.00             |
| 2           | Jane          | IT         | 60000.00 | 60000.00              |
| 4           | Alice         | IT         | 70000.00 | 130000.00             |

In this example, the running_total_salary column represents the running total salary within each department, calculated based on the ascending order of salaries. The PARTITION BY clause is used to partition the result set by the department column. The ORDER BY clause specifies the order of rows within each partition based on the salary column. The SUM function is applied as a window function, and it calculates the running total salary for each row within its department.

SQL is a powerful tool for managing and manipulating data in relational databases. While basic SQL queries are essential, mastering advanced SQL queries empowers data professionals to extract complex insights and perform sophisticated data transformations. In this article, we’ll explore 20 advanced SQL queries that cover a wide range of scenarios, from subqueries to window functions, with practical code examples.

Photo from Pexels

1. Subqueries

Subqueries allow us to nest one query inside another, enabling more complex data retrieval and filtering.

Example:

SELECT *
FROM employees
WHERE department_id IN (SELECT department_id FROM departments WHERE location_id = 1700);

2. Joins

SQL joins combine rows from two or more tables based on a related column between them.

Example:

SELECT e.employee_id, e.first_name, e.last_name, d.department_name
FROM employees e
JOIN departments d ON e.department_id = d.department_id;

3. Aggregate Functions

Aggregate functions perform calculations on a set of values and return a single value.

Example:

SELECT AVG(salary) AS avg_salary
FROM employees;

4. Window Functions

Window functions operate on a set of rows related to the current row within a query result.

Example:

SELECT employee_id, salary, 
       AVG(salary) OVER (PARTITION BY department_id) AS avg_salary_by_dept
FROM employees;

5. Common Table Expressions (CTEs)

CTEs are temporary result sets that can be referenced within a SELECT, INSERT, UPDATE, or DELETE statement.

Example:

WITH high_salary_employees AS (
    SELECT *
    FROM employees
    WHERE salary > 100000
)
SELECT * FROM high_salary_employees;

6. Pivot Tables

Pivot tables reorganize data from rows into columns, summarizing it in the