Top 10 Most Important SQL Queries for Interview preparation – part 1

SQL is a language which helps us to work with the databases as database do not understand English or any other language. So we use SQL for requesting data from database.

SQL is the standard language of Database and is also pronounced as Sequel by many people.

Below 10 queries are very common in interviews. You should have a look at these before going to a SQL technical interview.

Q.1) SQL query to find the Nth highest salary of the employee?

This can be achieved in multiple ways.

select * from (select ename, sal, dense_rank() over (order by sal desc) r from Employee) where r=&n;   

-- OR

select salary from employee order by salary desc limit n-1,1;

-- OR

SELECT name, salary FROM Employee e1 WHERE (N-1) = (SELECT COUNT(DISTINCT salary) FROM Employee e2 WHERE e2.salary > e1.salary);

Q.2) SQL query to find the 2nd highest salary of the employee?

SELECT MAX(salary) FROM Employee WHERE Salary NOT IN ( SELECT Max(Salary) FROM Employee) ;

-- OR

SELECT MAX(Salary) From Employee WHERE Salary < ( SELECT Max(Salary) FROM Employee);

-- OR

select salary from employee order by salary desc limit 1,1;

Q.3) How to show Max and Min marks together from student table?

SELECT MIN(marks) AS Minmarks, MAX(marks) AS Maxmarks FROM STUDENT;

Q.4) Write an SQL query for fetching the details of duplicate records?

To find the rows which are having duplicate name in Student table:

select NAME from STUDENT group by NAME having count(*) > 1

To print all the duplicate rows:

select * from STUDENT where (NAME) in (select NAME from STUDENT group by
 NAME having count(*) > 1)

Q.5) Write an SQL Query to find the year from date?

SELECT EXTRACT(YEAR FROM "2017-06-15");

-- OR

SELECT YEAR("2017-06-15");

Q.6) There is a table which contains two column Student and Marks, you need to find all
the students, whose marks are greater than average marks i.e. list of above average
students?

SELECT student, marks from STUDENT where marks > SELECT AVG(marks) from STUDENT);

Q.7) How do you find all employees which are also manager?

EMPNOENAMEJOBDEPTNOMNGR
1234JohnPRESIDENT10
1235ShekarMANAGER201234
1236WallyMANAGER201234
1237SamPROGRAMMER 201235
1238LeeSALESMAN301236

Query:

select e.EMPNO, e.ENAME, m.ENAME AS MANAGER, e.MGR from EMPLOYEE e,
 EMPLOYEE m where e.MGR = m.EMPNO;

Q.8) Find all Employee records containing the word “Joe”, regardless of whether it was
stored as JOE, Joe, or joe?

SELECT * from Employees WHERE UPPER(EmpName) like 'JOE';

Q.9) Write an SQL Query find number of employees according to gender whose DOB is
between 01/01/1960 to 31/12/1975?

SELECT COUNT(*), gender from Employees WHERE DOB BETWEEN '01/01/1960' AND
 '31/12/1975' GROUP BY gender;

Q.10) SQL Query to find Max Salary from each department?

SELECT DeptID, MAX(Salary) FROM Employee GROUP BY DeptID;

With Dept. name

SELECT d.DeptName, MAX(e.Salary) FROM Employee e RIGHT JOIN Department d ON
 e.DeptId = d.DeptID GROUP BY d.DeptID;

Q.11) How to Create a new Table from an Existing Table?

Syntax:

CREATE TABLE NEW_TABLE_NAME AS
   SELECT [ column1, column2...columnN ]
   FROM EXISTING_TABLE_NAME
   [ WHERE ]

Example query:

CREATE TABLE SALARY AS SELECT ID,NAME ,SALARY FROM CUSTOMERS;

Some questions here are very specific like date etc, but these are just to give an idea.

Related posts: