现在的位置: 首页 > 数据库 > 正文

数据库sql210个综合复习查询语句

2019年09月30日 数据库 ⁄ 共 4523字 ⁄ 字号 暂无评论

任务1、SELECT * FROM emp;
任务2、使用列名查看员工表的所有员工信息
SELECT empno,ename,job,mgr,hiredate,sal,comm,deptno
FROM emp;
任务3、查询所有员工的部门号和姓名,包括rowid伪列。
SELECT deptno, ename ,rowid
FROM emp;
任务4、查询员工信息:员工号 姓名 月薪 年薪
SELECT ename , sal, 12*sal
FROM emp;
任务5、查询员工信息:员工号 姓名 月薪 年薪 奖金 年收入
SELECT empno,ename,sal,sal*12,comm,sal*12+NVL(comm,0)
FROM emp;
任务6、显示昨天,今天,明天的时间。(使用系统虚表dual)
SELECT SYSDATE-1,SYSDATE,SYSDATE+1
FROM dual;
任务7、查询出所有员工加薪400元后的薪水。
SELECT ename, sal, sal + 400 AS newsal
FROM emp;
任务8、查询显示员工工资降10%后的年收入信息
SELECT ename, sal, sal*(1-0.1)*12+nvl(comm,0) income
FROM emp;
任务9、给员工表起一个简单别名,查询所有员工的姓名,职位。
SELECT e.ename, e.job
FROM emp e ;
任务10、利用dual表连接字符串‘Hello’与‘World’并显示结果。
SELECT 'Hello'||'World'
FROM dual;
任务11、查询显示所有员工信息“员工***的工资是:多少”
SELECT ename|| ’的工资是:’||sal
FROM emp;
任务12、 在SCOTT模式下,显示emp表中的job(职务)列,要求显示的“职务”记录不重复。
SELECT DISTINCT job
FROM emp;
任务13、查询员工SCOTT的姓名,工资和部门号
SELECT ename,sal,deptno
FROM emp
WHERE ename =‘SCOTT’;
任务14、查询薪水在1000到2000元的部门的员工名单。
SELECT ename,sal,deptno
FROM emp
WHERE sal BETWEEN 1000 AND 2000;
任务15、查询10部门,20部门和50部门的员工的薪水。
SELECT ename,sal,deptno
FROM emp
WHERE deptno IN(10,20,50);
任务16、查询名字以‘S’开头的员工的员工姓名,工资和部门号。
SELECT ename,sal,deptno
FROM emp
WHERE ename LIKE‘S%’;
任务17、查询名字中带有'_' 下划线的员工的员工姓名和工资
SELECT ename,sal FROM emp
WHERE ename LIKE '%\_%' ESCAPE '\';
任务18、查询没有奖金的员工的员工姓名,工资和部门号。
SELECT ename,sal,deptno
FROM emp
WHERE comm IS NULL;
任务19、查询20号部门工资大于2000 (包括2000)的员工的姓名,工资和部门号。
SELECT ename,sal,deptno
FROM emp
WHERE sal>=2000 AND deptno=20;
任务20、查询部门是20号的员工或者工资大于1500(包括1500)的员工姓名,工资和部门号。
SELECT ename,sal,deptno
FROM emp
WHERE sal>=1500 OR deptno=20;
任务21、查询部门在30号并且工资大于1000的员工或者工资大于2000的员工 的姓名,工资和部门号。
SELECT ename,sal ,deptno
FROM emp
WHERE deptno=30 AND
sal>1000 OR sal >2000;
任务22、 查询出不在10号部门和20号部门的员工姓名和部门号。
SELECT ename,deptno
FROM emp
WHERE deptno NOT IN (10,20);
任务23、查询出每个员工的薪水与部门,并按着部门编号升序排序,工资降序。
SELECT ename, sal, deptno
FROM emp
ORDER BY deptno, sal DESC;
任务24、查询出部门表的部门编号,部门名称,并按着部门编号降序排序。
SELECT deptno,dname
FROM dept
ORDER BY deptno DESC;
任务25、查询每个员工的姓名,工资,年薪(别名ysal), 并按年薪降序排序
SELECT ename, sal, sal*12 ysal
FROM emp
ORDER BY ysal DESC;
任务26、查询各个部门的员工的平均工资。
SELECT deptno, AVG(sal) avgsal
FROM emp
GROUP BY deptno;
任务27、查询各个部门各个职位的平均工资和最大工资。
SELECT deptno, job,AVG(sal) avgsal,max(sal) maxsal
FROM emp
GROUP BY deptno,job;
任务28、查询各个部门的平均工资,并按照平均工资升序排序。
SELECT deptno,AVG(sal)
FROM emp
GROUP BY deptno
ORDER BY AVG(sal);
任务29、查询各部门的平均工资和各岗位的平均工资,统计结果同时显示各岗位和各部门平均工资(显示多个分组的统计结果)。
SELECT deptno,job,AVG(sal)
FROM emp
GROUP BY GROUPING SETS(deptno,job);
任务30、查询各个部门工资小于2500的员工的平均工资和最高工资。
SELECT deptno,AVG(sal),MAX(sal)
FROM emp
GROUP BY deptno
HAVING AVG(sal)<2500;
任务31、列出至少有一个员工的所有部门。
SELECT deptno,count(*)
FROM emp
GROUP BY deptno
HAVING count(*) >= 1;
任务32 在SCOTT用户模式下,查询所有雇员姓名,要求将雇员的姓名全部转换成小写的形式显示
SELECT LOWER(ename) FROM emp;
任务33 在HR用户模式下查询姓名加起来的长度小于11位的记录(显示员工号,姓名,工资)
SELECT employee_id,first_name,last_name,salary
FROM employees
WHERE LENGTH(first_name||last_name)<11;
任务34 查询学生选修课成绩,要求四舍五入。
SELECT ROUND(grade) FROM t_scgrade;
任务35 查询员工工资, 显示整数,不用四舍五入
SELECT TRUNC(sal) FROM emp;
任务36 利用dual表,求17除以3的余数
SELECT MOD(17,3) FROM dual;
任务37 在HR用户模式下查询雇员信息不满120个月的雇员信息
SELECT * FROM employees WHERE MONTHS_BETWEEN(sysdate,hire_date)<=120;
任务38 查询下个月的日期
SELECT ADD_MONTHS(SYSDATE,1)
FROM dual;
任务39 在SCOTT用户模式下把emp表中hiredate列看做是员工的生日,求本月过生日的员工
SELECT * FROM emp
WHERE TO_CHAR(hiredate, 'mm') = TO_CHAR(sysdate , 'mm');
任务40 给一个工资为NULL的员工加500的奖金用什么方法。
使用转换函数nvl(exp1,exp2)处理空值null
UPDATE emp SET comm=nvl(comm,0)+500
WHERE comm IS NULL AND ename='SMITH';
COMMIT;
任务41 查询员工表前5条记录的rownum,员工姓名和部门号。
SELECT rownum, ename,deptno
FROM emp
WHERE rownum<=5;
任务42 计算某部门雇员的平均工资、最高工资、最低工资及工资总额
SELECT avg(sal),max(sal),min(sal),sum(sal) FROM emp WHERE deptno=&deptno;
任务43 对日期型数据使用MAX、MIN函数,查看受雇最短雇员与最长雇员的受雇时间
SELECT MAX(hiredate),MIN(hiredate) FROM emp;
任务44 对字符型数据使用MAX、MIN函数,查看获取数据的依据
SELECT MAX(ename),MIN(ename) FROM emp;
任务45 用COUNT查询一个表的总记录数
SELECT COUNT(*) FROM emp;
或者
SELECT COUNT(empno) FROM emp;
任务46 统计有奖金的员工人数
SELECT COUNT(comm) FROM emp
WHERE comm!=0;
或者
SELECT COUNT(*) FROM emp
WHERE comm!=0 AND comm IS NOT NULL;
任务47 查询员工表员工涨前与涨后工资情况,根据职位涨工资,职位为PRESIDENT的涨1000,职位是经理的涨800,其他涨400
SELECT empno,ename,job,sal 涨前,CASE job WHEN 'PRESIDENT' THEN sal+1000
WHEN 'MANAGER' THEN sal+800
ELSE sal+400
END 涨后
FROM emp;
或者
SELECT empno,ename,job,sal 涨前,CASE WHEN job='PRESIDENT' THEN sal+1000
WHEN job='MANAGER' THEN sal+800
ELSE sal+400
END 涨后
FROM emp;

