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