??????????????????????????
????--?????????
create table  plspl_company_info(
empno number(5) not null??
ename varchar2(10) not null??
job varchar2(10)??
manager number(5)??
hiredate date??
sal  number(5)??
comm  number(5)??
deptno number(3)
);
insert into plspl_company_info values (7369?? 'SMITH'?? 'CLERK'?? 7902?? date '1980-12-17'?? 800?? NULL?? 10);
insert into plspl_company_info values (7293?? 'ALLEN'?? 'SALESMAN'?? 7689?? date '1981-03-27'?? 1867?? NULL?? 20);
insert into plspl_company_info values (7562?? 'JAMES'?? 'SALESMAN'?? 7689?? date '1981-09-11'?? 1796?? NULL?? 20);
insert into plspl_company_info values (7936?? 'JONES'?? 'ANAYST'?? 7656?? date '1980-09-01'?? 3250?? NULL?? 30);
insert into plspl_company_info values (7688?? 'WEST'?? 'MANAGER'?? 7839?? date '1981-02-28'?? 2985?? 900?? 40);
insert into plspl_company_info values (7499?? 'PAUL'?? 'MANAGER'?? 7839?? date '1980-03-26'?? 3600?? NULL?? 40);
insert into plspl_company_info values (7778?? 'FORD'?? 'CLERK'?? 7902?? date '1987-04-17'?? 960?? NULL?? 10);
insert into plspl_company_info values (7289?? 'ADAMS'?? 'SALESMAN'?? 7689?? date '1980-01-09'?? 1956?? NULL?? 20);
insert into plspl_company_info values (7531?? 'MATIN'?? 'SALESMAN'?? 7689?? date '1980-05-14'?? 1906?? NULL?? 20);
insert into plspl_company_info values (7916?? 'KING'?? 'ANAYST'?? 7656?? date '1982-06-04'?? 2864?? NULL?? 30);
insert into plspl_company_info values (7365?? 'BLKAE'?? 'CLERK'?? 7902?? date '1981-09-11'?? 1200?? 1100?? 10);
insert into plspl_company_info values (7784?? 'CHRIS'?? 'CLERK'?? 7902?? date '1981-07-16'?? 1376?? NULL?? 10);
select * from plspl_company_info;
??????н????
SQL>
Table created
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
1 row inserted
EMPNO ENAME JOB MANAGER HIREDATE SAL COMM DEPTNO
------ ---------- ---------- ------- ----------- ------ ------ ------
7369 SMITH CLERK 7902 1980/12/17 800 10
7293 ALLEN SALESMAN 7689 1981/3/27 1867 20
7562 JAMES SALESMAN 7689 1981/9/11 1796 20
7936 JONES ANAYST 7656 1980/9/1 3250 30
7688 WEST MANAGER 7839 1981/2/28 2985 900 40
7499 PAUL MANAGER 7839 1980/3/26 3600 40
7778 FORD CLERK 7902 1987/4/17 960 10
7289 ADAMS SALESMAN 7689 1980/1/9 1956 20
7531 MATIN SALESMAN 7689 1980/5/14 1906 20
7916 KING ANAYST 7656 1982/6/4 2864 30
7365 BLKAE CLERK 7902 1981/9/11 1200 1100 10
7784 CHRIS CLERK 7902 1981/7/16 1376 10
12 rows selected
SQL>
???????
????1?????????30?????????????
????select * from plspl_company_info where deptno = 30;
????2???г????а????(CLERK)?????????????????
????select ename?? empno ?? deptno from  plspl_company_info where job = 'CLERK';
????3?????н????????60%?????
????select * from plspl_company_info where comm > sal*0.6 ;
????4?????????10?????о????MANAGER???????20?????а??????CLERK??
????select * from plspl_company_info where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK') ;
????5?????????10?????о????MANAGER???????20?????а??????CLERK????????????????в???????????н?????????2000??????????????????
????select *
????from plspl_company_info
????where (deptno = 10 and job = 'MANAGER') or (deptno = 20 and job = 'CLERK') or ((job not in('MANAGER'?? 'CLERK')) and sal >= 2000) ;
????6???????????????????????
????select distinct job from plspl_company_info where comm is not null ;
????7?????????????????????????100?????
????select * from plspl_company_info where (comm is  null) or comm < 100 ;
????8?????????“R”?????????
????select * from plspl_company_info where ename not like '%A%';
????9???????????ε??κ?λ?e???'A'??????????????????????????????????????????????????????????????????絽?????????????????????????λ????
????select * from plspl_company_info where ename like '%A%' order by sal DESC?? hiredate?? job;