How to write complex sql queries?

I have already given the multiple complex sql queries as well as complex sql queries for interview purpose. I have got so many questions and one of the important question is – How to write complex sql queries? In this article i would like to give you multiple steps to write complex sql queries with multiple real life examples in industry. We have heard so many questions like what is mean by complex sql queries? How to write it? What are different complex sql queries?

What is mean by complex sql queries and how to write complex sql queries?

The queries which are going beyond the standard sql syntax and which become complex in syntax. In simple words the complex sql queries are nothing but queries which involves – Complex joins,unions,subqueries,nested queries rather than using the standard SQL syntax.

Real example:

To understand the difference between basic and complex queries i would like to give you example of both basic and complex queries.

Basic SQL query :

If you want to fetch the records from the customer table.

Select * from Customer where customer_name=’Amit’;

The above query will give you information about the customer who’s name is ‘Amit’.

Complex query :

If user wants to fetch the data of customer who bought the shoes from sales table.(joining condition is sales_id and customer_id) and from ‘Amazon.com’ website

Select a.customer_name,b.sale_item from Customer a,Sales b where b.sales_item=’Shoes’ and a.website=’Amazon.com’;

The above query is called as complex sql query. So there is always a question in mind that how do you recognize the complex query? So i would like you to give you some points to recognize the query is complex query with the examples :

1.Use of Multiple AND OR operators :

The queries which are using multiple AND OR operators are called as complex queries.

Example :

If you want to fetch the customer records who’s name is Amit and age is greater than 20.

Query :

Select * from Customer where name=’Amit’ and Age>20;

2.Use of subqueries and corelated subqueries :

The subqueries are also the complex in nature. We require to use multiple queries with using In or contains operator to fetch the required results.

If user wants to fetch the students from student’s table whose marks are maximum(marks not stored in student table and stored in Marks table).

Select roll_no,Student_Name from Student

where Marks=(Select max(Mark_percent) from Marks);

The above query is bit complex where we require to fetch the students whose marks are maximum.

You can check more subqueries here :

Corelated subquery example :

The corelated subqueries are also the type of complex queries where outer query will execute at first step and then the inner query will execute.

Example :

If we need to fetch students who is not assigned to any class.

Select * from Student S where Not exist

(Select class_no From Classes D where S.roll_no=D.roll_no);

The above corelated subquery will fetch information about the students who are not assigned to any class.

3.Use of Joins

The queries with complex joins are considered as complex sql queries. In real life scenarios we always require to use the joins to get specified results. Sometime we require to use multiple tables to fetch results. These queries become complex.

Query with self join :

If you want to fetch the records of employees with his manager names from employee table ,

Query :

Select a.Employee_Name,b.Employee_Name ‘Manager_Name’ from Employee a , Employee b where a.Employee_Id=b.Employee_Id;

Using other joins :

If user wants to fetch employee_name and department_name from multiple tables how to fetch that.

Select a.employee_name,b.department_name from Employee a,Department b where a.employee_id=b.department_id;

So the queries with joins are considered as complex queries in nature.

You can check more queries with joins :

4.Queries with Regular Expressions :

The queries with regular expressions are also the complex queries. There are so many examples where we require to use the regular expressions.

If you want to check the email_format from table,

SELECT
Email
FROM
Employee
where NOT REGEXP_LIKE(Email, ‘[A-Z0-9._%+-]+@[A-Z0-9.-]+\.[A-Z]{2,4}’, ‘i’);

You can check the regular expressions complex queries here :

5.Queries with group by and having clause :

If queries contains group by and having clause those are not simple in nature. Those queries are also considered as having and group by clause.

Example :

Select rollno, count (rollno) from Student

Group by rollno

Having count (rollno)>1

Order by count (rollno) desc;

You can check more information here :

6.Queries with Set operators :

We can consider the queries with set operators as complex sql queries. The queries in which you use union,intersection,minus operators are considered as complex queries.

Example :

Select Employee_Num,Employee_name,Department,Salary from Employee1;

Union

Select Employee_Num,Employee_name,Department,Salary from Employee2;

Set Operator more examples :

These are multiple types of complex queries. I have tried giving multiple examples of it. You can also check following sections where you can check more complex sql queries :

I hope that you like this article and you got the information about the multiple queries. If you like this article or if you have any concerns with the same kindly comment in comments section.