|
|
|
Oracle:
Create an abstract data type
Your
assignments to create and abstract datatypes
to represent a person's address, place this
abstract datatypes within a table, and Reich
where East against that table.
SQL> host notepad adt_def.sql
create
or replace type address
as
object
(
street char(20),
city char(20),
state char(2),
zip char(5)
);
/
Step 1
- Enter this type defection into your
database. Your output should look like
this:
SQL>
@adt_def
Type
Created.
Step 2
- Create a table called test_adt with the
following columns, and describe the new
test_adt table:
SQL> host notepad adt_tab.sql
create table
test_adt
(
first_name char(20),
last_name char(20),
full_address address
);
< this
is a pop-up window?>
SQL> desc test_adt
Name
Null? Type
-----------------------------------------
-------- --------
FIRST_NAME
CHAR(20)
LAST_NAME
CHAR(20)
FULL_ADDRESS
ADDRESS
Step 3:
Insert five (5) rows into your test_adt
table. Here is a sample insert statement.
Copy this statement onto your computer and
clone the line, creating five unique rows of
your choosing.
insert into
test_adt
values
(
'Joe',
'Palooka',
address('41 Cherise Ave.',
'Minot',
'ND',
'66654'
)
);
Step 4:
Write a query against the test_adt table to
display all of the rows. Note the
difference in the display of an ADT column
from the display of a regular column in a
traditional table. E-mail the listing to
your instructor.
Step 5
– Write a report to show only the last_name,
zip, and city columns. Hint, the following
query will only display the street address:
select
t.full_address.street,
from
test_adt t
;
ANSWERS:
Step 4:
FIRST_NAME
LAST_NAME
--------------------
--------------------
FULL_ADDRESS(STREET, CITY, STATE,
ZIP)
--------------------------------------------------------------------------------
Joe
Palooka
ADDRESS('41 Cherise Ave. ',
'Minot ', 'ND',
'66654')
Mary
Baker
ADDRESS('3341 Marv Ave. ',
'Pietown ', 'NM',
'75643')
Joe
Smith
ADDRESS('8841 25st Ave.
', 'Bismarck ', 'ND',
'66454')
Sheana
West
ADDRESS('4455333 1st st. ',
'HoHo ', 'NV',
'63344')
Jenny
Salmon
ADDRESS('99272 Nutty Ave. ',
'Wallpaper ', 'NC', '88321')
Step 5:
select
last_name,
t.full_address.zip,
t.full_address.city
from
test_adt t
;
Note: These exercises may use the
pubsdb.sql script that can be
downloaded at this link.
|
|
|
|
|
|
