1. Worksheets

The purpose of a worksheet is to provide a support structure for your study and to provide better coverage of routine introductory exercises prior to completing more challenging homework assignments.

Much of the text comes from questions that arise during the course. The worksheets should make the class easier. Please attempt the worksheet on your own. Answers are provided.

2. Questions and Completion

To receive credit for completing the worksheet, you must submit 'Done' in a textbox on D2L submission folder when you have finished the worksheet.

If you have questions as you go through this worksheet, please feel free to email the instructor.

3. Prerequisites: Oracle SQL Developer

If you have not done so already, download Oracle SQL Developer for your specific OS.
Now connect to the database using the instructions.
Test your connection. If successful, you are good to start.

4. Data Insertion Errors

Create table CSStudent as below:

Create table CSStudent (

studentID number(6),

SSN number(10),

Program varchar(10),

Started number(4),

primary key (studentID)

)

Do not copy/paste the create table statement. Type it in your SQL window.

Insert the following data in CSStudent:

insert into csstudent values ( 90421, 987654321, 'COMP-GAM', 2010 );

insert into csstudent values ( null, 14662, 'COMP-SCI', 2013 );

insert into csstudent values (123123123, 08871, 'INFO-SYS', 2009 );

Q. What is the error and why are you getting it?

5. Database Creation Errors

Download University.sql from here.
Run the entire script.
If you get errors in running your script, then determine the nature of errors.
Fix the script and re-run it.
The fixed script is here.

6. Revising Database

Modify University.sql as follows:

Alter table ‘student’ to add the attribute ‘Photo’.
Alter table ‘course’ and change attribute ‘CourseName’s’ data type to varchar(150).
Alter table ‘enrolled’. Add delete cascade constraints on the Enrolled table

The altered University.sql is available here.