sql assignment questions

SQL Tutorial

Sql database, sql references, sql examples, sql exercises.

You can test your SQL skills with W3Schools' Exercises.

We have gathered a variety of SQL exercises (with answers) for each SQL Chapter.

Try to solve an exercise by filling in the missing parts of a code. If you're stuck, hit the "Show Answer" button to see what you've done wrong.

Count Your Score

You will get 1 point for each correct answer. Your score and total score will always be displayed.

Start SQL Exercises

Start SQL Exercises ❯

If you don't know SQL, we suggest that you read our SQL Tutorial from scratch.

Kickstart your career

Get certified by completing the course

COLOR PICKER

Contact Sales

If you want to use W3Schools services as an educational institution, team or enterprise, send us an e-mail: [email protected]

Report Error

If you want to report an error, or if you want to make a suggestion, send us an e-mail: [email protected]

Get Started with 100 SQL Query Interview Questions

We recommend that you first try to form queries by yourself rather than just reading them from the post. This tutorial includes SQL scripts to create the test data. So, you can use them to create a test database and tables. Once you have done enough SQL practice, then also check out another post on SQL interview prep for QA and DBAs .

Let’s Prepare Sample Data for SQL Practice

Sample table – worker, sample table – bonus, sample table – title.

To prepare the sample data, you can run the following queries in your database query executor or on the SQL command line. We’ve tested them with the latest version of MySQL Server and MySQL Workbench query browser. You can download these tools and install them to execute the SQL queries. However, these queries will run fine in any online MySQL compiler, so you may use them.

SQL Script to Seed Sample Data.

Once the above SQL runs, you’ll see a result similar to the one attached below.

SQL Query Questions - Creating Sample Data

Practice with SQL Queries Asked in Interview Questions

Below are the 50 commonly asked SQL queries in interviews from various fields. For the second part including 50 tricky SQL queries for interview, read from below.

Must Read: 50 Tricky SQL Queries for Practice Before Your Interview

Q-1. Write an SQL query to fetch “FIRST_NAME” from the Worker table using the alias name <WORKER_NAME>.

The required query is:

Q-2. Write an SQL query to fetch “FIRST_NAME” from the Worker table in upper case.

Q-3. write an sql query to fetch unique values of department from the worker table., q-4. write an sql query to print the first three characters of first_name from the worker table., q-5. write an sql query to find the position of the alphabet (‘a’) in the first name column ‘amitabh’ from the worker table..

The INSTR does a case-insensitive search.
Using the BINARY operator will make INSTR work as the case-sensitive function.

Q-6. Write an SQL query to print the FIRST_NAME from the Worker table after removing white spaces from the right side.

Q-7. write an sql query to print the department from the worker table after removing white spaces from the left side., q-8. write an sql query that fetches the unique values of department from the worker table and prints its length., q-9. write an sql query to print the first_name from the worker table after replacing ‘a’ with ‘a’., q-10. write an sql query to print the first_name and last_name from the worker table into a single column complete_name. a space char should separate them., q-11. write an sql query to print all worker details from the worker table order by first_name ascending., q-12. write an sql query to print all worker details from the worker table order by first_name ascending and department descending., q-13. write an sql query to print details for workers with the first names “vipul” and “satish” from the worker table., q-14. write an sql query to print details of workers excluding first names, “vipul” and “satish” from the worker table., q-15. write an sql query to print details of workers with department name as “admin”., q-16. write an sql query to print details of the workers whose first_name contains ‘a’., q-17. write an sql query to print details of the workers whose first_name ends with ‘a’., q-18. write an sql query to print details of the workers whose first_name ends with ‘h’ and contains six alphabets., q-19. write an sql query to print details of the workers whose salary lies between 100000 and 500000., q-20. write an sql query to print details of the workers who joined in feb 2021., q-21. write an sql query to fetch the count of employees working in the department ‘admin’..

At this point, you have acquired a good understanding of the basics of SQL, let’s move on to some more intermediate-level SQL query interview questions. These questions will require us to use more advanced SQL syntax and concepts, such as GROUP BY, HAVING, and ORDER BY.

Q-22. Write an SQL query to fetch worker names with salaries >= 50000 and <= 100000.

Q-23. write an sql query to fetch the number of workers for each department in descending order., q-24. write an sql query to print details of the workers who are also managers., q-25. write an sql query to fetch duplicate records having matching data in some fields of a table., q-26. write an sql query to show only odd rows from a table., q-27. write an sql query to show only even rows from a table., q-28. write an sql query to clone a new table from another table..

The general query to clone a table with data is:

The general way to clone a table without information is:

An alternate way to clone a table (for MySQL) without data is:

Q-29. Write an SQL query to fetch intersecting records of two tables.

Q-30. write an sql query to show records from one table that another table does not have., q-31. write an sql query to show the current date and time..

The following MySQL query returns the current date:

Whereas the following MySQL query returns the current date and time:

Here is a SQL Server query that returns the current date and time:

Find this Oracle query that also returns the current date and time:

Q-32. Write an SQL query to show the top n (say 10) records of a table.

MySQL query to return the top n records using the LIMIT method:

SQL Server query to return the top n records using the TOP command:

Oracle query to return the top n records with the help of ROWNUM:

Now, that you should have a solid foundation in intermediate SQL, let’s take a look at some more advanced SQL query questions. These questions will require us to use more complex SQL syntax and concepts, such as nested queries, joins, unions, and intersects.

Q-33. Write an SQL query to determine the nth (say n=5) highest salary from a table.

MySQL query to find the nth highest salary:

SQL Server query to find the nth highest salary:

Q-34. Write an SQL query to determine the 5th highest salary without using the TOP or limit method.

The following query is using the correlated subquery to return the 5th highest salary:

Use the following generic method to find the nth highest salary without using TOP or limit.

Q-35. Write an SQL query to fetch the list of employees with the same salary.

Q-36. write an sql query to show the second-highest salary from a table., q-37. write an sql query to show one row twice in the results from a table., q-38. write an sql query to fetch intersecting records of two tables., q-39. write an sql query to fetch the first 50% of records from a table..

Practicing SQL query interview questions is a great way to improve your understanding of the language and become more proficient in using it. However, in addition to improving your technical skills, practicing SQL query questions can also help you advance your career. Many employers are looking for candidates who have strong SQL skills, so being able to demonstrate your proficiency in the language can give you a competitive edge.

Q-40. Write an SQL query to fetch the departments that have less than five people in them.

Q-41. write an sql query to show all departments along with the number of people in there..

The following query returns the expected result:

Q-42. Write an SQL query to show the last record from a table.

The following query will return the last record from the Worker table:

Q-43. Write an SQL query to fetch the first row of a table.

Q-44. write an sql query to fetch the last five records from a table., q-45. write an sql query to print the names of employees having the highest salary in each department., q-46. write an sql query to fetch three max salaries from a table., q-47. write an sql query to fetch three min salaries from a table., q-48. write an sql query to fetch nth max salaries from a table., q-49. write an sql query to fetch departments along with the total salaries paid for each of them., q-50. write an sql query to fetch the names of workers who earn the highest salary..

Must Check: SQL Programming Test with 20+ Basic to Advanced Queries

