sql problem solving questions

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

Revising the select query ii easy sql (basic) max score: 10 success rate: 98.69%, 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.52%, 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: 98.04%, 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.44%, cookie support is required to access hackerrank.

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

50 SQL Query Practice Questions for Interview

Hello friends, we’ve brought you 50 frequently asked SQL query interview questions and answers for practice. Solving practice questions is the fastest way to learn any subject. That’s why we’ve selected these 50 SQL queries ⤵ to give you enough exercises for practice. You can now run these SQL exercises inline and get a feel of live execution.

If it is your first time here, you should start by running the readymade SQL scripts to create the test data . These scripts include a sample Worker table, a Bonus, and a Title table with pre-filled data. Just run the SQL scripts to set everything you need to practice with the SQL queries. By the end of this assignment, you will feel more confident to face SQL interviews at top IT MNCs like Amazon, Flipkart, Facebook, etc.

SQL Query Questions and Answers for Practice

We recommend you go through the questions and build queries by yourself. Try to find answers on your own. However, you need to set up the sample tables and test data. We have provided simple SQL scripts to seed the test data. Use those first to create the test database and tables.

By the way, our site has more SQL queries available for interview preparation. So if you are interested, then follow the link given below.

• Most Frequently Asked SQL Interview Questions

Prepare Sample Data To Practice SQL Skills

Sample table – worker.

Sample Table – Bonus

Sample Table – Title

To prepare the sample data, run the following queries in your database query executor or 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, you may use them.

SQL Script to Seed Sample Data.

Running the above SQL on any MySQL instance will show a result similar to the one below.

Start with 20 Basic SQL Questions for Practice

Below are some of the most commonly asked SQL query questions and answers for practice. Get a timer to track your progress and start practicing.

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., 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., 12 medium sql query interview questions / answers for practice.

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-21. Write an SQL query to fetch the count of employees working in the department ‘Admin’.

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:

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:

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

Specify the SQL query in the below code box:

18 Complex SQL Queries for Practice

Now, that you have built a solid foundation in intermediate SQL, It’s time to practice with some advanced SQL query questions with answers. These interview questions involve queries with 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 SQL. In addition to improving your technical skills, practicing SQL query questions can help you advance your career. Many employers seek candidates with strong SQL skills, so demonstrating your proficiency can get 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., summary: 50 sql query interview questions for practice.

We hope you enjoyed solving the SQL exercises and learned something new. 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 yes, please share it on Facebook / Twitter with your friends and colleagues You can also follow us on our social media platforms for more resources. if you seek more information on this topic, check out the “You Might Also Like” section below.

SQL Performance Interview Guide

Check 25 SQL performance-related questions and answers.

Keep Learning SQL, TechBeamers.

You Might Also Like

If statement in sql queries: a quick guide, how to create a table in sql, 30 pl sql interview questions and answers, 20 sql tips and tricks for performance, 25 sql performance interview questions and answers.

Leave a Reply

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

Popular Tutorials

41 Essential SQL Interview Questions  *

Toptal sourced essential questions that the best sql developers and engineers can answer. driven from our community, we encourage experts to submit questions and offer feedback..

Interview Questions

What does UNION do? What is the difference between UNION and UNION ALL ?

UNION merges the contents of two structurally-compatible tables into a single combined table. The difference between UNION and UNION ALL is that UNION will omit duplicate records whereas UNION ALL will include duplicate records.

It is important to note that the performance of UNION ALL will typically be better than UNION , since UNION requires the server to do the additional work of removing any duplicates. So, in cases where is is certain that there will not be any duplicates, or where having duplicates is not a problem, use of UNION ALL would be recommended for performance reasons.

List and explain the different types of JOIN clauses supported in ANSI-standard SQL.

ANSI-standard SQL specifies five types of JOIN clauses as follows:

INNER JOIN (a.k.a. “simple join”): Returns all rows for which there is at least one match in BOTH tables. This is the default type of join if no specific JOIN type is specified.

LEFT JOIN (or LEFT OUTER JOIN ): Returns all rows from the left table, and the matched rows from the right table; i.e., the results will contain all records from the left table, even if the JOIN condition doesn’t find any matching records in the right table. This means that if the ON clause doesn’t match any records in the right table, the JOIN will still return a row in the result for that record in the left table, but with NULL in each column from the right table.

RIGHT JOIN (or RIGHT OUTER JOIN ): Returns all rows from the right table, and the matched rows from the left table. This is the exact opposite of a LEFT JOIN ; i.e., the results will contain all records from the right table, even if the JOIN condition doesn’t find any matching records in the left table. This means that if the ON clause doesn’t match any records in the left table, the JOIN will still return a row in the result for that record in the right table, but with NULL in each column from the left table.

FULL JOIN (or FULL OUTER JOIN ): Returns all rows for which there is a match in EITHER of the tables. Conceptually, a FULL JOIN combines the effect of applying both a LEFT JOIN and a RIGHT JOIN ; i.e., its result set is equivalent to performing a UNION of the results of left and right outer queries.

CROSS JOIN : Returns all records where each row from the first table is combined with each row from the second table (i.e., returns the Cartesian product of the sets of rows from the joined tables). Note that a CROSS JOIN can either be specified using the CROSS JOIN syntax (“explicit join notation”) or (b) listing the tables in the FROM clause separated by commas without using a WHERE clause to supply join criteria (“implicit join notation”).

Given the following tables:

What will be the result of the query below?

Explain your answer and also provide an alternative version of this query that will avoid the issue that it exposes.

Surprisingly, given the sample data provided, the result of this query will be an empty set. The reason for this is as follows: If the set being evaluated by the SQL NOT IN condition contains any values that are null, then the outer query here will return an empty set, even if there are many runner ids that match winner_ids in the races table.

Knowing this, a query that avoids this issue would be as follows:

Note, this is assuming the standard SQL behavior that you get without modifying the default ANSI_NULLS setting.

Apply to Join Toptal's Development Network

and enjoy reliable, steady, remote Freelance SQL Developer Jobs

Given two tables created and populated as follows:

What will the result be from the following query:

Explain your answer.

The result of the query will be as follows:

The EXISTS clause in the above query is a red herring. It will always be true since ID is not a member of dbo.docs . As such, it will refer to the envelope table comparing itself to itself!

The idnum value of NULL will not be set since the join of NULL will not return a result when attempting a match with any value of envelope .

Assume a schema of Emp ( Id, Name, DeptId ) , Dept ( Id, Name) .

If there are 10 records in the Emp table and 5 records in the Dept table, how many rows will be displayed in the result of the following SQL query:

The query will result in 50 rows as a “cartesian product” or “cross join”, which is the default whenever the ‘where’ clause is omitted.

Given two tables created as follows

Write a query to fetch values in table test_a that are and not in test_b without using the NOT keyword.

Note, Oracle does not support the above INSERT syntax, so you would need this instead:

In SQL Server, PostgreSQL, and SQLite, this can be done using the except keyword as follows:

In Oracle, the minus keyword is used instead. Note that if there are multiple columns, say ID and Name, the column should be explicitly stated in Oracle queries: Select ID from test_a minus select ID from test_b

MySQL does not support the except function. However, there is a standard SQL solution that works in all of the above engines, including MySQL:

Write a SQL query to find the 10th highest employee salary from an Employee table. Explain your answer.

(Note: You may assume that there are at least 10 records in the Employee table.)

This can be done as follows:

This works as follows:

First, the SELECT DISTINCT TOP (10) Salary FROM Employee ORDER BY Salary DESC query will select the top 10 salaried employees in the table. However, those salaries will be listed in descending order. That was necessary for the first query to work, but now picking the top 1 from that list will give you the highest salary not the the 10th highest salary.

Therefore, the second query reorders the 10 records in ascending order (which the default sort order) and then selects the top record (which will now be the lowest of those 10 salaries).

Not all databases support the TOP keyword. For example, MySQL and PostreSQL use the LIMIT keyword, as follows:

Or even more concisely, in MySQL this can be:

And in PostgreSQL this can be:

Write a SQL query using UNION ALL ( not UNION ) that uses the WHERE clause to eliminate duplicates. Why might you want to do this?

You can avoid duplicates using UNION ALL and still run much faster than UNION DISTINCT (which is actually same as UNION) by running a query like this:

The key is the AND a!=X part. This gives you the benefits of the UNION (a.k.a., UNION DISTINCT ) command, while avoiding much of its performance hit.

Write a query to to get the list of users who took the a training lesson more than once in the same day, grouped by user and training lesson, each ordered from the most recent lesson date to oldest date.

What is an execution plan? When would you use it? How would you view the execution plan?

An execution plan is basically a road map that graphically or textually shows the data retrieval methods chosen by the SQL server’s query optimizer for a stored procedure or ad hoc query. Execution plans are very useful for helping a developer understand and analyze the performance characteristics of a query or stored procedure, since the plan is used to execute the query or stored procedure.

In many SQL systems, a textual execution plan can be obtained using a keyword such as EXPLAIN , and visual representations can often be obtained as well. In Microsoft SQL Server, the Query Analyzer has an option called “Show Execution Plan” (located on the Query drop down menu). If this option is turned on, it will display query execution plans in a separate window when a query is run.

List and explain each of the ACID properties that collectively guarantee that database transactions are processed reliably.

ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. They are defined as follows:

• Atomicity. Atomicity requires that each transaction be “all or nothing”: if one part of the transaction fails, the entire transaction fails, and the database state is left unchanged. An atomic system must guarantee atomicity in each and every situation, including power failures, errors, and crashes.
• Consistency. The consistency property ensures that any transaction will bring the database from one valid state to another. Any data written to the database must be valid according to all defined rules, including constraints, cascades, triggers, and any combination thereof.
• Isolation. The isolation property ensures that the concurrent execution of transactions results in a system state that would be obtained if transactions were executed serially, i.e., one after the other. Providing isolation is the main goal of concurrency control. Depending on concurrency control method (i.e. if it uses strict - as opposed to relaxed - serializability), the effects of an incomplete transaction might not even be visible to another transaction.
• Durability. Durability means that once a transaction has been committed, it will remain so, even in the event of power loss, crashes, or errors. In a relational database, for instance, once a group of SQL statements execute, the results need to be stored permanently (even if the database crashes immediately thereafter). To defend against power loss, transactions (or their effects) must be recorded in a non-volatile memory.

Given a table dbo.users where the column user_id is a unique numeric identifier, how can you efficiently select the first 100 odd user_id values from the table?

(Assume the table contains well over 100 records with odd user_id values.)

SELECT TOP 100 user_id FROM dbo.users WHERE user_id % 2 = 1 ORDER BY user_id

What are the NVL and the NVL2 functions in SQL? How do they differ?

Both the NVL(exp1, exp2) and NVL2(exp1, exp2, exp3) functions check the value exp1 to see if it is null.

With the NVL(exp1, exp2) function, if exp1 is not null, then the value of exp1 is returned; otherwise, the value of exp2 is returned, but case to the same data type as that of exp1 .

With the NVL2(exp1, exp2, exp3) function, if exp1 is not null, then exp2 is returned; otherwise, the value of exp3 is returned.

How can you select all the even number records from a table? All the odd number records?

To select all the even number records from a table:

To select all the odd number records from a table:

What is the difference between the RANK() and DENSE_RANK() functions? Provide an example.

The only difference between the RANK() and DENSE_RANK() functions is in cases where there is a “tie”; i.e., in cases where multiple values in a set have the same ranking. In such cases, RANK() will assign non-consecutive “ranks” to the values in the set (resulting in gaps between the integer ranking values when there is a tie), whereas DENSE_RANK() will assign consecutive ranks to the values in the set (so there will be no gaps between the integer ranking values in the case of a tie).

For example, consider the set {25, 25, 50, 75, 75, 100} . For such a set, RANK() will return {1, 1, 3, 4, 4, 6} (note that the values 2 and 5 are skipped), whereas DENSE_RANK() will return {1,1,2,3,3,4} .

What is the difference between the WHERE and HAVING clauses?

When GROUP BY is not used, the WHERE and HAVING clauses are essentially equivalent.

However, when GROUP BY is used:

• The WHERE clause is used to filter records from a result. The filtering occurs before any groupings are made.
• The HAVING clause is used to filter values from a group (i.e., to check conditions after aggregation into groups has been performed).

Given a table Employee having columns empName and empId , what will be the result of the SQL query below?

“Order by 2” is only valid when there are at least two columns being used in select statement. However, in this query, even though the Employee table has 2 columns, the query is only selecting 1 column name, so “Order by 2” will cause the statement to throw an error while executing the above sql query.

What will be the output of the below query, given an Employee table having 10 records?

This query will return 10 records as TRUNCATE was executed in the transaction. TRUNCATE does not itself keep a log but BEGIN TRANSACTION keeps track of the TRUNCATE command.

• What is the difference between single-row functions and multiple-row functions?
• What is the group by clause used for?
• Single-row functions work with single row at a time. Multiple-row functions work with data of multiple rows at a time.
• The group by clause combines all those records that have identical values in a particular field or any group of fields.

Imagine a single column in a table that is populated with either a single digit (0-9) or a single character (a-z, A-Z). Write a SQL query to print ‘Fizz’ for a numeric value or ‘Buzz’ for alphabetical value for all values in that column.

['d', 'x', 'T', 8, 'a', 9, 6, 2, 'V']

…should output:

['Buzz', 'Buzz', 'Buzz', 'Fizz', 'Buzz','Fizz', 'Fizz', 'Fizz', 'Buzz']

What is the difference between char and varchar2 ?

When stored in a database, varchar2 uses only the allocated space. E.g. if you have a varchar2(1999) and put 50 bytes in the table, it will use 52 bytes.

But when stored in a database, char always uses the maximum length and is blank-padded. E.g. if you have char(1999) and put 50 bytes in the table, it will consume 2000 bytes.

Write an SQL query to display the text CAPONE as:

Or in other words, an SQL query to transpose text.

In Oracle SQL, this can be done as follows:

Can we insert a row for identity column implicitly?

Yes, like so:

Given this table:

What will be the output of below snippet?

Table is as follows:

Print the rows which have ‘Yellow’ in one of the columns C1, C2, or C3, but without using OR .

Write a query to insert/update Col2 ’s values to look exactly opposite to Col1 ’s values.

Or if the type is numeric:

How do you get the last id without the max function?

In SQL Server:

What is the difference between IN and EXISTS ?

• Works on List result set
• Doesn’t work on subqueries resulting in Virtual tables with multiple columns
• Compares every value in the result list
• Performance is comparatively SLOW for larger resultset of subquery
• Works on Virtual tables
• Is used with co-related queries
• Exits comparison when match is found
• Performance is comparatively FAST for larger resultset of subquery

Suppose in a table, seven records are there.

The column is an identity column.

Now the client wants to insert a record after the identity value 7 with its identity value starting from 10 .

Is it possible? If so, how? If not, why not?

Yes, it is possible, using a DBCC command:

How can you use a CTE to return the fifth highest (or Nth highest) salary from a table?

Given the following table named A :

Write a single query to calculate the sum of all positive values of x and he sum of all negative values of x .

Given the table mass_table :

Write a query that produces the output:

Consider the Employee table below.

Write a query to generate below output:

How do you copy data from one table to another table ?

Find the SQL statement below that is equal to the following: SELECT name FROM customer WHERE state = 'VA';

• SELECT name IN customer WHERE state IN ('VA');
• SELECT name IN customer WHERE state = 'VA';
• SELECT name IN customer WHERE state = 'V';
• SELECT name FROM customer WHERE state IN ('VA');

Given these contents of the Customers table:

Here is a query written to return the list of customers not referred by Jane Smith:

What will be the result of the query? Why? What would be a better way to write it?

Although there are 4 customers not referred by Jane Smith (including Jane Smith herself), the query will only return one: Pat Richards. All the customers who were referred by nobody at all (and therefore have NULL in their ReferredBy column) don’t show up. But certainly those customers weren’t referred by Jane Smith, and certainly NULL is not equal to 2, so why didn’t they show up?

SQL Server uses three-valued logic, which can be troublesome for programmers accustomed to the more satisfying two-valued logic (TRUE or FALSE) most programming languages use. In most languages, if you were presented with two predicates: ReferredBy = 2 and ReferredBy <> 2, you would expect one of them to be true and one of them to be false, given the same value of ReferredBy. In SQL Server, however, if ReferredBy is NULL, neither of them are true and neither of them are false. Anything compared to NULL evaluates to the third value in three-valued logic: UNKNOWN.

The query should be written in one of two ways:

Watch out for the following, though!

This will return the same faulty set as the original. Why? We already covered that: Anything compared to NULL evaluates to the third value in the three-valued logic: UNKNOWN. That “anything” includes NULL itself! That’s why SQL Server provides the IS NULL and IS NOT NULL operators to specifically check for NULL. Those particular operators will always evaluate to true or false.

Even if a candidate doesn’t have a great amount of experience with SQL Server, diving into the intricacies of three-valued logic in general can give a good indication of whether they have the ability learn it quickly or whether they will struggle with it.

Given a table TBL with a field Nmbr that has rows with the following values:

1, 0, 0, 1, 1, 1, 1, 0, 0, 1, 0, 1, 0, 1, 0, 1

Write a query to add 2 where Nmbr is 0 and add 3 where Nmbr is 1.

Suppose we have a Customer table containing the following data:

Write a single SQL statement to concatenate all the customer names into the following single semicolon-separated string:

This is close, but will have an undesired trailing ; . One way of fixing that could be:

In PostgreSQL one can also use this syntax to achieve the fully correct result:

How do you get the Nth-highest salary from the Employee table without a subquery or CTE?

This will give the third-highest salary from the Employee table. Accordingly we can find out Nth salary using LIMIT (N-1),1 .

But MS SQL Server doesn’t support that syntax, so in that case:

OFFSET ’s parameter corresponds to the (N-1) above.

How to find a duplicate record?

duplicate records with one field

duplicate records with more than one field

Considering the database schema displayed in the SQLServer-style diagram below, write a SQL query to return a list of all the invoices. For each invoice, show the Invoice ID, the billing date, the customer’s name, and the name of the customer who referred that customer (if any). The list should be ordered by billing date.

This question simply tests the candidate’s ability take a plain-English requirement and write a corresponding SQL query. There is nothing tricky in this one, it just covers the basics:

Did the candidate remember to use a LEFT JOIN instead of an inner JOIN when joining the customer table for the referring customer name? If not, any invoices by customers not referred by somebody will be left out altogether.

Did the candidate alias the tables in the JOIN? Most experienced T-SQL programmers always do this, because repeating the full table name each time it needs to be referenced gets tedious quickly. In this case, the query would actually break if at least the Customer table wasn’t aliased, because it is referenced twice in different contexts (once as the table which contains the name of the invoiced customer, and once as the table which contains the name of the referring customer).

Did the candidate disambiguate the Id and Name columns in the SELECT? Again, this is something most experienced programmers do automatically, whether or not there would be a conflict. And again, in this case there would be a conflict, so the query would break if the candidate neglected to do so.

Note that this query will not return Invoices that do not have an associated Customer. This may be the correct behavior for most cases (e.g., it is guaranteed that every Invoice is associated with a Customer, or unmatched Invoices are not of interest). However, in order to guarantee that all Invoices are returned no matter what, the Invoices table should be joined with Customers using LEFT JOIN:

There is more to interviewing than tricky technical questions, so these are intended merely as a guide. Not every “A” candidate worth hiring will be able to answer them all, nor does answering them all guarantee an “A” candidate. At the end of the day, hiring remains an art, a science — and a lot of work .

Tired of interviewing candidates? Not sure what to ask to get you a top hire?

Let Toptal find the best people for you.

Our Exclusive Network of SQL Developers

Looking to land a job as a SQL Developer?

Let Toptal find the right job for you.

Job Opportunities From Our Network

Submit an interview question

Submitted questions and answers are subject to review and editing, and may or may not be selected for posting, at the sole discretion of Toptal, LLC.

Looking for SQL Developers?

Looking for SQL Developers ? Check out Toptal’s SQL developers.

Andrea Angiolini

Andrea's been working in hospitality technology for over 20 years and has been involved in almost every aspect of it: database administration, SQL reports, database development (PL/SQL), product management, project management, design of new products, implementation, process automation, support, training, and senior management. Andrea has delivered several projects involving coordinating teams, including OTA-based integrations, full end-to-end solutions, and a full customer intelligence database.

Jeremy Barrio

Jeremy is a Microsoft SQL Server database administrator and T-SQL developer offering nearly 15 years of experience working with SQL Server 2008-2019, developing T-SQL queries and stored procedures, managing database-related projects, monitoring and optimizing databases, and developing and maintaining recurring automated processes using SQL jobs, SSIS, and PowerShell. Jeremy enjoys learning new technologies and excels with replication, HA/DR, and clustering.

Pedro Correia

Pedro has been working with Microsoft technologies since the early days of MS-DOS and GW-BASIC when he became fascinated with DBMS and used SQL Server 6.5 for the first time. During the last 20 years, he's implemented numerous web and Windows solutions in .NET, using SQL Server on the back end. Also, he taught several official Microsoft courses where he shared his expertise in the field. In one sentence, Pedro is eager to learn, inspired by challenges, and motivated to share.

Toptal Connects the Top 3% of Freelance Talent All Over The World.

Join the Toptal community.

• Cheat Sheet

SQL Practice Questions

• To-Do 53 Questions
• Done 2 Questions
• Start Over Reset All Questions

Reset your password

Please confirm your email to start using sqlinterview.com.

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]

Top Tutorials

Top references, top examples, get certified.

SQL Interview Prep: 24 Essential Questions, Answers + Code Examples

In This Guide:

Beginner sql interview questions, intermediate sql interview questions, advanced sql interview questions, how did you do.

Preparing for an SQL interview doesn’t have to be stressful. I’ve been there, and I know how overwhelming it can feel. But here’s the good news—you can absolutely nail it with the right prep. Whether you’re just getting started or diving into more advanced topics, I’ve got you covered.

In this guide, I’ll walk you through some of the most common SQL interview questions, from beginner basics to the trickier advanced stuff. By the time you’re done, you’ll have the knowledge and confidence to tackle anything the interviewer throws your way.

So, let’s jump in and get you ready to ace that interview!

Sidenote: If you find that you’re struggling with the questions in this guide, or perhaps feel that you could use some more training, or simply want to build some more impressive projects for your portfolio , then check out my complete SQL course :

This SQL Bootcamp will take you from complete beginner to a master of SQL, database management, and database design. You'll learn by using fun exercises and working with all database types to give you real-world experience. No prior experience needed. Updated with all modern SQL and Database (PostgreSQL, MySQL) features for 2024, you'll learn SQL from not one, but two industry experts. Learning SQL and database best practices from this bootcamp will help you level-up your skillset to earn a higher salary and even get you hired.

With that out of the way, let’s get into the questions!

#1. What is SQL?

SQL, or Structured Query Language, is the standard way to interact with relational databases.

It allows you to define, manipulate, and retrieve data using simple commands like SELECT , UPDATE , and DELETE . Whether you're querying customer records or building entire databases, SQL is an essential skill for anyone working with data.

For instance, e-commerce platforms rely on SQL to handle product catalogs, customer accounts, and order tracking. Without SQL, interacting with relational data would be cumbersome and prone to errors.

#2. What are the different types of SQL commands?

SQL commands are categorized into five primary types:

• DDL (Data Definition Language): Used to define or alter the structure of database objects. Commands like CREATE , ALTER , and DROP allow you to define tables, indexes, or views
• DML (Data Manipulation Language): This group includes INSERT , UPDATE , and DELETE for modifying data within tables
• DCL (Data Control Language): GRANT and REVOKE are used to manage permissions and control who can access or manipulate the data
• TCL (Transaction Control Language): Commands like COMMIT and ROLLBACK ensure that transactions are completed properly, preventing issues like partial updates or system crashes from corrupting data
• DQL (Data Query Language): Primarily includes the SELECT command, which retrieves data based on conditions you specify

Knowing these command types is vital for effective database management.

For instance, when designing a new database schema, you’ll use DDL commands to create tables, while DML commands let you update records when new data comes in. While using TCL commands ensures that multi-step processes (such as financial transactions) are handled reliably.

#3. What is a primary key?

A primary key uniquely identifies each record in a table. It ensures that no two rows can have the same value in the primary key column, and it cannot contain null values. The primary key is used in operations like retrieving specific records or establishing relationships with other tables.

Primary keys are essential for database design because they enforce uniqueness, which prevents issues like data duplication.

#4. What is a foreign key?

A foreign key is a column or group of columns that creates a relationship between two tables by referencing the primary key in another table. This ensures that data remains consistent between related tables.

Foreign keys are important because they maintain data integrity across your database. If a customer record is deleted, the foreign key constraint ensures that related orders in the Orders table are handled properly, either by cascading the deletion or preventing the removal until the related records are updated.

#5. What are aggregate functions?

Aggregate functions perform calculations on a set of values and return a single result.

Examples include:

• SUM() adds up the values in a numeric column
• AVG() returns the average of the values in a column
• COUNT() counts the number of rows in a column
• MAX() returns the largest value in a column
• MIN() returns the smallest value

These functions are useful when you need to analyze data across a large dataset.

For example, if you’re working with sales data, you might use SUM() to calculate total revenue for the quarter, or COUNT() to determine how many orders were placed.

