第2章 编写简单的查询语句
1.使用两种方式查询所有员工(EMP)信息。
SELECT *
FROM emp;
SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno
FROM emp;
2.查询(EMP)员工编号、员工姓名、员工职位、员工月薪、工作部门编号。
SELECT empno,ename,job,sal,deptno
FROM emp;
1.员工转正后,月薪上调20%,请查询出所有员工转正后的月薪。
SELECT ename,sal*(1+0.2)
FROM emp;
2.员工试用期6个月,转正后月薪上调20%,请查询出所有员工工作第一年的年薪所得(不考虑奖金部分,年薪的试用期6个月的月薪+转正后6个月的月薪)
SELECT empno,sal*(1+0.2)*6+sal*6
FROM emp;
1.员工试用期6个月,转正后月薪上调20%,请查询出所有员工工作一年的所有收入(需考虑奖金部分),要求显示列标题为员工姓名,工资收入,奖金收入,总收入。
SELECT ename 员工姓名,sal*(1+0.2)*6+sal*6 工资收入,comm 奖金收 入,sal*(1+0.2)*6+sal*6+nvl(comm*12,0) 总收入
FROM emp;
1.员工试用期6个月,转正后月薪上调20%,请查询出所有员工工作第一年的所有收入(需考虑奖金部分),要求显示格式为:XXX的第一年总收入为XXX。
SELECT ename || '的第一年总收入为' || (sal*(1+0.2)*6+sal*6+nvl(comm*12,0))
FROM emp;
2.查询员工表中一共有哪几种岗位类型。
SELECT DISTINCT deptno
FROM emp;
1.分别选择员工表、部门表、薪资等级表中的所有数据。
SELECT *
FROM emp;
SELECT *
FROM dept;
SELECT *
FROM salgrade;
2.分别查看员工表、部门表、薪资等级表结构。
DESC emp;
DESC dept;
DESC salgrade;
第3章 限制数据和对数据排序
1.查询职位为SALESMAN的员工编号、职位、入职日期。
SELECT empno,job,hiredate
FROM emp
WHERE job='SALESMAN';
2.查询1985年12月31日之前入职的员工姓名及入职日期。
SELECT empno,hiredate
FROM emp
WHERE hiredate<'31-12月1985';
3.查询部门标号不在10部门的员工姓名、部门编号。
SELECT ename,deptno
FROM emp
WHERE deptno<>10;
1.查询入职日期造82年至85年员工姓名,入职日期。
SELECT ename,hiredate
FROM emp
WHERE hiredate BETWEEN '01-1月-82' AND '01-1月-85';
SELECT ename,hiredate
FROM emp
WHERE hiredate BETWEEN TO_DATE('1982-01-01','YYYY-MM-DD')
AND TO_DATE('1985-01-01','YYYY-MM-DD');
2.查询月薪在3000到5000的员工姓名,月薪。
SELECT ename,sal
FROM emp
WHERE sal BETWEEN 3000 AND 5000;
3.查询部门编号为10或者20的员工姓名,部门编号。
SELECT ename,deptno
FROM emp
WHERE deptno=10 OR deptno=20;
4.查询经理编号为7709,7566,7788的员工姓名,经理编号。
SELECT ename,mgr
FROM emp
WHERE mgr IN(7709,7756,778);
1.查询员工姓名以W开头的员工姓名。
SELECT ename
FROM emp
WHERE ename LIKE 'W%';
SELECT ename
FROM emp
WHERE INSTR(ename,'W')=1;
2.查询员工姓名倒数第2个字符为T的员工姓名。
SELECT ename
FROM emp
WHERE ename LIKE '%T_';
SELECT ename
FROM emp
WHERE SUBSTR(ename,-2,1)='T';
3.查询奖金为空的员工姓名,奖金。
SELECT ename,comm
FROM emp
WHERE comm IS NULL;
1.查询工资超过2000并且职位是WANAGER,或者职位是SALESMAN的员工姓名、职位、工资。
SELECT ename,job,sal
FROM emp
WHERE (sal>2000 AND job='MANAGER') OR job='SALESMAN';
2 .查询工资超过2000并且职位是WANAGER或SALESMAN的员工姓名、职位、工资。
SELECT ename,job,sal
FROM emp
WHERE (sal>2000 AND job='MANAGER') OR job='SALESMAN';
3.查询部门在10或者20,并且工资在3000到5000之间的员工姓名、部门、工资。
SELECT ename,deptno,sal
FROM emp
WHERE (deptno=10 OR deptno=20) AND sal BETWEEN 3000 AND 5000;
4.查询入职日期在81年,并且职位不是SALES开头的员工姓名、入职日期、职位。
SELECT ename,hiredate,job
FROM emp
WHERE (hiredate BETWEEN '01-1月-81' AND '31-12月-81') AND job NOT LIKE 'SALES%';
SELECT ename,hiredate,job
FROM emp
WHERE (hiredate BETWEEN '01-1月-81' AND '31-12月-81')
AND SUBSTR(job,0,5)<>'SALES';
5.查询职位为SALESMAN或MANAGER,部门编号为10或者20,姓名包含A的员工姓名、职位、部门编号。
SELECT ename,job,deptno
FROM emp
WHERE (job='SALESMAN' OR job='MANAGER') AND (deptno=10 OR deptno=20) AND ename LIKE '%A%';
SELECT ename,job,deptno
FROM emp
WHERE (job='SALESMAN' OR job='MANAGER')
AND (deptno=10 OR deptno=20)
AND INSTR(ename,'A')>0;
1.查询部门在20或30的员工姓名,部门编号,并按照工资升序排序。
SELECT ename,deptno,sal
FROM emp
WHERE deptno=20 OR deptno=30
ORDER BY sal;
2.查询工资在2000-3000之间,部门不在10号的员工姓名,部门编号,工资,并按照部门升序,工资降序排序。
SELECT ename,deptno, sal
FROM emp
WHERE (sal BETWEEN 2000 AND 3000) AND deptno<>10
ORDER BY deptno,sal DESC;
3.查询入职日期在82年至83年之间,职位以SALES或者MAN开头的员工姓名,入职日期,职位,并按照入职日期降序排序。
--3.查询入职日期在82年至83年之间,职位以SALES或者MAN开头的员工姓名,入职日期,职位,并按照入职日期降序排序。
SELECT ename,hiredate,job
FROM emp
WHERE (hiredate BETWEEN '01-1月-82' AND '01-1月-83')
AND (job LIKE 'SALES%' OR job LIKE 'MAN%')
ORDER BY hiredate DESC;
1.查询入职时间在1982-7-9之后,并且不从事SALESMAN工作的员工姓名、入职时间、职位。
SELECT ename,hiredate,job
FROM emp
WHERE hiredate>'09-7月-1982' AND job<>'SALESMAN';
2.查询员工姓名的第三个字母是a的员工姓名。
SELECT ename
FROM emp
WHERE SUBSTR(ename,3,1)='a';
SELECT ename
FROM emp
WHERE ename LIKE '__a%';
3.查询除了10、20号部门以外的员工姓名、部门编号。
SELECT ename,deptno
FROM emp
WHERE deptno NOT IN(10,20);
4.查询部门号为30号员工的信息,先按工资降序排序,再按姓名升序排序。
SELECT *
FROM emp
WHERE deptno=30
ORDER BY sal DESC,ename;
5.查询没有上级的员工(经理号为空)的员工姓名。
SELECT ename
FROM emp
WHERE mgr IS NULL;
6.查询工资大于等于4500并且部门为10或者20的员工的姓名,工资,部门编号。
SELECT ename,sal,deptno
FROM emp
WHERE sal>=4500 AND deptno IN(10,20);
第4章 单行函数
1.写一个查询,用首字母大写,其他字母小写显示雇员的ename,显示名字的长度,并给每列一个适当的标签,条件是满足所有雇员名字的开始字母是J、A或M的雇员,并对查询结果按雇员的ename升序排序。(提示:使用initcap、length、substr)
SELECT INITCAP(ename) 雇员姓名,LENGTH(ename) 姓名长度
FROM emp
WHERE ename LIKE 'J%' OR ename LIKE 'A%' OR ename LIKE 'M%'
ORDER BY ename;
1.查询员工姓名中包含大写或小写字母A的员工姓名。
SELECT ename
FROM emp
WHERE SUBSTR(ename,1,1)='A' OR SUBSTR(ename,1,1)='a';
2.查询部门编号为10或20,入职日期在81年5月1日之后,并且姓名中包含大写字母A的员工姓名,员工姓名长度。(提示:要求使用INSTR函数)
SELECT ename 员工姓名,LENGTH(ename) 姓名长度
FROM emp
WHERE (deptno=10 OR deptno=20)
AND hiredate>'01-5月-81'
AND SUBSTR(ename,1,1)='A';
3.查询每个职工的编号,姓名,工资
-要求将查询到的数据按照一定的格式合并成一个字符串
-前10位:编号,不足部分用*填充,左对齐
-中间10位:姓名,不足部分用*填充,左对齐
-后10位:工资,不足部分用*填充,右对齐
SELECT CONCAT(CONCAT(LPAD(empno,10,'*'),LPAD(ename,10,'*')),RPAD(sal,10,'*'))
FROM emp;
1.写一个查询,分别计算100.456四舍五入到小数点后第2位,第1位,整数位的值。
SELECT ROUND(100.456,2)
FROM dual;
SELECT ROUND(100.456,1)
FROM dual;
SELECT ROUND(100.456,-1)
FROM dual;
2.写一个查询,分别计算100.456从小数点后第2位,第1位,整数位截断的值。
SELECT TRUNC(100.456,2)
FROM dual;
SELECT TRUNC(100.456,1)
FROM dual;
SELECT TRUNC(100.456,-1)
FROM dual;
1.查询每个员工截止到现在一共入职多少天?
SELECT empno,SYSDATE-hiredate 入职天数
FROM emp
2.当前日期为2015年,指定日期格式DD-MON-RR,指定日期为01-1月-01,该日期实际所代表的日期为?
2001-01-01
3. 当前日期为2015年,指定日期格式DD-MON-RR,指定日期为01-1月-95,该日期实际所代表的日期为?
1995-01-01
4.当前日期为1998年,指定日期格式DD-MON-RR,指定日期为01-1月-01,该日期实际所代表的日期为?
2001-01-01
5.当前日期为1998年,指定日期格式DD-MON-RR,指定日期为01-1月-95,该日期实际所代表的日期为?
1995-01-01
6.当前日期为1998年,指定日期格式DD-MON-YY,指定日期为01-1月-01,该日期实际所代表的日期为?
1901-01-01
7.当前日期为1998年,指定日期格式DD-MON-YY,指定日期为01-1月-95,该日期实际所代表的日期为?
1995-01-01
1.查询服务器当前时间。
SELECT SYSDATE
FROM dual;
2.查询部门10,20的员工截止到2000年1月1日,工作了多少个月,入职的月份。(使用months_between, extract)
SELECT MONTHS_BETWEEN('01-1月-2000',hiredate) 工作月份,
EXTRACT(MONTH FROM DATE '2000-01-01') 入职月份
FROM emp
WHERE deptno=10 OR deptno=20;
3.如果员工使用期6个月,查询职位不是MANAGER员工姓名,入职日期,转正日期,入职日期后的第一个星期一,入职当前月的最后一天日期。(使用add_months, next_day, last_day)
SELECT ename,hiredate,
ADD_MONTHS(hiredate,6) 转正日期,
NEXT_DAY(hiredate,'星期一') 入职日期后的第一个星期一,
LAST_DAY(hiredate) 入职当前月的最后一天日期
FROM emp
WHERE job<>'MANAGER';
1.显示服务器系统当前时间,格式为2007-10-12 17:11:11。(用to_char函数)
SELECT to_char(SYSDATE,'YYYY-MM-DD HH24:MI:SS')
FROM dual;
2.显示ename,hiredate和雇员开始工作日是星期几,列标签DAY。(用to_char函数)
SELECT ename,hiredate,
to_char(hiredate,'DAY') DAY
FROM emp;
3.查询员工姓名,工资,格式化的工资(¥999,999.99)。(用to_char函数)
SELECT ename,sal,
to_char(sal,'$999,999.99') 格式化的工资
FROM emp;
4.把字符串2015-3月-18 13:13:13转换成日期格式,并计算和系统当前时间间隔多少天。(用to_char函数)
SELECT TO_DATE('2015-3月-18 13:13:13','YYYY-MM"月"-DD HH24:MI:SS'),
SYSDATE-TO_DATE('2015-3月-18 13:13:13','YYYY-MM"月"-DD HH24:MI:SS')
FROM dual;
1.计算2000年1月1日到现在又多少月,多少周(四舍五入)。
SELECT ROUND(MONTHS_BETWEEN(SYSDATE,'01-1月-2000')),
ROUND(ROUND(SYSDATE-TO_DATE('2000-01-01','YYYY-MM-DD'))/7)
FROM dual;
2.查询员工ename的第三个字母是A的员工信息(使用两个函数)。
SELECT *
FROM emp
WHERE SUBSTR(ename,3,1)='A';
SELECT *
FROM emp
WHERE INSTR(ename,'A',3)=3;
3.使用trim函数将字符串‘hello’,‘Hello’,‘bllb’,‘hello’分别处理得以下字符串ello、Hello、ll、hello。
SELECT TRIM('h' FROM 'hello'),
TRIM(' ' FROM 'Hello '),
TRIM('b' FROM 'bllb'),
TRIM(' ' FROM 'hello ')
FROM dual;
4.将员工工资按如下格式显示:123,234.00 RMB。
SELECT empno,to_char(sal,'999,999.99') || 'RMB'
FROM emp;
5.查询员工的姓名及其经理编号,要求对于没有经理的显示“No Manager”字符串。
SELECT ename,NVL(to_char(mgr),'No Manager')
FROM emp;
6.将员工的参加工作日期按照如此啊格式显示:月份/年份
SELECT empno,to_char(hiredate,'MM/YYYY')
FROM emp;
7.在员工表中查询除员工的工资,并计算应交税款:如果工资小于1000,税率为0,如果工资大于等于1000并小于2000,税率为10%,如果工资大于等于2000并小于3000,税率为15%,如果工资大于等于3000,税率为20%。
SELECT empno,sal,
(CASE
WHEN sal>=0 AND sal<1000 THEN sal*0
WHEN sal>=1000 AND sal<2000 THEN sal*0.1
WHEN sal>=2000 AND sal<3000 THEN sal*0.15
WHEN sal>=3000 THEN sal*0.1
ELSE 0 END) 税率
FROM emp;
8.创建一个查询显示所有雇员的ename和sal。格式化sal围殴15个字符长度,用$左填充,列标签SALARY。
SELECT ename,LPAD(sal,15,'$') SALARY
FROM emp;
第5章 多表连接
1.写一个查询,显示所有员工姓名,部门编号,部门姓名。
SELECT ename,emp.deptno,dname
FROM emp,dept
WHERE emp.deptno=dept.deptno;
2.写一个查询,显示所有工作在CHICAGO并且奖金不为空的员工姓名,工作地点,奖金。
SELECT ename,loc,comm
FROM emp,dept
WHERE emp.deptno=dept.deptno AND comm IS NOT NULL;
3.写一个查询,显示所有姓名中含有A字符的员工姓名,工作地点。
SELECT ename,loc
FROM emp,dept
WHERE emp.deptno=dept.deptno AND ename LIKE '%A%';
1.查询每个员工的编号,姓名,工资,工资等级,所在工作城市,按照工资等级进行升序排序。
SELECT e.deptno,ename,sal,grade,loc
FROM emp e,dept d,salgrade s
WHERE e.deptno=d.deptno
AND (e.sal BETWEEN s.losal AND s.hisal)
ORDER BY grade;
1.查询所有工作在NEW YORK和CHICAGO的员工姓名,员工编号,以及他们的经理姓名,经理编号。
SELECT e.ename 员工姓名,e.empno 员工编号,
m.ename 经理姓名,m.empno 经理编号
FROM emp e,emp m
WHERE e.mgr=m.empno;
2.在上一题的基础上,添加没有经理的员工King,并按照员工编号排序。
SELECT e.ename 员工姓名,e.empno 员工编号,
m.ename 经理姓名,m.empno 经理编号
FROM emp e,emp m
WHERE e.mgr=m.empno(+)
ORDER BY e.empno;
3.查询所有员工编号,姓名,部门名称,包括没有部门的员工也要显示出来。
SELECT emp.empno,ename,dname
FROM emp,dept
WHERE emp.deptno=dept.deptno(+);
使用SQL-99写法,完成如下练习
1.创建一个员工表和部门表的交叉连接。
SELECT *
FROM emp
CROSS JOIN dept;
2.使用自然连接,显示入职日期在80年5月1日之后的员工姓名,部门名称,入职日期。
SELECT ename,dname,hiredate
FROM emp
NATURAL JOIN dept
WHERE hiredate>'01-5月-80';
3.使用USING子句,显示工作在CHICAGO的员工姓名,部门名称,工作地点。
SELECT ename,dname,loc
FROM emp
JOIN dept USING(deptno)
WHERE loc='CHICAGO';
4.使用ON子句,显示工作在CHICAGO的员工姓名,本门名称,工作地点,薪资等级。
SELECT ename,dname,loc,grade
FROM emp e
JOIN dept d
ON e.deptno=d.deptno AND loc='CHICAGO'
JOIN salgrade s
ON sal BETWEEN losal AND hisal;
5.使用左连接,查询每个员工的姓名,经理姓名,没有经理的King也要显示出来。
SELECT e.ename 员工姓名,m.ename 经理姓名
FROM emp e
OUTER JOIN emp m
ON e.mgr=m.empno;
6.使用右外连接,查询每个员工的姓名,经理姓名,没有经理的King也要显示出来。
SELECT e.ename 员工姓名,m.ename 经理姓名
FROM emp m
RIGHT OUTER JOIN emp e
ON e.mgr=m.empno;
1.显示员工SMITH的姓名,部门名称,直接上级名称。
SELECT e.ename,dname,m.ename 直接上级名称
FROM emp e,emp m,dept d
WHERE e.mgr=m.empno AND e.deptno=d.deptno AND e.ename='SMITH';
2.显示员工姓名,部门名称,工资,工资级别,要求工资级别大于4级。
SELECT ename,dname,sal,grade
FROM emp e,dept d,salgrade s
WHERE e.deptno=d.deptno
AND (sal BETWEEN losal AND hisal)
AND grade>4;
3.显示员工KING和FORD管理的员工姓名及其经理姓名。
SELECT e.ename 员工姓名,m.ename 经理姓名
FROM emp e,emp m
WHERE e.mgr=m.empno
AND m.ename IN('KING','FORD');
4.显示员工姓名,参加工作时间,经理名,参加工作时间,要求参加时间比经理早。
SELECT e.ename,e.hiredate,m.ename,m.hiredate
FROM emp e,emp m
WHERE e.mgr=m.empno AND e.hiredate>m.hiredate;
第6章 分组函数
1.查询部门20的员工,每月的工资总和及平均工资。
SELECT SUM(sal),AVG(sal)
FROM emp
WHERE deptno=20;
2.查询工作在CHICAGO的员工人数,最高工资及最低工资。
SELECT COUNT(*),MIN(sal),MAX(sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno AND loc='CHICAGO';
3.查询员工表中一共有几种岗位类型。
SELECT COUNT(DISTINCT job)
FROM emp;
1.查询每个部门的部门编号,部门名称,部门人数,最高工资,最低工资,工资总和,平均工资。
SELECT d.deptno,dname,COUNT(e.deptno),MIN(sal),MAX(sal),SUM(sal),AVG(sal)
FROM emp e,dept d
WHERE e.deptno(+)=d.deptno
GROUP BY d.deptno,dname,loc;
2.查询每个部门,每个岗位的部门编号,部门名称,岗位名称,部门人数,最高工资,最低工资,工资总和,平均工资。
SELECT d.deptno,dname,job,COUNT(e.empno),MAX(sal),MIN(sal),SUM(sal),AVG(sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,dname,e.job;
3.查询每个经理所管理的人数,经理编号,经理姓名,要求包括没有经理的人员信息。
SELECT COUNT(e.empno),m.empno,m.ename
FROM emp e,emp m
WHERE e.mgr=m.empno(+)
GROUP BY m.empno,m.ename;
1.查询部门人数大于2的部门编号,部门名称,部门人数。
SELECT d.deptno,dname,COUNT(empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,dname
HAVING COUNT(empno)>2;
2.查询部门平均工资大于2000,且人数大于2的部门编号,部门名称,部门人数,
部门平均工资,并按照部门人数升序排序。
SELECT d.deptno,dname,COUNT(empno),AVG(sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,dname
HAVING AVG(sal)>2000 AND COUNT(empno)>2
ORDER BY COUNT(empno);
1.查询部门平均工资在2500元以上的部门名称及平均工资。
SELECT dname,AVG(sal)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY dname
HAVING AVG(sal)>2500;
2.查询员工岗位中不是以“SA”开头并且平均工资在2500员以上的岗位及平均工资,并按平均工资降序排序。
SELECT job,AVG(sal)
FROM emp
WHERE job NOT LIKE 'SA%'
GROUP BY job
HAVING AVG(sal)>2500
ORDER BY AVG(sal) DESC;
3.查询部门人数在2人以上的部门名称,最低工资,最高工资,并对要求的工资进行四舍五入到整数位。
SELECT dname,ROUND(MIN(sal),-1) 最低工资,
ROUND(MAX(sal),-1) 最高工资
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY dname
HAVING COUNT(empno)>2;
4.查询岗位不为SALESMSMAN,工资和大于等于2500的岗位及每种岗位的工资和。
SELECT job,SUM(sal)
FROM emp
WHERE job<>'SALESMSMAN'
GROUP BY job
HAVING SUM(sal)>2500;
5.显示经理编号和经理姓名,这个经理所管理员工的最低工资,没有经理的KING也要显示,不包括最低工资小于3000的,按最低工资由高到低排序。
SELECT m.empno,m.ename,MIN(e.sal)
FROM emp e,emp m
WHERE e.mgr=m.empno(+)
GROUP BY m.empno,m.ename
HAVING MIN(e.sal)>=3000
ORDER BY MIN(e.sal) DESC;
6.写一个查询,显示每个部门最高工资和最低工资的差额。
SELECT deptno,MAX(sal)-MIN(sal)
FROM emp
GROUP BY deptno;
第7章 子查询
1.查询入职日期最早的员工姓名,入职日期。
--最早的入职日期
SELECT MIN(hiredate)
FROM emp;
SELECT ename,hiredate
FROM emp
WHERE hiredate=
(SELECT MIN(hiredate)
FROM emp);
2.查询工资比SMITH工资高并且工作地点在CHICAGO的员工姓名,工资,部门名称。
--SMITH的工资
SELECT sal
FROM emp
WHERE ename='SMITH';
SELECT ename,sal,dname
FROM emp e,dept d
WHERE sal>
(SELECT sal
FROM emp
WHERE ename='SMITH')
AND loc='CHICAGO';
3.查询入职日期比20部门入职日期最早的员工还早的员工姓名,入职日期。
--20部门员工的最早入职日期
SELECT MIN(hiredate)
FROM emp
WHERE deptno=20;
SELECT ename,hiredate
FROM emp
WHERE hiredate<
(SELECT MIN(hiredate)
FROM emp
WHERE deptno=20);
4.查询部门人数大于所有部门平均人数的部门编号,部门名称,部门人数。
--所有部门平均人数
SELECT AVG(COUNT(empno))
FROM emp
GROUP BY deptno;
SELECT d.deptno,dname,COUNT(empno)
FROM emp e,dept d
WHERE e.deptno=d.deptno
GROUP BY d.deptno,dname
HAVING COUNT(empno)>
(SELECT AVG(COUNT(empno))
FROM emp
GROUP BY deptno);
1.查询入职日期比10部门任意一个员工晚的员工姓名,入职日期,不包括10部门员工。
--10部门员工的最早入职日期
SELECT MIN(hiredate)
FROM emp
WHERE deptno=10;