dbms assignment questions with solutions

DBMS Lab Assignment Questions

These are DBMS Lab Assignment Questions for practice RDBMS in a lab environment and all queries were tested using Oracle 10g. The Queries may not give you expected result if you implement them on any other DBMS setup without necessary modifications.

Q1: Consider the following relational schema for the Office of the Controller of Examinations Application.

Student ( Rollno , Name, Dob, Gender, Doa, Bcode );

Implement a check constraint for

• Date of Admission

Branch ( Bcode , Bname, Dno );

Department ( Dno , Dname );

Course ( Ccode , Cname, Credits, Dno );

Branch_Course ( Bcode , Ccode , Semester );

Enrolls ( Rollno , Ccode , Sess , Grade );

For Example,

SESS can take values ‘APRIL 2013’, ‘NOV 2013’

Implement a check constraint for grade Value Set (‘S’, ‘A’, ‘B’, ‘C’, ‘D’, ‘E’, ‘U’ );

Students are admitted to Branches and they are offered by Departments. A branch is offered by only one department.

Each branch has a set of Courses (Subjects). Each student must enroll during a semester. Courses are offered by Departments. A course is offered only by one department. If a student is unsuccessful in a course he/she must enroll for the course during next session. A student has successfully completed a course if the grade obtained by is from the list (A, B, C, D, and E).

A student is unsuccessful if he/she have grade ‘U’ in a course.

Primary Keys are underlined.

These are questions for assignment 1. The solution is available after the last question.

Question (A)

Develop a SQL query to list details of Departments that offer more than 3 branches.

Question (B)

Develop a SQL query to list the details of Departments that offer more than 6 courses.

Question (C)

Develop a SQL query to list the details of courses that are common for more than 3 branches.

Question (D)

Develop a SQL query to list students who got ‘S’ in more than 2 courses during single enrollment.

Question (E)

Create a view that will keep track of the roll number, name and number of courses, a student has completed successfully.

Q2: Consider the following relations for an Order Processing Database application in a Company.

Customer ( Customerno varchar2 (5), Cname varchar2 (50) );

Implement check constraints to check Customerno starts with ‘C’ .

Cust_Order ( Orderno varchar2(5), Odate Date, Customerno references Customer, Ord_amt number(8) );

Implement check constraints to check Orderno starts with ‘ O ’.

Ord_amt is derived attribute (default value is 0);

Item ( Itemno varchar2 (5), Item_name varchar2 (30), unit_price number (5) );

Implement check constraint to check Itemno starts with ‘I’ .

Order_item ( Orderno references Cust_order, Itemno references item, qty number (3) );

Primary Key is underlined.

These are questions for assignment 2. The solution is available after the last question.

Develop DDL to implement above schema enforcing primary key, check constraints and foreign key constraints.

Populate Database with rich data set.

Develop SQL query to list the details of customers who have placed more than 3 orders.

Develop a SQL query to list details of items whose price is less than the average price of all items in each order.

Develop a SQL query to list the orderno and number of items in each order.

Question (F)

Develop a SQL query to list the details of items that are present in 25% of the orders.

Question (G)

Develop an update statement to update the value of Ord_amt.

Question (H)

Create a view that keeps track of detail of each customer and number of Order placed.

Q3: Consider the following relational schema

Staff ( Staffno number (5), Name varchar2 (30), Dob Date, Gender Char (2), Doj Date, Designation varchar2 (30), Basic_pay number (6), Deptno varchar2 (5) );

Gender must take value ‘M ’ or ‘F’.

Dept ( Deptno varchar2 (5), Name varchar2 (30) );

Skill ( Skill_code varchar2 (5), Description varchar2 (30), Charge_Outrage number (3) );

Staff_skill ( Staffno number (5), Skill_code varchar2 (5) );

Project ( Projectno varchar2 (5), Pname varchar2 (5), Start_Date Date, End_Date Date, Project_Manager_Staffno number (5) );

Project Number must start with ‘ P’.

Works ( Staffno number (5), Projectno varchar2 (5), Date_Worked_On Date, Intime Timestamp, Outtime Timestamp );

These are questions for assignment 3. The solution is available after the last question.

Develop DDL to implement the above schema specifying appropriate data types for each attributes and enforcing primary key, check constraints and foreign key constraints.

Populate the database with rich data set.

Develop a SQL query to list the departmentno and number of staff in each department,

Develop a SQL query to list the details of staff who earn the AVG basic pay of all staff.

Develop a SQL query to list the details of staff who have more than 3 skills.

Develop a SQL query to list the details of staff who have skills with a charge outrate greater than 60 per hour.

Create a view that will keep track of the department number, department name, the number of employees in the department and total basic pay expenditure for the department.

Develop a SQL query to list the details of Depts which has more than 5 staff working in it.

Question (I)

Q4: Consider the following relational schema for a banking database application.

Customer ( Cid , Cname );

Branch ( Bcode , Bname );

Account ( Ano , Atype, Balance, Cid, Bcode );

An account can be a saving account or a current account. Check Atype in ‘S’ or ‘C’. A customer can have both types of accounts.

Transaction ( Tid , Ano , Tttype, Tdate, Tamount );

Ttype can be ‘D’ or ‘W’.

D – Deposit, W – Withdrawal

These are questions for assignment 4. The solution is available after the last question.

Develop DDL to implement the above schema specifying an appropriate data type for each attribute enforcing primary key, check constraints and foreign key constraints.

Populate the database with a rich data set.

Develop a SQL query to list the details of customers who have a saving account and a current account.

Develop a SQL query to list the details of branches and the number of accounts in each branch.

Develop a SQL query to list the details of branches where the number of accounts is less than the average number of accounts in all branches.

Develop a SQL query to list the details of customers who have performed three transaction on a day.

Create a view that will keep track of branch details and the number of accounts in each branch.

Ads Blocker Detected!!!

We have detected that you are using extensions to block ads. Please support us by disabling these ads blocker.

50 SQL Query Practice Questions for Interview

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

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

SQL Query Questions and Answers for Practice

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

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

• Most Frequently Asked SQL Interview Questions

Prepare Sample Data To Practice SQL Skills

Sample table – worker.

Sample Table – Bonus

Sample Table – Title

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

SQL Script to Seed Sample Data.

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

Start with 20 Basic SQL Questions for Practice

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

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

The required query is:

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

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

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

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

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

The general query to clone a table with data is:

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

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

The following MySQL query returns the current date:

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

Specify the SQL query in the below code box:

18 Complex SQL Queries for Practice

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

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

MySQL query to find the nth highest salary:

SQL Server query to find the nth highest salary:

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

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

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

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

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

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

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

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

The following query returns the expected result:

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

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

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

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

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

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

SQL Performance Interview Guide

Check 25 SQL performance-related questions and answers.

Keep Learning SQL, TechBeamers.

You Might Also Like

Where clause in sql: a practical guide, a beginner’s guide to sql joins, top 50 tricky sql queries for interview, how to create a table in sql, if statement in sql queries: a quick guide.

Leave a Reply

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

Popular Tutorials

• Data Science
• Trending Now
• Data Structures
• System Design
• Foundational Courses
• Practice Problem
• Machine Learning
• Data Science Using Python
• Web Development
• Web Browser
• Design Patterns
• Software Development
• Product Management
• Programming

50 DBMS MCQs with Answers

In E-R model, Y is the dominant entity and X is subordinate entity

None of the above

If Y is deleted, then X is also deleted

If Y is deleted, then X is not deleted

If X is deleted, then Y is also deleted

Let R 1 (a, b, c) and R 2 (x, y, z) be two relations in which a is the foreign key of R 1 that refers to the primary key of R 2 . Consider following four options. (a)Insert into R 1 (b)Insert into R 2 (c)Delete from R 1 (d)Delete from R 2 Which of the following is correct about the referential integrity constraint with respect to above?

• Operations (a) and (b) will cause violation.
• Operations (b) and (c) will cause violation.
• Operations (c) and (d) will cause violation.
• Operations (d) and (a) will cause violation.

A view of database that appears to an application program is known as

Virtual table

Index Table

The relation schemas R 1 and R 2 form a Lossless join decomposition of R if and only if: (a) R 1 ∩ R 2 ↠ (R 1 - R 2 ) (b) R 1 → R 2 (c) R 1 ∩ R 2 ↠ (R 2 - R 1 ) (d)(R 2 → R 1 ) ∩ R 2

• (a) and (b) happens
• (a) and (d) happens
• (a) and (c) happens
• (b) and (c) happens
• Reflexivity, Augmentation and Decomposition
• Transitivity, Augmentation and Reflexivity
• Augmentation, Transitivity, Reflexivity and Decomposition
• Reflexivity, Transitivity and Decomposition
• Weak entities can be deleted automatically when their strong entity is deleted.
• Weak entity set avoids the data duplication and consequent possible inconsistencies caused by duplicating the key of the strong entity.
• A weak entity set has no primary keys unless attributes of the strong entity set on which it depends are included
• Tuples in a weak entity set are not partitioned according to their relationship with tuples in a strong entity set.

Question 10

• Functional Dependencies
• Data integrity
• Referential integrity
• Normal Forms

There are 50 questions to complete.

• SQL Server training
• Write for us!

SQL Practice – common questions and answers for SQL skills

This article will show some important questions and answers to practice SQL.

Introduction

SQL is the base to handle different databases. Even some of the NoSQL databases use an extension of SQL to query data. Data Scientists, BI Analysts, BI Developers, DBAs, Database Developers, Data Engineers, Reporting Analysts, and several other jobs require SQL knowledge as part of the knowledge required. For data management, SQL knowledge is the base to handle databases and the numbers of jobs related to databases are growing each day. The software industry, including databases, is the world’s most in-demand profession. That is why in this article, we will show some questions and answers to practice SQL.

Requirements

In order to start, you need the SQL Server installed and the Adventureworks sample database installed. The following links can help you if you do not have them installed:

• How to install SQL Server developer edition
• Install and configure the AdventureWorks2016 sample database

Q1. Please select all the information of persons table that the name starts with A

A1. You will show all the columns with the select * and the where [FirstName] like ‘a%’ will filter the data to all the persons that the name starts with a. The query used is the following:

The LIKE operator is very common in SQL queries. The like ‘a%’ will show all the first names that start with the letter a. There are several other operators that you need to practice like the EXISTS, IN, =, <>, ANY. The following link provides more information about the operators:

• Operators (Transact-SQL)

Note that the use of * is a bad practice for big tables, but in this case, it is a small table, so it will not impact performance. In general, try to select only the columns required and not all.

Q2. Create a store procedure that receives the first name of the person table as input and the last name as output.

In SQL practice, it is necessary to mention the stored procedures because they are frequently used in SQL Server. The stored procedures are T-SQL sentences that cannot be applied to other databases like Oracle, MySQL, PostgreSQL. The following sample of code shows how to create a stored procedure named GetLastName. It receives the parameter @firstname and returns the last name of the provided firstname. It queries the Person table:

To call the stored procedure using T-SQL, you will need to declare a variable to store the output variable. We use the execute command to call a stored procedure. John is the parameter value for the first name and the last name is the output of the stored procedure:

The result displayed by this store procedure invocation will be something like this:

For more information about creating stored procedures, please refer to this link:

• Learn SQL: User-Defined Stored Procedures
• SQL Server stored procedures for beginners
• CREATE PROCEDURE (Transact-SQL)

Q3. Which query would you execute to delete all the rows from the person table with minimal bulk-logged activity?

A3. The truncate table sentence removes data without logging individual row deletions. It is the most efficient way to remove all the data. The delete command, on the other hand, logs a lot of data if we have multiple rows and can consume a lot of space in the transaction log files. So, the code will be the following:

Truncate table person.person

To practice SQL, try the truncate and delete statements. For more detailed information about the differences between the truncate and the delete sentences, refer to this link:

• The internals of SQL Truncate and SQL Delete statements

Q4. Create a query to show the account number and customerid from the customer table for the customer without sales orders.

A4. The query would be something like this:

The result displayed will be something like this:

We are using the left join to look for all the customers without sales, so the salesorderid will be null. In SQL practice, you need to know the use of the different JOINS like the LEFT JOIN, RIGHT JOIN, CROSS JOIN. We created some articles about the different types of JOINs here. It is very important for you to check, practice, and study all the options:

• Learn SQL: Join multiple tables
• Learn SQL: INNER JOIN vs LEFT JOIN
• SQL CROSS JOIN with examples

We could also use the EXISTS or the IN operators instead of using the JOINS. The performance is different according to the scenario. The performance in queries is out of the scope of this SQL practice. However, we have an entire article about this topic here:

• How to design SQL queries with better performance: SELECT * and EXISTS vs IN vs JOINs

Q5. You have a table with some of the temperatures in Celsius of some patients. Create a function or a stored procedure to get the convert Fahrenheit to Celsius. Would you use a function or a stored procedure?

A5. Basically, the question here is to create a stored procedure or a function to convert from Celsius to Fahrenheit. If you already have a table with some rows in Celsius, the easiest option is to use a function. The function could be something like this:

Invoking a function is easier than a stored procedure. For more information about the use of functions vs stored procedures, refer to this link:

• Functions vs stored procedures in SQL Server

Q6. Create a query to show the top 10 customerIDs of users with more Orders.

A6. For this practice test, we will use the TOP 10 to get the customer IDs with more orders. We will use the SUM to SUM the Order Quantity column. Note that for aggregated functions like the SUM, the alias is needed to define the column name in the query. Also, the Sum is grouped by the customer ID. Usually aggregated functions come with the GROUP BY clause. Finally, we are using the order by to order the result in descendant order:

For more information about aggregated functions like the SUM, MAX, MIN, AVG, refer to this link:

• Aggregate Functions (Transact-SQL)

In this article, we show different questions for SQL practice. We show some questions and answers to practice SQL and improve the knowledge. If you have more questions about this topic, do not hesitate to contact us.

• Recent Posts

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

Related posts:

• A complete guide to T-SQL Metadata Functions in SQL Server
• Top SQL Server Books
• An overview of the SQL Server Update Join
• FOR XML PATH clause in SQL Server
• Creating and using CRUD stored procedures

Advanced SQL Practice: 10 Exercises with Solutions

• sql practice
• Online practice
• advanced sql

Table of Contents

Practicing Your Way to SQL Proficiency

Exercise 1: list all clothing items, exercise 2: get all non-buying customers, exercise 3: select all main categories and their subcategories, exercise 4: organize runners into groups, exercise 5: how many runners participate in each event, exercise 6: group runners by main distance and age, exercise 7: list the top 3 most expensive orders, exercise 8: compute deltas between consecutive orders, exercise 9: compute the running total of purchases per customer, exercise 10: find the invitation path for each student, advancing one query at a time.

As SQL proficiency continues to be in high demand for data professionals and developers alike, the importance of hands-on practice cannot be emphasized enough. Read on to delve into the world of advanced SQL and engage in practical exercises to enhance your skills.

This article provides you with a collection of ten challenging SQL practice exercises specifically for those seeking to enhance their advanced SQL skills. The exercises cover a selection of SQL concepts and will help you refresh your advanced SQL knowledge. Each exercise is accompanied by a detailed solution, allowing you to test your knowledge and gain a deeper understanding of complex SQL concepts. The exercises come from our advanced SQL practice courses . If you want to see more exercises like this, check out these courses:

• Window Functions Practice Set
• 2021 Monthly SQL Practice Sets - Advanced
• 2022 Monthly SQL Practice Sets - Advanced

Find out how you can practice advanced SQL with our platform.

Let’s get started.

Practice is an integral component in mastering SQL; its importance cannot be overstated. The journey to becoming proficient in advanced SQL requires dedication, perseverance, and a strong commitment to continuous practice. By engaging in regular advanced SQL practice, individuals can sharpen their skills, expand their knowledge, and develop a deep understanding of the intricacies of data management and manipulation.

Advanced SQL exercises serve as invaluable tools, challenging learners to apply their theoretical knowledge in practical scenarios and further solidifying their understanding of complex concepts. With each session of dedicated SQL practice, you can discover efficient techniques and gain the confidence needed to tackle real-world data challenges.

Let’s go over the exercises and their solutions.

Advanced SQL Practice Exercises

We’ll present various advanced SQL exercises that cover window functions , JOINs, GROUP BY, common table expressions (CTEs), and more.

Section 1: Advanced SQL JOIN Exercises

In the following advanced SQL exercises, we’ll use a sportswear database that stores information about clothes, clothing categories, colors, customers, and orders. It contains five tables: color , customer , category , clothing , and clothing_order . Let's look at the data in this database.

The color table contains the following columns:

• id stores the unique ID for each color.
• name stores the name of the color.
• extra_fee stores the extra charge (if any) added for clothing ordered in this color.

In the customer table, you'll find the following columns:

• id stores customer IDs.
• first_name stores the customer's first name.
• last_name stores the customer's last name.
• favorite_color_id stores the ID of the customer's favorite color (references the color table).

The category table contains these columns:

• id stores the unique ID for each category.
• name stores the name of the category.
• parent_id stores the ID of the main category for this category (if it's a subcategory). If this value is NULL , it denotes that this category is a main category. Note: Values are related to those in the id column in this table.

The clothing table stores data in the following columns:

• id stores the unique ID for each item.
• name stores the name of that item.
• size stores the size of that clothing: S, M, L, XL, 2XL, or 3XL.
• price stores the item's price.
• color_id stores the item's color (references the color table).
• category_id stores the item's category (references the category table).

The clothing_order table contains the following columns:

• id stores the unique order ID.
• customer_id stores the ID of the customer ordering the clothes (references the customer table).
• clothing_id stores the ID of the item ordered (references the clothing table).
• items stores how many of that clothing item the customer ordered.
• order_date stores the date of the order.

Let’s do some advanced SQL exercises that focus on JOINs .

Display the name of clothing items (name the column clothes ), their color (name the column color ), and the last name and first name of the customer(s) who bought this apparel in their favorite color. Sort rows according to color, in ascending order.

Solution explanation:

We want to display the column values from three different tables ( clothing , color , and customer ), including information on which customer ordered a certain item (from the clothing_order table). Therefore, we need to join these four tables on their common columns.

First, we select from the clothing_order table (aliased as co ) and join it with the clothing table (aliased as cl ). We join the tables using the primary key column of the clothing table ( id ) and the foreign key column of the clothing_order table ( clothing_id ); this foreign key column links the clothing and clothing_order tables.

Next, we join the color table (aliased as col ) with the clothing table (aliased as cl ). Here we use the primary key column of the color table ( id ) and the foreign key column of the clothing table ( color_id ).

Finally, we join the customer table (aliased as cus ) with the clothing_order table (aliased as co ). The foreign key of the clothing_order table ( customer_id ) links to the primary key of the customer table ( id ).

The ON clause stores the condition for the JOIN statement. For example, an item from the clothing table with an id of 23 is joined with an order from the clothing_order table where the clothing_id value equals 23.

Follow this article to see more examples on JOINing three (or more) tables . And here is how to LEFT JOIN multiple tables .

Select the last name and first name of customers and the name of their favorite color for customers with no purchases.

Here we need to display customers’ first and last names from the customer table and their favorite color name from the color table. We must do it only for customers who haven’t placed any orders yet; therefore, we require information from the clothing_order table. So the next step is to join these three tables.

First, we join the customer table (aliased as cus ) with the color table (aliased as col ). To do that, we use the following condition: the primary key column of the color table ( id ) must be equal to the foreign key column of the customer table ( favorite_color_id ). That lets us select the favorite color name instead of its ID.

Here is how to ensure that we list only customers who haven’t placed any orders yet:

• We LEFT JOIN the clothing_order table (aliased as o ) with the customer table (aliased as cus ) to ensure that all rows from the customer table (even the ones with no match) are listed.
• In the WHERE clause, we define a condition to display only the rows with the customer_id column from the clothing_order table equal to NULL (meaning only the customers whose IDs are not in the clothing_order table will be returned).

There are different types of JOINs , including INNER JOIN , LEFT JOIN , RIGHT JOIN , and FULL JOIN . You can learn more by following the linked articles.

Select the name of the main categories (which have a NULL in the parent_id column) and the name of their direct subcategory (if one exists). Name the first column category and the second column subcategory.

Each category listed in the category table has its own ID (stored in the id column); some also have the ID of their parent category (stored in the parent_id column). Thus, we can link the category table with itself to list main categories and their subcategories.

The kind of JOIN where we join a table to itself is colloquially called a self join . When you join a table to itself, you must give different alias names to each copy of the table. Here we have one category table aliased as c1 and another category table aliased as c2 .

We select the name from the category table (aliased as c1 ) and ensure that we list only main categories by having its parent_id column equal to NULL in the WHERE clause. Next, we join the category table (aliased as c1 ) with the category table (aliased as c2 ). The latter one provides subcategories for the main categories. Therefore, in the ON clause, we define that the parent_id column of c2 must be equal to the id column of c1 .

Read this article to learn more about self joins .

The exercises in this section have been taken from our course 2021 Monthly SQL Practice Sets - Advanced . Every month we publish a new SQL practice course in our Monthly SQL Practice track; every odd-numbered month, the course is at an advanced level. The advanced SQL practice courses from 2021 have been collected in our 2021 Monthly SQL Practice Sets - Advanced course. Check it out to find more JOIN exercises and other advanced SQL challenges.

Section 2: Advanced GROUP BY Exercises

In the following advanced SQL exercises, we’ll use a sportsclub database that stores information about runners and running events. It contains three tables: runner , event , and runner_event . Let's look at the data in this database.

The runner table contains the following columns:

• id stores the unique ID of the runner.
• name stores the runner's name.
• main_distance stores the distance (in meters) that the runner runs during events.
• age stores the runner's age.
• is_female indicates if the runner is male or female.

The event table contains the following columns:

• id stores the unique ID of the event.
• name stores the name of the event (e.g. London Marathon, Warsaw Runs, or New Year Run).
• start_date stores the date of the event.
• city stores the city where the event takes place.

The runner_event table contains the following columns:

• runner_id stores the ID of the runner.
• event_id stores the ID of the event.

Let’s do some advanced SQL exercises that focus on GROUP BY .

Select the main distance and the number of runners that run the given distance ( runners_number ). Display only those rows where the number of runners is greater than 3.

Solution explanation :

We want to get the count of runners for each distance that they run. To do that, we need to group all runners by distance and use the COUNT() aggregate function to calculate how many runners are in each distance group.

We select the main_distance column and GROUP BY this column. Now when we use the COUNT() aggregate functions, it is going to give us the number of runners that match each main_distance value.

The GROUP BY clause is used to group rows from a table based on one or more columns. It divides the result set into subsets or groups, where each group shares the same values in the specified column(s). This allows us to perform aggregate functions (such as SUM() , COUNT() , AVG() , etc.) on each group separately.

Here are the most common GROUP BY interview questions .

To display only the groups with more than three runners, we use a HAVING clause that filters the values returned by the COUNT() aggregate function.

The HAVING clause is often used together with the GROUP BY clause to filter the grouped data based on specific conditions. It works similarly to the WHERE clause, but it operates on the grouped data rather than individual rows. Check out this article to learn more about the HAVING clause .

Display the event name and the number of club members that take part in this event (call this column runner_count ). Note that there may be events in which no club members participate. For these events, the runner_count should equal 0.

Here we want to display the event name from the event table and the number of participants from the runner table. The event and runner tables are linked by a many-to-many relation; to join these tables, we also need the runner_event table that relates events and runners.

First, we select from the event table. Then, we LEFT JOIN it with the runner_event table, which is further LEFT JOINed with the runner table. Why do we use the LEFT JOIN here? Because we want to ensure that all events (even the ones with no participants) are displayed.

We select the event name and the count of all participants; therefore, we need to GROUP BY the event name to get the count of participants per event. Please note that we use COUNT(runner_id) instead of COUNT(*) . This is to ensure that we display zero for events with no participants (i.e. for events that do not link to any runner_id ). You can read more about different variants of the COUNT() function here .

Display the distance and the number of runners there are for the following age categories: under 20, 20–29, 30–39, 40–49, and over 50. Use the following column aliases: under_20 , age_20_29 , age_30_39 , age_40_49 , and over_50 .

This is similar to Exercise 4 – we want to know the number of runners per distance value. So we select the main_distance column and GROUP BY this column. Then, we use several COUNT() aggregate functions to get the number of runners per distance. However, here we need to further divide the runners according to their age.

The CASE WHEN statement comes in handy here, as it can be used to evaluate conditions and return different values based on the results of those conditions. We can pass it as an argument to the COUNT() aggregate function to get the number of runners fulfilling a given condition. Let’s see how that works.

This CASE WHEN statement returns id only when a runner’s age is greater than or equal to 20 and less than 30. Otherwise, it returns NULL . When wrapped in the COUNT() aggregate function, it returns the count of runners fulfilling the condition defined in the CASE WHEN statement.

To get the number of runners for each of the five age groups, we need to use as many COUNT() functions and CASE WHEN statements as we have age groups. You can read about counting rows by combining CASE WHEN and GROUP BY here .

Section 3: Advanced Window Functions Exercises

In the following advanced SQL exercises, we’ll use a Northwind database for an online shop with numerous foods. It contains six tables: customers , orders , products , categories , order_items , and channels . Let's look at the data in this database.

The customers table has 15 columns:

• customer_id stores the ID of the customer.
• email stores the customer’s email address.
• full_name stores the customer’s full name.
• address stores the customer’s street and house number.
• city stores the city where the customer lives.
• region stores the customer’s region (not always applicable).
• postal_code stores the customer’s ZIP/post code.
• country stores the customer’s country.
• phone stores the customer’s phone number.
• registration_date stores the date on which the customer registered.
• channel_id stores the ID of the channel through which the customer found the shop.
• first_order_id stores the ID of the first order made by the customer.
• first_order_date stores the date of the customer’s first order.
• last_order_id stores the ID of the customer’s last (i.e. most recent) order.
• last_order_date stores the date of the customer’s last order.

The orders table has the following columns:

• order_id stores the ID of the order.
• customer_id stores the ID of the customer who placed the order.
• order_date stores the date when the order was placed.
• total_amount stores the total amount paid for the order.
• ship_name stores the name of the person to whom the order was sent.
• ship_address stores the address (house number and street) where the order was sent.
• ship_city stores the city where the order was sent.
• ship_region stores the region in which the city is located.
• ship_postalcode stores the destination post code.
• ship_country stores the destination country.
• shipped_date stores the date when the order was shipped.

The products table has the following columns:

• product_id stores the ID of the product.
• product_name stores the name of the product.
• category_id stores the category to which the product belongs.
• unit_price stores the price for one unit of the product (e.g. per bottle, pack, etc.).
• discontinued indicates if the product is no longer sold.

The categories table has the following columns:

• category_id stores the ID of the category.
• category_name stores the name of the category.
• description stores a short description of the category.

The order_items table has the following columns:

• order_id stores the ID of the order in which the product was bought.
• product_id stores the ID of the product purchased in the order.
• unit_price stores the per-unit price of the product. (Note that this can be different from the price in the product’s category; the price can change over time and discounts can be applied.)
• quantity stores the number of units bought in the order.
• discount stores the discount applied to the given product.

The channels table has the following columns:

• channel_name stores the name of the channel through which the customer found the shop.

Let’s do some advanced SQL exercises that focus on window functions.

Create a dense ranking of the orders based on their total_amount . The bigger the amount, the higher the order should be. If two orders have the same total_amount , the older order should go higher (you'll have to add the column order_date to the ordering). Name the ranking column rank . After that, select only the orders with the three highest dense rankings . Show the rank, order_id , and total_amount .

Let’s start with the first part of the instruction. We want to create a dense ranking of orders based on their total_amount (the greater the value, the higher the rank) and their order_date value (the older the date, the higher the rank). Please note that the rank value may be duplicated only when total_amount and order_date columns are both equal for more than one row.

To do that, we use the DENSE_RANK() window function. In its OVER() clause, we specify the order: descending for total_amount values and ascending for order_date values. We also display the order_id and total_amount columns from the orders table.

Until now, we listed all orders along with their dense rank values. But we want to see only the top 3 orders (where the rank column is less than or equal to 3). Let’s analyze the steps we take from here:

• We define a Common Table Expression (CTE) using this SELECT statement – i.e. we use the WITH clause followed by the CTE’s name and then place the SELECT statement within parentheses.
• Then we select from this CTE, providing the condition for the rank column in the WHERE clause.

You may wonder why we need such a complex syntax that defines a CTE and then queries it. You may say that we could set the condition for the rank column in the WHERE clause of the first SELECT query. Well, that’s not possible because of the SQL query order of execution.

We have to use the Common Table Expression here because you can’t use window functions in the WHERE clause. The order of operations in SQL is as follows:

• FROM , JOIN
• Aggregate functions
• Window functions

You may only use window functions in SELECT and ORDER BY clauses. If you want to refer to window functions in the WHERE clause, you must place the window function computation in a CTE (like we did in our example) or in a subquery and refer to the window function in the outer query.

Follow this article to learn more about CTEs and recursive CTEs .

To give you some background on the available ranking functions , there are three functions that let you rank your data: RANK() , DENSE_RANK() , and ROW_NUMBER() . Let’s see them in action.

The RANK() function assigns the same rank if multiple consecutive rows have the same value. Then, the next row gets the next rank as if the previous rows had distinct values. Here, the ranks 1,1,1 are followed by 4 (as if it was 1,2,3 instead of 1,1,1 ).

The DENSE_RANK() function also assigns the same rank if multiple consecutive rows have the same value. Then, the next row gets the next rank one greater than the previous one. Here, 1,1,1 is followed by 2.

The ROW_NUMBER() function assigns consecutive numbers to each next row without considering the row values.

Here is an article on how to rank data . You can also learn more about differences between SQL’s ranking functions .

In this exercise, we're going to compute the difference between two consecutive orders from the same customer.

Show the ID of the order ( order_id ), the ID of the customer ( customer_id ), the total_amount of the order, the total_amount of the previous order based on the order_date (name the column previous_value ), and the difference between the total_amount of the current order and the previous order (name the column delta ).

Here we select the order ID, customer ID, and total amount from the orders table. The LAG() function fetches the previous total_amount value. In the OVER() clause, we define the LAG() function separately for each customer and order the outcome by an order date. Finally, we subtract the value returned by the LAG() function from the total_amount value for each row to get the delta.

The previous_value column stores null for the first row, as there are no previous values. Therefore, the delta column is also null for the first row. The following delta column values store the differences between consecutive orders made by the same customer.

It is worth mentioning that a delta represents the difference between two values. By calculating the delta between daily sales amounts, we can determine the direction of sales growth/decline on a day-to-day basis.

Follow this article to learn more about calculating differences between two rows . And here is how to compute year-over-year differences .

For each customer and their orders, show the following:

• customer_id – the ID of the customer.
• full_name – the full name of the customer.
• order_id – the ID of the order.
• order_date – the date of the order.
• total_amount – the total spent on this order.
• running_total – the running total spent by the given customer.

Sort the rows by customer ID and order date.

A running total refers to the calculation that accumulates the values of a specific column or expression as rows are processed in a result set. It provides a running sum of the values encountered up to the current row. A running total is calculated by adding the current value to the sum of all previous values. This can be particularly useful in various scenarios, such as tracking cumulative sales, calculating running balances or analyzing cumulative progress over time.

Follow this article to learn more about computing a running total . And here is an article about computing running averages .

We select customer ID, order ID, order date, and order total from the orders table. Then, we join the orders table with the customers table on their respective customer_id columns so we can display the customer's full name.

We use the SUM() window function to calculate the running total for each customer separately ( PARTITION BY orders.customer_id ) and then order ascendingly by date ( ORDER BY orders.order_date ).

Finally, we order the output of this query by customer ID and order date.

Section 4: Advanced Recursive Query Exercises

In the following advanced SQL exercises, we’ll use a website database that stores information about students and courses. It contains three tables: student , course , and student_course . Let's look at the data in this database.

The student table contains the following columns:

• id stores the unique ID number for each student.
• name stores the student's name.
• email stores the student's email.
• invited_by_id stores the ID of the student that invited this student to the website. If the student signed up without an invitation, this column will be NULL.

The course table consists of the following columns:

• id stores the unique ID number for each course.
• name stores the course's name.

The student_course table contains the following columns:

• id stores the unique ID for each row.
• student_id stores the ID of the student.
• course_id stores the ID of the course.
• minutes_spent stores the number of minutes the student spent on the course.
• is_completed is set to True when the student finishes the course.

The exercises in this section have been taken from our Window Functions Practice Set . In this set, you will find more window function exercises on databases that store retail, track competitions, and website traffic.

Let’s do some advanced SQL exercises that focus on recursive queries.

Show the path of invitations for each student (name this column path ). For example, if Mary was invited by Alice and Alice wasn't invited by anyone, the path for Mary should look like this: Alice->Mary .

Include each student's id , name , and invited_by_id in the results.

This exercise requires us to create a custom value for the path column that contains the invitation path for each customer. For example, Ann Smith was invited by Veronica Knight , who in turn was invited by Karli Roberson ; hence, we get the path column as Karli Roberson->Veronica Knight->Ann Smith for the name Ann Smith .

As you may notice, we need a recursion mechanism to dig down into the invitation path. We can write a recursive query by defining it with the WITH RECURSIVE statement, followed by the query name.

The content of the hierarchy recursive query is as follows:

• We select the id , name , and invited_by_id columns from the student table. Then, we use the CAST() function to cast the name column type to the TEXT data type, ensuring smooth concatenation (with -> and the following names) in the main query. The WHERE clause condition ensures that only students who haven’t been invited are listed by this query.
• The UNION ALL operator combines the result sets of two or more SELECT statements without removing duplicates. Here the queries on which UNION ALL is performed have the same sets of four columns; the result set of one is appended to the results set of another.
• In the next SELECT statement, we again select the id , name , and invited_by_id columns from the student table. Then, we concatenate the path column (that comes from the hierarchy recursive query as defined in the first SELECT statement) with the -> sign and the student name. To accomplish this concatenation, we select from both the student table and the hierarchy recursive query.(This is where the recursive mechanism comes into play.) In the WHERE clause, we define that the invited_by_id column of the student table is equal to the id column of the hierarchy recursive query, so we get the student name who invited the current student; on the next iteration, we get the name of the student who invited that student, and so on.

This is called a recursive query, as it queries itself to work its way down the invitation path.

The advanced SQL exercises presented in this article provide a comprehensive platform for honing your SQL skills, one query at a time. By delving into window functions, JOINs , GROUP BY , and more, you have expanded your understanding of complex SQL concepts and gained hands-on experience in solving real-world data challenges.

Practice is the key to mastering SQL skills. Through consistent practice, you can elevate your proficiency and transform your theoretical knowledge into practical expertise. This article showcased exercises from our courses; you can discover more exercises like this by enrolling in our:

Sign up now and get started for free! Good luck!

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

Navigation Menu

Search code, repositories, users, issues, pull requests..., provide feedback.

We read every piece of feedback, and take your input very seriously.

Saved searches

Use saved searches to filter your results more quickly.

To see all available qualifiers, see our documentation .

• Notifications You must be signed in to change notification settings

This repository contains solutions to the weekly assignments (2 & 3) of the Database Management Systems (DBMS) course offered by the Indian Institute of Technology Madras (IITM) as part of the Diploma in Programming program. Additionally, it also contains past OPPE solutions for the course.

NebulaTris/DBMS_IITM

Folders and files.

Repository files navigation

Dbms_iitm ✨, course overview.

The DBMS course covers the fundamental concepts of database management systems, including the relational data model, SQL, transaction management, concurrency control, and database design.

Folder Structure

The repository is structured as follows:

How to Use this Repository

• Clone the repository
• The Practice.md files contain practice queries for each week, while the Graded.md files contain graded queries.
• The OPPE practice questions for Week 7 are stored in separate files named Ques1.md through Ques6.md.
• The OPPE Python-PostgreSQL questions are stored in separate files named Q1.md and Q2.md.
• To use this repository, navigate to the desired folder and open the relevant markdown file in a text editor or browser.
• Use the solutions as a reference to check your own work and to learn more about the concepts covered in the course.
• The solutions provided in this repository are meant to be used as a reference only. It is recommended that you try to solve the assignments on your own before referring to the solutions.
• The solutions provided may not be the most optimal or efficient solutions. They are intended to serve as a starting point for your own work.
• This repository is not affiliated with the Indian Institute of Technology Madras or the Diploma in Programming program. The solutions provided are the work of the repository owner and contributors.

Contributing

Contributions to this repository are welcome. If you find any errors or would like to add more queries, feel free to create a pull request. Please ensure that your queries are organized according to the folder structure above.

Meme Time 😎

This repository is licensed under the MIT License. See the LICENSE file for more details

• Markdown 100.0%

DBMS assignment questions with solutions

1. Data types BIT and BIT VARYING are known as ________.

Answer:- Bit string data types

2. ________ is collection of tables where table is an unordered collection of rows.

Answer:- Relational Database

3. Physical design is tailored to a specific DBMS system.

Answer:- True

4. WWW stands for :-

Answer:- World wide web

5. In a two tier architecture, the software components are distributed over which two systems?

Answer:- Client , Server

6. ________ are binary computer representations of stored logical entities.

Answer:- data

7. Access to a database can be controlled using :-

Answer:- Access Privileges

8. Which of the following is a criterion that should be satisfied by an optimal data model?

Answer:- Structural validity , Express ability , Shareability

9. A ________ is a situation, event or personnel that will adversely affect the database security and the smooth functioning of organization.

Answer:- Threat

10. Different syntaxes used for update command in SQL are as follows :-

Answer:- update set = ,update set = where

11. Database design model help in improving maintainability.

12. If role B has been previously granted to role A :-

Answer:- Role A cannot be granted to role B

13. Write examples of dbms :-

Answer:- FoxBASE , FoxPro

14. A database must be protected against :-

Answer:- Viruses

15. Each attribute is associted with set of values called as________

Answer:- Domain

16. A fuzzy checkpoint allows transactions to perform updates even while buffer blocks are being written out.

17. When a particular transaction never makes progress because the data item it wants to lock is already locked and other transactions are also waiting for the same data item then it is said to be :-

Answer:- Starvation

18. A program is a collections of instructions for manipulating data.

19. The set of all relationships of the same type called as

Answer:- Relationship set

20. Deadlock prevention scheme using time stamps are:-

Answer:- Wait - die , Wound - wait

21. Specify any 2 forms of insert command :-

Answer:- INSERT INTO SELECT , FROM , INSERT INTO SELECT , FROM WHERE

22. Which SQL statement will show contents of table book, where 2nd character of bookname is 'm' ?

Answer:- select * from book where bookname like '_m%'

23. In a database for geographic information system ________ can be stored.

Answer:- Satellite images

24. ________ is visual representation of Entity Relationships.

Answer:- ERD

25. Some clauses of GRANT command are as follows :-

Answer:- ALL , ON , PUBLIC

26. Which of the following is high level data model?

Answer:- Entity-Relationship model

27. Advantages of Database Management System are :-

Answer:- Reduced redundancy , Sharing of data , Security

28. ________ is the process by which the user's access to physical data in the application is limited, based on his privileges.

Answer:- Access control

29. Diagrammatic representation in data modeling is ________ to represent a model in diagrammatic notation.

Answer:- Capability

30. Entity name is written in ________ in E-R diagram.

Answer:- Upper case

31. The database is partitioned into some number of fixed length block called as pages.

32. Following are properties of roles allowed for easier privilege management :-

Answer:- Application awareness , Reduced privilege administration , Dynamic privilege administration

33. Database is widely used in banking.

34. What is many-to-one relationship between two sets of attributes of a given relation?

Answer:- Functional dependency

35. Match The Following

36. File is collection of ________

Answer:- Records

37. The levels of abstraction of data are :-

Answer:- Physical level , Logical level , View level

38. >= ' is arithmetic operator.

Answer:- False

39. DES provides standards based encryption for data privacy.

40. Block movements between disk and main memory are initiated through two operations namely :-

Answer:- Input , Outpu

41. What is alternative name for functions?

Answer:- Methods

42. ERD captures :-

Answer:- Entity type , Attributes , Relationship between entities

43. Match The Following

44. ________are two critical phases in database development lifecycle.

Answer:- Data modeling and database design

45. The set of all entities of the same type called as

Answer:- entity set

46. A graphical user interface typically displays a schema to the user in which form?

Answer:- Diagrammatic form

47. Command to create table catalog having similar structure as book is :-

Answer:- CREATE TABLE CATALOG LIKE BOOK

• MassiveMark Playground
• Transliteration Playground
• Professional Practice Test
• Our Services
• Assignmenthelp Services
• Custom Writing help
• Free Assignment Samples
• Free Homework Help Samples
• Terms of Use
• Refund Policy

• ▼PL/SQL Exercises with Solution
• Introduction to PL/SQL
• Fundamentals
• Control Statement
• String Functions
• Exception Handling
• Object-Oriented Programming
• ..More to come..

PL/SQL Exercises with Solution

Introduction.

The best way we learn anything is by practice and exercise questions. We have started this section for those (beginner to intermediate) who are familiar with SQL and Oracle . Exercises are designed to enhance your ability to write well-structured PL/SQL programs. Hope, these exercises help you to improve your PL/SQL query skills. Currently following sections are available, we are working hard to add more exercises. Happy Coding!

• PL/SQL Fundamentals [ 16 Exercises ]
• PL/SQL DataType [ 8 Exercises ]
• PL/SQL Control Statement [ 30 Exercises ]
• PL/SQL String Functions [ 12 Exercises ]
• PL/SQL While Loop [ 21 Exercises ]
• PL/SQL Cursor [ 50 exercises ]
• PL/SQL Trigger [ 11 exercises ]
• PL/SQL Exception Handling [ 16 exercises ]
• PL/SQL Package [ 17 exercises ]
• PL/SQL Object-Oriented Programming [ 6 exercises ]

Follow us on Facebook and Twitter for latest update.

It will be nice if you may share this link in any developer community or anywhere else, from where other developers may find this content. Thanks.

https://www.w3resource.com/plsql-exercises/

• Weekly Trends and Language Statistics

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