Note!
Submit your assignment to the Dropbox located on the silver tab at the top of this page.
(See the Syllabus section “Due Dates for Assignments & Exams” for due date information.)
Remember This
Connect to the iLab here.
GENERAL OVERVIEW
Scenario and Summary
My colleague, Ann Henry, operates a regional training center for a commercial software organization. She created a database to track client progress so she can analyze effectiveness of the certification program. CLIENT, COURSE, and COURSE_ACTIVITY are three of the tables in her database. The CLIENT table contains client name, company, client number, pre-test score, certification program and e-mail address. The COURSE_ACTIVITY table contains client number, course code, grade, and instructor notes. The COURSE table contains the course code, course name, instructor, course date, and location. Although she and her instructors enter much of the data themselves, some of the data are extracted from the corporate database and loaded into her tables.
Loading the initial data was easy. For grade entry at the end of each course, a former employee created a data entry form for the instructors. Updating most client information and generating statistics on client progress is not easy because Ann does not know much SQL. For now, she exports the three tables into three spreadsheets. To look up a grade in the COURSE_ACTIVITY spreadsheet, she first has to look up client number in the CLIENT spreadsheet. While this is doable, it is certainly not practical. For statistics, she sorts the data in the COURSE_ACTIVITY spreadsheet using multiple methods to get the numbers she needs.
Every month, Ann’s database tables need to be refreshed to reflect changes in the corporate database. Ann describes this unpleasant task. She manually compares the contents of newly extracted data from corporate to the data in her spreadsheets, copies in the new values, and then replaces the database contents with the new values.
Ann needs our help. Let’s analyze her situation and determine what advanced SQL she could use to make her tasks easier.
iLAB OVERVIEW
Scenario and Summary
The purpose of this lab is to explore join operators to determine which, if any, are appropriate for solving Ann’s business problems, as described in this week’s lecture.
Because Ann prefers to work from Excel spreadsheets, she wants her CLIENT and COURSE_ACTIVITY tables exported into one spreadsheet rather than two, as she is currently using. We need to determine which, if any, of the join operators will provide the data she wants for the single spreadsheet. (Note: we will not perform the export, just determine how to retrieve the necessary data.) Using the spreadsheet, she will be able to determine
1. which course(s) a specific client has taken;
2. what grade(s) a specific client has earned in a specific course;
3. which clients did not take any courses; and
4. which courses were not taken by any client.
The CREATE TABLE statements in the SQL script for this lab (see Doc Sharing) contains commands that show structure (columns and their data types) of tables CLIENT and COURSE_ACTIVITY. You may refer to it while constructing your queries.
For this lab you will be creating several documents. First, write your queries in Notepad to create a script file that will contain all of the queries asked for in lab steps 4 through 13. You can (and should) test each query as you write it to make sure that it works and is returning the correct data. Once you have all of your queries written then run your entire script file, and copy your results. Make sure that both the query and the output are visible in your results. This will give you two files for the lab. The third file will be this Lab1 Report document. You will record your responses to the questions asked in the various lab steps in the DBM449 iLab Results section toward the end of this document.
Now let’s begin.
iLAB STEPS
STEP 1: Connect to MySQL, and Create the Schema
Back to Top
Log into the MySQL iLab environment (you may also choose to install and run MySQL on your own computer in order to complete the labs, if you wish). Create an appropriate schema (e.g., DBM449LAB1), and add the user ID you will use, and permissions.
STEP 2: Initialize Tables
Back to Top
Open a connection to the DBMS using the query editor you will be using to complete the lab. There are a variety of query editors possible, depending on whether you have installed MySQL locally, or are using the DeVry MySQL lab. In general, any query editor will do; confer with your instructor if you have questions on this point.
Issue a use command to begin using the schema created previously (e.g., USE DBM449LAB1), then run the following script to create and populate the tables for this lab. Note that you should omit the Drop Table commands if this is the first time you are running the script:
SET @[email protected]@UNIQUE_CHECKS, UNIQUE_CHECKS=0;
SET @[email protected]@FOREIGN_KEY_CHECKS, FOREIGN_KEY_CHECKS=0;
SET @[email protected]@SQL_MODE, SQL_MODE=’TRADITIONAL,ALLOW_INVALID_DATES’;

