This Question is from DATABASE course. Copy and run the following code and then
ID: 3803003 • Letter: T
Question
This Question is from DATABASE course.
Copy and run the following code and then answer the questions that follow:
CREATE TYPE address_t AS OBJECT (
street VARCHAR2(30),
city VARCHAR2(20),
state CHAR(2),
zip CHAR(5) );
/
CREATE TYPE address_tab IS TABLE OF address_t;
/
CREATE TABLE customers (
custid NUMBER,
address address_tab )
NESTED TABLE address STORE AS customer_addresses;
/
INSERT INTO customers VALUES (1,
address_tab(
address_t('62 Oak St.', 'Redwood Shores', 'CA', '94065'),
address_t('33 Union St.', 'Mill Valley', 'CA', '90952')
) );
/
INSERT INTO customers VALUES (2,
address_tab(
address_t('2 Queens Rd.', 'Poway', 'CA', '88065'),
address_t('129 Beach Rd.', 'Portola', 'CA', '91132'),
address_t('81 Federation St.', 'Plymouth', 'CA', '71532')
) );
/
INSERT INTO customers VALUES (3,
address_tab(
address_t('72 Gilmour Rd.', 'Placentia', 'CA', '44065')
) );
2.1 Write a query to display the street, city, state, and zip for the customers whose id is 1. That is, your query should display the following:
STREET CITY STATE ZIP
------------------------------------------------------------
62 Oak St. Redwood Shores CA 94065
33 Union St. Mill Valley CA 90952
2.2 Write an update query to update the street of the first address of the customer whose id is 1 from street 62 Oak St. to 63 Oak St.
Explanation / Answer
2.1)SELECT a.* FROM customers c,table (c.address) a WHERE c.custid=1;
The above query willl give all the fields street,city.state,zip which are in address field whose customer id is1.
2.2)
UPDATE a
SET a.street="63 Oak St."
FROM customers c,table (c.address) a WHERE c.custid=1;
The above query to update the street of the first address of the customer whose id is 1 from street 62 Oak St. to 63 Oak St.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.