Learn how to write SQL Queries(Practice Complex SQL Queries)

Learning SQL syntax is very easy but getting comfortable in writing SQL Queries, especially the complex SQL Queries can be tricky and will need a lot of practice.

In this blog, I have listed below 9 SQL Queries which should help you to practice intermediate to complex SQL queries.

You will find below SQL Questions along with the data and table structure required to solve each SQL question. The SQL Query to solve these questions will be attached to an .txt file. You can simple download the file for each question to get the solved SQL Queries.

*** Note: Please note, I have used PostgreSQL database to executed all of these queries. I believe these queries would work just fine with any other major RDBMS such as Oracle, MySQL, Microsoft SQL Server. However, if you find any query not working in your RDBMS, then leave a comment below so I could help.

Write a SQL Query to fetch all the duplicate records in a table.
Table Name: USERS
Note: Record is considered duplicate if a user name is present more than once.
Approach: Partition the data based on user name and then give a row number to each of the partitioned user name. If a user name exists more than once then it would have multiple row numbers. Using the row number which is other than 1, we can identify the duplicate records.

There are several ways to write this query. Such as either using the CTID field in PostgreSQL or by using the ROWID field in Oracle, MySQL, Microsoft SQL Server etc but a simpler way to write this query would be using window function.

Try to write this query yourself before looking the query I have written to solve it.

Click on the download button below to download the .txt file which will have the table structure, table data and the solved SQL Query.

Download Script - Query 1

2. Write a SQL query to fetch the second last record from employee table.

Table Name: EMPLOYEE

Approach: Using window function sort the data in descending order based on employee id. Provide a row number to each of the record and fetch the record having row number as 2.

Again, there are several ways to write this query but this becomes very simple using a window function.

Try to write this query yourself before looking the query I have written to solve it.

Click on the download button below to download the .txt file which will have the table structure, table data and the solved SQL Query.

Download Script - Query 2

3. Write a SQL query to display only the details of employees who either earn the highest salary or the lowest salary in each department from the employee table.

Table Name: EMPLOYEE

Approach: Write a sub query which will partition the data based on each department and then identify the record with maximum and minimum salary for each of the partitioned department. Finally, from the main query fetch only the data which matches the maximum and minimum salary returned from the sub query.

Again there are many way to do this and also we can use a few window functions to achieve the same result. As an added challenge, try out solving this query using a different window function and then comment out your query.

Click on the download button below to download the .txt file which will have the table structure, table data and the solved SQL Query.

Download Script - Query 3

4. From the doctors table, fetch the details of doctors who work in the same hospital but in different specialty.

Table Name: DOCTORS

Approach: Use self join to solve this problem. Self join is when you join a table to itself.

Additional Query: Write SQL query to fetch the doctors who work in same hospital irrespective of their specialty.

Expected Output: Same hospital different speciality — **Expected Output:** Same hospital different speciality

Expected Output: Same hospital irrespective of speciality — **Expected Output:** Same hospital irrespective of speciality

Click on the download button below to download the .txt file which will have the table structure, table data and the solved SQL Query.

Download Script - Query 4

5. From the login_details table, fetch the users who logged in consecutively 3 or more times.

Table Name: LOGIN_DETAILS

Approach: We need to fetch users who have appeared 3 or more times consecutively in login details table. There is a window function which can be used to fetch data from the following record. Use that window function to compare the user name in current row with user name in the next row and in the row following the next row. If it matches then fetch those records.

Click on the download button below to download the .txt file which will have the table structure, table data and the solved SQL Query.

Download Script - Query 5

6. From the students table, write a SQL query to interchange the adjacent student names.

Note: If there are no adjacent student then the student name should stay the same.

Table Name: STUDENTS

Approach: Assuming id will be a sequential number always. If id is an odd number then fetch the student name from the following record. If id is an even number then fetch the student name from the preceding record. Try to figure out the window function which can be used to fetch the preceding the following record data.

If the last record is an odd number then it wont have any adjacent even number hence figure out a way to not interchange the last record data.

Click on the download button below to download the .txt file which will have the table structure, table data and the solved SQL Query.

Download Script - Query 6

7. From the weather table, fetch all the records when London had extremely cold temperature for 3 consecutive days or more.

Note: Weather is considered to be extremely cold when its temperature is less than zero.

Table Name: WEATHER

Approach: First using a sub query identify all the records where the temperature was very cold and then use a main query to fetch only the records returned as very cold from the sub query. You will not only need to compare the records following the current row but also need to compare the records preceding the current row. And may also need to compare rows preceding and following the current row. Identify a window function which can do this comparison pretty easily.

Click on the download button below to download the .txt file which will have the table structure, table data and the solved SQL Query.

Download Script - Query 7

8. From the following 3 tables (event_category, physician_speciality, patient_treatment), write a SQL query to get the histogram of specialties of the unique physicians who have done the procedures but never did prescribe anything.

Table Name: EVENT_CATEGORY, PHYSICIAN_SPECIALITY, PATIENT_TREATMENT

Approach: Using the patient treatment and event category table, identify all the physicians who have done “Prescription”. Have this recorded in a sub query.

Then in the main query join the patient treatment, event category and physician speciality table to identify all the physician who have done “Procedure”. From these physicians, remove those physicians you got from sub query to return the physicians who have done Procedure but never did Prescription.

Click on the download button below to download the .txt file which will have the table structure, table data and the solved SQL Query.

Download Script - Query 8

9. Find the top 2 accounts with the maximum number of unique patients on a monthly basis.

Note: Prefer the account id with the least value in case of same number of unique patients

Table Name: PATIENT_LOGS

Approach: First convert the date to month format since we need the output specific to each month. Then group together all data based on each month and account id so you get the total no of patients belonging to each account per month basis.

Then rank this data as per no of patients in descending order and account id in ascending order so in case there are same no of patients present under multiple account if then the ranking will prefer the account if with lower value. Finally, choose upto 2 records only per month to arrive at the final output.