3.14 作业一和作业二
本帖最后由 宝安22班-朱文峰 于 2022-3-16 10:37 编辑作业一
简单3表查询
#1.查询出学习成绩及格以上的学生姓名与成绩；
select sname,grade
from A
left join B
on A.sid=B.sid
where grade>60;
#2.查询姓名以ny结尾的学生姓名及其任课老师姓名；
select sname,teacher
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where sname like'%ny';
#3.选修课名为Maths的学生学号与姓名;
select A.sid,sname
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where cname='Maths';
#4.选修课号为C2和C4的学生学号；
select A.sid
from A
left join B
on A.sid=B.sid
where cid in('C2','C4');
#5.请问没有参加考试的学生的姓名及其学号；
select A.sid,sname
from A
left join B
on A.sid=B.sid
where grade is null;
#6.请问总分在80分以上的学生姓名；
select sname
from A
where A.sid in (select sid from B group by sid having sum(grade)>80) group by A.sid;
#7.请问考试不及格的考生姓名以及科目名称；
select sname,cname
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where grade<60;
#8.找出Dany的所有考试后成绩中最高的科目的任课老师姓名；
select teacher
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where C.cid in(select cid from B group by sid having sid=(select sid from A where sname='Dany') and max(grade));
#9.各个科目的平均成绩各是多少；
select cname,avg(grade)
from B
left join C
on B.cid=C.cid
group by B.cid;
#10.由于录入员的失误，导致TOM同学有成绩不及格的科目，请帮忙将其合理修正；
update B
set grade=60
where grade<60 and sid=(select sid from A where sname='TOM');
#11.显示出参加考试的学生的学号和姓名；
select A.sid,sname
from A
left join B
on A.sid=B.sid
where grade is not null group by B.sid;
#12.请问TOM一共参加了几门科目的考试；
select count(B.cid)
from A
left join B
on A.sid=B.sid
where sname='TOM';
#13.期末，DBA发现学生表中没有班级字段，请帮忙将其合理修正；
alter table A add (class char);
#14.学校新招了1位美术老师叫“白雪”，请在课程表中增加一条相应的记录，课程编号为C5；
insert into C values('C5','Art','baixue');
#15.赵姓的老师很早就离职了，然而课程表中却还有其上课的记录，请帮忙删除；
delete from C where teacher like 'zhao%';
#16.请求出TOM同学所有任课老师的名字及其所教的课程名；
select teacher,C.cname
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where A.sname='TOM'
#17.求每个同学的各科(总分或者平均)成绩，并以每一个人的成绩的先大后小的顺序显示；
select sname,sum(grade)
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
group by sname order by sum(grade) desc;
#18.请问张老师教什么课程，以及他班上所有学生的姓名；
select cname,sname
from A
left join B
on A.sid=B.sid
left join C
on B.cid=C.cid
where teacher like 'zhang%';
#作业二
单表查询
#1、查找部门30中员工的详细信息。
select *
from EMP
where deptno=30
#2、找出从事职员工作的员工的编号、姓名、部门号。
select empno,ename,deptno
from EMP
where job='职员';
#3、检索出奖金多于基本工资的员工信息。
select *
from EMP
where comm>sal;
#4、检索出奖金多于基本工资60%的员工信息。
select *
from EMP
where comm >(sal*0.6);
#5、找出10部门的经理、20部门的职员 的员工信息。
select *
from EMP
where (deptno=10 and job='经理')or(deptno=20 and job='职员');
#6、找出10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。
select *
from EMP
where (deptno=10 and job='经理')or(deptno=20 and job='职员')or sal>2000;
#7、找出获得奖金的员工的工作。
select *
from EMP
where comm is not null;
#8、找出奖金少于100或者没有获得奖金的员工的信息。
select *
from EMP
where comm is null or comm<100;
#9、找出姓名以A、B、S开始的员工信息.
select *
from EMP
where ename like 'A%' or ename like 'B%' or ename like 'S%';
#10、找到名字长度为6个字符的员工信息。
select *
from EMP
where length(ename)=6;
#11、名字中不包含R字符的员工信息。
select *
from EMP
where ename not like'%R%';
#12、查找员工的详细信息并按姓名排序。
select *
from EMP
order by ename ;
#13、返回员工的信息并按工作降序工资升序排列。(升序的规则)
select *
from EMP
order by job desc,sal;
#14、计算员工的日薪(按30天)。
select ename,sal/30
from EMP;
#15、找出姓名中包含A的员工信息。
select *
from EMP
where ename like'%A%';
多表查询
#1、返回拥有员工的部门名、部门号。
select DEPT.deptno,dname
from DEPT
left join EMP
on DEPT.deptno=EMP.deptno
where empno is not null
group by DEPT.deptno;
#2、工资水平多于smith的员工信息。
select *
from EMP
where sal>(select sal from EMP where ename='smith');
#3、返回员工和所属经理的姓名。
select a.ename,b.ename
from EMP as a
left join EMP as b
on a.mgr=b.empno;#自关联
#4、返回雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。
select a.ename,b.ename
from EMP as a
left join EMP as b
on a.mgr=b.empno
where a.hiredate<b.hiredate;
#5、返回员工姓名及其所在的部门名称。
select ename,dname
from EMP
left join DEPT
on EMP.deptno=DEPT.deptno;
#6、返回从事clerk工作的员工姓名和所在部门名称。
select ename,dname
from EMP
left join DEPT
on EMP.deptno=DEPT.deptno
where job='销售员';
#7、返回部门号及其本部门的最低工资。
select deptno,min(sal)
from EMP
group by deptno;
#8、返回销售部(sales)所有员工的姓名。
select ename
from EMP
left join DEPT
on EMP.deptno=DEPT.deptno
where dname='sales';
#9、返回工资水平多于平均工资的员工。
select *
from EMP
where sal>(select avg(sal) from EMP);
#10、返回与SCOTT从事相同工作的员工。(职员)
select *
from EMP
where job=(select job from EMP where ename='SCOTT');
#11、返回与30部门员工工资水平相同的员工姓名与工资。
select ename,sal
from EMP
where sal in(select sal from EMP where deptno=30);
#12、返回工资高于30部门所有员工工资水平的员工信息
select *
from EMP
where sal>(select max(sal) from EMP where deptno=30);
#13、返回部门号、部门名、部门所在位置及其每个部门的工员总数。
select DEPT.deptno,dname,loc,count(EMP.empno)
from EMP
left join DEPT
on EMP.deptno=DEPT.deptno
group by EMP.deptno;
#14、返回员工的姓名、所在部门名及其工资。
select ename,dname,sal
from EMP
left join DEPT
on EMP.deptno=DEPT.deptno;
#15、返回员工的详细信息。(包括部门名)
select EMP.*,dname
from EMP
left join DEPT
on EMP.deptno=DEPT.deptno;
#16、返回员工工作及其从事此工作的最低工资。
select job ,min(sal)
from EMP
group by job;
#17、计算出员工的年薪，并且以年薪排序。
select ename,sal*12
from EMP
order by sal*12 desc;
#18、返回工资处于第四级别的员工的姓名。
select ename
from EMP,SALGRADE
where sal>=losal and sal<=hisal and grade=4; #无条件查询
#19、返回工资为二等级的职员名字、部门所在地、和二等级的最低工资和最高工资
select ename,loc,max(sal),min(sal)
from SALGRADE,EMP
left join DEPT
on EMP.deptno=DEPT.deptno
where sal>=losal and sal<=hisal and grade=2 group by ename;
#20.工资等级多于smith的员工信息。
select empno,ename,job,mgr,hiredate,sal,comm,deptno
from EMP,SALGRADE
where grade>(select grade from EMP,SALGRADE where sal>=losal and sal<=hisal and ename='smith') group by empno;
页:
[1]