sql assignment questions

50 SQL Query Practice Questions for Interview

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

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

SQL Query Questions and Answers for Practice

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

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

• Most Frequently Asked SQL Interview Questions

Prepare Sample Data To Practice SQL Skills

Sample table – worker.

Sample Table – Bonus

Sample Table – Title

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

SQL Script to Seed Sample Data.

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

Start with 20 Basic SQL Questions for Practice

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

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

The required query is:

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

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

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

Q-21. Write an SQL query to fetch the count of employees working in the department ‘Admin’.

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

The general query to clone a table with data is:

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

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

The following MySQL query returns the current date:

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

Specify the SQL query in the below code box:

18 Complex SQL Queries for Practice

Now, that you have built a solid foundation in intermediate SQL, It’s time to practice with some advanced SQL query questions with answers. These interview questions involve queries with more complex SQL syntax and concepts, such as nested queries, joins, unions, and intersects.

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

MySQL query to find the nth highest salary:

SQL Server query to find the nth highest salary:

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

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

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

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

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

Practicing SQL query interview questions is a great way to improve your understanding of the language and become more proficient in SQL. In addition to improving your technical skills, practicing SQL query questions can help you advance your career. Many employers seek candidates with strong SQL skills, so demonstrating your proficiency can get you a competitive edge.

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

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

The following query returns the expected result:

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

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

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

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

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

Thanks for reading! We hope you found this tutorial helpful. If yes, please share it on Facebook / Twitter with your friends and colleagues You can also follow us on our social media platforms for more resources. if you seek more information on this topic, check out the “You Might Also Like” section below.

SQL Performance Interview Guide

Check 25 SQL performance-related questions and answers.

Keep Learning SQL, TechBeamers.

You Might Also Like

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

Leave a Reply

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

Popular Tutorials

SQL Tutorial

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

You can test your SQL skills with W3Schools' Exercises.

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

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

Count Your Score

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

Start SQL Exercises

Start SQL Exercises ❯

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

Kickstart your career

Get certified by completing the course

COLOR PICKER

Contact Sales

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

Report Error

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

Top Tutorials

Top references, top examples, get certified.

• Cheat Sheet

SQL Practice Questions

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

Reset your password

Please confirm your email to start using sqlinterview.com.

• SQL Server training
• Write for us!

Learn SQL: Practice SQL Queries

Today is the day for SQL practice #1. In this series, so far, we’ve covered most important SQL commands ( CREATE DATABASE & CREATE TABLE , INSERT , SELECT ) and some concepts ( primary key , foreign key ) and theory ( stored procedures , user-defined functions , views ). Now it’s time to discuss some interesting SQL queries.

Let’s take a quick look at the model we’ll use in this practice.

You can expect that in real-life situations (e.g., interview), you’ll have a data model at your disposal. If not, then you’ll have the description of the database (tables and data types + additional description of what is stored where and how the tables are related).

The worst option is that you have to check all the tables first. E.g., you should run a SELECT statement on each table and conclude what is where and how the tables are related. This won’t probably happen at the interview but could happen in the real-life, e.g., when you continue working on an existing project.

Before We Start

The goal of this SQL practice is to analyze some typical assignments you could run into at the interview. Other places where this might help you are college assignments or completing tasks related to online courses.

The focus shall be on understanding what is required and what is the learning goal behind such a question. Before you continue, feel free to refresh your knowledge on INNER JOIN and LEFT JOIN , how to join multiple tables , SQL aggregate functions , and the approach to how to write complex queries . If you feel ready, let’s take a look at the first 2 queries (we’ll have some more in upcoming articles). For each query, we’ll describe the result we need, take a look at the query, analyze what is important for that query, and take a look at the result.

SQL Practice #1 – Aggregating & LEFT JOIN

Create a report that returns a list of all country names (in English), together with the number of related cities we have in the database. You need to show all countries as well as give a reasonable name to the aggregate column. Order the result by country name ascending.

Let’s analyze the most important parts of this query:

• We’ve used LEFT JOIN ( LEFT JOIN city ON country.id = city.country_id ) because we need to include all countries, even those without any related city
• We must use COUNT(city.id) AS number_of_cities and not only COUNT(*) AS number_of_cities because COUNT(*) would count if there is a row in the result (LEFT JOIN creates a row no matter if there is related data in other table or not). If we count the city.id , we’ll get the number of related cities
• The last important thing is that we’ve used GROUP BY country.id, country.country_name_eng instead of using only GROUP BY country.country_name_eng . In theory (and most cases), grouping by name should be enough. This will work OK if the name is defined as UNIQUE. Still, including a primary key from the dictionary, in cases similar to this one, is more than desired

