Oracle Consulting Oracle Training Development

Remote DBA

Remote DBA Plans  

Remote DBA Service

Remote DBA RAC

   
Remote DBA Oracle Home
Remote DBA Oracle Training
Remote DBA SQL Tuning Consulting
Remote DBA Oracle Tuning Consulting
Remote DBA Data Warehouse Consulting
Remote DBA Oracle Project Management
Remote DBA Oracle Security Assessment
Remote DBA Unix Consulting
Burleson Books
Burleson Articles
Burleson Web Courses
Burleson Qualifications
Oracle Links
Remote DBA Oracle Monitoring
Remote DBA Support Benefits
Remote DBA Plans & Prices
Our Automation Strategy
What We Monitor
Oracle Apps Support
Print Our Brochure
Contact Us (e-mail)
Oracle Job Opportunities
Oracle Consulting Prices





   

 

 

 

Remote DBA services

Remote DBA Support

Remote DBA RAC

Remote DBA Reasons

Remote Oracle Tuning

Remote DBA Links

Oracle DBA Support

Oracle DBA Forum

Oracle Disaster

Oracle Training

Oracle Tuning

Oracle Training

 Remote DBA SQL Server

Remote MSSQL Consulting

Oracle DBA Hosting

Oracle License Negotiation

 

 


 

 

 

        
 
     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:
 
Select * from test_adt;
 
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.

For a complete overview of Oracle SQL, see the book "Easy Oracle SQL".  It's only $9.95 and has a downloadable code depot:

http://www.rampant-books.com/book_2005_1_easy_sql.htm

     

Remote DBA Service
 

Oracle Tuning Book

 

Advance SQL Tuning Book 

BC Oracle support

Oracle books by Rampant

Oracle monitoring software

 

 

 

 

 

 

 

 

 

BC Remote Oracle Support

Remote DBA

Remote DBA Services

Copyright © 1996 -  2013 by Burleson. All rights reserved.

Oracle® is the registered trademark of Oracle Corporation.