sql assignment example

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

SQL Exercises

• SQL Concepts and Queries
• SQL Inner Join
• SQL - SELECT LAST
• SQL for Data Science
• Comparison Operators in SQL
• SQL Query Interview Questions
• 7 Best Books for SQL
• SAP Labs Interview Experience
• Oracle Interview Experience
• Shell India Interview Experience
• DE Shaw Interview Experience
• TCS NQT Interview Experience
• Sapient Interview Experience | Set 4
• Spring Works Interview Experience
• TCS Ninja Interview Experience
• Infosys InfyTQ Interview Experience
• SAP Labs Interview Experience | Set 7
• SQL Tutorial
• PostgreSQL Tutorial
• MongoDB Tutorial
• Getting started with Databases
• Rank and Dense Rank in SQL Server
• How to Insert If Not Exists in SQL SERVER?
• MySQL Tutorial

SQL ( Structured Query Language ) is a powerful tool used for managing and manipulating relational databases. Whether we are beginners or experienced professionals, practicing SQL exercises is essential for our skills and language mastery.

In this article, we’ll cover a series of SQL practice exercises covering a wide range of topics suitable for beginners , intermediate , and advanced learners. These exercises are designed to provide hands-on experience with common SQL tasks, from basic retrieval and filtering to more advanced concepts like joins window functions , and stored procedures.

Table of Content

SQL Questions for Practice

Sql practice exercises for beginners, sql practice exercises for intermediate, sql practice exercises for advanced, more questions for practice.

Practice SQL questions to enhance our skills in database querying and manipulation. Each question covers a different aspect of SQL , providing a comprehensive learning experience.

We have covered a wide range of topics in the sections beginner , intermediate and advanced .

• Basic Retrieval
• Arithmetic Operations and Comparisons:
• Aggregation Functions
• Group By and Having
• Window Functions
• Conditional Statements
• DateTime Operations
• Creating and Aliasing
• Constraints
• Stored Procedures:
• Transactions

let’s create the table schemas and insert some sample data into them.

Create Sales table

Create Products table

1. Retrieve all columns from the Sales table.

Explanation: This SQL query selects all columns from the Sales table, denoted by the asterisk (*) wildcard. It retrieves every row and all associated columns from the Sales table.

2. Retrieve the product_name and unit_price from the Products table.

Explanation:

This SQL query selects the product_name and unit_price columns from the Products table. It retrieves every row but only the specified columns, which are product_name and unit_price.

3. Retrieve the sale_id and sale_date from the Sales table.

This SQL query selects the sale_id and sale_date columns from the Sales table. It retrieves every row but only the specified columns, which are sale_id and sale_date.

4. Filter the Sales table to show only sales with a total_price greater than $100.

This SQL query selects all columns from the Sales table but only returns rows where the total_price column is greater than 100. It filters out sales with a total_price less than or equal to $100.

5. Filter the Products table to show only products in the ‘Electronics’ category.

This SQL query selects all columns from the Products table but only returns rows where the category column equals ‘Electronics’. It filters out products that do not belong to the ‘Electronics’ category.

6. Retrieve the sale_id and total_price from the Sales table for sales made on January 3, 2024.

This SQL query selects the sale_id and total_price columns from the Sales table but only returns rows where the sale_date is equal to ‘2024-01-03’. It filters out sales made on any other date.

7. Retrieve the product_id and product_name from the Products table for products with a unit_price greater than $100.

This SQL query selects the product_id and product_name columns from the Products table but only returns rows where the unit_price is greater than $100. It filters out products with a unit_price less than or equal to $100.

8. Calculate the total revenue generated from all sales in the Sales table.

This SQL query calculates the total revenue generated from all sales by summing up the total_price column in the Sales table using the SUM() function.

9. Calculate the average unit_price of products in the Products table.

This SQL query calculates the average unit_price of products by averaging the values in the unit_price column in the Products table using the AVG() function.

10. Calculate the total quantity_sold from the Sales table.

This SQL query calculates the total quantity_sold by summing up the quantity_sold column in the Sales table using the SUM() function.

11. Retrieve the sale_id, product_id, and total_price from the Sales table for sales with a quantity_sold greater than 4.

This SQL query selects the sale_id, product_id, and total_price columns from the Sales table but only returns rows where the quantity_sold is greater than 4.

12. Retrieve the product_name and unit_price from the Products table, ordering the results by unit_price in descending order.

This SQL query selects the product_name and unit_price columns from the Products table and orders the results by unit_price in descending order using the ORDER BY clause with the DESC keyword.

13. Retrieve the total_price of all sales, rounding the values to two decimal places.

This SQL query calculates the total sales revenu by summing up the total_price column in the Sales table and rounds the result to two decimal places using the ROUND() function.

14. Calculate the average total_price of sales in the Sales table.

This SQL query calculates the average total_price of sales by averaging the values in the total_price column in the Sales table using the AVG() function.

15. Retrieve the sale_id and sale_date from the Sales table, formatting the sale_date as ‘YYYY-MM-DD’.

This SQL query selects the sale_id and sale_date columns from the Sales table and formats the sale_date using the DATE_FORMAT() function to display it in ‘YYYY-MM-DD’ format.

