[go: up one dir, main page]
Scribd
Upload
2 views3 pages

Week 3

The document outlines a lab activity for the Advanced Database Management Systems course, focusing on SQL operations related to various types of joins. It includes objectives, tools, and a comprehensive case study with specific questions designed to practice SQL joins using a university database. The marking criteria for the exercise are also provided, emphasizing the correct implementation of joins, query correctness, handling of NULL values, and use of aggregation and filtering.

Uploaded by

saadbadar444x
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
2 views3 pages

Week 3

The document outlines a lab activity for the Advanced Database Management Systems course, focusing on SQL operations related to various types of joins. It includes objectives, tools, and a comprehensive case study with specific questions designed to practice SQL joins using a university database. The marking criteria for the exercise are also provided, emphasizing the correct implementation of joins, query correctness, handling of NULL values, and use of aggregation and filtering.

Uploaded by

saadbadar444x
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
You are on page 1/ 3

Advanced Database Management Systems (CSC-234) Instructor: Hafiz Ahsan Atiq

Week:3 Marks: 10
Date: 24-Feb-2025 Time Allowed: 3 hrs
CLOs PLOs
Objectives: 1, 2, 3, 4 1, 2, 4, 5,
This lab activity aims to enhance students' understanding and practical skills in SQL
operations, focusing on essential commands such as
• Join clause
• Inner Join
• Outer Join
• Cross Join
• Self-Join

Tools:
➢ MySQL (MySQL Workbench), PostgreSQL, Microsoft SQL Server,
➢ Oracle, SQLite, Xampp

Exercise:
Below is a comprehensive case study for practicing SQL joins. Here we have a university
database. This database will contain several tables, each representing different entities, such
as departments, students, courses, and enrollments. The case study will be structured to
encourage the use of various types of joins, including inner joins, left joins, right joins, cross
joins, and self-joins.
2. Case Study Scenarios and Questions

The following questions are designed to help students practice different types of SQL joins:

1.) List all students along with the courses they have enrolled in.

2.) Find the names of students who have taken courses offered by the Computer Science
department.

3.) Show the grades obtained by each student for each course in the Fall semester.

4.) List all students and the courses they have taken, including students who have not enrolled
in any courses.

5.) Display all courses along with the names of students enrolled in them, even if a course has
no students enrolled.

6.) List all enrollments, including details about students even if the course information is not
available.

7.) Generate a list of all possible student-course pairs, even if a student is not enrolled in the
course.

8.) Find pairs of students who are in the same department.

9.) Identify departments where multiple students enrolled in the same year.

10.) List the students who have taken courses outside their department and the grades they
obtained.

11.) Find the department heads whose departments have the most students enrolled in courses
for the Fall semester.

12.) Show the average grade per course for each department.

Marking Criteria:
Criteria Marks Details
Implementation of Joins
4 Correct application of different types of joins in queries.
(Inner, Outer, Cross, Self)
Queries correctly retrieve the required data without syntax or
Correctness of Queries 2
logical errors.
Handling of NULL values Proper use of outer joins to include unmatched data where
1
& Unmatched Data required.
Use of Aggregation & Correct use of COUNT(), AVG(), and filtering conditions in
2
Filtering queries.
Queries are well-structured, efficient, and use appropriate
Optimization & Readability 1
aliases or formatting.

You might also like