|
SQL>
SQL>
SQL> create table customer(
2 cust_no integer primary key
3 ,lastname varchar2(20) not null
4 ,firstname varchar2(15) not null
5 ,midinit varchar2(1)
6 ,street varchar2(30)
7 ,city varchar2(20)
8 ,state varchar2(2)
9 ,zip varchar2(5)
10 ,zip_4 varchar2(4)
11 ,area_code varchar2(3)
12 ,phone varchar2(8)
13 ,company_name varchar2(50)
14 );
Table created.
SQL>
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(1, 'Allen', 'Joe','J','10 Ave','London','CA','11111','1111','111', '111-1111','Big Company');
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(2,'Ward','Sue','W','20 Ave','New York','NY','44444','4444','444', '436-4444','B Company');
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(3,'Jason','Pure','J','50 St','Longli','CA','55555','5555','555', '234-4444','C Company');
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(4,'Bird','Jill', null,'30 St','Pais','NY','22222','2222','222', '634-7733','D Company');
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(5,'Hill','Carl','H','19 Drive','A Town','CA','66666','6566','666', '243-4243','E Company');
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(6,'Peter','Yari','P','38 Ave','Small City','NY','77777','7777','777', '454-5443','F Inc');
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(7,'Joe','Paula','J','78 St. Apt 3A','Queen City','NY','32322','2323','888', '664-4333','E Inc');
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(8,'Chili','Steve','C','38 Ave Apt 62','Mili','CA','88888','8888','787', '456-4566','G Inc');
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(9,'Mona','Joe','M','930 Ave933','Kansas City','MO','12345','1234','412', '456-4563','H Inc');
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(10,'Hack','Kisi','H','Kings Rd','Bellmore','NY','54321','3898','516', '767-5677','I Inc');
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(11,'Bill','Jose','B','12 Giant Rd.','Newton','NJ','23454','1234','958', '123-7367','J Associates');
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(12,'Taker','Lawrence','T','1 Sask Rd.','Camp','NJ','19191','3298','928', '123-7384','K Company');
1 row created.
SQL> insert into customer(cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(13,'Richer','Doris','R','213 Easy Street','WarPease','RI','34343','2112','501', '123-7384','L Inc');
1 row created.
SQL> insert into customer( cust_no,lastname,firstname,midinit,street,city,state,zip,zip_4,area_code,phone,company_name)
2 values(14,'Pete','Doris','P','9 Ave','New York','NY','45454','4222','112', '123-1234','M Company');
1 row created.
SQL>
SQL> select state from customer;
ST
--
CA
NY
CA
NY
CA
NY
NY
CA
MO
NY
NJ
NJ
RI
NY
14 rows selected.
SQL>
SQL> SELECT
2 SUM( decode ( state, 'NJ' , 1 , 0) ) as nj,
3 SUM( decode ( state, 'NY' , 1 , 0) ) as ny,
4 SUM( decode ( state, 'RI' , 1 , 0) ) as ri,
5 SUM( decode ( state, 'NJ', 0,
6 'NY', 0,
7 'RI', 0,
8 1) ) AS other,
9 COUNT(*) AS total
10 FROM customer;
NJ NY RI OTHER TOTAL
---------- ---------- ---------- ---------- ----------
2 6 1 5 14
1 row selected.
SQL>
SQL> drop table customer;
Table dropped.
SQL>
SQL> --
|