|
SQL>
SQL> CREATE TABLE customer
2 (customer_id NUMBER(7),
3 customer_name VARCHAR2(50),
4 phone VARCHAR2(15),
5 address VARCHAR2(400),
6 city VARCHAR2(35),
7 state VARCHAR2(30),
8 country VARCHAR2(30),
9 zip_code VARCHAR2(10),
10 credit_rating VARCHAR2(9),
11 sales_rep_id NUMBER(7),
12 region_id NUMBER(7),
13 comments VARCHAR2(255),
14 preferred_customer VARCHAR2(1) DEFAULT 'N' NOT NULL,
15 shipping_method VARCHAR2(1) DEFAULT 'M' NOT NULL);
Table created.
SQL>
SQL> INSERT INTO customer VALUES (201, 'Jane', '111-1111', '7 AVE','SAO', NULL, 'BRAZIL', NULL, 'EXCELLENT',12, 2, 'A', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (202, 'Todd', '222-2222', '6 BLVD.','OSAKA', NULL, 'JAPAN', NULL, 'POOR', 14, 4, 'B', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (203, 'Sharon', '333-3333', '1 STREET', 'NEW DELHI', NULL, 'INDIA', NULL, 'GOOD', 14, 4,'C', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (204, 'Hong', '444-4444', '2 STREET','SEATTLE', 'WASHINGTON', 'USA', '98101', 'EXCELLENT',11, 1, NULL, 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (205, 'Anderson','555-5555', '5 ROAD', 'HONG KONG', NULL, NULL,NULL, 'EXCELLENT', 15, 4, NULL, 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (206, 'Bob', '666-6666', '1 ROAD','CANNES', NULL, 'FRANCE', NULL, 'EXCELLENT', 15, 5,'D', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (207, 'Cat', '777-7777', '6 STREET','LAGOS', NULL, 'NIGERIA', NULL, 'GOOD', NULL, 3, NULL,'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (208, 'Doge', '888-8888', '4 RASSE', 'STUTTGART', NULL, 'GERMANY', NULL, 'GOOD', 15, 5,'E', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (209, 'Black', '999-9999', '2 MAR','SAN PEDRO DE MACON''S', NULL, 'DOMINICAN REPUBLIC',NULL, 'EXCELLENT', 11, 1, NULL, 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (210, 'Red', '000-0000', '3 ARO','NOGALES', NULL, 'MEXICO', NULL, 'EXCELLENT', 12, 2,'Customer is difficult to reach by phone. Try mail.','N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (211, 'Ted', '123-1231', '7 MOD', 'PRAGUE',NULL, 'CZECHOSLOVAKIA', NULL, 'EXCELLENT', 15, 5, NULL,'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (212, 'Homas', '124-1234', '5 COR','ALEXANDRIA', NULL, 'EGYPT', NULL, 'EXCELLENT', 13, 3,'F', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (213, 'Look', '555-6281', '4 STREET', 'SAN FRANCISCO', 'CA', 'USA', '94117','EXCELLENT', 11, 1, 'G', 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (214, 'Yellow', '555-7171', '4 STREET','BUFFALO', 'NY', 'USA', '14202', 'POOR', 11, 1, NULL, 'N', 'M');
1 row created.
SQL> INSERT INTO customer VALUES (215, 'White', '337-3892', '6 YEK','SAINT PETERSBURG', NULL, 'RUSSIA', NULL, 'POOR',15, 5, 'T', 'N', 'M');
1 row created.
SQL>
SQL>
SQL>
SQL> SELECT customer_name || CHR(10) ||
2 address || CHR(10) ||
3 customer_csz(customer_id) full_address
4 FROM customer;
FULL_ADDRESS
--------------------------------------------------------------------------------
Jane
7 AVE
SAO, BRAZIL
Todd
6 BLVD.
OSAKA, JAPAN
Sharon
1 STREET
NEW DELHI, INDIA
FULL_ADDRESS
--------------------------------------------------------------------------------
Hong
2 STREET
SEATTLE, WASHINGTON 98101 USA
Anderson
5 ROAD
HONG KONG,
Bob
1 ROAD
FULL_ADDRESS
--------------------------------------------------------------------------------
CANNES, FRANCE
Cat
6 STREET
LAGOS, NIGERIA
Doge
4 RASSE
STUTTGART, GERMANY
Black
FULL_ADDRESS
--------------------------------------------------------------------------------
2 MAR
SAN PEDRO DE MACON'S, DOMINICAN REPUBLIC
Red
3 ARO
NOGALES, MEXICO
Ted
7 MOD
PRAGUE, CZECHOSLOVAKIA
FULL_ADDRESS
--------------------------------------------------------------------------------
Homas
5 COR
ALEXANDRIA, EGYPT
Look
4 STREET
SAN FRANCISCO, CA 94117 USA
Yellow
4 STREET
BUFFALO, NY 14202 USA
FULL_ADDRESS
--------------------------------------------------------------------------------
White
6 YEK
SAINT PETERSBURG, RUSSIA
15 rows selected.
SQL>
SQL>
SQL>
SQL> drop table customer;
Table dropped.
SQL>
|