Top 40+ SQL Query Interview Questions for Freshers with Answers

Are you preparing for an SQL interview? To help you out, we’ve compiled a list of the top 40+ SQL query interview questions and answers for freshers. This guide covers all of SQL’s core concepts, definitions, and types of statements, as well as more advanced concepts like joins, subqueries, and data normalization.

Top 40+ SQL Query Interview Questions for Freshers with Answers

1. What is SQL?
2. What are the different types of SQL statements?
3. What is a primary key in SQL?
4. What is a foreign key in SQL?
5. What are joins in SQL?
6. What is normalization? Explain its types.
7. What is denormalization?
8. What is a view in SQL?
9. What is an index in SQL?
10. What is a subquery in SQL?
11. What is the difference between DELETE and TRUNCATE commands?
12. What is the difference between WHERE and HAVING clauses?
13. What is the difference between UNION and UNION ALL?
14. How can you retrieve unique records from a table?
15. What is a self-join?
16. What is a correlated subquery?
17. How do you find the second highest salary from a table?
18. What is the difference between CHAR and VARCHAR data types?
19. How can you update data in SQL?
20. How do you delete duplicate rows in SQL?
21. What is a composite key in SQL?
22. What is a unique constraint in SQL?
23. What is a check constraint?
24. How can you retrieve the current date in SQL?
25. What is the difference between the RANK() and DENSE_RANK() functions?
26. How do you add a new column to an existing table?
27. How can you remove a column from an existing table?
28. What is a stored procedure?
29. What is a trigger in SQL?
30. How can you fetch common records from two tables?
31. How do you fetch alternate records from a table?
32. What is the difference between CROSS JOIN and INNER JOIN?
33. How can you find the number of rows in a table?
34. What is the purpose of the GROUP BY clause?
35. How do you retrieve the first N records from a table?
36. What is a window function in SQL?
37. How can you remove duplicate records from a table?
38. What is the difference between a clustered and a non-clustered index?
39. How do you perform pattern matching in SQL?
40. What is the difference between the COALESCE() and ISNULL() functions?
41. What is the purpose of the CASE statement in SQL?
42. How can you concatenate strings in SQL?
43. What is the difference between DELETE and DROP commands?
44. How can you change the data type of a column in SQL?

1. What is SQL?

Answer: SQL, or Structured Query Language, is a standard programming language specifically designed for managing and manipulating relational databases. It allows users to perform various operations such as querying data, updating records, creating and modifying database structures, and controlling access to the data. SQL is fundamental for interacting with relational database management systems (RDBMS) like MySQL, PostgreSQL, and SQL Server.

2. What are the different types of SQL statements?

Answer: SQL statements are categorized into several types based on their functionality:

• Data Definition Language (DDL): Includes commands like CREATE, ALTER, and DROP used to define and modify database structures.
• Data Manipulation Language (DML): Consists of commands such as SELECT, INSERT, UPDATE, and DELETE for managing data within tables.
• Data Control Language (DCL): Comprises commands like GRANT and REVOKE to control access permissions.
• Transaction Control Language (TCL): Includes commands such as COMMIT, ROLLBACK, and SAVEPOINT to manage transactions within the database.

3. What is a primary key in SQL?

Answer: A primary key is a column or a set of columns in a table that uniquely identifies each row in that table. Primary keys enforce entity integrity by ensuring that no two rows have the same primary key value and that the primary key columns do not contain NULL values. For example, in an Employees table, an EmployeeID column can serve as the primary key.

4. What is a foreign key in SQL?

Answer: A foreign key is a column or a set of columns in one table that establishes a link between data in two tables. It refers to the primary key in another table, thereby enforcing referential integrity. For instance, if you have an Orders table with a CustomerID column that references the CustomerID in a Customers table, CustomerID in the Orders table is a foreign key.

5. What are joins in SQL?

Answer: Joins are SQL operations that allow you to combine rows from two or more tables based on a related column between them. The main types of joins are:

• INNER JOIN: Returns records that have matching values in both tables.
• LEFT (OUTER) JOIN: Returns all records from the left table, and the matched records from the right table.
• RIGHT (OUTER) JOIN: Returns all records from the right table, and the matched records from the left table.
• FULL (OUTER) JOIN: Returns all records when there is a match in either left or right table.

6. What is normalization? Explain its types.

Answer: Normalization is the process of organizing data in a database to reduce redundancy and improve data integrity. The main normal forms are:

• First Normal Form (1NF): Ensures that the table has a primary key and that all columns contain atomic (indivisible) values.
• Second Normal Form (2NF): Meets all requirements of 1NF and ensures that all non-key columns are fully dependent on the primary key.
• Third Normal Form (3NF): Meets all requirements of 2NF and ensures that all columns are not transitively dependent on the primary key.