You can see the result returned in the picture below.

SQL Practice #2 – Combining Subquery & Aggregate Function

Write a query that returns customer id and name and the number of calls related to that customer. Return only customers that have more than the average number of calls of all customers.

The important things I would like to emphasize here are:

• Please notice that we’ve used aggregate functions twice, once in the “main” query, and once in the subquery. This is expected because we need to calculate these two aggregate values separately – once for all customers (subquery) and for each customer separately (“main” query)
• The aggregate function in the “main” query is COUNT(call.id) . It’s used in the SELECT part of the query, but we also need it in the HAVING part of the query (Note: HAVING clause is playing the role of the WHERE clause but for aggregate values)
• Group is created by id and customer name. These values are the ones we need to have in the result
• In the subquery, we’ve divided the total number of rows ( COUNT(*) ) by the number of distinct customers these calls were related to ( COUNT(DISTINCT customer_id) ). This gave us the average number of calls per customer
• The last important thing here is that we used the CAST operator ( CAST(… AS DECIMAL(5,2)) ). This is needed because the final result would probably be a decimal number. Since both COUNTs are integers, SQL Server would also return an integer result. To prevent this from happening, we need to CAST both divider and the divisor as decimal numbers

Let’s take a look at what the query actually returned.

In today’s SQL practice, we’ve analyzed only two examples. Still, these two contain some parts you’ll often meet at assignments – either in your work, either in a testing (job interview, college assignments, online courses, etc.). In the next part, we’ll continue with a few more interesting queries that should help you solve problems you might run into.

Table of contents

• Recent Posts

• Learn SQL: How to prevent SQL Injection attacks - May 17, 2021
• Learn SQL: Dynamic SQL - March 3, 2021
• Learn SQL: SQL Injection - November 2, 2020

Related posts:

• Learn SQL: How to Write a Complex SELECT Query
• Learn SQL: Join multiple tables
• Learn SQL: Aggregate Functions
• Learn SQL: Set Theory
• Top SQL Server Books

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

In This Guide:

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

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

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

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

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

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

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

#1. What is SQL?

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

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

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

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

SQL commands are categorized into five primary types:

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

Knowing these command types is vital for effective database management.

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

#3. What is a primary key?

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

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

#4. What is a foreign key?

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

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

#5. What are aggregate functions?

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

Examples include:

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

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

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

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

#6. Explain the difference between DELETE and TRUNCATE commands

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

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

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

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

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

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

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

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

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

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

The different normal forms include:

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

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

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

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

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

Types of joins:

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

Example of an INNER JOIN :

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

#10. Explain the use of indexes in SQL

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

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

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

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

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

#11. What is a self-join?

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

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

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

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

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

Why use views?

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

Example of creating a view:

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

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

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

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

OUTER JOIN can be divided into three types:

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

Example of a LEFT OUTER JOIN:

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

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

#14. What is a subquery?

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

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

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

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

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

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

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

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

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

#16. What are window functions in SQL?

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

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

For example :

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

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

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

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

The ACID properties ensure that SQL transactions are processed reliably:

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

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

#18. What are stored procedures?

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

Example of a stored procedure that retrieves employee details:

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

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

#19. Explain the use of triggers in SQL

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

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

For example:

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

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

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

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

OLTP (Online Transaction Processing)

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

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

OLAP (Online Analytical Processing)

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

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

#21. Explain the differences between UNION and UNION ALL

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

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

Complete SQL + Databases Bootcamp

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

More from Zero To Mastery

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

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

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

Advertisement

• Oracle / PLSQL
• Web Development
• Color Picker
• Programming
• Techie Humor

Tutorial Resources

• Practice Exercises

• AND & OR
• COMPARISON OPERATORS
• IS NOT NULL
• SELECT LIMIT

SQL Advanced

• ALTER TABLE
• CREATE TABLE
• CREATE TABLE AS
• GLOBAL TEMP
• PRIMARY KEY

SQL Functions

SQL: Practice Exercises for SELECT Statement

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

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