— —————————————————–
— Table `CLIENT`
— —————————————————–
DROP TABLE IF EXISTS `CLIENT` ;

CREATE TABLE IF NOT EXISTS `CLIENT` (
`CLIENT_NO` CHAR(8) NOT NULL,
`CLIENT_COMPANY` VARCHAR(35) NOT NULL,
`CLIENT_NAME` VARCHAR(35) NOT NULL,
`CLIENT_EMAIL` VARCHAR(35) NULL,
`CLIENT_PROGRAM` CHAR(3) NOT NULL,
`CLIENT_SCORE` DECIMAL(10,0) NOT NULL,
PRIMARY KEY (`CLIENT_NO`))
ENGINE = InnoDB;

— —————————————————–
— Table `COURSE`
— —————————————————–
DROP TABLE IF EXISTS `COURSE` ;

CREATE TABLE IF NOT EXISTS `COURSE` (
`COURSE_CODE` CHAR(8) NOT NULL,
`COURSE_NAME` VARCHAR(35) NOT NULL,
`COURSE_DATE` DATE NOT NULL,
`COURSE_INSTRUCTOR` VARCHAR(35) NOT NULL,
`COURSE_LOCATION` VARCHAR(20) NOT NULL,
PRIMARY KEY (`COURSE_CODE`))
ENGINE = InnoDB;

— —————————————————–
— Table `COURSE_ACTIVITY`
— —————————————————–
DROP TABLE IF EXISTS `COURSE_ACTIVITY` ;

CREATE TABLE IF NOT EXISTS `COURSE_ACTIVITY` (
`ACTIVITY_CODE` CHAR(8) NOT NULL,
`CLIENT_NO` CHAR(8) NOT NULL,
`COURSE_CODE` CHAR(8) NOT NULL,
`GRADE` CHAR(1) NULL,
`INSTR_NOTES` VARCHAR(50) NULL,
PRIMARY KEY (`ACTIVITY_CODE`))
ENGINE = InnoDB;

— —————————————————–
— Table `CORP_EXTRACT1`
— —————————————————–
DROP TABLE IF EXISTS `CORP_EXTRACT1` ;

CREATE TABLE IF NOT EXISTS `CORP_EXTRACT1` (
`EXTRACT_NO` CHAR(3) NOT NULL,
`CLIENT_NO` CHAR(8) NOT NULL,
`CLIENT_NAME` VARCHAR(35) NOT NULL,
`CLIENT_EMAIL` VARCHAR(35) NULL,
`CLIENT_COMPANY` VARCHAR(35) NOT NULL,
`CLIENT_PROGRAM` CHAR(3) NOT NULL,
`CLIENT_SCORE` DECIMAL(10,0) NOT NULL,
`COURSE_NAME` VARCHAR(35) NOT NULL,
`COURSE_DATE` DATE NOT NULL,
`COURSE_INSTRUCTOR` VARCHAR(35) NOT NULL,
`COURSE_LOCATION` VARCHAR(20) NOT NULL,
`COURSE_STATUS` VARCHAR(10) NOT NULL,
PRIMARY KEY (`EXTRACT_NO`))
ENGINE = InnoDB;

SET [email protected]_SQL_MODE;
SET [email protected]_FOREIGN_KEY_CHECKS;
SET [email protected]_UNIQUE_CHECKS;
— CLIENT rows —
INSERT INTO CLIENT VALUES(‘C2122542′,’Bryson, Inc.’ ,’Smithson’,’[email protected]’ ,’DBA’,47);
INSERT INTO CLIENT VALUES(‘C2122356′,’SuperLoo, Inc.’ ,’Flushing’,’[email protected]’ ,’DBA’,38);
INSERT INTO CLIENT VALUES(‘C2123871′,’D\&E Supply’ ,’Singh’ ,’[email protected]’ ,’EAI’,42);
INSERT INTO CLIENT VALUES(‘C2134452′,’Gomez Bros.’ ,’Ortega’ ,’[email protected]’ ,’DBA’,39);
INSERT INTO CLIENT VALUES(‘C2256716′,’Dome Supply’ ,’Smith’ ,’[email protected]’ ,’ADM’,41);