16. Calculate the total revenue generated from sales of products in the ‘Electronics’ category.

This SQL query calculates the total revenue generated from sales of products in the ‘Electronics’ category by joining the Sales table with the Products table on the product_id column and filtering sales for products in the ‘Electronics’ category.

17. Retrieve the product_name and unit_price from the Products table, filtering the unit_price to show only values between $20 and $600.

This SQL query selects the product_name and unit_price columns from the Products table but only returns rows where the unit_price falls within the range of $50 and $200 using the BETWEEN operator.

18. Retrieve the product_name and category from the Products table, ordering the results by category in ascending order.

This SQL query selects the product_name and category columns from the Products table and orders the results by category in ascending order using the ORDER BY clause with the ASC keyword.

19. Calculate the total quantity_sold of products in the ‘Electronics’ category.

This SQL query calculates the total quantity_sold of products in the ‘Electronics’ category by joining the Sales table with the Products table on the product_id column and filtering sales for products in the ‘Electronics’ category.

20. Retrieve the product_name and total_price from the Sales table, calculating the total_price as quantity_sold multiplied by unit_price.

This SQL query retrieves the product_name from the Sales table and calculates the total_price by multiplying quantity_sold by unit_price, joining the Sales table with the Products table on the product_id column.

1. Calculate the total revenue generated from sales for each product category.

This query joins the Sales and Products tables on the product_id column, groups the results by product category, and calculates the total revenue for each category by summing up the total_price.

2. Find the product category with the highest average unit price.

This query groups products by category, calculates the average unit price for each category, orders the results by the average unit price in descending order, and selects the top category with the highest average unit price using the LIMIT clause.

3. Identify products with total sales exceeding $500.

This query joins the Sales and Products tables on the product_id column, groups the results by product name, calculates the total sales revenue for each product, and selects products with total sales exceeding 30 using the HAVING clause.

4. Count the number of sales made in each month.

This query formats the sale_date column to extract the month and year, groups the results by month, and counts the number of sales made in each month.

5. Determine the average quantity sold for products with a unit price greater than $100.

This query joins the Sales and Products tables on the product_id column, filters products with a unit price greater than $100, and calculates the average quantity sold for those products.

6. Retrieve the product name and total sales revenue for each product.

This query joins the Sales and Products tables on the product_id column, groups the results by product name, and calculates the total sales revenue for each product.

7. List all sales along with the corresponding product names.

This query joins the Sales and Products tables on the product_id column and retrieves the sale_id and product_name for each sale.

8. Retrieve the product name and total sales revenue for each product.

This query will give you the top three product categories contributing to the highest percentage of total revenue generated from sales. However, if you only have one category (Electronics) as in the provided sample data, it will be the only result.

9. Rank products based on total sales revenue.

This query joins the Sales and Products tables on the product_id column, groups the results by product name, calculates the total sales revenue for each product, and ranks products based on total sales revenue using the RANK () window function.

10. Calculate the running total revenue for each product category.

This query joins the Sales and Products tables on the product_id column, partitions the results by product category, orders the results by sale date, and calculates the running total revenue for each product category using the SUM() window function.

11. Categorize sales as “High”, “Medium”, or “Low” based on total price (e.g., > $200 is High, $100-$200 is Medium, < $100 is Low).

This query categorizes sales based on total price using a CASE statement. Sales with a total price greater than $200 are categorized as “High”, sales with a total price between $100 and $200 are categorized as “Medium”, and sales with a total price less than $100 are categorized as “Low”.

12. Identify sales where the quantity sold is greater than the average quantity sold.

This query selects all sales where the quantity sold is greater than the average quantity sold across all sales in the Sales table.

13. Extract the month and year from the sale date and count the number of sales for each month.

14. calculate the number of days between the current date and the sale date for each sale..

This query calculates the number of days between the current date and the sale date for each sale using the DATEDIFF function.

15. Identify sales made during weekdays versus weekends.

This query categorizes sales based on the day of the week using the DAYOFWEEK function. Sales made on Sunday (1) or Saturday (7) are categorized as “Weekend”, while sales made on other days are categorized as “Weekday”.

1. Write a query to create a view named Total_Sales that displays the total sales amount for each product along with their names and categories.

This query creates a view named Total_Sales that displays the total sales amount for each product along with their names and categories.

2. Retrieve the product details (name, category, unit price) for products that have a quantity sold greater than the average quantity sold across all products.

This query retrieves the product details (name, category, unit price) for products that have a quantity sold greater than the average quantity sold across all products.

3. Explain the significance of indexing in SQL databases and provide an example scenario where indexing could significantly improve query performance in the given schema.

With an index on the sale_date column, the database can quickly locate the rows that match the specified date without scanning the entire table. The index allows for efficient lookup of rows based on the sale_date value, resulting in improved query performance.

4. Add a foreign key constraint to the Sales table that references the product_id column in the Products table.

This query adds a foreign key constraint to the Sales table that references the product_id column in the Products table, ensuring referential integrity between the two tables.

5. Create a view named Top_Products that lists the top 3 products based on the total quantity sold.

This query creates a view named Top_Products that lists the top 3 products based on the total quantity sold.

