Scribd
Upload
42 views2 pages

DBMS Lab Assignment-8

The document outlines an assignment for a DBMS lab at Motillal Nehru National Institute of Technology Allahabad, focusing on SQL queries related to two scenarios: a university course enrollment system and a sales management system. It provides relational schemas for both systems and specifies various SQL queries to be written, including listing students, courses, customers, and products with different enrollment and order conditions. The assignment emphasizes the importance of including all entities, even those without corresponding enrollments or orders.

Uploaded by

Aryan Maurya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
42 views2 pages

DBMS Lab Assignment-8

The document outlines an assignment for a DBMS lab at Motillal Nehru National Institute of Technology Allahabad, focusing on SQL queries related to two scenarios: a university course enrollment system and a sales management system. It provides relational schemas for both systems and specifies various SQL queries to be written, including listing students, courses, customers, and products with different enrollment and order conditions. The assignment emphasizes the importance of including all entities, even those without corresponding enrollments or orders.

Uploaded by

Aryan Maurya
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

Computer Science and Engineering Department

Motillal Nehru National Institute of Technology Allahabad


[Link] V Semester
DBMS Lab (CSN15401)
Assignment-8

1. Consider the university course enrollment system with following schema and tables:
Relational schema: Students (StudentID, StudentName, Department)
Table: Students

Relational schema: Courses (CourseID, CourseName, Credits)


Table: Courses

Relational schema: Enrollments (StudentID, CourseID, Grade)


Table: Enrollments

For the above scenario, write SQL queries for the following:
a) List all students who have enrolled in courses, along with their course names and grades.
b) List all students, including those who are not enrolled in any courses. Show the courses
they are enrolled in and their grades if available.
c) List all courses, including those with no students enrolled. Show the names of students
and their grades if available.
d) List all students and all courses, even if no enrollment exists.

2. Consider a sales management system with relational schema given as:


i. Customers (CustomerID, CustomerName, City)
Stores information about customers.
ii. Orders (OrderID, CustomerID, OrderDate)
Stores details about orders placed by customers.
iii. Products (ProductID, ProductName, Price)
Stores details of the products offered by the store.
iv. OrderDetails (OrderID, ProductID, Quantity)
Tracks which products were ordered in each order and their quantities.

For the above relational schemas, write SQL queries for the following:
a) List all customers and the products they have been ordered, including customers who
haven’t placed any orders and products that haven’t been purchased
b) List all products, their prices, and the total number of times each product has been
ordered. Include products that have never been ordered.
c) List all customers, orders, and products (including those with no orders or products)
d) List all orders and their products (including orders without products)

You might also like