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.
Hai bro this is raju.i want sql,plsql query’s and answers.do you have please send me.thank you
Hey Raju,
Kindly check your inbox.
Regards,
Amit S
send me some view related questions and other complex queries
Thanks Anup for comments.I have sent you the interview questions on your mail id.
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.
Sure Avinash!! I will send you information on Email id provided!!!
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
Thanks for appreciating blog!! I do not have SQL dumps but i sent you SQL Interview questions on your mail id!!
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.
Thanks Saritha for good words!!!
I sent you the SQL interview questions on respective mail id!!
Hi Amit,
can u please send pl/sql related questions to my mail id..my mail id is surya.future@gmail.com.
Thanks,
Surya
Hi Surya,
I have sent you PDF of SQL interview questions on respective mail id.
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.
Sure Saif .I will share 🙂
Hi Amit,
Can you please send me the sql & pl/sql related query’s and answers to my mail id.
My mail id is rajanare1@gmail.com
Thanks,
Rajesh
Kindy check your mailbox Rajesh for questions
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
Thanks Ankita for lovely comments…Kindly check the mail id for More SQL Related interview questions.
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.
Thanks Seema for lovely comments…Kindly check the mail id for More SQL Related interview questions.
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
Hello Seema,
Thanks for your comments. You can refeer following links for the questions related to joins and subqueries:
Joins : http://www.complexsql.com/sql-joins-interview-questions/
Subqueries :http://www.complexsql.com/subqueries-correlated-subquery/
http://www.complexsql.com/scalar-subqueries-scalar-subqueries-examples/
Hope this helps !!
Regards,
Amit S
can you please send details on rajeshrai1008@gmail.com
Sure Rajesh….:)
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.
Thanks Deepak for your lovely comments.I will send you Queries on your mail id.
Hi Amit,
Could you please share with me more interview questions on SQL and PL/SQL
Regards,
Ravi Ranjan
Sure Ravi…I sent you the questions on respective mail id.
Hi Amit,
Can you please send me the sql & pl/sql related query’s and answers to my mail id.
My mail id is ygtraghav@gmail.com
Thanks,
Yogita
Hi Yogita,
Sure ..I will send toy the SQL interview questions..
Thanks,
Amit
send me some more complex Query
Sent you interview questions..
Can you please send me sql and plsql interview complex queries with answers
Hi Madhuri,
Sent interview questions on your id!!
Regards,
Amit S
Could you please send me complex SQL and plsql queries with answer.my mailid jayakumar.as27@gmail.com
Hi Jayakumar,
Sent you interview questions on your mail id.
Regards,
Amit s
Awesome , can you please share some more complex queries for data science to my mail email id: sibivarun@gmail.com
Thanks Shibi.I have sent you more SQL Query Questions and Answers for Practice for your reference.
Thanks for this article. Please share me all the important interview questions and answers .
Sure Raghavendra!!! I will share you important SQL Query Questions and Answers for Practice for your reference.
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
Hello Anand,
Kindly check following interview questions :
http://www.complexsql.com/sql-joins-interview-questions/
http://www.complexsql.com/complex-sql-queries-examples-with-answers/complex-sql-for-interviews/
http://www.complexsql.com/oracle-sql-interview-questions-oracle-interview-questions/
Regards,
Amit S
Thanks for this queries
can you please send me sql and pl/sql related interview queries to my mail : manandeepsingh456@gmail.com
I sent you interview questions on your mail id..Kindly check!!
Hi
I need questions to JOIN more than two tables to fetch data.
Hi Shubham,
Check this :
http://www.complexsql.com/sql-joins-interview-questions/
Regards,
Amit S
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.
Sure!!! Kirti!!! I will send you all kind of interview questions
can you please send me sql server related interview queries to my mail : kush.kaushik87@gmail.com
thank you
Sure Kush!!! I will send you interview questions !!
Hi Amit, Could you please share the interview question with me on email id: mrinalini8june@gmail.com
Thanks in advance
Sure Mini!! Sent you interview questions on your mail id!!