6. Implement a transaction that deducts the quantity sold from the Products table when a sale is made in the Sales table, ensuring that both operations are either committed or rolled back together.

The quantity in stock for product with product_id 101 should be updated to 5.The transaction should be committed successfully.

7. Create a query that lists the product names along with their corresponding sales count.

This query selects the product names from the Products table and counts the number of sales (using the COUNT() function) for each product by joining the Sales table on the product_id. The results are grouped by product name using the GROUP BY clause.

8. Write a query to find all sales where the total price is greater than the average total price of all sales.

The subquery (SELECT AVG(total_price) FROM Sales) calculates the average total price of all sales. The main query selects all columns from the Sales table where the total price is greater than the average total price obtained from the subquery.

9. Analyze the performance implications of indexing the sale_date column in the Sales table, considering the types of queries commonly executed against this column.

By comparing the execution plans and analysis results of these queries, we can evaluate the performance implications of indexing the sale_date column. We’ll be able to observe differences in factors such as the query execution time, the type of scan used (sequential scan vs. index scan), and any additional costs associated with using the index.

10. Add a check constraint to the quantity_sold column in the Sales table to ensure that the quantity sold is always greater than zero.

All rows in the Sales table meet the condition of the check constraint, as each quantity_sold value is greater than zero.

11. Create a view named Product_Sales_Info that displays product details along with the total number of sales made for each product.

This view provides a concise and organized way to view product details alongside their respective sales information, facilitating analysis and reporting tasks.

12. Develop a stored procedure named Update_Unit_Price that updates the unit price of a product in the Products table based on the provided product_id.

The above SQL code creates a stored procedure named Update_Unit_Price. This stored procedure takes two parameters: p_product_id (the product ID for which the unit price needs to be updated) and p_new_price (the new unit price to set).

13. Implement a transaction that inserts a new product into the Products table and then adds a corresponding sale record into the Sales table, ensuring that both operations are either fully completed or fully rolled back.

This will update the unit price of the product with product_id 101 to 550.00 in the Products table.

14. Write a query that calculates the total revenue generated from each category of products for the year 2024.

When you execute this query, you will get the total revenue generated from each category of products for the year 2024.

If you’re looking to sharpen your SQL skills and gain more confidence in querying database s, consider delving into these articles. They’re packed with query-based SQL questions designed to enhance your understanding and proficiency in SQL .

By practicing with these exercises, you’ll not only improve your SQL abilities but also boost your confidence in tackling various database-related tasks. The Questions are as follows:

• How to Insert a Value that Contains an Apostrophe in SQL?
• How to Select Row With Max Value in SQL?
• How to Efficiently Convert Rows to Columns in SQL?
• How To Use Nested Select Queries in SQL
• How to Select Row With Max Value on a Column in SQL?
• How to Specify Condition in Count() in SQL?
• How to Find the Maximum of Multiple Columns in SQL?
• How to Update Top 100 Records in SQL?
• How to Select the Last Records in a One-To-Many Relationship Using SQL Join
• How to Join First Row in SQL?
• How to Insert Row If Not Exists in SQL?
• How to Use GROUP BY to Concatenate Strings in SQL?
• How Inner Join works in LINQ to SQL
• How to Get the Identity of an Inserted Row in SQL
• How to Declare a Variable in SQL?

Mastering SQL requires consistent practice and hands-on experience. By working through these SQL practice exercises , you’ll strengthen your skills and gain confidence in querying relational databases.

Whether you’re just starting or looking to refine your expertise, these exercises provide valuable opportunities to hone your SQL abilities. Keep practicing , and you’ll be well-equipped to tackle real-world data challenges with SQL.

Please Login to comment...

Similar reads.

Improve your Coding Skills with Practice

What kind of Experience do you want to share?

SQL Tutorial

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

You can test your SQL skills with W3Schools' Exercises.

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

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

Count Your Score

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

Start SQL Exercises

Start SQL Exercises ❯

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

Kickstart your career

Get certified by completing the course

COLOR PICKER

Contact Sales

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

Report Error

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

Top Tutorials

Top references, top examples, get certified.

• SQL Server training
• Write for us!

Learn SQL: SQL Query examples

In the previous article we’ve practiced SQL , and today, we’ll continue with a few more SQL examples. The goal of this article is to start with a fairly simple query and move towards more complex queries. We’ll examine queries you could need at the job interview, but also some you would need in real-life situations. So, buckle up, we’re taking off!

As always, let’s first take a quick look at the data model we’ll use. This is the same model we’re using in this series, so you should be familiar by now. In case, you’re not, just take a quick look at the tables, and how are they related.

We’ll analyze 6 SQL examples, starting from a pretty simple one. Each example will add something new, and we’ll discuss the learning goal behind each query. I’ll use the same approach covered in the article Learn SQL: How to Write a Complex SELECT Query? Let’s start.

#1 SQL Example – SELECT

We want to examine what is in the call table in our model. Therefore, we need to select all attributes, and we’ll sort them first by employee_id and then by start_time.

