Oracle数据库查询语句练习

列别名等。

 SELECT MAX(sal) AS "最高薪",MIN(sal) AS "最低薪",ROUND(AVG(DISTINCT sal)) AS "平均薪水",SUM(DISTINCT sal) AS "薪水总和"FROM emp ; SELECT COUNT(NVL(ENAME,0)) FROM emp;SELECT deptno,MAX(sal) FROM emp GROUP BY deptno ORDER BY MAX(SAL) DESC;SELECT E.ENAME AS "员工姓名",D.DNAME AS "部门",S.GRADEO AS "薪水等级" FROM EMP E,DEPT D, SALGRADE S WHERE E.JOB = 'MANAGER' AND (E.DEPTNO=D.DEPTNO);SELECT ENAME FROM EMP WHERE JOB='MANAGER';

--多表连接SELECT E.Ename,E.Sal,s.gradeoFROM EMP Ejoin salgrade s on(E.sal between s.losal and s.hisal)ORDER BY ENAME;以特定格式输出日期

SELECT EMPNOO,ENAME,TO_CHAR(HIREDATE,'YYYY')||'年'||TO_CHAR(HIREDATE,'MM')||'月'||TO_CHAR(HIREDATE,'DD')||'日' AS "入职时间" FROM EMP WHERE ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE))>12;--计算工作时间

SELECT ROUND(MONTHS_BETWEEN(SYSDATE,HIREDATE)) AS "工作时间" FROM EMP;

--截取前三个字符SELECT SUBSTR(ENAME,1,3) FROM EMP WHERE DEPTNO=10 ORDER BY ENAME;

--字符转小写，日期转字符，字符转特定格式SELECT LOWER(ENAME),TO_CHAR(HIREDATE,'YYYY-MM-DD'),TO_CHAR(SAL,'$99,999.999') FROM EMP;

函数和多表连接

--COALESCE函数SELECT first_name||' '||last_name,COALESCE(TO_CHAR(commission_pct),TO_CHAR(manager_id),'老板') AS COALESCE列FROM employeesWHERE DEPARTMENT_ID IN('80','90');--多表等值连接SELECT E.FIRST_NAME,J.JOB_TITLE,D.DEPARTMENT_NAME,L.CITY,E.SALARYFROM EMPLOYEES E,JOBS J,DEPARTMENTS D,LOCATIONS LWHERE E.JOB_ID=J.JOB_ID AND E.DEPARTMENT_ID=D.DEPARTMENT_ID AND D.LOCATION_ID=L.LOCATION_ID;--自然连接   注意：两个表中的字段名，字段类型需要完全一致，否则返回错误。SELECT E.FIRST_NAME,J.JOB_TITLE,E.SALARYFROM EMPLOYEES ENATURAL JOIN JOBS J;--USING子句SELECT E.FIRST_NAME,J.JOB_TITLE,E.SALARYFROM EMPLOYEES E JOIN JOBS JUSING(JOB_ID);--ON子句SELECT E.FIRST_NAME,J.JOB_TITLE,E.SALARYFROM EMPLOYEES E JOIN JOBS JON(E.JOB_ID=J.JOB_ID);  --相比USING子句更为灵活，因为字段名可以不一致。--三向连接SELECT E.FIRST_NAME,D.DEPARTMENT_NAME,L.CITY,E.SALARYFROM EMPLOYEES ENATURAL JOIN DEPARTMENTS DNATURAL JOIN LOCATIONS L;---非等值内连接SELECT E.FIRST_NAME,E.SALARY,EG.NAMEFROM EMPLOYEES E,EGRADE EGWHERE E.HIRE_DATE BETWEEN EG.HIRE_START AND EG.HIRE_END;--左外连接SELECT E.FIRST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAMEFROM EMPLOYEES ELEFT OUTER JOIN DEPARTMENTS DON(E.DEPARTMENT_ID=D.DEPARTMENT_ID);--左外连接简洁写法SELECT E.FIRST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAMEFROM EMPLOYEES E,DEPARTMENTS DWHERE D.DEPARTMENT_ID=E.DEPARTMENT_ID(+);--全外连接SELECT E.FIRST_NAME,DEPARTMENT_ID,D.DEPARTMENT_NAMEFROM EMPLOYEES EFULL OUTER JOIN DEPARTMENTS DUSING(DEPARTMENT_ID);