SQL Complex Queries

SQL Query Questions and Answers for Practice for 2021

Amit S July 24, 2018

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.

206 Replies to “SQL Query Questions and Answers for Practice for 2021”

  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.

      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

  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.

  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.

      1. bro
        plz share me the lab model queries with answers and also interview questions pdf mail id:sabhavathmahesh1805@gmail.com….
        thanks,
        MAHESH……

  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

  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.

      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

  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.

  11. Hi Amit,

    Could you please share with me more interview questions on SQL and PL/SQL

    Regards,
    Ravi Ranjan

  17. Thanks for this article. Please share me all the important interview questions and answers .

  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

  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.

  26. Hi Amit,
    Can You send some interesting select statement queries , joins, views, store procedure, constraints, operators and cte queries. Maximum i need all kind of SQL Server Queries.
    And this is my mail id: suganeshbaskar@gmail.com

  29. Hello Amit,

    Can you please send me the SQL interview questions. It would be great if could share SQL query related questions as well. Thanking you in advance.

    Email: dipanjannet@gmail.com

      1. hi amit can u pls share some IQ questions for sql related gonna attend interview on cts tomorow
        share me some documents to this mail id: glenhari13@gmail.com

        thanks in advance

  31. Thank you for posting this. Can you please interview questions for SQL to my email ID? Thank you!

  34. Hi bro,
    This is Ravi.
    I want sql,plsql query’s and answers with their tables for practice
    Please send me.
    Thank you in advance

  35. hey , ur blog is very nice. can u please share me some more queries on Joins as i m a beginner and preparing for SQL interviews.

  38. Hi Amit,
    Great work Amit.
    I want Sql queries with answers on Joins and SubQueries. Could you please share me that.
    This will be very helpful. Thanks

  40. Thanks for publishing this! Can you email me some SQL beginner level and advanced level queries and tables to practice, please?

  42. Hai, this is very useful queries, can you please give some more queries on joins, sub queries and date related queries…..

  43. Hi,
    This article is very helpful. Can you please share more questions related to operators like group by, sets, order by, joins, subqueries and window functions i..e complex ones to bhawna.mscs@gmail.com?

    Thanks,
    Bhawna

  44. Hi Amit,
    First of all i want to appreciate you because the way you are helping others is very much helpful to their career growth.
    Can you email me complex SQL queries of all topics from beginner level and advanced level , please?
    Email id: chaitanyapondala@gmail.com

  45. Hello Amit,

    Thanks for helping us.
    Could you please send me SQL questions for joins, views, subqueries, CTE, Rank, dense rank, aggregations.
    I want some scenario based questions for good practice.

  46. Hi,
    Can you kindly provide complex sql question with answer and also provide SQL beginner level and advanced level queries and tables to practice.

    1. Hi Malcom ,
      I sent you SQL Query Questions and Answers for Practice. Kindly check it.

      Regards and thanks,
      Amit Shiravadekar

  50. Please share complex queries and joins multiple joins and all of that complex interview questions

  51. Hi sir, can I know more sql queries which are frequently asked in interviews and complex queries

  52. Hello Amit, can you please send some complex join related questions on the given email id for practice purpose with some example?

  54. Hi Amit, can you send me advanced SQL queries and solutions to prepare for mid-level SQL developer hands-on interviews.

  55. Hi Amit,
    Would you send me some interview questions on sub queries, joins,group by,having, limit ans also time and date types such as sql query to fetch recently added records etc,.

    Thanks

  56. Hi Amit S,
    Thank you.
    You providing this SQL quries
    Could you plese send most interview SQL quries and all DDL ,DCL and DML commnds in pdf type.

      1. still i didnt get any documents.so can u pls send documents like joins and subquery related quetsions and solved answers.

  60. Hi Amit,

    Very nice details about SQL interview questions, which may look easy to answer but need little efforts to derive accurate query. This gives really good thought process on how to think formatting queries to such questions. Really useful brother!! Could you share more similar and more complex interview questions on sql queries (related to SQL joins and other topics) on my email?

    Also, for question no.14 above, little doubt how outer table a will be accessible in the inner query as it will be executed first? May be i am missing something, so want to clarify.

    Thanks in advance!!

  61. Thank you, could you please send all the queries to me i am in dire need of practice, please.

  62. Hi Amit,

    I really liked the interview questions on queries, for which my wife is currently searching for. This material will really help her a lot for her preparation. Could you please share more Oracle SQL interview questions for her to prepare more.

    Thanks in advance!

  64. Hi Amit ,
    Can you please send me more complex sql queries with relevant solutions.

    Thanks in Advance

  67. Hi Amit,

    Thanks for the questions for practising . Its really helpfull. will u send me the complex and different queries on different topics on my email id please…..

  71. Hi Amit,

    Thanks for the questions for practicing . Its really helpful. will u send me the complex and different queries on different topics on my email id please…..

  73. Hi Amit,
    Thanks for sql questions, please share inner queries /sub queries, Joins and more common SQL interview problems to my email
    raz4rent@gmail.com
    Thanks In Advance.
    Regards,
    Rajesh K

  74. Hi Amit
    First of all so much thanku for the provided above queries for practice.
    Can you please share some more questions (focus more on different joins) for practice on my mentioned email id , It will be very helpful.

  76. Hi Amit,

    Nice blog and very good information.
    Thanks a lot

    Can you please share me few more complex sql queries so that it helps me a lot.

  77. Hi Amit, My name is Shiv. I just download sql. I am trying to do some practice here but I need a database for this practice. can you please send me link to get database for practice .

  79. Hi Amit,

    Can you provide me SQL topicwise all sql solved queries for practise?
    Or pdf which have number of solved queries?

  80. Hi Amit,

    Thanks for uploading imp questions on SQL query.

    I request you to please share me related question on ,inner join, outer join and ( insert into, update, delete) and aggregates functions, range on my personal mail id: hiramazhar1717@gmail.com

    This is my humble and kind request to you.

    Thanks in advance.

    1. Sure Hira…
      Joins Interview Questions : http://www.complexsql.com/sql-joins-interview-questions/http://www.complexsql.com/category/this-category-includes-sql-interview-questions/
      http://www.complexsql.com/pl-sql-examples/.http://www.complexsql.com/unix-scripting-interview-questions/
      http://www.complexsql.com/etl-testing-interview-questions/
      http://www.complexsql.com/data-modeling-interview-questions-with-answers-for-professionals/

      Like this page on Facebook for More Updates :
      https://www.facebook.com/Complexsqlcom-1302739539812605/  
      Regards and thanks,
      Amit Shiravadekar

  82. Hi Amit,
    can you please share me the pdf/doc related to more complex and simple SQL queries on Joins, Multiple Joins, Union, Union all, Intersect and views,sub queries, keys and command related questions. Thanks in advance.

    1. Yes Sowmya…
      Joins Interview Questions : http://www.complexsql.com/sql-joins-interview-questions/http://www.complexsql.com/category/this-category-includes-sql-interview-questions/
      http://www.complexsql.com/pl-sql-examples/.http://www.complexsql.com/unix-scripting-interview-questions/
      http://www.complexsql.com/etl-testing-interview-questions/
      http://www.complexsql.com/data-modeling-interview-questions-with-answers-for-professionals/

      Like this page on Facebook for More Updates :
      https://www.facebook.com/Complexsqlcom-1302739539812605/  

      Regards and thanks,
      Amit S

  83. Can you send me sql queries based on views,stored procedure,triggers,functions,indexes?

    1. Sure Pranati..
      On Special Request of some of the users kindly check following interview questions related to joins and advanced SQL:
      Joins Interview Questions : http://www.complexsql.com/sql-joins-interview-questions/
      http://www.complexsql.com/category/this-category-includes-sql-interview-questions/
      http://www.complexsql.com/pl-sql-examples/.
      http://www.complexsql.com/unix-scripting-interview-questions/
      http://www.complexsql.com/etl-testing-interview-questions/
      http://www.complexsql.com/data-modeling-interview-questions-with-answers-for-professionals/

      Like this page on Facebook for More Updates :

      https://www.facebook.com/Complexsqlcom-1302739539812605/

  85. i want to find an answer for this
    1. consider the customer data base given below.
    customer(custNo:int,custname:string,state:char(2),phone:number)
    item(itemno:int,itemName:string,itemprice:float,Qtyonhand:int)
    INVOICE(invno:int,invdate:date,custno:int)
    invoiceitem(invno:int,itemno:int,qty:int)

    create the above tables by properly specifying the primary keys and the foreign keys

    enter appropriate number of tuple(minimum3)for each table.

    1.find all customer whose names starts with letter E
    2.count the number the number of items ordered in each invoce.
    3.display the names of items ordered in invoice number 1001 using subquery.
    4.create a simple view with item name and item prices only.
    5.sort all item in descending order by their price.

  86. Hi Amit,

    Thanks for such informative questions, can u plz provide some more questions and solutions for multiple joins, with clause usage, and more complex queries with more joins.
    sn.haque136@gmail.com

  92. Hi Amit,

    Can you email(vivek.dbg15@gmail.com) me some complex questions on queries related to subqueries(nested,normal,corelated,inline),joins etc so that i can
    practice on them?Try mailing me as many as possible.

    Regards,
    Vivek

  93. We have a funding_transactions table:
    id| user_id | timestamp | funding_type | amount
    Note: funding_type could be direct_deposit, ach_transfer, cash_deposit, check_deposit
    Report the daily total amount of direct_deposit per user for the past 30 days
    Identify the id, timestamp, and amount of the first ever cash_deposit for all users, null if never had a cash_deposit

Comments are closed.