— COURSE rows —
INSERT INTO COURSE VALUES(‘DBA12345′,’DBA 101′ ,’2005-10-03′,’Phung’ ,’Kaanapali’);
INSERT INTO COURSE VALUES(‘DBA12346′,’Advanced DBA’ ,’2005-11-23′,’Browne’ ,’San Mateo’);
INSERT INTO COURSE VALUES(‘EAI12345′,’EAI Intro’ ,’2005-11-30′,’Luss’ ,’Danbury’);
INSERT INTO COURSE VALUES(‘DBA12347′,’DBA 101′ ,’2006-01-08′,’Fiorillo’ ,’Paramus’);
INSERT INTO COURSE VALUES(‘DBA12348′,’DBA 101′ ,’2006-02-28′,’Majmundar’ ,’Racine’);

— COURSE ACTIVITY rows —
INSERT INTO COURSE_ACTIVITY VALUES(‘A0000001′,’C2122542′,’DBA12345′,’A’,NULL);
INSERT INTO COURSE_ACTIVITY VALUES(‘A0000002′,’C2122356′,’DBA12347′,’F’,NULL);
INSERT INTO COURSE_ACTIVITY VALUES(‘A0000003′,’C2134452′,’DBA12345′,’B’,NULL);
INSERT INTO COURSE_ACTIVITY VALUES(‘A0000004′,’C2122542′,’DBA12346′,’A’,NULL);
INSERT INTO COURSE_ACTIVITY VALUES(‘A0000005′,’C2123871′,’EAI12345′,’A’,NULL);
INSERT INTO COURSE_ACTIVITY VALUES(‘A0000006′,’C2122356′,’DBA12345’,NULL,NULL);

— CORP_EXTRACT1 rows —
INSERT INTO CORP_EXTRACT1 VALUES (‘001′,’C2122542′,’Smithson’,’[email protected]’,’Bryson, Inc.’,’DBA’,47,’EAI Intro’,’2007-03-01′,’Luss’,’Hilo’,’Enrolled’);
INSERT INTO CORP_EXTRACT1 VALUES (‘002′,’C2122356′,’Flushing’,’[email protected]’,’SuperLoo, Inc.’,’DBA’,38,’DBA 101′,’2005-10-03′,’Luss’,’Hilo’,’Dropped’);
INSERT INTO CORP_EXTRACT1 VALUES (‘003′,’C2172249′,’Bizet’,’[email protected]’,’Bryson, Inc.’,’EAI’,44,’EAI Intro’,’2007-03-01′,’Luss’,’Hilo’,’Enrolled’);