Aggregate functions provide key insights into trends and patterns within your data.

#6. Explain the difference between DELETE and TRUNCATE commands

• DELETE removes specific rows from a table based on conditions specified in a WHERE clause. Each deleted row is logged, allowing the operation to be rolled back if needed
• TRUNCATE removes all rows from a table without logging individual row deletions, which makes it faster but irreversible. It also resets the table’s identity counter (if applicable)

TRUNCATE is generally faster than DELETE because it doesn't generate individual log entries for each row deleted, making it the better choice when clearing large datasets.

For example, if you're wiping data from a test environment, you don’t need to log each deletion, so TRUNCATE speeds up the process.

However, DELETE is more suitable when precision and the ability to undo the operation are required, such as when removing specific rows based on a condition or when transactions need to be controlled.

#7. Explain the difference between WHERE and HAVING clauses.

• WHERE filters records before any grouping takes place in a query. It’s used to filter individual rows of data
• HAVING filters records after aggregation and is used in conjunction with the GROUP BY clause to filter grouped data

For example, if you’re querying sales data, you might use WHERE to select only sales from a specific region. After grouping the data by product, you could use HAVING to show only those products with sales totals greater than a specified amount.

The key distinction is that WHERE works on raw data, while HAVING operates on aggregated data.

#8. What is normalization? Explain the different normal forms

Normalization is the process of organizing data in a database to reduce redundancy and improve integrity. It involves dividing large tables into smaller, related tables and defining relationships between them.

The different normal forms include:

• 1NF (First Normal Form): Ensures each column contains only atomic (indivisible) values and that each entry in the column is unique
• 2NF (Second Normal Form): Builds on 1NF by ensuring that all non-key attributes are fully dependent on the primary key
• 3NF (Third Normal Form): Further refines the table by ensuring that non-key attributes are dependent only on the primary key, not on other non-key attributes

Normalization is important because it improves data integrity and makes databases more efficient.

For example, in an e-commerce platform, normalization prevents storing redundant information like customer addresses in multiple places, which helps avoid inconsistencies when the data needs to be updated.

#9. What are joins in SQL? Name the different types

Joins in SQL allow you to retrieve data from multiple tables based on a related column. When working with normalized databases, data is often spread across multiple tables, and joins help you combine this data into a meaningful result set.

Types of joins:

• INNER JOIN : Returns only rows where there’s a match in both tables
• LEFT JOIN : Returns all rows from the left table and the matched rows from the right. Unmatched rows in the right table are filled with NULL
• RIGHT JOIN : Returns all rows from the right table and matched rows from the left. Unmatched rows in the left table are filled with NULL
• FULL JOIN : Returns all rows from both tables. Rows that don’t match in either table are filled with NULL
• CROSS JOIN : Returns the Cartesian product of both tables, combining every row from the first table with every row from the second

Example of an INNER JOIN :

This query retrieves all customers who have placed an order by joining the Customers and Orders tables on the CustomerID field.

#10. Explain the use of indexes in SQL

Indexes speed up data retrieval by allowing the database to find rows more quickly without having to scan the entire table.

• A clustered index alters the physical order of the data to match the index
• While a non-clustered index creates a separate structure that points to the data

For instance, when querying a table with millions of records, having an index on frequently searched columns like CustomerID can make queries execute significantly faster.

Without indexes, the database would have to perform a full table scan for each query, which can be extremely slow for large datasets.

However, indexes also take up space and can slow down write operations (like INSERT or UPDATE ), so it's important to use them judiciously.

#11. What is a self-join?

A self-join is when a table is joined with itself. This type of join is particularly useful when you want to compare rows within the same table. It’s commonly used in hierarchical data structures, such as employee-manager relationships.

For example, in a table of employees, you might use a self-join to find each employee's manager by joining the table to itself based on the ManagerID column:

Self-joins are essential for working with recursive or hierarchical data structures where relationships exist within the same entity, like organizational charts or product categories.

#12. What is a view in SQL, and why is it used?

A view is a virtual table based on the result of a SELECT query. Views do not store data but instead present data from one or more tables, making it easier to query complex datasets without having to rewrite complicated queries each time.

Why use views?

• Simplify complex queries: If you have a query that involves many joins, calculations, or conditions, a view can wrap it into a single virtual table that’s easier to query
• Restrict access to data: Views can hide sensitive columns or rows, providing a filtered perspective of the data without exposing everything in the underlying tables
• Present data in a specific format: Views can be used to aggregate data or structure it in a way that is convenient for reporting or application use

Example of creating a view:

This view returns only the FirstName , LastName , and Salary of employees who work in the Sales department.

#13. Explain the difference between INNER JOIN and OUTER JOIN

An INNER JOIN returns only the rows that have matching values in both tables. It effectively filters out rows from either table that don’t have a corresponding match in the other table. This is the default join type and is commonly used when you want to retrieve only the rows that meet specific criteria in both tables.

An OUTER JOIN returns all the rows from one or both tables, regardless of whether there is a match. If no match is found, the missing data is filled with NULL values.

OUTER JOIN can be divided into three types:

• LEFT OUTER JOIN : Returns all rows from the left table, along with matched rows from the right table. If there’s no match, NULL is returned for columns from the right table
• RIGHT OUTER JOIN : Returns all rows from the right table, along with matched rows from the left table. If there’s no match, NULL is returned for columns from the left table
• FULL OUTER JOIN : Returns all rows from both tables. If there’s no match in either table, NULL is returned for the missing data from the other table

Example of a LEFT OUTER JOIN:

This query retrieves all customers, including those who haven’t placed any orders. If a customer hasn’t placed an order, NULL is returned for the OrderID .

The key difference is that INNER JOIN excludes non-matching rows, while OUTER JOIN includes all rows from one or both tables and uses NULL to fill in gaps for unmatched rows.

#14. What is a subquery?

A subquery is a query nested inside another SQL query, often used in the WHERE clause to filter results based on the output of another query. Subqueries can be either:

• Correlated Subquery: The subquery depends on values from the outer query
• Non-Correlated Subquery: The subquery runs independently of the outer query

Example of a subquery used to find employees who work in the same department as 'John':

Subqueries are particularly useful when a single query isn’t sufficient to retrieve the needed data. For example, if you're looking for records that depend on the results of another query, subqueries provide a convenient solution without requiring multiple steps or temporary tables.

#15. What is a CTE (Common Table Expression)?

A CTE (Common Table Expression) is a temporary result set that you can reference within a SELECT , INSERT , UPDATE , or DELETE statement. It’s defined using the WITH clause and can improve query readability and performance by breaking down complex queries into smaller, reusable parts.

Example of a CTE used to calculate total sales per salesperson:

CTEs are especially helpful when working with recursive queries or when you need to reference the same result set multiple times in a query.

They improve query organization and make complex operations easier to manage.

#16. What are window functions in SQL?

Window functions perform calculations across a set of table rows related to the current row.

Unlike aggregate functions, window functions do not collapse rows into a single result; instead, they calculate values across a set of rows, maintaining individual row outputs.

For example :

Here you can see a window function that calculates the average salary for each department while showing individual salaries:

Window functions are used when you need to calculate running totals, moving averages, or rank rows within partitions of data.

They provide powerful analytical capabilities that are more flexible than simple aggregate functions, allowing for complex data analysis without restructuring your query results.

#17. Explain the ACID properties in the context of a SQL transaction

The ACID properties ensure that SQL transactions are processed reliably:

• Atomicity : Ensures that all operations within a transaction are completed. If any part of the transaction fails, the entire transaction is rolled back
• Consistency : Ensures the database remains in a valid state before and after the transaction
• Isolation : Ensures that transactions are executed in isolation from each other
• Durability : Guarantees that once a transaction is committed, the changes are permanent, even if the system crashes afterward

These properties are crucial in scenarios where data consistency and reliability are essential, such as in financial systems. If a bank transfer fails halfway through, atomicity ensures that the funds aren’t deducted from one account without being credited to the other.

#18. What are stored procedures?

Stored procedures are precompiled collections of SQL statements that are stored in the database and can be executed as a single unit. They can accept input parameters, perform operations, and return results.

Example of a stored procedure that retrieves employee details:

Stored procedures are used to encapsulate repetitive tasks, enforce business logic at the database level, and improve performance by reducing the need to send multiple queries across the network.

Since they are stored directly in the database, they also offer better security and can be optimized for faster execution.

#19. Explain the use of triggers in SQL

Triggers are special types of stored procedures that automatically execute (or “fire”) when certain events occur in the database, such as an INSERT , UPDATE , or DELETE operation.

Triggers are often used to enforce business rules, maintain data integrity, or automate auditing.

For example:

Here you can see a trigger that logs every time a new employee is added:

Triggers are particularly useful for automatic tasks like logging changes, enforcing constraints, or cascading updates.

For example, in an HR system, a trigger can ensure that whenever an employee is added, a corresponding record is created in the payroll table.

#20. What is the difference between OLTP and OLAP systems?

OLTP (Online Transaction Processing)

These systems are designed for managing transaction-oriented applications where quick query processing and maintaining data integrity are important.

They support a high volume of short, atomic transactions like order processing, customer management, and financial operations.

OLAP (Online Analytical Processing)

These systems are optimized for complex queries and analysis, often using historical data.

They’re typically used in business intelligence and data warehousing, allowing for the analysis of large datasets and the generation of reports. For example, a retail company might use an OLTP system to handle customer orders in real-time, while using an OLAP system to analyze historical sales trends and create reports that inform business decisions.

#21. Explain the differences between UNION and UNION ALL

• **UNION** combines the result sets of two or more SELECT queries and removes duplicate rows
• **UNION ALL** combines the result sets of two or more SELECT queries but keeps all duplicate rows

UNION is useful when you want a clean, deduplicated list of results, such as when merging customer data from multiple sources.

However, UNION ALL is faster because it doesn't remove duplicates, making it a better choice when you need all records, including duplicates, for analytical purposes.

#22. Explain the concept of a Primary Key in SQL and its importance

A primary key is a column (or a set of columns) that uniquely identifies each row in a table. It ensures that no two rows can have the same primary key value, and it does not allow null values.

A table can have only one primary key, but it may consist of multiple columns (a composite key).

For example, in an Employees table, EmployeeID could be the primary key, ensuring that each employee has a unique identifier:

The primary key is crucial for maintaining data integrity and allows relational databases to efficiently index and access specific records.

Without a primary key, it would be difficult to ensure that each record is unique, leading to potential data duplication and inconsistency.

#23. Explain the difference between a view and a table in SQL

A table is a physical object in the database that stores data in rows and columns. You can perform various operations on a table, such as inserting, updating, or deleting records. Tables store data directly.

A view , on the other hand, is a virtual table that is the result of a SELECT query.

Views do not store data directly; instead, they display data from one or more underlying tables. A view can be used to simplify complex queries, provide specific data to users without exposing the full table, or present data in a different format.

For example, a view might show only the name and salary of employees, hiding sensitive information like social security numbers:

Views provide an abstraction layer, which can help control access to data, streamline complex queries, and present data in a more user-friendly format.

#24. What are SQL transactions, and what are the properties (ACID) associated with them?

A transaction in SQL is a sequence of one or more SQL operations that are executed as a single unit of work.

Transactions are used to ensure that operations on the database are executed reliably and that the database remains consistent even in the event of a failure.

• Atomicity: Ensures that all operations within a transaction are completed. If any part of the transaction fails, the entire transaction is rolled back, leaving the database unchanged
• Consistency: Ensures the database moves from one valid state to another, following all defined rules and constraints
• Isolation: Ensures that transactions are securely processed in isolation from other concurrent transactions
• Durability: Once a transaction has been committed, it is permanent, even if the system crashes afterward

For example, when transferring money between two bank accounts, a transaction ensures that either both the debit and credit operations are completed, or neither is. This guarantees the accuracy and integrity of the financial data:

These properties are crucial in any environment where data accuracy and integrity are essential, such as in banking, ecommerce, or healthcare systems.

There you have it - 24 of the most common SQL questions and answers that you might encounter in your interview.

Preparing for an SQL interview requires a solid understanding of both fundamental and advanced SQL concepts, but by practicing these questions and understanding their underlying principles, you can confidently navigate through your SQL interview.

What did you score? Did you nail all 24 questions? If so, it might be time to move from studying to actively interviewing!

Didn't get them all? Got tripped up on a few? Don't worry; I'm here to help.

If you want to fast-track your SQL knowledge and interview prep, and get as much hands-on practice as possible, then check out my complete SQL course :

Like I said earlier, this SQL Bootcamp will take you from complete beginner to a master of SQL, database management, and database design. You'll learn by using fun exercises and working with all database types to give you real-world experience. No prior experience needed.

Plus, once you join, you'll have the opportunity to ask questions in our private Discord community from me, other students and working SQL Devs.