This is a pretty simple query and you should understand it without any problem. The only thing I would like to point here is that we’ve ordered our result first by the id of the employee (call.employee_id ASC) and then by the call start time (call.start_time). In real-life situations, this is something you would do if you want to perform analytics during the time on the given criteria (all data for the same employee are ordered one after another).

#2 SQL Example – DATEDIFF Function

We need a query that shall return all call data, but also the duration of each call, in seconds. We’ll use the previous query as the starting point.

The result returned is almost the same as in the previous query (same columns & order) except for one column added. We’ve named this column call_duration. To get the call duration, we’ve used the SQL Server DATEDIFF function. It takes 3 arguments, the unit for the difference (we need seconds), first date-time value (start time, lower value), second date-time value (end time, higher value). The function returns the time difference in the given unit.

• Note: SQL Server has a number of (date & time) functions and we’ll cover the most important ones in upcoming articles.

#3 SQL Example – DATEDIFF + Aggregate Function

Now we want to return the total duration of all calls for each employee. So, we want to have 1 row for each employee and the sum of the duration of all calls he ever made. We’ll continue from where we stopped with the previous query.

There is nothing special to add regarding the result – we got exactly what we wanted. But let’s comment on how we achieved that. Few things I would like to emphasize here are:

• We’ve joined tables call and employee because we need data from both tables (employee details and call duration)
• We’ve used the aggregate function SUM(…) around the previously calculated call duration for each employee
• Since we’ve grouped everything on the employee level, we have exactly 1 row per employee
• Note: There are no special rules when you combine the result returned by any function and aggregate function. In our case, you can use combine the SUM function with DATEDIFF without any problem.

#4 SQL Example – Calculating Ratio

For each employee, we need to return all his calls with their duration. We also want to know the percentage of time an employee spent on this call, compared to the total call time of all his calls.

• Hint: We need to combine value calculated for one row with the aggregated value. To do that, we’ll use a subquery to calculate that aggregated value and then join into the related row.

You can notice that we’ve achieved in combining row values with aggregated value. This is very useful because you could put such calculations inside the SQL query and avoid additional work later. This query contains a few more interesting concepts that should be mentioned:

• The most important is that we’ve placed the entire query returning the aggregated value in the subquery (the part starting from the 2nd INNER JOIN (INNER JOIN () and ending with ) AS duration_sum ON employee.id = duration_sum.id . Between these brackets, we’ve placed the slightly modified query from part #2 SQL Example – DATEDIFF Function. This subquery returns the id of each employee and the SUM of all his calls durations. Just think of it as a single table with these two values
• We’ve joined the “table” from the previous bullet to tables call and employee because we need values from these two tables
• We’ve already analyzed the DATEDIFF(…) function used to calculate the duration of a single call in part #2 SQL Example – DATEDIFF Function
• This part CAST( CAST(DATEDIFF(“SECOND”, call.start_time, call.end_time) AS DECIMAL(7,2)) / CAST(duration_sum.call_duration_sum AS DECIMAL(7,2)) AS DECIMAL(4,4)) AS call_percentage is pretty important. First we’ve casted both dividend ( CAST(DATEDIFF(“SECOND”, call.start_time, call.end_time) AS DECIMAL(7,2)) ) and divisor ( CAST(duration_sum.call_duration_sum AS DECIMAL(7,2) ) as decimal numbers. While they are whole numbers, the expected result is a decimal number, and we have to “tell” that to SQL Server. In case, we haven’t CAST-ed them, SQL Server would perform division of whole numbers. We’ve also cast the result as a decimal number. This wasn’t needed because we’ve previously defined that when casting dividend and divisor, but I wanted to format the result to have 4 numeric values, and all 4 of them will be decimal places (this is a percentage in decimal format)

From this example, we should remember that we can use subqueries to return additional values we need. Returning the aggregated value using a subquery and combining that value with the original row is one good example where we could do exactly that.

#5 SQL Example – Average (AVG)

We need two queries. First shall return the average call duration per employee, while the second shall return average call duration for all calls.

There is no need to explain this in more detail. Calculating the average call duration per employee is the same as calculating the SUM of call durations per employee (#3 SQL Example – DATEDIFF + Aggregate Function). We’ve just replaced the aggregate function SUM with AVG.

The second query returns the AVG call duration of all calls. Notice that we haven’t used GROUP BY. We simply don’t need it, because all rows go into this group. This is one of the cases when aggregate function could be used without the GROUP BY clause.

#6 SQL Example – Compare AVG Values

We need to calculate the difference between the average call duration for each employee and the average call duration for all calls.

This query is really complex, so lets’ comment on the result first. We have exactly 1 row per employee with an average call duration per employee, and the difference between this average and average duration of all calls.

So, what we did to achieve this. Let’s mention the most important parts of this query:

• We’ve again used a subquery to return the aggregated value – average duration of all calls
• Besides that, we’ve added this – 1 AS join_id. It serves the purpose to join these two queries using the id. We’ll “generate” the same value in the main subquery too
• The “main” subquery returns data grouped on the employee level. Once more we’ve “generated” artificial key, we’ll use to join these two subqueries – 1 AS join_id
• We’ve joined subqueries using the artificial key (join_id) and calculated the difference between average values

I hope you’ve learned a lot in today’s article. The main thing I would like you to remember after this one is that you can perform many statistical computations directly in SQL, and then use the web form or Excel to present results using shiny tables and graphs. We’ll continue practicing in the next article, so stay tuned.

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: Create SQL Server reports using date and time functions
• Learn SQL: SQL Scripts
• Learn SQL: SQL Server Pivot Tables
• SQL AVG() function introduction and examples

SQL Joins: 12 Practice Questions with Detailed Answers

• sql practice
• sql-practice-guide

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.

List of Exercises

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

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, exercise 12: show cars with higher mileage than a specific car.

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.

Non-Equi Joins

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:

SQL JOINs Practice Makes Perfect. More Practice? Perfect-er!

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

The Best SQL Examples

SQL stands for Structured Query Language. It's used with all kinds of relational databases.

Basic SQL Syntax Example

This guide provides a basic, high level description of the syntax for SQL statements.

SQL is an international standard (ISO), but you will find many differences between implementations. This guide uses MySQL as an example. If you use one of the many other Relational Database Managers (DBMS) you’ll need to check the manual for that DBMS if needed.

What we will cover

• Use (sets what database the statement will use)

Select and From clauses

• Where Clause (and / or, IN, Between, LIKE)

Order By (ASC, DESC)

• Group by and Having

How to use this

This is used to select the database containing the tables for your SQL statements:

The Select part is normally used to determine which columns of the data you want to show in the results. There are also options you can use to show data that is not a table column.

This example shows two columns selected from the “student” table, and two calculated columns. The first of the calculated columns is a meaningless number, and the other is the system date.

Where Clause (and / or, IN, Between and LIKE)

The WHERE clause is used to limit the number of rows returned.

In this case all five of these will be used is a somewhat ridiculous Where clause.

Compare this result to the above SQL statement to follow this logic.

Rows will be presented that:

• Have Student IDs between 1 and 5 (inclusive)
• or studentID = 8
• or have “Maxmimo” in the name

The following example is similar, but it further specifies that if any of the students have certain SAT scores (1000, 1400), they will not be presented:

Order By gives us a way to sort the result set by one or more of the items in the SELECT section. Here is the same list as above, but sorted by the students Full Name. The default sort order is ascending (ASC), but to sort in the opposite order (descending) you use DESC, as in the example below:

Group By and Having

Group By gives us a way to combine rows and aggregate data. The Having clause is like the above Where clause, except that it acts on the grouped data.

This data is from the campaign contributions data we’ve been using in some of these guides.

This SQL statement answers the question: “which candidates recieved the largest number of contributions (not $ amount, but count (*)) in 2016, but only those who had more than 80 contributions?”

Ordering this data set in a descending (DESC) order places the candidates with the largest number of contributions at the top of the list.

As with all of these SQL things there is MUCH MORE to them than what’s in this introductory guide. I hope this at least gives you enough to get started. Please see the manual for your database manager and have fun trying different options yourself.

Common SQL Interview Questions

What is an inner join in sql.

This is the default type of join if no join is specified. It returns all rows in which there is at least one match in both tables.

What is a left join in SQL?

A left join returns all rows from the left table, and the matched rows from the right table. Rows in the left table will be returned even if there was no match in the right table. The rows from the left table with no match in the right table will have null for right table values.

What is a right join in SQL?

A right join returns all rows from the right table, and the matched rows from the left table. Opposite of a left join, this will return all rows from the right table even where there is no match in the left table. Rows in the right table that have no match in the left table will have null values for left table columns.

What is a full join in SQL?

A full join returns all rows for which there is a match in either of the tables. So if there are rows in the left table that do not have matches in the right table, those will be included. As well as if there are rows in the right table that do not have matches in the left table, those will be included.

What is the result of the following command?

Here it’ll be an error because we can’t perform a DML operation on a view.

Can we perform a rollback after using ALTER command?

No, because ALTER is a DDL command and Oracle server performs an automatic COMMIT when the DDL statements are executed.

Which is the only constraint that enforces rules at column level?

NOT NULL is the only constraint that works at the column level.

What are the pseudocolumns in SQL? Give some examples?

A pseudocolumn is a function which returns a system generated value. The reason it is known as so because a pseudocolumn is an Oracle assigned value used in the same context as an Oracle database column but not stored on disk.

Create a user my723acct with password kmd26pt. Use the user data and temporary data tablespaces provided by PO8 and provide to this user 10M of storage space in user data and 5M of storage space in temporary_data.

Create the role role tables and_views.

Grant to the role of the previous question the privileges to connect to the database and the privileges to create tables and views.

The privilege to connect to the database is CREATE SESSION The privilege to create table is CREATE TABLE The privilege to create view is CREATE VIEW

Grant the previous role in the question to the users anny and rita

The privilege to connect to the database is CREATE SESSION The privilege to create table is CREATE TABLE The privilege to create view is CREATE VIEW.

Write a command to change the password of the user rita from abcd to dfgh

The users rita and anny do not have SELECT privileges on the table INVENTORY that was created by SCOTT. Write a command to allow SCOTT to grant the users SELECT priviliges on these tables.

User rita has been transferred and no longer needs the privilege that was granted to her through the role role tables and_views. Write a command to remove her from her previous given priviliges except that she still could connect to the database.

The user rita who was transferred is now moving to another company. Since the objects that she created is of no longer use, write a commmand to remove this user and all her objects.

Here CASCADE option is necessary to remove all the objects of the user in the database.

The user rita who was transferred is now moving to another company. Since the objects that she created is of no longer use, write a command to remove this user and all her objects.

Write SQL query to find the nth highest salary from table.

Sql create view statement, what is a view.

A View is a database object that presents data existing in one or more tables. Views are used in a similar way to tables, but they don’t contain any data. They just “point” to the data that exists elsewhere (tables or views, for example).

Why do we like them?

• Views are a way to limit the data presented. For example, the human resources department data filtered to only present non-sensitive information. Sensitive information in this case could be social security numbers, sex of employee, payrate, home address, etc.
• Complex data across more than one table can be combined into a single “view.” This can make life easier for your business analysts and programmers.

Important Safety Tips

• Views are managed by the system. When data in the related tables are changed, added, or updated, the View is updated by the system. We want to use these only when needed to manage use of system resources.
• In MySQL, changes to the table design (that is, new or dropped columns) made AFTER a view is created are not updated in the view itself. The view would have to be updated or recreated.
• Views are one of the four standard database object types. The others are tables, stored procedures, and functions.
• Views can usually be treated as you would a table, but updates are limited or not available when the view contains more than one table.
• There are many other details about views that are beyond the scope of this introductory guide. Spend time with your database managers manual and have fun with this powerful SQL object.

Syntax of the Create View Statement (MySQL)

This guide will cover this part of of the statement…

Sample View creation from the student tables

• The name of the view has a “v” at the end. It’s recommended that the view name indicate that it’s a view in some way to make life easier for programmers and database administrators. Your IT shop should have its own rules on naming objects.
• The columns in the view are limited by the SELECT and the rows of data by the WHERE clause.
• the ”`” character around the view names is required because of the ”-” in the names. MySQL reports an error without them.

Sample of using a View to combine data from more than one table

A Student demographics table was added to the database to demonstrate this usage. This view will combine these tables.

• To “join” tables, the tables must have fields in common (usually primary keys) that uniquely identity each row. In this case it’s the student ID. (More on this in the SQL Joins guide.)
• Notice the “alias” given to each table (“s” for student and “sc” for student contact). This is a tool to shorten the table names and make it easier to identify which table is being used. It’s easier than typing long table names repeatedly. In this example, it was required because studentID is the same column name in both tables, and the system would present an “ambiguous column name error” without specifying which table to use.

Guide to the SQL Between Operator

The BETWEEN Operator is useful because of the SQL Query Optimizer. Although BETWEEN is functionally the same as: x <= element <= y, the SQL Query Optimizer will recognize this command faster, and has optimized code for running it.

This operator is used in a WHERE clause or in a GROUP BY HAVING clause.

Rows are selected that have a value greater than the minimum value and less than the maximum value.

It’s important to keep in mind that the values entered in the command are excluded from the result. We get just what is between them.

Here is the syntax for using the function in a WHERE Clause:

Here is an example using the student table and the WHERE clause:

Here is an example using the campaign funds table and the having clause. This will return rows where the sum of the donations for a candidate are between $3 Million and $18 Million based on the HAVING clause in the GROUP BY part of the statement. More on aggregation in that guide.

SQL Create Table Statement Example

A table is a group of data stored in a database.

To create a table in a database you use the CREATE TABLE statement. You give a name to the table and a list of columns with its datatypes.

Here’s an example creating a table named Person:

In the example above, each Person has a Name, a Date of Birth and a Gender. The Id column is the key that identifies one person in the table. You use the keyword PRIMARY KEY to configure one or more columns as a primary key.

A column can be not null or null indicating whether it is mandatory or not.

A guide to the SQL Insert Query

Insert queries are a way to insert data into a table. Let’s say we have created a table using

example_table

Now to add some data to this table , we’ll use   INSERT  in the following way:

INSERT INTO example_table (column1,column2) VALUES ("Andrew",23)

Even the following will work, but it’s always a good practice to specify which data is going into which column.

INSERT INTO table_name VALUES ("John", 28)

A guide to the SQL AND operator

AND is used in a WHERE clause or a GROUP BY HAVING clause to limit the rows returned from the executed statement. Use AND when it’s required to have more than one condition met.

We’ll use the student table to present examples.

Here’s the student table without a WHERE clause:

Now the WHERE clause is added to display only programming students:

Now the WHERE clause is updated with AND to show results for programming students that also have a SAT score greater than 800:

This is a more complex example from the campaign contributions table. This example has a GROUP BY clause with HAVING clause using an AND to restrict the returned records to candidates from 2016 with contributions between $3 Million and $18 Million in total.

How to use the SQL Order By Keyword

ORDER BY gives us a way to SORT the result set by one or more of the items in the SELECT section. Here is an SQL sorting the students by FullName in descending order. The default sort order is ascending (ASC) but to sort in the opposite order (descending) you use DESC.

Here is the UN-ORDERED, current, full student list to compare to the above.

As with all of these SQL things there is MUCH MORE to them than what’s in this introductory guide.

I hope this at least gives you enough to get started.

Please see the manual for your database manager and have fun trying different options yourself.

If this article was helpful, share it .

Learn to code for free. freeCodeCamp's open source curriculum has helped more than 40,000 people get jobs as developers. Get started

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

This browser is no longer supported.

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

= (Assignment Operator) (Transact-SQL)

• 11 contributors

The equal sign (=) is the only Transact-SQL assignment operator. In the following example, the @MyCounter variable is created, and then the assignment operator sets @MyCounter to a value returned by an expression.

The assignment operator can also be used to establish the relationship between a column heading and the expression that defines the values for the column. The following example displays the column headings FirstColumnHeading and SecondColumnHeading . The string xyz is displayed in the FirstColumnHeading column heading for all rows. Then, each product ID from the Product table is listed in the SecondColumnHeading column heading.

Operators (Transact-SQL) Compound Operators (Transact-SQL) Expressions (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

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

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

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

• Python Multiline String
• Python Multiline Comment
• Python Iterate String
• Python Dictionary
• Python Lists
• Python List Contains
• Page Object Model
• TestNG Annotations
• Python Function Quiz
• Python String Quiz
• Python OOP Test
• Java Spring Test
• Java Collection Quiz
• JavaScript Skill Test
• Selenium Skill Test
• Selenium Python Quiz
• Shell Scripting Test
• Latest Python Q&A
• CSharp Coding Q&A
• SQL Query Question
• Top Selenium Q&A
• Top QA Questions
• Latest Testing Q&A
• REST API Questions
• Linux Interview Q&A
• Shell Script Questions
• Python Quizzes
• Testing Quiz
• Shell Script Quiz
• WebDev Interview
• Python Basic
• Python Examples
• Python Advanced
• Python Selenium
• General Tech

Top 100 SQL Query Interview Questions for Practice

Hey Friends, we’ve brought you over 100 SQL queries and exercises for practice. The first 50, in this post, are the most frequently asked SQL query interview questions and the remaining 50 are the tricky SQL queries for interview. So, start with the readymade SQL script provided to create the test data. The script includes a sample Worker table, a Bonus, and a Title table with pre-filled data. Just run the SQL script, and you are all set to get started with the SQL queries. Practice with these SQL queries asked in interview questions and get ready for interviews with top IT MNCs like Amazon, Flipkart, Facebook, etc.

Get Started with 100 SQL Query Interview Questions

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

Let’s Prepare Sample Data for SQL Practice

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

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

SQL Script to Seed Sample Data.

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

Practice with SQL Queries Asked in Interview Questions

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

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

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

The required query is:

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

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

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

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

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

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

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

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

The general query to clone a table with data is:

The general way to clone a table without information is:

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

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

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

The following MySQL query returns the current date:

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

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

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

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

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

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

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

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

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

MySQL query to find the nth highest salary:

SQL Server query to find the nth highest salary:

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

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

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

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

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

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

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

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

The following query returns the expected result:

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

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

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

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

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

Summary – Master SQL with Practice Questions.

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

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

-TechBeamers.

You Might Also Like

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

Leave a Reply

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

Popular Tutorials

7 Demo Websites to Practice Selenium Automation Testing

SQL Exercises – Complex Queries

15 Java Coding Questions for Testers

30 Python Programming Questions On List, Tuple, and Dictionary

Assignment Operator in SQL Server

Back to: SQL Server Tutorial For Beginners and Professionals

Assignment Operator in SQL Server with Examples

In this article, I am going to discuss Assignment Operator in SQL Server with Examples. Please read our previous article, where we discussed Clauses in SQL Server . Before understanding Assignment Operator in SQL Server, let us first understand what are operators and why we need operators, and what are the different types of operators available in SQL Server.

What is an Operator in SQL Server?

A n operator is a symbol that performs some specific operation on operands or expressions. These operators are classified as follows in SQL Server.

• Assignment operator
• Arithmetic operator
• Comparison operator
• Logical operator
• Set operator

Note: In this article, I am going to discuss Assignment Operator,  rest of all other operators will discuss one by one from our upcoming articles.

Understanding the Assignment Operator in SQL Server:

Let us understand how to use the Assignment Operator in SQL Server with an example. We are going to use the following Employee table to understand the Assignment Operator.

Please use the below script to create and populate the Employee table with the required data.

Assignment operator:.

The assignment operator (=) in SQL Server is used to assign the values to a variable. The equal sign (=) is the only Transact-SQL assignment operator. In the following example, we create the @MyCounter variable, and then the assignment operator sets the @MyCounter variable to a value i.e. 1 .

DECLARE @MyCounter INT; SET @MyCounter = 1;

The assignment operator can also be used to establish the relationship between a column heading and the expression that defines the values for that column. The following example displays the column headings as FirstColumn and SecondColumn. The string ‘ abcd ‘ is displayed for all the rows in the FirstColumn column heading. Then, each Employee ID from the Employee table is listed in the SecondColumn column heading.

SELECT FirstColumn = ‘abcd’,  SecondColumn = ID  FROM Employee;

Compound Assignment Operators in SQL Server:

SQL SERVER 2008 has introduced a new concept of Compound Assignment Operators. The Compound Assignment Operators are available in many other programming languages for quite some time. Compound Assignment Operators are operated where variables are operated upon and assigned in the same line. Compound-assignment operators provide a shorter syntax for assigning the result of an arithmetic or bitwise operator. They perform the operation on the two operands before assigning the result to the first operand.

Example without using Compound Assignment Operators

The following example is without using Compound Assignment Operators.

Example using Compound Assignment Operators

The above example can be rewritten using Compound Assignment Operators as follows.

Following are the list of available compound operators in SQL Server

+= Adds some amount to the original value and sets the original value to the result. -= Subtracts some amount from the original value and sets the original value to the result. *= Multiplies by an amount and sets the original value to the result. /= Divides by an amount and sets the original value to the result. %= Divides by an amount and sets the original value to the modulo.

In the next article, I am going to discuss Arithmetic Operators in SQL Server. Here, in this article, I try to explain the Assignment Operator in SQL Server with Examples. I hope this article will help you with your needs. I would like to have your feedback. Please post your feedback, question, or comments about this article.

About the Author: Pranaya Rout

Pranaya Rout has published more than 3,000 articles in his 11-year career. Pranaya Rout has very good experience with Microsoft Technologies, Including C#, VB, ASP.NET MVC, ASP.NET Web API, EF, EF Core, ADO.NET, LINQ, SQL Server, MYSQL, Oracle, ASP.NET Core, Cloud Computing, Microservices, Design Patterns and still learning new technologies.

1 thought on “Assignment Operator in SQL Server”

Operators in SQL Server covers almost all the important areas of SQL. This tutorial is very good.

Leave a Reply Cancel reply

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

How We Work

• Aug 25, 2021

SQL Sample assignment

Instructions.

This assignment will test your understanding of conditional logic, views, ranking and windowing functions, and transactions, as shown in the videos. The prompt includes 10 questions.

You will need to create your own test database and tables using the criteria below but it's not necessary to submit the scripts for creating the database objects. Please submit your answers using only one file. The preferable format is a text file with a .sql extension. You can easily edit the file using a text editor such as Notepad ++ or Sublime (for Macs as well as Windows), which are available online for free.

Prompt : A manufacturing company’s data warehouse contains the following tables.

Note: (p) = "primary key" and (f) = "foreign key". They are not part of the column names.

Sales_Totals

Answer the following questions using the above tables/data:

Write a SELECT statement to return the month column, as well as an additional column for the quarter (1, 2, 3, or 4) that is based on the month. Please use a CASE expression for this and do not alter the table.

Write a query that will pivot the Sales_Totals data so that there is a column for each of the 4 products containing the total sales across all months of 2020. It is OK to include the product_id values in your query, and the results should look as follows:

Write a query that retrieves all columns from the Sales_Totals table, along with a column called sales_rank which assigns a ranking to each row based on the value of the Sales column in descending order. Please use SQL RANK functions shown in the class video.

Write a query that retrieves all columns from the Sales_Totals table, along with a column called product_sales_rank which assigns a ranking to each row based on the value of the Sales column in descending order, with a separate set of rankings for each product. Please use SQL RANK functions shown in the class video.

Expand on the query from question #4 by adding logic to return only those rows with a product_sales_rank of 1 or 2.

Write a set of SQL statements which will add a row to the Region table for Europe, and then add a row to the Sales_Total table for the Europe region and the Sprocket product (product_id = 7684) for October 2020, with a sales total of $1,500. You can assign any value to the region_id column, as long as it is unique to the Region table. The statements should be executed as a single unit of work. Please note that since the statements are executed as a single unit of work, additional code is needed.

Write a statement to create a view called Product_Sales_Totals which will group sales data by product and year. Columns should include product_id, year, product_sales, and gear_sales, which will contain the total sales for the “Gear - Large” and “Gear Small” products (should be generated by an expression, and it is OK to use the product_id values in the expression). To accomplish this, you need a CASE statement. The product_sales column should be a sum of sales for the particular product_id and year, regardless of what kind of product it is. The gear_sales column should be a sum of sales only in the case where the product is either "Gear - Large” or “Gear Small”. Else in the case that the product is neither “Gear - Large” or “Gear Small”, the value for gear_sales should be 0.

Write a query to return all sales data for 2020, along with a column called “pct_product_sales” showing the percentage of sales for each product by region_id and month. Columns should include product_id, region_id, month, sales, and pct_product_sales. The values in pct_product_sales should add up to 100% for each product.

Write a query to return the year, month, and sales columns, along with a 4th column named prior_month_sales showing the sales from the prior month. There are only 12 rows in the sales_totals table, one for each month of 2020, so you will not need to group data or filter/partition on region_id or product_id. Please use a windowing function for this as shown in the class video.

If the tables used in this prompt are in the ‘sales’ database, write a query to retrieve the name and type of each of the columns in the Product table. Please specify the 'sales' schema in your answer.

Recent Posts

IMDB Subset Database: SQL Queries and Analysis

Data Management Solutions for Healthcare: Integrating SQL

FitTrack: Fitness Database Design and Implementation

Commentaires