任务48 利用学生选课成绩表使用CASE WHEN表达式给学生成绩进行评等级,成绩在60分一下显示‘不及格’,60-70之间显示‘及格‘ ,70-85分显示’良好‘85-100’(不包括100)显示‘优秀’,否则显示‘满分’。
SELECT sno,cno,grade ,CASE WHEN grade<60 THEN '不及格'
WHEN grade>=60 AND grade<=70 THEN ' 及格'
WHEN grade>70 AND grade<85 THEN '良好'
WHEN grade>=85 AND grade<100 THEN '优秀'
ELSE '满分'
END 等级
FROM t_scgrade;
任务49 利用学生选课成绩表使用DECODE函数给选修课程1001的学生成绩加5分,选修课程1002的学生成绩加3分,选修课程1003的学生成绩加2分,选修其他课程的加1分。
SELECT sno,cno,grade 加分前成绩,
DECODE(cno,1001,grade+5,1002,grade+3,1003,grade+2,grade+1)
加分后成绩
FROM t_scgrade

任务50 按雇员所在部门统计各部门内雇员的平均工资
SELECT deptno,AVG(sal) FROM emp
GROUP BY deptno;
任务51 统计各部门的人数(不算没有员工的部门)
SELECT deptno,COUNT(empno) FROM emp
GROUP BY deptno;
任务52 统计各个部门最低工资
SELECT deptno,MIN(sal) FROM emp
GROUP BY deptno;
任务53 统计每种工作的平均工资
SELECT job,MIN(sal) FROM emp
GROUP BY job;
任务54 检索部门号及其本部门的最低工资,并按着最低工资降序排序。
SELECT deptno,MIN(sal) minsal FROM emp
GROUP BY deptno
ORDER BY minsal DESC;
任务55 查询所有名字长度为4 的员工的员工编号,姓名
SELECT empno,ename FROM emp
WHERE LENGTH(ename)=4;
任务56 创建一学员表,有姓名,性别,班级编号三个字段,添加10条模拟数据,查询各班级中男女学生的人数
SELECT sex, count(sno) FROM t_student
GROUP BY sex;

任务57 利用之前建立的学生表,课程表,学生选修成绩表完成:
1)、统计每门课所有学生的总成绩。
SELECT cno,SUM(grade)
FROM t_scgrade
GROUP BY cno;

2)、 列出每个学生的平均成绩。
SELECT sno,AVG(grade)
FROM t_scgrade
GROUP BY sno;
3)、 列出各科的平均成绩、最高成绩、最低成绩和选课人数。按课程号升序排序。
SELECT cno,AVG(grade),MAX(grade),MIN(grade),COUNT(DISTINCT sno) FROM t_scgrade
GROUP BY cno
ORDER BY cno;

