Advance SQL Queries Part II Overiew

Chapter 5

Advanced SQL

This week, we continue exploring the SQL commands in Chapter 5. This week includes the SQL UPDATE, DELETE and ALTER TABLE commands. While we review and practice these commands in class, you should begin to study the material in Chapter 6.

As before, if you are participating in a face to face (F2F) section or Hybrid section of this course, it will be extremely important to be present and participate in the in class presentations demonstrating how to write the next categories of queries. Attending class and participating in class activities contributes to success in any course.

We are now in the segment of the class where you are writing multiple table queries using metadata that you can examine in the RDBMS, and actually running these queries in a real time RDBMS, ours RDBMS is MySQL. In this Module, you will continue writing more sophisticated multiple table queries. You will continue to explore writing other types of SQL statements. In this Module, we examine the alter table statement in SQL. It permits the real time redefinition of the structure of a table in a database. Using this command, you will not have to drop a table in order to make changes to its structure. Additionally, we will study using Inner Queries.

Inner Queries will provide the capability to perform Intersect and Difference operations on sets of tuples in two tables or two sets of tuples. This Module will also illustrate code that performs Union operations on two sets of tables. Running the example code in this module will demonstrate the operations to you. We will also continue studying other useful SQL helper commands that we will practice using during our face-to-face sessions this week.

This is week 11 of the semester. I want to continue reinforcing the need to work in several short sessions during the week -- perhaps even daily -- rather than one or two longer sessions at the end of the week

As we have been emphasizing, following this basic outline will contribute to success in the course.

read the weekly overview
read the lecture notes posted in weekly modules
participate in the weekly discussion forums
review the supplement documents and example code posted in the weekly modules
attempt the weekly practice exercise
complete the weekly assignment
complete the weekly quiz

Highlights and Objectives This Week

UPDATE and DELETE Statements
The ALTER TABLE Statement
Unions in SQL
Intersections in SQL
Differences in SQL
More Multi-Table Joins

Last week, we used the Worker Branch Table to illustrate the examples of joining tables together. We used this script to build a sandbox to demonstrate JOIN Operations. This week we will continue to use this table to demonstrate SQL commands. The SQL example commands below will work with tables in the Worker Branch Database. You should follow the examples on your own or during the in class presentation of this topic

WorkerBranch.sql

Highlights and Outcomes This Week

We will practice the above script in class. Additionally, during class, we will practice Union, Intersection, and Difference queries. Difference queries are sometimes referred to as minus queries. At the end of the week you should be able to write your own independent Alter Table queries, Intersection queries, Difference queries, and Union Queries.

In Class Practice Assignments

Practice Assignment: Advanced Sailor Queries

Homework

Assignment: - Customer Order - Warehouse Shipping

Assignment: Student Course Registration

Readings

Continue Reviewing and Studying Chapter 5. Thoroughly read this Chapter to fully understand the material in this Module

Review the example code in the Module

Example Exercise

There are several in class example assignments this week. We will continue to use SQL script described in chapter 5 and in the material that I provide. This week we introduce additional advance applied SQL queries. We will expand on writing SQL script while continuing to use multiple table joins, and now introducing inner queries.

Discussions for the Week

Database Project

Additionally, this week, all of the final project options are released. There will be a 5 week window to complete this assignment. I strongly recommend to take the group project option. This will provide participants the opportunity to understand the dynamics of working on a complex problem in a group. In today’s business world, everyone must learn how to work with other people, even if they do not get along. However, I understand that this is an academic model where everyone may not have synchronizing schedules to practically work collaboratively. This is why there are two options for the final project. There is both a group project option and several individual final project options. If you are working in a group, you get to choose the topic. If you want to work on the final individually, you must choose one of the individual project topics

Weekly Quiz

There is no Quiz scheduled for this Module.

In Class Example Exercise I

In Class Example Exercise II

In Class Example III