【文章內(nèi)容簡(jiǎn)介】 (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。此員工不在此次活動(dòng)范圍內(nèi)39。END AS 獎(jiǎng)金FROM EMPLOYEES。SELECT LAST_NAME,TO_CHAR(HIRE_DATE,39。YYYYMMDD39。) AS HIRE_DATE,TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12) AS 工齡,CASEWHEN TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)15 AND TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)=18 THEN SALARY*8WHEN TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)18 AND TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)=20 THEN SALARY*10WHEN TRUNC(MONTHS_BETWEEN(SYSDATE,HIRE_DATE)/12)20 THEN SALARY*15ELSE 0END AS 獎(jiǎng)金FROM EMPLOYEES。查詢(xún)員工的last_name,job_title,department_name,citySELECT ,FROM EMPLOYEES EMP,DEPARTMENTS DEPT,LOCATIONS LOC,JOBS JOBWHERE = AND = AND =。SQL:1999查詢(xún)員工的last_name,department_nameSELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP ,DEPARTMENTS DEPTWHERE =。SELECT ,FROM EMPLOYEES EMP INNER JOIN DEPARTMENTS DEPT ON =WHERE LIKE 39。%a%39。查詢(xún)員工的last_name,job_title,department_name,citySELECT ,FROM EMPLOYEES EMP,DEPARTMENTS DEPT,LOCATIONS LOC,JOBS JOBWHERE = AND = AND =。SELECT ,FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON =JOIN JOBS JOB ON =JOIN LOCATIONS LOC ON =。內(nèi)連接：查詢(xún)出滿(mǎn)足連接條件的數(shù)據(jù)外連接：查詢(xún)出滿(mǎn)足連接條件與不滿(mǎn)足連接條件的數(shù),左外連接、右外連接查詢(xún)所有員工的last_name,department_nameSELECT ,FROM EMPLOYEES EMP LEFT JOIN DEPARTMENTS DEPTON =。SELECT ,FROM EMPLOYEES EMP RIGHT JOIN DEPARTMENTS DEPTON =。SELECT ,FROM EMPLOYEES EMP FULL OUTER JOIN DEPARTMENTS DEPTON =。ORACLE中獨(dú)有的外連接:(+)左外連接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE =(+)。右外連接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE (+)=。SQL Server:*左外連接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE *=。右外連接SELECT LAST_NAME,DEPARTMENT_NAMEFROM EMPLOYEES EMP,DEPARTMENTS DEPTWHERE =*。組函數(shù)（聚合函數(shù)）SUM():求總和SELECT SUM(SALARY)FROM EMPLOYEES。AVG():求平均值SELECT AVG(SALARY)FROM EMPLOYEES。MAX()/MIN():求最大值/最小值SELECT MAX(SALARY),MIN(SALARY)FROM EMPLOYEES。SELECT MAX(HIRE_DATE),MIN(HIRE_DATE)FROM EMPLOYEES。SELECT MAX(LAST_NAME),MIN(LAST_NAME)FROM EMPLOYEES。求50號(hào)部門(mén)的平均工資，總工資，最高工資與最低工資SELECT AVG(SALARY),SUM(SALARY),MAX(SALARY),MIN(SALARY)FROM EMPLOYEESWHERE DEPARTMENT_ID=50。COUNT()SELECT COUNT(*)返回結(jié)果的行數(shù)FROM EMPLOYEESWHERE DEPARTMENT_ID=50。SELECT COUNT(COMMISSION_PCT)返回指定列中不為NULL的值的個(gè)數(shù)FROM EMPLOYEES。查詢(xún)employees表中l(wèi)ast_name中包含A（不區(qū)分大小寫(xiě)）的員工的人數(shù)SELECT COUNT(*)FROM EMPLOYEESWHERE UPPER(LAST_NAME) LIKE 39。%A%39。SELECT ,FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON =JOIN LOCATIONS LOC ON =WHERE =39。Toronto39。SELECT ,FROM EMPLOYEES EMP,DEPARTMENTS DEPT,LOCATIONS LOC WHERE = AND = AND =39。Toronto39。創(chuàng)建一個(gè)查詢(xún)顯示雇員的 last_name 并帶星號(hào)顯示他們的月薪，每個(gè)星號(hào)表示1000美圓。按薪水降序排序數(shù)據(jù)。列標(biāo)簽為 EMPLOYEES_AND_THEIR_SALARIES。查詢(xún)所有90號(hào)部門(mén)員工的last_name,salary(格式為：￥50,),citySELECT ,TO_CHAR(,39。FML99,39。),FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON = JOIN LOCATIONS LOC ON = WHERE =90。查詢(xún)97年以后入職員工的人數(shù)。SELECT COUNT(*)FROM EMPLOYEESWHERE HIRE_DATETO_DATE(39。1997123139。,39。YYYYMMDD39。)。SELECT COUNT(*)FROM EMPLOYEESWHERE HIRE_DATE39。3112月9739。SELECT COUNT(*)FROM EMPLOYEESWHERE TO_CHAR(HIRE_DATE,39。YYYY39。)39。199739。GROUP BY子句:分組。統(tǒng)計(jì)，報(bào)表。SELECT 4FROM 1[WHERE] 2[GROUP BY] 3[ORDER BY] 5 查詢(xún)每個(gè)部門(mén)的最高的工資SELECT DEPARTMENT_ID,MAX(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_IDORDER BY 1 ASC。SELECT DEPARTMENT_ID,MAX(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_ID。查詢(xún)每個(gè)職位的平均工資，顯示job_id與平均工資SELECT JOB_ID,AVG(SALARY)FROM EMPLOYEESGROUP BY JOB_ID。查詢(xún)每個(gè)部門(mén)最高的工資SELECT MAX(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_ID。查詢(xún)每個(gè)部門(mén)的人數(shù)，顯示department_name與人數(shù)SELECT ,COUNT(*)FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON =GROUP BY 。查詢(xún)每個(gè)部門(mén)的人數(shù)，顯示department_id,department_name與人數(shù)SELECT ,COUNT(*)FROM EMPLOYEES EMP JOIN DEPARTMENTS DEPT ON =GROUP BY ,ORDER BY 1 ASC。查詢(xún)所個(gè)部門(mén)中每個(gè)職位的人數(shù)，顯示department_id,job_id,人數(shù)SELECT DEPARTMENT_ID,JOB_ID,COUNT(*)FROM EMPLOYEESGROUP BY DEPARTMENT_ID,JOB_IDORDER BY 1 ASC,2 ASC。組函數(shù)與NULL值:所有組函數(shù)在進(jìn)行計(jì)算時(shí)會(huì)自動(dòng)忽略NULL值SELECT AVG(NVL(COMMISSION_PCT,0))FROM EMPLOYEES。HAVING子句：過(guò)濾分組結(jié)果，使用HAVING時(shí)必須使用GROUP BY.SELECT 5FROM 1[WHERE] 2[GROUP BY] 3[HAVING 條件] 4[ORDER BY] 6WHERE子句中不能使用組函數(shù)作為過(guò)濾條件。/*WHERE與HAVING的區(qū)別：，HAVING過(guò)濾的是分組的結(jié)果，HAVING可以使用組函數(shù)*/查詢(xún)平均工資大于10000的部門(mén)編號(hào)與平均工資，結(jié)果按工資降序排序SELECT DEPARTMENT_ID,AVG(SALARY)FROM EMPLOYEESGROUP BY DEPARTMENT_IDHAVING AVG(SALARY)10000ORDER BY AVG(SALARY) DESC。當(dāng)使用GROUP BY與DISTINCT時(shí)ORDER BY中的列必須在SELECT子句中出現(xiàn)。查詢(xún)最高工資大于6000的職位與最高工資，并且JOB_ID中要包含REPSELECT JOB_ID,MAX(SALARY)FROM EMPLOYEESWHERE JOB_ID LIKE 39。%REP%39。GROUP BY JOB_IDHAVING MAX(SALARY)=6000。查詢(xún)每年入職的人數(shù)，顯示年份與人數(shù)，結(jié)果按年份升序排序SELECT TO_CHAR(HIRE_DATE,39。YYYY39。),COUNT(*)FROM EMPLOYEESGROUP BY TO_CHAR(HIRE_DATE,39。YYYY39。)ORDER BY TO_CHAR(HIRE_DATE,39。YYYY39。) ASC。查詢(xún)?nèi)藬?shù)大于或等于3人的部門(mén)的department_id與人數(shù)SELECT DEPARTMENT_ID,COUNT(*)FROM EMPLOYEESGROUP BY DEPARTMENT_IDHAVING COUNT(*)=3。組函數(shù)嵌套：查詢(xún)所有部門(mén)的最高的平均工資SELECT MAX(AVG(SALARY))FROM EMPLOYEESGROUP BY DEPARTMENT_ID。/* BY子句*/子查詢(xún)（嵌套查詢(xún)）：查詢(xún)與144號(hào)員工在同一個(gè)部門(mén)工作的員工的last_name,department_idSELECT DEPARTMENT_IDFROM EMPLOYEESWHERE EMPLOYEE_ID=144。普通子查詢(xún)：先執(zhí)行子查詢(xún)，再執(zhí)行主查詢(xún)SELECT LAST_NAME,DEPARTMENT_IDFROM EMPLOYEESWHERE DEPARTMENT_ID=(SELECT DEPARTMENT_ID FROM EMPLOYEES WHERE EMPLOYEE_ID=144)。查詢(xún)哪些員工的工資大于104號(hào)員工的工資，顯示這些員工的last_name,salarySELECT LAST_NAME,SALARYFROM EMPLOYEESWHERE SALARY(SELECT SALARY FROM EMPLOYEES WHERE EMPLOYEE_ID=104)。/*，除了GROUP BY子句外其它子句都可以使用子查詢(xún)，否則不要在子查詢(xún)中使用ORDER BY子句。，必須與主查詢(xún)中條件的列的個(gè)數(shù)與類(lèi)型一致*/查詢(xún)工資大于公司平均工資的員工的last_name,department_id,salarySELECT LAST_NAME,DEPARTMENT_ID,SALARYFROM EMPLOYEESWHERE SALARY(SELECT AVG(SALARY) FROM E