Summary – Master SQL with Practice Questions.

We hope you enjoyed solving the SQL exercises and learned something new along the way. Stay tuned for our next post, where we’ll bring you even more challenging SQL query interview questions to sharpen your proficiency.

Thanks for reading! We hope you found this tutorial helpful. If you did, please consider sharing it with your friends and colleagues. You can also follow us on our social media platforms for more helpful resources. And if you’re looking for more information on this topic, be sure to check out the “You Might Also Like” section below.

-TechBeamers.

How to use union in sql queries, if statement in sql queries: a quick guide, where clause in sql: a practical guide, a beginner’s guide to sql joins, 20 sql tips and tricks for better performance.

4 Unique Ways to Check if Windows is 32-bit or 64-bit

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

7 Demo Websites to Practice Selenium Automation Testing

SQL Exercises with Sample Table and Demo Data

SQL Exercises – Complex Queries

Java Coding Questions for Software Testers

15 Java Coding Questions for Testers

30 Quick Python Programming Questions On List, Tuple & Dictionary

30 Python Programming Questions On List, Tuple, and Dictionary

25 SQL Performance Interview Questions and Answers

Tutorial Resources

Practice Exercises

AND & OR
COMPARISON OPERATORS
IS NOT NULL
SELECT LIMIT

SQL Advanced

ALTER TABLE
CREATE TABLE
CREATE TABLE AS
GLOBAL TEMP
PRIMARY KEY

SQL Functions

SQL: Practice Exercises for SELECT Statement

If you want to test your skills using the SQL SELECT statement, try some of our practice exercises.

These practice exercises allow you to test your skills with the SELECT statement. You will be given questions that you need to solve. After each exercise, we provide the solution so you can check your answer.

Get started!

Return to Tutorial

Practice Exercise #1:

Based on the employees table below, select all fields from the employees table whose salary is less than or equal to $52,500 (no sorting is required):

Solution for Practice Exercise #1:

The following SQL SELECT statement would select these records from the employees table:

These are the results that you should see:

Practice Exercise #2:

Based on the suppliers table below, select the unique city values that reside in the state of California and order the results in descending order by city :

Solution for Practice Exercise #2:

The following SELECT statement would select these records from the suppliers table:

Practice Exercise #3:

Based on the customers table and the orders table below, select the customer_id and last_name from the customers table and select the order_date from the orders table where there is a matching customer_id value in both the customers and orders tables. Order the results by customer_id in descending order.

Solution for Practice Exercise #3:

The following SQL SELECT statement would select these records from the customers and orders table (using an INNER JOIN ):

Practice Exercise #4:

Based on the customers and orders table from Practice Exercise #3, select the customer_id and last_name from the customers table where there is a record in the orders table for that customer_id . Order the results in ascending order by last_name and then descending order by customer_id .

Solution for Practice Exercise #4:

The following SQL SELECT statement would select the records from the customers and orders table (using the SQL EXISTS clause ):

Or alternatively you could exclude the ASC keyword for customer_name in the ORDER BY clause . Both of these SELECT statements would generate the same results:

Home | About Us | Contact Us | Testimonials | Donate

While using this site, you agree to have read and accepted our Terms of Service and Privacy Policy .

SQL Server training
Write for us!

SQL Practice – common questions and answers for SQL skills

This article will show some important questions and answers to practice SQL.

Introduction

SQL is the base to handle different databases. Even some of the NoSQL databases use an extension of SQL to query data. Data Scientists, BI Analysts, BI Developers, DBAs, Database Developers, Data Engineers, Reporting Analysts, and several other jobs require SQL knowledge as part of the knowledge required. For data management, SQL knowledge is the base to handle databases and the numbers of jobs related to databases are growing each day. The software industry, including databases, is the world’s most in-demand profession. That is why in this article, we will show some questions and answers to practice SQL.

Requirements

In order to start, you need the SQL Server installed and the Adventureworks sample database installed. The following links can help you if you do not have them installed:

How to install SQL Server developer edition
Install and configure the AdventureWorks2016 sample database

Q1. Please select all the information of persons table that the name starts with A

A1. You will show all the columns with the select * and the where [FirstName] like ‘a%’ will filter the data to all the persons that the name starts with a. The query used is the following:

The LIKE operator is very common in SQL queries. The like ‘a%’ will show all the first names that start with the letter a. There are several other operators that you need to practice like the EXISTS, IN, =, <>, ANY. The following link provides more information about the operators:

Operators (Transact-SQL)

Note that the use of * is a bad practice for big tables, but in this case, it is a small table, so it will not impact performance. In general, try to select only the columns required and not all.

Q2. Create a store procedure that receives the first name of the person table as input and the last name as output.

In SQL practice, it is necessary to mention the stored procedures because they are frequently used in SQL Server. The stored procedures are T-SQL sentences that cannot be applied to other databases like Oracle, MySQL, PostgreSQL. The following sample of code shows how to create a stored procedure named GetLastName. It receives the parameter @firstname and returns the last name of the provided firstname. It queries the Person table:

To call the stored procedure using T-SQL, you will need to declare a variable to store the output variable. We use the execute command to call a stored procedure. John is the parameter value for the first name and the last name is the output of the stored procedure:

The result displayed by this store procedure invocation will be something like this:

For more information about creating stored procedures, please refer to this link:

Learn SQL: User-Defined Stored Procedures
SQL Server stored procedures for beginners
CREATE PROCEDURE (Transact-SQL)

Q3. Which query would you execute to delete all the rows from the person table with minimal bulk-logged activity?

A3. The truncate table sentence removes data without logging individual row deletions. It is the most efficient way to remove all the data. The delete command, on the other hand, logs a lot of data if we have multiple rows and can consume a lot of space in the transaction log files. So, the code will be the following:

Truncate table person.person

To practice SQL, try the truncate and delete statements. For more detailed information about the differences between the truncate and the delete sentences, refer to this link:

The internals of SQL Truncate and SQL Delete statements

Q4. Create a query to show the account number and customerid from the customer table for the customer without sales orders.

A4. The query would be something like this:

The result displayed will be something like this:

We are using the left join to look for all the customers without sales, so the salesorderid will be null. In SQL practice, you need to know the use of the different JOINS like the LEFT JOIN, RIGHT JOIN, CROSS JOIN. We created some articles about the different types of JOINs here. It is very important for you to check, practice, and study all the options:

Learn SQL: Join multiple tables
Learn SQL: INNER JOIN vs LEFT JOIN
SQL CROSS JOIN with examples

We could also use the EXISTS or the IN operators instead of using the JOINS. The performance is different according to the scenario. The performance in queries is out of the scope of this SQL practice. However, we have an entire article about this topic here:

How to design SQL queries with better performance: SELECT * and EXISTS vs IN vs JOINs

Q5. You have a table with some of the temperatures in Celsius of some patients. Create a function or a stored procedure to get the convert Fahrenheit to Celsius. Would you use a function or a stored procedure?

A5. Basically, the question here is to create a stored procedure or a function to convert from Celsius to Fahrenheit. If you already have a table with some rows in Celsius, the easiest option is to use a function. The function could be something like this:

Invoking a function is easier than a stored procedure. For more information about the use of functions vs stored procedures, refer to this link:

Functions vs stored procedures in SQL Server

Q6. Create a query to show the top 10 customerIDs of users with more Orders.

A6. For this practice test, we will use the TOP 10 to get the customer IDs with more orders. We will use the SUM to SUM the Order Quantity column. Note that for aggregated functions like the SUM, the alias is needed to define the column name in the query. Also, the Sum is grouped by the customer ID. Usually aggregated functions come with the GROUP BY clause. Finally, we are using the order by to order the result in descendant order:

query with the sum, group by and order by including the TOP sentence

For more information about aggregated functions like the SUM, MAX, MIN, AVG, refer to this link:

Aggregate Functions (Transact-SQL)

In this article, we show different questions for SQL practice. We show some questions and answers to practice SQL and improve the knowledge. If you have more questions about this topic, do not hesitate to contact us.

Recent Posts

PostgreSQL tutorial to create a user - November 12, 2023
PostgreSQL Tutorial for beginners - April 6, 2023
PSQL stored procedures overview and examples - February 14, 2023

A complete guide to T-SQL Metadata Functions in SQL Server
Top SQL Server Books
An overview of the SQL Server Update Join
FOR XML PATH clause in SQL Server
Creating and using CRUD stored procedures

Revising the Select Query I Easy SQL (Basic) Max Score: 10 Success Rate: 95.95%

Revising the select query ii easy sql (basic) max score: 10 success rate: 98.68%, select all easy sql (basic) max score: 10 success rate: 99.54%, select by id easy sql (basic) max score: 10 success rate: 99.66%, japanese cities' attributes easy sql (basic) max score: 10 success rate: 99.59%, japanese cities' names easy sql (basic) max score: 10 success rate: 99.53%, weather observation station 1 easy sql (basic) max score: 15 success rate: 99.42%, weather observation station 3 easy sql (basic) max score: 10 success rate: 97.99%, weather observation station 4 easy sql (basic) max score: 10 success rate: 98.72%, weather observation station 5 easy sql (intermediate) max score: 30 success rate: 94.35%, cookie support is required to access hackerrank.

Seems like cookies are disabled on this browser, please enable them to open this website

SQL EXERCISES

30 Exercises: agregate functions, order, group by, having , boolean, joins.
14 Exercises: select, filtering, scalar functions, group by, joins, subquery, tables, DDL.
Beginner – Intermediate
400 Exercises: sql queries, filtering, sorting, multiple tables, joins, subqueries.
140 Exercises
40 Exercises: select, variables, subqueries, joins, aggregation, data modification.
100 Exercises
20 Exercises: select, sum, count, joins, nulls.
20 Exercises/projects/challenges
Intermediate
60 Exercises: multiple tables queries.
50 Exercises
1 Challenge: Football World Cup 2014
27 Practice exams: databases
16 Skills evaluation tests
7 Evaluation questions
45 Interview questions
20 Interview questions. 10 Exercises
4 Exercises & Mock interview questions: joins and sub queries.
50 Theory questions
15 Theory questions: MySQL certification
Challenge & Quiz
Intermediate – Advanced
50 Exercises: multiple table queries
10 Exercises: subqueries, joins.
Beginner – Intermediate – Advanced
190 Exercises
30 Exercises/Labs
20 Challenges
12 SQL Server Developer questions.

Terms of Use

Shopping cart

ArtOfTesting

Interview , SQL

SQL Query Interview Questions for Freshers

Here is a list of top SQL query interview questions and answers for fresher candidates that will help them in their interviews. In these queries, we will focus on the basic SQL commands only.

1. Write an SQL query to fetch the EmpId and FullName of all the employees working under the Manager with id – ‘986’. We can use the EmployeeDetails table to fetch the employee details with a where clause for the manager-

2. Write an SQL query to fetch the different projects available from the EmployeeSalary table. While referring to the EmployeeSalary table, we can see that this table contains project values corresponding to each employee, or we can say that we will have duplicate project values while selecting Project values from this table. So, we will use the distinct clause to get the unique values of the Project.

3. Write an SQL query to fetch the count of employees working in project ‘P1’. Here, we would be using aggregate function count() with the SQL where clause-

4. Write an SQL query to find the maximum, minimum, and average salary of the employees. We can use the aggregate function of SQL to fetch the max, min, and average values-

5. Write an SQL query to find the employee id whose salary lies in the range of 9000 and 15000. Here, we can use the ‘Between’ operator with a where clause.

6. Write an SQL query to fetch those employees who live in Toronto and work under the manager with ManagerId – 321. Since we have to satisfy both the conditions – employees living in ‘Toronto’ and working in Project ‘P2’. So, we will use the AND operator here-

7. Write an SQL query to f etch all the employees who either live in California or work under a manager with ManagerId – 321. This interview question requires us to satisfy either of the conditions – employees living in ‘California’ and working under Manager with ManagerId – 321. So, we will use the OR operator here-

8. Write an SQL query to fetch all those employees who work on Projects other than P1. Here, we can use the NOT operator to fetch the rows which are not satisfying the given condition.

Or using the ‘not equal to’ operator-

For the difference between NOT and <> SQL operators, check this link – Difference between the NOT and != operators .

9. Write an SQL query to display the total salary of each employee adding the Salary with Variable value. Here, we can simply use the ‘+’ operator in SQL.

10. Write an SQL query to fetch the employees whose name begins with any two characters, followed by a text “hn” and ends with any sequence of characters. For this question, we can create an SQL query using like operator with ‘_’ and ‘%’ wild card characters, where ‘_’ matches a single character and ‘%’ matches ‘0 or multiple characters.

11. Write an SQL query to fetch all the EmpIds which are present in either of the tables – ‘EmployeeDetails’ and ‘EmployeeSalary’. In order to get unique employee ids from both tables, we can use the Union clause which can combine the results of the two SQL queries and return unique rows.

12. Write an SQL query to fetch common records between two tables. SQL Server – Using INTERSECT operator-

MySQL – Since MySQL doesn’t have INTERSECT operator so we can use the subquery-

13. Write an SQL query to fetch records that are present in one table but not in another table. SQL Server – Using MINUS- operator-

MySQL – Since MySQL doesn’t have a MINUS operator so we can use LEFT join-

14. Write an SQL query to fetch the EmpIds that are present in both the tables – ‘EmployeeDetails’ and ‘EmployeeSalary. Using subquery-

15. Write an SQL query to fetch the EmpIds that are present in EmployeeDetails but not in EmployeeSalary. Using subquery-

16. Write an SQL query to fetch the employee’s full names and replace the space with ‘-’. Using the ‘Replace’ function-

17. Write an SQL query to fetch the position of a given character(s) in a field. Using the ‘Instr’ function-

18. Write an SQL query to display both the EmpId and ManagerId together. Here we can use the CONCAT command.

19. Write a query to fetch only the first name(string before space) from the FullName column of the EmployeeDetails table. In this question, we are required to first fetch the location of the space character in the FullName field and then extract the first name out of the FullName field. For finding the location we will use the LOCATE method in MySQL and CHARINDEX in SQL SERVER and for fetching the string before space, we will use the SUBSTRING OR MID method. MySQL – using MID