Once the script has finished you will be ready to start your lab.
STEP 3: Verify Your Tables
Back to Top
You want to verify that everything completed successfully. To do this, execute a SELECT * FROM TAB statement to make sure all five tables were created, and then you can execute a SELECT COUNT(*) FROM statement using each of the table names. You should find the following numbers of records for each table.
• CLIENT table—5 rows
• COURSE table—5 rows
• COURSE_ACTIVITY table—6 rows
• CORP_EXTRACT1 table—3 rows
• CORP_EXTRACT2 table—0 rows
NOTE: In the following steps when writing your queries be sure to list the tables in the FROM clause in the same order they are listed in the instructions. Reversing the order of the tables in the FROM clause will produce an incorrect results set
STEP 4: Simulating the FULL OUTER JOIN Operator
Back to Top
Although all modern relational Database Management Systems implement the JOIN operator, not all support the FULL OUTER JOIN clause. MySQL does not directly support FULL OUTER JOIN. However, it is possible to emulate this feature. First, determine EXACTLY what results should be returned by a logical FULL OUTER JOIN on the CLIENT and COURSE_ACTIVITY tables. Then, construct a logically equivalent SQL query to provide identical results:
• Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.
Will the equivalent of a FULL OUTER JOIN be helpful to Ann? Why or why not? Place your response in the lab report document for this step.
STEP 5: Using the RIGHT OUTER JOIN Operator
Back to Top
Join the CLIENT and COURSE_ACTIVITY tables using a RIGHT OUTER JOIN.
• Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.
Will the RIGHT OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.
STEP 6: Using the LEFT OUTER JOIN Operator
Back to Top
Join the CLIENT and COURSE_ACTIVITY tables using a LEFT OUTER JOIN.
• Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.
Will the LEFT OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.
STEP 7: Using the NATURAL JOIN Operator
Back to Top
Join the CLIENT and COURSE_ACTIVITY tables using a NATURAL JOIN.
• Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.
• Will the NATURAL JOIN be helpful to Ann? Place your response in the lab report document for this step.
STEP 8: Using the INNER JOIN Operator
Back to Top
Join the CLIENT and COURSE_ACTIVITY tables using an INNER JOIN.
• Write and execute the SQL statement that produces the client number and name, course code and grade that the client got in this course.
Will the INNER JOIN be helpful to Ann? Place your response in the lab report document for this step.
Write a conclusion based on the five steps above, which join—if any—should Ann use to populate the spreadsheet that can answer her questions.
STEP 9: Using the UNION Operator
Back to Top
Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the UNION operator.
• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.
Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.
STEP 10: Using the UNION ALL Operator
Back to Top
Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using the UNION ALL operator.
• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.
Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.
STEP 11: Simulating the INTERSECT Operator
Back to Top
MySQL does not implement the INTERSECT relational set operator. However, it is possible to emulate this feature. First, determine EXACTLY what results should be returned by a logical INTERSECT between the CORP_EXTRACT1 table and tables which follow. Then, construct a logically equivalent SQL query to provide identical results.
• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.
Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.
STEP 12: Simulating the MINUS Operator
Back to Top
MySQL does not implement the MINUS relational set operator. However, it is possible to emulate this feature. First, determine EXACTLY what results should be returned by a logical MINUS between Ann’s tables and the CORP_EXTRACT1 table. Then, construct a logically equivalent SQL query to provide identical results: Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.
Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.
STEP 13: Using Subqueries
Back to Top
Examine the clients and courses in Ann’s tables and the CORP_EXTRACT1 table using a subquery with NOT IN operator.
• Write and execute the SQL statement that examines client numbers in CLIENT and CORP_EXTRACT1.
• Write and execute the SQL statement that examines client numbers in COURSE_ACTIVITY and CORP_EXTRACT1.
• Write and execute the SQL statement that examines course names in COURSE and CORP_EXTRACT1.
Which of these statements, if any, will be helpful to Ann? Place your response in the lab report document for this step.
Deliverables
What is Due?
Submit the output from your query editor (be sure to show both the SQL commands and the results generated) along with this completed Lab 1 Report to the Dropbox. Your report should contain copies of each query and result set outlined in the lab along with the requested explanation of whether or not it satisfied the business requirement outlined for that particular section of the lab.
Note!
Submit your assignment to the Dropbox located on the silver tab at the top of this page.
See the Syllabus section “Due Dates for Assignments & Exams” for due date information.
DBM449 iLab Results
NOTE: Be sure that you have also recorded the complete output of your lab queries and subsequent result sets for the lab. That file should be submitted along with this document in an individual ZIPPED file to the appropriate Dropbox for this lab.
Step 4—Using the FULL OUTER JOIN operator

Step 5—Using the RIGHT OUTER JOIN operator

Step 6—Using the LEFT OUTER JOIN operator

Step 7—Using the NATURAL JOIN operator

Step 8—Using the INNER JOIN operator

Step 9—Using the UNION operator

Step 10—Using the UNION ALL operator

Step 11—Using the INTERSECT operator

 
Step 12 – Using the MINUS operator

Step 13 – Using subqueries