Get started!

Return to Tutorial

Practice Exercise #1:

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

Solution for Practice Exercise #1:

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

These are the results that you should see:

Practice Exercise #2:

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

Solution for Practice Exercise #2:

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

Practice Exercise #3:

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

Solution for Practice Exercise #3:

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

Practice Exercise #4:

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

Solution for Practice Exercise #4:

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

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

Home | About Us | Contact Us | Testimonials | Donate

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

Copyright © 2003-2024 TechOnTheNet.com. All rights reserved.

SQL EXERCISES

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

Terms of Use

Shopping cart

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

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

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

• ▼MySQL Exercises
• Introduction
• ▼DML and DDL
• Create Table statement
• Insert Into statement
• Update Table statement
• Alter Table statement
• ▼Exercises on HR Database
• Basic SELECT statement
• Restricting and Sorting Data
• MySQL Aggregate Functions
• ▼Exercises on Northwind Database
• Products Table

MySQL Exercises, Practice, Solution

What is mysql.

MySQL is the world's most widely used open-source relational database management system (RDBMS), enabling the cost-effective delivery of reliable, high-performance and scalable Web-based and embedded database applications. It is widely-used as the database component of LAMP (Linux, Apache, MySQL, Perl/PHP/Python) web application software stack.

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 and MySQL . Hope, these exercises help you to improve your MySQL query skills. Currently following sections are available, we are working hard to add more exercises. Happy Coding!

Exercises on Data Manipulation Language (DML) & Data Definition Language (DDL)

• MySQL Create Table statement [20 Exercises]
• MySQL Insert Into statement [14 Exercises]
• MySQL Update Table statement [9 Exercises]
• MySQL Alter Table statement [15 Exercises]

Exercises on HR Database

• MySQL Basic SELECT statement [19 Exercises]
• MySQL Restricting and Sorting Data [11 Exercises with Solutions]
• MySQL Aggregate Functions and Group by [14 Exercises with Solutions]
• MySQL Subqueries [22 Exercises with Solution ]
• MySQL JOINS [13 Exercises with Solution]
• MySQL Date Time [21 Exercises with Solution]
• MySQL String Functions [17 Exercises with Solution]

Exercises on Northwind Database

• Exercises on Products Table [ 10 Exercises]

More to Come !

Structure of 'hr' database :

You may download the structure and data of the database used here

Click here to get Oracle Hr Database

Structure of 'northwind' database:

Click here to get Northwind sample databases for Microsoft SQL Server.

Follow us on Facebook and Twitter for latest update.

• Weekly Trends and Language Statistics

SQL Joins: 12 Practice Questions with Detailed Answers

• sql practice

Table of Contents

List of Exercises

Exercise 1: list all books and their authors, exercise 2: list authors and books published after 2005, exercise 3: show books adapted within 4 years and rated lower than the adaptation, exercise 4: show all books and their adaptations (if any), exercise 5: show all books and their movie adaptations, exercise 6: show all books with their reviews (if any), exercise 7: list all the books and all the authors, exercise 8: show products under 150 calories and their department, exercise 9: list all products with their producers, departments, and carbs, exercise 10: show all the products, prices, producers, and departments, exercise 11: list all workers and their direct supervisors, non-equi joins, exercise 12: show cars with higher mileage than a specific car, sql joins practice makes perfect. more practice perfect-er.

In this article, we dig into our SQL JOINS course and give you 12 join exercises to solve. But don’t worry – all the exercises have solutions and explanations. If you get stuck, help is there! This is, after all, made for practicing and learning. 

SQL joins can be tricky. It’s not just the syntax, but also knowing what joins to use in what scenarios.

Joins are used when combining data from two or more tables in SQL. The tables can be joined in several ways, and, depending on the tables, each way of joining them can result in a completely different result.  There’s no other way to learn this than practice. Yes, you can read explanations and typical uses of SQL joins. That helps, for sure! But practice builds on that through problem-solving and repetition, which makes your knowledge stick. The more you practice, the greater the possibility that the real-life data problems you’ll have to solve will be similar or completely the same as what you’ve already done!

And practice is what we’ll do in this article! We’ll show you exercises for basic and more advanced SQL joins uses. If you like them, you’ll enjoy our SQL JOINs course even more, as all the exercises are taken from there. In total, the course offers you 93 SQL joins exercises. They cover topics ranging from the types of joins in SQL, to filtering data, joining more than two tables, self-joining a table, and using non-equi joins.

