assignment on sql queries

• ▼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

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

30 pl sql interview questions and answers, where clause in sql: a practical guide, 20 sql tips and tricks for performance, a beginner’s guide to sql joins, how to use union in sql queries, leave a reply.

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

Popular Tutorials

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.03%, weather observation station 4 easy sql (basic) max score: 10 success rate: 98.71%, weather observation station 5 easy sql (intermediate) max score: 30 success rate: 94.42%, cookie support is required to access hackerrank.

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

• 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

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

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

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

SQL Query Interview Questions

SQL or Structured Query Language is a standard language for relational databases. SQL queries are powerful tools used to, manipulate, and manage data stored in these databases like MySQL , Oracle , PostgreSQL , etc. Whether you’re fetching specific data points, performing complex analyses, or modifying database structures, SQL queries provide a standardized language for executing these tasks efficiently.

Here, we will cover 45+ MySQL interview questions with answers that are commonly asked during interviews for Data Analyst and Data Engineer positions at MAANG and other high-paying companies. Whether you are a fresher or an experienced professional with 5 , 8 , or 10 years of experience, this article gives you all the confidence you need to ace your next interview.

SQL Query Interview Questions and Answers

We have created three sample tables: Student Table, Program Table, and Scholarship Table. We will be using these tables to perform various query operations.

Student Table

Program Table

Scholarship Table

Let us start by taking a look at some of the most asked SQL Query interview questions :

1. Write a SQL query to fetch “FIRST_NAME” from the Student table in upper case and use ALIAS name as STUDENT_NAME.

2. write a sql query to fetch unique values of major subjects from student table., 3. write a sql query to print the first 3 characters of first_name from student table., 4. write a sql query to find the position of alphabet (‘a’) int the first name column ‘shivansh’ from student table., 5. write a sql query that fetches the unique values of major subjects from student table and print its length..

6. Write a SQL query to print FIRST_NAME from the Student table after replacing ‘a’ with ‘A’.

7. write a sql query to print the first_name and last_name from student table into single column complete_name., 8. write a sql query to print all student details from student table order by first_name ascending and major subject descending ..

9. Write a SQL query to print details of the Students with the FIRST_NAME as ‘Prem’ and ‘Shivansh’ from Student table.

10. Write a SQL query to print details of the Students excluding FIRST_NAME as ‘Prem’ and ‘Shivansh’ from Student table.

11. Write a SQL query to print details of the Students whose FIRST_NAME ends with ‘a’.

12. Write an SQL query to print details of the Students whose FIRST_NAME ends with ‘a’ and contains six alphabets.

13. write an sql query to print details of the students whose gpa lies between 9.00 and 9.99..

14. Write an SQL query to fetch the count of Students having Major Subject ‘Computer Science’.

15. Write an SQL query to fetch Students full names with GPA >= 8.5 and <= 9.5.

16. write an sql query to fetch the no. of students for each major subject in the descending order..

17. Display the details of students who have received scholarships, including their names, scholarship amounts, and scholarship dates.

18. Write an SQL query to show only odd rows from Student table.

19. Write an SQL query to show only even rows from Student table.

20. List all students and their scholarship amounts if they have received any. If a student has not received a scholarship, display NULL for the scholarship details.

21. write an sql query to show the top n (say 5) records of student table order by descending gpa..

22. Write an SQL query to determine the nth (say n=5) highest GPA from a table.

23. Write an SQL query to determine the 5th highest GPA without using LIMIT keyword.

24. Write an SQL query to fetch the list of Students with the same GPA.

25. write an sql query to show the second highest gpa from a student table using sub-query., 26. write an sql query to show one row twice in results from a table., 27. write an sql query to list student_id who does not get scholarship., 28. write an sql query to fetch the first 50% records from a table., 29. write an sql query to fetch the major subject that have less than 4 people in it..

30. Write an SQL query to show all MAJOR subject along with the number of people in there.

31. Write an SQL query to show the last record from a table.

32. Write an SQL query to fetch the first row of a table.

33. Write an SQL query to fetch the last five records from a table.

34. Write an SQL query to fetch three max GPA from a table using co-related subquery.

35. write an sql query to fetch three min gpa from a table using co-related subquery., 36. write an sql query to fetch nth max gpa from a table., 37. write an sql query to fetch major subjects along with the max gpa in each of these major subjects..

38. Write an SQL query to fetch the names of Students who has highest GPA.

39. Write an SQL query to show the current date and time.

40. write a query to create a new table which consists of data and structure copied from the other table (say student) or clone the table named student., 41. write an sql query to update the gpa of all the students in ‘computer science’ major subject to 7.5., 42. write an sql query to find the average gpa for each major..

43. Write an SQL query to show the top 3 students with the highest GPA.

44. Write an SQL query to find the number of students in each major who have a GPA greater than 7.5.

45. Write an SQL query to find the students who have the same GPA as ‘Shivansh Mahajan’.

In summary, mastering SQL query interview questions is essential for anyone looking to excel in roles such as data analysts, data engineers, and business analysts. This guide has provided a comprehensive collection of SQL query interview questions and answers designed to prepare you thoroughly for your interviews.

By understanding and practicing these queries, you can demonstrate your proficiency in SQL, a critical skill that underpins successful data manipulation and analysis in various tech-driven industries.

Please Login to comment...

Similar reads.

• interview-questions

Improve your Coding Skills with Practice

What kind of Experience do you want to share?

This browser is no longer supported.

Upgrade to Microsoft Edge to take advantage of the latest features, security updates, and technical support.

SELECT examples (Transact-SQL)

• 8 contributors

This article provides examples of using the SELECT statement.

The Transact-SQL code samples in this article use the AdventureWorks2022 or AdventureWorksDW2022 sample database, which you can download from the Microsoft SQL Server Samples and Community Projects home page.

A. Use SELECT to retrieve rows and columns

The following example shows three code examples. This first code example returns all rows (no WHERE clause is specified) and all columns (using the * ) from the Product table in the AdventureWorks2022 database.

This example returns all rows (no WHERE clause is specified), and only a subset of the columns ( Name , ProductNumber , ListPrice ) from the Product table in the AdventureWorks2022 database. Additionally, a column heading is added.

This example returns only the rows for Product that have a product line of R and that have days to manufacture that is less than 4 .

B. Use SELECT with column headings and calculations

The following examples return all rows from the Product table. The first example returns total sales and the discounts for each product. In the second example, the total revenue is calculated for each product.

This is the query that calculates the revenue for each product in each sales order.

C. Use DISTINCT with SELECT

The following example uses DISTINCT to prevent the retrieval of duplicate titles.

D. Create tables with SELECT INTO

The following first example creates a temporary table named #Bicycles in tempdb .

This second example creates the permanent table NewProducts .

E. Use correlated subqueries

A correlated subquery is a query that depends on the outer query for its values. This query can be executed repeatedly, one time for each row that could be selected by the outer query.

The first example shows queries that are semantically equivalent to illustrate the difference between using the EXISTS keyword and the IN keyword. Both are examples of a valid subquery that retrieves one instance of each product name for which the product model is a long sleeve logo jersey, and the ProductModelID numbers match between the Product and ProductModel tables.

The next example uses IN and retrieves one instance of the first name and family name of each employee for which the bonus in the SalesPerson table is 5000.00 , and for which the employee identification numbers match in the Employee and SalesPerson tables.

The previous subquery in this statement can't be evaluated independently of the outer query. It requires a value for Employee.EmployeeID , but this value changes as the SQL Server Database Engine examines different rows in Employee .

A correlated subquery can also be used in the HAVING clause of an outer query. This example finds the product models for which the maximum list price is more than twice the average for the model.

This example uses two correlated subqueries to find the names of employees who sold a particular product.

F. Use GROUP BY

The following example finds the total of each sales order in the database.

Because of the GROUP BY clause, only one row containing the sum of all sales is returned for each sales order.

G. Use GROUP BY with multiple groups

The following example finds the average price and the sum of year-to-date sales, grouped by product ID and special offer ID.

H. Use GROUP BY and WHERE

The following example puts the results into groups after retrieving only the rows with list prices greater than $1000 .

I. Use GROUP BY with an expression

The following example groups by an expression. You can group by an expression if the expression doesn't include aggregate functions.

J. Use GROUP BY with ORDER BY

The following example finds the average price of each type of product and orders the results by average price.

K. Use the HAVING clause

