/*--------1、改变NLS_LANG 的值,让SELECT TO_CHAR(SALARY,'L99,999.99') FROM HR.EMPLOYEES WHERE ROWNUM SELECTTO_CHAR(SALARY,'L99,999.99')2FROMHR.EMPLOYEES3WHEREROWNUM<5;
TO_CHAR(SALARY,'L99,999.99')----------------------------¥24,000.00¥20,000.00¥20,000.00¥9,000.00SQL>SELECTTO_CHAR(SALARY,'$99,999.99')2FROMHR.EMPLOYEES3WHEREROWNUM<5;
TO_CHAR(SALARY,'$99,999.99')----------------------------$24,000.00$20,000.00$20,000.00$9,000.00/*--说明:对于'$99,999.99'格式符:
L:表示强制显示当地货币符号
$: 表示显示美元符号
9: 表示一个数字
0: 表示强制0显示
.: 表示一个小数点
,: 表示一个千位分隔符
--------------*//*--------2、列出前五位每个员工的名字,工资、涨薪后的的工资(涨幅为8%),以“元”为单位进行四舍五入。---------*/SQL>SELECTFIRST_NAME,SALARY,ROUND(SALARY*1.08)FROMHR.EMPLOYEES2WHEREROWNUM<=5;
FIRST_NAME SALARYROUND(SALARY*1.08)-------------------- ---------- ------------------Steven24000.0025920Neena20000.0021600Lex20000.0021600Alexander9000.009720Bruce6000.006480/*--------3、找出谁是最高领导,将名字按大写形式显示。---------*/SQL>SELECTUPPER(FIRST_NAME||''||LAST_NAME)ASNAME2FROMHR.EMPLOYEES3WHEREMANAGER_IDISNULL;
NAME----------------------------------------------STEVEN KING/*--------4、找出David 的直接领导的名字。---------*/SQL>SELECTUPPER(FIRST_NAME||''||LAST_NAME)ASNAME2FROMHR.EMPLOYEES3WHEREEMPLOYEE_IDIN(4SELECTMANAGER_IDFROMHR.EMPLOYEES5WHEREFIRST_NAME='David'ANDLAST_NAME='Austin');
NAME----------------------------------------------ALEXANDER HUNOLD--或采用以下方法SQL>SELECTUPPER( EMP1.FIRST_NAME||''||EMP1.LAST_NAME)ASNAME2FROMHR.EMPLOYEES EMP1,HR.EMPLOYEES EMP23WHEREEMP1.EMPLOYEE_ID=EMP2.MANAGER_ID4ANDEMP2.FIRST_NAME='David'ANDEMP2.LAST_NAME='Austin';
NAME----------------------------------------------ALEXANDER HUNOLD/*--------5、First_Name 为Alexander,LAST_NAME为Hunold领导谁。(谁向David 报告)。---------*/SQL>SELECTUPPER(FIRST_NAME||''||LAST_NAME)ASNAME2FROMHR.EMPLOYEES3WHEREMANAGER_IDIN(4SELECTEMPLOYEE_IDFROMHR.EMPLOYEES5WHEREFIRST_NAME='Alexander'ANDLAST_NAME='Hunold');
NAME----------------------------------------------BRUCE ERNST
DAVID AUSTIN
VALLI PATABALLA
DIANA LORENTZ--或采用以下方法SQL>SELECTUPPER( EMP1.FIRST_NAME||''||EMP1.LAST_NAME)ASNAME2FROMHR.EMPLOYEES EMP1,HR.EMPLOYEES EMP23WHEREEMP1.MANAGER_ID=EMP2.EMPLOYEE_ID4ANDEMP2.FIRST_NAME='Alexander'ANDEMP2.LAST_NAME='Hunold';
NAME----------------------------------------------BRUCE ERNST
DAVID AUSTIN
VALLI PATABALLA
DIANA LORENTZ/*--------6、哪些员工的工资高于他直接上司的工资,列出员工的名字和工资,上司的名字和工资。---------*/SQL>SELECTE.FIRST_NAME,E.SALARY,M.FIRST_NAME,M.SALARY2FROMEMPLOYEES E,EMPLOYEES M3WHEREE.MANAGER_ID=M.EMPLOYEE_IDANDE.SALARY>M.SALARY;
FIRST_NAME SALARY FIRST_NAME SALARY-------------------- ---------- -------------------- ----------Lisa11500.00Gerald11000.00Ellen11000.00Eleni10500.00--要是只列出员工的名字与工资的话,还可以这样:SQL>SELECTE.FIRST_NAME,E.SALARY2FROMEMPLOYEES EWHEREE.SALARY>3(SELECTM.SALARYFROMEMPLOYEES M4WHEREE.MANAGER_ID=M.EMPLOYEE_ID);
FIRST_NAME SALARY-------------------- ----------Lisa11500.00Ellen11000.00/*--------7、哪些员工和Chen(LAST_NAME)同部门。---------*/SQL>SELECTFIRST_NAMEFROMEMPLOYEES2WHEREDEPARTMENT_IDIN3(SELECTDEPARTMENT_IDFROMEMPLOYEESWHERELAST_NAME='Chen')4ANDLAST_NAME<>'Chen';
FIRST_NAME--------------------Nancy
Daniel
Ismael
Jose Manuel
Luis--或者--SQL>SELECTE1.FIRST_NAMEFROMEMPLOYEES E1,EMPLOYEES E22WHEREE1.DEPARTMENT_ID=E2.DEPARTMENT_ID3ANDE2.LAST_NAME='Chen'ANDE1.LAST_NAME<>'Chen';
FIRST_NAME--------------------Nancy
Daniel
Ismael
Jose Manuel
Luis/*--------8、哪些员工跟De Haan(LAST_NAME)做一样职位。---------*/SQL>SELECTFIRST_NAMEFROMEMPLOYEES2WHEREJOB_IDIN3(SELECTJOB_IDFROMEMPLOYEES4WHERELAST_NAME='De Haan')5ANDLAST_NAME<>'De Haan';
FIRST_NAME--------------------Neena--或者--SQL>SELECTE1.FIRST_NAMEFROMEMPLOYEES E1,EMPLOYEES E22WHEREE1.JOB_ID=E2.JOB_ID3ANDE2.LAST_NAME='De Haan'ANDE1.LAST_NAME<>'De Haan';
FIRST_NAME--------------------Neena/*--------9、哪些员工跟Hall(LAST_NAME)不在同一个部门。---------*/SQL>SELECTFIRST_NAME||''||LAST_NAMEFROMHR.EMPLOYEES2WHEREDEPARTMENT_IDNOTIN(3SELECTDEPARTMENT_IDFROMHR.EMPLOYEES4WHERELAST_NAME='Hall');
FIRST_NAME||''||LAST_NAME----------------------------------------------Steven King
Neena Kochhar
Lex De Haan
Alexander Hunold
Bruce Ernst
David Austin
Valli Pataballa
Diana Lorentz
Nancy Greenberg--...初始有72条数据--或者:SQL>SELECTe1.FIRST_NAMEFROMEMPLOYEES e1,EMPLOYEES e22WHEREe1.DEPARTMENT_ID=e2.DEPARTMENT_ID(+)3ande2.LAST_NAME(+)='Hall'4ande2.LAST_NAMEISNULL;/*-------10、哪些员工跟William(FIRST_NAME)、Smith(LAST_NAME)做不一样的职位。--------*/SQL>SELECTFIRST_NAME||''||LAST_NAMEFROMHR.EMPLOYEES2WHEREJOB_ID<>(SELECTDISTINCTJOB_IDFROMEMPLOYEES3WHEREFIRST_NAME='William'ANDLAST_NAME='Smith');
FIRST_NAME||''||LAST_NAME----------------------------------------------Steven King
Neena Kochhar
Lex De Haan
Alexander Hunold----...初始有77条数据