任务58 查询当前月有多少天
SELECT TRUNC(ADD_MONTHS(sysdate,1),'month') - TRUNC(sysdate,'month')
FROM dual;
任务59 求1981年各个月入职的的员工个数
SELECT count(empno)
FROM emp
WHERE TO_CHAR(hiredate,'yyyy')='1981'
GROUP BY TO_CHAR(hiredate,'mm')
ORDER BY TO_CHAR(hiredate,'mm');
任务60 查询统计部门员工人数大于3的部门的员工人数。
SELECT deptno,COUNT(empno) c
FROM emp
GROUP BY deptno
HAVING COUNT(empno) >3;
任务61 查看部门表的所有字段信息
SELECT * FROM dept;
任务62 查看部门里部门号和部门名称的字段信息内容
SELECT deptno,dname FORM dept;
任务63 查看员工表的所有员工的姓名,工资,奖金,部门号,包括伪列ROWID
SELECT ename ,sal ,comm,deptno, rowid
FROM emp;
任务64 查询显示员工姓名,工资和涨300元后的工资的信息。
SELECT ename, sal, sal + 300
FROM emp;
任务65 查询显示员工工资降10%后的年收入信息
SELECT ename, sal, sal*(1-0.1)*12+NVL(comm,0)
FROM emp;
任务66 查询所有员工入职试用期一个月后转正的信息,显示字段(姓名,入职时间,转正时间)。
SELECT ename,hiredate,hiredate+30
FROM emp;
任务68 查询部门表中deptno字段,dname字段,loc字段 ,使用中文作为别名。(loc字段表示部门所在地址)
SELECT deptno "部门号", dname "部门名称" , loc "部门所在地址"
FROM emp;
任务69 查询工资等级表salgrade 的所有信息,给工资等级表取一个别名。
SELECT grade,losal ,hisal
FROM salgrade sg;
任务70 在SCOTT用户模式下查询出员工表中每个员工的职位和入职时间,要求显示成字符串的形式,结果如下
SELECT ename||'的职位是'||job||',入职时间是'||hiredate
FROM emp;
任务71 在HR用户模式下查询出所有员工的全名,包括first_name和last_name 。
SELECT first_name||' .'||last_name
FROM employees;
任务72 查询所有员工所在的部门号,要求不重复。
SELECT DISTINCT deptno
FROM emp;
任务73 显示部门与薪水不重复的员工信息。
SELECT DISTINCT deptno, sal
FROM emp;
任务74 检索出工资大于1500的所有员工的员工姓名和工资。
SELECT ename, sal
FROM emp
WHERE sal>1500;
任务75 查询员工工资等于1500或3000的员工记录,显示这些员工的员工号,姓名,工资。
SELECT empno,ename,sal
FROM emp
WHERE sal = ANY(1500,3000);
任务76 使用ALL关键字过滤工资同时不等于3000、950和800的员工记录并显示这些员工的员工号,姓名,工资
SELECT empno,ename,sal
FROM emp
WHERE sal <>ALL(3000,950,800);
任务77 查询名字的第二个字母是‘S’的员工的员工姓名,工资和部门号。
SELECT ename,sal,deptno
FROM emp
WHERE ename LIKE '_S%';
任务78 查询有奖金的员工的员工号,姓名,工资,奖金。
SELECT empno,ename,sal,comm
FROM emp
WHERE comm IS NOT NULL AND comm!=0;
任务79 查询出在20号部门和30号部门的员工姓名和部门号。
SELECT ename,deptno
FROM emp
WHERE deptno IN(20,30);
任务80 查询出工资在2000到3000之间的员工的姓名和工资。
SELECT ename,sal
FROM emp
WHERE sal NOT BETWEEN 2000 AND 3000;
任务81 检索10号部门职位是'CLERK' 工资大于1000的员工的姓名和工资及部门号。
SELECT ename,sal,deptno
FROM emp
WHERE sal>1000 AND deptno=10 AND job='CLERK';
任务82 检索20号部门职位是经理或者工资大于3000的员工的姓名,工资和部门号。
SELECT ename,sal,deptno
FROM emp
WHERE sal>3000 OR deptno=20 AND job='MANAGER';
任务83 检索不在30号部门并且工资不在1500到3000的员工的姓名,工资和部门号。
SELECT ename,sal,deptno
FROM emp
WHERE deptno<>30 AND
sal NOT BETWEEN 1500 AND 3000;
任务84 查询出员工的姓名,职位,入职时间和部门号,按着入职时间和部门号升序排序。
SELECT ename,job,hiredate,deptno
FROM emp
ORDER BY hiredate,deptno;
任务85 查询出员工的姓名,年收入(起别名annualIncome),部门号,按着annualIncome 降序排序,部门也降序排序。
SELECT ename, sal*12+NVL(comm,0) as annualIncome ,deptno
FROM emp
ORDER BY annualIncome desc ,deptno desc;
任务86 统计10,20, 30 号部门的员工人数。
SELECT deptno,count(empno)
FROM emp
WHERE deptno IN(10,20,30)
GROUP BY deptno;
任务87 统计各个职位的员工人数和平均工资。
SELECT job,COUNT(empno),AVG(sal)
FROM emp
GROUP BY job;
任务88 查询各个部门各个岗位的平均工资。
SELECT deptno,job,AVG(sal)
FROM emp
GROUP BY deptno,job;
任务89 列出最低薪金大于1500的各种工作及从事此工作的全部雇员人数
SELECT job,COUNT(empno)
FROM emp
GROUP BY job
HAVING MIN(sal)>1500 ;
任务90 列出最高薪水小于3500的各个部门人数(不包括没有员工的部门)
SELECT deptno,COUNT(deptno)
FROM emp
GROUP BY deptno
HAVING MAX(sal)<3500;
任务91 从雇员表中选出部门编号为30的员工信息
SELECT * FROM emp WHERE deptno = 30;
任务92 检索出工资大于2000小于5000的雇员信息
SELECT ename,sal FROM emp WHERE sal BETWEEN 2000 AND 5000;
任务93 在条件中使用IN运算,查询在部门编码为10,30的部门工作的员工
SELECT empno,ename,deptno FROM emp WHERE deptno IN (10,30);
任务94 显示姓名中有“%N%”的雇员
SELECT empno,ename,sal,job,deptno FROM emp
WHERE ename LIKE '%\%N\%%' ESCAPE '\' ;
任务95 显示姓名以S开头的的雇员
SELECT empno,ename,sal,job,deptno FROM emp WHERE ename LIKE 'S%';
任务96 显示姓的第二个字母为“A”的的雇员
SELECT empno,ename,sal,job,deptno FROM emp WHERE ename LIKE '_A%';
任务97 显示姓名中没有'L'字的员工的详细信息或含有'SM'字的员工信息
SELECT * FROM emp
WHERE ename NOT LIKE '%L%' OR ename like '%SM%';
任务98 查出奖金为空的员工信息
SELECT * FROM emp WHERE comm IS NULL;
任务99 查询部门编号为20,并且工资少于1000的员工信息
SELECT * FROM emp WHERE deptno =20 AND sal<1000;
任务100 查询姓名为“ALLEN”,或者工作为“ANALYST”
SELECT * FROM emp WHERE ename ='ALLEN' OR job='ANALYST';
任务101 查出部门编号不为空的员工信息
SELECT * FROM emp WHERE deptno IS NOT NULL;
任务102 查询职位(JOB)为'PRESIDENT'的员工的工资
SELECT sal FROM emp WHERE job = 'PRESIDENT';
任务103 查询佣金(COMM)为0或为NULL的员工信息
SELECT * FROM emp WHERE comm = 0 OR comm IS NULL;
任务104 显示10 号部门的所有经理('MANAGER')和20号部门的所有职员('CLERK')的详细信息
SELECT * FROM emp
WHERE deptno = 10 AND job = 'MANAGER'
OR deptno = 20 AND job='CLERK';
任务105 检索部门号及其本部门的最低工资,并按着最低工资降序排序。
SELECT deptno,min(sal) FROM emp
GROUP BY deptno
ORDER BY min(sal) DESC;
任务106 列出最高薪水大于2500的各个部门人数
SELECT deptno,COUNT(deptno)
FROM emp
GROUP BY deptno
HAVING MAX(sal)>2500;
任务107 求1001号课成绩大于80分的学生的学号及成绩,并按成绩由高到低列出。
SELECT sno,grade FROM t_scgrade
WHERE cno='1001' AND grade>80
ORDER BY grade DESC;
任务108 查询成绩在70~80分之间的学生选课得分情况
SELECT sno,cno,grade FROM t_scgrade
WHERE grade BETWEEN 70 AND 80 ;
任务109 列出每门选修课的平均成绩。按着每门课平均成绩降序排列。
SELECT cno,AVG(grade) avggrade FROM t_scgrade
GROUP BY cno
ORDER BY avggrade DESC ;
任务110 列出成绩不为空值的学生的学号和课号。
SELECT sno,cno FROM t_scgrade
WHERE grade IS NOT NULL;
任务111 统计每门课及格的人数
SELECT cno,COUNT(sno) FROM t_scgrade
WHERE grade>=60
GROUP BY cno;
任务112 查询至少有两门课成绩都大于80的学生号,统计课程数
SELECT sno,COUNT(cno) FROM t_scgrade
WHERE grade>80
GROUP BY sno
HAVING COUNT(cno)>=2;
任务113 查询所有员工所在的部门的员工姓名和部门名称。
SELECT e.ename,d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno;
任务114 查询10号部门的所有员工信息,显示员工姓名,部门编号,部门名称。
SELECT e.ename,d.deptno,d.dname
FROM emp e, dept d
WHERE e.deptno=d.deptno
AND d.deptno=10;
任务115 显示销售部('SALES')员工的姓名
SELECT ename
FROM emp e, dept d
WHERE e.deptno = d.deptno and d.dname='SALES';
任务116 查询20号部门的员工的员工姓名,职位,平均工资,所在部门名称
SELECT e.ename ,e.job,AVG(e.sal),d.dname
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.deptno=20
GROUP BY e.ename,e.job,d.dname;
任务117 查询出员工的姓名,薪水及薪水等级。
SELECT e.ename,e.sal,s.grade
FROM emp e, salgrade s
WHERE e.sal BETWEEN s.losal AND s.hisal;
任务118 查询10号部门工资小于2000的员工的姓名,工资及工资等级。
SELECT e.ename,e.sal ,sg.grade
FROM emp e, salgrade sg
WHERE e.sal between sg.losal and sg.hisal
and e.deptno=10 and e.sal<2000;
任务119 查询工资等级处于第四级别的员工的姓名。
SELECT e.ename,e.sal
FROM emp e , salgrade sg
WHERE e.sal between losal AND hisal
AND sg.grade = 4;
任务120 在SCOTT的用户模式下用自然连接查询出所有员工的部门号,员工姓名,部门名称
SELECT deptno ,e.ename,d.dname
FROM emp e NATURAL JOIN dept d ;
任务121 在SCOTT模式下,通过deptno字段来内连接emp表和dept表,并检索这两个表中员工编号,员工姓名,部门名称的信息。
SELECT e.empno,e.ename,d.dname
FROM emp e INNER JOIN dept d
ON e.deptno=d.deptno;
任务122 使用内连接实现检索从事CLERK工作的员工姓名和所在部门名称
SELECT e.ename,d.dname
FROM emp e INNER JOIN dept d
ON e.deptno=d.deptno
WHERE e.job='CLERK';
任务123 使用内连接实现:按部门号(deptno) 及 工资(sal)排序检索公司的员工信息(要求部门号从大到小 ,部门号相同的按工资由低到高),显示的字段为:员工号、员工姓名、工资、部门号
SELECT e.empno,e.ename,e.sal,d.deptno
FROM emp e JOIN dept d
ON e.deptno=d.deptno
ORDER BY d.deptno DESC,e.sal;
任务124 显示BLAKE的上级领导
SELECT manager.ename
FROM emp manager, emp worker
WHERE manager.empno=worker.mgr
AND worker.ename=‘BLAKE’;
任务125 检索员工和所属领导的姓名。
SELECT e.ename,m.ename
FROM emp e, emp m
WHERE e.mgr = m.empno;
任务126 使用左外连接统计每个部门的员工人数,显示部门号,部门名称,员工人数。
SELECT d.deptno,
d.dname,count(e.empno)
FROM dept d
LEFT OUTER JOIN emp e
ON d.deptno=e.deptno
GROUP BY d.deptno,d.dname ;
任务127 使用右外连接统计每个部门的员工人数,显示部门号,部门名称,员工人数。
SELECT d.deptno,
d.dname,count(e.empno)
FROM emp e
RIGHT OUTER JOIN dept d
ON d.deptno=e.deptno
GROUP BY d.deptno,d.dname ;
任务128 使用完全外连接统计每个部门的员工人数。
SELECT d.deptno, count(e.empno)
FROM dept d FULL JOIN emp e
ON d.deptno = e.deptno
GROUP BY d.deptno;
任务129 查询没有员工的部门信息
SELECT d.*
FROM dept d LEFT JOIN emp e ON e.deptno =d.deptno
WHERE empno IS NULL;
或者
SELECT d.deptno,d.dname,d.loc,COUNT(e.empno)
FROM dept d LEFT JOIN emp e ON e.deptno =d.deptno
GROUP BY d.deptno ,d.dname,d.loc
HAVING COUNT(e.empno)=0;
任务130 在SCOTT模式下,通过deptno字段来交叉连接emp表和dept表,并检索这两个表中员工编号,员工姓名,部门名称的信息。
SELECT e.empno,e.ename,d.dname
FROM emp e CROSS JOIN dept d
WHERE e.deptno=d.deptno;
任务131 显示所有职员的姓名及其所在部门的名称和工资
SELECT ename, job, dname,sal
FROM emp e, dept d
WHERE e.deptno = d.deptno;
或者
SELECT ename, job, dname,sal
FROM emp e JOIN dept d ON e.deptno=d.deptno;
任务132 查询在研发部('RESEARCH')工作员工的编号,姓名,工作部门,工作所在地
SELECT empno,ename,dname,loc
FROM emp e, dept d
WHERE e.deptno = d.deptno AND d.dname='RESEARCH';
任务133 查询每个员工的信息及工资级别
SELECT e.*, sg.grade FROM emp e, salgrade sg
WHERE sal BETWEEN losal AND hisal;
任务134 查询选修1002课程的学生的学生姓名
SELECT s.sname
FROM t_student s, t_scgrade sc
WHERE s.sno = sc.sno
AND sc.cno = 1002;
任务135 查询Oracle不及格的学生姓名和成绩
SELECT s.sname, sc.grade
FROM t_student s, t_scgrade sc, t_course c
WHERE s.sno = sc.sno
AND c.cno = sc.cno
AND c.cname='Oracle'
AND sc.grade<60;
任务136 查询平均分不及格的学生的学号,姓名,平均分
SELECT s.sno,s.sname, AVG(sc.grade)
FROM t_student s, t_scgrade sc
WHERE s.sno = sc.sno
GROUP BY s.sno,s.sname
HAVING AVG(sc.grade)<60;
任务137 查询与SCOTT在同一部门的员工的姓名,薪水
SELECT ename,sal
FROM emp
WHERE deptno=(SELECT deptno FROM emp WHERE ename=‘SCOTT’); 任务138 子查询与主查询不是使用的同一张表:
任务139 查询部门名称为"RESEARCH"的员工信息(显示员工号,姓名,职位)
SELECT empno,ename,sal
FROM emp
WHERE deptno = (SELECT deptno FROM dept WHERE dname='RESEARCH');
任务140 检索与SCOTT从事相同工作的员工信息。
SELECT *
FROM emp
WHERE job=(SELECT job FROM emp WHERE ename='SCOTT'); 任务141 检索和名叫SCOTT的员工相同工资的员工信息,但不显示SCOTT。显示字段:员工号、员工名、工资。
SELECT empno,ename,sal
FROM emp
WHERE sal= (SELECT sal
FROM emp
WHERE ename='SCOTT')
AND ename NOT LIKE 'SCOTT';

