Advance Query III Overview-2

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 WorkerBranch.sql The ALTER TABLE statement in SQL is a DDL statement used to alter the structure of an existing table while the table is actively in use. It is used to add, delete, or modify columns in an existing table in real time. Below are a few examples of the SQL ALTER TABLE Syntax: As you will see, even though SQL is a standardized language, there are a few subtleties in the syntax of the commands between the developers of the software. To add a column in a table, use the following syntax: ALTER TABLE table_name ADD column_name datatype To delete a column in a table, use the following syntax (Some database systems don't allow deleting a column): ALTER TABLE table_name DROP COLUMN column_name To change the data type of a column in a table, use the following syntax: Using MS SQL Server or MS Access: ALTER TABLE table_name ALTER COLUMN column_name datatype Using My SQL or Oracle: ALTER TABLE table_name MODIFY COLUMN column_name datatype Oracle 10G and later: ALTER TABLE table_name MODIFY column_name datatype 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 In Class Practice: Delete and Update Little League Football In Class Practice: Alter Table Statements 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 for “Advance SQL Queries Part II” 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 This week there is only one discussion. It is an ethical discussion on Ethics in Information Control 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. Important Reminder The correct process for responding to discussions is to first post your own reply to the discussion topic. Other participant’s replies will not be visible until you post your first individual reply. Second, you must post a brief reply to at least two other students’ replies. This along with a relevant individual reply will give you full credit in discussion assignments In Class Example Exercise I This in class practice will consist of following along and participating in class during the demonstration of the SQL commands for the Worker Branch Database. In Class Example Exercise II This in class practice will consist of reviewing a database that parallels the structure of the homework assignment database. The homework assignment database is the course registration database. The example sub queries in the request in class practice assignment are similar to the queries that you will need to create to respond the two advance queries information request for the homework assignment, the course registration database In Class Example III That is a series of UPDATE and DELETE commands that are associated with our ongoing Little league Football Database