Top 30 SQL Query Interview Questions and Answers

Most Frequently Asked SQL Query Interview Questions

To understand SQL queries better, let’s make a table and see the practical implementation of the queries.

Create a table and named it ‘employee. Remember, SQL is not case sensitive so you can write queries and data in uppercase and lowercase both. It will not create any complication for executing queries.

The table looks like as follows-

Emp_id First_name Last_name Salary Department
1 JON SNOW 20000.00 DEVELOPMENT
2 ARYA STARK 19898.00 DEVELOPMENT
3 TYRION LANNISTER 21000.00 DEVELOPMENT
4 NED STARK 18000.00 DESIGNER
5 DANERIS TARGERION 17000.00 DESIGNER

Queries for creating employee table

To create the employee table performs the following queries:

 

Table is created now.

To insert values in the employee table, perform the following queries:

 

One row inserted.

Similarly, you can add other rows like-

 

Now, I hope you have created your desired table. You can check it like-

Let’s make another table named it as ‘TITLE’, for further understanding of SQL queries.

emp_id emp_title location
1 MANAGER NOIDA
2 ASSISTANT MANAGER NOIDA
3 OPERATIONS NOIDA
4 MANAGER GHAZIABAAD
5 ASSISTANT MANAGER GHAZIABAAD

Let’s have a look at some important question and answer for interview purpose as well as competitive exams.

QUESTION1:  Write an SQL query to find the highest salary of the employee from ‘development’ department.

Answer-

This query will show the highest salary for the development department.

Output-

Question2: Write an SQL query to fetch unique values of salary from table employee.

Answer-

SELECT distinct salary FROM employee;

This query will show the only salary column.

Output-

Question3:  Write an SQL query to fetch “last_name” from employee table using the alias name as <EMPLOYEE_NAME>.

 Answer-

This query will show the last name as employee_name  in the result.

Output-

Question4:  Write an SQL query to edit the record of the employee table as put the department ‘testing’ in place of ‘designer.’

Answer

This query will change the department. It will replace the designer department with ‘testing.’

Output-

Question5: Write an SQL query to count the no of employees in the employee table.

Answer-

This query will select the entire row of emp_id and count every emp_id as one then shows the total count.

Output-

Question6: Write an SQL query to count the no of the employees in the HR department.

Answer-

Here, result will be zero because there is no ‘HR’ department in the table employee.

Output

Question7: Write an SQL query to select first_name whose salary is equal to or greater than 19000 from the table employee.

Answer

This query gives you the first_name of the people whose salary is 19000 or higher. As in my database, three names have such salary.

Output-  

Question8: Write an SQL query to find the position of the alphabet (‘N’) in the first_name column ‘JON’ from table employee.  

Answer-

By INSTR method, we can evaluate the position of any word or letter. This query shows the position of a particular letter from the left. The most important thing to remember for the INSTR method, that it is case sensitive by default.

Output:

Question9: Write an SQL query to print the first_name and last_name together from table employee.  

Ans- Required query is:

This query will join two columns respectively.

OUTPUT-

Question10: Write an SQL query to add a column as joining_date in the table employee.

Answer- Required query is-

This query will add a column to an existing table in the database. We can add one or more column in a table.

The output of this query is as follows-

Output-

Question11: Write an SQL query to rename a table.

Answer- Required query is-

This query gives a new name to the existing table.

Output-

Question12: write an SQL Query to select the no of employees for every department in the ascending order.

Answer- Required query is-

This query will count the number of employees in every department and print them in ascending order.

Output-

Question13: Write an SQL query to print details of the employees who are also the manager.

Answer- Required query is-  

We have another table ‘title’ having emp_id and emp_title and location details of the employee. So, we have to join both tables for selecting the employees whose title is the manager. This query prints the list of the employees who are also the manager.

Output-

Question14: Write an SQL query to fetch the list of employees with the same salary.

Answer- Required query is-

Output-

This query gives the result as no rows selected because, in my table no two employees have same salary.

Question15: Write an SQL query to print the top n records of a table.

Answer- Required query is-

Suppose, we need top 3 rows of the table, then we will perform the following query:

Output-

Question16: Write an SQL query to find the nth highest salary without using TOP or Limit method.

Answer-

Suppose, we want the 4th highest salary then we perform the following query

Output-

Question17: Write an SQL query to fetch duplicate records having duplicity in some fields of a table.

Answer-Required query is-

As in our employee table, in last_name column two starks are taken place. So this query will print those records.

Output-

Question18:  Write an SQL query to fetch only even rows from the table.

Answer-

This query will give all even records.

Output-

Question19: Write an SQL query to make a duplicate table of an existing table.

Answer- Required query is-

As I want to create a clone of an employee table so, I will perform the following query.

Output-

Question20: Write an SQL query to fetch the common records of two tables.

Answer-

This query will print the common record of both table, and as I have selected cloned table of the employee, so it will print the same table as-

Output-                                         

Emp_id first_name last_name salary department
1 JON SNOW 20000.00 DEVELOPMENT
2 ARYA STARK 19898.00 DEVELOPMENT
3 TYRION LANNISTER 21000.00 DEVELOPMENT
4 NED STARK 18000.00 DESIGNER
5 DANERIS TARGERION 17000.00 DESIGNER

Quistion21: Write an SQL query to fetch the records from two tables that are not common in both.

Answer-

This query results in the record that are not common in two tables. As I select the cloned table in the query so it will give the output as no rows selected because all records are the same in both tables.

Output-    

Question22: Tell the output of statement SELECT COUNT(7).

Answer-

Question23: Write an SQL query to print the current date and time.

Answer- To see the current system date and time perform query as

This query will give the output as-

Output-

The output is in the form of fractional second and time zone with date and time.

Question24: write an SQL query to find the sum of employee that has less than 3 emp_id.

Answer-

This query will count the number of employees as they have the emp_id less than 3. So in my table, two employees have emp_id less than 3. The output will be as-

Output-

Question25: write an SQL query to fetch the last record of the table.

Answer-

This query will show the last record of the table. This query selects the employee table with condition maximum employee id so that it will select the last row of the table.

Output-                                                                                        

Emp_id first_name last_name salary department
5 DANERIS TARGERION 17000.00 DESIGNER

 Question26: write an SQL query to fetch the first record of a table.

Answer-

This query will show the first record of the table. This query selects the employee table with condition minimum employee id so that it will select the first row of the table.

Output-                                                       

Emp_id first_name last_name salary department
1 JON SNOW 20000.00 DEVELOPMENT

 Question27: write an SQL query to show a single row for two times.

Answer-required query is-

This query will show if a single row available for two times in the table.  In the above table, no such rows are available so it will print result as no rows selected.

Output-

Question28: write an SQL query to show all departments along with the total number of employee.

 Answer-required query is

This query will count no of the employee in a department.

Output-

Question29: write an SQL query to show department along with the total salary paid for every department.

Answer-

This query will show the sum of the salary with the department.

Output-

Question30: write an SQL query to select all EMPLOYEE record from an employee table that have a title record in Title table.

Answer- required query is

This query only prints the records that are available in the title table. Since emp_id is a common attribute, so it compares both tables by this attribute. In our title table, all emp_id is available so it will print the entire table.

Output-

Emp_id first_name last_name salary department
1 JON SNOW 20000.00 DEVELOPMENT
2 ARYA STARK 19898.00 DEVELOPMENT
3 TYRION LANNISTER 21000.00 DEVELOPMENT
4 NED STARK 18000.00 DESIGNER
5 DANERIS TARGERION 17000.00 DESIGNER