任务142 在emp表中,查询出既不是最高工资,也不是最低工资的员工信息
SELECT empno,ename,sal
FROM emp
WHERE sal>(SELECT MIN(sal) FROM emp)
AND sal<(SELECT MAX(sal) FROM emp);
任务143 查询出工资低于所有员工平均工资的员工姓名和工资,按照工资高低降序排序SELECT ename ,sal
FROM emp
WHERE sal<(SELECT AVG(sal) FROM emp )
ORDER BY sal;

任务144 在SCOTT用户模式下查询出各部门员工的平均工资低于各部门最高平均工资的部门号和部门的平均工
SELECT deptno,AVG(sal) avgsal
FROM emp
GROUP BY deptno
HAVING AVG(sal)<( SELECT MAX(AVG(sal)) FROM emp GROUP BY deptno);

任务145 从学生选修成绩表t_scgrade查询出课程平均成绩低于课程最高平均值的课程编号和平均成绩。
SELECT cno,avg(grade)
FROM t_scgrade
GROUP BY cno
HAVING AVG(grade)<(SELECT MAX(AVG(grade))
FROM t_scgrade GROUP BY cno);
任务146 在HR用户模式下查询显示部门内最低工资比100号部门最低工资要高的部门编号以及部门内最低工资
SELECT department_id,MIN(salary)
FROM employees
GROUP BY department_id
HAVING MIN(salary)>(SELECT MIN(salary)
FROM employees
WHERE department_id=100);
任务147 列出薪金与部门30中员工的薪金相同的所有员工的姓名和薪金。
SELECT ename,sal
FROM emp
WHERE sal IN (SELECT sal FROM emp WHERE deptno = 30);
任务148 使用标准嵌套子查询列出薪水与销售部门在同部门的员工薪水相同的所有员工的姓名和薪金。
SELECT ename,sal
FROM emp
WHERE sal IN (SELECT sal
FROM emp
WHERE deptno = (SELECT
FROM dept
WHERE dname = 'SALES')
);
任务149 列出至少有两个员工的所有部门信息
SELECT *
FROM dept
WHERE deptno IN(SELECT deptno
FROM emp
GROUP BY deptno
HAVING COUNT(*)>1);
任务150 在HR用户模式下查询工资与任何一个部门最低工资相等的雇员姓名,工资
SELECT first_name||' '||last_name,salary
FROM employees
WHERE salary IN(SELECT MIN(salary)
FROM employees
GROUP BY department_id);
任务151 使用SQL标准嵌套子查询查询选修了课程名称为“Oracle”的学生学号和姓名。
SELECT sno,sname
FROM t_student
WHERE sno IN( SELECT sno
FROM t_scgrade
WHERE cno=(SELECT cno
FROM t_course
WHERE cname='Oracle'));