7. What is denormalization?

Answer: Denormalization is the process of combining normalized tables into larger tables to improve database read performance. It involves adding redundant data to one or more tables, which can reduce the complexity of queries and the need for joins, at the expense of potential data anomalies and increased storage requirements.

8. What is a view in SQL?

Answer: A view is a virtual table in SQL that is based on the result set of a SELECT query. It contains rows and columns, just like a real table, and can be used to simplify complex queries, enhance security by restricting access to specific data, and present data in a particular format without altering the underlying tables.

9. What is an index in SQL?

Answer: An index is a database object that improves the speed of data retrieval operations on a table at the cost of additional storage space and maintenance overhead. Indexes are created on columns that are frequently searched or used in join conditions. They function similarly to an index in a book, allowing the database engine to locate data without scanning the entire table.

10. What is a subquery in SQL?

Answer: A subquery, or inner query, is a query nested within another SQL query. The result of the subquery is used by the outer query to further refine the data retrieval process. Subqueries can be used in various clauses like SELECT, FROM, WHERE, and HAVING to perform operations such as filtering, aggregation, and comparison.

11. What is the difference between DELETE and TRUNCATE commands?

Answer: Both DELETE and TRUNCATE are used to remove data from tables, but they differ in functionality:

• DELETE: Removes specified rows from a table based on a WHERE condition. It is a DML (Data Manipulation Language) operation and can be rolled back if wrapped in a transaction.
• TRUNCATE: Removes all rows from a table, resetting any auto-increment counters. It is a DDL (Data Definition Language) operation and cannot be rolled back in most database systems.

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

Answer: Both WHERE and HAVING clauses are used to filter records, but they operate differently:

• WHERE Clause: Filters rows before any grouping operations are applied. It cannot contain aggregate functions like SUM, COUNT, etc.
• HAVING Clause: Filters groups after the GROUP BY operation has been performed. It can contain aggregate functions to filter based on group-level data.

Example:

Suppose we have a table Sales with columns ProductID, Quantity, and Price. To find products with total sales exceeding $1,000, we can use:

SELECT ProductID, SUM(Quantity * Price) AS TotalSales
FROM Sales
GROUP BY ProductID
HAVING SUM(Quantity * Price) > 1000;

In this query, HAVING is used to filter products based on the aggregated TotalSales value.

13. What is the difference between UNION and UNION ALL?

Answer:

• UNION: Combines the result sets of two or more SELECT queries into a single result set and removes duplicate rows.
• UNION ALL: Combines the result sets of two or more SELECT queries into a single result set without removing duplicates, thus including all records.

Example:

SELECT column1 FROM table1
UNION
SELECT column1 FROM table2;

This query returns distinct values from both table1 and table2.

SELECT column1 FROM table1
UNION ALL
SELECT column1 FROM table2;

This query returns all values from both tables, including duplicates.

14. How can you retrieve unique records from a table?

Answer:

To retrieve unique records from a table, you can use the DISTINCT keyword in the SELECT statement.

Example:

SELECT DISTINCT column1, column2
FROM table_name;

This query returns unique combinations of column1 and column2 from table_name.

15. What is a self-join?

Answer:

A self-join is a join in which a table is joined with itself. It is useful for comparing rows within the same table.

Example:

SELECT A.employee_id, A.name, B.manager_id, B.name AS manager_name
FROM employees A
JOIN employees B ON A.manager_id = B.employee_id;

In this query, the employees table is joined with itself to retrieve employees along with their managers’ names.

16. What is a correlated subquery?

Answer:

A correlated subquery is a subquery that references columns from the outer query. It is evaluated once for each row processed by the outer query.

Example:

SELECT employee_id, name, salary
FROM employees E1
WHERE salary > (
    SELECT AVG(salary)
    FROM employees E2
    WHERE E2.department_id = E1.department_id
);

This query retrieves employees whose salary is above the average salary of their respective departments.

17. How do you find the second highest salary from a table?

Answer:

To find the second highest salary, you can use the LIMIT clause with an offset or a subquery.

Using LIMIT with offset (in MySQL):

SELECT DISTINCT salary
FROM employees
ORDER BY salary DESC
LIMIT 1 OFFSET 1;

Using a subquery:

SELECT MAX(salary) AS SecondHighestSalary
FROM employees
WHERE salary < (
    SELECT MAX(salary)
    FROM employees
);

Both queries return the second highest salary from the employees table.

18. What is the difference between CHAR and VARCHAR data types?

Answer:

• CHAR: A fixed-length character data type. It reserves a fixed amount of space regardless of the actual data length. Suitable for storing data that has a consistent length.
• VARCHAR: A variable-length character data type. It uses only as much space as needed for the data, plus a small amount of overhead. Suitable for storing data with varying lengths.

Example:

CREATE TABLE example_table (
    fixed_column CHAR(10),
    variable_column VARCHAR(10)
);

In this table, fixed_column will always use 10 bytes of storage, while variable_column will use storage based on the actual data length.

19. How can you update data in SQL?

Answer:

To update data in SQL, use the UPDATE statement along with the SET clause to specify the new values, and the WHERE clause to specify which rows should be updated.

Example:

UPDATE employees
SET salary = salary * 1.1
WHERE department_id = 5;

This query increases the salary of all employees in department 5 by 10%.

20. How do you delete duplicate rows in SQL?

Answer:

To delete duplicate rows, you can use a common table expression (CTE) with the ROW_NUMBER() window function to identify duplicates and then delete them.

Example:

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY (SELECT 0)) AS rn
    FROM table_name
)
DELETE FROM CTE WHERE rn > 1;

This query deletes duplicate rows based on column1 and column2, keeping only the first occurrence.

21. What is a composite key in SQL?

Answer: A composite key is a combination of two or more columns in a table that uniquely identifies each row in that table. This is used when a single column is not sufficient to uniquely identify records.

Example: In a table OrderDetails with columns OrderID and ProductID, neither can uniquely identify a row. However, the combination of both OrderID and ProductID can serve as a composite key to uniquely identify each record.

22. What is a unique constraint in SQL?

Answer: A unique constraint ensures that all values in a column or a set of columns are distinct across the database. Unlike the primary key, a table can have multiple unique constraints, and columns with unique constraints can accept a single NULL value.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Email VARCHAR(255) UNIQUE
);

In this example, the Email column has a unique constraint, ensuring no two employees can have the same email address.

23. What is a check constraint?

Answer: A check constraint limits the values that can be placed in a column by enforcing a specific condition for each row.

Example:

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    Age INT,
    CHECK (Age >= 18)
);

This ensures that the Age of an employee must be 18 or older.

24. How can you retrieve the current date in SQL?

Answer: To retrieve the current date, you can use built-in functions provided by the SQL dialect:

• MySQL: SELECT CURDATE();
• SQL Server: SELECT GETDATE();
• Oracle: SELECT SYSDATE FROM DUAL;

These functions return the current date and time based on the server’s system clock.

25. What is the difference between the RANK() and DENSE_RANK() functions?

Answer: Both RANK() and DENSE_RANK() are window functions that assign ranks to rows within a partition:

• RANK(): Assigns ranks with gaps in the ranking sequence when there are ties.
• DENSE_RANK(): Assigns ranks without gaps, even if there are ties.

Example:

SELECT
    EmployeeID,
    Salary,
    RANK() OVER (ORDER BY Salary DESC) AS Rank,
    DENSE_RANK() OVER (ORDER BY Salary DESC) AS DenseRank
FROM Employees;

If two employees have the same salary and are ranked 1st, RANK() will assign the next employee a rank of 3, while DENSE_RANK() will assign a rank of 2.

26. How do you add a new column to an existing table?

Answer: To add a new column to an existing table, use the ALTER TABLE statement with the ADD clause:

ALTER TABLE table_name
ADD column_name datatype;

Example:

ALTER TABLE Employees
ADD DateOfBirth DATE;

This adds a new column DateOfBirth of type DATE to the Employees table.

27. How can you remove a column from an existing table?

Answer: To remove a column from an existing table, use the ALTER TABLE statement with the DROP COLUMN clause:

ALTER TABLE table_name
DROP COLUMN column_name;

Example:

ALTER TABLE Employees
DROP COLUMN DateOfBirth;

This removes the DateOfBirth column from the Employees table.

28. What is a stored procedure?

Answer: A stored procedure is a precompiled collection of one or more SQL statements stored under a name and processed as a unit. Stored procedures can accept parameters, perform operations, and return results, allowing for modular programming and improved performance.

Example:

CREATE PROCEDURE GetEmployeeByID (
    IN emp_id INT
)
BEGIN
    SELECT * FROM Employees WHERE EmployeeID = emp_id;
END;

This procedure retrieves the details of an employee based on the provided emp_id.

29. What is a trigger in SQL?

Answer: A trigger is a set of SQL statements that automatically execute in response to certain events on a particular table or view, such as INSERT, UPDATE, or DELETE operations. Triggers are used to enforce business rules, validate data, and maintain audit trails.

Example:

CREATE TRIGGER UpdateTimestamp
BEFORE UPDATE ON Employees
FOR EACH ROW
BEGIN
    SET NEW.LastModified = NOW();
END;

This trigger updates the LastModified column to the current timestamp whenever a row in the Employees table is updated.

30. How can you fetch common records from two tables?

Answer: To fetch common records from two tables, use the INTERSECT operator (if supported) or an INNER JOIN:

Using INTERSECT:

SELECT column1, column2 FROM table1
INTERSECT
SELECT column1, column2 FROM table2;

Using INNER JOIN:

SELECT a.column1, a.column2
FROM table1 a
INNER JOIN table2 b ON a.common_column = b.common_column;

Both queries return records that are present in both table1 and table2.

31. How do you fetch alternate records from a table?

Answer: To fetch alternate records (e.g., odd or even rows) from a table, you can use the ROW_NUMBER() window function in conjunction with a Common Table Expression (CTE) or a subquery. Here’s how you can achieve this:

Example:

WITH NumberedRows AS (
    SELECT *,
           ROW_NUMBER() OVER (ORDER BY some_column) AS row_num
    FROM your_table
)
SELECT *
FROM NumberedRows
WHERE row_num % 2 = 1; -- For odd rows; use 0 for even rows

In this example:

• The CTE NumberedRows assigns a unique sequential integer (row_num) to each row based on the specified ORDER BY clause.
• The outer query selects rows where row_num is odd (row_num % 2 = 1). To select even rows, use row_num % 2 = 0.

This method allows you to retrieve alternate rows from the table.

32. What is the difference between CROSS JOIN and INNER JOIN?

Answer:

• CROSS JOIN: Produces the Cartesian product of two tables, combining each row of the first table with every row of the second table. If Table A has 3 rows and Table B has 4 rows, the result will have 12 rows.
• INNER JOIN: Returns only the rows that have matching values in both tables based on a specified condition.

Example:

-- CROSS JOIN example
SELECT *
FROM TableA
CROSS JOIN TableB;

-- INNER JOIN example
SELECT *
FROM TableA
INNER JOIN TableB ON TableA.common_column = TableB.common_column;

In the CROSS JOIN, every combination of rows from both tables is returned. In the INNER JOIN, only rows with matching values in the specified columns are returned.

33. How can you find the number of rows in a table?

Answer:

To find the number of rows in a table, use the COUNT() aggregate function:

SELECT COUNT(*) AS total_rows
FROM your_table; 

This query returns the total number of rows in your_table.

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

Answer:

The GROUP BY clause groups rows that have the same values in specified columns into aggregated data. It is often used with aggregate functions like COUNT(), SUM(), AVG(), MAX(), and MIN() to perform operations on each group of data.

Example:

SELECT department_id, COUNT(*) AS employee_count
FROM employees
GROUP BY department_id;

This query counts the number of employees in each department.

35. How do you retrieve the first N records from a table?

Answer:

To retrieve the first N records from a table, use the LIMIT clause (in MySQL) or the FETCH FIRST clause (in SQL Server and Oracle):

MySQL:

SELECT *
FROM your_table
ORDER BY some_column
LIMIT N;

SQL Server:

SELECT *
FROM your_table
ORDER BY some_column
OFFSET 0 ROWS FETCH NEXT N ROWS ONLY;

Oracle:

SELECT *
FROM your_table
ORDER BY some_column
FETCH FIRST N ROWS ONLY;

Replace N with the number of records you want to retrieve and some_column with the column you want to sort by.

36. What is a window function in SQL?

Answer:

A window function performs a calculation across a set of table rows that are related to the current row. Unlike aggregate functions, window functions do not cause rows to become grouped into a single output row; instead, they return a value for each row. Examples include ROW_NUMBER(), RANK(), and DENSE_RANK().

Example:

SELECT employee_id,
       salary,
       RANK() OVER (ORDER BY salary DESC) AS salary_rank
FROM employees;

This query assigns a rank to each employee based on their salary, with the highest salary receiving the highest rank.

37. How can you remove duplicate records from a table?

Answer:

To remove duplicate records, you can use a CTE with the ROW_NUMBER() function to identify duplicates and then delete them:

WITH CTE AS (
    SELECT *,
           ROW_NUMBER() OVER (PARTITION BY column1, column2 ORDER BY (SELECT 0)) AS rn
    FROM your_table
)
DELETE FROM CTE WHERE rn > 1; 

This query keeps the first occurrence of each duplicate and removes the rest. Replace column1 and column2 with the columns that define duplication.

38. What is the difference between a clustered and a non-clustered index?

Answer:

• Clustered Index: Determines the physical order of data in a table. A table can have only one clustered index.
• Non-Clustered Index: Does not alter the physical order of data. It creates a separate object within the table that points back to the original table rows. A table can have multiple non-clustered indexes.

Clustered indexes are generally faster for retrieving data because they store rows in the sorted order of the index key.

39. How do you perform pattern matching in SQL?

Answer:

Pattern matching in SQL is performed using the LIKE operator along with wildcards:

• %: Represents zero or more characters.
• _: Represents a single character.

Example:

SELECT *
FROM employees
WHERE name LIKE 'J%'; -- Names starting with 'J'

This query retrieves all employees whose names start with the letter ‘J’.

40. What is the difference between the COALESCE() and ISNULL() functions?

Both COALESCE() and ISNULL() are used to handle NULL values in SQL, but they have distinct differences:

• Number of Arguments:
  • ISNULL() accepts exactly two arguments: the expression to evaluate and the replacement value if the expression is NULL.
  • COALESCE() can accept multiple arguments and returns the first non-NULL value among them.
• Data Type Precedence:
  • ISNULL() returns the data type of the first argument.
  • COALESCE() returns the data type of the first non-NULL argument, following standard data type precedence rules.
• Nullability of Result:
  • Expressions involving ISNULL() with non-NULL parameters are considered to be NOT NULL.
  • Expressions involving COALESCE() with non-NULL parameters are considered to be NULL.
• Standard Compliance:
  • ISNULL() is specific to SQL Server.
  • COALESCE() is part of the ANSI SQL standard and is supported by multiple database systems.

Example:

-- Using ISNULL
SELECT ISNULL(NULL, 'Replacement Value'); -- Returns 'Replacement Value'

-- Using COALESCE
SELECT COALESCE(NULL, NULL, 'First Non-NULL Value'); -- Returns 'First Non-NULL Value'

In this example, both functions return the first non-NULL value from their arguments.

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

Answer:

The CASE statement in SQL provides conditional logic to perform different actions based on different conditions, similar to IF-THEN-ELSE statements in programming languages. It allows for conditional expressions within SQL queries.

Syntax:

CASE
    WHEN condition1 THEN result1
    WHEN condition2 THEN result2
    ...
    ELSE default_result
END

Example:

SELECT
    employee_id,
    salary,
    CASE
        WHEN salary > 50000 THEN 'High'
        WHEN salary BETWEEN 30000 AND 50000 THEN 'Medium'
        ELSE 'Low'
    END AS salary_level
FROM employees;

This query categorizes employees’ salaries into ‘High’, ‘Medium’, or ‘Low’ based on specified ranges.

42. How can you concatenate strings in SQL?

Answer:

String concatenation in SQL can be performed using different operators or functions, depending on the database system:

SQL Server: Use the + operator.

SELECT first_name + ' ' + last_name AS full_name FROM employees;

MySQL and PostgreSQL: Use the CONCAT() function.

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

Oracle: Use the || operator.

SELECT first_name || ' ' || last_name AS full_name FROM employees;

These methods combine first_name and last_name into a single full_name field.

43. What is the difference between DELETE and DROP commands?

Answer:

DELETE: Removes rows from a table based on a WHERE condition. It is a Data Manipulation Language (DML) operation and can be rolled back if within a transaction. The table structure remains intact.

DELETE FROM employees WHERE department_id = 10;

DROP: Removes an entire database object, such as a table or view. It is a Data Definition Language (DDL) operation and cannot be rolled back in most systems. This command deletes the table structure and all its data.

DROP TABLE employees;

Use DELETE to remove specific records and DROP to remove entire structures.

44. How can you change the data type of a column in SQL?

Answer:

To change the data type of a column, use the ALTER TABLE statement with the ALTER COLUMN clause. The exact syntax depends on the database system:

SQL Server:

ALTER TABLE table_name
ALTER COLUMN column_name new_data_type;

MySQL:

ALTER TABLE table_name
MODIFY COLUMN column_name new_data_type;

Oracle:

ALTER TABLE table_name
MODIFY column_name new_data_type;

Example:

ALTER TABLE employees
ALTER COLUMN birth_date DATE;

This changes the data type of the birth_date column to DATE.

Learn More: Carrer Guidance | Hiring Now!

Top 45+ GCP (Google Cloud Platform) Interview Questions and Answers

Selenium Coding Interview Questions and Answers

Power Automate Interview Questions for Freshers with Answers

Mobile Testing Interview Questions and Answers- Basic to Advanced

JMeter Interview Questions and Answers

UI UX Design Interview Questions and Answers

SAP FICO Interview Questions and Answers- Basic to Advanced