The first example that follows shows a HAVING clause with an aggregate function. It groups the rows in the SalesOrderDetail table by product ID and eliminates products whose average order quantities are five or less. The second example shows a HAVING clause without aggregate functions.

This query uses the LIKE clause in the HAVING clause.

L. Use HAVING and GROUP BY

The following example shows using GROUP BY , HAVING , WHERE , and ORDER BY clauses in one SELECT statement. It produces groups and summary values but does so after eliminating the products with prices over $25 and average order quantities under 5. It also organizes the results by ProductID .

M. Use HAVING with SUM and AVG

The following example groups the SalesOrderDetail table by product ID and includes only those groups of products that have orders totaling more than $1000000.00 and whose average order quantities are less than 3 .

To see the products with total sales greater than $2000000.00 , use this query:

If you want to make sure there are at least 1,500 items involved in the calculations for each product, use HAVING COUNT(*) > 1500 to eliminate the products that return totals for fewer than 1500 items sold. The query looks like this:

N. Use the INDEX optimizer hint

The following example shows two ways to use the INDEX optimizer hint. The first example shows how to force the optimizer to use a nonclustered index to retrieve rows from a table. The second example forces a table scan by using an index of 0.

M. Use OPTION and the GROUP hints

The following example shows how the OPTION (GROUP) clause is used with a GROUP BY clause.

O. Use the UNION query hint

The following example uses the MERGE UNION query hint.

P. Use a UNION

In the following example, the result set includes the contents of the ProductModelID and Name columns of both the ProductModel and Gloves tables.

Q. Use SELECT INTO with UNION

In the following example, the INTO clause in the second SELECT statement specifies that the table named ProductResults holds the final result set of the union of the designated columns of the ProductModel and Gloves tables. The Gloves table is created in the first SELECT statement.

R. Use UNION of two SELECT statements with ORDER BY

The order of certain parameters used with the UNION clause is important. The following example shows the incorrect and correct use of UNION in two SELECT statements in which a column is to be renamed in the output.

S. Use UNION of three SELECT statements to show the effects of ALL and parentheses

The following examples use UNION to combine the results of three tables that all have the same five rows of data. The first example uses UNION ALL to show the duplicated records, and returns all 15 rows. The second example uses UNION without ALL to eliminate the duplicate rows from the combined results of the three SELECT statements, and returns five rows.

The third example uses ALL with the first UNION and parentheses enclose the second UNION that isn't using ALL . The second UNION is processed first because it's in parentheses, and returns five rows because the ALL option isn't used and the duplicates are removed. These five rows are combined with the results of the first SELECT by using the UNION ALL keywords. This example doesn't remove the duplicates between the two sets of five rows. The final result has 10 rows.

Related content

• CREATE TRIGGER (Transact-SQL)
• CREATE VIEW (Transact-SQL)
• DELETE (Transact-SQL)
• EXECUTE (Transact-SQL)
• Expressions (Transact-SQL)
• INSERT (Transact-SQL)
• LIKE (Transact-SQL)
• Set Operators - UNION (Transact-SQL)
• Set Operators - EXCEPT and INTERSECT (Transact-SQL)
• UPDATE (Transact-SQL)
• WHERE (Transact-SQL)
• PathName (Transact-SQL)
• SELECT - INTO Clause (Transact-SQL)

Was this page helpful?

Coming soon: Throughout 2024 we will be phasing out GitHub Issues as the feedback mechanism for content and replacing it with a new feedback system. For more information see: https://aka.ms/ContentUserFeedback .

Submit and view feedback for

Additional resources

• Stack Overflow for Teams Where developers & technologists share private knowledge with coworkers
• Advertising & Talent Reach devs & technologists worldwide about your product, service or employer brand
• OverflowAI GenAI features for Teams
• OverflowAPI Train & fine-tune LLMs
• Labs The future of collective knowledge sharing
• About the company Visit the blog

Collectives™ on Stack Overflow

Find centralized, trusted content and collaborate around the technologies you use most.

Q&A for work

Connect and share knowledge within a single location that is structured and easy to search.

Get early access and see previews of new features.

Where can I find exercises to practice SQL statements? [closed]

Do you know where I can find some practice SQL problems where I can write select statements?

• 1 Look at my comments below the chosen answer. You might get better stuff there than the junk at sql.ru, w3 schools etc. –  Trojan.ZBOT Commented Dec 30, 2013 at 3:38
• HackeRank has a good collection of questions : hackerrank.com/domains/sql/select –  saruftw Commented Feb 20, 2017 at 9:41
• Check out my book SQL Practice Problems , available on Amazon. I wrote it specifically because it's easy to find SQL keyword and syntax info, but difficult to find good real-world practice in writing select statements. –  Sylvia Commented Jun 7, 2017 at 16:24

4 Answers 4

SQL exercises or you can create a test table with fake data and manipulate that. Personally, I learn better with hands-on activity, by playing with the SELECT statements myself before even practicing an online guide. However, not everyone is the same.

Here are a few other links to check out:

• SQLCourse - Interactive for beginners.
• SQLCourse2 - It's awesome you can try and sharpen your skills.

• 2 In one word - yuck ! Only if you can get the databases used, then go for examples like these - google.com/search?q=sql+assignments+with+solutions Or based on the public AdventureWorks DB for MS-SQL server - cuttingedgecourse.com/CIS310/Assignments.htm –  Trojan.ZBOT Commented Dec 30, 2013 at 3:26
• Or search google like this - sql assignment site:.edu –  Trojan.ZBOT Commented Dec 30, 2013 at 3:33
• 1 This website, for example has IMDB (movies) website data and questions - courses.cs.washington.edu/courses/cse444/06wi/hw/hw1.htm data for questions is here - courses.cs.washington.edu/courses/cse444/06wi/hw/IMDB.zip –  Trojan.ZBOT Commented Dec 30, 2013 at 3:35
• 3 This is the most complex query in page 10 of the "advanced course" - SELECT customers.customerid, customers.firstname, customers.lastname, items_ordered.order_date, items_ordered.item, items_ordered.price FROM customers, items_ordered WHERE customers.customerid = items_ordered.customerid; Glance these sqlCourse links, but don't expect to get any real knowledge out of them. –  Trojan.ZBOT Commented Dec 30, 2013 at 4:19

Look the line of books by Celko, it has lots of examples and exercises.

You can find some here as well.

• 1 Although really, I would download SQL Server Express, download the AdventureWorks db, and get a beginning SQL book like "Beginning SQL" –  davecoulter Commented Mar 9, 2010 at 23:46

Try SQL Exercises . Free and interactive.

• 2 Already posted in accepted answer. –  ctomek Commented Feb 18, 2017 at 20:29

Not the answer you're looking for? Browse other questions tagged sql or ask your own question .

• Featured on Meta
• We've made changes to our Terms of Service & Privacy Policy - July 2024
• Introducing an accessibility dashboard and some upcoming changes to display...
• Tag hover experiment wrap-up and next steps

Hot Network Questions

• What type of concept is "mad scientist"?
• Cycloheptatrienyl anion is antiaromatic or non-aromatic?
• dealing with the feeling after rejection
• Refereeing papers by people you are very close to
• Operator norm of sum of tensor products
• Which Boolean Math mode should I use?
• Why do most published papers hit the maximum page limit exactly?
• "Seagulls are gulling away."
• Can I use specific preprocess hooks for a node type or a view mode?
• In relation to the Tandy TRS-80 Model 1 and Model III, what are "ISAM" and "PDS"?
• Strategies for handling Maternity leave the last two weeks of the semester
• Would a manned Mars landing be possible with Apollo-era technology?
• 16 of them stay the same, some of the rest are "questionable"
• Will a spaceship that never stops between earth and mars save fuel?
• I don't do something AFTER I did something
• TikZ, node not well connected, small gap
• Why do we sometimes use the concept of limits but sometimes don't forget similar kind of problems?
• Dark, cynical video game taking place in the medieval ages
• Will lights plugged into cigarette lighter drain the battery to the point that the truck won't start?u
• What is the first recorded usage of the three ‘’R’s: Reading wRiting and aRithmetic?"
• In Norway, when number ranges are listed 3 times on a sign, what do they mean?
• Statistically, which is more of a severe penalty on a d20 roll, Disadvantage or a -10 penalty?
• Weird lines in the Aeneid (Book I, lines 444-445)
• What does "No camping 10-21" mean?