任务152 在emp表中,查询工资大于部门编号为10的任意一个员工工资的其他部门的员工信息
SELECT deptno,ename,sal
FROM emp
WHERE sal>ANY(SELECT sal
FROM emp
WHERE deptno=10)
AND deptno<>10;
任务153 在HR用户模式下显示只要比职位ID为'IT_PROG'的工资低但又不是从事IT工作的雇员姓名,职位ID,工资
SELECT first_name||last_name,job_id,salary
FROM employees
WHERE salary FROM employees
WHERE job_id=‘IT_PROG’)
AND job<>’IT_PROG’;

任务154 显示工资大于所有部门平均工资的雇员姓名,工资
SELECT ename,sal
FROM emp
WHERE sal>ALL(SELECT AVG(sal) FROM emp GROUP BY deptno);
任务155 查询工资大于部门编号为20的所有员工工资的员工信息
SELECT ename,sal,deptno
FROM emp
WHERE sal>ALL(SELECT sal FROM emp WHERE deptno = 20);
任务156 查询在“SALES"销售部门的所有员工信息。(使用EXISTS)
SELECT *
FROM emp e
WHERE EXISTS (SELECT deptno
FROM dept d
WHERE e.deptno = d.deptno
AND d.dname='SALES');
任务157 查询在NEW YORK工作的所有雇员的名字、职位、薪水、所在部门
SELECT ename,job,sal,deptno
FROM emp
WHERE EXISTS(SELECT 1 FROM dept
WHERE dept.deptno=emp.deptno
AND dept.loc='NEW YORK');
任务158 在SCOTT用户模式下查询显示和ALLEN同部门同职位的员工姓名、职位、部门编号
SELECT ename,job,deptno
FROM emp
WHERE (deptno,job)=(SELECT deptno,job
FROM emp
WHERE ename = 'ALLEN');

