Advance SQL Queries Part II Overiew
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
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