The Best 40+ SQL Query Interview Questions And Answers 2024

SQL Query Interview Questions And Answers for experienced professionals from Codingcompiler. These SQL Query interview questions were asked in various interviews conducted by top multinational companies across the globe. We hope that these interview questions on SQL Query will help you in cracking your job interview. All the best and happy learning.

In this SQL Query Interview Questions Blog, You Will Learn

SQL Query Interview Questions
SQL Query Interview Questions and Answers
Frequently asked SQL Query Interview Questions
Advanced SQL Query Interview Questions and Answers

Table of Contents

SQL Query Interview Questions

1. Write a SQL query to fetch the count of employees working in project ‘P1’.?

Ans. Here, we would be using aggregate function count() with the SQL where clause-

SELECT COUNT(*) 
FROM EmployeeSalary 
WHERE Project = 'P1';

2. Write a query to fetch employee names and salary records. Return employee details even if the salary record is not present for the employee.

Ans. Here, we can use left join with EmployeeDetail table on the left side.

SELECT E.FullName, S.Salary  
FROM EmployeeDetails E LEFT JOIN EmployeeSalary S
ON E.EmpId = S.EmpId;

3. Write a SQL query to create an empty table with the same structure as some other table.

Ans. Using SELECT INTO command with False ‘WHERE’ condition-

SELECT * INTO newTable 
FROM EmployeeDetails 
WHERE 1 = 0;

This can also be done using MySQL ‘Like’ command with CREATE statement-

CREATE TABLE newTable 
LIKE EmployeeDetails;

4. Write a SQL query to fetch common records between two tables?

Ans. Using INTERSECT-

SELECT * FROM EmployeeSalary
INTERSECT
SELECT * FROM ManagerSalary

5. Write a query to fetch the EmpFname from the EmployeeInfo table in upper case and use the ALIAS name as EmpName.

Ans:

SELECT EmpFname AS UPPER(EmpName) FROM EmployeeInfo;

6. Write a query to get the current date.

You can write a query as follows in SQL Server:

Answer:

SELECT GETDATE();
You can write a query as follows in MySQL:
SELECT SYSTDATE();

7. Write q query to find all the employees whose salary is between 50000 to 100000.

And:

SELECT * FROM EmployeePosition WHERE Salary BETWEEN '50000' AND '100000';

8. Write a query to retrieve the EmpFname and EmpLname in a single column as “FullName”. The first name and the last name must be separated with space.

Ans:

SELECT CONCAT(EmpFname, ' ', EmpLname) AS 'FullName' FROM EmployeeInfo;

9. Write a SQL query to retrieve employee details from EmployeeInfo table who have a date of joining in the EmployeePosition table.

Ans:

SELECT * FROM EmployeeInfo E 
WHERE EXISTS 
(SELECT * FROM EmployeePosition P WHERE E.EmpId = P.EmpId);

10. Write An SQL Query To Fetch “FIRST_NAME” From Worker Table Using The Alias Name As <WORKER_NAME>?

Ans.

The required query is:

Select FIRST_NAME AS WORKER_NAME from Worker;

SQL Query Interview Questions and Answers

11. Write An SQL Query To Fetch Unique Values Of DEPARTMENT From Worker Table.

Ans.

The required query is:

Select distinct DEPARTMENT from Worker;

12. rite An SQL Query To Find The Position Of The Alphabet (‘A’) In The First Name Column ‘Amitabh’ From Worker Table.

Ans.

The required query is:

Select INSTR(FIRST_NAME, BINARY'a') from Worker where FIRST_NAME = 'Amitabh';

13. Write An SQL Query To Print The DEPARTMENT From Worker Table After Removing White Spaces From The Left Side.

Ans.

The required query is:

Select LTRIM(DEPARTMENT) from Worker;

14. Write An SQL Query To Print All Worker Details From The Worker Table Order By FIRST_NAME Ascending And DEPARTMENT Descending.

Ans.

The required query is:

Select * from Worker order by FIRST_NAME asc,DEPARTMENT desc;

15. Write An SQL Query To Print Details Of The Workers Whose FIRST_NAME Contains ‘A’?

Ans.

The required query is:

Select * from Worker where FIRST_NAME like '%a%';

16. Write An SQL Query To Print Details Of The Workers Whose FIRST_NAME Ends With ‘H’ And Contains Six Alphabets.

Ans.

The required query is:

Select * from Worker where FIRST_NAME like '_____h';

17. Write An SQL Query To Fetch Worker Names With Salaries >= 50000 And <= 100000.

Ans.

The required query is:

SELECT CONCAT(FIRST_NAME, ' ', LAST_NAME) As Worker_Name, Salary FROM worker 
WHERE WORKER_ID IN 
(SELECT WORKER_ID FROM worker 
WHERE Salary BETWEEN 50000 AND 100000);

18. Write An SQL Query To Print Details Of The Workers Who Are Also Managers.

Ans.

The required query is:

SELECT DISTINCT W.FIRST_NAME, T.WORKER_TITLE
FROM Worker W
INNER JOIN Title T
ON W.WORKER_ID = T.WORKER_REF_ID
AND T.WORKER_TITLE in ('Manager');

