Chapter 7 and 8

Advanced SQL

This week, we transition from the material in Chapter 7 into the material in Chapter 8. There are still a few more example to demonstrate in class from Chapter 7. This 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 8.

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. 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 is MySQL. In this Module, you will continue writing more sophisticated multiple table queries.material in. 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 code will demonstrate the operation 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

.

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

The ALTER TABLE Statement
Unions in SQL
Intersections in SQL
Differences in SQL
More Multi-Table Joins

We will use the Worker Branch Table to illustrate the examples below. You may use this script to build a sandbox to play in at home or in the Academic Computers in the CBI building. The SQL example commands below will work with tables in the Worker Branch Databas. 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

SQL ALTER TABLE Example

Look at the "Worker" table:

We will continue to use the WorkerBranch SQL file to create the table structure for the relational database, and insert data into the tables of the database. You should follow the examples on your own or during the in class presentation of this

We want to add a column named "DateOfBirth" in the "Worker" table without dropping the table, adding the new attribute, recreating the table and reinserting the data.

To do this, use the following SQL statement:

ALTER TABLE Worker

ADD DateOfBirth date;

Notice that the new column, "DateOfBirth", is of type date and is going to hold a date. The data type specifies what type of data the column can hold. The Date data type enforces a constraint that will validate that a correct date was entered into the attribute.

Add SQL data types to this document

The "Worker" table will have a new structure after the ALTER TABLE query executes:

Change Data Type of an existing attribute example

Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.

To do this use the following SQL statement:

ALTER TABLE Worker

ALTER COLUMN DateOfBirth year;

This statement will change the data type of the attribute "DateOfBirth" from data type DATE to data type YEAR. A YEAR will hold a year in a two-digit or four-digit format.

DROP COLUMN Example

Next, we want to delete the column named "DateOfBirth" in the "Worker" table. This will permanently remove the attribute from the table and any data that is contained in the column

Use the following SQL statement to drop an attribute from a table:

ALTER TABLE Worker

DROP COLUMN DateOfBirth

Highlights and Outcomes This Week

We will practice the above script in class. Additionally 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.

Homework

See the Assignment Listed in this Module of Canvas

Readings

Review Chapter 7 and Chapter 8

Thoroughly read these Chapters to fully understand this material in this Module

Example Exercise

We will continue to use SQL script files that I provide to introduce advance applied sql queries this week. We will expand on SQL script continuing to use multiple table joins and introducing inner queries.

Discussions for the Week

Chapter Discussion 1

Chapter discusses the Cross Join in SQL, what is a CROSS JOIN? Give an example of its syntax in a SQL statement.

Chapter Discussion 2

Chapter discusses the sub query sometimes referred to as an inner query. In our Lab Exercises for this Module, I we will practice using them to implement an Intersection and Difference queries.

In your own word describe what is a subquery?, Additionally, what are its basic characteristics? Put this into your own words do not copy and paste definitions into this response. If you copy and paste material cite the webpage or publication that you are copying from.

Database Project

The Customer Order, Warehouse Shipping Assignment

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

Homework Database Assignments this Week

See Assignment Link in this module for instruction for the assignment in this Module

The Customer Order, Warehouse Shipping Assignment

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 sailor reservation database. The database is the course registration database. The example sub queries in the course registration database are similar to the queries that you will need to create to respond the two advance queries information request for the in class practice assignment