【正文】 60)。 查詢平均工資大于60號部門最高工資的部門的department_id,平均工資SELECT DEPARTMENT_ID,AVG(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_IDHAVING AVG(SALARY)(SELECT MAX(SALARY) FROM EMPLOYEES WHERE DEPARTMENT_ID=60)。FMYYYYMMDD39。，必須與主查詢中條件的列的個數(shù)與類型一致*/查詢工資大于公司平均工資的員工的last_name,department_id,salarySELECT LAST_NAME,DEPARTMENT_ID,SALARYFROM EMPLOYEESWHERE SALARY(SELECT AVG(SALARY) FROM EMPLOYEES)。查詢哪些員工的工資大于104號員工的工資，顯示這些員工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY(SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID=104)。/* BY子句*/子查詢（嵌套查詢）：查詢與144號員工在同一個部門工作的員工的last_name,department_idSELECT DEPARTMENT_IDFROM EMPLOYEESWHERE EMPLOYEE_ID=144。查詢?nèi)藬?shù)大于或等于3人的部門的department_id與人數(shù)SELECT DEPARTMENT_ID,COUNT(*)FROM EMPLOYEESGROUP BY DEPARTMENT_IDHAVING COUNT(*)=3。YYYY39。YYYY39。YYYY39。GROUP BY JOB_IDHAVING MAX(SALARY)=6000。查詢最高工資大于6000的職位與最高工資，并且JOB_ID中要包含REPSELECT JOB_ID,MAX(SALARY)FROM EMPLOYEESWHERE JOB_ID LIKE 39。/*WHERE與HAVING的區(qū)別：，HAVING過濾的是分組的結(jié)果，HAVING可以使用組函數(shù)*/查詢平均工資大于10000的部門編號與平均工資，結(jié)果按工資降序排序SELECT DEPARTMENT_ID,AVG(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_IDHAVING AVG(SALARY)10000ORDER BY AVG(SALARY) DESC。組函數(shù)與NULL值:所有組函數(shù)在進(jìn)行計算時會自動忽略NULL值SELECT AVG(NVL(COMMISSION_PCT,0))FROM EMPLOYEES。查詢每個部門的人數(shù)，顯示department_id,department_name與人數(shù)SELECT ,COUNT(*)FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON =GROUP BY ,ORDER BY 1 ASC。查詢每個部門最高的工資SELECT MAX(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_ID。SELECT DEPARTMENT_ID,MAX(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_ID。統(tǒng)計，報表。199739。YYYY39。3112月9739。)。,39。SELECT COUNT(*)FROM EMPLOYEESWHERE HIRE_DATETO_DATE(39。),FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON = JOIN LOCATIONS LOC ON = WHERE =90。查詢所有90號部門員工的last_name,salary(格式為：￥50,),citySELECT ,TO_CHAR(,39。按薪水降序排序數(shù)據(jù)。Toronto39。Toronto39。%A%39。SELECT COUNT(COMMISSION_PCT)返回指定列中不為NULL的值的個數(shù)FROM EMPLOYEES。求50號部門的平均工資，總工資，最高工資與最低工資SELECT AVG(SALARY),SUM(SALARY),MAX(SALARY),MIN(SALARY)FROM EMPLOYEESWHERE DEPARTMENT_ID=50。SELECT MAX(HIRE_DATE),MIN(HIRE_DATE)FROM EMPLOYEES。AVG():求平均值SELECT AVG(SALARY)FROM EMPLOYEES。右外連接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE =*。右外連接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE (+)=。SELECT ,FROM EMPLOYEES EMP FULL OUTER JOIN DEPARTMENTS DEPTON =。內(nèi)連接：查詢出滿足連接條件的數(shù)據(jù)外連接：查詢出滿足連接條件與不滿足連接條件的數(shù),左外連接、右外連接查詢所有員工的last_name,department_nameSELECT ,FROM EMPLOYEES EMP LEFT JOIN DEPARTMENTS DEPTON =。查詢員工的last_name,job_title,department_name,citySELECT ,FROM EMPLOYEES EMP,DEPARTMENTS DEPT,LOCATIONS LOC,JOBS JOBWHERE = AND = AND =。SELECT ,FROM EMPLOYEES EMP INNER JOIN DEPARTMENTS DEPT ON =WHERE LIKE 39。查詢員工的last_name,job_title,department_name,citySELECT ,FROM EMPLOYEES EMP,DEPARTMENTS DEPT,LOCATIONS LOC,JOBS JOBWHERE = AND = AND =。YYYYMMDD39。END AS 獎金FROM EMPLOYEES。) AS HIRE_DATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) AS 工齡,CASEWHEN TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)20 THEN TO_CHAR(SALARY*15)WHEN TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)18 THEN TO_CHAR(SALARY*10)WHEN TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)15 THEN TO_CHAR(SALARY*8)ELSE 39。/*查詢employees表中所有員工的last_name,hire_date(格式為:19991010),工齡(只保留整數(shù)部分),以及獎金，如果員工的工齡在15（不包含15年）年以上，獎金為8倍的工資，如果員工的工齡大于18（不包含18年）年以上，獎金為10倍的工資，如果員工的工齡在20（不包含20年）年以上，獎金為15倍的工資，如果員工的工齡小于或等于15年，則沒有獎金顯示此員工不在此次活動范圍內(nèi).*/SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。查詢員工的last_name,department_id,department_nameSELECT ,FROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE =。查詢所有員工的last_name,department_nameSELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES,DEPARTMENTSWHERE =。, SALARY*2, SALARY) AS 增加后工資FROM EMPLOYEES。, SALARY*, 39。, SALARY*, 39。SELECT LAST_NAME,JOB_ID,SALARY,DECODE( JOB_ID, 39。SA_REP39。ST_CLERK39。IT_PROG39。 THEN SALARY*2ELSE SALARYEND AS 增加后工資FROM EMPLOYEES。 THEN SALARY*WHEN JOB_ID=39。 THEN SALARY*WHEN JOB_ID=39。SELECT LAST_NAME,JOB_ID,SALARY,CASEWHEN JOB_ID=39。)FROM EMPLOYEES。,39。NVL2():當(dāng)?shù)谝粋€參數(shù)不為NULL，返回第二個參數(shù)，當(dāng)?shù)谝粋€參數(shù)為NULL，返回第三個參數(shù)SELECT LAST_NAME,NVL2(COMMISSION_PCT,39。沒有傭金39。當(dāng)NULL參與到算術(shù)運算時結(jié)果一定為NULLNVL():當(dāng)?shù)谝粋€參數(shù)不為NULL時，返回第一個參數(shù)的值，當(dāng)?shù)谝粋€參數(shù)為NULL時，返回第二個參數(shù)的值，兩個參數(shù)的類型必須一致。$9,39。$2,39。9,39。2,39。39。)SYSDATEFROM DUAL。,39。SELECT TO_DATE(39。YYYYMMDD39。1999101039。)FROM EMPLOYEESORDER BY SALARY DESC。SELECT LAST_NAME,TO_CHAR(SALARY*3,39。ORDER BY 2 ASC。)=39。) AS HIRE_DATEFROM EMPLOYEESWHERE TO_CHAR(HIRE_DATE,39。SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。)FROM DUAL。), TO_CHAR(,39。), TO_CHAR(,39。), TO_CHAR(,39。), TO_CHAR(,39。), TO_CHAR(,39。), TO_CHAR(,39。), TO_CHAR(,39。TO_CHAR(N,M):將數(shù)字類型N按模板M轉(zhuǎn)換為字符類型的值SELECT TO_CHAR(), TO_CHAR(,39。)=39。)FROM EMPLOYEESWHERE TO_CHAR(HIRE_DATE,39。SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。)=39。)FROM EMPLOYEESWHERE TO_CHAR(HIRE_DATE,39。查詢employees表中所有周三入職的員工的last_name,hire_date(格式為：YYYYMMDD DY)SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。FMYYYY年MM月DD日 DY39。)FROM DUAL。SELECT TO_CHAR(SYSDATE,39。D DD DDD39。)FROM EMPLOYEES。SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。YYYYMMDD DY39。顯式轉(zhuǎn)換：/*數(shù)字與字符進(jìn)行類型轉(zhuǎn)換日期與字符進(jìn)行類型轉(zhuǎn)換數(shù)字與日期不能進(jìn)行類型轉(zhuǎn)換*/TO_CHAR(D,M):將日期類型D按模板M轉(zhuǎn)換為字符類型的值。SELECT LAST_NAME,SALARY,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID=39。LAST_DAY():求日期所在月份的最后一天SELECT LAST_DAY(SYSDATE)FROM DUAL。星期四39。NEXT_DAY():SELECT NEXT_DAY(SYSDATE,7)FROM DUAL。MONTHS_BETWEEN(D1,D2):求兩個參數(shù)相差的月數(shù)SELECT LAST_NAME,HIRE_DATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) AS 年數(shù)FROM EMPLOYEES。/*SQL Server:getDate():獲得系統(tǒng)時間MySQL:currdate():獲得系統(tǒng)日期,沒有時間now():獲得系統(tǒng)日期+時間*//*日期+天數(shù)=日期日期天數(shù)=日期日期日期=天數(shù)日期不能加日期*/SELECT SYSDATE100FROM DUAL。SELECT CEIL()FROM DUAL。MOD():取余數(shù)SELECT MOD(15