DeVry DBM449 Week 4 Lab - 28630

Solution Posted by
knoppolis

knoppolis

Rating : No Rating
Solution Detail
Price: $25.00
  • From: Computer Science,
  • Posted on: Wed 16 Oct, 2013
  • Request id: None
  • Purchased: 0 time(s)
  • Average Rating: No rating
Request Description

STEP 1: Create a table with a column data type

Modify the design of the COURSE table created in iLab 1 to incorporate the use of the column abstract data type.

1.  Write and execute the SQL to create a single object type called COURSE_OBJ1 that contains both the

attributes course code and course name. Remember that with abstract objects you must use the / after the

CREATE statement to execute it.

2.  Next, write and execute the SQL to create a table called NEW_COURSE1 that contains COURSE_OBJ1

along with the original attributes from the original COURSE table. Keep in mind what attributes the new

object type COURSE_OBJ1 contains. Your table should have a total of 4 individual columns when finished.

3.  Using the data from the LAB4_DATA file create and execute the insert statements to load the new table

NEW_COURSE1. SUGGESTION: Using the Lab4_data file create a script file of your insert statements and

then run the script file. Remember that you will need enclose some of the data in single quotes depending

on if it is character, date, or numeric data.

4.  Run DESCRIBE command to describe structure of table NEW_COURSE1.

5.  SET DESCRIBE DEPTH 2 and run DESCRIBE NEW_COURSE1 again.

6.  Execute a SELECT statement to query the data from the new table (DO NOT use a SELECT * type query).

Use the COLUMN column_name FORMAT A## session command to format columns within the table to

keep the result set data from wrapping around. Be sure that you properly display data inside the object

column. (HINT: When querying attributes of an abstract data type, you must use a correlation variable for

the table.)

STEP 2: Create an object table with a row data type

27/05/2013 Advanced Topics in DB w/Lab

www.devryu.net/re/DotNextLaunch.asp?courseid=8124573&userid=6028517&sessionid=5ee158111c&tabid=U/crofokXtwFEysM4xSEoiL/zkTMjh/6Bq8/wLjcnbnpv… 2/2

Create a second COURSE table, this time as an object table using the row abstract data type.

1. Write and execute the SQL to create an object called COURSE_OBJ2 that contains the attributes course

code, course name, course date, instructor, and location.

2. Write and execute the SQL to create a table called NEW_COURSE2 with a single column defined using the

COURSE_OBJ2 object.

3. Using the data from the LAB4_DATA file create execute the insert statements to load the new table

NEW_COURSE2.

4. Execute a SELECT statement to query the data from the new table (DO NOT use a SELECT * type query).

STEP 3: Create a Varying Array

Modify the design of the CLIENT table created in iLab 1 to incorporate the use of the Varying Array.

1. Write and execute the SQL to create a Varying Array to represent the phone contact information for the

client (up to 3 phone numbers). Name the varying array as PHONE_LIST.

2. Write and execute the SQL to create a table called NEW_CLIENT that contains the attributes that the

original CLIENT table contained plus the phone list array.

3. Using the data from the LAB4_DATA file create execute the insert statements to load the new table

NEW_CLIENT.

4.  Execute a SELECT statement to query the data from the CLIENT_NO and CLIENT_NAME columns along

with the data in the column containing the phone number Varray (You cannot use a SELECT * type query

for this step).

Solution Description

spool v:\dbm449\Week4_Lab4.sql

 

@v:\dbm449\pupbld.sql

@v:\dbm449\Lab1_init.sql

 

set echo on

set pagesize 999

set linesize 999

 

 

/* **************************************************

Lab Number: 4

Class:

Date Written:

Author:

************************************************** */

 

--This will need to be edited to match your login

conn dbm449_user/devry@db????.world

 

--Step 01 Create a table with a column data type

 

----01

---drop type course_obj1;

create type course_obj1 as object

(

course_codechar(8),

course_namevarchar2(35)

);

/

 

----02

---drop table new_course1;

create table new_course1

(

c_obj1course_obj1,

course_datedatenot null,

course_instructorvarchar2(35)not null,

course_locationvarchar2(20)not null

)

;

 

----03

---Data Insert Script File

@v:\dbm449\new_course1_

Attachments
Week4Code.sql
Week4Code.sql
Week 4 Lab.pdf
Week 4 Lab.pdf