SQL Query Questions and Answers for Practice :
In previous articles i have given different examples of complex sql queries. In this article i will give you SQL Query Questions and Answers for practice which includes the complex sql queries for interviews also. I want to give you different SQL Query Questions and Answers for practice which are not only simple but also complex.All these SQL Questions and Answers for Practice are very useful.
Let us consider table named Employee and with using this table write different SQL Queries
Query 1 : List the employee whose employee number is 100.
Answer:
Simple where clause is used to write this query,
Select * from Employee where employee_Num=100;
Query 2 : List the Employee whose salary is between 50 K to 1 Lac.
Answer:
Here user needs to use between..and operator or where clause less than and greater than operator,
Solution 1 : Using Between..and operator
Select * from Employee where salary between 50000 and 100000;
Solution 2 : Using operators (Greater than and less than)
Select * from Employee where salary >= 50000 and salary <= 100000;
Query 3 : List the Employees whose name starts with ‘Ami’.
Answer :
We need to use like operator to achieve this,
Select * from Employees where name like ‘Ami%’;
Query 4 : List the Employees whose name starts with A and surname starts with S.
Answer :
We need to use like operator to achieve this,
Select * from Employees where name like ‘A%’ and surname like ‘S%’;
Query 5 : List the Employees whos surname contains kar word.
Answer :
We need to use like operator to achieve this,
Select * from Employees where surname like ‘%kar%’;
Query 6: List the Employees whose name starts with P,B,R characters.
Answer:
Select * from Employees where name like ‘[PBR]%’;
Query 7: List the Employees whose name not starts with P,B,R characters.
Answer:
We can achieve this using two queries,
Solution 1 : Using Not operator symbol
Select * from Employees where name like ‘[!PBR]%’;
Solution 2 : Using Not Operator
Select * from Employees where name not like ‘[PBR]%’;
Query 8 : What is query to fetch first record from Employee table?
Answer :
We can achieve this using rownum concept of SQL,
Select * from Employees where rownum=1;
Query 9: What is query to fetch last record from Employees table?
Answer :
We can achieve this using rowid and max function together,
Select * from Employees where rowid = select max(rowid) from Employee;
Query 10 : How to find 2nd highest salary of Employees using Self join?
Answer:
Select * from Employees a where 2 = select count (distinct salary) from Employee where a.salary <= b.salary;
Query 11 : What is query to display odd rows from the Employees table?
Answer:
We can achieve this using Mod function,
Select * from(Select rownum as rno,E.* from Employees E) where Mod(rno,2)=1;
Query 11 : What is query to display even rows from the Employees table?
Answer:
We can achieve this using Mod function,
Select * from(Select rownum as rno,E.* from Employees) where Mod(rno,2)=0;
Query 12 : Find Query to get information of Employee where Employee is not assigned to the department
Answer:
We can achieve this using not in operator,
Select * from Employees where Dept_no Not in(Select Department_no from Employee);
Query 13 : How to Show the Max salary and min salary together from Employees table?
Answer:
Select max (salary) from Employees
Union
Select min (salary) from Employees;
Query 14 : How to get distinct records from the Employees table without using distinct keyword.
Answer:
Select * from Employees a where rowid = (select max(rowid) from Employees b where a.Employee_no=b.Employee_no);
Query 15 :How to fetch all the records from Employee whose joining year is 2018?
Answer:
Oracle:
select * from Employees where To_char(Joining_date,’YYYY’)=’2018′;
MS SQL:
select * from Employees where substr(convert(varchar,Joining_date,103),7,4)=’2018′;
Query 16 : How to display following using query?
*
**
***
Answer:
We cannot use dual table to display output given above. To display output use any table. I am using Employees table.
SELECT lpad (‘*’, ROWNUM,’*’) FROM Employees WHERE ROWNUM <4;
Query 17: What is SQL Query to find maximum salary of each department?
Answer:
To achieve this we need to use max function with group by clause,
Select Dept_id,max(salary) from Employees group by Dept_id;
Query 18:How Do you find all Employees with its managers?(Consider there is manager id also in Employee table)
Answer:
We can achieve this using self join of Employees table,
Select e.employee_name,m.employee name from Employees e,Employees m where e.Employee_id=m.Manager_id;
Query 19 : Display 3 to 7 records from Employee table.
Answer:
Select * from (Select rownum as ‘No_of_Row’, E.* from Employee E)
Where No_of_Row between 3 and 7;
Query 20 : How to fetch common records from two different tables Employees and Employees1 which has not any joining condition.
Answer:
To achieve this we need to use intersect operator,
Select * from Employees
Intersect
Select * from Employees1 ;
Query 21 : Write a query to validate Email of Employee.
Answer :
To achieve this user needs to use Regular Expression function,
SELECT
FROM
Employee
where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);
Query 22 : How to remove duplicate rows from Employees table.
Answer :
Tip: Use concept of max (rowid) of table. Click here to get concept of rowid.
Select Employee_No FROM Employees WHERE ROWID <>
(Select max (rowid) from Employees b where Employee_No =b.Employee_No);
These are above some most important SQL Query Questions and Answers for Practice.Hope you like this article on SQL Query Questions and Answers for Practice.If you like the article on SQL Query Questions and Answers for Practice kindly comment in to comment section.