Assignment 1: SQL Queries

Objectives:.

To understand and be able to write simple SQL queries.

Reading Assignments:

6.1 - 6.6, 7.1

Number of points:

Tools for the assignment.

For this assignment, you will use your SQL Server account on IISQLSRV. You should have an account, and you know your password from the first lecture.

As an alternative to SQL Server, you are allowed to use a different database, e.g. MySQL, Postgres, or SQL Server installed in your own machine. In that case you fist need to import the movie database in your database system: you can obtain the data from here . There is no support for database systems other than IISQLSRV.

What to turn in:

Note: a minor modification is made for the turn-in format as follows. please check  thanks for your cooperation :-)     , homework description.

• ACTOR ( id , fname, lname, gender)
• MOVIE ( id , name, year, rank)
• DIRECTOR ( id , fname, lname)
• CAST (pid, mid, role)
• MOVIE_DIRECTOR (did, mid)
• (8') a. List all the actors who acted in at least one film in 2nd half of the 19th century and in at least one film in the 1st half of the 20th century b. List all the directors who directed a film in a leap year  
• (8') List all the movies that have the same year as the movie 'Shrek (2001)', but a better rank. (Note: bigger value of rank implies a better rank)  
• (8') List first name and last name of all the actors who played in the movie 'Officer 444 (1926)'  
• (8') List all directors in descending order of the number of films they directed  
• (8')  a. Find the film(s) with the largest cast.   b. Find the film(s) with the smallest cast. In both cases, also return the size of the cast.  
• (8') Find all the actors who acted in films by at least 10 distinct directors ( i.e. actors who worked with at least 10 distinct directors).  
• (8') Find all actors who acted only in films before 1960.  
• (8') Find the films with more women actors than men.  
• (8') For every pair of male and female actors that appear together in some film, find the total number of films in which they appear together. Sort the answers in decreasing order of the total number of films.  
• (8') For every actor, list the films he/she appeared in their debut year. Sort the results by last name of the actor.  
• (10') The Bacon number of an actor is the length of the shortest path between the actor and Kevin Bacon in the "co-acting" graph. That is, Kevin Bacon has Bacon number 0; all actors who acted in the same film as KB have Bacon number 1; all actors who acted in the same film as some actor with Bacon number 1 have Bacon number 2, etc. Return all actors whose Bacon number is 2. Bonus: Suppose you write a single SELECT-FROM-WHERE SQL query that returns all actors whose Bacon number is infinity. How big is the query? Note: The above "Bonus" problem is ill-stated. The correct one should be as follows: Suppose you write a single SELECT-FROM-WHERE SQL query that returns all actors who have finite Bacon numbers. How big is the query?  
• (10') A decade is a sequence of 10 consecutive years. For example 1965, 1966, ..., 1974 is a decade, and so is 1967, 1968, ..., 1976. Find the decade with the largest number of films.  
• Bonus: Rank the actors based on their popularity, and compute a list of all actors in descending order of their popularity ranks.  You need to come up with your own metric for computing the popularity ranking.  This may include information such as the number of movies that an actor has acted in; the 'popularity' of these movies' directors (where the directors' popularity is the number of movies they have directed), etc.  Be creative in how you choose your criteria of computing the actors' popularity.   For this answer, in addition to the query, also turn in the criteria you used to rank the actors.

MySQL Tutorial

Mysql database, mysql references, mysql examples, mysql exercises.

You can test your MySQL skills with W3Schools' Exercises.

We have gathered a variety of MySQL exercises (with answers) for each MySQL 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 MySQL Exercises

Start MySQL Exercises ❯

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

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 Practice Exercises with Solutions | SQL Queries Practical Exercise

Sql practice exercises with solutions :.

In my previous article i have given the different examples of SQL as well as most important complex sql queries for interview purpose .I would like to combine all those examples and want to make one best article on SQL Practice Exercises with solutions.My main purpose writing this article on SQL Practice Exercises with solution is to get idea about different SQL real world examples as well as user can easily implement it in day to day life.These are the scenarios which are useful for real world industry. I have already written article on real world industry examples of SQL .I want to add some queries from that article also.There are following SQL Practice Exercises with Solutions which are mostly used in day to day life in world of programming.

Important Queries for SQL Practice Exercises with Solutions :

Example 1 : how to create table with same structure with data.

Let us consider that user wants to create a replica of table named ‘Student’.

Create table Student_Replica as Select * from Student;

Explanation :

The above query will create the same table as student named ‘Student_Replica’ with its data.

Example 2 : How to create table with same structure without data?

Let us consider that user wants to create a replica of table named ‘Student’ without having data.

Create table Student_Replica as Select * from Student where 1=2;

The above query will create the same table as student named ‘Student_Replica’ without data.It is possible due to the condition 1=2. The condition 1=2 means True=False which is always False condition.So with using the above query user can create duplicate table structure without data.

Example 3 : How to display Last 10 records from Student table.

There are some situations where user needs to fetch some last records from the table.The following query will fetch the last records from the table.

Select * from Student S where rownum <=10 union select * from (Select * from Student S order by rowid desc) where rownum <=10;

Here we are using simple logic of union and order by the 10 records from student table.

Example 4 : How to fetch first 5 highest marks with Student table.

There are so many examples where user needs to fetch the highest values from the specified table.Following query will fetch the first 5 highest marks from student table.

select min(marks)from(select distinct marks from Student order by marks desc)where rownum<=5;

Explanation:

In above example we are using the rownum concept as well as we are using inner view of descending marks from student table.

Example 5: How to display 1 to 100 numbers with using query.

There are scenarios where user wants to display 1 to 100 numbers with using the query.

Select level from dual connect by level <=100;

In this example user needs to use the concept of hierarchical queries. With using the level attribute of hierarchical query user can display first 100 numbers.

Example 6 : How to find the duplicate row count from specific table.

This example is most important example for real world scenarios.There are so many times where user needs to find out the duplicate row count from the table.

Select Employee_no, count (Employee_no) from Employee Group by Employee_no Having count (Employee_no)>1 Order by count (Employee_no) desc;

In this example we need to use the Count function as well as group by and having. You need to use order by clause as well.

Example 7 : How to delete duplicate rows from the table.

Using above query we find out the duplicate record count from the table. There are situations where user needs to find out the duplicate rows as well as delete those rows. Following query is useful in that case.

Delete FROM Employee WHERE ROWID <> (Select max (rowid) from Employee b where Employee_num=b.Employee_num);

Here we are using the <> operator from SQL to delete duplicate rows from the table.

Example 8 : How user can display following structure with using single SQL Query.

We can not use dual table to perform this operation. We need to use Employee table with data more than 4 to perform this.

SELECT lpad (‘$’, ROWNUM,’$’) FROM Employee WHERE ROWNUM <4;

Here we are using lpad() function to fetch dollar symbol.

Example 9 :How to check for Email is correct or wrong with using single query.

User needs to use regular expression function to check the Email validation with single query.

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

These are above some SQL Practice Exercises with Solutions which are highly used in real life scenarios.In out SQL Practice Exercises with Solutions we are using the Regular expression function named REGEXP_LIKE to check for Email validation.

FOR ANY SQL SUPPORT CONTACT : [email protected]

Example 10 : How to fetch maximum salary of Employee and minimum salary of Employee together from Employee table.

This is bit tricky question.We can not use the 2 aggregate functions together.

Select max (salary) from Employee

Select min (salary) from Employee;

The example is pretty simple where user needs to use the Set operators from SQL .Here user needs to use union set operator.

Example 11 :  List the Students whose name starts with P and surname starts with S.

The Like operator will help to achieve this,

Select * from Students where name like ‘P%’ and surname like ‘S%’;

The SQL Like special character is most used wildcard to fetch data with specific condition in the table.

Example 12 :How to fetch last record from Student table.

This is also most common SQL Practice Exercises with Solutions where user needs to fetch the last record from the table,

Select * from Student where rowid = select max(rowid) from Student;

The records are stored in to table according to the rowid of the table. So User needs to fetch the maximum of row id record from Student table.

Example 13 : How to fetch all the Student who took admission at 2016.

There are so many scenarios where user needs to fetch the record according to admission year or joining date.

select * from Student where To_char(Joining_date,’YYYY’)=’2016′;

The above example uses the To_Char function to fetch the specific year from the date.

Example 14 : What is query to display odd records from Student table.

This query is also most important and most used SQL Practice Exercises with Solutions to display odd as well as display Even records from the specific table,

Select * from(Select rownum as rno,S.* from Student S) where Mod(rno,2)=1;

In this example user needs to use the Rownum as well as Mod functions to check out the odd records from the table.

Example 15 : What is query to display even records from Student table.

This query is also most important and most used SQL Practice Exercises with Solutions to display even as well as display odd records from the specific table,

Select * from(Select rownum as rno,S.* from Student S) where Mod(rno,2)=0;

In this example user needs to use the Rownum as well as Mod functions to check out the even records from the table.

Example 16 : How to find out manager name and employee name from same table.

This is scenario with self join in SQL.Following query will find out the Manager name with Employee name from Employee table,

Select e.employee_name,m.employee name from Employee e,Employee m where e.Employee_id=m.Manager_id;

In this query user is using the self join to fetch the records from the table.

These are some most important SQL Practice Exercises with Solution.I will update the examples of SQL Practice Exercises with Solutions on weekly basis so that user will get the best SQL queries for practice.Hope you like this article on SQL Practice Exercises with Solutions.If you like this article or if you have any questions or queries regarding the same kindly comment in to comment section.

4 Replies to “SQL Practice Exercises with Solutions | SQL Queries Practical Exercise”

Hello how many years of experience person can solve above questions.

can you share more questions to below email id [email protected]

Sure… You can solve problems with 5 years experience.

You can check following links :

You can refer this : https://www.complexsql.com/category/this-category-includes-performance-tuning-articles-of-sql/ https://www.complexsql.com/real-time-scenarios-in-sql-queries/#:~:text=There%20are%20real%20world%20situations,needs%20to%20delete%20duplicate%20rows .

https://www.complexsql.com/pl-sql-examples/

hello.. can you share one real time project please so that it can be helpful for us in interview. [email protected] thanks

Hope this helps!

Regards, Amit

Comments are closed.

{{ activeMenu.name }}

• Python Courses
• JavaScript Courses
• Artificial Intelligence Courses
• Data Science Courses
• React Courses
• Ethical Hacking Courses
• View All Courses

Fresh Articles

• Python Projects
• JavaScript Projects
• Java Projects
• HTML Projects
• C++ Projects
• PHP Projects
• View All Projects

• Python Certifications
• JavaScript Certifications
• Linux Certifications
• Data Science Certifications
• Data Analytics Certifications
• Cybersecurity Certifications
• View All Certifications

• IDEs & Editors
• Web Development
• Frameworks & Libraries
• View All Programming
• View All Development

• App Development
• Game Development
• Courses, Books, & Certifications
• Data Science
• Data Analytics
• Artificial Intelligence (AI)
• Machine Learning (ML)
• View All Data, Analysis, & AI

• Networking & Security
• Cloud, DevOps, & Systems
• Recommendations
• Crypto, Web3, & Blockchain
• User-Submitted Tutorials
• View All Blog Content
• Python Online Compiler
• JavaScript Online Compiler
• HTML & CSS Online Compiler
• Certifications
• Programming
• Development
• Data, Analysis, & AI
• Online Python Compiler
• Online JavaScript Compiler
• Online HTML Compiler

Don't have an account? Sign up

Forgot your password?

Already have an account? Login

Have you read our submission guidelines?

Go back to Sign In

75+ Top SQL Interview Questions and Answers in 2024

If you’re preparing for an SQL interview, you’ve got to make sure that you are prepared to handle all sorts of questions, from the basic to the advanced SQL interview questions. A little preparation can go a long way in boosting your confidence for that next Database Developer job interview.

The set of common SQL interview questions here covers all SQL functionalities, split into three sections of basic, intermediate, and advanced difficulty. If you want a refresher you may want to check out SQL Tutorials recommended for you.

But before we hop into these SQL server interview questions , let’s take a look at some general questions on SQL and related interviews.

• How Do I Prepare for the SQL Interview?

Like you would prepare for any other interview, though you might find yourself focusing a little more on theory with an SQL interview. Be sure to know the commands, as well as the theory behind processes and decisions. A mix of hands-on learning and reading up is the best way to prepare for an SQL interview.

• What Do I Need to Know for the SQL Interview?

You’ll need to know some basic ideas and commands about how to operate with databases. Essentially, you will have to prove that you can carry out the functions you would need to do in a practical setting. Fortunately, the SQL interview questions and answers listed here cover everything you need to know.

• How Can I Practice SQL?

The best way to practice SQL is to download the database software and start working with it. That means creating a table, entering data, and modifying it. Look up the various SQL commands and execute them in the software. And of course, don’t forget to read SQL query interview questions.

• Top SQL Interview Questions and Answers

Basic SQL Interview Questions

The following section covers basic concepts, with the following section focusing on intermediate and advanced SQL questions.

1. What are the 5 basic SQL commands?

The 5 basic SQL commands are ALTER, UPDATE, DELETE, INSERT and CREATE. Of course, there are many more commands, some more nuanced, but the aforementioned are the fundamentals.

2. What is the difference between DBMS and RDBMS?

A Database Management System (DBMS) is a software application that helps you build and maintain databases. A Relational Database Management System (RDBMS) is a subset of DBMS, and it is one based on the relational model of the DBMS.

3. Can we embed Pl/SQL in SQL? Justify your answers.

PL/SQL is a procedural language, and it has one or more SQL statements in it, so SQL can be embedded in a PL/SQL block; however, PL/SQL cannot be embedded in SQL as SQL executes a single query at a time.

4. What do you mean by Data Manipulation Language (DML)?

Data Manipulation Language (DML) includes the most common SQL statements to store, modify, delete, and retrieve data. They are SELECT, UPDATE, INSERT, and DELETE.

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

A join is used to query data from multiple tables based on the relationship between the fields.

There are four types of joins:

Rows are returned when there is at least one match of rows between the tables.

/* customers and orders are two tables. Data will be displayed from the two tables where the customer_id from customer table matches

The customer_id from the orders table. */

Right join returns all rows from the right table and those which are shared between the tables. If there are no matching rows in the left table, it will still return all the rows from the right table.

/* customers and orders are two tables. All rows from the Orders table is returned with matching rows from the Customers table if any */

Left join returns all rows from the Left table and those which are shared between the tables. If there are no matching rows in the right table, it will still return all the rows from the left table.

/* customers and orders are two tables. All rows from the customers table is returned with matching rows from the orders table if any */

Full join return rows when there are matching rows in any one of the tables. This means it returns all the rows from the left-hand side table and all the rows from the right-hand side table.

/* customers and orders are two tables. All rows from the Orders table and customer table are returned */

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

CHAR is used to store fixed-length character strings, and VARCHAR2 is used to store variable-length character strings.

For example, suppose you store the string ‘Database’ in a CHAR(20) field and a VARCHAR2(20) field.

The CHAR field will use 22 bytes (2 bytes for leading length ).

The VARCHAR2 field will use 10 bytes only (8 for the string , 2 bytes for leading length ).

7. Explain SQL constraints.

Constraints are used to specify the rules of data type in a table. They can be specified while creating and altering the table. The following are the constraints in SQL:

• NOT NULL: Restricts NULL value from being inserted into a column
• CHECK: Verifies that all values in a field satisfy a condition
• DEFAULT: Automatically assigns a default value if no value has been specified for the field
• UNIQUE: Ensures unique values to be inserted into the field
• INDEX: Indexes a field providing faster retrieval of records
• PRIMARY KEY: Uniquely identifies each record in a table
• FOREIGN KEY: Ensures referential integrity for a record in another table

8. What is a primary key, a foreign key, and a unique key?

Primary Key

The primary key is a field in the table which uniquely identifies a row. It cannot be NULL

Foreign Key

A foreign key is a field in one table that is a primary key in another table. A relationship is created between the two tables by referencing the foreign key of one table with the primary key of another table.

In the example below, the employee_id_ref in the salary table is the foreign key.

The Unique Key uniquely identifies a record in a table. There can be many unique key constraints defined on a table.

In the table above Emp_id is the primary key but Government_id is the unique key. You may want the Government_id to be unique for every employee. Since the data belongs to the government, you may not want it to be the primary key.

The Complete SQL Bootcamp 2024: Go from Zero to Hero

9. What is a trigger?

Triggers are stored programs that get automatically executed when an event such as INSERT, DELETE, and the UPDATE (DML) statement occurs. Triggers can also be evoked in response to Data definition statements(DDL) and database operations, for example, SERVER ERROR, LOGON.

10. What is the difference between SQL and MySQL?

SQL is a structured query language used to access the DBMS whereas MYSQL is an Open Source Relational DBMS. The latter operates on the relational model, allowing for more advanced ways to deal with data.

11. What is a NULL Value field?

A NULL value is a field with No Value.

Intermediate-Level Interview Questions

12. what are acid properties in a transaction.

In order to maintain consistency in a database’s ‘before and after’ transactions, certain properties are followed. They are:

• Atomicity: The transaction must happen fully and cannot be left midway
• Consistency: This maintains integrity constraints to ensure valid data enters the database
• Isolation: Controls concurrency
• Durability: Once a transaction is committed, it remains committed

13. What is Dateadd in SQL?

Dateadd is a function that is used to add a number to a specified part of the date and returns the modified date. The syntax is DATEADD (date_part, value, input_date).

The Date part can take any of the following forms:

14. What is SAVEPOINT in transaction control?

A SAVEPOINT is a point in a transaction when you can roll the transaction back to a certain point without rolling back the entire transaction.

1 row inserted.

Rollback complete.

15. What is a Natural Join?

A natural join by default is an inner join that creates an implicit join based on the common columns in the two tables being joined:

A NATURAL JOIN can be an INNER join, a LEFT OUTER join, or a RIGHT OUTER join. The default is INNER join.

If the tables COUNTRIES and CITIES have two common columns named COUNTRY and COUNTRY_ISO_CODE, the following two SELECT statements are equivalent:

16. What is a Cross Join?

In an SQL cross join, a combination of every row from the two tables is included in the result set. This is also called cross-product join.

For example, if table A has ten rows and table B has 20 rows, the result set will have 10 * 20 = 200 rows provided there is a NOWHERE clause in the SQL statement.

17. What are the subsets of SQL?

The following are the subsets of SQL

• DDL(Data Definition Language) : Includes SQL commands like CREATE, ALTER, and DELETE
• DML(Data Manipulation Language): Accesses and manipulates data, including the INSERT and UPDATE commands
• DCL(Data Control Language): Controls access to the database. Uses commands like GRANT and REVOKE.

18. What are scalar functions in SQL?

Scalar Functions are used to return a single value based on the input values. Scalar Functions are as follows:

• UCASE(): Converts the specified field in upper case
• LCASE(): Converts the specified field in lower case

19. What is a cursor, and when do you use it?

A cursor is a database object which is used to manipulate data by traversing row by row in a result set. A cursor is used when you need to retrieve data, one row at a time from a result set and when you need to update records one row at a time.

20. What is a set-based solution?

Cursors operate on individual rows, and in the case of a set, it works on a resultant set of data, which could be a table/view or a join of both. The resultant set is an output of a SQL query.

21. What is a forward cursor?

Forward cursors support fetching of rows from start to end from a result set. You cannot go to the previous row in the result set.

22. State one situation where set-based solutions are better than cursor-based solutions.

Set-based solutions provide better performance when you are working on a result set, as opposed to one row at a time. They are concise and more readable.

23. What is normalization and what are the normal forms?

Normalization is a process in database design to minimize data redundancy and dependency. The database is divided into two or more tables, and relationships are defined between them.

First Normal Form

Every record is unique in a table and is identified by a primary or a composite key.

StudiD Name Phonenum

-----------------------

1 John 9176612345,9176645698

2 Susie 9176645789

3 Jim 9176696325

In the above table the field ‘phonenum’ is a multi-valued attribute, so it is not in 1NF.

The table below is in 1NF as there is no multi-valued attribute.

------------------

1 John 9176612345

1 John 9176645698

Second Normal Form

The table must be in First Normal Form, and it should have a single column as its primary key. 2NF tries to reduce the redundant data getting stored in memory. To transform the table above into 2NF, we split the table into two tables:

StudiD Name /* student table */

StudiD Phonenum /* studentphonenumber table */

1 9176612345

1 9176645698

2 9176645789

3 9176696325

Third Normal Form

The table must be in Second Normal Form and must have no transitive functional dependencies, i.e., a non-key column must not be dependent on another non-key column within the same table.

Consider the EMPLOYEE_DETAIL table: This table is not in the third normal form because the fields emp_state and emp_city depend on emp_zip and not on the primary key emp_id.

The table above is split into 2 tables and now the tables are in the third normal form.

EMPLOYEE table:

EMPLOYEE_ZIP table:

24. What is denormalization and when do you use it?

Denormalization is a technique used to improve performance so the table design allows you to avoid complex joins with redundant data. If the application involves heavy read operations, then denormalization is used at the expense of the write operations performance.

25. What are clustered indexes and non-clustered indexes?

A table can have only one clustered index. In this type of index, it reorders the table based on the key values and physically stores them in that order.

The non-clustered index does not have the physical ordering of the data in the table; it has a logical order.

The script above creates a clustered index named “IX_tblStudent_Gender_Score” on the student table. This index is created on the “gender” and “total_score” columns. An index that is created on more than one column is called the “composite index”.

A non-clustered index doesn’t sort the physical data inside the table. A non-clustered index is stored in one place, and table data is stored in another place. This allows for more than one non-clustered index per table.

The script above creates a non-clustered index on the “name” column of the student table — the index sorts by name in ascending order. The table data and index will be stored in different places.

26. What is T-SQL?

It is an extension of SQL (Structured Query Language) developed by Sybase and used by Microsoft.

27. What are system functions? Offer an example.

System functions are operations performed on the database server, and values are returned accordingly. Example @@ERROR - Returns 0 if the previous Transact-SQL statement encountered no errors. Otherwise, it returns an error number.

@@ERROR - Returns 0 if the previous Transact-SQL statement encountered no errors.

28. What is a transaction log?

A log is an audit trail file where the history of actions executed by the DBMS is stored.

29. How do you maintain database integrity when deletions from one table automatically cause deletions in another?

ON DELETE CASCADE is a command that is used when deletions happen in the parent table, and all child records are automatically deleted, and the child table is referenced by the foreign key in the parent table.

The Products table is the parent table and the inventory table is the child table. If a productid is deleted from the parent table all the inventory records for that productid will be deleted from the child table

30. Can we use TRUNCATE with a WHERE clause?

No, we cannot use TRUNCATE with the WHERE clause.

31. Define COMMIT.

When a COMMIT is used in a transaction, all changes made are written into the database permanently.

The example above deletes a job candidate in an SQL server.

32. What does CHECK CONSTRAINT do?

Check Constraint limits the values that can enter a column in a database table. It is used as an integrity constraint check.

The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created. The CHECK constraint ensures that you can not have any person below 18 years:

The syntax below is in MySQL.

33. What is a schema?

A schema is a collection of database objects in a database for a particular user/owner. Objects can be tables, views, indices, and so on.

34. How can you create an empty table from an existing table?

35. what is a composite key.

When more than one column is used to define the primary key, it is called a composite key. Here is a SQL syntax to create a composite key in MySQL:

36. How do you sort records in a table?

The ORDER BY Clause is used to sort records in a table.

By default, the records are returned in ascending order.

37. What is a shared lock?

When two transactions are granted read access to the same data, they are given a shared lock. This enables reading the same data, and data is not updated until the shared lock is released.

38. What is a deadlock?

A deadlock is a situation where two or more transactions are waiting indefinitely for each other to release the locks.

The following is an example of a deadlock situation:

39. What is lock escalation?

Lock escalation is the process of converting row or page locks into table locks. It is an optimization technique used by RDBMS like SQL Server dynamically.

40. What is SQL injection?

SQL injection is a code injection technique used to hack data-driven applications.

41. What are views, and why are they used?

SQL views are virtual tables created from one or more tables. Views are a subset of data; hence, it can limit the degree of exposure of data from the tables.

The following SQL creates a view that shows all customers from Brazil:

You can query the view above as follows:

42. How do we avoid getting duplicate entries in a query?

The Select DISTINCT is used to get distinct data from tables using a query.

The following SQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table:

43. Give an example of a comparison operator in SQL.

EQUAL TO written as = is used to compare data values

44. What is a Subquery?

A subQuery is a SQL query nested into a larger Query.

The query placed within the parentheses is called a subquery. It is also known as an inner query or inner select. The query that contains the subquery is called an outer query or an outer select.

45. What is a non-correlated subquery?

A non-correlated subquery is an independent query, and the output of the subquery is substituted in the main query.

Advanced SQL Interview Questions and Answers

46. what is a system privilege.

This is when rights are given to a user, usually by the DBA, to perform a particular action on the database schema objects like creating tablespaces.

The following are examples of system privileges that can be granted to users:

• CREATE TABLE allows a grantee to create tables in the grantee's schema
• CREATE USER allows a grantee to create users in the database
• CREATE SESSION allows a grantee to connect to an Oracle database to create a user session

47. What are Object Privileges?

An object-level privilege is a permission granted to a database user account or role to perform some action on a database object. These object privileges include SELECT, INSERT, UPDATE, DELETE, ALTER, INDEX on tables, and so on.

The following examples are object privileges that can be granted to users:

• SELECT ON hr.employees TO myuser
• INSERT ON hr.employees TO myuser

48. What does the BCP command do?

The BCP (Bulk Copy) is a utility or a tool that exports/imports data from a table into a file and vice versa.

49. What does the VARIANCE function do?

This function returns the VARIANCE of a set of numbers:

50. What is the role of GRANT and REVOKE commands?

The GRANT command enables privileges on the database objects and the REVOKE command removes them. They are DCL commands.

51. What is a UNION operator?

The UNION operator combines the results of two or more SELECT statements by removing duplicate rows. The columns and the data types must be the same in the SELECT statements.

52. Where are stored procedures stored and can we call one inside another?

Stored Procedures are stored in the Data Dictionary of the database.

Yes, we can call a stored procedure from another stored procedure. For example, Procedure2 is the procedure which is called Procedure1. Both Procedure1 and Procedure2 can have business logic implemented in it.

53. Does the data stored in the stored procedure increase access time or execution time?

Data stored in stored procedures can be retrieved much faster than the data stored in the SQL database. Data can be precompiled and stored in stored procedures. This reduces the time gap between query and compiling as the data has been pre-compiled and stored in the procedure. Procedures may or may not return values.

54. Can a view be active if the base table is dropped?

No, the view cannot be active if the parent table is dropped.

55. What is a One-Many Relationship in SQL?

In a One-Many relationship, a record in one table can be associated or related to many records in another table.

56. Distinguish between a table and a field in SQL.

The collection of data organized in the form of columns and rows refers to the table. The number of columns in a table refers to the field.

Table: Employee_Details

Fields: Emp_id, Emp_name, Emp_dept, Emp_salary

57. What is data integrity?

Data integrity defines the accuracy, consistency, and reliability of data that is stored in the database.

There are four kinds of data integrity:

• Row integrity
• Column integrity
• Referential integrity
• User-defined integrity

58. What are entities and relationships?

• Entity: A person, place, or any real-world thing that can be represented as a table is called an entity. An example is an employee table that represents the details of an employee in an organization.
• Relationship: Relationship defines the dependency that entities share amongst each other. An example is the fact that an employee name, ID, salary might belong to the same or different tables.

59. How do TRUNCATE and DELETE Differ?

60. What is the difference between null, zero, and blank space?

NULL refers to a value that is unknown, not available, inapplicable, or unassigned. Zero is a number, and blank space is treated as a character.

61. Which function is used to return the remainder in a division operator in SQL?

The MOD function returns the remainder in the division operation.

62. What are case manipulation functions?

Case manipulation functions convert existing data in the table to lower, upper or mixed case characters.

63. What are the different case manipulation functions in SQL?

• LOWER: Converts all the characters to lowercase
• UPPER: Converts all the characters to uppercase
• INITCAP: Converts initial character of each word to uppercase

64. What are the different character manipulation functions?

• CONCAT: Joins two or more string values
• SUBSTR: Extracts string of a specific length
• LENGTH: Returns the length of the string
• INSTR: Returns the position of the specific character
• LPAD: Padding of the left-side character value for right-justified value
• RPAD: Padding of right-side character value for left-justified value
• TRIM: Removes the defined character from beginning and end or both
• REPLACE: Replaces a specific sequence of characters with another sequence of characters

65. Define inconsistent dependency.

The difficulty of accessing data as the path may be broken or missing defines inconsistent dependency. Inconsistent dependency enables users to search for data in the wrong different table which afterward results in an error as an output.

66. What are GROUP functions? Why do we need them?

Group functions work on a set of rows and return a single result per group. The popularly used group functions are AVG, MAX, MIN, SUM, VARIANCE, COUNT

67. Distinguish between BETWEEN and IN conditional operators.

BETWEEN displays the rows based on a range of values. IN checks for values contained in a specific set of values.

68. What is the MERGE statement?

The statement enables conditional updates or inserts into the table. It updates the row if it exists or inserts the row if it does not exist.

69. Explain the recursive stored procedure.

A stored procedure calling itself until it reaches some boundary condition is a recursive stored procedure. It enables the programmers to use a set of code any number of times.

70. How can dynamic SQL be executed?

It can be executed in the following ways:

• By executing the query with parameters.
• By using EXEC
• By using sp_executesql

71. What is the stored procedure?

It is a function consisting of many SQL statements to access the database system. Several SQL statements are consolidated into a stored procedure and are executed wherever and whenever required.

72. What is auto increment?

This keyword allows a new unique number to be generated whenever a new record is inserted into the table. It can be used wherever we need the PRIMARY KEY.

73. What is a data warehouse?

Data from multiple sources of information is stored in a central repository called the data warehouse. Data warehouses have subsets of data called data marts. The data stored is transformed and used for online mining and processing.

74. What are user-defined functions?

Functions written to use the specific logic whenever required are user-defined functions. It avoids redundancy by avoiding writing the same logic again.

75. What is the ALIAS command?

This command provides another name to a table or a column. It can be used in the WHERE clause of a SQL query using the as keyword.

S and E are alias names for student table and exam table respectively.

76. What is Collation?

Collation is defined as the set of rules that determines how to store and compare data.

77. Mention the different types of collation sensitivity.

The following are the types of collation sensitivity:

78. What are STUFF and REPLACE functions?

STUFF: Overwrites the existing character or inserts a string into another string. The syntax is:

REPLACE: replaces the existing characters of all the occurrences. The syntax is:

• Start Preparing With These SQL Interview Questions

With the help of this list of the top SQL interview questions, you will stand a better chance of nailing interviews for Database Administrator and SQL developer positions.

It will even help with an SQL server certification . You could also use the SQL in 10 Minutes, Sams Teach Yourself book to help prepare.

SQL Interview Questions/Answers PDF

Good luck! And don’t forget, on top of reading SQL questions for interviews, you have to practice hands-on.

People are also reading:

• SQL Courses
• SQL Certifications
• Download SQL Cheat Sheet PDF
• Top DBMS Interview Questions & Answers
• Difference between MongoDB and MySQL
• Create a Database in MySQL
• Difference between OLTP and OLAP
• What is MongoDB?
• Basic SQL Commands

Simran works at Hackr as a technical writer. The graduate in MS Computer Science from the well known CS hub, aka Silicon Valley, is also an editor of the website. She enjoys writing about any tech topic, including programming, algorithms, cloud, data science, and AI. Traveling, sketching, and gardening are the hobbies that interest her.

Subscribe to our Newsletter for Articles, News, & Jobs.

Disclosure: Hackr.io is supported by its audience. When you purchase through links on our site, we may earn an affiliate commission.

In this article

• Top 48 Networking Interview Questions and Answers in 2024 Computer Networks Career Development Interview Questions
• Top 20 REST API Interview Questions & Answers [2024] Web Development Career Development Interview Questions
• Top 45 QA Interview Questions and Answers in 2024 Software Engineering Software Testing Career Development Interview Questions

Please login to leave comments

Always be in the loop.

Get news once a week, and don't worry — no spam.

{{ errors }}

{{ message }}

• Help center
• We ❤️ Feedback
• Advertise / Partner
• Write for us
• Privacy Policy
• Cookie Policy
• Change Privacy Settings
• Disclosure Policy
• Terms and Conditions
• Refund Policy

Disclosure: This page may contain affliate links, meaning when you click the links and make a purchase, we receive a commission.

Pardon Our Interruption

As you were browsing something about your browser made us think you were a bot. There are a few reasons this might happen:

• You've disabled JavaScript in your web browser.
• You're a power user moving through this website with super-human speed.
• You've disabled cookies in your web browser.
• A third-party browser plugin, such as Ghostery or NoScript, is preventing JavaScript from running. Additional information is available in this support article .

To regain access, please make sure that cookies and JavaScript are enabled before reloading the page.

SQL Practice for Beginners: AdventureWorks Exercises

• sql practice
• online practice

Table of Contents

Reviewing the AdventureWorks Database

Exercise 1: select the job titles of all single male employees, exercise 2: select employees whose pay rate is 50 or more, exercise 3: select all employees who joined new departments in 2008, exercise 4: select departments whose names match the pattern, exercise 5: select departments that belong to certain groups, exercise 6: select employees and their departments, exercise 7: select female employees’ job titles and dates of department change, exercise 8: select job titles per department, exercise 9: select employees with their departments and shifts, exercise 10: select employees hired after 2010 with their departments and shifts, exercise 11: select the highest and lowest sick leave hours, exercise 12: select the average number of vacation hours per job title, exercise 13: select the count of employees based on their gender, exercise 14: select the count of departments in each group, exercise 15: select the sum of sick leave hours for each department, exercise 16: select employees with their current pay rate, exercise 17: select the minimum, average, and maximum pay rates, exercise 18: select employee ids with their out-of-office hours, exercise 19: select employee ids with certain job titles and departments, exercise 20: list sales reps and marketing managers with their departments, practice sql with adventureworks exercises.

Hone your SQL skills with hands-on exercises using the AdventureWorks sample database. Practice essential SQL features using real-world scenarios.

Practicing SQL is important if you want to get better at using it. This article has 20 beginner-friendly exercises using the AdventureWorks sample database from Microsoft. This database is designed to show how SQL Server works. It represents a fictitious bicycle manufacturing company called AdventureWorks Cycles and includes five schemas: HumanResources, Person, Production, Purchasing, and Sales. This makes it great for learning and practicing SQL.

The AdventureWorks database covers various business scenarios such as manufacturing, sales, purchasing, product management, contact management, and human resources. This makes it an excellent resource for learning and practicing SQL because it provides a wide range of data and business processes to work with. By practicing with this database, you can gain hands-on experience with real-world data and scenarios; this will help you understand how to write efficient queries and effectively solve data problems.

If you want to try other sample databases, check out our list of the most interesting data sources for SQL practice . However, setting up your own SQL practice environment can take time. For quick practice, try our SQL Practice track or our SQL practice databases . We also release one free SQL course each month to help you keep learning.

Now, let’s start practicing SQL with AdventureWorks exercises!

The AdventureWorks database comprises five database schemas (that is, database structures used to group data tables), including HumanResources , Person , Production , Purchasing , and Sales . In the following exercises, we will use the HumanResources schema.

Let’s start by analyzing the schema:

First, let’s review the concepts of primary and foreign keys:

• The primary key (PK) is a column (or a set of columns) that uniquely identifies each row in a table. For example, the BusinessEntityID column is the primary key of the Employee table because each employee is assigned a unique ID number.
• The foreign key (FK) is a column (or a set of columns) that links two tables. Note that the foreign key of one table is the primary key of another table – based on that, the two tables are linked. For example, the BusinessEntityID column is the primary key of the Employee table and also a foreign key in the JobCandidate table. This links both tables together and lets the JobCandidate table refer to rows in the Employee

Now, let’s analyze the tables in the schema.

The Employee table stores information about the employees and is the main table of this schema. It is linked with the following tables:

• The JobCandidate table stores resumes of job applicants. The BusinessEntityID column is a PK in the Employee table and an FK in the JobCandidate The PK of the JobCandidate table is the JobCandidateID column.
• The EmployeePayHistory table stores the history of employees’ pay rates. The BusinessEntityID column is a PK in the Employee table and an FK in the EmployeePayHistory Note that the BusinessEntityID column in the EmployeePayHistory table is both an FK and part of the PK at the same time. The PK of the EmployeePayHistory table comprises both the BusinessEntityID and RateChangeDate columns. This is called a composite primary key .
• The EmployeeDepartmentHistory table stores the history of employees’ departments. The BusinessEntityID column is a PK in the Employee table and an FK in the EmployeeDepartmentHistory Note that the BusinessEntityID column in the EmployeeDepartmentHistory table is both an FK and part of the PK at the same time. The PK of the EmployeeDepartmentHistory table comprises the BusinessEntityID , DepartmentID , ShiftID , and StartDate columns. This is another composite primary key.

The EmployeeDepartmentHistory table stores information about employees and their departments over time and can be considered as another main table of this schema. It is linked with the following tables:

• The Shift table stores information about available shifts. The ShiftID column is a PK in the Shift table and an FK in the EmployeeDepartmentHistory Note that the ShiftID column is both an FK and part of the PK in the EmployeeDepartmentHistory table.
• The Department table stores information about departments. The DepartmentID column is a PK in the Department table and an FK in the EmployeeDepartmentHistory Note that the DepartmentID column is both an FK and part of the PK in the EmployeeDepartmentHistory table.

Now we are ready to begin our AdventureWorks exercises.

AdventureWorks Exercises for Beginners

We’ll cover all the basics of SQL (and dabble in some advanced features) as we move through this article:

• Single table queries for retrieving and filtering data.
• Multiple table queries that use JOINs to combine data from two or more tables.
• Grouping and aggregating data to perform mathematical operations on selected data.
• Other relevant SQL features like subqueries, UNION and INTERSECT, and common table expressions (CTEs).

Part 1: Single Table Queries

In this part, we will retrieve and filter data.

Note: To write correct queries, you should first familiarize yourself with the data stored in the tables.

Exercise: Select the job title of all male employees who are not married.

Explanation: We select the JobTitle column from the Employee table. Here the SQL syntax can be read exactly like plain English.

The filtering conditions are placed in the WHERE clause:

• We want to select only male employees, so we impose a condition on the Gender column: Gender = 'M' .
• We want to select only employees that are not married, so we impose another condition on the MaritalStatus column: MaritalStatus != 'M' .

Since both the conditions should apply at the same time, we use the AND operator to combine them.

Exercise: Select the BusinessEntityID , Rate , and RateChangeDate for all employees whose pay rate has ever been 50 or more.

Explanation: We select three columns – BusinessEntityID , Rate , and RateChangeDate – from the EmployeePayHistory table.

Then we provide a filtering condition in the WHERE clause to retrieve only the ones whose pay rate has ever been greater than or equal to 50: Rate >= 50 .

Exercise: Select the BusinessEntityID , DepartmentID , and StartDate for each employee who started working in any department in 2008.

Explanation: We select IDs of employees ( BusinessEntityID ), the IDs of departments ( DepartmentID ), and the date when the employee joined that department ( StartDate ) from the EmployeeDepartmentHistory table.

As we want to list only employees who joined new departments in 2008, we impose a condition on the StartDate column: StartDate BETWEEN '2008-01-01' AND '2008-12-31' .

This condition is self-explanatory, as it is understandable in plain English. We want the StartDate value to be between Jan 1, 2008 and Dec 31, 2008, ensuring we cover all days in 2008.

Exercise: Select the department IDs, names, and group names for all departments whose name starts with ‘Prod’ OR for all departments whose group name ends with ‘ring’.

Explanation: We select DepartmentID , Name , and GroupName columns from the Department table.

Then, we provide the WHERE clause conditions:

• We want to select departments whose name starts with ‘Prod’, so we impose a condition on the Name column: Name LIKE 'Prod%' .
• We also want to select departments whose group name ends with ‘ring’, so we impose a condition on the GroupName column: GroupName LIKE '%ring' .

The LIKE keyword lets us define the pattern which the column value should match. For example, we want the Name column to start with ‘Prod’, so the pattern is 'Prod%'; % stands for any sequence of characters.

Because we want to find all records where at least one of the conditions is true, we use the OR keyword.

Exercise: Select department names that belong to either the ‘Research and Development’ group or the ‘Manufacturing’ group.

Explanation: We select the Name column from the Department table.

As we want to list departments that belong to defined groups, we use the IN keyword in the WHERE clause condition: GroupName IN ('Research and Development', 'Manufacturing') .

This ensures that we output all departments that belong to groups listed in the IN keyword.

Part 2: Multiple Table Queries

In this part, we will use JOINs to combine data from multiple tables.

Note: To write correct queries, you should first familiarize yourself with data stored in the relevant tables.

Exercise: Select employees’ IDs with all the names of the departments where they have ever worked.

Explanation: We select the BusinessEntityID column from the EmployeeDepartmentHistory table and the Name column from the Department table.

We join these two tables using the JOIN clause on their common column, DepartmentID .

Exercise: Select the employee ID and job title along with the dates when the employee changed departments ( StartDate ) for all female employees.

Explanation: We select the BusinessEntityID and JobTitle columns from the Employee table and the StartDate column from the EmployeeDepartmentHistory table.

We use the JOIN clause to join the tables on their common column, BusinessEntityID .

As we want to list this information only for female employees, we impose a condition on the Gender column: e.Gender = 'F' .

Exercise: Select job titles and their corresponding department names to find all job titles that have ever been used in each department. Do not include multiples of the same job title.

Explanation: We select the JobTitle column from the Employee table and the Name column from the Department table.

To select distinct job titles (i.e. no duplicates), we use the DISTINCT keyword before the column names.

We must join the Employee table with the EmployeeDepartmentHistory table on their common column, BusinessEntityID . Then we join the EmployeeDepartmentHistory   table with the Department table on their common column, DepartmentID .

Exercise: Select distinct department names  and shift names (no duplicate pairs) that employees in each department work. Rename the department name to DepartmentName and shift name to ShiftName .

Explanation: We select the Name column from the Department table and the Name column from the Shift table, providing alias names for them using the AS keyword. We use DISTINCT to select distinct pairs.

To select department names and shift names, we must join these three tables:

• We join the EmployeeDepartmentHistory table with the Department table on the DepartmentID
• We join the EmployeeDepartmentHistory table with the Shift table on the ShiftID

Each of the JOIN clauses has an ON clause that defines the common columns on which join is performed.

Exercise: Select employees’ IDs, department names, and shift names. Include only employees hired after 2010-01-01 and who work for departments within the Manufacturing and Quality Assurance groups.

Explanation: We select the BusinessEntityID column from the Employees table, the Name column from the Department table, and the Name column from the Shift table.

To select employee IDs along with department names and shift names, we must use three joins:

• We join the Employee table with the EmployeeDepartmentHistory table on the BusinessEntityID column.
• We join the EmployeeDepartmentHistory table with the Shift table on the ShiftID column.

Then we provide the WHERE clause conditions as follows:

• We want to list all employees hired after Jan 1, 2010, so we impose a condition on the HireDate column: HireDate > '2010-01-01' .
• We want to list only the employees that belong to certain department groups, so we use the IN keyword to create this condition: GroupName IN ('Manufacturing', 'Quality Assurance') .

Part 3: Grouping and Aggregating Data

In this part, we will group and aggregate data so we can perform mathematical operations on selected data.

Exercise: Select the minimum and maximum number of sick leave hours taken by the employees.

Explanation: We use the MIN() and MAX() aggregate functions – providing the SickLeaveHours column from the Employee table as an argument – to select the minimum and maximum numbers of sick leave hours taken by the employees.

Exercise: Select the job titles and the average number of vacation hours per job title.

Explanation: We select the JobTitle column from the Employee table.

We use the AVG() aggregate function to get the average number of vacation hours for each job title. We want to have groups of data based on the distinct values in the JobTitle column; this requires us to use the GROUP BY clause with the JobTitle column as its argument.

Exercise: Select the employees’ gender and the count of employees of each gender.

Explanation: We select the Gender column from the Employee table.

We use the COUNT() aggregate function to get the count of employees for each gender. This requires us to use the GROUP BY clause with the Gender column as its argument; we want to have groups of data based on the distinct values in the Gender column.

Exercise: Find the count of departments in each department group. List only those department group names that have more than two departments.

Explanation: We select the GroupName column from the Department table.

We use the COUNT() aggregate function to get the count of departments in each department group. This requires us to use the GROUP BY clause with the GroupName column as its argument.

To impose a condition on the aggregate function, we use the HAVING clause after the GROUP BY clause: HAVING COUNT(*) > 2 .

Exercise: Select the department names and the sum of sick leave hours taken by employees working currently in each department. Rename this column to SumSickLeaveHours .

Explanation: We select the Name column from the Department .

We use the SUM() aggregate function to get the sum of sick leave hours used by employees for each department. This requires us to use the GROUP BY clause with the Name column from the Department table as its argument.

We must join the Employee table with the EmployeeDepartmentHistory table on the BusinessEntityID column. Then we join the EmployeeDepartmentHistory   table with the Department table on the DepartmentID column.

As we consider only the employees that currently work in any department, we impose a condition that the EndDate column from the EmployeeDepartmentHistory table must be NULL .

Part 4: Other SQL features

In this part, we will cover subqueries (i.e. queries nested within queries), operators like UNION and INTERSECT , and common table expressions (CTEs).

Exercise: Select employees’ IDs and their current pay rates.

Explanation: We select the BusinessEntityID and Rate columns from the EmployeePayHistory table.

Since the EmployeePayHistory table stores the history of pay rates for each employee, we need to impose a condition on the RateChangeDate column value to be equal to an employee’s most recent date of pay rate change. We do this by defining a subquery in the WHERE clause that selects the most recent date when the pay rate was modified. This subquery uses the MAX() aggregate function to select the most recent date.

We’re selecting the most recent RateChangeDate for each employee separately, so we add a WHERE clause in the subquery to match the employee IDs between outer and inner queries.

Exercise: Select the minimum, average, and maximum pay rates from employees’ current pay rates.

Explanation: We use the MIN() , AVG() , and MAX() aggregate functions to select the minimum, average, and maximum pay rates, providing the CurrentPayRate column from the subquery as an argument.

We use the subquery in the FROM clause. This subquery selects the current pay rates for each employee, as explained in Exercise 16.

Note this solution nests two subqueries. First, we use a subquery in the FROM clause. And then that subquery uses a subquery in its own WHERE clause.

Exercise: Select all employee IDs who took more than 60 hours of vacation or who took more than 60 hours of sick leave.

Explanation: The first query selects the BusinessEntityID column from the Employee table and uses the SUM() aggregate function to sum the number of vacation hours taken by each employee. We group by the employee ID and impose a condition that the sum must be greater than 60.

Analogically, the second query selects the BusinessEntityID column from the Employee table and uses the SUM() aggregate function to sum the number of sick leave hours taken by each employee. We group by the employee ID and impose a condition that the sum must be greater than 60.

The UNION operator combines the output of both queries.

Exercise: Select the IDs of employees who have the job titles ‘Sales Representative’ or ‘Tool Designer’ and who have worked (or are working) in the Sales or Marketing departments.

Explanation: The first query selects the BusinessEntityID column from the Employee table. It filters the JobTitle column to include only Sales Representatives and Tool Designers.

The second query selects the BusinessEntityID column from the EmployeeDepartmentHistory table. It filters the department name to include only Sales and Marketing departments.

The INTERSECT operator finds the common output of the two queries – that is, it will output only the  IDs of employees that fulfill the WHERE clause conditions of both queries.

Exercise: Select the IDs, job titles, and department names for employees associated with Sales Representative or Marketing Manager roles.

Explanation: The query uses a JOIN to link the Employee table with the EmployeeDepartmentHistory table based on the BusinessEntityID column. This join ensures that each employee's record is connected to their department history.

Another JOIN links the EmployeeDepartmentHistory table to the Department table using the DepartmentID . This allows the query to retrieve department information related to the employee's job history.

The WHERE clause filters the results to include only those employees whose job titles are either ‘Sales Representative’ or ‘Marketing Manager’. This filtering is done using the IN operator, which specifies the desired job titles. The query's structure efficiently combines and filters data from multiple tables to return the relevant employee job titles and their associated departments.

In this article, you explored the fundamentals of SQL through using exercises with the AdventureWorks database. You learned how to perform single table queries with SELECT , combine table data using JOIN , group data with GROUP BY , and use aggregate functions like COUNT() , AVG() , SUM() , MIN() , and MAX() . Additionally, you delved into subqueries, UNION , and INTERSECT .

For more exercises with solutions and detailed explanations, check out our articles 10 Beginner SQL Practice Exercises With Solutions and SQL Joins: 12 Practice Questions with Detailed Answers . Dive deeper into SQL practice opportunities with our comprehensive Guide to SQL Practice at LearnSQL.com . And as I mentioned earlier, you can also continue your practice with our SQL Practice track , SQL practice databases , or monthly free SQL course .

Good luck on your SQL journey!

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