MySQL试题

create table dept( dept no varchar(IO) primary key,dn ame varchar(10))；create table emp(emp no varchar(10) primary key,en ame varchar(IO),job varchar(IO),mgr varchar(10),sal varchar(10),dept no varchar(10) referen ces dept(dept no));insert into dept values ('1',' 事业部');insert into dept values ('2',' 销售部');insert into dept values ('3',' 技术部');in sert into emp values ('01','jacky','clerk','tom','1000','1');in sert into emp values ('02','tom','clerk',",'2000','1');in sert into emp values ('07','biddy','clerk',”,'2000','1');in sert into emp values ('03','je nn y','sales','pretty','600','2');in sert into emp values ('04','pretty','sales',",'800','2');in sert into emp values ('05','buddy','jishu','ca nn dy','1000','3');in sert into emp values ('06','ca nn dy','jishu',",'1500','3'); select * from dept;select * from emp;--1列出emp表中各部门的部门号，最高工资，最低工资fromselect dept no as 咅部门号,max(sal) as 最高工资，min( sal) as 最低工资emp group by dept no;--2列出emp表中各部门job为'CLERK'的员工的最低工资，最高工资select max(sal) as 最高工资，min(sal) as 最低工资，dept no as咅部门号from emp where job='clerk' group by dept no;--3对于emp中最低工资小于2000的部门，列出job为'CLERK'的员工的部门号，最低工资，最高工资select b.deptno as 咅咅门号,max(sal) as 最高工资，min(sal) as 最低工资from emp as bwhere job='clerk' and (select min( sal)from emp as a where a.dept no=b.dept no )<2000 group by b.dept no;--4根据部门号由高而低，工资有低而高列出每个员工的姓名，部门号，工资select en ame as 姓名,dept no as 咅咅门号,sal as 工资from emp order by dept no desc,salasc;--5列出'buddy'所在部门中每个员工的姓名与部门号select b.ename as 姓名,b.deptno as 咅咅门号from emp as b where b.deptno=(select a.dept no from emp as a where a.e name='buddy');--6列出每个员工的姓名，工作，部门号，部门名select ename as 姓名,job as 工作,dept.deptno as 咅咅门号,dept.dname as 咅咅门名from emp,deptwhere emp.dept no=dept.dept no;--7列出emp中工作为'CLERK'的员工的姓名，工作，部门号，部门名select ename as 姓名,job as 工作,dept.deptno as 咅咅门号,dept.dname as 咅咅门名from emp,deptwhere emp.dept no=dept.dept no and job='clerk';--8对于emp中有管理者的员工，列出姓名，管理者姓名(管理者外键为mgr) select a.deptno as 咅咅门号,a.ename as 员工,b.ename as 管理者from emp as a,emp as b where a.mgr is not n ull and a.mgr=b.e name;--9对于dept表中，列出所有部门名，部门号，同时列出各部门工作为'CLERK'的员工名与工作select a.deptno as 咅咅门号,a.dname as 咅咅门名,b.ename as 员工名,b.job as 工作fromdept as a,emp as b where a.dept no=b.dept no and b.job='clerk：--10对于工资高于本部门平均水平的员工，列出部门号，姓名，工资，按部门号排序select b.deptno as 咅部门号,b.ename as 姓名,b.sal as 工资from emp as bwhere b.sal>(select avg(a.sal) from emp as a where a.dept no=b.dept no) order byb. dept no;--11对于emp，列出各个部门中工资高于本部门平均工资的员工数和部门号，按部门号排序select a.deptno as 咅咅门号,count(a.sal) as 员工数from emp as awhere a.sal>(select avg(b.sal) from emp as b where a.dept no=b.dept no) group bya.dept no orderby a.dept no;--12对于emp中工资高于本部门平均水平，人数多与1人的，列出部门号，人数，平均工资，按部门号排序select count(a.empno) as 员工数,a.deptno as 咅咅门号,avg(sal) as 平均工资from emp as a where (select coun t(c.emp no) from emp as c where c.dept no=a.dept no andc. sal>(select avg(sal) from emp as b where c.dept no=b.dept no) )>1group by a.dept no order by a.dept no;--13对于emp中低于自己工资至少5人的员工，列出其部门号，姓名，工资，以及工资少于自己的人数select a.deptno as 咅咅门号,a.ename as 姓名,a.sal as 工资,(select count(b.ename) fromemp as bwhere b.sal<a.sal) as 人数from emp as awhere (select coun t(b.e name) from emp as b where b.sal<a.sal)>=5/*题目2问题描述：为管理学员培训信息，建立3个表：S (SID,SN,SD,SA) SID,SN,SD,SA 分别代表学号、学员姓名、所属单位、学员年龄C (CID,CN ) CID,CN 分别代表课程编号、课程名称SC ( SID,CID,G ) S#,C#,G 分别代表学号、所选修的课程编号、学习成绩*/create table s(sid VARCHAR(IO) PRIMARY KEY,sn varchar(1O),sd varchar(10),sa varchar(10));create table c(cid varchar(10) primary key,cn varchar(10));create table sc(sid VARCHAR(10) REFERENCES S(SID),cid VARCHAR(10) REFERENCES C(CID), g int);in sert into c values('O1',' 化学');in sert into c values('O2',' 语文');insert into c values('O3',' 数学');in sert into s values('001',' in sert into s values('002',' in sert into s values('003',' in sert into s values('004',' in sert into s values('005',' select * from s;汪洋','计算机学院','25'); 王世俊','计算机学院','23');王鑫','软件学院','22');陈桂超','化学学院','23');通讯生','软件学院','25');in sert into c values('O4',' 英语');select * from c;in sert into sc values('001','01',70);in sert into sc values('001','02',80);in sert into sc values('001','03',90);in sert into sc values('001','04',100);in sert into sc values('002','01',80);in sert into sc values('002','02',90);in sert into sc values('003','01',60);in sert into sc values('004','02',70);in sert into sc values('003','03',50);in sert into sc values('004','04',60);select * from sc;1. 使用标准SQL嵌套语句查询选修课程名称为语文'的学员学号和姓名和单位--实现代码：select sid as 学号,sd AS 单位，sn AS 姓名from s where sid in (select sid from c,sc wherec.cid=sc.cid and =' 语文');2. 使用标准SQL嵌套语句查询选修课程编号为’(的学员姓名和所属单位--实现代码：select sn as 姓名,sd as 单位from s,sc where s.sid=sc.sid and sc.cid='02';3. 使用标准SQL嵌套语句查询不选修课程编号为’0的学员姓名和所属单位--实现代码：select sn as 姓名,sd as 单位from s where sid not in(select sid from sc where sc.cid='02');4. 使用标准SQL嵌套语句查询选修全部课程的学员姓名和所属单位--实现代码：select sn,sd from s where sid in (select sid from sc group by sid havi ng cou nt(*)=(select coun t(*) from c));5. 查询选修了课程的学员人数--实现代码：select count(distinet sid) as 人数from sc ;6. 查询选修课程超过2门的学员学号，。