OK, so let’s introduce the datasets and start exercising, shall we? Feel free to help yourself with the SQL JOIN Cheat Sheet as you go.

Here's a list of all exercises in the article:

INNER JOIN is a type of SQL join that returns only the matching rows from the joined tables.

To show you how this works, we’ll use Dataset 1 from the course.

The dataset consists of four tables: author , book , adaptation , and book_review .

The first table shows the author data in the following columns:

• id – The author’s unique ID within the database.
• name – The author’s name.
• birth_year – The year when that author was born.
• death_year – The year when that author died (the field is empty if they are still alive).

Here are the table’s first few rows:

The second table, book ,  shows details about books. The columns are:

• id – The ID of a given book.
• author_id – The ID of the author who wrote that book.
• title – The book’s title.
• publish_year – The year when the book was published.
• publishing_house – The name of the publishing house that printed the book.
• rating – The average rating for the book.

These are the first five rows:

The adaptation table has the following columns:

• book_id – The ID of the adapted book.
• type – The type of adaptation (e.g. movie, game, play, musical).
• title – The name of this adaptation.
• release_year – The year when the adaptation was created.
• rating – The average rating for the adaptation.

Here’s a snapshot of the data from this table:

The final table is book_review . It consists of the following columns:

• book_id - The ID of a reviewed book.
• review - The summary of the review.
• author - The name of the review's author.

Here’s the data:

Exercise: Show the name of each author together with the title of the book they wrote and the year in which that book was published.

Solution explanation: The query selects the name of the author, the book title, and its publishing year. This is data from the two tables: author and book . We are able to access both tables by using INNER JOIN . It returns only rows with matching values (values that satisfy the join condition) from both tables.

We first reference the table author in the FROM clause. Then we add the JOIN clause (which can also be written as INNER JOIN in SQL) and reference the table book .

The tables are joined on the common column. In this case, it's id from the table author and author_id from the table book . We want to join the rows where these columns share the same value. We do that using the ON clause and specifying the column names. We also put the table name before each column so the database knows where to look. That’s primarily because there’s an id column in both tables, but we want the id column only from the author table. By referencing the table name, the database will know from which table we need that column.

Solution output:

Here’s the output snapshot. We got all this data by joining two tables:

Exercise: Show the name of each author together with the title of the book they wrote and the year in which that book was published. Show only books published after 2005.

Solution explanation: This exercise and its solution are almost the same as the previous one. This is reflected by the query selecting the same columns and joining the tables in the same way as earlier.

The difference is that the exercise now asks us to show only books published after 2005. This requires filtering the output; we do that using the WHERE clause.

WHERE is a clause that accepts conditions used to filter out the data. It is written after joining the tables. In our example, we filter by referencing the column publish_year after WHERE and using the comparison operator ‘greater than’ ( > ) to find the years after 2005.

The output shows only one book published after 2005.

Exercise: For each book, show its title, adaptation title, adaptation year, and publication year.

Include only books with a rating lower than the rating of their corresponding adaptation. Additionally, show only those books for which an adaptation was released within four years of the book’s publication.

Rename the title column from the book table to book_title and the title column from the adaptation table to adaptation_title .

Solution explanation: Let’s start explaining the solution from the FROM and JOIN clauses. The columns we need to show are from the tables book and adaptation . We reference the first table in FROM and the second in JOIN .

In the ON clause, we equal the two book ID columns and specify the table of each column. This is the same as earlier, only with different table and column names.

Now, we need to select the required columns. The thing here is there’s a title column in both tables. To avoid ambiguity, a best practice is to reference the table name before each column in the SELECT .

Note: The above is mandatory only for ambiguous columns. However, it’s a good idea to do that with all columns; it improves code readability and the approach remains consistent.

After selecting the columns, we need to rename some of them. We do that using the keyword AS and writing a new column name afterward. That way, one title column becomes book_title , the other becomes adaptation_title . Giving aliases to the column names also helps get rid of ambiguity.

Now we need to filter the output. The first condition is that the adaptation had to be released four years or less after the book. We again use WHERE and simply deduct the book publish year from the adaptation release year. Then we say that the difference has to be less than or equal to ( <= ) 4.