任务159 查询显示和 ADAMS 同部门且同工作岗位的员工姓名、工作岗位、工资、部门编号
SELECT ename, job,sal,deptno
FROM emp
WHERE (deptno,job)=(SELECT deptno,job
FROM emp
WHERE ename='ADAMS ');
任务160 在HR用户模式下查询显示和 Adam 同部门且同工作岗位的员工姓名、工作岗位、工资、部门编号
SELECT first_name, job_id,salary,department_id
FROM employees
WHERE (department_id,job_id)=(SELECT department_id,job_id
FROM employees
WHERE first_name='Adam');
任务161 在emp表中,使用“关联子查询”检索工资大于同职位的平均工资的员工信息(显示字段:员工编号,姓名,工资)。
SELECT empno,ename,sal
FROM emp e
WHERE sal>(SELECT AVG(sal) FROM emp WHERE job=e.job);
任务162 查询所有大于本部门平均工资的员工信息
SELECT * FROM emp e WHERE sal >(SELECT avg(sal)
FROM emp
WHERE deptno = e.deptno);
任务163 创建一个和员工表一样的表,结构与数据都一样。
CREATE TABLE emp1 AS
SELECT * FROM emp;
任务164 创建一个和员工表结构一样的空表。
CREATE TABLE emp2 AS
SELECT * FROM emp
WHERE 1=2;
任务165 创建一个和部门表一样的表,结构与数据都一样。
CREATE TABLE dept1 AS
SELECT * FROM dept;
任务166 创建一个和部门表结构一样的空表。
CREATE TABLE dept2 AS
SELECT * FROM dept
WHERE 1=2;
任务167 将20号部门的员工信息插入新的员工表emp2中 (emp2与emp结构一样,没有数据)
INSERT INTO emp2(empno,ename,sal,deptno)
SELECT empno,ename,sal,deptno FROM emp WHERE deptno=20;
任务168 向新表dept2里批量插入dept表里的数据。(dept2是与dept结构一样的空表)
INSERT INTO dept2
SELECT * FROM dept;
任务169 将emp1表里JAMES的职位,薪水,奖金更改为和SCOTT一样的职位,薪水,奖金UPDATE emp1 SET(job,sal,comm)=(SELECT job,sal,comm
FROM emp1
WHERE ename='SCOTT')
WHERE ename='JAMES';
任务170 将emp1里SCOTT的职位,薪水更改为和emp表中员工编号为7369一样的职位,薪水。
UPDATE emp1 SET (job,sal)=(SELECT job,sal
FROM emp
WHERE empno=7369)
WHERE ename='SCOTT';
任务171 查询高于部门平均工资的员工信息(姓名和工资)
SELECT ename,sal
FROM emp, (SELECT deptno,avg(sal) avgsal
FROM emp
GROUP BY deptno) d
WHERE emp.deptno=d.deptno AND sal>d.avgsal;
任务172 按照工资由高到低进行排序,获取第5~10条记录
SELECT rownum,g.*
FROM (SELECT ROWNUM rn,t.*
FROM (SELECT sal
FROM emp
ORDER BY sal DESC) t
) g
WHERE rn>=5 AND rn<=10;
任务173 检索部门编号、部门名称、部门所在地及其每个部门的员工总数
SELECT d.deptno,d.dname,d.loc,e.count
FROM dept d ,(SELECT deptno,COUNT(empno) count
FROM emp GROUP BY deptno) e
WHERE e.deptno = d.deptno;
任务174 查询工资最高的3名员工信息
SELECT * FROM (SELECT * FROM emp ORDER BY sal DESC)
WHERE ROWNUM <= 3;
任务175 任务检索出员工表emp中的第3条到第8条记录。显示字段:emp表中的全部字段SELECT *
FROM (SELECT ROWNUM num,e.* FROM emp e)
WHERE num>=3 and num<=8;
任务176 员工表emp中有14条记录,假设分页显示,每页最多显示3条记录,显示第4页的记录。
SELECT * FROM (SELECT ROWNUM rn,ename ,job,deptno
FROM emp) t
WHERE t.rn>3*(4-1) AND t.rn<=3*4;
任务177 已创建好一个“student1”表,字段有学号s_id,姓名s_name,性别sex,联系方式phone,里面有100条仿真记录,要求:分页查询,一页20条记录,查询第3页的记录。
SELECT *
FROM (SELECT ROWNUM r,s_id,s_name,sex,phone
FROM student1) t
WHERE t.r>20*(3-1)
AND t.r<=20*3;

任务178 查询工资大于2500或者职位为经理的员工的姓名,工资,职位。(使用UNION)
SELECT ename,sal,job FROM emp WHERE sal>2500
UNION
SELECT ename,sal,job FROM emp WHERE job='MANAGER';
任务179 查询工资大于2500或者职位为经理的员工的姓名,工资,职位。(使用UNION ALL)
SELECT ename,sal,job FROM emp WHERE sal>2500
UNION ALL
SELECT ename,sal,job FROM emp WHERE job='MANAGER';

任务180 查询工资大于2500并且职位为经理的员工的姓名,工资,职位。
SELECT ename,sal,job FROM emp WHERE sal>2500
INTERSECT
SELECT ename,sal,job FROM emp WHERE job='MANAGER';
任务181 查询工资大于2500但是职位不是经理的员工的姓名,工资,职位。
SELECT ename,sal,job FROM emp WHERE sal>2500
MINUS
SELECT ename,sal,job FROM emp WHERE job='MANAGER';
任务182 使用集合操作查询既选修了1001课程,又选修了1003课程的学生姓名
SELECT sname FROM t_student s, t_scgrade sc
WHERE s.sno = sc.sno AND sc.cno = 1001
INTERSECT
SELECT sname FROM t_student s, t_scgrade sc
WHERE s.sno = sc.sno AND sc.cno = 1003;
任务183 使用集合操作查询选修了1002课程没有选修1004课程的学生姓名
SELECT sname FROM t_student s, t_scgrade sc
WHERE s.sno = sc.sno AND sc.cno = 1002
MINUS
SELECT sname FROM t_student s, t_scgrade sc
WHERE s.sno = sc.sno AND sc.cno = 1004;
任务184 使用集合操作查询选修了1001课程或选修了1005课程的学生姓名
SELECT sname FROM t_student s, t_scgrade sc
WHERE s.sno = sc.sno AND sc.cno = 1001
UNION
SELECT sname FROM t_student s, t_scgrade sc
WHERE s.sno = sc.sno AND sc.cno = 1005;
任务185 在HR用户模式下:查询出比编号151雇员工资高的其他雇员
SELECT * FROM employees
WHERE salary>(SELECT salary
FROM employees
WHERE employee_id=151
);
任务186 在HR用户模式下:显示和雇员151从事相同工作并且工资大于雇员123的雇员的姓名和工作
SELECT * FROM HR.EMPLOYEES
WHERE job_id = (SELECT job_id FROM HR.EMPLOYEES WHERE employee_id = 151)
AND salary >(SELECT salary FROM HR.EMPLOYEES WHERE employee_id = 123);
任务187 显示工资最低的雇员的姓名、工作、和工资
SELECT ename,job,salary
FROM employees
WHERE salary= (SELECT MIN(salary)
FROM employees);
任务188 从学生表t_student和学生选课成绩表t_scgrade中查询出选修了课程编号1003的学生编号和姓名。
SELECT sno,sname
FROM t_student
WHERE sno IN (SELECT sno FROM t_scgrade WHERE cno=1003 );
任务189 查询大于课程编号为1002中任意一个成绩的其他成绩信息
SELECT grade
FROM t_scgrade
WHERE grade>ANY(SELECT grade
FROM t_scgrade
WHERE cno = 1002);
任务190 查询大于课程编号为1002中所有成绩的其他成绩信息
SELECT grade
FROM t_scgrade
WHERE grade>ALL(SELECT grade
FROM t_scgrade
WHERE cno = 1002);

任务191 查询与SMITH所在部门和职务相同的员工信息
SELECT ename,job,sal,deptno
FROM emp
WHERE (deptno,job)=(SELECT deptno,job FROM emp WHERE ename='SMITH');
任务192 显示BLAKE同部门的所有员工,但不显示BLAKE