SQL Server – using SUBSTRING

20. Write an SQL query to uppercase the name of the employee and lowercase the city values. We can use SQL Upper and Lower functions to achieve the intended results.

21. Write an SQL query to find the count of the total occurrences of a particular character – ‘n’ in the FullName field. Here, we can use the ‘Length’ function. We can subtract the total length of the FullName field from the length of the FullName after replacing the character – ‘n’.

22. Write an SQL query to update the employee names by removing leading and trailing spaces. Using the ‘Update’ command with the ‘LTRIM’ and ‘RTRIM’ functions.

23. Fetch all the employees who are not working on any project. This is one of the very basic interview questions in which the interviewer wants to see if the person knows about the commonly used – Is NULL operator.

24. Write an SQL query to fetch employee names having a salary greater than or equal to 5000 and less than or equal to 10000. Here, we will use BETWEEN in the ‘where’ clause to return the EmpId of the employees with salary satisfying the required criteria and then use it as a subquery to find the fullName of the employee from the EmployeeDetails table.

25. Write an SQL query to find the current date-time. MySQL-

SQL Server-

26. Write an SQL query to fetch all the Employee details from the EmployeeDetails table who joined in the Year 2020. Using BETWEEN for the date range ’01-01-2020′ AND ’31-12-2020′-

Also, we can extract the year part from the joining date (using YEAR in MySQL)-

27. Write an SQL query to fetch all employee records from the EmployeeDetails table who have a salary record in the EmployeeSalary table. Using ‘Exists’-

28. Write an SQL query to fetch the project-wise count of employees sorted by project’s count in descending order. The query has two requirements – first to fetch the project-wise count and then to sort the result by that count. For project-wise count, we will be using the GROUP BY clause and for sorting, we will use the ORDER BY clause on the alias of the project count.

29. Write a query to fetch employee names and salary records. Display the employee details even if the salary record is not present for the employee. This is again one of the very common interview questions in which the interviewer just wants to check the basic knowledge of SQL JOINS. Here, we can use the left join with the EmployeeDetail table on the left side of the EmployeeSalary table.

30. Write an SQL query to join 3 tables. Considering 3 tables TableA, TableB, and TableC, we can use 2 joins clauses like below-

For more questions on SQL Joins, you can also check our top SQL Joins Interview Questions .

SQL Query Interview Questions for Experienced

Here is a list of some of the most frequently asked SQL query interview questions for experienced professionals. These questions cover SQL queries on advanced SQL JOIN concepts, fetching duplicate rows, odd and even rows, nth highest salary, etc.

31. Write an SQL query to fetch all the Employees who are also managers from the EmployeeDetails table. Here, we have to use Self-Join as the requirement wants us to analyze the EmployeeDetails table as two tables. We will use different aliases ‘E’ and ‘M’ for the same EmployeeDetails table.

32. Write an SQL query to fetch duplicate records from EmployeeDetails (without considering the primary key – EmpId). In order to find duplicate records from the table, we can use GROUP BY on all the fields and then use the HAVING clause to return only those fields whose count is greater than 1 i.e. the rows having duplicate records.

33. Write an SQL query to remove duplicates from a table without using a temporary table. Here, we can use delete with alias and inner join. We will check for the equality of all the matching records and then remove the row with a higher EmpId.

34. Write an SQL query to fetch only odd rows from the table. In case we have an auto-increment field e.g. EmpId then we can simply use the below query-

In case we don’t have such a field then we can use the below queries. Using Row_number in SQL server and checking that the remainder when divided by 2 is 1-

Using a user-defined variable in MySQL-

35. Write an SQL query to fetch only even rows from the table. In case we have an auto-increment field e.g. EmpId then we can simply use the below query-

In case we don’t have such a field then we can use the below queries. Using Row_number in SQL server and checking that the remainder, when divided by 2, is 1-

36. Write an SQL query to create a new table with data and structure copied from another table .

37. Write an SQL query to create an empty table with the same structure as some other table. Here, we can use the same query as above with the False ‘WHERE’ condition-

38. Write an SQL query to fetch top n records. In MySQL using LIMIT-

In SQL server using TOP command-

39. Write an SQL query to find the nth highest salary from a table. Using Top keyword (SQL Server)-

Using limit clause(MySQL)-

40. Write SQL query to find the 3rd highest salary from a table without using the TOP/limit keyword. This is one of the most commonly asked interview questions. For this, we will use a correlated subquery. In order to find the 3rd highest salary, we will find the salary value until the inner query returns a count of 2 rows having a salary greater than other distinct salaries.

For the nth highest salary-

Scenario-based SQL Query Interview Questions

Let’s see some interview questions based on different scenarios. The questions are of varying difficulty levels and the goal is to prepare you for different real-time scenario-based questions.

41. Consider a SalesData with columns SaleID , ProductID , RegionID , SaleAmount . Write a query to find the total sales amount for each product in each region. The below query sums up SaleAmount for each combination of ProductID and RegionID , giving an insight into the total sales per product per region.

42. Write a query to find employees who earn more than their managers. Here, we will write a query that joins the EmployeeDetails table with itself to compare the salaries of employees with their respective managers.

43. Consider a BookCheckout table with columns – CheckoutID , MemberID , BookID , CheckoutDate , ReturnDate . Write an SQL query to find the number of books checked out by each member.

44. Consider a StudentGrades table with columns – StudentID , CourseID , Grade . Write a query to find students who have scored an ‘A’ in more than three courses. Here we will write an SQL query that filters students who have received an ‘A’ grade and groups them by StudentID , counting the number of ‘A’ grades per student.

45. Consider a table OrderDetails with columns – OrderID , CustomerID , ProductID , OrderDate , Quantity , Price . Write a query to find the average order value for each customer. The below query calculates the average order value (quantity multiplied by price) for each customer.

46. Consider a table PatientVisits with Columns VisitID , PatientID , DoctorID , VisitDate , Diagnosis . Write a query to find the latest visit date for each patient.

47. For a table FlightBookings with columns – BookingID , FlightID , PassengerID , BookingDate , TravelDate , Class , write a query to count the number of bookings for each flight class. Here, we will write an SQL query that groups the bookings by Class and counts the number of bookings in each class.

48. Consider a table FoodOrders with columns – OrderID , TableID , MenuItemID , OrderTime , Quantity . Write a query to find the most ordered menu item. For the desired output, we will group the orders by MenuItemID and then sort the results by the count in descending order, fetching the top result.

49. Consider a table Transactions with columns – TransactionID , CustomerID , ProductID , TransactionDate , Amount . Write a query to find the total transaction amount for each month. The below query sums the Amount for each month, giving a monthly total transaction amount.

50. Consider a table EmployeeAttendance with columns – AttendanceID , EmployeeID , Date , Status . Write a query to find employees with more than 5 absences in a month. This query filters the records for absent status, groups them by EmployeeID and month, and counts absences, filtering for more than 5 absences.

This concludes our post on frequently asked SQL query interview questions and answers . I hope you practice these questions and ace your database interviews. If you feel, we have missed any of the common interview questions on SQL then do let us know in the comments and we will add those questions to our list.

Do check our article on – RDBMS Interview Questions , focussing on the theoretical interview questions based on the DBMS and SQL concepts.