We also need to add the second condition, where the book has a lower rating than the adaptation. It’s simple! The question implies that both the first and the second conditions have to be satisfied. The clue is in AND , a logical operator we use for adding the second condition. Here, it uses the ‘less than’ (< ) operator to compare the two ratings.

The output shows three book–adaptation pairs that satisfy the conditions.

Now that you get the gist of INNER JOIN , let’s move on to LEFT JOIN . It’s a type of outer join that returns all the columns from the left (the first) table and only the matching rows from the right (the second) table. If there is non-matching data, it’s shown as NULL .

You can learn more in our article about LEFT JOIN .

Exercise: Show the title of each book together with the title of its adaptation and the date of the release. Show all books, regardless of whether they had adaptations.

Solution explanation: We first select the required columns from the two tables. Then we join book (the left table) with adaptation (the right table) using LEFT JOIN . You see that the SQL join syntax is the same for INNER JOIN . The only thing that changes is the join keyword.

Note: SQL accepts both LEFT JOIN and LEFT OUTER JOIN . They are the same command.

The output snapshot shows the required data, with some of the data shown as NULL . These are the books without the adaptation.

Exercise: Show all books with their movie adaptations. Select each book's title, the name of its publishing house, the title of its adaptation, and the type of the adaptation. Keep the books with no adaptations in the result.

Solution explanation:

The question asks to show all the rows, even those without any adaptations. It’s possible that there are books without adaptations, so we use LEFT JOIN .

We first select the book title, its publishing house, its adaptation title, and its type.

Then we join book (the left table) with adaptation (the right table) using LEFT JOIN . We join the tables on the book ID. All the books that don’t satisfy the conditions will have NULL s as an adaptation title and type.

We filter data using WHERE . The first condition is that the adaptation type has to be a movie, so we equal the type column with a movie using the equal sign ( = ).  Note: When using text data in the WHERE condition, it must be enclosed in single quotes ( '' ).

The second filtering condition is added using the logical operator OR. It says that the type can also be NULL if it’s not a movie. The exercise asks us to keep books with no adaptations in the results.

Here’s the output snapshot. You can see that it shows only books adapted as movies or not adapted at all.

Where there’s LEFT JOIN , there’s also RIGHT JOIN , right? Despite being the LEFT JOIN's mirror image, it’s still a part of the SQL joins practice.

It’s a type of join that returns all the columns from the right (the second) table and only the matching rows from the left (the first) table. If there is non-matching data, it’s shown as NULL .

Exercise: Join the book_review and book tables using a RIGHT JOIN . Show the title of the book, the corresponding review, and the name of the review's author. Consider all books, even those that weren't reviewed.

We first select the required columns. Then we do as we’re told: join the tables using RIGHT JOIN . We join the tables on the book ID. The table book is the right table; we want all the data from it, regardless of the reviews.

As you can see, the syntax stays the same as in INNER JOIN and LEFT JOIN .

Note: SQL accepts both RIGHT JOIN and RIGHT OUTER JOIN .

The query returns all the book titles, their reviews, and authors. Where there’s no review or author information, a NULL is shown.

Here’s another join type that’s useful in some scenarios: the FULL JOIN . This is a LEFT JOIN and RIGHT JOIN put together. It shows matching rows from both tables, rows that have no match from the left table, and rows that have no match from the right table. In short, it shows all data from both tables.

You can read more about how and when to use FULL JOIN .

Exercise: Display the title of each book along with the name of its author. Show all books, even those without an author. Show all authors, even those who haven't published a book yet. Use a FULL JOIN .

Solution explanation: The question requires showing all books, but also all authors – FULL JOIN is perfect for doing this elegantly.

We select the book title and the author's name. Next, we FULL JOIN the table book with the table author . The joining condition is that the author ID has to be the same in both tables. Again, the syntax is the same as in all the previous join types.

Note: SQL accepts both FULL JOIN and FULL OUTER JOIN.

The output shows all the books and all the authors, whether the authors or books exist in both tables or not.

Joining 3 or More Tables

Yes, SQL joins allow for joining more than two tables. We’ll see how to do that in this part of the SQL joins practice. You can find a more detailed explanation of multiple joins here .

We also need a new dataset, so let’s introduce it.

The first table in the dataset is department . Its columns are:

• id – The unique ID of the department.
• name – The department name, i.e. where a particular type of product is sold.