任务193 查询与10号部门员工职位相同的员工信息。
SELECT ename,job,sal,deptno FROM emp
WHERE job IN (SELECT DISTINCT job
FROM emp
WHERE deptno=10);
任务194 显示超过平均工资的所有员工名、工资和部门号
SELECT ename,sal,deptno
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp );
任务195 显示超过部门平均工资的所有员工名、工资和部门号
SELECT ename,sal,deptno
FROM emp
WHERE sal>ANY(SELECT AVG(sal) FROM emp GROUP BY deptno);
任务196 显示高于CLERK岗位所有雇员工资的所有雇员名、工资和岗位
SELECT ename,sal,job
FROM emp
WHERE sal>ALL(SELECT sal FROM emp WHERE job='CLERK');
任务197 显示工资、补助与SCOTT完全一致的所有雇员名、工资和补助
SELECT ename,sal,NVL(comm,0)
FROM emp
WHERE (sal,NVL(comm,0))=(SELECT sal,NVL(comm,0) FROM emp WHERE ename='SCOTT');
任务198 查询比10号部门员工工资高的员工信息(姓名,薪水,部门号)
SELECT ename,sal,deptno
FROM emp
WHERE sal> ANY(SELECT sal
FROM emp
WHERE deptno=10);
任务199 查询比30号部门所有员工工资高的员工信息(姓名,薪水,部门号)
SELECT ename,sal,deptid
FROM emp
WHERE sal> ALL(SELECT sal
FROM emp
WHERE deptno=30);
任务200 查询各个职位员工工资大于平均工资(平均工资包括所有员工)的人数和员工职位
SELECT job,COUNT(empno)
FROM emp
WHERE sal>(SELECT AVG(sal) FROM emp)
GROUP BY job;
任务201 求入职日期相同的(年月日相同)的员工
SELECT * FROM emp e
WHERE (SELECT COUNT(*) FROM emp WHERE e.hiredate=hiredate)>1;
或者
SELECT * FROM emp
WHERE hiredate IN(SELECT hiredate
FROM emp GROUP BY hiredate
HAVING COUNT(empno)>1
);
任务202 查询工资最高的第4-8名员工
SELECT * FROM (SELECT rownum rn ,e.*
FROM (SELECT * FROM emp ORDER BY sal DESC) e
WHERE rownum <=8)
WHERE rn >=4;
或者
SELECT dd.* FROM (SELECT rownum m,tt.*
FROM (SELECT * FROM emp ORDER BY sal DESC) tt ) dd
WHERE m>=4 AND m<=8;

任务203 查询出有2个以上下属的员工信息
SELECT * FROM emp e
WHERE (SELECT COUNT(*) FROM emp WHERE e.empno = mgr) >= 2;
或者
SELECT *
FROM (SELECT mgr,COUNT(empno) count FROM emp GROUP BY mgr ) x ,emp e
WHERE x.mgr=e.empno AND count.a>=2;
任务204 查询所有大于本部门平均工资的员工信息,按部门号升序排序
SELECT * FROM emp e
WHERE sal >(SELECT avg(sal) FROM emp WHERE deptno = e.deptno)
ORDER BY deptno;
或者
SELECT e.*
FROM emp e,(SELECT deptno,avg(sal) avgsal FROM emp GROUP BY deptno ) x
WHERE x.deptno=e.deptno and e.sal>x.avgsal
ORDER BY e.deptno;
任务205 查询大于各部门总工资的平均值的部门下的员工信息
SELECT e.*,sumsal
FROM emp e, (SELECT SUM(sal) sumsal, deptno FROM emp GROUP BY deptno) se
WHERE sumsal >(SELECT AVG(sum(sal)) FROM emp GROUP BY deptno)
AND se.deptno=e.deptno;

任务206 列出薪金高于公司平均薪金的所有员工,所在部门,上级领导,公司的工资等级
SELECT e.empno,e.ename,d.dname,m.ename,s.grade
FROM emp e,dept d,emp m,salgrade s
WHERE e.sal>(SELECT AVG(sal) FROM emp)
AND e.mgr=m.empno
AND d.deptno=e.deptno(+)
AND e.sal BETWEEN s.losal AND s.hisal ;

任务207 统计所有学生选修了课程的学生人数。
SELECT COUNT(DISTINCT sno) FROM t_scgrade ;

任务208 统计每门课程的学生选修人数(超过5人的课程才统计)。要求显示课程号和人数,查询结果按人数降序排列,若人数相同,按课程号升序排列。
SELECT t.c cn, t.count_s cs
FROM (SELECT sc.cno c, COUNT(sc.sno) count_s
FROM t_student s, t_scgrade sc WHERE s.sno = sc.sno
GROUP BY sc.cno HAVING COUNT(sc.sno) >5 ) t
ORDER BY cs DESC , cn ASC;

任务209 创建一个与学生表t_student结构一样的空表t_stu,然后向该表中批量插入学生表t_student里的男生记录数据
CREATE TABLE t_stu AS SELECT * FROM t_student WHERE 1=2;
INSERT INTO t_stu SELECT * FROM t_student WHERE sex='男';

任务210 客户表a(id name address) 登陆流水表b(id time) 购物流水表c(id time productid productnum)
1. 求每个客户的最新登陆时间time,姓名name,客户id?
SELECT a.id,a.name,d.maxtime AS time
FROM a LEFT JOIN (SELECT id,MAX(time) AS maxtime FROM b GROUP BY id) d
ON a.id =d.id ;
2.查最新登陆并且已经购买商品的客户id,name,登陆的时间time(一条sql语句)
SELECT a.id,a.name,d.time AS time
FROM a,(SELECT id,MAX(time) as time FROM b GROUP BY id) d
WHERE a.id =d.id
AND EXISTS (SELECT * FROM c WHERE id = a.id);

给我留言

留言无头像?