dbms theory assignment questions

18 SQL Questions for Beginners: Theory and Practice

• sql practice

Table of Contents

SQL Practice for Beginners

Question 1: elements of an sql query, question 2: filtering data in an sql query, data for questions 3 – 6, question 3: select cats of a given age and breed, question 4: list cats whose favorite toy is a ball, question 5: find the most bored cat, question 6: select cats that love teaser toys, question 7: the role of join, question 8: types of joins, data for questions 9 – 12, question 9: find artists born after 1800 and the art they created, question 10: select all pieces of art and their location, question 12: list pieces of art created by unknown artists, question 13: aggregate functions and the role of group by, question 14: where vs. having, data for questions 15 – 18, question 15: calculate the average production cost of good games, question 16: provide game production statistics by year, question 17: calculate the gross profit per company, question 18: identify good games, get more basic sql practice.

Whether you're starting or refreshing your SQL skills, join us as we work through these 18 SQL practice questions for beginners.

SQL, or Structured Query Language, is a programming language used to define, retrieve, and manipulate data in relational databases. It provides an intuitive syntax of SQL statements and keywords that create, modify, and query relational databases.

This article focuses on reviewing and practicing the basics of SQL. We’ll start by reviewing the SELECT statement and its required and optional components for fetching data from a single table. Following that, we’ll delve into JOINs , which allow us to merge data from two or more tables. Finally, we’ll demonstrate how to aggregate and group data to perform more advanced analysis. This can help you review your SQL knowledge before an interview or a test – or simply refresh and consolidate your skills.

This article showcases SQL practice exercises from our interactive  SQL Practice Set course. The course offers over 80 hands-on practice exercises that cover different SQL topics: single table queries, joins, aggregation and grouping, subqueries, and more.  If you want to practice more on your own, we encourage you to check out our SQL Practice track.

All our SQL practice courses provide exercises based on real-world datasets, so you can practice SQL in realistic scenarios. The courses are grouped into different topics – e.g. single table queries, joins, aggregation and grouping, and subqueries – so you can choose what you want to practice.

Let’s get started.

The SQL practice exercises in this article cover the basics of querying data. We’ll review:

• Single table queries – Querying data from a single table using the SELECT statement.
• JOINs – Joining data from multiple tables using various JOINs.
• Aggregating and grouping data – Putting data into groups based on defined columns and compiling statistics.

Single Table Queries

We’ll start by reviewing the basics of querying data from a single table and imposing custom conditions on data columns.

List all elements in an SQL query.

The SELECT statement consists of the following components:

• SELECT column_name(s) – Defines the data columns shown in the output.
• FROM table_name – Defines the database table from which data is selected.
• WHERE column_name = value – Filters the output data based on stated conditions (optional).
• GROUP BY column_name(s) – Groups data based on distinct values (optional).If you’re using aggregate functions , you must use the GROUP BY clause.
• HAVING – Filters data after it has been processed by GROUP BY (optional); you can use this to impose conditions on aggregate functions.
• ORDER BY column_name [ASC | DESC] – Orders output data by a defined column in ascending or descending order (optional).

Both the SELECT and FROM clauses are easy to grasp, as SELECT lists data columns and FROM defines the data table. In the case of the WHERE clause, there are a variety of conditions you can impose on columns, which we’ll review in the next question.

You can read more about the basic query elements in our article Enumerate and Explain All the Basic Elements of an SQL Query .

These are the elements of an SQL query in order of appearance: SELECT , FROM , WHERE , GROUP BY , ORDER BY , and HAVING .

How do you filter data in an SQL query using custom conditions?

To impose custom conditions on data columns, we use the WHERE clause. For example, if you want to select people older than 18, use the WHERE clause as follows:

The WHERE clause conditions typically involve comparisons or logical operations and depend on the data type stored in the column.

• Numeric data types: =, <> or !=, >, <, >=, <=
• Text/string data types: =, <> or !=, LIKE, IN, NOT LIKE, NOT IN
• Date and time data types: =, <> or !=, >, <, >=, <=, BETWEEN, NOT BETWEEN
• Boolean data types: =, <> or !=
• Operators used to check for NULL values: IS NULL, IS NOT NULL
• Logical operators used to combine multiple conditions: AND, OR, NOT

You can read more about filtering data in our articles How to Write a WHERE Clause in SQL and Using AND, OR, and NOT Operators in SQL .

The WHERE clause is used to filter data by imposing conditions on data columns.

In the exercises 3 – 6, we’ll use the cat table. It has the following columns:

• id – The id of a given cat.
• name – The cat’s name.
• breed – The cat’s breed (e.g. Siamese, British shorthair, etc.).
• coloration – The cat’s coloration (e.g. calico, tabby, etc.).
• age – The cat’s age.
• sex – The cat’s sex.
• fav_toy – The cat’s favorite toy.

Select the ID and name for every Ragdoll cat that is either 1) younger than five years old, or 2) older than ten years old.

Explanation:

As the instruction says, we select the id and name columns from the cat table.

Next, we use the WHERE clause to impose conditions:

• On the age column:

We want to select cats that are younger than 5 ( age < 5 ) or older than 10 ( age > 10 ), so we use the OR keyword and enclose both conditions in parentheses.

Why do we need parenthesis? Well, we want to impose this composite condition on the age column. What if we do not include parenthesis? The parenthesis will be implicitly imposed on the last two conditions, like this: age < 5 OR (age > 10 AND breed = 'Ragdoll') . This will cause an incorrect result.

• On the breed column:

We want to select cats of the Ragdoll breed; therefore, we simply define the condition as breed = 'Ragdoll' . Note that text values in SQL are enclosed in single quotes (').

This exercise demonstrates a composite condition that uses logical operators ( AND , OR ) and mathematical comparison operators ( < , > , = ).

Select all data for cats whose breed starts with 'R', whose favorite toy starts with 'ball', and whose coloration ends with an 'm'.

Here, we select all data columns ( * ) from the cat table.

We want to impose conditions on the literal values of the breed , colorations , and fav_toy columns. To do that, we’ll use pattern matching; in SQL, % is a wildcard character that stands for any sequence of characters.

The breed column value should start with an 'R'. Therefore, we use a pattern that indicates a value starting with 'R' and followed by any number of characters (defined by % ). If we want to impose such a condition on a literal value, we must use the LIKE keyword: breed LIKE 'R%' .

Similarly, we want the favorite toy name to start with 'ball'; therefore, the condition is fav_toy LIKE 'ball%' .

And it’s the same again for the coloration column. We want the literal value to end with an 'm', so the % character goes in front: coloration LIKE '%m' .

You can read more about using the LIKE operator in our articles What Do the Operators LIKE and NOT LIKE Do? and How to Use LIKE in SQL .

Select the names of all male cats that don't have a favorite toy – that is, the value of the field fav_toy is NULL .

As the instruction says, we select the name column from the cat table.

We want to select only male cats; therefore, we define a condition on the sex column as sex = 'M' . You need to be familiar with the data stored in the cat table to define this condition – i.e. to know that the sex column stores the value ‘F’ for female cats and ‘M’ for male cats.

As we’re looking for the most bored cat, we need to define a condition that says the fav_toy column must have no value, or be NULL . We do this with fav_toy IS NULL .

Working with NULLs is quite complicated in SQL. For more details, we recommend the articles:

• What Is a NULL in SQL?
• How to Use Comparison Operators with NULLs in SQL
• How to Find Records with NULL in a Column
• How to Filter Rows Without NULL in a Column

Select the ID, name, breed, and coloration of all cats that:

• Are females.
• Like teaser toys,
• Are not of the Persian or Siamese breeds.

In this exercise, we select the id , name , breed , and coloration columns from the cat table. Then we impose the following conditions:

• On the sex column: We want to select female cats; hence, the condition is sex = 'F' .
• On the fav_toy column: We want to find cats that like teaser toys, so the condition is fav_toy = 'teaser' .
• On the breed column: We want to select any breed except for Persian and Siamese. To do that, we use the NOT LIKE keyword and enclose the entire composite condition in parenthesis ( breed NOT LIKE 'Persian' AND breed NOT LIKE 'Siamese' ).

Great work! You’ve completed the section on selecting data from a single table with various filter conditions. Let’s move on to working with multiple tables.

Data from Multiple Tables: SQL JOINs

Now you know how to select data from a single table. But what if we want to select data from two or more tables? We need to join these tables based on common column values. This is where JOIN operations come into play.

What does JOIN do in SQL?

The JOIN clause is used to combine data from two or more tables.

You can use as many JOINs as you need. Below, we use two JOINs to combine data from three tables:

When joining tables, it’s best to use alias names for each table (here, t1 , t2 , and  t3 ). These alias names are used to refer to columns from each table.

To find out more about SQL JOINs, see our articles SQL INNER JOIN Explained in Simple Words and How to Join Two Tables in SQL .

JOINs are used to combine data from multiple tables.

List all types of JOINs available in SQL and briefly describe each one.

There are four types of JOINs: [INNER] JOIN , RIGHT JOIN , LEFT JOIN , and FULL [OUTER] JOIN . Each one provides different results.

A JOIN , also known as an INNER JOIN , is the most common type of join. It returns only the matching records from two or more tables.

A LEFT JOIN returns all the records from the left (first) table and the matching records from the right (second) table. If there are no matches in the right table, null values are included in the result set.

Read What Is a LEFT JOIN in SQL? for more details.

A RIGHT JOIN returns all the records from the right (second) table and the matching records from the left (first) table. If there are no matches in the left table, null values are included in the result set.

A FULL JOIN , also known as a FULL OUTER JOIN , returns all the records from both the left and right tables. It includes matching records from both tables and uses null values for non-matching records.

Read this article to learn more about FULL JOINs .

In summary, LEFT JOIN and RIGHT JOIN focus on one table as the primary source of data, while a FULL JOIN combines all the records from both tables. The choice of which JOIN to use depends on the specific data retrieval needs and the relationship between the tables involved.

To read more about different JOIN types, we recommend our articles SQL JOINs and SQL JOIN Types Explained . Our SQL JOIN Cheat Sheet summarizes the syntax of different types of  JOINs.

JOIN types include [INNER] JOIN , LEFT JOIN , RIGHT JOIN , and FULL [OUTER] JOIN .

In exercises 9 – 12, we’ll use the Museum dataset that consists of three tables.

The artists table contains the following columns:

• id – The database ID for a given artist.
• name – The artist’s name.
• birth_year – The year the artist was born.
• death_year – The year the artist
• artistic_field – That artist’s primary field (e.g. watercolor painting, sculpture, oil painting).

The museum table contains the following columns:

• id – The ID of a given museum.
• name – The museum’s name.
• country – The country where the museum is located.

The piece_of_art table contains the following columns:

• id – The ID of a given piece of art.
• name – The piece’s name.
• artist_id – The ID of the artist who created this piece.
• museum_id – The ID of the museum that has this piece in its collection.

For each artist who was born after the year 1800 and lived for more than 50 years, show their name and the name of the pieces of art they created. Rename the columns as artist_name and piece_name , respectively.

We select artist names (aliased as artist_name ) along with pieces of art they created (aliased as piece_name ). Therefore, we must join the artist table (aliased as a ) with the piece_of_art table (aliased as poa) on their common column that stores artist IDs ( ON a.id = poa.artist_id ).

We want to consider only artists who lived for more than 50 years. To define this condition, we’ll use the birth_year and death_year columns from the artist table as follows:

Also, we want to list artists born after 1800: birth_year > 1800 .

Check out this article about joining two tables in SQL to learn more.

Select the names of all pieces of art together with the names of the museums that house them and the countries in which these museums are located. Also show lost pieces of art (those without an associated museum).

As we want to select the names of art pieces and the names and countries of museums, we must join the piece_of_art table (aliased as poa ) with the museum table (aliased as m ) on the museum ID column ( ON poa.museum_id = m.id ).

We need to show all pieces of art, including the ones that are lost. Note that the lost pieces of art do not have any museum assigned. Therefore, we require a specific type of JOIN that selects all data from the piece_of_art table, regardless of whether it has any matching records in the museum table:

This LEFT JOIN ensures that we select all rows from the left table (here, piece_of_art ).

Check out this article on LEFT JOIN to learn more.

Question 11: List All Pieces of Art

Show the names of all pieces of art together with the names of their creators and the names of the museums that house these pieces of art. Omit lost works and pieces of art with an unknown artist. Name the columns piece_of_art_name, artist_name , and museum_name .

Here we select names of artists from the artist table, names of museums from the museum table, and names of art pieces from the piece_of_art table. Hence, we must join all three tables on their common columns:

• We join the museum table with the piece_of_art table on museum ID values.
• We join the artist table with the piece_of_art table on artist ID values.

Once we’ve joined all three tables, we can select the output values.

Note that we want to omit art pieces that do not have any museum or any artist assigned. Therefore, we use the standard JOIN (or INNER JOIN ) that joins data from tables only when there is a match in the column on which the JOIN is performed.

Follow this article on how to join 3 or more tables to learn more.

Check whether any pieces were created by unknown artists. Show the names of these pieces together with the names of the museums that house them.

We want to show the names of ‘unknown artist’ pieces along with the names of museums where the pieces are located. Hence, we join the piece_of_art table (aliased as poa ) with the museum table (aliased as m ) on the museum ID column ( ON poa.museum_id = m.id ).

As we’re looking for art pieces created by unknown artists, we include the following condition in the WHERE clause: poa.artist_id IS NULL .

Grouping and Aggregating Data

Aggregation and grouping are techniques used to organize data into groups based on defined criteria and perform calculations on the groups.

List the available aggregate functions and explain the role of the GROUP BY clause.

Aggregation involves applying mathematical operations to a set of values in a column. The more commonly used aggregate functions include SUM() , AVG() , COUNT() , MAX() , and MIN() .

For example, imagine a table that stores monthly sales values:

You can use the SUM() aggregate function to get the total sales, like this:

The output is as follows:

When we’re aggregating data, we also often segment data into groups based on distinct values in the column that is used to group data.

Grouping involves creating groups of data based on values in column(s) given as arguments to the GROUP BY clause.

For example, imagine you want to select sales per year. To do this, you have to group data by the year, like this:

If the column on which we group data has five distinct values, data will be grouped into five groups.

We recommend this article if you want to learn more about the GROUP BY clause.

Aggregation is about performing calculations on a set of values and grouping is about organizing data into groups based on specific criteria.

What is the difference between WHERE and HAVING ?

Both WHERE and HAVING are used to filter data by imposing certain conditions.

The difference is that WHERE is used to impose conditions on data columns (as you’ve seen in the Single Table Queries section) and HAVING is used to impose conditions on aggregate functions (as you’ll see in this section).

Read this article on WHERE vs. HAVING to learn more about the differences between these two clauses.

WHERE imposes conditions on columns. HAVING imposes conditions on aggregate functions.

In exercises 15 – 18, we’ll use the games table. It consists of the following columns:

• id – The ID of a given game.
• title – The game’s name (e.g. ‘Super Mario Bros’).
• company – The name of the company that makes this game (e.g. ‘Nintendo’).
• type – The type of game (e.g. ‘arcade’).
• production_year – The year when the game was created.
• system – The system for which a game was released (e.g. ‘NES’).
• production_cost – The cost of producing the game.
• revenue – The revenue generated by the game.
• rating – The rating given to this game.

Show the average production cost of games that were produced between 2010 and 2015 and were rated higher than 7.

To select the average production cost of games, we use the AVG() aggregate function on the production_cost column. This function takes all values present in the production_cost column and calculates the average.

As we are interested in games produced between 2010 and 2015, we must include this condition in the WHERE clause: production_year BETWEEN 2010 AND 2015 . That sounds just like plain English!

Also, we want to include only games with a rating higher than 7, so we add another condition in the WHERE clause: AND rating > 7 .

Check out this article on the AVG() function to see more examples.

For each year:

• Display the year ( production_year ).
• Count the number of games released in this year (name this count ).
• Show the average cost of production (as avg_cost ) for these games.
• Show the average revenue (as avg_revenue ) of these games.

We want to display different statistics per year; therefore, we need to GROUP BY production_year .

As we select from the games table, we use the COUNT() aggregate function to count games released per year. We use * as an argument because we want to count all rows (not values of a specific column). We alias it AS count .

Next, we want to display the average cost of production: AVG(production_cost). We alias it AS avg_cost.

Finally, we show the average revenue: AVG(revenue). We alias it AS avg_revenue.

For all companies present in the games table, show their name and their gross profit over all years. To simplify this problem, assume that the gross profit is equal to revenue minus the production cost of all games; name this column gross_profit_sum . Order the results so the company with the highest gross profit is first.

We select the company column from the games table. For each company, we sum the gross profit values ( revenue - production_cost ) produced by each game created by this company.

As we want to see the sum of gross profit per company, we must GROUP BY company. However, in this case, we use a different syntax: GROUP BY 1 , which means that we want to GROUP BY the 1 st column listed in SELECT .

Finally, we order the output in descending order based on the gross profit values per company.

We're interested in good games produced between 2000 and 2009. A good game has a rating higher than 6 and was profitable. For each company, show:

• The company name.
• The total revenue from good games produced between 2000 and 2009 (as the revenue_sum column).
• The number of good games the company produced in this period (as the number_of_games column).

Important: Only show companies with good-game revenue over 4,000,000.

This one is a bit trickier, as we need to create a query that uses WHERE , HAVING , aggregate functions, and grouping.

Let’s analyze the instructions step by step and translate it into SQL code.

WHERE -related instructions:

• games produced between 2000 and 2009 results in this condition being added to the WHERE clause: WHERE production_year BETWEEN 2000 AND 2009
• games rated higher than 6 results in this condition being added to the WHERE clause: AND rating > 6
• games that were profitable results in this condition being added to the WHERE clause: AND revenue - production_cost > 0 Remember, a profitable game means that the revenue is higher than the cost of production.

SELECT -related instructions:

• show company name results in this column being added to the SELECT statement: SELECT company
• show its total revenue (as revenue_sum ) results in this column being added to the SELECT statement: SUM(revenue) AS revenue_sum
• show the number of good games ( number_of_games ) results in this column being added to the SELECT statement: COUNT(company) AS number_of_games

GROUP BY- and HAVING -related instructions:

• for each company means that we calculate the statistics ( COUNT() and SUM() ) on a company So, we must group the data by company: GROUP BY company
• show companies with good-game revenue over 4,000,000 result in this condition being added to the HAVING clause: HAVING SUM(revenue) > 4000000

That’s how we dissected the instructions and translated them into SQL code.

This article covered the basics of SQL queries, including how to filter data, join multiple tables, order and sort output, and aggregate and group data.

Have you enjoyed the SQL practice exercises so far? All these exercises come from our SQL Practice Set course. For more SQL exercises, check out these LearnSQL.com practice courses:

• Basic SQL Practice: A Store
• Basic SQL Practice: University
• Basic SQL Practice: Blog & Traffic Data
• Basic SQL Practice: Run Track Through Queries!

You can buy each of these courses individually, or you can purchase our All Forever SQL package . It covers all 70+ SQL courses offered on our platform, including these practice courses, and all new courses we’ll add in the future.

And remember, practice makes perfect. Good luck on your SQL journey!

You may also like

How Do You Write a SELECT Statement in SQL?

What Is a Foreign Key in SQL?

Enumerate and Explain All the Basic Elements of an SQL Query

AfterAcademy

Theoretical questions on dbms, dbms - database management system. here, we have listed all the commonly asked questions during the tech interview on dbms, connect with your mentors.

Janishar Ali

Amit Shekhar

Introduction to DBMS Concepts Test

Have doubt related to any question?

Go to our Forum: Ask Question

Having second thoughts for any code related question?

Try executing the code in our Code Playground

Related Tests:

Test result.

• 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 ( 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.

List of SQL Exercises

• SQL Questions for Practice

SQL Practice Exercises for Beginners

Sql practice exercises for intermediate, sql practice exercises for advanced, more questions for practice, sql exercises 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

This hands-on approach provides a practical environment for beginners to experiment with various SQL commands, gaining confidence through real-world scenarios. By working through these exercises, newcomers can solidify their understanding of fundamental concepts like data retrieval, filtering, and manipulation, laying a strong foundation for their SQL journey.

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.

These exercises are designed to challenge you beyond basic queries, delving into more complex data manipulation and analysis. By tackling these problems, you’ll solidify your understanding of advanced SQL concepts like joins, subqueries, functions, and window functions, ultimately boosting your ability to work with real-world data scenarios effectively.

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”.

This section likely dives deeper into complex queries, delving into advanced features like window functions, self-joins, and intricate data manipulation techniques. By tackling these challenging exercises, users can refine their SQL skills and tackle real-world data analysis scenarios with greater confidence and efficiency.

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?

PrepBytes Blog

ONE-STOP RESOURCE FOR EVERYTHING RELATED TO CODING

Sign in to your account

Forgot your password?

Login via OTP

We will send you an one time password on your mobile number

An OTP has been sent to your mobile number please verify it below

Register with PrepBytes

Dbms interview questions | set 2 | top 10 theory questions.

Last Updated on October 3, 2022 by Ria Pathak

DBMS stands for Database management System. DBMS is a software which is used to manage databases. It is used to insert, delete, update and retrieve data from the database by running some queries. It acts as an interface between the user and the database. Examples: MySQL, Oracle, SQL server, PostgreSQL and Microsoft Access.

DBMS is one of the must have skills for software engineers as proper analysis of data and generating insightful business insights using DBMS can help in growth of the business. So, a lot of companies ask questions related to DBMS in SDE interviews. Studying frequently asked DBMS interview questions before your interview can increase your chances of clearing the interview.

DBMS Interview Questions

1. What is a Database? Answer: A database is an organized and systematic collection of data, which is stored electronically on a computer. The database helps in managing large amounts of data efficiently. A software called DBMS can be used to extract data from the database by running some queries. A database consists of sets of tables or objects which contain records and fields. A row in the database represents a single entry and columns define the attributes which define a particular aspect or property of the table.

2. Explain different languages present in DBMS?

Answer : Different languages present in DBMS are:

DDL(Data Definition Language): DDL commands operate on the structure of the database and not on the data. This commands are required to define the database.I

• CREATE: This command is used to create a new database, tables, procedure, etc.
• ALTER: This command is used to alter the structure of the database.
• DROP: This command is used to delete records or tables from the database
• TRUNCATE: This command is used to delete all the records from a table.
• RENAME: This command is used to change the name of the database.

DML(Data Manipulation Language): DML commands are used to manipulate the data in the database.

• SELECT: This command is used to retrieve data from the database.
• UPDATE: This command is used to update existing data in the database.
• INSERT: This command is used to insert new data in the database.
• DELETE: This command is used to delete data from the database.

DCL(Data Control Language): DCL commands are used to control permissions for the database system access.

• GRANT: This command is used to provide access to the database to an user.
• REVOKE: This command is used to revoke access to the database from an user.

TCL(Transaction Control Language): TCL commands are used to handle transactions in the database.

• COMMIT: This command is used to commit a transaction.
• ROLLBACK: This command is used to rollback a transaction in case of a failure.
• SAVEPOINT: This command is used to set a savepoint within a transaction.

3. What are the different levels of data abstraction in the database? Answer: Data abstraction is the process of hiding complex and unwanted information from the user and showing only the essential information. Data abstraction makes the system simple and more secure. Levels of data abstraction are:

Physical or Internal Level It is the lowest level of abstraction for DBMS . It defines the structure of the database schema. It defines how the data is actually stored, it defines data-structures to store data and access methods used by the database. It is decided by developers or database application programmers how to store the data in the database.

Logical or Conceptual Level Logical level is the intermediate level or next higher level. It describes what data is stored in the database and also defines the relationship among the data. It tries to define entire data in the database. It also describes whether new tables need to be created and if new tables are created what be the links among those tables that are created.

View or External Level It is the highest level. It simplifies the view of the database by providing multiple views of the database. It also increases interaction between the user and the database. In view level, there are different levels of views and every view only defines a part of the entire data. View level can be used by all users (all levels’ users).

4. What is a join? Answer: Join is the process of joining two or more tables, based on some common field between them.

Example: Table: Student

Table: Course

Query: SELECT Course.Course_ID, Student.Student_name FROM Course INNER JOIN Student ON Course.Roll_no = Student.Roll_no ORDER BY tCourse.Course_ID;

The above query will produce the following table as the result:

5. What is the difference between left outer join and right outer join? Answer: Left outer join: If we perform left outer join or simply left join, on two tables then the resulting table will contain the overlapping data of both the tables and the non-overlapping data of the FIRST table.

Right outer join: If we perform right outer join or simply right join, on two tables then the resulting table will contain the overlapping data of both the tables and the non-overlapping data of the SECOND table.

6. What is the difference between DELETE and TRUNCATE command?

DELETE command : DELETE command is a data manipulation command which is used to delete the rows from the table based on the condition passed in the WHERE clause. This command maintains a log for each row it deletes and uses row lock while performing the DELETE function. The DELETE command can be rolled back.

TRUNCATE command : TRUNCATE command is a data definition command which is used to delete all rows from a table by deallocating the data pages used to store the table data. It is faster than the DELETE command because it records only the page deallocations in the transaction log. Unlike delete command TRUNCATE command cannot be rolled back.

7. What are relationships and mention different types of relationships in the DBMS

Answer : Relationship in DBMS refers to the scenario when two entities are related to each other. Types of relationships in DBMS are:

One-to-one relationship : When one row in database A is related to only one row in database B and vice versa.

Many-to-one relationship : When many rows in database A are related to only one row in database B.

Many-to-Many relationship : When multiple rows in database A are related to multiple rows in database B.

8. What do you mean by entity in DBMS?

Answer : An entity can be any real world object or thing that has an independent existence and can be uniquely identified.

Entity can be of two types:

Tangible entity : An entity which has a physical existence is called a tangible entity. Ex: Laptop, bike, car, person, etc.

Intangible entity: An entity which does not have a physical existence and only exists logically is called an intangible entity. Example: Prepbytes courses, bank account, etc.

9. How do you communicate with an RDBMS? You have to use Structured Query Language (SQL) to communicate with the RDBMS. Using queries of SQL, we can give the input to the database and then after processing of the queries database will provide us the required output.

10. What is BCNF?

BCMF stands for Boyce-Codd Normal Form . It is an advanced version of 3NF, so it is also referred to as 3.5NF. BCNF is stricter than 3NF. A table complies with BCNF if it satisfies the following conditions:

• It is in 3NF.
• For every functional dependency X->Y, X should be the super key of the table. It merely means that X cannot be a non-prime attribute if Y is a prime attribute.

Also you can prefer this DBMS Interview Questions article for better understanding, go and check it out!

We tried to discuss DBMS Interview Questions in this article. We hope this article gives you a better understanding of the type of DBMS Interview Questions. Prepbytes also provides a good collection of Foundation Courses that can help you enhance your coding skills. Want to make sure you ace the interview in one go? Join our Placement Program that will help you get prepared and land your dream job at MNCs. Mentors of Prepbytes are highly experienced and can provide you with basic, in-depth subject knowledge for better understanding.

Leave a Reply Cancel reply

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

Save my name, email, and website in this browser for the next time I comment.

• Linked List
• Segment Tree
• Backtracking
• Dynamic Programming
• Greedy Algorithm
• Operating System
• Company Placement
• Interview Tips
• General Interview Questions
• Data Structure
• Other Topics
• Computational Geometry
• Game Theory

Related Post

Federated database management system issues, challenges of database security, semantic heterogeneity, advantages of distributed database, timestamp based concurrency control, multiple granularity locking in dbms.

One stop guide to computer science students for solved questions, Notes, tutorials, solved exercises, online quizzes, MCQs and more on DBMS, Advanced DBMS, Data Structures, Operating Systems, Machine learning, Natural Language Processing etc.

TOPICS (Click to Navigate)

• Advanced Database Concepts
• Data structures, Operating Systems
• Natural Language Processing
• Quiz Questions and Answers
• DBMS, ADBMS Question Bank
• RDBMS Exam and Interview Questions
• Parallel Databases
• ADBMS Quizzes
• Advanced DBMS Concepts
• Distributed Databases
• Modern Databases - Special Purpose Databases
• Object Based Database Systems

Monday, April 27, 2020

Relational algebra in database management systems solved exercise, relational algebra – solved exercise, related links:.

• Go to DBMS - solved exercises page 
• Go to DBMS - Multiple Choice Questions (MCQs) page

solved exercises in dbms

Solved exercises in relational algebra, solved exercises in sql, sql and relational algebra short answers, sql and relational algebra short exercises, relational algebra exercises with answers explained, 6 comments:.

Is that last RA query wrong ? .. I think you forgot to filter upon the Jet agency

Thanks. Corrected

OMG THANK YOU SO MUCH

nyc presentation

in question (i) the arity for set difference must be same arity(agency ⨝ (Π aid (agency)) is not equal to arity(Π aid (σ pid = 123 (booking)))

You are correct. But you missed the set of parenthesis. Please check the RA expression once again. Thanks.

Featured Content

Multiple choice questions in natural language processing home.

MCQ in Natural Language Processing, Quiz questions with answers in NLP, Top interview questions in NLP with answers Multiple Choice Que...

All time most popular contents

• Machine Learning Multiple Choice Questions and Answers Home Top 5 Machine Learning Quiz Questions with Answers explanation, Interview questions on machine learning, quiz questions for data scienti...
• Relational algebra in database management systems solved exercise Relational algebra in database management systems solved exercise Relational algebra – solved exercise Question: Consider the fo...
• Multiple Choice Questions MCQ on Distributed Database Multiple Choice Questions MCQ on Distributed Database with answers Distributed Database – Multiple Choice Questions with Answers 1...
• Bigram probability estimate of a word sequence Bigram probability estimate of a word sequence, Probability estimation for a sentence using Bigram language model Bigram Model - Probab...
• Machine Learning Multiple Choice Questions and Answers 01 Top 5 Machine Learning Quiz Questions with Answers explanation, Interview questions on machine learning, quiz questions for data scientist...

• January 2024
• December 2023
• November 2023
• October 2023
• September 2023
• August 2023
• February 2023
• January 2023
• December 2022
• January 2022
• November 2021
• August 2021
• February 2021
• November 2019
• Academic Performance
• AU Syllabus
• Duplicate Certificate
• Internal Marks
• Malpractice
• Notes & QP
• Recent Notifications
• Transcripts
• WH (With-Held)
• Entries feed
• Comments feed
• WordPress.org

Database Management Systems (CS8492) Notes, Question Papers & Syllabus

APR/MAY 2023 EXAMS

Engineering Graphics (GE3251) [EG] Notes, Question Papers & Syllabus

Basic electrical, electronics and instrumentation engineering (be3252) [beeie] notes, question papers & syllabus, electric circuit analysis (ee3251) [eca] notes, question papers & syllabus.

Stay Connected with STUCOR App