Here’s the data from the table.

The second table is product , and it consists of the following columns:

• id – The ID of a given product.
• name – The product’s name.
• department_id – The ID of the department where the product is located.
• shelf_id – The ID of the shelf of that department where the product is located.
• producer_id – The ID of the company that manufactures this product.
• price – The product’s price.

Here’s the data snapshot:

The next table is nutrition_data . Its columns and data are given below:

• product_id – The ID of a product.
• calories – The calorific value of that product.
• fat – The amount of fat in that product.
• carbohydrate – The amount of carbohydrates in that product.
• protein – The amount of protein in that product.

The fourth table is named producer . It has the following columns:

• id – The ID of a given food producer.
• name – The name of the producer.

Below is the data from this table:

The last table in the dataset is sales_history . It has the following columns:

• date – The date of sale.
• product_id – The ID of the product sold.
• amount – The amount of that product sold on a particular day.

Here’s the data, too:

Exercise: List all products that have fewer than 150 calories. For each product, show its name (rename the column product ) and the name of the department where it can be found (name the column department ).

Solution explanation: The general principle of how you join the third (fourth, fifth…) table is that you simply add another JOIN . You can see how it’s done in this article explaining multiple joins . We’ll do it the same way here.

We first join the department table with the product table on the department ID using JOIN . But we also need the third table. To get the data from it, we just add another JOIN , which will join the product table with the nutrition_data table. The syntax is the same as with the first join. In this case, the query joins the tables on the product ID.

Then we use WHERE to find products with fewer than 150 calories. We finally select the product and department names and rename the columns as per the exercise instructions.

Note: You probably noticed both selected columns have the same original name. And you also noticed we solved this ambiguity by putting some strange short table names in front of all the columns in the query. These shortened names are table aliases, which you give by simply writing them after the table name in FROM or JOIN . By giving aliases to the tables, you can shorten the tables’ names. Therefore, you don’t have to write their full names (sometimes they can be really long!), but the short aliases instead. This saves time and space.

The output shows a list of the products and the department they belong to. It includes only those products with fewer than 150 calories.

Exercise: For each product, display the:

• Name of the company that produced it (name the column producer_name ).
• Name of the department where the product is located (name it department_name ).
• Product name (name it product_name ).
• Total number of carbohydrates in the product.

Your query should still consider products with no information about producer_id or department_id .

Solution explanation: The query selects the required columns. Then it joins the table product with the table producer on the producer ID using LEFT JOIN . We choose this type of join because we have to include products without producer data.

Then we add another LEFT JOIN . This one adds the department table and joins it with the product table. Again, we choose LEFT JOIN because we need to show products that don’t have a department.

There’s also a third join! We simply add it to the chain of the previous joins. It’s again LEFT JOIN , as we add the nutrition_data table and join it with the product table.

This is an interesting topic to explore, so here’s an article that explains multiple LEFT JOINs to help you with it.

The output shows all the products with their producer and department names and carbohydrate amounts:

If you need more details, please read how to LEFT JOIN multiple tables in SQL .

Exercise: For each product, show its name, price, producer name, and department name.

Alias the columns as product_name , product_price , producer_name , and department_name , respectively. Include all the products, even those without a producer or department. Also, include the producers and departments without a product.

Solution explanation: This exercise requires using FULL JOIN , as we need all the data from the tables we’ll use: product , producer , and department .

The syntax is the same as in the previous examples. We just join the different tables ( product and producer ) on the producer ID and use a different type of join:  FULL JOIN .

The second FULL JOIN joins the product table with the department table.

After selecting the required columns and renaming them, we get the following output.

The solution shows all the data from the selected tables and columns:

A self-join is not a distinct type of SQL JOIN – any join can be used for self-joining a table. It’s simply a join used to join the table with itself. By giving different aliases to the same table, it’s treated as two different tables when self-joined.

For more details, check out our illustrated guide to the SQL self-join .

The dataset for this example consists of only one table: workshop_workers . It has the following columns.

• id – The worker’s ID.
• name – The worker’s first and last name.
• specialization – The worker's specialization.
• master_id – The ID of the worker's supervisor.
• experience – The worker's years of experience.
• project_id – The ID of the project to which the worker is currently assigned.