19. Write An SQL Query To Clone A New Table From Another Table.

Ans.

The general query to clone a table with data is:

SELECT * INTO WorkerClone FROM Worker;

20. Write An SQL Query To Fetch The List Of Employees With The Same Salary.

Ans.

The required query is:

Select distinct W.WORKER_ID, W.FIRST_NAME, W.Salary 
from Worker W, Worker W1 
where W.Salary = W1.Salary 
and W.WORKER_ID != W1.WORKER_ID;

Frequently asked SQL Query Interview Questions

21. Write An SQL Query To Show The Second Highest Salary From A Table.

Ans.

The required query is:

Select max(Salary) from Worker 
where Salary not in (Select max(Salary) from Worker);

22. What is a Self-Join?

Answer: A self JOIN is a case of regular join where a table is joined to itself based on some relation between its own column(s). Self-join uses the INNER JOIN or LEFT JOIN clause and a table alias is used to assign different names to the table within the query.

SELECT A.emp_id AS "Emp_ID",A.emp_name AS "Employee",
B.emp_id AS "Sup_ID",B.emp_name AS "Supervisor"
FROM employee A, employee B
WHERE A.emp_sup = B.emp_id;

23. What is a view in SQL?

Answer: Views in SQL are kind of virtual tables. A view also has rows and columns as they are in a real table in the database. We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain conditions.

The CREATE VIEW statement of SQL is used for creating Views.

Basic Syntax:

CREATE VIEW view_name AS
SELECT column1, column2.....
FROM table_name
WHERE condition;
view_name: Name for the View
table_name: Name of the table
condition: Condition to select rows

24. How we can update the view?

Answer:

SQL CREATE and REPLACE can be used for updating the view.

Following query syntax is to be executed to update the created view

Syntax:

CREATE OR REPLACE VIEW view_name AS
 SELECT column_name(s)
 FROM table_name
 WHERE condition

25. Explain the working of SQL Privileges?

Answer:

SQL GRANT and REVOKE commands are used to implement privileges in SQL multiple user environments. The administrator of the database can grant or revoke privileges to or from users of database object like SELECT, INSERT, UPDATE, DELETE, ALL etc.

GRANT Command: This command is used provide database access to user apart from an administrator.

Syntax:

GRANT privilege_name
 ON object_name
 TO {user_name|PUBLIC|role_name}
 [WITH GRANT OPTION];

In above syntax WITH GRANT OPTIONS indicates that the user can grant access to another user too.

REVOKE command: This command is used provide database deny or remove access to database objects.

Syntax:

REVOKE privilege_name
 ON object_name
 FROM {user_name|PUBLIC|role_name};

Advanced SQL Query Interview Questions and Answers

26. Get First_Name,Last_Name from employee table

Answer:

Select first_name, Last_Name from employee

27. Get First_Name from employee table in upper case

Answer:

 Select upper(FIRST_NAME) from EMPLOYEE

28. Get FIRST_NAME from employee table after removing white spaces from right side

Answer:

select RTRIM(FIRST_NAME) from employee

29. Get all employee details from the employee table order by First_Name Ascending and Salary descending

Answer:

Select * from employee order by FIRST_NAME asc,SALARY desc

30. Get employee details from employee table whose joining year is “2013”

Ans:

SQL Queries in Oracle, Select * from EMPLOYEE where to_char(joining_date,'YYYY')='2013'

SQL Queries in SQL Server, Select * from EMPLOYEE where SUBSTRING(convert(varchar,joining_date,103),7,4)='2013'

SQL Queries in MySQL, Select * from EMPLOYEE 
where year(joining_date)='2013'

31. How to remove duplicate rows from table?

Answer:

User needs to select the duplicate rows from the table without using distinct keyword.Following query will give you the duplicate rows from the table.

Select rollno FROM Student WHERE ROWID <>
(Select max (rowid) from Student b where rollno=b.rollno);

32. How to calculate number of rows in table without using count function?

Answer:

There are so many system tables which are very important .Using the system table user can count the number of rows in the table.following query is helpful in that case,

Select table_name, num_rows from user_tables 
where table_name=’Employee’;

33. How to add Email validation using single SQL Statement

This is one of the most important SQL Query Interview Questions .User can see the Email validation in SQL using multiple ways.

Answer :

Query :

SELECT
Email
FROM
Employee
where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

34. How to check the procedure code using the system tables?

Answer :

SELECT * FROM User_Source
WHERE Type=’PROCEDURE’ 
AND NAME IN (‘SP_CONNECTED_AGG’,’SP_UNCONNECTED_AGG’);

35. Will following statement give Divide by Zero error?

SELECT NULL/0

Answer- No. It will execute and result will be NULL.

SQL Query Interview Questions And Answers

SQL Query Interview Questions

SQL Query Interview Questions and Answers

Frequently asked SQL Query Interview Questions

Advanced SQL Query Interview Questions and Answers

OTHER INTERVIEW QUESTIONS

Leave a Comment Cancel reply