JMeter Interview Questions

Top rdbms interview questions | dbms viva questions, 70 thoughts on “top 50 sql query interview questions and answers for practice”.

Thank You so much, these queries are very useful.

select max(salary) as maxsalary from emp where salary ( select max(salary) as maxsalary from emp where salary( select max(salary) as maxsalary from emp))

you must have to give the condition in every where clause for getting result

Thanks for the queries.

Hi Geet, thanks for pointing out, please let us know, what you find wrong in this query.

can I write the query for 6th question like

Select FullName from EmployeeDeatils where ManagerId is not null;

Because if anyone has ManagerId then he must be a manager right.so need to join??

Hi Ramesh, the ManagerId field in the EmployeeDetails table refers to the Manager of that Employee. So, your query will return only the Employees with Manager assigned.

how to fetch last second row records ???? and anyone sugest me the most asking sql queries in mnc

SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Table1 ORDER BY RowID DESC) X ORDER BY RowID

Oracle 12c and above:

select * from DEPARTMENTS offset (select count(*) -2 from departments) rows fetch next 1 rows only;

In Oracle SQL server:- Select b.*,nth_value(id,2) over(order by Id desc range between unbounded preceding and unbounded following) from your_table b;

with cte as ( select *,ROW_NUMBER() over(order by id desc) as rownum

from Employee1 ) select * from cte where rownum =2

select * from Employee

