|
SQL>
SQL> create table emp(
2 emp_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 shortZipCode varchar2(4),
11 area_code varchar2(3),
12 phone varchar2(8),
13 salary number(5,2),
14 birthdate date,
15 startDate date,
16 title varchar2(20),
17 dept_no integer ,
18 mgr integer,
19 region number,
20 division number,
21 total_sales number
22 );
Table created.
SQL> -- emp Table Inserts:
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, birthdate, title)values
2 (1,'Z','Joy','R','1 Ave','New York','NY','12122','2333','212','200-1111','12-nov-1976','President');
1 row created.
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, salary, birthdate, startDate,title, dept_no, mgr, region, division, total_sales)valu
es
2 (2,'X','Lucy','J','1 Street','New York','NY','43552','6633','212','234-4444',7.75,'21-mar-1976','1-feb-1994','Sales Manager',2,1,100,10,40000);
1 row created.
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip,shortZipCode, area_code, phone, salary, birthdate, startDate,title, dept_no, mgr, region, division, total_sales)valu
es
2 (3,'Y','Jordan','E','1 Drive','New York','NY','76822','8763','212','222-2222',7.75,'14-feb-1963','15-mar-1995','Sales Clerk',2,2,100,10,10000);
1 row created.
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip, shortZipCode, area_code, phone, salary, birthdate, startDate, title, dept_no, mgr, region, division, total_sales)va
lues
2 (4,'Washington','Georgia','J','1 13th Street Apt 14','New York','NY','43122','4333','212','340-4365',11.50,'2-jul-1963','21-apr-1994','Designer',1,1,100,10,40000);
1 row created.
SQL> insert into emp(emp_no, lastname, firstname, midinit, street, city, state, zip, shortZipCode, area_code, phone, salary, birthdate, startDate, title, dept_no, mgr, region, division, total_sales)va
lues
2 (5,'Jason','Jordan','J','56 Langer Street','Staten Island','NY','23332','4983','718','777-4365',21.65,'15-may-1958','2-aug-1994','Designer',1,1,100,10,40000);
1 row created.
SQL>
SQL>
SQL> select emp_no,
2 lastname || ', ' || firstname as "Name",
3 salary * 40 as "Weekly Salary"
4 from emp
5 order by "Weekly Salary" desc;
EMP_NO Name Weekly Salary
---------- ------------------------------------- -------------
1 Z, Joy
5 Jason, Jordan 866
4 Washington, Georgia 460
2 X, Lucy 310
3 Y, Jordan 310
5 rows selected.
SQL>
SQL>
SQL> drop table emp;
Table dropped.
|