If you join or not, I just want to wish you the best of luck with your interview!

Complete SQL + Databases Bootcamp

This SQL Bootcamp will teach you SQL, database management, and database design using real-world exercises working with all database types. No experience needed.

More from Zero To Mastery

From getting paid to find exploits to defending against hackers, it's never a boring job in Cyber Security! Here are the top 5 reasons to learn cybersecurity.

Quality over quantity... we give you the only 7 SQL projects (+ our top 3) that you need to boost your SQL skills, confidence, and portfolio!

With 400,000+ jobs available and $120,000+ / year salaries, now is the perfect time to become a DevOps Engineer! Here's your step-by-step guide (with all the resources you need to go from complete beginner to getting hired).

• SQL Cheat Sheet

SQL Interview Questions

• MySQL Interview Questions
• PL/SQL Interview Questions
• Learn SQL and Database

SQL  is a standard database language used for accessing and manipulating data in databases. It stands for  Structured Query Language  and was developed by IBM Computer Scientists in the 1970s. By executing queries, SQL can create, update, delete, and retrieve data in databases like MySQL, Oracle, PostgreSQL, etc. Overall, SQL is a query language that communicates with databases.

In this article, we cover  70+ SQL Interview Questions with answers  asked in SQL developer interviews at MAANG and other high-paying companies. Whether you are a fresher or an experienced professional with 2, 5, or 10 years of experience, this article gives you all the confidence you need to ace your next SQL interview.

Table of Content

SQL Interview Questions and Answers for Freshers

Intermediate sql interview questions and answers, advanced sql interview questions and answers, 1. what is sql  .

SQL stands for Structured Query Language. It is a language used to interact with the database, i.e to create a database, to create a table in the database, to retrieve data or update a table in the database, etc. SQL is an ANSI(American National Standards Institute) standard. Using SQL, we can do many things. For example – we can execute queries, we can insert records into a table, can update records, can create a database, can create a table, can delete a table, etc.

2. What is a database?  

A Database is defined as a structured form of data storage in a computer or a collection of data in an organized manner and can be accessed in various ways. It is also the collection of schemas, tables, queries, views, etc. Databases help us with easily storing, accessing, and manipulating data held on a computer. The Database Management System allows a user to interact with the database.

3. Does SQL support programming language features?  

It is true that SQL is a language, but it does not support programming as it is not a programming language, it is a command language. We do not have conditional statements in SQL like for loops or if..else, we only have commands which we can use to query, update, delete, etc. data in the database. SQL allows us to manipulate data in a database.

4. What is the difference between CHAR and VARCHAR2 datatype in SQL? 

Both of these data types are used for characters, but varchar2 is used for character strings of variable length, whereas char is used for character strings of fixed length. For example , if we specify the type as char(5) then we will not be allowed to store a string of any other length in this variable, but if we specify the type of this variable as varchar2(5) then we will be allowed to store strings of variable length. We can store a string of length 3 or 4 or 2 in this variable.

5. What do you mean by data definition language? 

Data definition language or DDL allows to execution of queries like CREATE, DROP, and ALTER. That is those queries that define the data.

6. What do you mean by data manipulation language?

Data manipulation Language or DML is used to access or manipulate data in the database. It allows us to perform the below-listed functions: 

• Insert data or rows in a database
• Delete data from the database
• Retrieve or fetch data
• Update data in a database.

7. What is the view in SQL? 

Views in SQL are a kind of virtual table. A view also has rows and columns as they are on a real table in the database. We can create a view by selecting fields from one or more tables present in the database. A View can either have all the rows of a table or specific rows based on certain conditions.

The CREATE VIEW statement of SQL is used for creating views. 

Basic Syntax: 

8. What do you mean by foreign key? 

A Foreign key is a field that can uniquely identify each row in another table. And this constraint is used to specify a field as a Foreign key. That is this field points to the primary key of another table. This usually creates a kind of link between the two tables. 

Consider the two tables as shown below: 

As we can see clearly, that the field C_ID in the Orders table is the primary key in the Customers’ table, i.e. it uniquely identifies each row in the Customers table. Therefore, it is a Foreign Key in the Orders table. 

9. What are table and Field?

Table: A table has a combination of rows and columns. Rows are called records and columns are called fields. In MS SQL Server, the tables are being designated within the database and schema names. 

Field: In DBMS, a database field can be defined as – a single piece of information from a record.

10. What is the primary key?

A Primary Key is one of the candidate keys. One of the candidate keys is selected as the most important and becomes the primary key. There cannot be more than one primary key in a table.

11. What is a Default constraint?

The DEFAULT constraint is used to fill a column with default and fixed values. The value will be added to all new records when no other value is provided.

12. What is normalization?

It is a process of analyzing the given relation schemas based on their functional dependencies and primary keys to achieve the following desirable properties: 

• Minimizing Redundancy
• Minimizing the Insertion, Deletion, And Update Anomalies

Relation schemas that do not meet the properties are decomposed into smaller relation schemas that could meet desirable properties. 

13. What is Denormalization?

Denormalization is a database optimization technique in which we add redundant data to one or more tables. This can help us avoid costly joins in a relational database. Note that denormalization does not mean not doing normalization. It is an optimization technique that is applied after normalization. 

In a traditional normalized database, we store data in separate logical tables and attempt to minimize redundant data. We may strive to have only one copy of each piece of data in the database.

14. What is a query?

An SQL query is used to retrieve the required data from the database. However, there may be multiple SQL queries that yield the same results but with different levels of efficiency. An inefficient query can drain the database resources, reduce the database speed or result in a loss of service for other users. So it is very important to optimize the query to obtain the best database performance.

15. What is a subquery?

In SQL, a Subquery can be simply defined as a query within another query. In other words, we can say that a Subquery is a query that is embedded in the WHERE clause of another SQL query.

16. What are the different operators available in SQL?

There are three operators available in SQL namely:

• Arithmetic Operators
• Logical Operators
• Comparison Operators

17. What is a Constraint?

Constraints are the rules that we can apply to the type of data in a table. That is, we can specify the limit on the type of data that can be stored in a particular column in a table using constraints. For more details please refer to SQL|Constraints article.

18. What is Data Integrity?

Data integrity is defined as the data contained in the database being both correct and consistent. For this purpose, the data stored in the database must satisfy certain types of procedures (rules). The data in a database must be correct and consistent. So, data stored in the database must satisfy certain types of procedures (rules). DBMS provides different ways to implement such types of constraints (rules). This improves data integrity in a database. For more details please refer difference between data security and data integrity article.

19. What is Auto Increment?

Sometimes, while creating a table, we do not have a unique identifier within the table, hence we face difficulty in choosing Primary Key. So as to resolve such an issue, we’ve to manually provide unique keys to every record, but this is often also a tedious task. So we can use the  Auto-Increment feature that automatically generates a numerical Primary key value for every new record inserted. The Auto Increment feature is supported by all the Databases. For more details please refer SQL Auto Increment article.

20. What is MySQL collation?

A MySQL collation is a well-defined set of rules which are used to compare characters of a particular character set by using their corresponding encoding. Each character set in MySQL might have more than one collation, and has, at least, one default collation. Two character sets cannot have the same collation. For more details please refer What are collation and character set in MySQL? article.

21. What are user-defined functions?

We can use User-defined functions in PL/SQL or Java to provide functionality that is not available in SQL or SQL built-in functions. SQL functions and User-defined functions can appear anywhere, that is, wherever an expression occurs.

For example, it can be used in:

• Select a list of SELECT statements.
• Condition of the WHERE clause.
• CONNECT BY, ORDER BY, START WITH, and GROUP BY
• The VALUES clause of the INSERT statement.
• The SET clause of the UPDATE statement.

22. What are all types of user-defined functions?

User-Defined Functions allow people to define their own T-SQL functions that can accept 0 or more parameters and return a single scalar data value or a table data type. Different Kinds of User-Defined Functions created are:

1. Scalar User-Defined Function  A Scalar user-defined function returns one of the scalar data types. Text, image, and timestamp data types are not supported. These are the type of user-defined functions that most developers are used to in other programming languages. You pass in 0 to many parameters and you get a return value.

2. Inline Table-Value User-Defined Function  An Inline Table-Value user-defined function returns a table data type and is an exceptional alternative to a view as the user-defined function can pass parameters into a T-SQL select command and, in essence, provide us with a parameterized, non-updateable view of the underlying tables.

3. Multi-statement Table-Value User-Defined Function  A Multi-Statement Table-Value user-defined function returns a table and is also an exceptional alternative to a view, as the function can support multiple T-SQL statements to build the final result where the view is limited to a single SELECT statement. Also, the ability to pass parameters into a TSQL select command or a group of them gives us the capability to, in essence, create a parameterized, non-updateable view of the data in the underlying tables. Within the create function command you must define the table structure that is being returned. After creating this type of user-defined function, it can be used in the FROM clause of a T-SQL command, unlike the behavior found when using a stored procedure which can also return record sets.

23. What is a  stored procedure?

Stored Procedures are created to perform one or more DML operations on databases. It is nothing but a group of SQL statements that accepts some input in the form of parameters and performs some task and may or may not return a value. For more details please refer to our Stored procedures in the SQL article.

24. What are aggregate and scalar functions?

For doing operations on data SQL has many built-in functions, they are categorized into two categories and further sub-categorized into seven different functions under each category. The categories are:

• Aggregate functions: These functions are used to do operations from the values of the column and a single value is returned.
• Scalar functions: These functions are based on user input, these too return a single value.

For more details, please read the SQL | Functions (Aggregate and Scalar Functions) article.

25. What is an ALIAS command?

Aliases are the temporary names given to a table or column for the purpose of a particular SQL query. It is used when the name of a column or table is used other than its original name, but the modified name is only temporary.

• Aliases are created to make table or column names more readable.
• The renaming is just a temporary change and the table name does not change in the original database.
• Aliases are useful when table or column names are big or not very readable.
• These are preferred when there is more than one table involved in a query.

For more details, please read the SQL | Aliases article.

26. What are Union, minus, and Interact commands?

Set Operations in SQL eliminate duplicate tuples and can be applied only to the relations which are union compatible. Set Operations available in SQL are :

• Set Intersection
• Set Difference

UNION Operation: This operation includes all the tuples which are present in either of the relations. For example: To find all the customers who have a loan or an account or both in a bank.

The union operation automatically eliminates duplicates. If all the duplicates are supposed to be retained, UNION ALL is used in place of UNION. 

INTERSECT Operation:  This operation includes the tuples which are present in both of the relations. For example: To find the customers who have a loan as well as an account in the bank:

The Intersect operation automatically eliminates duplicates. If all the duplicates are supposed to be retained, INTERSECT ALL is used in place of INTERSECT. 

EXCEPT for Operation:  This operation includes tuples that are present in one relationship but should not be present in another relationship. For example: To find customers who have an account but no loan at the bank:

The Except operation automatically eliminates the duplicates. If all the duplicates are supposed to be retained, EXCEPT ALL is used in place of EXCEPT.

27. What is a T-SQL?

T-SQL is an abbreviation for Transact Structure Query Language. It is a product by Microsoft and is an extension of SQL Language which is used to interact with relational databases. It is considered to perform best with Microsoft SQL servers. T-SQL statements are used to perform the transactions to the databases. T-SQL has huge importance since all the communications with an instance of an SQL server are done by sending Transact-SQL statements to the server. Users can also define functions using T-SQL.

Types of T-SQL functions are :

• Aggregate functions.
• Ranking functions. There are different types of ranking functions.
• Rowset function.
• Scalar functions.

28. What is ETL in SQL?

ETL is a process in Data Warehousing and it stands for Extract , Transform, and Load . It is a process in which an ETL tool extracts the data from various data source systems, transforms it in the staging area, and then finally, loads it into the Data Warehouse system. These are three database functions that are incorporated into one tool to pull data out from one database and put data into another database.

29. How to copy tables in SQL?

Sometimes, in SQL, we need to create an exact copy of an already defined (or created) table. MySQL enables you to perform this operation. Because we may need such duplicate tables for testing the data without having any impact on the original table and the data stored in it. 

For more details, Please read Cloning Table in the MySQL article.

30.  What is SQL injection?

SQL injection is a technique used to exploit user data through web page inputs by injecting SQL commands as statements. Basically, these statements can be used to manipulate the application’s web server by malicious users.

• SQL injection is a code injection technique that might destroy your database.
• SQL injection is one of the most common web hacking techniques.
• SQL injection is the placement of malicious code in SQL statements, via web page input.

For more details, please read the SQL | Injection article.

31. Can we disable a trigger? If yes, how?

 Yes, we can disable a trigger in PL/SQL. If consider temporarily disabling a trigger and one of the following conditions is true:

• An object that the trigger references is not available.
• We must perform a large data load and want it to proceed quickly without firing triggers.
• We are loading data into the table to which the trigger applies.
• We disable a trigger using the ALTER TRIGGER statement with the DISABLE option.
• We can disable all triggers associated with a table at the same time using the ALTER TABLE statement with the DISABLE ALL TRIGGERS option.

32. What are the differences between SQL and PL/SQL?  

Some common differences between SQL and PL/SQL are as shown below: 

33. What is the difference between BETWEEN and IN operators in SQL?  

BETWEEN: The BETWEEN operator is used to fetch rows based on a range of values.  For example, 

This query will select all those rows from the table. Students where the value of the field ROLL_NO lies between 20 and 30.  IN: The IN operator is used to check for values contained in specific sets.  For example, 

This query will select all those rows from the table Students where the value of the field ROLL_NO is either 20 or 21 or 23.

34. Write an SQL query to find the names of employees starting with ‘A’. 

The LIKE operator of SQL is used for this purpose. It is used to fetch filtered data by searching for a particular pattern in the where clause.  The Syntax for using LIKE is, 

SELECT column1,column2 FROM table_name WHERE column_name LIKE pattern; LIKE: operator name pattern: exact value extracted from the pattern to get related data in result set.

The required query is: 

You may refer to this article WHERE clause for more details on the LIKE operator.

35. What is the difference between primary key and unique constraints? 

The primary key cannot have NULL values, the unique constraints can have NULL values. There is only one primary key in a table, but there can be multiple unique constraints. The primary key creates the clustered index automatically but the unique key does not.

36. What is a join in SQL? What are the types of joins?  

An SQL Join statement is used to combine data or rows from two or more tables based on a common field between them. Different types of Joins are: 

• INNER JOIN : The INNER JOIN keyword selects all rows from both tables as long as the condition is satisfied. This keyword will create the result set by combining all rows from both the tables where the condition satisfies i.e. the value of the common field will be the same.
• LEFT JOIN : This join returns all the rows of the table on the left side of the join and matching rows for the table on the right side of the join. For the rows for which there is no matching row on the right side, the result set will be null. LEFT JOIN is also known as LEFT OUTER JOIN
• RIGHT JOIN : RIGHT JOIN is similar to LEFT JOIN. This join returns all the rows of the table on the right side of the join and matching rows for the table on the left side of the join. For the rows for which there is no matching row on the left side, the result set will contain null. RIGHT JOIN is also known as RIGHT OUTER JOIN.
• FULL JOIN : FULL JOIN creates the result set by combining the results of both LEFT JOIN and RIGHT JOIN. The result set will contain all the rows from both tables. For the rows for which there is no matching, the result set will contain NULL values.

37. What is an index?  

A database index is a data structure that improves the speed of data retrieval operations on a database table at the cost of additional writes and the use of more storage space to maintain the extra copy of data. Data can be stored only in one order on a disk. To support faster access according to different values, a faster search like a binary search for different values is desired. For this purpose, indexes are created on tables. These indexes need extra space on the disk, but they allow faster search according to different frequently searched values.

38. What is the On Delete cascade constraint?

An ‘ON DELETE CASCADE’ constraint is used in MySQL to delete the rows from the child table automatically when the rows from the parent table are deleted. For more details, please read MySQL – On Delete Cascade constraint article.

39. Explain  WITH clause in SQL?

The WITH clause provides a way relationship of defining a temporary relationship whose definition is available only to the query in which the with clause occurs. SQL applies predicates in the WITH clause after groups have been formed, so aggregate functions may be used.

40. What are all the different attributes of indexes?

The indexing has various attributes:

• Access Types : This refers to the type of access such as value-based search, range access, etc.
• Access Time : It refers to the time needed to find a particular data element or set of elements.
• Insertion Time : It refers to the time taken to find the appropriate space and insert new data.
• Deletion Time : Time is taken to find an item and delete it as well as update the index structure.
• Space Overhead : It refers to the additional space required by the index.

41. What is a Cursor?

The cursor is a Temporary Memory or Temporary Work Station. It is Allocated by Database Server at the Time of Performing DML operations on the Table by the User. Cursors are used to store Database Tables. 

42. Write down various types of relationships in SQL?

There are various relationships, namely:

• One-to-One Relationship.
• One to Many Relationships.
• Many to One Relationship.
• Self-Referencing Relationship.

43. What is a trigger?

The trigger is a statement that a system executes automatically when there is any modification to the database. In a trigger, we first specify when the trigger is to be executed and then the action to be performed when the trigger executes. Triggers are used to specify certain integrity constraints and referential constraints that cannot be specified using the constraint mechanism of SQL.

44. What is the difference between SQL DELETE and SQL TRUNCATE commands?

45. What is the difference between Cluster and Non-Cluster Index?

For more details please refer Difference between Clustered index and the No-Clustered index article.

46.  What is a Live Lock?

Livelock occurs when two or more processes continually repeat the same interaction in response to changes in the other processes without doing any useful work. These processes are not in the waiting state, and they are running concurrently. This is different from a deadlock because in a deadlock all processes are in the waiting state.

47. What is Case WHEN in SQL?

Control statements form an important part of most languages since they control the execution of other sets of statements. These are found in SQL too and should be exploited for uses such as query filtering and query optimization through careful selection of tuples that match our requirements. In this post, we explore the Case-Switch statement in SQL. The CASE statement is SQL’s way of handling if/then logic.

CASE case_value WHEN when_value THEN statement_list [WHEN when_value THEN statement_list] … [ELSE statement_list]END CASE

CASE WHEN search_condition THEN statement_list [WHEN search_condition THEN statement_list] … [ELSE statement_list]END CASE

For more details, please read the SQL | Case Statement article.

48. Name different types of case manipulation functions available in SQL. 

There are three types of case manipulation functions available in SQL. They are, 

• LOWER : The purpose of this function is to return the string in lowercase. It takes a string as an argument and returns the string by converting it into lower case.  Syntax: 

LOWER(‘string’)

• UPPER : The purpose of this function is to return the string in uppercase. It takes a string as an argument and returns the string by converting it into uppercase.  Syntax:

UPPER(‘string’)

• INITCAP : The purpose of this function is to return the string with the first letter in uppercase and the rest of the letters in lowercase.  Syntax: 

INITCAP(‘string’)

49. What are local and global variables and their differences?

Global Variable: In contrast, global variables are variables that are defined outside of functions. These variables have global scope, so they can be used by any function without passing them to the function as parameters.

Local Variable: Local variables are variables that are defined within functions. They have local scope, which means that they can only be used within the functions that define them.

50. Name the function which is used to remove spaces at the end of a string?

In SQL, the spaces at the end of the string are removed by a trim function.

Trim(s) , Where s is a any string.

51. What is the difference between TRUNCATE and DROP statements?

For more details, please read the Difference between DROP and TRUNCATE in the SQL  article.

52. Which operator is used in queries for pattern matching?

LIKE operator: It is used to fetch filtered data by searching for a particular pattern in the where clause.

SELECT column1,column2 FROM table_name WHERE column_name LIKE pattern; LIKE: operator name

53. Define SQL Order by the statement?

The ORDER BY statement in SQL is used to sort the fetched data in either ascending or descending according to one or more columns.

• By default ORDER BY sorts the data in ascending order.
• We can use the keyword DESC to sort the data in descending order and the keyword ASC to sort in ascending order.

For more details please read SQL | ORDER BY article.

54. Explain SQL Having statement?

HAVING is used to specify a condition for a group or an aggregate function used in the select statement. The WHERE clause selects before grouping. The HAVING clause selects rows after grouping. Unlike the HAVING clause, the WHERE clause cannot contain aggregate functions. See Having vs Where Clause?  

55. Explain SQL AND OR statement with an example?

In SQL, the AND & OR operators are used for filtering the data and getting precise results based on conditions. The AND and OR operators are used with the WHERE clause.

These two operators are called conjunctive operators .

• AND Operator: This operator displays only those records where both conditions condition 1 and condition 2 evaluate to True.
• OR Operator:  This operator displays the records where either one of the conditions condition 1 and condition 2 evaluates to True. That is, either condition1 is True or condition2 is True.

For more details please read the SQL | AND and OR operators article.

56. Define BETWEEN statements in SQL?

The SQL BETWEEN condition allows you to easily test if an expression is within a range of values (inclusive). The values can be text, date, or numbers. It can be used in a SELECT, INSERT, UPDATE, or DELETE statement. The SQL BETWEEN Condition will return the records where the expression is within the range of value1 and value2.

For more details please read SQL | Between & I operator article.

57. Why do we use  Commit and Rollback commands?

For more details please read the Difference between Commit and Rollback in SQL article.

58. What are ACID properties?

A transaction is a single logical unit of work that accesses and possibly modifies the contents of a database. Transactions access data using read-and-write operations. In order to maintain consistency in a database, before and after the transaction, certain properties are followed. These are called ACID properties. ACID (Atomicity, Consistency, Isolation, Durability) is a set of properties that guarantee that database transactions are processed reliably. For more details please read ACID properties in the DBMS article.

59. Are NULL values the same as zero or a blank space? 

In SQL, zero or blank space can be compared with another zero or blank space. whereas one null may not be equal to another null. null means data might not be provided or there is no data.

60. What is the need for group functions in SQL? 

In database management, group functions, also known as aggregate functions,  is a function where the values of multiple rows are grouped together as input on certain criteria to form a single value of more significant meaning.

Various Group Functions

For more details please read the Aggregate functions in the SQL article.

61. What is the need for a MERGE statement?

The MERGE command in SQL is actually a combination of three SQL statements: INSERT, UPDATE, and DELETE . In simple words, the MERGE statement in SQL provides a convenient way to perform all these three operations together which can be very helpful when it comes to handling large running databases. But unlike INSERT, UPDATE, and DELETE statements MERGE statement requires a source table to perform these operations on the required table which is called a target table. For more details please read the SQL | MERGE Statement article.

62. How can you fetch common records from two tables?

The below statement could be used to get data from multiple tables, so, we need to use join to get data from multiple tables.

SELECT tablenmae1.colunmname, tablename2.columnnmae FROM tablenmae1 JOIN tablename2 ON tablenmae1.colunmnam = tablename2.columnnmae ORDER BY columnname;

For more details and examples, please read SQL | SELECT data from the Multiple Tables article.

63. What are the advantages of PL/SQL functions?

The advantages of PL / SQL functions are as follows: 

• We can make a single call to the database to run a block of statements. Thus, it improves the performance against running SQL multiple times. This will reduce the number of calls between the database and the application.
• We can divide the overall work into small modules which becomes quite manageable, also enhancing the readability of the code.
• It promotes reusability.
• It is secure since the code stays inside the database, thus hiding internal database details from the application(user). The user only makes a call to the PL/SQL functions. Hence, security and data hiding is ensured.

64. What is the SQL query to display the current date?

CURRENT_DATE returns to the current date. This function returns the same value if it is executed more than once in a single statement, which means that the value is fixed, even if there is a long delay between fetching rows in a cursor.

CURRENT_DATE or CURRENT DATE

65. What are Nested Triggers?

A trigger can also contain INSERT, UPDATE, and DELETE logic within itself, so when the trigger is fired because of data modification it can also cause another data modification, thereby firing another trigger. A trigger that contains data modification logic within itself is called a nested trigger.

66. How to find the available constraint information in the table?

In SQL Server the data dictionary is a set of database tables used to store information about a database’s definition. One can use these data dictionaries to check the constraints on an already existing table and to change them(if possible). For more details please read SQL | Checking Existing Constraint on a table article.

67.  How do we avoid getting duplicate entries in a query without using the distinct keyword?

DISTINCT is useful in certain circumstances, but it has drawbacks that it can increase the load on the query engine to perform the sort (since it needs to compare the result set to itself to remove duplicates). We can remove duplicate entries using the following options:

• Remove duplicates using row numbers.
• Remove duplicates using self-Join.
• Remove duplicates using group by.

For more details, please read SQL | Remove duplicates without distinct articles.

68. The difference between NVL and NVL2 functions?

These functions work with any data type and pertain to the use of null values in the expression list. These are all single-row functions i.e. provide one result per row.

NVL(expr1, expr2): In SQL, NVL() converts a null value to an actual value. Data types that can be used are date, character, and number. Data types must match with each other. i.e. expr1 and expr2 must be of the same data type.

NVL (expr1, expr2)

NVL2(expr1, expr2, expr3):  The NVL2 function examines the first expression. If the first expression is not null, then the NVL2 function returns the second expression. If the first expression is null, then the third expression is returned i.e. If expr1 is not null, NVL2 returns expr2. If expr1 is null, NVL2 returns expr3. The argument expr1 can have any data type.

NVL2 (expr1, expr2, expr3)

For more details please read SQL general functions | NVL, NVL2, DECODE, COALESCE, NULLIF, LNNVL , and NANVL article.

69.  What is the difference between COALESCE() & ISNULL()? 

COALESCE():  COALESCE function in SQL returns the first non-NULL expression among its arguments. If all the expressions evaluate to null, then the COALESCE function will return null. Syntax:

SELECT column(s), CAOLESCE(expression_1,….,expression_n)FROM table_name;

ISNULL():  The ISNULL function has different uses in SQL Server and MySQL. In SQL Server, ISNULL() function is used to replace NULL values. Syntax:

SELECT column(s), ISNULL(column_name, value_to_replace)FROM table_name;

For more details, please read the SQL | Null functions article.

70. Name the operator which is used in the query for appending two strings?

In SQL for appending two strings, the ” Concentration operator”  is used and its symbol is ” || “.

In conclusion, mastering SQL interview questions is crucial for data analysts, data engineers, and business analysts aiming to excel in their respective fields. This article has provided a comprehensive set of SQL interview questions and answers designed to prepare you thoroughly.

By understanding and practicing these questions, you enhance your ability to tackle the challenges posed in SQL-related interviews effectively, ultimately paving the way for a successful career in data handling and analysis. Remember, each question is an opportunity to demonstrate your analytical prowess and technical expertise, essential traits for any aspiring professional in these critical roles.

Please Login to comment...

Similar reads.

• interview-questions
• placement preparation
• Best Twitch Extensions for 2024: Top Tools for Viewers and Streamers
• Discord Emojis List 2024: Copy and Paste
• Best Adblockers for Twitch TV: Enjoy Ad-Free Streaming in 2024
• PS4 vs. PS5: Which PlayStation Should You Buy in 2024?
• Full Stack Developer Roadmap [2024 Updated]

Improve your Coding Skills with Practice

What kind of Experience do you want to share?

Top 15 SQL Scenario Based Interview Questions with Answers (2024)

According to a 2021 developer survey , each of the top four most common database management systems uses SQL. If you have an interview coming up for a data-based role, you should expect and practice SQL interview questions .

SQL scenario-based questions don’t just test your knowledge of SQL queries, but also how well you can apply them in certain situations. The situations or scenarios used will typically mimic real-world problems, and you’ll need your technical and comprehension skills to tackle them.

These scenario-based SQL interview questions can be classified as easy, intermediate, or hard, based on: The difficulty in defining or understanding the problem. The complexity of the query is required to solve the problem.

We’ve compiled 13 SQL scenario-based interview questions you can practice to pinpoint your skill level and improve. The problems and solutions are well-explained to assist you in understanding how you can approach similar questions in an actual interview!

Easy SQL Scenario Based Interview Questions

Easy SQL scenario based interview questions will test your ability to apply simple queries and solve basic problems. There will be limited use of subqueries or multiple tables.

1. Write a query to display the highest salaries in each department.

More context: You have been provided with a table called ‘salaries’ that has three columns: EmployeeID, Department, and Salary.

The goal here is to list the single highest salary in each department. The output will consist of one column for the department and another column for the highest salary paid to a person in that department.

If there are four UNIQUE departments in the table, there should be four rows in the output.The GROUP BY function can be used to solve this problem as follows:

2. Several employees are yet to be assigned a department in your company. How would you find their details?

This question tests two different things. The first is a basic ability to filter data. You are looking through a list of employees to find only those who meet a particular criterion, in this case, if they have been assigned a department. The WHERE clause is commonly used for this purpose in SQL.

This question also tests whether you know how empty entries are stored in an SQL database. In SQL, empty values are stored as NULL , which can be used to locate empty entries by pairing the WHERE clause and the IS NULL operator.

For this question, you can find all employees without assigned departments with a query like:

3. Show all employees hired between 2015 and 2020.

When you need to find records of activities that took place within a specific time frame, you can use the BETWEEN comparator in SQL. It’s important to remember the format of the date used in the database. Here’s how you can write the query:

Note: Although dates are compared like other values, they still need the quotes around them to avoid errors.

4. How would you display the records of the top 15 students in the most recent exam?

Some of the tables you’ll be working with will contain thousands or even millions of records. You’ll often want to limit the amount of data displayed, so you can preview the relevant records efficiently.

This can be quickly achieved by employing the LIMIT clause in SQL. For this question, this could simply be:

While the above query will yield a result, it may not be the correct one. The question asks for the “top”’ students. This means that the student’s records must first be organized from highest to lowest based on their examination results before a LIMIT clause is applied.

This can be handled using the ORDER BY clause as shown below:

5. You have been tasked with finding new sales areas to target. Find the neighborhoods with no current users.

More context: You have been given a ‘users’ table and a ‘neighborhoods’ table.

The fact that you have two tables means you’ll probably need to use a JOIN clause, but that’s just half the story.

The neighborhoods being asked about in this question are those with no existing users. This means that you need to join the two tables and find the neighborhoods that appear on the ‘neighborhoods’ table but don’t have a single user in them in the ‘users’ table.

Assuming the ‘neighborhoods’ table is on the left, you can use LEFT JOIN to identify all the neighborhoods. The WHERE clause can then be used to filter out any neighborhood that shows up in the ‘users’ table.

Hint: The IS NULL operator can be handy in solving this problem.

6. Find the last record in a table of daily transactions.

More context: You have a single table with columns for the transaction ID, the time of the transaction, and the value of the transaction.

Like many questions in data science, there is more than one way to arrive at the answer to this question. A straightforward approach is to use a combination of data sorting and the limit function.

Date and time data can be sorted in ascending or descending order with the latest date-time considered the higher value. With this in mind, you can find the last transaction on a given day using the query below:

The query above will display only one record, and, thanks to the DESC clause, it will be the transaction with the highest date-time value.

An alternative approach to answering this question uses the MAX function. Can you figure out what the query will be?

7. Given the tables users and rides, write a query to report the distance traveled by each user in descending order.

To solve the problem of finding the total distance traveled by each user and then ordering the users by this distance, we start by calculating the total distance for all users. This is done by summing the distances of individual rides. We group these sums by each user’s ID to get the total distance traveled by each user.

Next, we retrieve the users’ names and join this information with the aggregated distance data. A left join is used to ensure users without any rides are included, with their distance shown as zero. We use a function to handle cases where users have taken no rides. Finally, we order the results by the total distance traveled in descending order, and by the users’ names in ascending order.

Intermediate SQL Scenario Based Interview Questions

Intermediate SQL scenario based interview questions will push your comprehension and query writing skills. These scenarios will usually require working with two or more tables. You’ll also want to take time to understand the problem posed and the possible ways of solving it, before jumping into possible solutions.

8. Write a query to display each city where the company has customers from two different states and the number of customers in each city.

More context: You have been provided with two tables with identical columns for two different states. The tables contain columns for customer ID, and their cities within the two states.

The count() function and the GROUP BY clause would have sufficed if all the records were on one table. However, this problem is slightly more challenging since the records are in two separate tables.

You can solve this question by taking advantage of subqueries. In this case, the UNION ALL clause can be used in the subquery to append the customers’ cities from the two tables. The customer IDs from each city are then grouped and counted.

The subquery used in the FROM clause must have an alias so it can be referenced in the query. The query would look as shown below:

Ensure you use the UNION ALL and not the UNION clause. The latter would append each city once to the combined list of cities, leading to each city showing just one customer.

9. How can you identify the customers who have made more than ‘X’ number of purchases in the past year?

Context: You have a purchases table and a users table

Once again, having two tables usually means needing to use a UNION or JOIN clause. In this case, the purchases table should have a customer ID column to uniquely identify which customer made which purchase.

Based on this, an INNER JOIN can be performed on the two tables, outputting a table with transaction IDs and the IDs of the customers who made the transactions.

The transactions can then be grouped by the customer IDs and the number of transactions associated with each customer added.

This question tests your ability to use JOINs and Subqueries, as well as your ability to apply the GROUP BY clause with a filter.

Hint: You may need to use the HAVING clause.

10. How would you write a query to find the second or third-highest bonuses paid out the previous month?

This type of question is common and it can be solved in different ways. Some solutions are not as elegant as others but the logic is easy to follow..

In the less elegant approach, you can use subqueries or nested queries to find the maximum bonus in a list of all bonuses that are less than the maximum bonus. The query to find the second-highest bonus using this approach looks like this:

By adding an identical subquery inside the first subquery, you can create the query that gives the third highest bonus. This can look like this:

The more elegant approach is to order the bonuses from highest to lowest, limit the result to one, and use an offset to choose the 2nd or 3rd highest bonuses as shown here:

You can change the offset to 2 to see the third-highest bonus or change the limit to 2 to view the second and third-highest bonuses at the same time.

11. Write a query to get the average commute time (in minutes) for each commuter in New York (NY) and the average commute time (in minutes) across all commuters in New York.

Write a query to calculate the average commute time for each commuter and the overall average commute time for all commuters in New York City. First, determine each commuter’s average commute time by computing the difference in minutes between the start and end times of their rides and then averaging these values.

Next, calculate the overall average commute time for all commuters by averaging the minute differences between start and end times across all rides. Finally, combine these results to show each commuter’s average commute time alongside the city-wide average commute time.

Advanced SQL Scenario Based Interview Question

At this level, you can expect SQL scenario based interview questions to be highly challenging. The scenario may not be well-defined and may require you to fill in some blanks with informed assumptions. Furthermore, the questions may be straightforward, but will require using specific functions that a beginner may not be familiar with.

12. Suggest a new online connection based on mutual likes and mutual friends.

More Context: You’ve been provided with four tables; ‘users’, ‘friends’, ‘likes’, and ‘blocked’. You are to suggest one friend to a user in the ‘users’ table based on the number of mutual friends and mutually liked pages. Current friends and anyone already blocked by the user are disqualified. Each mutual friend gives a potential friend 3 points, and each mutually liked page gives them 2 points.

This is the type of question you may encounter in an interview for a social media company where a key goal is to get users to connect with others. The idea here is that two people are more likely to connect if they like similar things or associate with the same people.

Solving this question will require you to identify the chosen user’s existing friends, their page likes, and users they’ve already blocked. You’ll also need to calculate the points to be assigned to the other users for mutual friends and likes so you can identify those with the highest points.

Finally, you’ll need to compile the list of potential friends, rank them, and eliminate existing friends or blocked individuals.

HINT: Common table expressions (CTEs) can help tackle the different operations needed to complete this task.

13. Calculate the daily three-day rolling average for deposits made to a bank .

More Context: You have been provided with a table containing columns for user ID, transaction amount, and time of transaction in datetime format. The positive transactions in the table are deposits and the negative transactions are withdrawals. The date in the output should be in the format ‘%Y-%m-%d’

There are different scenarios where calculating rolling averages would be important. A similar question could show up when interviewing for companies involved in stock or crypto trading. The most important columns in this table are for transactions and dates.

The first step to solving this problem is to sum the deposits made on each date. We can do this with the help of a CTE.

The above code outputs the total amount of deposits for each day. The next challenge is calculating the rolling 3-day average. If you had to use a JOIN clause, how would you solve this second part of the problem?

14. Write a query that repeats an integer as many times as the value of the integer itself.

More Context: You have a table with a column of integers. If the number in a row is 3, the integer 3 should be repeated three times in the output. This should be done sequentially for each integer in the table.

When an interviewer asks this type of question, they are probably testing your knowledge of advanced SQL queries. This type of problem can be solved in languages like Python using loops. In SQL you can employ the concept of recursion instead. This uses the RECURSIVE clause.

Recursion is a unique form of looping where the output from one iteration becomes the input for the next iteration.

In this scenario, the output of each iteration is a counter and the unchanged integer from the table. If the integer in the table is three, the first iteration will select 3 and a counter.

The value of the counter is the only one that changes in each iteration, but each time, the value of the number from the table, 3, is selected until the value of the counter is equal to it.

15. Write a query to calculate the percentage of users who recommended each page and are in the same postal code as that page.

More Context: There are three tables provided - ‘page_sponsorships’, ‘recommendations’, and ‘users’. The ‘sponsorship’ table has columns for page_id, postal_code, and price, i.e., the value of the sponsorship. The ‘recommendations’ table contains the user_id and the page_id of the recommended page. The ‘users’ table holds the user IDs and their postal codes. Pages are allowed to sponsor more than one postal code.

This type of question will test your comprehension. Several criteria must be met by the output and they require inputs from all three tables.

A distinction must be made between users who recommend a page and share its sponsored postal code and those who recommend the page and don’t share its postal code. The SUM CASE WHEN conditional statement can be handy here.

All three tables will have to be joined so the postal codes of the users who recommended a page and those sponsored by the page can be compared.

Grouping will have to be done based on two columns to separately aggregate users in different postal codes sponsored by the same page.

How To Solve Scenario Based Interview Questions in SQL