Exercise: Show all workers' names together with the names of their direct supervisors. Rename the columns  apprentice_name and master_name , respectively. Consider only workers who have a supervisor (i.e. a master).

Solution explanation: Let’s start with explaining the self-join. The general principle is the same as with regular joins. We reference the table in FROM and give it an alias, apprentice . Then we use JOIN and reference the same table in it. This time, we give the table the alias master . We’re basically pretending that one table has the apprentice data and the other has the master data.

The tables are joined on the master ID from the apprentice table and the ID from the master table.

This example is a typical use of a self-join: the table has a column ( master_id ) that references another column from the same table ( id ). Both columns show the worker’s ID. When there’s NULL in master_id , it means that the worker doesn’t have a master. In other words, they are the master.

After self-joining, we simply select the required columns and rename them.

The output shows all the apprentices and their direct supervisors.

The final topic we’ll tackle in this SQL joins practice are non-equi joins. The joins we used so far are called equi-joins because they use the equality sign ( = ) in the joining condition. Non-equi are all other joins that use any other operators – comparison operators ( < , > , <= , >= , != , <> ), the BETWEEN operator, or any other logical condition – to join tables.

We’ll use the dataset consisting of two tables. The first table is car . Here are its columns:

• id – The car’s ID in the database.
• model – The car’s model.
• brand – The car’s brand.
• original_price – The original price of that car when new.
• mileage – The car’s total mileage.
• prod_year – The car’s production year.

The data looks like this:

The second table is charity_auction with these columns:

• car_id – The car’s ID.
• initial_price – The car’s initial (i.e. starting) price.
• final_price – The actual price when the car was sold.
• buyer_id – The ID of the person who bought the car.

Exercise: Show the model, brand, and final price of each car sold at the auction. Consider only those sold cars that have more mileage than the car with the id = 4 .

Solution explanation: We select the car model, brand, and final price.

In the first JOIN , we join the car table with the charity_auction table. The tables are joined where the car IDs are the same. This is our regular equi JOIN .

We add the second JOIN , which is a self-join. It adds the table car again, so we can filter the data using the non-equi join condition. The condition will return all the cars from the car table and all the cars from the car2 table with the lower mileage. This is a non-equi condition as it uses the ‘greater than’ ( > ) operator. The syntax is the same, but there’s > instead of = this time.

Finally, we need to filter data using WHERE . We’re not interested in comparing the mileage of all cars. We want to show the cars that have a mileage higher than the car with id = 4 . This is what the first filtering condition does.

We add another filtering condition that says the final price shouldn’t be NULL , i.e., the car has to have been sold in the auction.

The result shows two cars:

Twelve SQL join exercises is a solid amount of practice. Through these exercises, you could learn and practice all the most common join topics that trouble beginner and intermediate users.

Now, you just need to keep going! When you practice even more, you become even perfect-er. So if you liked our exercises, you can get more of the same in our SQL JOINS course or the article about the SQL JOIN interview questions .

Hope you ace all the exercises that await you there!

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

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

How to Declare a Variable in SQL Server?

In SQL Server, variables play a critical role in the dynamic execution of SQL scripts and procedures. Variables allow you to store and manipulate data temporarily within the scope of a batch or procedure. By using the DECLARE statement, you can create variables with specific data types, which can then be assigned values using the SET or SELECT commands.

In this article, We will learn about How to declare a variable in SQL Server by understanding various scenarios in detail.

How to Declare a Variable in SQL Server

To declare a variable in SQL Server , use the DECLARE statement. Variables must be prefixed with an @ symbol and must be assigned a data type.

Assigning a Value to a Variable

After declaring a variable, we can assign a value to it using the SET or SELECT statement.

Using SET Command to Declare Variable in SQL Server

Using select:, using variables in sql statements.

Variables can be used in various SQL statements like SELECT, INSERT, UPDATE, and DELETE.

Update Statement:

Number of rows affected by the update operation.

Select Statement:

Displays the ID and Name of the employee with the given @EmployeeID .

If @EmployeeID = 1 and Name = ‘John Doe’, the output would be:

Variable Scope and Lifetime

Scope: Local to batch, stored procedure, or function where declared. The variables are not accessible outside their declaring scope.

Lifetime : Till the end of the batch or procedure. The variable is not available any more after the execution is over.

No output outside the BEGIN…END block.

Using With Clause to Declare Variable in SQL Server

