SQL单表查询
--1,选择不满30中的雇员
SELECT *
FROM EMP
WHERE DEPTNO = 30;
--2,列出所有办事员的姓名,编号和部门
SELECT ENAME,EMPNO,DEPTNO
FROM EMP
WHERE JOB = 'CLERK';
--3,找出佣金高于薪金的雇员
SELECT *
FROM EMP
WHERE NVL(COMM,0) > SAL;
--4,找出佣金高出薪金的60%的雇员
SELECT *
FROM EMP
WHERE NVL(COMM,0) > SAL * 0.6;
--5,找出部门10中所有经理和部门20中所有办事员的详细资料
SELECT *
FROM EMP
WHERE (DEPTNO = 10 AND JOB = 'MANAGER')
OR (DEPTNO = 20 AND JOB = 'CLERK');
--
SELECT *
FROM EMP
WHERE DEPTNO = 10
AND JOB = 'MANAGER'
UNION
SELECT *
FROM EMP
WHERE DEPTNO = 20
AND JOB = 'CLERK';
--6,找出部门10中所有经理,部门20中所有办事员
--以及既不是经理又不是办事员但其薪金大于或等于2000
--的所有雇员的详细资料
SELECT *
FROM EMP
WHERE (DEPTNO = 10 AND JOB = 'MANAGER')
OR (DEPTNO = 20 AND JOB = 'CLERK')
OR (JOB NOT IN ('MANAGER','CLERK')
AND SAL >= 2000);
--
SELECT *
FROM EMP
WHERE DEPTNO = 10
AND JOB = 'MANAGER'
UNION
SELECT *
FROM EMP
WHERE DEPTNO = 20
AND JOB = 'CLERK'
UNION
SELECT *
FROM EMP
WHERE JOB != 'MANAGER'
AND JOB != 'CLERK'
AND SAL >= 2000;
--7,找出收取佣金的员工的不同工作
SELECT DISTINCT JOB
FROM EMP
WHERE NVL(COMM,0) > 0;
--8,找出不收取佣金或收取的佣金低于100的员工
SELECT *
FROM EMP
WHERE NVL(COMM,0) < 100
OR COMM IS NULL;
--9,找出各月倒数第三天受雇的所有员工
SELECT *
FROM EMP
WHERE HIREDATE = LAST_DAY(HIREDATE) -2;
--10,找出早于12年前受雇的员工
SELECT *
FROM EMP
WHERE HIREDATE < ADD_MONTHS(SYSDATE,-12*12);
--11,以首字母大写的方式显示所有员工的姓名
SELECT INITCAP(ENAME) AS 姓名
FROM EMP;
--12,显示正好为5个字符的员工的姓名
SELECT ENAME
FROM EMP
WHERE LENGTH(ENAME) = 5;
--
SELECT ENAME
FROM EMP
WHERE ENAME LIKE '_____';
--13,显示不带‘R’的员工的姓名
SELECT ENAME
FROM EMP
WHERE ENAME NOT LIKE '%R%';
--14,显示所有员工姓名的前三个字符
SELECT SUBSTR(ENAME,1,3) AS ENAME
FROM EMP;
--15,显示所有员工的姓名,用a替换所有‘A’
SELECT REPLACE(ENAME,'A','a') AS ENAME
FROM EMP;
--
SELECT TRANSLATE(ENAME,'A','a') AS ENAME
FROM EMP;
--16,显示满10年服务年限的员工的姓名和受雇日期
SELECT ENAME,HIREDATE
FROM EMP
WHERE MONTHS_BETWEEN(SYSDATE,HIREDATE) > 120;--!大时间写在后
--
SELECT ENAME,HIREDATE
FROM EMP
WHERE SYSDATE > ADD_MONTHS(HIREDATE,12 * 10);
--17,显示员工的详细资料,按姓名排序
SELECT *
FROM EMP
ORDER BY ENAME;
--18,显示员工的姓名和受雇日期,根据其服务年限,
--将最老的员工排在最前面
SELECT ENAME,HIREDATE
FROM EMP
ORDER BY HIREDATE;
--19,显示所有员工的姓名、工作和薪金,按工作的
--降序排列,若工作相同则按薪金排序
SELECT ENAME,JOB,SAL
FROM EMP
ORDER BY JOB DESC,SAL;
--20,显示所有员工的姓名、加入公司的年份和月份,
--按受雇日期所在月排序,若月份相同则将最早
--年份的员工排在最前面
SELECT ENAME,TO_CHAR(HIREDATE,'YYYY"年"MM"月"')
FROM EMP
ORDER BY TO_CHAR(HIREDATE,'MM'),TO_CHAR(HIREDATE,'YYYY')
--21,显示在一个月为30天的情况所有员工的日薪金,
--忽略余数
SELECT ENAME,TRUNC(SAL/30)
FROM EMP;
--22,找出在(任何年份的)2月受聘的所有员工
SELECT *
FROM EMP
WHERE TO_CHAR(HIREDATE,'MM') = '02';
--23,对于每个员工,显示其加入公司的天数
SELECT ENAME,FLOOR(SYSDATE - HIREDATE)
FROM EMP;
--24,显示姓名字段的任何位置包含‘A’的所有员工的姓名
SELECT *
FROM EMP
WHERE ENAME LIKE '%A%';
--25,以年月日的方式显示所有员工的服务年限(大概)
SELECT EMPNO,ENAME,
(
'在职'||TRUNC((MONTHS_BETWEEN(SYSDATE,HIREDATE)/12))||'年'||
TRUNC(MOD((MONTHS_BETWEEN(SYSDATE,HIREDATE)),12))||'个月'||
ROUND(SYSDATE-(ADD_MONTHS(HIREDATE,MONTHS_BETWEEN
(SYSDATE,HIREDATE))))||'天'
)
FROM EMP;