You may find scenario based SQL questions challenging, but with the right steps, you can learn to handle them with ease. Here are some ways you can improve your success rate with SQL scenario based questions.

1. Know what the question is asking before you start

Scenario based questions are used to test your understanding, and not just your ability to write SQL queries. Go over the question a few times and try to distill the question down to its simplest parts.

2. Clarify Assumptions

The interviewer may be targeting a specific answer when asking a specific question. Confirm any assumptions you may have and get a clearer picture of what the problem is.

3. Consider Similar Problems

The questions above reveal that many problems in SQL can be solved through similar steps. For example, questions involving multiple tables will require that the tables be joined in some way and a filter applied to the data left after joining.

4. Think of the steps logically

When working with subqueries, which will be often, it helps to think of the steps logically. Some actions cannot be performed unless other actions have been performed first. This can give you an entry point that allows you to work towards the final solution.

5. Practice writing queries

There are more scenarios that an interviewer can come up with than anyone could possibly cover. That’s why the best way of getting better at solving SQL scenario based interview questions is to answer as many practice questions as possible.

Practicing more SQL questions will allow you to master what you already know, and expose you to approaches you may never have considered.

Check out guides such as ‘ How to Use MAX CASE WHEN in SQL ’, ‘ How To Use CASE WHEN With SUM ’, and more for more resources.

• ▼SQL Exercises
• Introduction
• Retrieve data from tables
• Boolean and Relational Operators
• Wildcard and Special operators
• Aggregate Functions
• Formatting query output
• Query on Multiple Tables
• FILTERING and SORTING on HR Database
• SUBQUERIES on HR Database
• JOINS on HR Database
• SQL User Management
• ▼Movie Database
• BASIC QUERIES
• ▼Soccer Database
• ▼Hospital Database
• ▼Employee Database
• ▼AdventureWorks Database
• ▼SQL Challenges
• Challenges-1
• ..More to come..

SQL Exercises, Practice, Solution

What is sql.

SQL stands for Structured Query Language and it is an ANSI standard computer language for accessing and manipulating database systems. It is used for managing data in relational database management system which stores data in the form of tables and relationship between data is also stored in the form of tables. SQL statements are used to retrieve and update data in a database.

The best way we learn anything is by practice and exercise questions. We have started this section for those (beginner to intermediate) who are familiar with SQL . Hope, these exercises help you to improve your SQL skills. Currently following sections are available, we are working hard to add more exercises. Happy Coding!

You may read our SQL tutorial before solving the following exercises.

List of SQL Exercises

• SQL Retrieve data from tables [33 Exercises]
• SQL Boolean and Relational operators [12 Exercises]
• SQL Wildcard and Special operators [22 Exercises]
• SQL Aggregate Functions [25 Exercises]
• SQL Formatting query output [10 Exercises]
• SQL Quering on Multiple Tables [8 Exercises]
• FILTERING and SORTING on HR Database [38 Exercises]
• SQL JOINS [29 Exercises]
• SQL JOINS on HR Database [27 Exercises]
• SQL SUBQUERIES
• SQL SUBQUERIES [39 Exercises]
• SQL SUBQUERIES on HR Database [55 Exercises]
• SQL Union[9 Exercises]
• SQL View[16 Exercises]
• SQL User Account Management [16 Exercise]
• Movie Database
• BASIC queries on movie Database [10 Exercises]
• SUBQUERIES on movie Database [16 Exercises]
• JOINS on movie Database [24 Exercises]
• Soccer Database
• BASIC queries on soccer Database [29 Exercises]
• SUBQUERIES on soccer Database [33 Exercises]
• JOINS queries on soccer Database [61 Exercises]
• Hospital Database
• BASIC, SUBQUERIES, and JOINS [41 Exercises]
• Employee Database
• BASIC queries on employee Database [115 Exercises]
• SUBQUERIES on employee Database [77 Exercises]
• AdventureWorks Database:
• AdventureWorks Database [200 Exercises]
• SQL Challenges-1:
• SQL Challenges-1 [77 Exercises]
• More to come!

Structure of inventory database :

Structure of HR database :

Structure of movie database :

Structure of soccer database :

Structure of employee database :

Structure of hospital database :

Syntax diagram of SQL SELECT statement

You may download the structure and data of the tables of database on which SQL Exercises are built.

Please note that PostgreSQL 9.4 is used and the file which you would download is generated using pg_dump

Follow us on Facebook and Twitter for latest update.

• Weekly Trends and Language Statistics

Advanced SQL Practice: 10 Exercises with Solutions

• sql practice

Table of Contents

Practicing Your Way to SQL Proficiency

Exercise 1: list all clothing items, exercise 2: get all non-buying customers, exercise 3: select all main categories and their subcategories, exercise 4: organize runners into groups, exercise 5: how many runners participate in each event, exercise 6: group runners by main distance and age, exercise 7: list the top 3 most expensive orders, exercise 8: compute deltas between consecutive orders, exercise 9: compute the running total of purchases per customer, exercise 10: find the invitation path for each student, advancing one query at a time.

As SQL proficiency continues to be in high demand for data professionals and developers alike, the importance of hands-on practice cannot be emphasized enough. Read on to delve into the world of advanced SQL and engage in practical exercises to enhance your skills.

This article provides you with a collection of ten challenging SQL practice exercises specifically for those seeking to enhance their advanced SQL skills. The exercises cover a selection of SQL concepts and will help you refresh your advanced SQL knowledge. Each exercise is accompanied by a detailed solution, allowing you to test your knowledge and gain a deeper understanding of complex SQL concepts. The exercises come from our advanced SQL practice courses . If you want to see more exercises like this, check out these courses:

• Window Functions Practice Set
• 2021 Monthly SQL Practice Sets - Advanced
• 2022 Monthly SQL Practice Sets - Advanced

Find out how you can practice advanced SQL with our platform.

Let’s get started.

Practice is an integral component in mastering SQL; its importance cannot be overstated. The journey to becoming proficient in advanced SQL requires dedication, perseverance, and a strong commitment to continuous practice. By engaging in regular advanced SQL practice, individuals can sharpen their skills, expand their knowledge, and develop a deep understanding of the intricacies of data management and manipulation.

Advanced SQL exercises serve as invaluable tools, challenging learners to apply their theoretical knowledge in practical scenarios and further solidifying their understanding of complex concepts. With each session of dedicated SQL practice, you can discover efficient techniques and gain the confidence needed to tackle real-world data challenges.

Let’s go over the exercises and their solutions.

Advanced SQL Practice Exercises

We’ll present various advanced SQL exercises that cover window functions , JOINs, GROUP BY, common table expressions (CTEs), and more.

Section 1: Advanced SQL JOIN Exercises

In the following advanced SQL exercises, we’ll use a sportswear database that stores information about clothes, clothing categories, colors, customers, and orders. It contains five tables: color , customer , category , clothing , and clothing_order . Let's look at the data in this database.

The color table contains the following columns:

• id stores the unique ID for each color.
• name stores the name of the color.
• extra_fee stores the extra charge (if any) added for clothing ordered in this color.

In the customer table, you'll find the following columns:

• id stores customer IDs.
• first_name stores the customer's first name.
• last_name stores the customer's last name.
• favorite_color_id stores the ID of the customer's favorite color (references the color table).

The category table contains these columns:

• id stores the unique ID for each category.
• name stores the name of the category.
• parent_id stores the ID of the main category for this category (if it's a subcategory). If this value is NULL , it denotes that this category is a main category. Note: Values are related to those in the id column in this table.

The clothing table stores data in the following columns:

• id stores the unique ID for each item.
• name stores the name of that item.
• size stores the size of that clothing: S, M, L, XL, 2XL, or 3XL.
• price stores the item's price.
• color_id stores the item's color (references the color table).
• category_id stores the item's category (references the category table).

The clothing_order table contains the following columns:

• id stores the unique order ID.
• customer_id stores the ID of the customer ordering the clothes (references the customer table).
• clothing_id stores the ID of the item ordered (references the clothing table).
• items stores how many of that clothing item the customer ordered.
• order_date stores the date of the order.

Let’s do some advanced SQL exercises that focus on JOINs .

Display the name of clothing items (name the column clothes ), their color (name the column color ), and the last name and first name of the customer(s) who bought this apparel in their favorite color. Sort rows according to color, in ascending order.

Solution explanation:

We want to display the column values from three different tables ( clothing , color , and customer ), including information on which customer ordered a certain item (from the clothing_order table). Therefore, we need to join these four tables on their common columns.

First, we select from the clothing_order table (aliased as co ) and join it with the clothing table (aliased as cl ). We join the tables using the primary key column of the clothing table ( id ) and the foreign key column of the clothing_order table ( clothing_id ); this foreign key column links the clothing and clothing_order tables.

Next, we join the color table (aliased as col ) with the clothing table (aliased as cl ). Here we use the primary key column of the color table ( id ) and the foreign key column of the clothing table ( color_id ).

Finally, we join the customer table (aliased as cus ) with the clothing_order table (aliased as co ). The foreign key of the clothing_order table ( customer_id ) links to the primary key of the customer table ( id ).

The ON clause stores the condition for the JOIN statement. For example, an item from the clothing table with an id of 23 is joined with an order from the clothing_order table where the clothing_id value equals 23.

Follow this article to see more examples on JOINing three (or more) tables . And here is how to LEFT JOIN multiple tables .

Select the last name and first name of customers and the name of their favorite color for customers with no purchases.

Here we need to display customers’ first and last names from the customer table and their favorite color name from the color table. We must do it only for customers who haven’t placed any orders yet; therefore, we require information from the clothing_order table. So the next step is to join these three tables.

First, we join the customer table (aliased as cus ) with the color table (aliased as col ). To do that, we use the following condition: the primary key column of the color table ( id ) must be equal to the foreign key column of the customer table ( favorite_color_id ). That lets us select the favorite color name instead of its ID.

Here is how to ensure that we list only customers who haven’t placed any orders yet:

• We LEFT JOIN the clothing_order table (aliased as o ) with the customer table (aliased as cus ) to ensure that all rows from the customer table (even the ones with no match) are listed.
• In the WHERE clause, we define a condition to display only the rows with the customer_id column from the clothing_order table equal to NULL (meaning only the customers whose IDs are not in the clothing_order table will be returned).

There are different types of JOINs , including INNER JOIN , LEFT JOIN , RIGHT JOIN , and FULL JOIN . You can learn more by following the linked articles.

Select the name of the main categories (which have a NULL in the parent_id column) and the name of their direct subcategory (if one exists). Name the first column category and the second column subcategory.

Each category listed in the category table has its own ID (stored in the id column); some also have the ID of their parent category (stored in the parent_id column). Thus, we can link the category table with itself to list main categories and their subcategories.

The kind of JOIN where we join a table to itself is colloquially called a self join . When you join a table to itself, you must give different alias names to each copy of the table. Here we have one category table aliased as c1 and another category table aliased as c2 .

We select the name from the category table (aliased as c1 ) and ensure that we list only main categories by having its parent_id column equal to NULL in the WHERE clause. Next, we join the category table (aliased as c1 ) with the category table (aliased as c2 ). The latter one provides subcategories for the main categories. Therefore, in the ON clause, we define that the parent_id column of c2 must be equal to the id column of c1 .

Read this article to learn more about self joins .

The exercises in this section have been taken from our course 2021 Monthly SQL Practice Sets - Advanced . Every month we publish a new SQL practice course in our Monthly SQL Practice track; every odd-numbered month, the course is at an advanced level. The advanced SQL practice courses from 2021 have been collected in our 2021 Monthly SQL Practice Sets - Advanced course. Check it out to find more JOIN exercises and other advanced SQL challenges.

Section 2: Advanced GROUP BY Exercises

In the following advanced SQL exercises, we’ll use a sportsclub database that stores information about runners and running events. It contains three tables: runner , event , and runner_event . Let's look at the data in this database.

The runner table contains the following columns:

• id stores the unique ID of the runner.
• name stores the runner's name.
• main_distance stores the distance (in meters) that the runner runs during events.
• age stores the runner's age.
• is_female indicates if the runner is male or female.

The event table contains the following columns:

• id stores the unique ID of the event.
• name stores the name of the event (e.g. London Marathon, Warsaw Runs, or New Year Run).
• start_date stores the date of the event.
• city stores the city where the event takes place.

The runner_event table contains the following columns:

• runner_id stores the ID of the runner.
• event_id stores the ID of the event.

Let’s do some advanced SQL exercises that focus on GROUP BY .

Select the main distance and the number of runners that run the given distance ( runners_number ). Display only those rows where the number of runners is greater than 3.

Solution explanation :

We want to get the count of runners for each distance that they run. To do that, we need to group all runners by distance and use the COUNT() aggregate function to calculate how many runners are in each distance group.

We select the main_distance column and GROUP BY this column. Now when we use the COUNT() aggregate functions, it is going to give us the number of runners that match each main_distance value.

The GROUP BY clause is used to group rows from a table based on one or more columns. It divides the result set into subsets or groups, where each group shares the same values in the specified column(s). This allows us to perform aggregate functions (such as SUM() , COUNT() , AVG() , etc.) on each group separately.

Here are the most common GROUP BY interview questions .

To display only the groups with more than three runners, we use a HAVING clause that filters the values returned by the COUNT() aggregate function.

The HAVING clause is often used together with the GROUP BY clause to filter the grouped data based on specific conditions. It works similarly to the WHERE clause, but it operates on the grouped data rather than individual rows. Check out this article to learn more about the HAVING clause .

Display the event name and the number of club members that take part in this event (call this column runner_count ). Note that there may be events in which no club members participate. For these events, the runner_count should equal 0.

Here we want to display the event name from the event table and the number of participants from the runner table. The event and runner tables are linked by a many-to-many relation; to join these tables, we also need the runner_event table that relates events and runners.

First, we select from the event table. Then, we LEFT JOIN it with the runner_event table, which is further LEFT JOINed with the runner table. Why do we use the LEFT JOIN here? Because we want to ensure that all events (even the ones with no participants) are displayed.

We select the event name and the count of all participants; therefore, we need to GROUP BY the event name to get the count of participants per event. Please note that we use COUNT(runner_id) instead of COUNT(*) . This is to ensure that we display zero for events with no participants (i.e. for events that do not link to any runner_id ). You can read more about different variants of the COUNT() function here .

Display the distance and the number of runners there are for the following age categories: under 20, 20–29, 30–39, 40–49, and over 50. Use the following column aliases: under_20 , age_20_29 , age_30_39 , age_40_49 , and over_50 .

This is similar to Exercise 4 – we want to know the number of runners per distance value. So we select the main_distance column and GROUP BY this column. Then, we use several COUNT() aggregate functions to get the number of runners per distance. However, here we need to further divide the runners according to their age.

The CASE WHEN statement comes in handy here, as it can be used to evaluate conditions and return different values based on the results of those conditions. We can pass it as an argument to the COUNT() aggregate function to get the number of runners fulfilling a given condition. Let’s see how that works.

This CASE WHEN statement returns id only when a runner’s age is greater than or equal to 20 and less than 30. Otherwise, it returns NULL . When wrapped in the COUNT() aggregate function, it returns the count of runners fulfilling the condition defined in the CASE WHEN statement.

To get the number of runners for each of the five age groups, we need to use as many COUNT() functions and CASE WHEN statements as we have age groups. You can read about counting rows by combining CASE WHEN and GROUP BY here .

Section 3: Advanced Window Functions Exercises

In the following advanced SQL exercises, we’ll use a Northwind database for an online shop with numerous foods. It contains six tables: customers , orders , products , categories , order_items , and channels . Let's look at the data in this database.

The customers table has 15 columns:

• customer_id stores the ID of the customer.
• email stores the customer’s email address.
• full_name stores the customer’s full name.
• address stores the customer’s street and house number.
• city stores the city where the customer lives.
• region stores the customer’s region (not always applicable).
• postal_code stores the customer’s ZIP/post code.
• country stores the customer’s country.
• phone stores the customer’s phone number.
• registration_date stores the date on which the customer registered.
• channel_id stores the ID of the channel through which the customer found the shop.
• first_order_id stores the ID of the first order made by the customer.
• first_order_date stores the date of the customer’s first order.
• last_order_id stores the ID of the customer’s last (i.e. most recent) order.
• last_order_date stores the date of the customer’s last order.

The orders table has the following columns:

• order_id stores the ID of the order.
• customer_id stores the ID of the customer who placed the order.
• order_date stores the date when the order was placed.
• total_amount stores the total amount paid for the order.
• ship_name stores the name of the person to whom the order was sent.
• ship_address stores the address (house number and street) where the order was sent.
• ship_city stores the city where the order was sent.
• ship_region stores the region in which the city is located.
• ship_postalcode stores the destination post code.
• ship_country stores the destination country.
• shipped_date stores the date when the order was shipped.

The products table has the following columns:

• product_id stores the ID of the product.
• product_name stores the name of the product.
• category_id stores the category to which the product belongs.
• unit_price stores the price for one unit of the product (e.g. per bottle, pack, etc.).
• discontinued indicates if the product is no longer sold.

The categories table has the following columns:

• category_id stores the ID of the category.
• category_name stores the name of the category.
• description stores a short description of the category.

The order_items table has the following columns:

• order_id stores the ID of the order in which the product was bought.
• product_id stores the ID of the product purchased in the order.
• unit_price stores the per-unit price of the product. (Note that this can be different from the price in the product’s category; the price can change over time and discounts can be applied.)
• quantity stores the number of units bought in the order.
• discount stores the discount applied to the given product.

The channels table has the following columns:

• channel_name stores the name of the channel through which the customer found the shop.

Let’s do some advanced SQL exercises that focus on window functions.

Create a dense ranking of the orders based on their total_amount . The bigger the amount, the higher the order should be. If two orders have the same total_amount , the older order should go higher (you'll have to add the column order_date to the ordering). Name the ranking column rank . After that, select only the orders with the three highest dense rankings . Show the rank, order_id , and total_amount .

Let’s start with the first part of the instruction. We want to create a dense ranking of orders based on their total_amount (the greater the value, the higher the rank) and their order_date value (the older the date, the higher the rank). Please note that the rank value may be duplicated only when total_amount and order_date columns are both equal for more than one row.

To do that, we use the DENSE_RANK() window function. In its OVER() clause, we specify the order: descending for total_amount values and ascending for order_date values. We also display the order_id and total_amount columns from the orders table.

Until now, we listed all orders along with their dense rank values. But we want to see only the top 3 orders (where the rank column is less than or equal to 3). Let’s analyze the steps we take from here:

• We define a Common Table Expression (CTE) using this SELECT statement – i.e. we use the WITH clause followed by the CTE’s name and then place the SELECT statement within parentheses.
• Then we select from this CTE, providing the condition for the rank column in the WHERE clause.

You may wonder why we need such a complex syntax that defines a CTE and then queries it. You may say that we could set the condition for the rank column in the WHERE clause of the first SELECT query. Well, that’s not possible because of the SQL query order of execution.

We have to use the Common Table Expression here because you can’t use window functions in the WHERE clause. The order of operations in SQL is as follows:

• FROM , JOIN
• Aggregate functions
• Window functions

You may only use window functions in SELECT and ORDER BY clauses. If you want to refer to window functions in the WHERE clause, you must place the window function computation in a CTE (like we did in our example) or in a subquery and refer to the window function in the outer query.

Follow this article to learn more about CTEs and recursive CTEs .

To give you some background on the available ranking functions , there are three functions that let you rank your data: RANK() , DENSE_RANK() , and ROW_NUMBER() . Let’s see them in action.

The RANK() function assigns the same rank if multiple consecutive rows have the same value. Then, the next row gets the next rank as if the previous rows had distinct values. Here, the ranks 1,1,1 are followed by 4 (as if it was 1,2,3 instead of 1,1,1 ).

The DENSE_RANK() function also assigns the same rank if multiple consecutive rows have the same value. Then, the next row gets the next rank one greater than the previous one. Here, 1,1,1 is followed by 2.

The ROW_NUMBER() function assigns consecutive numbers to each next row without considering the row values.

Here is an article on how to rank data . You can also learn more about differences between SQL’s ranking functions .

In this exercise, we're going to compute the difference between two consecutive orders from the same customer.

Show the ID of the order ( order_id ), the ID of the customer ( customer_id ), the total_amount of the order, the total_amount of the previous order based on the order_date (name the column previous_value ), and the difference between the total_amount of the current order and the previous order (name the column delta ).

Here we select the order ID, customer ID, and total amount from the orders table. The LAG() function fetches the previous total_amount value. In the OVER() clause, we define the LAG() function separately for each customer and order the outcome by an order date. Finally, we subtract the value returned by the LAG() function from the total_amount value for each row to get the delta.

The previous_value column stores null for the first row, as there are no previous values. Therefore, the delta column is also null for the first row. The following delta column values store the differences between consecutive orders made by the same customer.

It is worth mentioning that a delta represents the difference between two values. By calculating the delta between daily sales amounts, we can determine the direction of sales growth/decline on a day-to-day basis.

Follow this article to learn more about calculating differences between two rows . And here is how to compute year-over-year differences .

For each customer and their orders, show the following:

• customer_id – the ID of the customer.
• full_name – the full name of the customer.
• order_id – the ID of the order.
• order_date – the date of the order.
• total_amount – the total spent on this order.
• running_total – the running total spent by the given customer.

Sort the rows by customer ID and order date.

A running total refers to the calculation that accumulates the values of a specific column or expression as rows are processed in a result set. It provides a running sum of the values encountered up to the current row. A running total is calculated by adding the current value to the sum of all previous values. This can be particularly useful in various scenarios, such as tracking cumulative sales, calculating running balances or analyzing cumulative progress over time.

Follow this article to learn more about computing a running total . And here is an article about computing running averages .

We select customer ID, order ID, order date, and order total from the orders table. Then, we join the orders table with the customers table on their respective customer_id columns so we can display the customer's full name.

We use the SUM() window function to calculate the running total for each customer separately ( PARTITION BY orders.customer_id ) and then order ascendingly by date ( ORDER BY orders.order_date ).

Finally, we order the output of this query by customer ID and order date.

Section 4: Advanced Recursive Query Exercises

In the following advanced SQL exercises, we’ll use a website database that stores information about students and courses. It contains three tables: student , course , and student_course . Let's look at the data in this database.

The student table contains the following columns:

• id stores the unique ID number for each student.
• name stores the student's name.
• email stores the student's email.
• invited_by_id stores the ID of the student that invited this student to the website. If the student signed up without an invitation, this column will be NULL.

The course table consists of the following columns:

• id stores the unique ID number for each course.
• name stores the course's name.

The student_course table contains the following columns:

• id stores the unique ID for each row.
• student_id stores the ID of the student.
• course_id stores the ID of the course.
• minutes_spent stores the number of minutes the student spent on the course.
• is_completed is set to True when the student finishes the course.

The exercises in this section have been taken from our Window Functions Practice Set . In this set, you will find more window function exercises on databases that store retail, track competitions, and website traffic.

Let’s do some advanced SQL exercises that focus on recursive queries.

Show the path of invitations for each student (name this column path ). For example, if Mary was invited by Alice and Alice wasn't invited by anyone, the path for Mary should look like this: Alice->Mary .

Include each student's id , name , and invited_by_id in the results.

This exercise requires us to create a custom value for the path column that contains the invitation path for each customer. For example, Ann Smith was invited by Veronica Knight , who in turn was invited by Karli Roberson ; hence, we get the path column as Karli Roberson->Veronica Knight->Ann Smith for the name Ann Smith .

As you may notice, we need a recursion mechanism to dig down into the invitation path. We can write a recursive query by defining it with the WITH RECURSIVE statement, followed by the query name.

The content of the hierarchy recursive query is as follows:

• We select the id , name , and invited_by_id columns from the student table. Then, we use the CAST() function to cast the name column type to the TEXT data type, ensuring smooth concatenation (with -> and the following names) in the main query. The WHERE clause condition ensures that only students who haven’t been invited are listed by this query.
• The UNION ALL operator combines the result sets of two or more SELECT statements without removing duplicates. Here the queries on which UNION ALL is performed have the same sets of four columns; the result set of one is appended to the results set of another.
• In the next SELECT statement, we again select the id , name , and invited_by_id columns from the student table. Then, we concatenate the path column (that comes from the hierarchy recursive query as defined in the first SELECT statement) with the -> sign and the student name. To accomplish this concatenation, we select from both the student table and the hierarchy recursive query.(This is where the recursive mechanism comes into play.) In the WHERE clause, we define that the invited_by_id column of the student table is equal to the id column of the hierarchy recursive query, so we get the student name who invited the current student; on the next iteration, we get the name of the student who invited that student, and so on.

This is called a recursive query, as it queries itself to work its way down the invitation path.

The advanced SQL exercises presented in this article provide a comprehensive platform for honing your SQL skills, one query at a time. By delving into window functions, JOINs , GROUP BY , and more, you have expanded your understanding of complex SQL concepts and gained hands-on experience in solving real-world data challenges.

Practice is the key to mastering SQL skills. Through consistent practice, you can elevate your proficiency and transform your theoretical knowledge into practical expertise. This article showcased exercises from our courses; you can discover more exercises like this by enrolling in our:

Sign up now and get started for free! Good luck!

You may also like

How Do You Write a SELECT Statement in SQL?

What Is a Foreign Key in SQL?

Enumerate and Explain All the Basic Elements of an SQL Query