SQL Complex Queries

SQL Query Questions and Answers for Practice

Amit S July 24, 2018 52 Comments

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;

Click here for explanation.

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);

SQL Query Questions and Answers for Practice

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
Email
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.

52 Replies to “SQL Query Questions and Answers for Practice”

  2. Hi Amit,
    I am visiting your site multiple times, can you please share me the pdf/doc related to more complex SQL queries on Joins, Multiple Joins, Union, Union all, Intersect, Substr, Instr, Ltrim, Rtrim and views related questions. Thanks in advance.

    Reply

      1. Hi Amit,

        Hope you are doing good !!

        Would it be possible for you share SQL dump so that i can insert data and start practicing the questions and also can you please complex SQL queries on Joins, Multiple Joins, Union, Union all, Intersect, Substr, Instr, Ltrim, Rtrim and views related questions and interview questions for experienced positions.

        great work mate, much appreciated. keep doing such blogs. how can i follow all your blogs related to SQl & other technologies like Selenium.

        thanks,
        MK

        Reply

  3. Hi Amit,
    I like the way u updated the site regarding queries, could you please share me the pdf related to simple and more complex SQL queries on Joins, Multiple Joins, Union, Union all, Intersect, Substr, Instr, Ltrim, Rtrim and views related questions.
    Thanks in advance.

    Reply

  5. Hi Amit,

    Thanks for uploading imp questions on SQL query.

    I request you to please share me related question on multiple joins,inner join full join and also interview related questiion on my personal mail id:saifsaiyed25@gmail.com

    This is my humble and kind request to you.

    Thanks in advance.

    Reply

  7. Hii Amit
    Please send me questions on joins,indexes, functions like substr, substring, aggregate functions,set operators and comparison operators,views,grant revoke, transaction command’s ddl and dml commands,clauses like where,group by, having,order by

    Reply

  8. Hi Amit,
    Thanks for writing these SQL queries, they are very helpful. I have a request. Could you please share with me queries on Joins – inner, self, outer, right, left joins and on Sub-queries. Please.

    Reply

      1. Hi Amit,

        Thanks for the PDF on interview questions. But I want Sql queries with answers on Joins and SubQueries. Could you please share me that. This will be very helpful. Thanks

        Reply

  10. Hi… Amit
    Your Question related to SQL very helpful for me. That’s a grate good job bro..
    Can have you send me the some SQL Query Question in my mail id.

    Reply

  18. hi
    i need more question on joins , where clause ,set operators and comparison operators,views,grant revoke, transaction command’s ddl and dml commands,clauses like where,group by, having,order by

    Reply

  21. Hi. Can you please provide me with some interview SQL Questions based on the topics :
    Join and group by in one query; group by on two variables; sub query, join (left, inner, outer, right) and group by in one script; windows function such as partition and rank; use Case statements, use update query, append query, partition function, dense rank.

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *