Top 50 SQL Interview Questions and Answers
1.What is SQL?
SQL stands for Structured Query Language, which is a programming language used to communicate with databases.
2.What are the different types of SQL statements?
There are mainly four types of SQL statements:
DDL (Data Definition Language): used to define the database schema, such as creating tables, altering tables, and dropping tables.
DML (Data Manipulation Language): used to manipulate the data in the database, such as inserting, updating, and deleting data from tables.
DCL (Data Control Language): used to control access to the database, such as granting and revoking permissions.
TCL (Transaction Control Language): used to control transactions in the database, such as committing or rolling back transactions.
3.What is a database?
A database is an organized collection of data stored and accessed electronically. It contains tables, columns, rows, and relationships between them.
4.What is a table in SQL?
A table is a collection of data in a database. It consists of rows and columns. Each column represents a specific attribute of the data, and each row represents a specific record.
5.What is a primary key?
A primary key is a unique identifier for a row in a table. It is used to enforce data integrity and ensure that each row can be uniquely identified.
Example:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50),
customer_email VARCHAR(50)
);
6.What is a foreign key?
A foreign key is a column or a set of columns in a table that refers to the primary key of another table. It is used to establish a relationship between two tables.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
customer_id INT,
order_date DATE,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
7.What is normalization in SQL?
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves creating tables and establishing relationships between them to avoid data duplication and maintain consistency.
8.What is a join in SQL?
A join is a way to combine rows from two or more tables based on a related column between them. There are different types of joins, such as inner join, left join, right join, and full outer join.
Example:
SELECT customers.customer_name, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
9.What is a subquery in SQL?
A subquery is a query that is nested inside another query. It is used to retrieve data from one table based on the result of another query.
Example:
SELECT customer_name
FROM customers
WHERE customer_id IN (SELECT customer_id FROM orders);
10.What is a view in SQL?
A view is a virtual table that is based on the result of a SELECT statement. It does not store any data, but it can be used as a regular table for querying.
Example:
CREATE VIEW order_details AS
SELECT customers.customer_name, orders.order_date, order_items.product_name, order_items.quantity
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id
INNER JOIN order_items ON orders.order_id = order_items.order_id;
11.What is a stored procedure in SQL?
A stored procedure is a precompiled set of SQL statements that can be executed on demand. It is used to perform a specific task or a set of tasks, and it can be reused multiple times.
Example:
CREATE PROCEDURE get_customer_orders
@customer_id INT
AS
BEGIN
SELECT order_id, order_date
FROM orders
WHERE customer_id = @customer_id
END;
12.What is a trigger in SQL?
A trigger is a special type of stored procedure that is automatically executed in response to certain events, such as insert, update, or delete operations on a table. It can be used to enforce business rules, audit data changes, or perform complex calculations.
Example:
CREATE TRIGGER update_order_total
ON orders
AFTER UPDATE
AS
BEGIN
UPDATE orders
SET order_total = order_items.quantity * order_items.unit_price
FROM orders
INNER JOIN order_items ON orders.order_id = order_items.order_id
END;
13.What is the difference between WHERE and HAVING clauses in SQL?
The WHERE clause is used to filter rows based on a condition, whereas the HAVING clause is used to filter groups based on a condition. The WHERE clause is applied before grouping, whereas the HAVING clause is applied after grouping.
Example:
SELECT customer_id, SUM(order_total)
FROM orders
GROUP BY customer_id
HAVING SUM(order_total) > 1000;
14.What is a NULL value in SQL?
A NULL value represents a missing or unknown value in a database. It is not the same as an empty string or a zero value. A column that allows NULL values can contain no value, a single value, or multiple NULL values.
15.What is the difference between INNER JOIN and OUTER JOIN in SQL?
An INNER JOIN returns only the rows that have matching values in both tables, whereas an OUTER JOIN returns all the rows from one table and the matching rows from another table, or NULL values for non-matching rows.
Example:
SELECT customers.customer_name, orders.order_date
FROM customers
INNER JOIN orders ON customers.customer_id = orders.customer_id;
SELECT customers.customer_name, orders.order_date
FROM customers
LEFT OUTER JOIN orders ON customers.customer_id = orders.customer_id;
16.What is the difference between DELETE and TRUNCATE in SQL?
DELETE is a DML statement that removes rows from a table based on a condition, whereas TRUNCATE is a DDL statement that removes all the rows from a table without logging the individual row deletions. TRUNCATE is faster and less resource-intensive than DELETE, but it cannot be rolled back.
Example:
DELETE FROM orders WHERE order_date < '2022-01-01';
TRUNCATE TABLE orders;
17.What is the difference between UNION and UNION ALL in SQL?
UNION combines the result sets of two or more SELECT statements and removes duplicates, whereas UNION ALL combines the result sets without removing duplicates.
Example:
SELECT customer_name FROM customers
UNION
SELECT customer_name FROM suppliers;
SELECT customer_name FROM customers
UNION ALL
SELECT customer_name FROM suppliers;
18.What is the difference between GROUP BY and ORDER BY in SQL?
GROUP BY is used to group rows based on a common value, whereas ORDER BY is used to sort rows based on one or more columns. GROUP BY creates summary rows for each group, whereas ORDER BY does not.
Example:
SELECT customer_id, COUNT(order_id) FROM orders
GROUP BY customer_id
ORDER BY COUNT(order_id) DESC;
19.What is a clustered index in SQL?
A clustered index is a type of index that physically organizes the data in a table based on the values of one or more columns. It determines the order of the data on disk and affects the performance of queries that involve range searches or sorting.
Example:
CREATE CLUSTERED INDEX idx_orders_order_date
ON orders(order_date);
20.What is a non-clustered index in SQL?
A non-clustered index is a type of index that creates a separate data structure to store the index keys and pointers to the data rows. It does not affect the physical order of the data in the table but can speed up queries that involve filtering or sorting.
Example:
CREATE NONCLUSTERED INDEX idx_customers_customer_name
ON customers(customer_name);
21.What is normalization in SQL?
Normalization is the process of organizing data in a database to reduce redundancy and dependency. It involves dividing large tables into smaller tables and defining relationships between them to eliminate data duplication and maintain data integrity.
Example:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50),
customer_email VARCHAR(50)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATETIME,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
22.What is denormalization in SQL?
Denormalization is the process of intentionally adding redundancy to a database to improve performance or simplify queries. It involves combining related tables into a single table or duplicating data across multiple tables.
Example:
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATETIME,
customer_name VARCHAR(50),
customer_email VARCHAR(50),
order_total DECIMAL(10, 2)
);
23.What is a view in SQL?
A view is a virtual table that is based on the result of a SELECT statement. It does not store data on its own but retrieves data from the underlying tables each time it is queried. Views can be used to simplify complex queries, restrict access to sensitive data, or provide a customized view of the data for different users.
Example:
CREATE VIEW high_value_customers AS
SELECT customer_id, customer_name
FROM customers
WHERE total_orders > 10;
24.What is a stored procedure in SQL?
A stored procedure is a precompiled set of SQL statements that can be executed repeatedly by calling its name. It can accept input parameters, perform calculations, manipulate data, and return output parameters or result sets. Stored procedures can be used to encapsulate business logic, improve performance, or enforce security.
Example:
CREATE PROCEDURE update_order_total
@order_id INT,
@new_total DECIMAL(10, 2)
AS
BEGIN
UPDATE orders
SET order_total = @new_total
WHERE order_id = @order_id
END;
25.What is a user-defined function in SQL?
A user-defined function is a reusable code block that performs a specific calculation or operation and returns a scalar value or a table. It can be used in SELECT, WHERE, and HAVING clauses, or as a parameter in a stored procedure. User-defined functions can be deterministic or non-deterministic and can have input parameters and output parameters.
Example:
CREATE FUNCTION calculate_discount(@order_total DECIMAL(10, 2))
RETURNS DECIMAL(10, 2)
AS
BEGIN
DECLARE @discount DECIMAL(10, 2)
IF @order_total > 1000
SET @discount = @order_total * 0.1
ELSE
SET @discount = 0
RETURN @discount
END;
26.What is the difference between a primary key and a unique key in SQL?
A primary key is a column or a set of columns that uniquely identifies each row in a table and cannot contain NULL values. It is used to enforce data integrity and create relationships between tables. A unique key is a column or a set of columns that also uniquely identifies each row in a table but can contain NULL values. It is used to enforce data integrity and prevent duplicate values.
Example:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50),
customer_email VARCHAR(50),
CONSTRAINT uk_customers_customer_email UNIQUE (customer_email)
);
27.What is a foreign key in SQL?
A foreign key is a column or a set of columns in one table that refers to the primary key or a unique key in another table. It is used to create a relationship between two tables and enforce referential integrity, which ensures that data in the related tables is consistent.
Example:
CREATE TABLE customers (
customer_id INT PRIMARY KEY,
customer_name VARCHAR(50),
customer_email VARCHAR(50)
);
CREATE TABLE orders (
order_id INT PRIMARY KEY,
order_date DATETIME,
customer_id INT,
FOREIGN KEY (customer_id) REFERENCES customers(customer_id)
);
28.What is a self join in SQL?
A self join is a join operation where a table is joined with itself based on a common column. It is used to retrieve data from a table that has a hierarchical structure or a recursive relationship.
Example:
CREATE TABLE employees (
employee_id INT PRIMARY KEY,
employee_name VARCHAR(50),
manager_id INT
);
SELECT e1.employee_name AS employee_name, e2.employee_name AS manager_name
FROM employees e1
INNER JOIN employees e2 ON e1.manager_id = e2.employee_id;
29.What is a correlated subquery in SQL?
A correlated subquery is a subquery that references one or more columns from the outer query. It is used to filter rows based on a condition that depends on the values of the outer query.
Example:
SELECT product_name, unit_price
FROM products p1
WHERE unit_price > (
SELECT AVG(unit_price)
FROM products p2
WHERE p1.category_id = p2.category_id
);
30.What is a common table expression (CTE) in SQL?
A common table expression (CTE) is a named temporary result set that can be used within a SELECT, INSERT, UPDATE, or DELETE statement. It is defined using a WITH clause and can reference itself recursively.
Example:
WITH cte_sales AS (
SELECT product_id, SUM(quantity) AS total_sales
FROM order_details
GROUP BY product_id
)
SELECT p.product_name, c.total_sales
FROM products p
INNER JOIN cte_sales c ON p.product_id = c.product_id;
31.What is a subquery in SQL?
A subquery is a query that is embedded within another query as a nested SELECT statement. It is used to retrieve data from one or more tables based on a condition that depends on the values of the outer query.
Example:
SELECT product_name, unit_price
FROM products
WHERE category_id IN (
SELECT category_id
FROM categories
WHERE category_name = 'Beverages'
);
32.What is a trigger in SQL?
A trigger is a special type of stored procedure that is automatically executed in response to a specific event, such as INSERT, UPDATE, or DELETE operations on a table. It can be used to enforce business rules, audit changes, or replicate data.
Example:
CREATE TRIGGER audit_orders
ON orders
AFTER INSERT, UPDATE, DELETE
AS
BEGIN
INSERT INTO order_audit (order_id, audit_date, audit_type)
VALUES (i.order_id, GETDATE(), CASE
WHEN EXISTS (SELECT * FROM deleted) AND EXISTS (SELECT * FROM inserted)
THEN 'Update'
WHEN EXISTS (SELECT * FROM deleted)
THEN 'Delete'
ELSE 'Insert'
END)
END;
33.What is a cursor in SQL?
A cursor is a database object that is used to retrieve and manipulate rows from a result set one at a time. It can be used to perform operations that are not possible or efficient with a single SQL statement.
Example:
DECLARE cursor_orders CURSOR FOR
SELECT order_id, order_date, customer_id
FROM orders
WHERE order_total > 1000
OPEN cursor_orders
FETCH NEXT FROM cursor_orders INTO @order_id, @order_date, @customer_id
WHILE @@FETCH_STATUS =0
BEGIN
PRINT 'Processing order ' + CAST(@order_id AS VARCHAR(10))
-- Perform operations on the current row
FETCH NEXT FROM cursor_orders INTO @order_id, @order_date, @customer_id
END
CLOSE cursor_orders
DEALLOCATE cursor_orders;
34.What is normalization in SQL?
Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. It involves dividing large tables into smaller tables and defining relationships between them.
Example:
Consider the following table that contains information about customers and their orders:
customers:
| customer_id | customer_name | customer_email |
|---|---|---|
| 1 | Alice | alice@example.com |
| 2 | Bob | bob@example.com |
orders:
| order_id | order_date | order_total | customer_id |
|---|---|---|---|
| 1 | 2022-01-01 | 100 | 1 |
| 2 | 2022-01-02 | 200 | 2 |
| 3 | 2022-01-03 | 150 | 1 |
35.What is denormalization in SQL?
Denormalization is the process of intentionally adding redundancy to a database to improve performance. It involves duplicating data across tables to avoid joins or aggregations that can be computationally expensive.
Example:
Consider the following normalized tables:
customers:
orders:
To denormalize these tables, we can add a total_spent column to the customers table:
customers:
This avoids the need to join the orders table to compute the total amount spent by each customer.
36.What is an index in SQL?
An index is a database object that is used to speed up queries by providing fast access to data based on the values of one or more columns. It is created using the CREATE INDEX statement and can be unique or non-unique.
Example:
CREATE INDEX idx_orders_customer_id ON orders (customer_id);
This creates an index on the customer_id column of the orders table, which can be used to quickly retrieve all orders for a given customer.
37.What is a materialized view in SQL?
A materialized view is a database object that contains the results of a query that has been precomputed and stored in a table. It can be used to improve query performance by avoiding the need to compute the
same query repeatedly.
Example:
CREATE MATERIALIZED VIEW mv_top_customers AS
SELECT c.customer_id, c.customer_name, SUM(o.order_total) AS total_spent
FROM customers c
JOIN orders o ON c.customer_id = o.customer_id
GROUP BY c.customer_id
ORDER BY total_spent DESC
LIMIT 10;
This creates a materialized view called mv_top_customers that contains the top 10 customers based on their total spending. The results of the query are precomputed and stored in a table, which can be accessed quickly.
38.What is a subquery in SQL?
A subquery is a query that is nested inside another query and is used to retrieve data that will be used in the outer query. It can be used in the SELECT, FROM, WHERE, and HAVING clauses of a query.
Example:
SELECT customer_name
FROM customers
WHERE customer_id IN (
SELECT customer_id
FROM orders
WHERE order_total > 100
);
This query retrieves the names of customers who have placed orders with a total value greater than 100. The subquery is used to retrieve the customer IDs for these orders, which are then used to filter the customers table.
39.What is a correlated subquery in SQL?
A correlated subquery is a subquery that is evaluated for each row of the outer query. It uses values from the outer query in its WHERE clause to retrieve data that will be used in the outer query.
Example:
SELECT customer_name,
(SELECT COUNT(*)
FROM orders o
WHERE o.customer_id = c.customer_id
AND o.order_date > '2022-01-01') AS num_orders
FROM customers c;
This query retrieves the names of customers and the number of orders they placed after January 1, 2022. The subquery is correlated with the outer query by using the customer_id value from the customers table to retrieve the number of orders for each customer.
40.What is a self-join in SQL?
A self-join is a join in which a table is joined with itself. It is used to retrieve information about related rows in the same table.
Example:
Consider the following table that contains information about employees and their managers:
To retrieve the names of employees and their managers, we can use a self-join:
SELECT e.employee_name, m.employee_name AS manager_name
FROM employees e
JOIN employees m ON e.manager_id = m.employee_id;
This query retrieves the names of employees and their managers. The employees table is joined with itself using the manager_id and employee_id columns.
41.What is a cross join in SQL?
A cross join is a join in which every row of one table is combined with every row of another table. It is used to generate all possible combinations of rows from two tables.
Example:
Consider the following tables:
customers:
products:
To generate all possible combinations of customers and products, we can use a cross join:
SELECT *
FROM customers
CROSS JOIN products;
This query generates a result set that contains all possible combinations of customers and products.
42.What is a natural join in SQL?
A natural join is a join that combines two tables based on their column names. It matches columns with the same name in both tables and returns the rows where the values in those columns are equal.
Example
Consider the following two tables:
employees:
departments:
To retrieve the names of employees and their department names, we can use a natural join:
SELECT employee_name, department_name
FROM employees
NATURAL JOIN departments;
This query joins the employees and departments tables based on their common column department_id and retrieves the employee_name and department_name columns.
43.What is an outer join in SQL?
An outer join is a join in which all rows from one table are included in the result set, even if there is no matching row in the other table. It is used to retrieve data from one table that may or may not have a matching row in another table.
Example:
Consider the following two tables:
employees:
departments:
To retrieve the names of employees and their department names, including those employees who do not have a department assigned, we can use an outer join:
SELECT employee_name, department_name
FROM employees
LEFT JOIN departments ON employees.department_id = departments.department_id;
This query retrieves the employee_name and department_name columns and includes all rows from the employees table, even if there is no matching row in the departments table.
44.What is a full outer join in SQL?
A full outer join is a join that includes all rows from both tables, even if there is no matching row in the other table. It is used to retrieve all data from both tables.
Example:
Consider the following two tables:
employees:
departments:
To retrieve the names of employees and their department names, including those employees and departments that do not have a match, we can use a full outer join:
SELECT employee_name, department_name
FROM employees
FULL OUTER JOIN departments ON employees.department_id = departments.department_id;
This query retrieves the employee_name and department_name columns and includes all rows from both the employees and departments table, even if there is no matching row in the other table.
45.What is a UNION in SQL?
A UNION in SQL is used to combine the result sets of two or more SELECT statements into a single result set. The SELECT statements must have the same number of columns, and the data types of the columns must be compatible.
Example:
Consider the following two tables:
employees1:
employees2:
To retrieve the names of all employees, we can use a UNION:
SELECT employee_name
FROM employees1
UNION
SELECT employee_name
FROM employees2;
This query retrieves the employee_name column from both employees1 and employees2 tables and combines them into a single result set, removing duplicates.
46.What is a UNION ALL in SQL?
A UNION ALL in SQL is used to combine the result sets of two or more SELECT statements into a single result set, including duplicates. Unlike a UNION, a UNION ALL does not remove duplicates.
Example:
Consider the following two tables:
employees1:
employees2:
To retrieve the names of all employees, including duplicates, we can use a UNION ALL:
SELECT employee_name
FROM employees1
UNION ALL
SELECT employee_name
FROM employees2;
This query retrieves the employee_name column from both employees1 and employees2 tables and combines them into a single result set, including duplicates.
47.What is a subquery in SQL?
A subquery in SQL is a query that is nested inside another query. The result of the subquery is used by the outer query as a condition to filter or join data.
Example:
Consider the following two tables:
employees:
departments:
To retrieve the names of all employees in the Sales department, we can use a subquery:
SELECT employee_name
FROM employees
WHERE department_id = (
SELECT department_id
FROM departments
WHERE department_name = 'Sales'
);
This query retrieves the department_id for the Sales department using a subquery and uses it as a condition in the WHERE clause of the outer query to filter the employee_name column.
48.What is a correlated subquery in SQL?
A correlated subquery in SQL is a subquery that depends on the outer query for its values. The subquery is executed for each row returned by the outer query.
Example:
Consider the following two tables:
employees:
salaries:
To retrieve the names of all employees who earn a salary greater than the average salary for their department, we can use a correlated subquery:
SELECT employee_name
FROM employees e
WHERE salary > (
SELECT AVG(salary)
FROM salaries s
WHERE s.employee_id = e.employee_id
);
This query retrieves the average salary for each employee's department using a correlated subquery and uses it as a condition in the WHERE clause of the outer query to filter the employee_name column.
50.What is a self-join in SQL?
A self-join in SQL is a join in which a table is joined to itself using one or more of its columns.
Example:
Consider the following table:
employees:
To retrieve the names of all employees and their managers, we can use a self-join:
SELECT e1.employee_name, e2.employee_name AS manager_name
FROM employees e1
LEFT JOIN employees e2 ON e1.manager_id = e2.employee_id;
This query joins the employees table to itself using the manager_id and employee_id columns and retrieves the employee_name and manager_name columns.
0 Comments
Please don't send any spam Link.