Movie table Creat table ” movie ” { “id” int(110 not null default ‘0’, “name” varchar(100)default null, “year” int(11) default null, ” rank” flaot default null, primary key (” id”)

Considering the above table how many movies does the dataset have for the year 1982 ! write ur query below

please help me to solve the query ….

SELECT COUNT(*) FROM movie WHERE year = 1982

select * from table order by ID(primary key) desc limit 1,1;

In Question no. 39 Put N=2 and ORDER BY asc You will get second lowest salary

select * from table name order by column name offset (select count(*) from table name)-1 rows fetch next 1 rows only

with ctek as ( select*row_number() over(order by id desc) as rowno from emp )

select*from ctek

where rowno=2

select*from emp

select * from EmployeeDeatils order by EMPID DESC LIMIT 1,1;

Thanks a lot for sharing these SQL queries. I have my interview tomorrow, these questions will really help.

Appreciate your feedback Amit :-). Best of luck with your interview.

Hi Kuldeep, Appreciate your efforts…

In Que.12 and Que. 13 , you have unconsciously written table name ‘ManagerSalary’ instead of EmployeeDetails.

Rest it is good blend of list of questions.

Hi Mithilesh,

Thanks. Actually, I have intentionally used a new table ManagerSalary. Since I have used ‘*’ in the queries which would require a similar table structure. Hence I introduced a new table – ManagerSalary, assuming the table to have a similar structure like that of EmployeeSalary.

This is extremely confusing and doesn’t make much sense. Some language about creating a new table would have been helpful. I kept searching this page for a ManagerSalary table.

This confused me too

This is really good for beginners.

Hi Kuldeep,

Great and fun article! A word of warning about the first answer to question 26. Not sure about SQL Server, but definitely in Oracle using BETWEEN with dates could get you in trouble. In “BETWEEN ‘2020/01/01’AND ‘2020/12/31′”, those dates are interpreted with a timestamp of 00:00. So with the latter date, it’ll only retrieve those hired until 2020/12/30 (or 2020/12/31 at exactly 00:00), and it would miss anyone hired after 00:00 on 2020/12/31 (so a person hired at 09:00 on 12/31 would be missed. Plus there are lots of additional complexities with doing something like BETWEEN ‘2020/01/01’AND ‘2020/12/31 23:59:59’. So in my experience, to get everyone hired in 2020, you’re better off using: AND dateOfJoining >= ‘2020/01/01’ AND dateOfJoining < '2021/01/01' …or just use the extract function like in your second answer 🙂

Thanks a lot, Phil.

Thanks for it

getting records of one managerid under their employeeid details alone have to come like their group memeber alone

In the following query can you please explain this line

WHERE Emp2.Salary > Emp1.Salary

SELECT Salary FROM EmployeeSalary Emp1 WHERE 2 = ( SELECT COUNT( DISTINCT ( Emp2.Salary ) ) FROM EmployeeSalary Emp2 WHERE Emp2.Salary > Emp1.Salary )

This is a use of Correlated Subquery. First, You are interested in fetching the “salary” from the EmployeeSalary Table or Emp1; You give the condition or criteria in WHERE clause.

The condition returns a value, where the total count of unique observations from Table EmployeeSalary. You are conditioning with respect to the same table.

For nth highest salary- SQL Server

SELECT Salary FROM EmployeeSalary Emp1 WHERE N-1 = ( SELECT COUNT( DISTINCT ( Emp2.Salary ) ) FROM EmployeeSalary Emp2 WHERE Emp2.Salary > Emp1.Salary )

Getting Error : Invalid column name ‘n’. Please tell me Some One ?

You don’t have to directly use ‘n’. You need to replace ‘n’ with a number e.g. if you want to find the 3rd highest salary, n would be 3.

This is be a more simple solution.

For nth highest salary,

Select Salary From EmployeeSalary Order By `Salary` Desc limit n-1,1;

For example, if we need 3rd highest salary, query will be

Select Salary From EmployeeSalary Order By `Salary` Desc limit 2,1;

But by this query, list will be printed from 3rd to minimum. Right ?

select max Salary from (select distinct Salary from EmployeeSalary order by Salary desc) where rownum < n+1;

thanks for sharing

Hi Kuldeep, Really useful and on point article, Great help at interviews. Thanks👍🏻

Hi, Can you please provide me the Create query for the above table and also provide me the create a query of ManagerSalary with insert data.

Hi kuldeep, Thanks for the effort you put in creating this blog. It’s very nice.

I just want to add one more sql query problem which i was asked in my Oracle Interview.

Suppose there is a table with 3 attr : city 1 city2 Distance Hyd. goa. 500 goa. Hyd. 500

These tuples represent the same information , so write an SQL query to remove these type of duplicates.

Ans : delete from t where (city1,city2) in ((select t1.city1,t1.city2 from t t1 where exists(select t2.city2 from t t2 where t2.city2=t1.city1) and exists(select t2.city1 from t t2 where t2.city1=t1.city2)) minus (select t1.city1,t1.city2 from t t1 where exists(select t2.city2 from t t2 where t2.city2=t1.city1) and exists(select t2.city1 from t t2 where t2.city1=t1.city2) fetch first 1 rows only));

Thanks a lot, Rahul. These types of questions will definitely help other readers. Keep contributing :-).

Cant we solve this using self join?

Delete E1 from E1.tab Join E2.tab where E1.city1 = E2.city2 and E1.city2 = E2.city 1 and E1.cost = E2.cost;

DELETE FROM distance t1 WHERE EXISTS ( SELECT 1 FROM distance t2 WHERE t1.city1= t2.city2 AND t1.city2 = t2.city1 AND t1.distance = t2.distance AND t1.rowid > t2.rowid );

Display list of employee having first name David or Diana without using like, in and or operater.please answer

Thanks kuldeep for such a good article and sharing valuable sql questions and answers which will help a lot for interview preparation.Hats off you.

How to fetch emplid with full name second letter is o

select Fullname from employeedetails where Fullname like’_o%’;

select empid, fullname where fullname like ‘_0__%’;

Select emplid, fullname from employee where fullname like ‘_o%’;

hi kuldeep sir, In my pc SQL does not support TOP keyword but it support LIMIT keyword. rest of the queries is nicely understandable.

I am a beginner in SQL and I was asked the below questions in one of my interviews, can anyone help me with the below.

Question1:- transaction table has 5 columns (transaction_id, customer_id, transation_date, product_id, transaction_amount)

write query to fetch 10 transaction made in last month by 1 customer

Question2:- transaction table has 5 columns (transaction_id, customer_id, transation_date, product_id, transaction_amount) product table has 2 columns (product_id, product_name)

write query to list all the product which are never sold write query to list all the product which are most sold

Question3:- transaction table has 5 columns (transaction_id, customer_id, transation_date, product_id, transaction_amount) product table has 2 columns (product_id, product_name)

Write query to fetch customer id, first transaction date, last transaction date, the difference in transaction amount, and difference in transaction date

1. SELECT * FROM ( SELECT transaction_id, customer_id, transaction_date, product_id, transaction_amount, ROW_NUMBER() OVER (ORDER BY transaction_date DESC) AS rn FROM transaction WHERE customer_id = 1 –(let 1) and transaction_date >= TRUNC(SYSDATE, ‘MM’) – 30 ) WHERE rn <= 10 order by transaction_id,customer_id; 2. SELECT p.product_id, p.product_name, total_amount FROM products p JOIN ( SELECT product_id, SUM(transaction_amount) AS total_amount FROM transaction GROUP BY product_id ORDER BY total_amount DESC ) t ON p.product_id = t.product_id WHERE ROWNUM = 1; 3. SELECT p.product_id, p.product_name FROM products p LEFT OUTER JOIN transaction t ON p.product_id = t.product_id WHERE t.transaction_id IS NULL; 4. SELECT t.customer_id, MIN(t.transaction_date) AS first_transaction_date, MAX(t.transaction_date) AS last_transaction_date, MAX(t.transaction_amount) – MIN(t.transaction_amount) AS diff_amount, MAX(t.transaction_date) – MIN(t.transaction_date) AS diff_date FROM transaction t GROUP BY t.customer_id;

Very helpful queries. 🙂

Really helpful article. Thank you

great art of testing. thanks for all the effort.

Thank you for sharing your knowledge with us.

select * from table_name where first_name=’David’ or first_name=’Diana’;

In the 31st question, can I write the query as SELECT DISTINCT(Fullname) FROM Employeedetails WHERE EmpID IN ( SELECT DISTINCT(ManagerID) FROM Employeedetails);

SELECT first_name FROM employees WHERE SUBSTR(first_name, 1, 5) = ‘David’ UNION SELECT first_name FROM employees WHERE SUBSTR(first_name, 1, 5) = ‘Diana’;

Hi, I have asked a question in My interview I have an order table with the following columns orderID,customerID,orderDate, sales amount. Please write a query to show total sales amount by order date? For these question we can use joins right?

select sales,orderdate,sum(sales) as total sales from order group by orderdate,sales

select FullName from EmployeeDetails where ManagerId is not null

SELECT first_name FROM employees WHERE SUBSTR(first_name, 1, 5) = ‘David’ UNION SELECT first_name FROM employees WHERE SUBSTR(first_name, 1, 5) = ‘Diana’;

Write a query to display details of employs with the text “Not given”, if commission is null?

What will be the ms sql query to get following output in a table? OUTPUT TABLE ITEM | TOTAL_COST VEGETABLES | 8 FRUITS | 12

SELECT ITEMS, SUM(TOTAL_COST) FROM ( SELECT CASE WHEN Item IN (‘APPLE’,’ORANGE’) then ‘FRUITS’ WHEN Item IN (‘POTATO’,’TOMATO’) then ‘VEGITABLES’ END ITEMS, CASE WHEN Item IN (‘APPLE’,’ORANGE’) then sum (cost) WHEN Item IN (‘POTATO’,’TOMATO’) then sum (cost) END TOTAL_COST FROM gross_market GROUP by Item ) A GROUP BY items

SQL Interview Questions for Data Analysts

jobs and career
sql interview questions

You have an upcoming interview for a data analyst position and SQL is one of the skills that will be tested. Well, these SQL questions for data analysts are specifically tailored for you!

SQL is a must-have skill in every data analyst's toolkit . It simplifies data extraction and manipulation, allowing analysts to convert raw data into actionable insights. It enables analysts to perform ad hoc analyses, accelerating organizational decision-making processes. It also encourages collaboration between data analysts and database administrators, resulting in smoother data operations.

If you're not sure where to begin your SQL journey, look no further than our SQL for Data Analysis course. This comprehensive course covers all aspects of data analysis, including data retrieval and report generation.

However, you’re probably here to prepare for your data analyst interview. So, without further ado, let’s jump right in.

How to Prepare for a Data Analyst Interview

Setting yourself up for a successful data analyst interview requires a strategic combination of technical expertise and focused preparation. Here are some practical pointers and suggestions to help you stand out:

Review fundamental SQL concepts. Ensure you have a thorough understanding of fundamental SQL concepts – i.e. querying, filtering, joining, and aggregating data. Consider enrolling in our SQL Basics course , which is designed to reinforce your knowledge of core SQL.
Learn standard SQL functions. Improve your understanding of standard SQL functions, including numeric, text, and date/time functions. Learn how to effectively handle NULL values and use SQL functions to write more effective queries.
Practice SQL reporting. Begin with basic reports using the GROUP BY , ORDER BY , and HAVING Then you can progress to intermediate and advanced reports that use subqueries, window functions, and common table expressions (CTEs).
Take a skill assessment. Consider taking an SQL assessment to evaluate your level of proficiency. Our SQL Skills Assessment includes 18 interactive online tasks that cover six SQL competency areas.
Utilize learning resources. Use online courses and tutorials to improve your SQL. Our SQL courses include comprehensive learning materials and hands-on exercises designed to help you improve your data analysis and SQL queries. Alternatively, if you prefer books, check out our curated list of the Best SQL Books for Data Analysis .

Basic SQL Interview Questions

Now that we've discussed the importance of learning SQL for data analysis, let's look at some actual SQL interview questions tailored to different skill levels – basic, intermediate, and advanced. Keep our SQL for Data Analysis Cheat Sheet handy to help you with the challenges ahead.

Question 1: What is SQL?

SQL (Structured Query Language) is a standard programming language for managing and communicating with relational databases. It enables users to query data; insert, update, and delete records; create and modify database schemas, and manage access permissions.

Question 2: List the basic elements of an SQL query.

An SQL query typically contains a:

SELECT statement that retrieves data from one or more tables.
FROM clause that specifies the table(s) where the data resides.
WHERE clause (optional) that filters results based on specified criteria.
GROUP BY clause (optional) that groups results by column(s).
HAVING clause (optional) that filters grouped results.
ORDER BY clause (optional) that sorts the results in ascending (or descending) order.

Question 3: How do you select all the data from a table?

To retrieve all the data from a table, use:

This query fetches all rows of all columns from the employees table:

SELECT * : Selects all columns from the specified table; the asterisk (*) represents all columns in a table.
FROM employees : Specifies the table from which data will be retrieved.

Question 4: How do you select certain columns from a table?

To retrieve specific columns from a table, you must list the desired column names in the SELECT statement:

This query retrieves data only from the specified columns, providing a focused view of relevant information from the employees table.

SELECT first_name, last_name, department : This returns only the data in the first_name , last_name , and department

We can also name selected columns using AS :

In the query results, the last_name column will be shown as surname. You can use this to differentiate between two columns with similar names or to add more context to a column. For example, renaming the number column to part_id_number allows readers to quickly identify what kind of data is stored in that column.

Question 5: Explain the syntax of the WHERE clause.

The WHERE clause filters rows to meet specific conditions. Here is an example of its syntax:

In this query, WHERE limits the results to data for employees in the Sales department.

SELECT first_name, last_name : Specifies which columns to retrieve data from.
WHERE department = 'Sales' : Limits the results to the employees in the Sales department.

WHERE can be applied to different data types. When filtering by numbers or dates, you can use direct comparisons or operators like < , <= , > , >= , or = .

When filtering NULL values, the IS NULL or IS NOT NULL operators can be used:

Question 6: How do you filter query results based on multiple conditions?

To filter results based on multiple conditions, logical operators such as AND , OR , and NOT are used in the WHERE clause.

The AND operator returns rows that meet all of the specified conditions.
The OR operator returns rows that meet at least one of the specified conditions.
The NOT operator returns rows that do not meet the condition(s).

Consider the following example:

This query will only return employees who work in sales and whose salary is greater than $50,000. If the employee works in any other department or works in sales and earns less than $50,000, they won’t be shown in the results.

SELECT first_name, last_name : Determines which columns to retrieve data from.
WHERE department = 'Sales' AND salary > 50000 : Filters the results to include only employees from the Sales department with a higher salary.

You can combine multiple conditions using parentheses:

In this query, only employees who don’t report to a manager (i.e. whose manager ID is NULL) or who work in sales and earn $50k+ will be included in the results.

Question 7: How do you filter results for a given text value?

To filter results based on a specific value in a text column, use the LIKE operator . You’ll also need the pattern matching symbols % (matches zero or more characters) and/or _ (matches any single character). Consider the following example:

This query demonstrates using LIKE with pattern matching:

SELECT product_name, description : specifies which columns to retrieve data from.
FROM products : Specifies the table from which data will be retrieved.
WHERE description IS LIKE '%apple%' : Filters the results to only show products that have the word " apple " in their description.

Another example could be:

Imagine you are looking for all employees named María or Maria. This query will return all employees whose first names begin with 'Mar', followed by any character in place of 'i', then an 'a'. This pattern can be used to match both 'Maria' and 'María' in a database where the name may not have the accent on the 'i'. (It would also return ‘Marta’, ‘Marla’, ‘Marja’, and any other names that fit the pattern.)

Question 8: How do you update rows in a database?

To update existing rows in a database table, use the UPDATE statement:

This query demonstrates how to update existing columns with new values for rows that satisfy the specified condition.

UPDATE employees : Specifies which table will be updated.
SET salary = 60000, department = 'Marketing' : Specifies the columns and their new (updated) values.
WHERE employee_id = 101 : Defines which rows will be updated – in this case, the row where the employee ID is 101. You can also use AND , OR , NOT , number filtering, etc. to specify more complex update conditions.

Question 9: How do you remove rows from a database?

To remove rows from a database table, use the DELETE FROM statement, followed by the table name and the condition indicating which rows to delete:

This query shows how to delete rows from a database table based on a specified condition:

DELETE FROM employees : Indicates the table from which rows will be deleted.
WHERE department = 'Human Resources' : Specifies the condition that determines which rows should be deleted – in this case, all employees in the Human Resources department. You can specify deletion conditions using standard WHERE

Question 10: How do you sort results from a SQL query?

Use the ORDER BY clause, followed by the column name(s) by which you want to sort the results:

This query demonstrates how to sort results based on a specified column, allowing data to be presented in the desired order.

SELECT product_name, price : Determines which columns to retrieve data from.
FROM products : Defines the table where this data resides.
ORDER BY price DESC : Sorts the results (from highest to lowest, aka descending order) by the price SQL’s default sort order is ascending, with text values sorted from A to Z and numerical values sorted from lowest to highest. If DESC is not specified, the sorting will be done in ascending order.

Intermediate SQL Questions

Question 11: how do you combine data from multiple tables in one query.

SQL queries use JOIN clauses to combine results from multiple tables. JOIN s allow you to retrieve data from related tables (i.e. tables with one or more columns in common) based on specific criteria. Here’s an example of JOIN :

This query retrieves the order ID and customer name from the orders and customers tables. It joins the information where records in both tables contain a matching customer ID. For more information on how JOIN s work, see An Illustrated Guide to the SQL INNER JOIN . You can find more examples of JOIN interview questions in The Top 10 SQL JOIN Interview Questions and How to Answer Them .

Question 12: Describe the JOIN types.

JOIN types include several variations, each serving a specific purpose in combining results from multiple tables:

INNER JOIN : Retrieves only records where both tables have matching values based on the specified condition.
LEFT JOIN : Returns all records from the left table and any matching records from the right table. If no match is found, NULL values are returned for the right table’s columns.
RIGHT JOIN : Returns all records from the right table and any matching records from the left table. If no match is found, NULL values are returned for the left table's columns.
FULL JOIN : Returns all records when there is a match in either the left or right tables. If no match is found, the non-matching rows are returned with NULL s.
CROSS JOIN : Returns the Cartesian product of the two tables – i.e. all possible combinations of rows.

Although a self-JOIN uses the same syntax as other JOIN s, it actually involves joining a table to itself. This allows for comparisons between rows within the same table.

Question 13: Describe LEFT JOIN and the difference between LEFT JOIN and INNER JOIN.

LEFT JOIN is a type of OUTER JOIN that returns all rows from the left table plus any matching rows from the right table. If there is no match, NULL values are returned for the columns in the right table.

The main distinction between LEFT JOIN and INNER JOIN is their behavior with unmatched rows:

LEFT JOIN returns all rows from the left table, regardless of whether a match exists in the right table. If there is no match in the right table, NULL values are assigned to those columns.
INNER JOIN returns only matching rows from both tables. Unmatched rows from either table are removed from the results set.

Imagine you have a table of students’ names and another containing their grades. Using a LEFT JOIN , you'd get a list of all students – including those who have NULL grades. In contrast, an INNER JOIN would only show students who have grades, excluding records with NULL grades.

Question 14: What does GROUP BY do?

The GROUP BY clause groups rows with the same values into summary rows. It is frequently combined with aggregate functions (such as SUM() , COUNT() , and AVG() ) to perform calculations on grouped data.

In this example, the query groups the rows based on the values in the department column and counts the employee_id values for each department. For more examples of similar queries, see our article Top 9 SQL GROUP BY Interview Questions .

Question 15: What are aggregate functions?

SQL aggregate functions calculate a set of values and return a single value. These functions are frequently used to summarize data and perform calculations on multiple rows.

The most common SQL aggregate functions are:

SUM() : Returns the sum of the numeric values in a set.
COUNT() : Counts the number of rows in a set, optionally including non-null values.
AVG() : Determines the average of numeric values in a set.
MIN() : Returns the set's minimum value.
MAX() : Returns the set's maximum value.

In this example, aggregate functions compute the average salary, count the total number of employees, and determine the greatest age among all employees in the employees table. Check out our SQL Aggregate Functions Cheat Sheet for more examples of these functions.

Question 16: What does HAVING do?

The HAVING clause filters the results of the GROUP BY clause. It is only used with aggregated data, allowing you to apply filtering criteria to grouped results.

Imagine you have a table of orders and you want to find the total sales amount for each customer. However, you only want to include customers with a total of $1,000 or more. This is the query you’d use:

Here, the HAVING clause filters the groups (customers) based on the condition that the customer’s total sales amount exceeds $1,000. It is applied following the GROUP BY operation and allows you to filter the grouped results.

Question 17: What is the difference between WHERE and HAVING?

HAVING filters groups of rows and is applied after the GROUP BY statement. It is intended specifically for aggregated data and allows you to apply filtering criteria to grouped results. WHERE filters individual rows and is applied before GROUP BY .

Imagine you have a table of orders and want to calculate the total sales amount for each product, but only for products whose total sales exceed $10,000. Here's how you’d use the HAVING clause:

Now consider the same scenario, but this time you only want need sales where the product’s price per unit is greater than $10 and the total sale is over $10,000. Some of this information is found in individual rows, so the rows must be filtered before grouping. We also have to filter for total sales, so the groups will be filtered too.

This demonstrates how the WHERE clause filters individual rows based on conditions, whereas the HAVING clause filters groups of rows based on aggregated data. For a deeper dive into this subject, check out our article HAVING vs. WHERE in SQL: What You Should Know .

Question 18: Give an example of an SQL subquery.

A SQL subquery , also known as a nested query or inner query, is a query contained within another query. It is used in SELECT , INSERT , UPDATE , and DELETE statements to execute operations based on the results of another query. Consider the following example:

In this query, the WHERE clause uses a subquery to retrieve category_id values from the categories table with the category_name 'Electronics' . The outer query then retrieves product names for electronics products based on the subquery results.

Advanced SQL Questions

Question 19: what is the difference between union and union all .

UNION removes duplicate rows from the combined result set. In contrast, UNION ALL keeps all rows, including duplicates.

Let's imagine we have two tables, employees and managers , both of which contain the column employee_id . We want to combine the employee IDs from the two tables.

In this query, if employee_id appears in both employees and managers , UNION will remove the duplicate, but UNION ALL will keep both occurrences. You can read more about this in UNION vs. UNION ALL in SQL: What’s the Difference?

Question 20: What are SQL window functions and what are they used for?

SQL window functions perform calculations on a subset of table rows that are related to the current row. They are used on a window , which is a collection of rows related to the current row.

Window functions differ from GROUP BY queries because GROUP BY aggregates data based on specific columns, collapsing multiple rows into summary rows . In contrast, window functions perform calculations across a set of rows related to the current row within the query result, without collapsing the result set . You can read more in SQL Window Functions vs. GROUP BY: What’s the Difference?

To apply window functions, the OVER clause is used with the following syntax:

<function> indicates the window function being used (e.g., AVG() , SUM() , RANK() ).
(column) : The column or expression to which the window function is applied.
PARTITION BY partition_column : An optional clause that splits the result set into subsets based on one or more columns. The function is then applied to each partition individually.
ORDER BY order_column : An optional clause specifying the order of rows within each partition. This determines which rows appear in the window frame for each calculation.

Window functions are frequently used to compute aggregate values such as averages, totals, and counts over a set of rows. They are also used to compare values to other rows in the result set; this aids in determining rankings, row numberings, and maximum and minimum values within a window. To find out more, read When Do I Use SQL Window Functions?

Imagine we have a table called employees with the columns employee_id , department_id , and salary . We can calculate the average salary per department using the AVG() window function:

SQL window functions are great for calculating aggregates over specific subsets of data – as shown in this example of calculating average salaries within departments. They are invaluable for data ranking, which can be useful in tasks like performance evaluation. These functions make it easier to perform analytical comparisons and identify trends, such as in quarterly revenue. We'll look at more examples in the following questions.

Question 21: How do you rank rows in SQL?

To rank rows in SQL , use the window functions RANK(), DENSE_RANK(), PERCENT_RANK() and ROW_NUMBER() . These functions assign a rank or row number to each row in the result set based on predefined criteria:

RANK() : Assigns a unique rank to each distinct row. Rows with the same value receive the same rank, but there are gaps in the ranking (e.g. 1,2,3,3,5).
DENSE_RANK() : Also assigns a unique rank to each row and gives tied rows the same rank, but it eliminates gaps in the ranking sequence. Instead, it assigns the next consecutive rank to the row following the tied rows (e.g. 1,2,3,3,4).
PERCENT_RANK() returns the ranking for each row in the partition/result set as a percentile (from 0 to 1).
ROW_NUMBER() assigns a unique row number to each row in the result set, regardless of duplicates or tie values.

Read our Overview of Ranking Functions in SQL for more differences between these functions.

Imagine we have a table called employees with the columns employee_id , employee_name , and salary . We can rank the employees based on their salary in descending order using the RANK() window function:

This query returns employee IDs, names, and salaries from the employees table. It then ranks each employee according to their salary, with higher salaries receiving lower ranks. The result includes the original columns as well as a new column, salary_rank , which indicates each employee's salary rank.

Question 22: What is a CTE? When do you use it?

A CTE , or common table expression, is a temporary named result set that can be used in a SELECT , INSERT , UPDATE , or DELETE statement. It allows you to divide complex queries into manageable and reusable chunks.

A CTE, introduced with the WITH keyword , allows you to create a temporary table-like structure that exists only during query execution. The temporary table can then be referred to in the same query. CTEs are especially useful for breaking down complex computations into multiple steps; this improves code organization and enables code reuse.

Assume we have a table called employees with the columns employee_id , employee_name , and salary . We can create a CTE to select high-paid employees who earn more than $100,000:

Question 23: What is a running total and how do you compute it in SQL?

A running total , also known as a cumulative sum, is the sum of a value over a set of rows in a particular order. In SQL, a running total can be calculated using SUM() and the OVER() clause.

The following query computes the running total of order_amount over all rows in the orders table sorted by order_date :

Question 24: What is a moving average and how do you compute it in SQL?

A moving average represents the average of a set of values over a set of times, where the window " moves " along the data set. In SQL, you can compute a moving average using AVG() with OVER() and specifying a window frame.

The following query calculates the moving average of value for the current row and the two preceding rows, ordered by date.

Question 25: How do you compute the year-to-year difference in SQL?

To calculate the year-to-year difference in SQL, use functions like LAG() to compare values between consecutive rows.

The following query calculates the year-to-year difference in revenue by subtracting the current year's revenue from the previous year's revenue: