Title: Week 12
Course: Introduction to Database Management Systems

DML (Subqueries, JOINs & Advanced Topics)

The aim of the topic is to expose you to advanced concepts for data filtering by exploring how to write and execute SQL statements that embrace subqueries and multi-table querying with or without the use of JOINs. It also explores the process of combining table results using Union, Intersect and Except set operations.

Learning Outcomes

At the end of the week, you will be able to:
1. Write and execute SQL statements containing subqueries.
2. Describe and perform multi-table querying with or without the use of JOINs.
3. Describe the process of combining table results using Union, Intersect and Except.

Over to you
This section involves you looking through the learning resources and summarizing the chapters provided. Thereafter, you will work on the e-tivities to demonstrate whether you understood and internalized the content.

Pre-Topic
You should have successfully completed week 11 activities

Due date Tuesday 23:59

FACE-TO-FACE

- Highlight concepts related to DML.
- Work on a group discussion on a Mini Case study inline with DML

Task
In reference to records inserted in tables in week 10, demonstrate the application of subqueries and JOINs on the data as guided by the instructor

E-TIVITIES

E-tivity 12.1 (Mandatory)

The purpose of this e-tivity is to explore advanced Data Manipulation Language concepts. For this e-tivity, you will be required as an individual to perform the tasks listed below based on the running case.

Task 1
Keeping in mind e-tivity 10.1 from the previous week that was based on the background of the running case that inserted records in all table of the database and in reference to week 11 exercises that introduced data filtering concepts, you are now required to go through chapter 16 of Database Design course textbook on SQL Data Manipulation Language and chapter 9 of the Relational Database Concepts course textbook on The SQL Data Manipulation Language (DML). Additionally, watch the following videos.

Sub Querying

Multitable Querying without JOINS

Multitable Querying with JOINS

Set Function

Capture critical points discovered from the learning resources based on what you understood and gained from the readings and the video.

Task 2

Based on the readings and video covered in Task 1 and in relation to the Running Case, write and execute SQL statements to:

- Display all records from all the tables without using JOINs.
- Repeat the previous tasks using JOINs.
- On your own, demonstrate the use of Union, Intersect and Except on the data in the database. Be sure to explain and justify your demonstration.

Capture the SQL statements executed plus the screenshots of the outcome in an MS-Word document.

Due date Sunday 23:59
Estimated study time: 6.5 hours

Keep a copy of your work in your e-portfolio folder.

ASSESSMENT