- SQL Server training
- Write for us!
SQL Practice – common questions and answers for SQL skills
This article will show some important questions and answers to practice SQL.
Introduction
SQL is the base to handle different databases. Even some of the NoSQL databases use an extension of SQL to query data. Data Scientists, BI Analysts, BI Developers, DBAs, Database Developers, Data Engineers, Reporting Analysts, and several other jobs require SQL knowledge as part of the knowledge required. For data management, SQL knowledge is the base to handle databases and the numbers of jobs related to databases are growing each day. The software industry, including databases, is the world’s most in-demand profession. That is why in this article, we will show some questions and answers to practice SQL.
Requirements
In order to start, you need the SQL Server installed and the Adventureworks sample database installed. The following links can help you if you do not have them installed:
- How to install SQL Server developer edition
- Install and configure the AdventureWorks2016 sample database
Q1. Please select all the information of persons table that the name starts with A
A1. You will show all the columns with the select * and the where [FirstName] like ‘a%’ will filter the data to all the persons that the name starts with a. The query used is the following:
The LIKE operator is very common in SQL queries. The like ‘a%’ will show all the first names that start with the letter a. There are several other operators that you need to practice like the EXISTS, IN, =, <>, ANY. The following link provides more information about the operators:
- Operators (Transact-SQL)
Note that the use of * is a bad practice for big tables, but in this case, it is a small table, so it will not impact performance. In general, try to select only the columns required and not all.
Q2. Create a store procedure that receives the first name of the person table as input and the last name as output.
In SQL practice, it is necessary to mention the stored procedures because they are frequently used in SQL Server. The stored procedures are T-SQL sentences that cannot be applied to other databases like Oracle, MySQL, PostgreSQL. The following sample of code shows how to create a stored procedure named GetLastName. It receives the parameter @firstname and returns the last name of the provided firstname. It queries the Person table:
To call the stored procedure using T-SQL, you will need to declare a variable to store the output variable. We use the execute command to call a stored procedure. John is the parameter value for the first name and the last name is the output of the stored procedure:
The result displayed by this store procedure invocation will be something like this:
For more information about creating stored procedures, please refer to this link:
- Learn SQL: User-Defined Stored Procedures
- SQL Server stored procedures for beginners
- CREATE PROCEDURE (Transact-SQL)
Q3. Which query would you execute to delete all the rows from the person table with minimal bulk-logged activity?
A3. The truncate table sentence removes data without logging individual row deletions. It is the most efficient way to remove all the data. The delete command, on the other hand, logs a lot of data if we have multiple rows and can consume a lot of space in the transaction log files. So, the code will be the following:
Truncate table person.person
To practice SQL, try the truncate and delete statements. For more detailed information about the differences between the truncate and the delete sentences, refer to this link:
- The internals of SQL Truncate and SQL Delete statements
Q4. Create a query to show the account number and customerid from the customer table for the customer without sales orders.
A4. The query would be something like this:
The result displayed will be something like this:
We are using the left join to look for all the customers without sales, so the salesorderid will be null. In SQL practice, you need to know the use of the different JOINS like the LEFT JOIN, RIGHT JOIN, CROSS JOIN. We created some articles about the different types of JOINs here. It is very important for you to check, practice, and study all the options:
- Learn SQL: Join multiple tables
- Learn SQL: INNER JOIN vs LEFT JOIN
- SQL CROSS JOIN with examples
We could also use the EXISTS or the IN operators instead of using the JOINS. The performance is different according to the scenario. The performance in queries is out of the scope of this SQL practice. However, we have an entire article about this topic here:
- How to design SQL queries with better performance: SELECT * and EXISTS vs IN vs JOINs
Q5. You have a table with some of the temperatures in Celsius of some patients. Create a function or a stored procedure to get the convert Fahrenheit to Celsius. Would you use a function or a stored procedure?
A5. Basically, the question here is to create a stored procedure or a function to convert from Celsius to Fahrenheit. If you already have a table with some rows in Celsius, the easiest option is to use a function. The function could be something like this:
Invoking a function is easier than a stored procedure. For more information about the use of functions vs stored procedures, refer to this link:
- Functions vs stored procedures in SQL Server
Q6. Create a query to show the top 10 customerIDs of users with more Orders.
A6. For this practice test, we will use the TOP 10 to get the customer IDs with more orders. We will use the SUM to SUM the Order Quantity column. Note that for aggregated functions like the SUM, the alias is needed to define the column name in the query. Also, the Sum is grouped by the customer ID. Usually aggregated functions come with the GROUP BY clause. Finally, we are using the order by to order the result in descendant order:
For more information about aggregated functions like the SUM, MAX, MIN, AVG, refer to this link:
- Aggregate Functions (Transact-SQL)
In this article, we show different questions for SQL practice. We show some questions and answers to practice SQL and improve the knowledge. If you have more questions about this topic, do not hesitate to contact us.
- Recent Posts
- PSQL stored procedures overview and examples - February 14, 2023
- How to create Power BI reports - January 11, 2023
- SQL Server Tutorial - December 23, 2022
Related posts:
- A complete guide to T-SQL Metadata Functions in SQL Server
- Top SQL Server Books
- An overview of the SQL Server Update Join
- FOR XML PATH clause in SQL Server
- Creating and using CRUD stored procedures
© 2023 Quest Software Inc. ALL RIGHTS RESERVED. | GDPR | Terms of Use | Privacy
- ▼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.
SQL: Tips of the Day
Ignore case while searching for a string
Ref: https://bit.ly/3t2SMjB
- Weekly Trends
- Java Basic Programming Exercises
- SQL Subqueries
- Adventureworks Database Exercises
- C# Sharp Basic Exercises
- SQL COUNT() with distinct
- JavaScript String Exercises
- JavaScript HTML Form Validation
- Java Collection Exercises
- SQL COUNT() function
- SQL Inner Join
- JavaScript functions Exercises
- Python Tutorial
- Python Array Exercises
- SQL Cross Join
- C# Sharp Array Exercises
50 SQL Query Questions and Answers for Practice
- category SQL Interview
- user Meenakshi Agarwal
If you want to improve SQL skills, then install a SQL package like MySQL and start practicing with it. To get you started, we’ve outlined a few SQL query questions in this post.
Solving practice questions is the fastest way to learn any subject. That’s why we’ve selected a set of 50 SQL queries that you can use to step up your learning. We’ve also given SQL scripts to create the test data . So, you can use them to create a test database and tables.
Most of the SQL query questions we’ve filtered out of interviews held by top IT MNC like Flipkart and Amazon. So you’ll gain real-time experience by going through them.
Also, we recommend that you first try to form queries by yourself rather than just reading them from the post. Try to find answers on your own.
But you can’t start until the required sample data is not in place. You can check out the tables below that we’ve provided for practice. So first of all, you need to create the test data in your database software.
By the way, we have a bunch of other posts available for SQL interview preparation. So if you are interested, then follow the link given below.
💡 Most Frequently Asked SQL Interview Questions .
Let’s Begin Learning SQL.
Prepare Sample Data To Practice SQL Skill.
Sample table – worker, sample table – bonus, sample table – title.
To prepare the sample data, you can run the following queries in your database query executor or on the SQL command line. We’ve tested them with MySQL Server 5.7 and MySQL Workbench 6.3.8 query browser. You can also download these Softwares and install them to carry on the SQL exercise.
SQL Script to Seed Sample Data.
Once above SQL would run, you’ll see a result similar to the one attached below.
50 SQL Query Questions and Answers for Practice.
Q-1. write an sql query to fetch “first_name” from worker table using the alias name as <worker_name>..
The required query is:
Q-2. Write an SQL query to fetch “FIRST_NAME” from Worker table in upper case.
Q-3. write an sql query to fetch unique values of department from worker table., q-4. write an sql query to print the first three characters of first_name from worker table., q-5. write an sql query to find the position of the alphabet (‘a’) in the first name column ‘amitabh’ from worker table..
- The INSTR method is in case-sensitive by default.
- Using Binary operator will make INSTR work as the case-sensitive function.
Q-6. Write an SQL query to print the FIRST_NAME from Worker table after removing white spaces from the right side.
Q-7. write an sql query to print the department from worker table after removing white spaces from the left side., q-8. write an sql query that fetches the unique values of department from worker table and prints its length., q-9. write an sql query to print the first_name from worker table after replacing ‘a’ with ‘a’., q-10. write an sql query to print the first_name and last_name from 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 name as “vipul” and “satish” from worker table., q-14. write an sql query to print details of workers excluding first names, “vipul” and “satish” from 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 have joined in feb’2014., 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 no. of workers for each department in the descending order., q-24. write an sql query to print details of the workers who are also managers., q-25. write an sql query to fetch duplicate records having matching data in some fields of a table., q-26. write an sql query to show only odd rows from a table., q-27. write an sql query to show only even rows from a table., q-28. write an sql query to clone a new table from another table..
The general query to clone a table with data is:
The general way to clone a table without information is:
An alternate way to clone a table (for MySQL) without 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..
Following MySQL query returns the current date:
Following MySQL query returns the current date and time:
Following SQL Server query returns the current date and time:
Following Oracle query returns the current date and time:
Q-32. Write an SQL query to show the top n (say 10) records of a table.
Following MySQL query will return the top n records using the LIMIT method:
Following SQL Server query will return the top n records using the TOP command:
Following Oracle query will return the top n records with the help of ROWNUM:
Q-33. Write an SQL query to determine the nth (say n=5) highest salary from a table.
The following MySQL query returns the nth highest salary:
The following SQL Server query returns the nth highest salary:
Q-34. Write an SQL query to determine the 5th highest salary without using 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 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 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% records from a table., q-40. write an sql query to fetch the departments that have less than five people in it., 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 name 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 questions and answers for practice..
Let us take a pause here. But we’ll come back with more challenging questions on SQL queries in our next post.
Hope, you’d fun learning through the SQL exercises.
If you find something new to learn today, then do share it with others. And, follow us on our social media ( Facebook / Twitter ) accounts to see more of this.
TechBeamers
You Might Also Like
Top SQL Interview Questions One Should Know Beforehand
Top 30 PL SQL Interview Questions and Answers for Freshers
SQL Exercises – Complex Queries
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
Get your certification today!
Get certified by completing a course today!
Report Error
If you want to report an error, or if you want to make a suggestion, do not hesitate to send us an e-mail:
[email protected]
Your Suggestion:
Thank you for helping us.
Your message has been sent to W3Schools.
Top Tutorials
Top references, top examples, web certificates, get certified.
- Entity Framework
- Responsive Layouts
- Interview Question
- Online Test
- SQL Server Tutorial
- Insert command in SQL
- Constraints in SQL
- Primary V/S Unique
- Primary V/S Foreign
- Delete V/S Truncate
- Aggregate Function
- Like Operator
- Case in SQL
- Range in SQL
- Stored Procedure
- Stored Procedure V/S Function
- Triggers in SQL
- XML in SQL Server
- SQL Practice Paper 1
- SQL Interview Question
SQL Queries for Practice with Answer
Written By:- Isha Malhotra
SQL Queries with Answer
In this tutorial I am representing some sql queries with answer . It will help all to improve your sql skills. These SQL Queries categorized into two part. In first part i have discussed basic sql queries with answers. in Second part i have discussed nested queries .
For this tutorial i am using following tables:-
Table Name:- Employee
Simple queries, 1. select the detail of the employee whose name start with p..
2. How many permanent candidate take salary more than 5000.
3. Select the detail of employee whose emailId is in gmail.
4. Select the details of the employee who work either for department E-104 or E-102.
5. What is the department name for DeptID E-102?
6. What is total salarythat is paid to permanent employees?
7. List name of all employees whose name ends with a.
8. List the number of department of employees in each project.
9. How many project started in year 2010.
10. How many project started and finished in the same year.
11. select the name of the employee whose name's 3rd charactor is 'h'.
Nested Queries
1. select the department name of the company which is assigned to the employee whose employee id is grater 103..
2. Select the name of the employee who is working under Abhishek.
3. Select the name of the employee who is department head of HR.
4. Select the name of the employee head who is permanent.
5. Select the name and email of the Dept Head who is not Permanent.
6. Select the employee whose department off is monday
7. select the indian clinets details.
8. select the details of all employee working in development department.
Video Tutorial for Basic SQL Queries
SQL Queries based on Aggregate Function
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.
Freelance SQL Developer
Duy has 20+ years of software development experience using Microsoft's technology stack, primarily with .NET. He builds high-quality and high-performance back-end systems and creates web applications with good UX using modern frameworks like Angular or React. Duy's proactive and results-oriented with a love of not only writing but also removing code to ensure efficient, stable, and flexible software is delivered within the projected scope and budget.
Matthew Newman
Matthew has over 15 years of experience in database management and software development, with a strong focus on full-stack web applications. He specializes in Django and Vue.js with expertise deploying to both server and serverless environments on AWS. He also works with relational databases and large datasets.
Vedansh Garg
Vedansh is a senior software engineer at HackerRank and has 5+ years of experience in writing scalable, maintainable code in Rails and Python. He has built apps that are being used by 9 million+ developers around the world. He strongly believes in test-driven development (TDD) and communicates really well, and has worked with teams of different sizes across the globe. Vedansh also comes with a strong background in freelancing with various startups in the valley.
Toptal Connects the Top 3% of Freelance Talent All Over The World.
Join the Toptal community.
Introduction to SQL
- What is a Database? Definition, Types and Components
- What is SQL and how to get started with it?
- SQL Basics – One Stop Solution for Beginners
What are SQL Operators and how do they work?
- Understanding SQL Data Types – All You Need To Know About SQL Data Types
SQL Tutorial : One Stop Solution to Learn SQL
Dbms tutorial : a complete crash course on dbms.
- CREATE TABLE in SQL – Everything You Need To Know About Creating Tables in SQL
- What is a Schema in SQL and how to create it?
- What is a Cursor in SQL and how to implement it?
Top 10 Reasons Why You Should Learn SQL
- Learn how to use SQL SELECT with examples
- SQL Functions: How to write a Function in SQL?
What is SQL Regex and how to implement it?
- SQL UPDATE : Learn How To Update Values In A Table
- SQL Union – A Comprehensive Guide on the UNION Operator
What are Triggers in SQL and how to implement them?
- INSERT Query SQL – All You Need to Know about the INSERT statement
- How To Use Alter Table Statement In SQL?
What is Normalization in SQL and what are its types?
How to perform if statement in sql.
- What are SQL constraints and its different types?
- Learn How To Use CASE Statement In SQL
Primary Key In SQL : Everything You Need To Know About Primary Key Operations
- Foreign Key SQL : Everything You Need To Know About Foreign Key Operations
- SQL Commands - A Beginner's Guide To SQL
- How To Rename a Column Name in SQL?
- How to retrieve a set of characters using SUBSTRING in SQL?
- What is the use of SQL GROUP BY statement?
- How To Use ORDER BY Clause In SQL?
- How to use Auto Increment in SQL?
- Everything You Need to Know About LIKE Operator in SQL
- What is an index in SQL?
Understanding SQL Joins – All You Need To Know About SQL Joins
- Differences Between SQL & NoSQL Databases – MySQL & MongoDB Comparison
- What is Database Testing and How to Perform it?
- SQL Pivot – Know how to convert rows to columns
Introduction To MySQL
- What is MySQL? – An Introduction To Database Management Systems
- How To Install MySQL on Windows 10? – Your One Stop Solution To Install MySQL
- MySQL Tutorial - A Beginner's Guide To Learn MySQL
- MySQL Data Types – An Overview Of The Data Types In MySQL
- How To Use CASE Statement in MySQL?
- What is the use of DECODE function in SQL?
What are basic MongoDB commands and how to use them?
- SSIS Tutorial For Beginners: Why, What and How?
- Learn About How To Use SQL Server Management Studio
- SQLite Tutorial: Everything You Need To Know
- What is SQLite browser and how to use it?
- MySQL Workbench Tutorial – A Comprehensive Guide To The RDBMS Tool
Postgre SQL
- PostgreSQL Tutorial For Beginners – All You Need To Know About PostgreSQL
- PL/SQL Tutorial : Everything You Need To Know About PL/SQL
- Learn How To Handle Exceptions In PL/SQL
SQL Interview Questions
- Top 115 SQL Interview Questions You Must Prepare In 2023
- Top 50 MySQL Interview Questions You Must Prepare In 2023
- Top 50 DBMS Interview Questions You Need to know in 2023
Top 30 SQL Query Interview Questions You Must Practice In 2023
SQL or Structured Query Language is a standard language for dealing with relational databases. With the humongous amount of data present, it is very important for us to understand how to use queries to retrieve the required data. In this article on SQL Query Interview Questions, I will discuss a few queries which you must practice to become a Database Administrator and will also help you ace your interviews.
Top SQL Query Interview Questions
For, your better understanding, I will be considering the following tables to write queries.
EmployeeInfo Table:
Employeeposition table:.
Let us start by taking a look at some of the most frequently asked SQL Query interview questions,
- Write a query to fetch the EmpFname from the EmployeeInfo table in the upper case and use the ALIAS name as EmpName.
- Write a query to fetch the number of employees working in the department ‘HR’.
- Write a query to get the current date.
- Write a query to retrieve the first four characters of EmpLname from the EmployeeInfo table.
- Write a query to fetch only the place name(string before brackets) from the Address column of EmployeeInfo table.
- Write a query to create a new table that consists of data and structure copied from the other table.
- Write q query to find all the employees whose salary is between 50000 to 100000.
- Write a query to find the names of employees that begin with ‘S’
- Write a query to fetch top N records.
- Write a query to retrieve the EmpFname and EmpLname in a single column as “FullName”. The first name and the last name must be separated with space.
Q1. Write a query to fetch the EmpFname from the EmployeeInfo table in upper case and use the ALIAS name as EmpName.
Q2. write a query to fetch the number of employees working in the department ‘hr’., q3. write a query to get the current date..
You can write a query as follows in SQL Server:
You can write a query as follows in MySQL :
Q4. Write a query to retrieve the first four characters of EmpLname from the EmployeeInfo table.
Q5. write a query to fetch only the place name(string before brackets) from the address column of employeeinfo table..
Using the MID function in MySQL
Q6. Write a query to create a new table which consists of data and structure copied from the other table.
Using the SELECT INTO command:
Using the CREATE command in MySQL:
Q7. Write q query to find all the employees whose salary is between 50000 to 100000.
Q8. write a query to find the names of employees that begin with ‘s’, q9. write a query to fetch top n records..
By using the TOP command in SQL Server:
By using the LIMIT command in MySQL:
Q10. Write a query to retrieve the EmpFname and EmpLname in a single column as “FullName”. The first name and the last name must be separated with space.
Q11. write a query find number of employees whose dob is between 02/05/1970 to 31/12/1975 and are grouped according to gender, q12. write a query to fetch all the records from the employeeinfo table ordered by emplname in descending order and department in the ascending order..
To order the records in ascending and descnding order, you have to use the ORDER BY statement in SQL .
Q13. Write a query to fetch details of employees whose EmpLname ends with an alphabet ‘A’ and contains five alphabets.
To fetch details mathcing a certain value, you have to use the LIKE operator in SQL .
Q14. Write a query to fetch details of all employees excluding the employees with first names, “Sanjay” and “Sonia” from the EmployeeInfo table.
Top 10 technologies to learn in 2022 | edureka, q15. write a query to fetch details of employees with the address as “delhi(del)”., q16. write a query to fetch all employees who also hold the managerial position., q17. write a query to fetch the department -wise count of employees sorted by department’s count in ascending order., q18. write a query to calculate the even and odd records from a table..
To retrieve the even records from a table, you have to use the MOD() function as follows:
Similarly, to retrieve the odd records from a table, you can write a query as follows:
Q19. Write a SQL query to retrieve employee details from EmployeeInfo table who have a date of joining in the EmployeePosition table.
Q20. write a query to retrieve two minimum and maximum salaries from the employeeposition table..
To retrieve two minimum salaries, you can write a query as below:
Q21. Write a query to find the Nth highest salary from the table without using TOP/limit keyword.
Q22. write a query to retrieve duplicate records from a table., q23. write a query to retrieve the list of employees working in the same department., q24. write a query to retrieve the last 3 records from the employeeinfo table., q25. write a query to find the third-highest salary from the empposition table., q26. write a query to display the first and the last record from the employeeinfo table..
To display the first record from the EmployeeInfo table, you can write a query as follows:
To display the last record from the EmployeeInfo table, you can write a query as follows:
Q27. Write a query to add email validation to your database
Q28. write a query to retrieve departments who have less than 2 employees working in it., q29. write a query to retrieve emppostion along with total salaries paid for each of them., q30. write a query to fetch 50% records from the employeeinfo table..
So this brings us to the end of the SQL Query Interview Questions article. I hope this set of SQL Query Interview Questions will help you ace your job interview. All the best for your interview!
Check out this MySQL DBA Certification Training by Edureka, a trusted online learning company with a network o f more than 250,000 satisfied learners spread across the globe. This course trains you on the core concepts & advanced tools and techniques to manage data and administer the MySQL Database. It includes hands-on learning on concepts like MySQL Workbench, MySQL Server, Data Modeling, MySQL Connector, Database Design, MySQL Command line, MySQL Functions, etc. End of the training you will be able to create and administer your own MySQL Database and manage data.
If you wish to learn Microsoft SQL Server and build a career in the relational databases, functions, queries, variables, etc domain, then check out our interactive, live-online SQL Certification here, which comes with 24*7 support to guide you throughout your learning period.
Got a question for us? Please mention it in the comments section of this “SQL Query Interview Questions ” article and we will get back to you as soon as possible.
Recommended videos for you
Introduction to mongodb, build application with mongodb, recommended blogs for you, mysql tutorial – a beginner’s guide to learn mysql, understanding mongodb architecture, cassandra use cases, understanding sql data types – all you need to know about sql data types, differences between sql & nosql databases – mysql & mongodb comparison, introduction to snitches in cassandra, top 50 sql server interview questions you must prepare in 2023, mongodb: the database for big data processing, what is the average salary of a sql developer.
Thank you so much edureka ,it helped me a lot to crack Data Analyst interviews.
Join the discussion Cancel reply
Trending courses in databases, microsoft sql server certification course.
- 1k Enrolled Learners
SQL Essentials Training
- 11k Enrolled Learners
- Weekend/Weekday
MongoDB Certification Training Course
- 17k Enrolled Learners
MySQL DBA Certification Training
- 7k Enrolled Learners
Teradata Certification Training
- 3k Enrolled Learners
Apache Cassandra Certification Training
- 13k Enrolled Learners
Mastering Neo4j Graph Database Certification ...
- 2k Enrolled Learners
Browse Categories
Subscribe to our newsletter, and get personalized recommendations..
Already have an account? Sign in .
20,00,000 learners love us! Get personalised resources in your inbox.
At least 1 upper-case and 1 lower-case letter
Minimum 8 characters and Maximum 50 characters
We have recieved your contact details.
You will recieve an email from us shortly.
Top 40 SQL Query Interview Questions and Answers for Practice
Hello friends! in this post, we will see some of the most common SQL queries asked in interviews. Whether you are a DBA, developer, tester, or data analyst, these SQL query interview questions and answers are going to help you. In fact, I have been asked most of these questions during interviews in the different phases of my career.
If you want to skip the basic questions and start with some tricky SQL queries then you can directly move to our SQL queries interview questions for the experienced section.
Consider the below two tables for reference while trying to solve the SQL queries for practice .
Table – EmployeeDetails
Table – EmployeeSalary
For your convenience, I have compiled the top 10 questions for you. You can try solving these questions and click on the links to go to their respective answers.
- SQL Query to fetch records that are present in one table but not in another table.
- SQL query to fetch all the employees who are not working on any project.
- SQL query to fetch all the Employees from EmployeeDetails who joined in the Year 2020.
- Fetch all employees from EmployeeDetails who have a salary record in EmployeeSalary.
- Write an SQL query to fetch a project-wise count of employees.
- Fetch employee names and salaries even if the salary value is not present for the employee.
- Write an SQL query to fetch all the Employees who are also managers.
- Write an SQL query to fetch duplicate records from EmployeeDetails.
- Write an SQL query to fetch only odd rows from the table.
- Write a query to find the 3rd highest salary from a table without top or limit keyword.
Or, you can also jump to our below two sections on interview questions for freshers and experienced professionals.
SQL Query Interview Questions for Freshers
Here is a list of top SQL query interview questions and answers for fresher candidates that will help them in their interviews. In these queries, we will focus on the basic SQL commands only.
Ques.1. Write an SQL query to fetch the EmpId and FullName of all the employees working under the Manager with id – ‘986’. Ans. We can use the EmployeeDetails table to fetch the employee details with a where clause for the manager-
Ques.2. Write an SQL query to fetch the different projects available from the EmployeeSalary table. Ans. While referring to the EmployeeSalary table, we can see that this table contains project values corresponding to each employee, or we can say that we will have duplicate project values while selecting Project values from this table. So, we will use the distinct clause to get the unique values of the Project.
Ques.3. Write an SQL query to fetch the count of employees working in project ‘P1’. Ans. Here, we would be using aggregate function count() with the SQL where clause-
Ques.4. Write an SQL query to find the maximum, minimum, and average salary of the employees. Ans. We can use the aggregate function of SQL to fetch the max, min, and average values-
Ques.5. Write an SQL query to find the employee id whose salary lies in the range of 9000 and 15000. Ans. Here, we can use the ‘Between’ operator with a where clause.
Ques.6. Write an SQL query to fetch those employees who live in Toronto and work under the manager with ManagerId – 321. Ans. Since we have to satisfy both the conditions – employees living in ‘Toronto’ and working in Project ‘P2’. So, we will use AND operator here-
Ques.7. Write an SQL query to f etch all the employees who either live in California or work under a manager with ManagerId – 321. Ans. This interview question requires us to satisfy either of the conditions – employees living in ‘California’ and working under Manager with ManagerId – 321. So, we will use the OR operator here-
Ques.8. Write an SQL query to fetch all those employees who work on Projects other than P1. Ans. Here, we can use the NOT operator to fetch the rows which are not satisfying the given condition.
Or using the ‘not equal to’ operator-
For the difference between NOT and <> SQL operators, check this link – Difference between the NOT and != operators .
Ques.9. Write an SQL query to display the total salary of each employee adding the Salary with Variable value. Ans. Here, we can simply use the ‘+’ operator in SQL.
Ques.10. Write an SQL query to fetch the employees whose name begins with any two characters, followed by a text “hn” and ends with any sequence of characters. Ans. For this question, we can create an SQL query using like operator with ‘_’ and ‘%’ wild card characters, where ‘_’ matches a single character and ‘%’ matches ‘0 or multiple characters.
Ques.11. Write an SQL query to fetch all the EmpIds which are present in either of the tables – ‘EmployeeDetails’ and ‘EmployeeSalary’. Ans. In order to get unique employee ids from both tables, we can use the Union clause which can combine the results of the two SQL queries and return unique rows.
Ques.12. Write an SQL query to fetch common records between two tables. Ans. SQL Server – Using INTERSECT operator-
MySQL – Since MySQL doesn’t have INTERSECT operator so we can use the subquery-
Ques.13. Write an SQL query to fetch records that are present in one table but not in another table. Ans. SQL Server – Using MINUS- operator-
MySQL – Since MySQL doesn’t have a MINUS operator so we can use LEFT join-
Ques.14. Write an SQL query to fetch the EmpIds that are present in both the tables – ‘EmployeeDetails’ and ‘EmployeeSalary. Ans. Using subquery-
Ques.15. Write an SQL query to fetch the EmpIds that are present in EmployeeDetails but not in EmployeeSalary. Ans. Using subquery-
Ques.16. Write an SQL query to fetch the employee’s full names and replace the space with ‘-’. Ans. Using the ‘Replace’ function-
Ques.17. Write an SQL query to fetch the position of a given character(s) in a field. Ans. Using the ‘Instr’ function-
Ques.18. Write an SQL query to display both the EmpId and ManagerId together. Ans. Here we can use the CONCAT command.
Ques.19. Write a query to fetch only the first name(string before space) from the FullName column of the EmployeeDetails table. Ans. In this question, we are required to first fetch the location of the space character in the FullName field and then extract the first name out of the FullName field. For finding the location we will use the LOCATE method in MySQL and CHARINDEX in SQL SERVER and for fetching the string before space, we will use the SUBSTRING OR MID method. MySQL – using MID
SQL Server – using SUBSTRING
Ques.20. Write an SQL query to uppercase the name of the employee and lowercase the city values. Ans. We can use SQL Upper and Lower functions to achieve the intended results.
Ques.21. Write an SQL query to find the count of the total occurrences of a particular character – ‘n’ in the FullName field. Ans. Here, we can use the ‘Length’ function. We can subtract the total length of the FullName field from the length of the FullName after replacing the character – ‘n’.
Ques.22. Write an SQL query to update the employee names by removing leading and trailing spaces. Ans. Using the ‘Update’ command with the ‘LTRIM’ and ‘RTRIM’ functions.
Ques.23. Fetch all the employees who are not working on any project. Ans. This is one of the very basic interview questions in which the interviewer wants to see if the person knows about the commonly used – Is NULL operator.
Ques.24. Write an SQL query to fetch employee names having a salary greater than or equal to 5000 and less than or equal to 10000. Ans. Here, we will use BETWEEN in the ‘where’ clause to return the EmpId of the employees with salary satisfying the required criteria and then use it as a subquery to find the fullName of the employee from the EmployeeDetails table.
Ques.25. Write an SQL query to find the current date-time. Ans. MySQL-
SQL Server-
Ques.26. Write an SQL query to fetch all the Employee details from the EmployeeDetails table who joined in the Year 2020. Ans. Using BETWEEN for the date range ’01-01-2020′ AND ’31-12-2020′-
Also, we can extract the year part from the joining date (using YEAR in MySQL)-
Ques.27. Write an SQL query to fetch all employee records from the EmployeeDetails table who have a salary record in the EmployeeSalary table. Ans. Using ‘Exists’-
Ques.28. Write an SQL query to fetch the project-wise count of employees sorted by project’s count in descending order. Ans. The query has two requirements – first to fetch the project-wise count and then to sort the result by that count. For project-wise count, we will be using the GROUP BY clause and for sorting, we will use the ORDER BY clause on the alias of the project count.
Ques.29. Write a query to fetch employee names and salary records. Display the employee details even if the salary record is not present for the employee. Ans. This is again one of the very common interview questions in which the interviewer just wants to check the basic knowledge of SQL JOINS. Here, we can use the left join with the EmployeeDetail table on the left side of the EmployeeSalary table.
Ques.30. Write an SQL query to join 3 tables. Ans. Considering 3 tables TableA, TableB, and TableC, we can use 2 joins clauses like below-
For more questions on SQL Joins, you can also check our top SQL Joins Interview Questions .
SQL Query Interview Questions for Experienced
Here is a list of some of the most frequently asked SQL query interview questions for experienced professionals. These questions cover SQL queries on advanced SQL JOIN concepts, fetching duplicate rows, odd and even rows, nth highest salary, etc.
Ques. 31. Write an SQL query to fetch all the Employees who are also managers from the EmployeeDetails table. Ans. Here, we have to use Self-Join as the requirement wants us to analyze the EmployeeDetails table as two tables. We will use different aliases ‘E’ and ‘M’ for the same EmployeeDetails table.
To learn more about Self Join along with some more queries, you can watch the below video that explains the self-join concept in a very simple way.
Ques.32. Write an SQL query to fetch duplicate records from EmployeeDetails (without considering the primary key – EmpId). Ans. In order to find duplicate records from the table, we can use GROUP BY on all the fields and then use the HAVING clause to return only those fields whose count is greater than 1 i.e. the rows having duplicate records.
Ques.33. Write an SQL query to remove duplicates from a table without using a temporary table. Ans. Here, we can use delete with alias and inner join. We will check for the equality of all the matching records and then remove the row with a higher EmpId.
Ques.34. Write an SQL query to fetch only odd rows from the table. Ans. In case we have an auto-increment field e.g. EmpId then we can simply use the below query-
In case we don’t have such a field then we can use the below queries. Using Row_number in SQL server and checking that the remainder when divided by 2 is 1-
Using a user-defined variable in MySQL-
Ques.35. Write an SQL query to fetch only even rows from the table. Ans. In case we have an auto-increment field e.g. EmpId then we can simply use the below query-
In case we don’t have such a field then we can use the below queries. Using Row_number in SQL server and checking that the remainder, when divided by 2, is 1-
Ques.36. Write an SQL query to create a new table with data and structure copied from another table. Ans.
Ques.37. Write an SQL query to create an empty table with the same structure as some other table. Ans. Here, we can use the same query as above with the False ‘WHERE’ condition-
Ques.38. Write an SQL query to fetch top n records. Ans. In MySQL using LIMIT-
In SQL server using TOP command-
Ques.39. Write an SQL query to find the nth highest salary from a table. Ans. Using Top keyword (SQL Server)-
Using limit clause(MySQL)-
Ques.40. Write SQL query to find the 3rd highest salary from a table without using the TOP/limit keyword. Ans. This is one of the most commonly asked interview questions. For this, we will use a correlated subquery. In order to find the 3rd highest salary, we will find the salary value until the inner query returns a count of 2 rows having the salary greater than other distinct salaries.
For the nth highest salary-
This concludes our post on frequently asked SQL query interview questions and answers . I hope you practice these questions and ace your database interviews. If you feel, we have missed any of the common interview questions on SQL then do let us know in the comments and we will add those questions to our list.
Do check our article on – RDBM Interview Questions , focussing on the theoretical interview questions based on the DBMS and SQL concepts.
Kuldeep is the founder and lead author of ArtOfTesting. He is skilled in test automation, performance testing, big data, and CI-CD. He brings his decade of experience to his current role where he is dedicated to educating the QA professionals. You can connect with him on LinkedIn .
61 thoughts on “Top 40 SQL Query Interview Questions and Answers for Practice”
Thank You so much, these queries are very useful.
select max(salary) as maxsalary from emp where salary ( select max(salary) as maxsalary from emp where salary( select max(salary) as maxsalary from emp))
Thanks for the queries.
Hi Geet, thanks for pointing out, please let us know, what you find wrong in this query.
can I write the query for 6th question like
Select FullName from EmployeeDeatils where ManagerId is not null;
Because if anyone has ManagerId then he must be a manager right.so need to join??
Hi Ramesh, the ManagerId field in the EmployeeDetails table refers to the Manager of that Employee. So, your query will return only the Employees with Manager assigned.
how to fetch last second row records ???? and anyone sugest me the most asking sql queries in mnc
SELECT TOP 1 * FROM (SELECT TOP 2 * FROM Table1 ORDER BY RowID DESC) X ORDER BY RowID
Oracle 12c and above:
select * from DEPARTMENTS offset (select count(*) -2 from departments) rows fetch next 1 rows only;
with cte as ( select *,ROW_NUMBER() over(order by id desc) as rownum
from Employee1 ) select * from cte where rownum =2
select * from Employee
Movie table Creat table ” movie ” { “id” int(110 not null default ‘0’, “name” varchar(100)default null, “year” int(11) default null, ” rank” flaot default null, primary key (” id”)
Considering the above table how many movies does the dataset have for the year 1982 ! write ur query below
please help me to solve the query ….
SELECT COUNT(*) FROM movie WHERE year = 1982
select * from table order by ID(primary key) desc limit 1,1;
In Question no. 39 Put N=2 and ORDER BY asc You will get second lowest salary
select * from table name order by column name offset (select count(*) from table name)-1 rows fetch next 1 rows only
with ctek as ( select*row_number() over(order by id desc) as rowno from emp )
select*from ctek
where rowno=2
select*from emp
Thanks a lot for sharing these SQL queries. I have my interview tomorrow, these questions will really help.
Appreciate your feedback Amit :-). Best of luck with your interview.
Hi Kuldeep, Appreciate your efforts…
In Que.12 and Que. 13 , you have unconsciously written table name ‘ManagerSalary’ instead of EmployeeDetails.
Rest it is good blend of list of questions.
Hi Mithilesh,
Thanks. Actually, I have intentionally used a new table ManagerSalary. Since I have used ‘*’ in the queries which would require a similar table structure. Hence I introduced a new table – ManagerSalary, assuming the table to have a similar structure like that of EmployeeSalary.
This is extremely confusing and doesn’t make much sense. Some language about creating a new table would have been helpful. I kept searching this page for a ManagerSalary table.
This confused me too
This is really good for beginners.
Hi Kuldeep,
Great and fun article! A word of warning about the first answer to question 26. Not sure about SQL Server, but definitely in Oracle using BETWEEN with dates could get you in trouble. In “BETWEEN ‘2020/01/01’AND ‘2020/12/31′”, those dates are interpreted with a timestamp of 00:00. So with the latter date, it’ll only retrieve those hired until 2020/12/30 (or 2020/12/31 at exactly 00:00), and it would miss anyone hired after 00:00 on 2020/12/31 (so a person hired at 09:00 on 12/31 would be missed. Plus there are lots of additional complexities with doing something like BETWEEN ‘2020/01/01’AND ‘2020/12/31 23:59:59’. So in my experience, to get everyone hired in 2020, you’re better off using: AND dateOfJoining >= ‘2020/01/01’ AND dateOfJoining < '2021/01/01' …or just use the extract function like in your second answer 🙂
Thanks a lot, Phil.
Thanks for it
getting records of one managerid under their employeeid details alone have to come like their group memeber alone
In the following query can you please explain this line
WHERE Emp2.Salary > Emp1.Salary
SELECT Salary FROM EmployeeSalary Emp1 WHERE 2 = ( SELECT COUNT( DISTINCT ( Emp2.Salary ) ) FROM EmployeeSalary Emp2 WHERE Emp2.Salary > Emp1.Salary )
This is a use of Correlated Subquery. First, You are interested in fetching the “salary” from the EmployeeSalary Table or Emp1; You give the condition or criteria in WHERE clause.
The condition returns a value, where the total count of unique observations from Table EmployeeSalary. You are conditioning with respect to the same table.
For nth highest salary- SQL Server
SELECT Salary FROM EmployeeSalary Emp1 WHERE N-1 = ( SELECT COUNT( DISTINCT ( Emp2.Salary ) ) FROM EmployeeSalary Emp2 WHERE Emp2.Salary > Emp1.Salary )
Getting Error : Invalid column name ‘n’. Please tell me Some One ?
You don’t have to directly use ‘n’. You need to replace ‘n’ with a number e.g. if you want to find the 3rd highest salary, n would be 3.
This is be a more simple solution.
For nth highest salary,
Select Salary From EmployeeSalary Order By `Salary` Desc limit n-1,1;
For example, if we need 3rd highest salary, query will be
Select Salary From EmployeeSalary Order By `Salary` Desc limit 2,1;
But by this query, list will be printed from 3rd to minimum. Right ?
select max Salary from (select distinct Salary from EmployeeSalary order by Salary desc) where rownum < n+1;
thanks for sharing
Hi Kuldeep, Really useful and on point article, Great help at interviews. Thanks👍🏻
Hi, Can you please provide me the Create query for the above table and also provide me the create a query of ManagerSalary with insert data.
Hi kuldeep, Thanks for the effort you put in creating this blog. It’s very nice.
I just want to add one more sql query problem which i was asked in my Oracle Interview.
Suppose there is a table with 3 attr : city 1 city2 Distance Hyd. goa. 500 goa. Hyd. 500
These tuples represent the same information , so write an SQL query to remove these type of duplicates.
Ans : delete from t where (city1,city2) in ((select t1.city1,t1.city2 from t t1 where exists(select t2.city2 from t t2 where t2.city2=t1.city1) and exists(select t2.city1 from t t2 where t2.city1=t1.city2)) minus (select t1.city1,t1.city2 from t t1 where exists(select t2.city2 from t t2 where t2.city2=t1.city1) and exists(select t2.city1 from t t2 where t2.city1=t1.city2) fetch first 1 rows only));
Thanks a lot, Rahul. These types of questions will definitely help other readers. Keep contributing :-).
Cant we solve this using self join?
Delete E1 from E1.tab Join E2.tab where E1.city1 = E2.city2 and E1.city2 = E2.city 1 and E1.cost = E2.cost;
DELETE FROM distance t1 WHERE EXISTS ( SELECT 1 FROM distance t2 WHERE t1.city1= t2.city2 AND t1.city2 = t2.city1 AND t1.distance = t2.distance AND t1.rowid > t2.rowid );
Display list of employee having first name David or Diana without using like, in and or operater.please answer
Thanks kuldeep for such a good article and sharing valuable sql questions and answers which will help a lot for interview preparation.Hats off you.
How to fetch emplid with full name second letter is o
select Fullname from employeedetails where Fullname like’_o%’;
hi kuldeep sir, In my pc SQL does not support TOP keyword but it support LIMIT keyword. rest of the queries is nicely understandable.
I am a beginner in SQL and I was asked the below questions in one of my interviews, can anyone help me with the below.
Question1:- transaction table has 5 columns (transaction_id, customer_id, transation_date, product_id, transaction_amount)
write query to fetch 10 transaction made in last month by 1 customer
Question2:- transaction table has 5 columns (transaction_id, customer_id, transation_date, product_id, transaction_amount) product table has 2 columns (product_id, product_name)
write query to list all the product which are never sold write query to list all the product which are most sold
Question3:- transaction table has 5 columns (transaction_id, customer_id, transation_date, product_id, transaction_amount) product table has 2 columns (product_id, product_name)
Write query to fetch customer id, first transaction date, last transaction date, the difference in transaction amount, and difference in transaction date
1. SELECT * FROM ( SELECT transaction_id, customer_id, transaction_date, product_id, transaction_amount, ROW_NUMBER() OVER (ORDER BY transaction_date DESC) AS rn FROM transaction WHERE customer_id = 1 –(let 1) and transaction_date >= TRUNC(SYSDATE, ‘MM’) – 30 ) WHERE rn <= 10 order by transaction_id,customer_id; 2. SELECT p.product_id, p.product_name, total_amount FROM products p JOIN ( SELECT product_id, SUM(transaction_amount) AS total_amount FROM transaction GROUP BY product_id ORDER BY total_amount DESC ) t ON p.product_id = t.product_id WHERE ROWNUM = 1; 3. SELECT p.product_id, p.product_name FROM products p LEFT OUTER JOIN transaction t ON p.product_id = t.product_id WHERE t.transaction_id IS NULL; 4. SELECT t.customer_id, MIN(t.transaction_date) AS first_transaction_date, MAX(t.transaction_date) AS last_transaction_date, MAX(t.transaction_amount) – MIN(t.transaction_amount) AS diff_amount, MAX(t.transaction_date) – MIN(t.transaction_date) AS diff_date FROM transaction t GROUP BY t.customer_id;
Very helpful queries. 🙂
Really helpful article. Thank you
great art of testing. thanks for all the effort.
Thank you for sharing your knowledge with us.
select * from table_name where first_name=’David’ or first_name=’Diana’;
In the 31st question, can I write the query as SELECT DISTINCT(Fullname) FROM Employeedetails WHERE EmpID IN ( SELECT DISTINCT(ManagerID) FROM Employeedetails);
SELECT first_name FROM employees WHERE SUBSTR(first_name, 1, 5) = ‘David’ UNION SELECT first_name FROM employees WHERE SUBSTR(first_name, 1, 5) = ‘Diana’;
Hi, I have asked a question in My interview I have an order table with the following columns orderID,customerID,orderDate, sales amount. Please write a query to show total sales amount by order date? For these question we can use joins right?
select FullName from EmployeeDetails where ManagerId is not null
SELECT first_name FROM employees WHERE SUBSTR(first_name, 1, 5) = ‘David’ UNION SELECT first_name FROM employees WHERE SUBSTR(first_name, 1, 5) = ‘Diana’;
Leave a Comment Cancel reply
Save my name, email, and website in this browser for the next time I comment.
Revising the Select Query I Easy SQL (Basic) Max Score: 10 Success Rate: 96.21%
Revising the select query ii easy sql (basic) max score: 10 success rate: 98.77%, select all easy sql (basic) max score: 10 success rate: 99.61%, select by id easy sql (basic) max score: 10 success rate: 99.69%, japanese cities' attributes easy sql (basic) max score: 10 success rate: 99.64%, japanese cities' names easy sql (basic) max score: 10 success rate: 99.61%, weather observation station 1 easy sql (basic) max score: 15 success rate: 99.48%, weather observation station 3 easy sql (basic) max score: 10 success rate: 97.93%, weather observation station 4 easy sql (basic) max score: 10 success rate: 98.73%, weather observation station 5 easy sql (intermediate) max score: 30 success rate: 94.27%.
Towards Data Science
Sep 16, 2020
Member-only
Twenty-five SQL practice exercises
These questions and example solutions will keep your skills sharp., introduction.
S tructured query language (SQL) is used to retrieve and manipulate data stored in relational databases. Gaining working proficiency in SQL is an important prerequisite for many technology jobs and requires a bit of practice.
To complement SQL training resources ( PGExercises , LeetCode , HackerRank , Mode ) available on the web, I’ve compiled a list of my favorite questions that you can tackle by hand or solve with a PostgreSQL instance.
These questions cover the following critical concepts:
- Basic retrieval (SELECT, FROM)
- Creating and aliasing (WITH, AS, GENERATE_SERIES)
- Filtering (DISTINCT, WHERE, HAVING, AND, OR, IN, NOT IN)
- Aggregation (GROUP BY with COUNT, SUM, AVERAGE)
- Joins (INNER JOIN, LEFT JOIN, FULL OUTER JOIN on one or multiple (in)equalities, CROSS JOIN, UNION and UNION ALL)
- Conditional statements (CASE - WHEN - THEN - ELSE - END)
- Window functions (RANK, DENSE_RANK, ROW_NUMBER, SUM with PARTITION BY - ORDER BY)
- Formatting (LIMIT, ORDER BY, casting as an integer, float, or date, CONCAT, COALESCE)
- Arithmetic operations and comparisons (+, -, *, /, //, ^, <, >, =, !=)
- Datetime operations (EXTRACT(month/day/year))
Try it yourself
You can try these out yourself by downloading PostgreSQL and PSequel (see this tutorial for a step-by-step installation guide) and then running the queries shown in the grey boxes in the text below. PSequel is only available for Mac — if you’re using a PC, you can try one of these Windows alternatives.
The first block of text in each query shown below establishes the input table and follows the format:
You can query against the input table using PSequel (shown above) and easily construct new tables for your own problems using this template.
Web-based SQL training resources fall short along a few dimensions. LeetCode, for instance, doesn’t support the use of window functions and hides its most interesting questions behind a paywall. In addition, running SQL queries in your browser can be extremely slow — the data sets are large and retrieval speed is often throttled for non-premium users. Locally executing a query, on the other hand, is instantaneous and allows for rapid iteration through syntax bugs and intermediate tables. I’ve found this to be a more satisfying learning experience.
The questions outlined below include example solutions confirmed to work in PostgreSQL. Keep in mind there is usually more than one way to obtain the correct answer to a SQL problem. My preference is to use common table expressions ( CTEs ) rather than nested subqueries — CTEs allow for a more linear illustration of the data wrangling sequence. Both approaches, however, can yield identical solutions. I also like to follow the convention of keeping SQL operators in all caps (SELECT, FROM, WHERE, etc.), column names in lowercase (user_id, date, etc.), and simple table aliasing (t1, t2, etc.) where possible.
The code snippets shown below can be run in PSequel as-is to yield the displayed result. Note one quirk of Postgres: fractions must be multiplied by 1.0 to convert from integer to float format. This is not needed in other implementations of SQL and is not expected in interviews.
Feel free to leave your alternative answers in the comments!
1. Cancellation rates
From the following table of user IDs, actions, and dates, write a query to return the publication and cancellation rate for each user.
2. Changes in net worth
From the following table of transactions between two users, write a query to return the change in net worth for each user, ordered by decreasing net change.
3. Most frequent items
From the following table containing a list of dates and items ordered, write a query to return the most frequent item ordered on each date. Return multiple items in the case of a tie.
4. Time difference between latest actions
From the following table of user actions, write a query to return for each user the time elapsed between the last action and the second-to-last action, in ascending order by user ID.
5. Super users
A company defines its super users as those who have made at least two transactions. From the following table, write a query to return, for each user, the date when they become a super user, ordered by oldest super users first. Users who are not super users should also be present in the table.
6. Content recommendation (hard)
Using the following two tables, write a query to return page recommendations to a social media user based on the pages that their friends have liked, but that they have not yet marked as liked. Order the result by ascending user ID. Source .
7. Mobile and web visitors
With the following two tables, return the fraction of users who only visited mobile, only visited web, and visited both.
8. Upgrade rate by product action (hard)
Given the following two tables, return the fraction of users, rounded to two decimal places, who accessed feature two (type: F2 in events table) and upgraded to premium within the first 30 days of signing up.
9. Most friended
Given the following table, return a list of users and their corresponding friend count. Order the result by descending friend count, and in the case of a tie, by ascending user ID. Assume that only unique friendships are displayed (i.e., [1, 2] will not show up again as [2, 1] ). From LeetCode .
10. Project aggregation (hard)
The projects table contains three columns: task_id, start_date, and end_date. The difference between end_date and start_date is 1 day for each row in the table. If task end dates are consecutive they are part of the same project. Projects do not overlap.
Write a query to return the start and end dates of each project, and the number of days it took to complete. Order by ascending project duration, and ascending start date in the case of a tie. From HackerRank .
11. Birthday attendance
Given the following two tables, write a query to return the fraction of students, rounded to two decimal places, who attended school (attendance = 1) on their birthday. Source .
12. Hacker scores
Given the following two tables, write a query to return the hacker ID, name, and total score (the sum of maximum scores for each challenge completed) ordered by descending score, and by ascending hacker ID in the case of score tie. Do not display entries for hackers with a score of zero. From HackerRank .
13. Rank without RANK (hard)
Write a query to rank scores in the following table without using a window function. If there is a tie between two scores, both should have the same rank. After a tie, the following rank should be the next consecutive integer value. From LeetCode .
14. Cumulative salary sum
The following table holds monthly salary information for several employees. Write a query to get, for each month, the cumulative sum of an employee’s salary over a period of 3 months, excluding the most recent month. The result should be ordered by ascending employee ID and month. From LeetCode .
15. Team standings
Write a query to return the scores of each team in the teams table after all matches displayed in the matches table. Points are awarded as follows: zero points for a loss, one point for a tie, and three points for a win. The result should include team name and points, and be ordered by decreasing points. In case of a tie, order by alphabetized team name.
16. Customers who didn’t buy a product
From the following table, write a query to display the ID and name of customers who bought products A and B, but didn’t buy product C, ordered by ascending customer ID.
17. Median latitude (hard)
Write a query to return the median latitude of weather stations from each state in the following table, rounding to the nearest tenth of a degree. Note that there is no MEDIAN() function in SQL! From HackerRank .
18. Maximally-separated cities
From the same table in question 17, write a query to return the furthest-separated pair of cities for each state, and the corresponding distance (in degrees, rounded to 2 decimal places) between those two cities. From HackerRank .
19. Cycle time
Write a query to return the average cycle time across each month. Cycle time is the time elapsed between one user joining and their invitees joining. Users who joined without an invitation have a zero in the “invited by” column.
20. Three in a row
The attendance table logs the number of people counted in a crowd each day an event is held. Write a query to return a table showing the date and visitor count of high-attendance periods, defined as three consecutive entries (not necessarily consecutive dates) with more than 100 visitors. From LeetCode .
21. Commonly purchased together
Using the following two tables, write a query to return the names and purchase frequency of the top three pairs of products most often bought together. The names of both products should appear in one column. Source .
22. Average treatment effect (hard)
From the following table summarizing the results of a study, calculate the average treatment effect as well as upper and lower bounds of the 95% confidence interval. Round these numbers to 3 decimal places.
23. Rolling sum salary
The following table shows the monthly salary for an employee for the first nine months in a given year. From this, write a query to return a table that displays, for each month in the first half of the year, the rolling sum of the employee’s salary for that month and the following two months, ordered chronologically.
24. Taxi cancel rate
From the given trips and users tables for a taxi service, write a query to return the cancellation rate in the first two days in October, rounded to two decimal places, for trips not involving banned riders or drivers. From LeetCode .
25. Retention curve (hard)
From the following user activity table, write a query to return the fraction of users who are retained (show some activity) a given number of days after joining. By convention, users are considered active on their join day (day 0).
A note on one common problem: if you see a syntax error when using CTEs, check that you have commas between CTEs and no comma after the last CTE.
Thanks to Ben Lacar and Minting Ye.
More from Towards Data Science
Your home for data science. A Medium publication sharing concepts, ideas and codes.
About Help Terms Privacy
Get the Medium app
Michael Boles
Chemistry PhD, Silicon Valley engineer, data scientist, traveler, polyglot, dog lover.
Text to speech
IMAGES
VIDEO
COMMENTS
Q2. Create a store procedure that receives the first name of the person table as input and the last name as output. ; DECLARE @mylastname
run "psql -h ip -U username dbname" from your command prompt. Supply your password for the user which you must have set while creating the user.
50 SQL Query Questions and Answers for Practice. Q-1. Write an SQL query to fetch “FIRST_NAME” from Worker table using the alias name as <WORKER_NAME>. Ans
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.
Queries:- · 1. Select the detail of the employee whose name start with P. · 2. How many permanent candidate take salary more than 5000. · 3. Select the detail of
List and explain the different types of JOIN clauses supported in ANSI-standard SQL. View answer.
Top 30 SQL Query Interview Questions You Must Practice In 2023 · SELECT UPPER (EmpFname) AS EmpName FROM EmployeeInfo; · SELECT COUNT (*) FROM
Write an SQL query to fetch all the Employees who are also managers. Write an SQL query to fetch duplicate records from EmployeeDetails. Write an SQL query to
Join over 16 million developers in solving code challenges on HackerRank, one of the best ways to prepare for programming interviews.
From the following table containing a list of dates and items ordered, write a query to return the most frequent item ordered on each date.