SQL Interview Questions

Let’s create some sample data to use with these questions and queries. We’ll define tables for employees, departments, and sales to illustrate the examples.

Sample Data

1. Employees Table: Contains information about employees, including their name, department, and salary.
2. Departments Table: Contains information about departments.
3. Sales Table: Contains information about sales made by employees, including product categories and sales amounts.

SQL Script to Create and Populate the Sample Data

-- Creating Employees Table
CREATE TABLE employees (
    employee_id INT PRIMARY KEY,
    name VARCHAR(50),
    department_id INT,
    salary DECIMAL(10, 2)
);

-- Inserting Data into Employees Table
INSERT INTO employees (employee_id, name, department_id, salary) VALUES
(1, 'Alice', 1, 60000),
(2, 'Bob', 2, 45000),
(3, 'Charlie', 1, 70000),
(4, 'David', 3, 55000),
(5, 'Eve', 2, 80000),
(6, 'Frank', 3, 50000),
(7, 'Grace', 1, 65000),
(8, 'Helen', 3, 75000),
(9, 'Ian', 2, 47000),
(10, 'Jack', 1, 58000);

-- Creating Departments Table
CREATE TABLE departments (
    department_id INT PRIMARY KEY,
    department_name VARCHAR(50)
);

-- Inserting Data into Departments Table
INSERT INTO departments (department_id, department_name) VALUES
(1, 'HR'),
(2, 'IT'),
(3, 'Sales');

-- Creating Sales Table
CREATE TABLE sales (
    sale_id INT PRIMARY KEY,
    employee_id INT,
    product_category VARCHAR(50),
    sales_amount DECIMAL(10, 2)
);

-- Inserting Data into Sales Table
INSERT INTO sales (sale_id, employee_id, product_category, sales_amount) VALUES
(1, 1, 'Electronics', 5000),
(2, 2, 'Furniture', 7000),
(3, 3, 'Electronics', 8000),
(4, 4, 'Clothing', 3000),
(5, 5, 'Furniture', 9000),
(6, 6, 'Electronics', 6000),
(7, 7, 'Clothing', 2000),
(8, 8, 'Electronics', 10000),
(9, 9, 'Furniture', 4000),
(10, 10, 'Electronics', 7500);

After creating table schema and inserting data your table data should look like below.

Employee Table

Department Table

Sales Table

Interview Questions

These questions cover the fundamental SQL concepts and functions you’ve learned, such as SUM, AVG, GROUP BY, ORDER BY, JOINS, and CONCAT. Practicing these will help solidify your understanding and prepare you for SQL-related interview questions.

Basic SQL Queries

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

Answer: WHERE is used to filter records before any groupings are made, while HAVING is used to filter records after the GROUP BY clause. WHERE cannot be used with aggregate functions, but HAVING can.

-- Using WHERE to filter before grouping
SELECT department, COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY department;

-- Using HAVING to filter after grouping
SELECT department, COUNT(*)
FROM employees
GROUP BY department
HAVING COUNT(*) > 5;

Q2. How do you use the SUM function to calculate the total sales from a sales table?

Answer: The SUM function adds up all values in a specified column.

SELECT SUM(sales_amount) AS total_sales FROM sales;

Q3. How would you calculate the average salary from an employees table?

Answer: The AVG function is used to calculate the average value of a numeric column.

SELECT AVG(salary) AS average_salary FROM employees;

Aggregation Functions

Q1. Can you explain how the GROUP BY clause works? Give an example.

Answer: The GROUP BY clause groups rows that have the same values into summary rows. It's often used with aggregate functions like SUM, AVG, etc.

SELECT department, SUM(salary) AS total_salary
FROM employees
GROUP BY department;

Q2. How would you use GROUP BY to find the total sales for each product in a sales table?

Answer: You can use GROUP BY on the product column to aggregate sales.

SELECT product_id, SUM(sales_amount) AS total_sales
FROM sales
GROUP BY product_id;

Q3. What is the purpose of the ORDER BY clause? How do you use it?

Answer: The ORDER BY clause is used to sort the result set in ascending or descending order based on one or more columns.

SELECT employee_name, salary
FROM employees
ORDER BY salary DESC;

Joins

Q1. What is a JOIN in SQL? Can you list and explain the different types of joins?

Answer: A JOIN is used to combine rows from two or more tables based on a related column. Types of joins include INNER JOIN, LEFT JOIN, RIGHT JOIN, and FULL OUTER JOIN.

-- INNER JOIN: Only returns matching rows
SELECT employees.name, departments.department_name
FROM employees
INNER JOIN departments ON employees.department_id = departments.id;

Q2. How do you perform an INNER JOIN between two tables? Provide an example.

Answer: An INNER JOIN returns only the rows that have matching values in both tables.

SELECT e.name, d.department_name
FROM employees e
INNER JOIN departments d ON e.department_id = d.id;

Q3. What is the difference between LEFT JOIN and RIGHT JOIN

Answer: LEFT JOIN returns all rows from the left table and matching rows from the right table. If no match, it returns NULL for columns from the right table.

RIGHT JOIN returns all rows from the right table and matching rows from the left table.

-- LEFT JOIN example
SELECT e.name, d.department_name
FROM employees e
LEFT JOIN departments d ON e.department_id = d.id;

-- RIGHT JOIN example
SELECT e.name, d.department_name
FROM employees e
RIGHT JOIN departments d ON e.department_id = d.id;

Q4. Can you explain what a FULL OUTER JOIN does and when you might use it?

Answer: A FULL OUTER JOIN returns all records when there is a match in either left or right table records. If there's no match, it returns NULL for unmatched columns.

SELECT e.name, d.department_name
FROM employees e
FULL OUTER JOIN departments d ON e.department_id = d.id;

Combining Data

Q1. How would you use the CONCAT function to combine the first and last name columns in an employees table?

Answer: CONCAT combines two or more strings into one string.

SELECT CONCAT(first_name, ' ', last_name) AS full_name
FROM employees;

Q2. What is the difference between CONCAT and CONCAT_WS in SQL?

Answer: CONCAT simply concatenates strings, while CONCAT_WS (Concatenate With Separator) adds a specified separator between strings.

-- Using CONCAT
SELECT CONCAT(first_name, last_name) AS full_name
FROM employees;

-- Using CONCAT_WS with a space separator
SELECT CONCAT_WS(' ', first_name, last_name) AS full_name
FROM employees;

Filtering and Sorting

Q1. How would you write a query to find all employees with a salary greater than the average salary in the company?

Answer: Use a subquery to calculate the average salary and filter employees with a salary above this average.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Q2. Can you use the ORDER BY clause to sort the results of a query by multiple columns? How?

Answer: Yes, you can specify multiple columns in the ORDER BY clause, separated by commas.

SELECT name, department, salary
FROM employees
ORDER BY department ASC, salary DESC;

Q3. What happens when you use ORDER BY with DESC? Provide an example.

Answer: Using ORDER BY with DESC sorts the results in descending order.

SELECT name, salary
FROM employees
ORDER BY salary DESC;

Complex Queries

Q1. How would you write a query to find the top 5 highest-paid employees in a company?

Answer: Use ORDER BY with DESC and the LIMIT clause to restrict the number of rows returned.

SELECT name, salary
FROM employees
ORDER BY salary DESC
LIMIT 5;

Q2. Write a query to calculate the total revenue generated by each product category in a sales table.

Answer: Use GROUP BY to group sales by product category and SUM to calculate total revenue.

SELECT product_category, SUM(sales_amount) AS total_revenue
FROM sales
GROUP BY product_category;

Q3. How can you use the GROUP BY clause to find duplicate entries in a table?

Answer: Group by the columns you want to check for duplicates and use HAVING COUNT(*) > 1.

SELECT name, COUNT(*)
FROM employees
GROUP BY name
HAVING COUNT(*) > 1;

Subqueries and Nested Queries

Q1. What is a subquery, and how would you use it to find employees who earn more than the average salary?

Answer: A subquery is a query nested inside another query. You can use it to filter main query results.

SELECT name, salary
FROM employees
WHERE salary > (SELECT AVG(salary) FROM employees);

Q2. Can you provide an example of using a subquery within the FROM clause of a query?

Answer: A subquery in the FROM clause is often referred to as a derived table. It allows the use of temporary results in further querying.

SELECT department, average_salary
FROM (
    SELECT department, AVG(salary) AS average_salary
    FROM employees
    GROUP BY department
) AS department_avg
WHERE average_salary > 50000;

SQL Quiz

SQL Quiz | SQL Online Test - javatpoint

W3Schools.com