Before starting this lab let's assume that you have the following two files Lab1_initialization.sql and 2.PUPBLD.SQL
Lab1_initialization.sql
DROP USER DBM449_USER CASCADE;
CREATE USER DBM449_USER
IDENTIFIED BY DEVRY
DEFAULT TABLESPACE USERS
TEMPORARY TABLESPACE TEMP;
GRANT CONNECT, RESOURCE TO DBM449_USER;
GRANT CREATE MATERIALIZED VIEW, CREATE DATABASE LINNK TO DBM449_USER;
CONN DBM449_USER/DEVRY@db####.WORLD
DROP TABLE CLIENT;
DROP TABLE COURSE;
DROP TABLE COURSE_ACTIVITY;
DROP TABLE CORP_EXTRACT1;
DROP TABLE CORP_EXTRACT2;
CREATE TABLE CLIENT (
CLIENT_NO CHAR(8) PRIMARY KEY,
CLIENT_COMPANY VARCHAR(35) NOT NULL,
CLIENT_NAME VARCHAR(35) NOT NULL,
CLIENT_EMAIL VARCHAR(35),
CLIENT_PROGRAM CHAR(3) NOT NULL,
CLIENT_SCORE NUMBER NOT NULL);
CREATE TABLE COURSE (
COURSE_CODE CHAR(8)PRIMARY KEY,
COURSE_NAME VARCHAR(35) NOT NULL,
COURSE_DATE DATE NOT NULL,
COURSE_INSTRUCTOR VARCHAR(35) NOT NULL,
COURSE_LOCATION VARCHAR(20) NOT NULL);
CREATE TABLE COURSE_ACTIVITY (
ACTIVITY_CODE CHAR(8) PRIMARY KEY,
CLIENT_NO CHAR(8) NOT NULL,
COURSE_CODE CHAR(8) NOT NULL,
GRADE CHAR(1),
INSTR_NOTES VARCHAR (50));
CREATE TABLE CORP_EXTRACT1 (
EXTRACT_NO CHAR(3) PRIMARY KEY,
CLIENT_NO CHAR(8) NOT NULL,
CLIENT_NAME VARCHAR(35) NOT NULL,
CLIENT_EMAIL VARCHAR(35),
CLIENT_COMPANY VARCHAR(35) NOT NULL,
CLIENT_PROGRAM CHAR(3) NOT NULL,
CLIENT_SCORE NUMBER 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);
CREATE TABLE CORP_EXTRACT2 (
EXTRACT_NO NUMBER PRIMARY KEY,
CLIENT_NO CHAR(8) NOT NULL,
CLIENT_NAME VARCHAR(45) NOT NULL,
CLIENT_EMAIL VARCHAR(35),
CLIENT_COMPANY VARCHAR(35) NOT NULL,
CLIENT_PROGRAM CHAR(8) NOT NULL,
CLIENT_SCORE NUMBER 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);
/* Loading data rows */
/* Turn Escape character on */
/* Default escape character "\" */
/* Used to enter special characters (&) */
SET ESCAPE ON;
/* CLIENT rows */
INSERT INTO CLIENT VALUES('C2122542','Bryson, Inc.' ,'Smithson','smithson@bryson.com' ,'DBA',47);
INSERT INTO CLIENT VALUES('C2122356','SuperLoo, Inc.' ,'Flushing','flushing@superloo.com' ,'DBA',38);
INSERT INTO CLIENT VALUES('C2123871','D\&E Supply' ,'Singh' ,'rsingh@desupply.com' ,'EAI',42);
INSERT INTO CLIENT VALUES('C2134452','Gomez Bros.' ,'Ortega' ,'ortega@gomez.com' ,'DBA',39);
INSERT INTO CLIENT VALUES('C2256716','Dome Supply' ,'Smith' ,'smith@dome' ,'ADM',41);
/* COURSE rows */
INSERT INTO COURSE VALUES('DBA12345','DBA 101' ,'03-OCT-2005','Phung' ,'Kaanapali');
INSERT INTO COURSE VALUES('DBA12346','Advanced DBA' ,'23-NOV-2005','Browne' ,'San Mateo');
INSERT INTO COURSE VALUES('EAI12345','EAI Intro' ,'30-NOV-2005','Luss' ,'Danbury');
INSERT INTO COURSE VALUES('DBA12347','DBA 101' ,'08-JAN-2006','Fiorillo' ,'Paramus');
INSERT INTO COURSE VALUES('DBA12348','DBA 101' ,'28-FEB-2006','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','smithson@bryson.com','Bryson, Inc.','DBA',47,'EAI Intro','01-MAR-2007','Luss','Hilo','Enrolled');
INSERT INTO CORP_EXTRACT1 VALUES ('002','C2122356','Flushing','flushing@superloo.com','SuperLoo, Inc.','DBA',38,'DBA 101','03-OCT-2005','Luss','Hilo','Dropped');
INSERT INTO CORP_EXTRACT1 VALUES ('003','C2172249','Bizet','gbizet@bryson.com','Bryson, Inc.','EAI',44,'EAI Intro','01-MAR-2007','Luss','Hilo','Enrolled');
COMMIT;
GRANT SELECT ON COURSE TO PUBLIC;
GRANT SELECT ON CLIENT TO PUBLIC;
GRANT SELECT ON COURSE_ACTIVITY TO PUBLIC;
SET ESCAPE OFF;
2.PUPBLD.SQL
conn system/manager@db1000.world
drop synonym product_user_profile;
create table sqlplus_product_profile as
select product, userid, attribute, scope, numeric_value, char_value,
date_value from product_user_profile;
drop table product_user_profile;
alter table sqlplus_product_profile add (long_value long);
rem +---------------------------------------+
rem | Create SQLPLUS_PRODUCT_PROFILE from scratch |
rem +---------------------------------------+
create table sqlplus_product_profile
(
product varchar2 (30) not null,
userid varchar2 (30),
attribute varchar2 (240),
scope varchar2 (240),
numeric_value decimal (15,2),
char_value varchar2 (240),
date_value date,
long_value long
);
rem
rem Remove SQL*Plus V3 name for sqlplus_product_profile
rem
drop table product_profile;
rem +------------------------------------------------------------------+
rem | Create the view PRODUCT_PRIVS and grant access to that |
rem +------------------------------------------------------------------+
drop view product_privs;
create view product_privs as
select product, userid, attribute, scope,
numeric_value, char_value, date_value, long_value
from sqlplus_product_profile
where userid = 'PUBLIC' or user like userid;
grant select on product_privs to public;
drop public synonym product_profile;
create public synonym product_profile for system.product_privs;
drop synonym product_user_profile;
create synonym product_user_profile for system.sqlplus_product_profile;
drop public synonym product_user_profile;
create public synonym product_user_profile for system.product_privs;
rem +---------------------------------------------------------------+
rem | CONNECT BACK AS THE SYS USER |
rem +---------------------------------------------------------------+
conn sys/oracle@DB###.world as sysdba
Exercise Description
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 email 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.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.Since 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:- Which course(s) a specific client has taken
- What grade(s) a specific client has earned in a specific course
- Which clients did not take any courses
- Which courses were not taken by any client
Here are results from DESCRIBE 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 create a SPOOL session and run your entire script file. Be sure that you execute a SET ECHO ON session command before running the file so that both the query and the output will be captured in the SPOOL file. IMPORTANT: If you are using Windows Vista you will need to create a directory on your C: drive to SPOOL your file into. Vista will not allow you to write a file directly to the C: drive. This will give you two files for the lab. The third file will the be the Lab1 Report document found in Doc Sharing. You will need to put your responses to the questions asked in the various lab steps.Now let's begin. STEP 1: Start Oracle SQL*Plus via Citrix
| 
|
STEP 2: Initialize tables
|
|
STEP 3: Verify your tables
|
|
- CLIENT table - 5 rows
- COURSE table - 5 rows
- COURSE_ACTIVITY table - 6 rows
- CORP_EXTRACT1 table - 3 rows
- CORP_EXTRACT2 table - 0 rows
STEP 4: Using the FULL OUTER JOIN operator
|
|
- 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 FULL OUTER JOIN be helpful to Ann? Place your response in the lab report document for this step.STEP 5: Using the RIGHT OUTER JOIN operator
|
|
- 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
|
|
- 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
|
|
- 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
|
|
- 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
|
|
- 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
|
|
- 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: Using the INTERSECT 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 12: Using the MINUS 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 13: Using subqueries
|
|
- 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.Related Documents