The WITH clause in SQL Server is primarily used for defining Common Table Expressions, which are temporary result sets that can be referenced in the SELECT , INSERT , UPDATE , or DELETE statement. That means the WITH clause will not be used to declare variables. Variables will be declared in SQL Server using the DECLARE statement.

Declaring and Using ariables in SQL Server

The example below shows how variables can be declared and used within SQL Server:

Variable Declaration:

Assign a Value to the Variable:

Use the variable in a query:.

Example: Use Variable in a SELECT Statement

CTE with the WITH Clause

The WITH clause is used for defining a temporary result set, known in this context as a common table expression (CTE), which you can then refer to within the execution of a single SQL statement .

Not for declaring variables, but quite handy in breaking down a complex query into manageable pieces.

Example of a CTE with the WITH Clause:

Mixing variables with ctes.

You can use both variables and CTEs in the same query, but they accomplish quite different things. Variables contain single scalar values whereas CTEs structure complex queries.

Example Mixing Variables and CTEs:

Using temporary variables to declare variable in sql server.

in SQL Server, when you refer to “temporary variables,” you probably mean variables in the scope of one batch, stored procedure, or function. They are only those declared using the DECLARE statement and are temporary in terms of living only during the life of the batch or session.

Declaring and Using Temporary Variables

Declaring a variable:.

• @MyVariable – Variable Name
• INT — Data Type

Value Assignment to the Variable:

This statement will assign a value 100 to the @MyVariable.

Using Variable:

It will return the value of the @MyVariable.

Example of Using a Temporary Variable in a Query

Suppose, you want the records based on a value to be stored in a variable:

This example declares a variable @CustomerID and initializes it to a value of 5. The second step makes use of this variable together with the SELECT statement for filtering the records from the Orders table where CustomerID is equal to 5. Temporary Variables with Arithmetic or String Operations You can use variables for arithmetic or string operations: sql Copy code DECLARE @Price DECIMAL(10, 2); DECLARE @Quantity INT; DECLARE @Total DECIMAL(10, 2);

Using Subqueries to Declare Variable in SQL Server

We cannot declare a variable and directly assign it the result from a subquery in the DECLARE statement in SQL Server. However, we can set a variable with a subquery after it has been declared. This is often done with a SET or SELECT statement.

Example: Subquery with SET

In this example:.

• A variable @ TotalSales is declared.
• The SET statement assigns the result of a subquery – that calculates the total sales for a particular customer – to the variable.
• The content stored in @ TotalSales is then selected and returned.

Example: Using a Subquery with SELECT

• A variable @HighestPrice is declared.
• The SELECT statement simply assigns the result of a subquery, which finds the highest price for a product, to the variable. It then selects and returns this value stored in @HighestPrice.

Common Data Types

SQL Server supports various data types for variables:

• INT: Integer values
• V ARCHAR(n): Variable-length character strings
• CHAR(n): Fixed-length character strings
• DATE: Dates
• FLOAT: Floating-point numbers

Variable declaration in SQL Server is an essential requirement to handle and process data efficiently in SQL scripts, stored procedures, and functions. Variables can be declared by using the DECLARE statement and then values set to them by SET or SELECT statements. The functioning or operation of temporary data, logic flow control, and execution of complicated queries can be performed in a better way. In order to manipulate variables, you must understand their scope of operation and lifetime. Variable declaration, in general, greatly enhances the capability of writing dynamic SQL code for efficiency, hence robust and manageable database applications.

How are variables declared in SQL Server?

In SQL Server, variables are declared using the DECLARE statement, followed by the variable name, prefixed with @ and the data type.

How to add a variable in SQL query?

After declaring a variable with DECLARE, assign it a value using SET or SELECT. Use the variable in SQL queries by including it in the query conditions or expressions.

How to DECLARE a variable in SQL Server?

Use the steps outlined above to declare and manage variables in SQL Server.

Please Login to comment...

Similar reads.

• Best Twitch Extensions for 2024: Top Tools for Viewers and Streamers
• Discord Emojis List 2024: Copy and Paste
• Best Adblockers for Twitch TV: Enjoy Ad-Free Streaming in 2024
• PS4 vs. PS5: Which PlayStation Should You Buy in 2024?
• 10 Best Free VPN Services in 2024

Improve your Coding Skills with Practice

What kind of Experience do you want to share?