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.gradeo
FROM EMP E
join 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 employees
WHERE DEPARTMENT_ID IN('80','90');
--多表等值连接
SELECT E.FIRST_NAME,J.JOB_TITLE,D.DEPARTMENT_NAME,L.CITY,E.SALARY
FROM EMPLOYEES E,JOBS J,DEPARTMENTS D,LOCATIONS L
WHERE 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.SALARY
FROM EMPLOYEES E
NATURAL JOIN JOBS J;
--USING子句
SELECT E.FIRST_NAME,J.JOB_TITLE,E.SALARY
FROM EMPLOYEES E JOIN JOBS J
USING(JOB_ID);
--ON子句
SELECT E.FIRST_NAME,J.JOB_TITLE,E.SALARY
FROM EMPLOYEES E JOIN JOBS J
ON(E.JOB_ID=J.JOB_ID);  --相比USING子句更为灵活，因为字段名可以不一致。
--三向连接
SELECT E.FIRST_NAME,D.DEPARTMENT_NAME,L.CITY,E.SALARY
FROM EMPLOYEES E
NATURAL JOIN DEPARTMENTS D
NATURAL JOIN LOCATIONS L;
---非等值内连接
SELECT E.FIRST_NAME,E.SALARY,EG.NAME
FROM EMPLOYEES E,EGRADE EG
WHERE E.HIRE_DATE BETWEEN EG.HIRE_START AND EG.HIRE_END;
--左外连接
SELECT E.FIRST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E
LEFT OUTER JOIN DEPARTMENTS D
ON(E.DEPARTMENT_ID=D.DEPARTMENT_ID);
--左外连接简洁写法
SELECT E.FIRST_NAME,E.DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E,DEPARTMENTS D
WHERE D.DEPARTMENT_ID=E.DEPARTMENT_ID(+);
--全外连接
SELECT E.FIRST_NAME,DEPARTMENT_ID,D.DEPARTMENT_NAME
FROM EMPLOYEES E
FULL OUTER JOIN DEPARTMENTS D
USING(DEPARTMENT_ID);