千纸鹤

  博客园  ::  :: 新随笔  ::  ::  :: 管理
  5 随笔 :: 70 文章 :: 0 评论 :: 9301 阅读
《0042》第四章 数据库应用：mysql数据库（二）_作业答案 
《一》部门表 && 员工表 && 工资等级表：
--新建部门表CREATE TABLE DEPT(
         DEPTNO int PRIMARY KEY, #部门编号
         DNAME VARCHAR(14) , #部门名称
         LOC VARCHAR(13) #部门地址
)ENGINE=INNODB CHARSEt=utf8
--插入数据
INSERT INTO DEPT VALUES (10,'ACCOUNTING','NEW YORK');
INSERT INTO DEPT VALUES (20,'RESEARCH','DALLAS');
INSERT INTO DEPT VALUES (30,'SALES','CHICAGO');
INSERT INTO DEPT VALUES (40,'OPERATIONS','BOSTON');

--新建员工表
CREATE TABLE EMP(
      EMPNO int PRIMARY KEY, #员工编号
      ENAME VARCHAR(10), #员工姓名
      JOB VARCHAR(9), #员工工作
      MGR int, #员工直属领导编号
      HIREDATE DATE, #入职时间
      SAL double, #工资
      COMM double, #奖金
      DEPTNO int #对应dept表的外键
)ENGINE=INNODB CHARSEt=utf8
ALTER TABLE EMP ADD CONSTRAINT FOREIGN KEY EMP(DEPTNO) REFERENCES DEPT (DEPTNO);
--插入数据
INSERT INTO EMP VALUES(7369,'SMITH','CLERK',7902,"1980-12-17",800,NULL,20);
INSERT INTO EMP VALUES(7499,'ALLEN','SALESMAN',7698,'1981-02-20',1600,300,30);
INSERT INTO EMP VALUES(7521,'WARD','SALESMAN',7698,'1981-02-22',1250,500,30);
INSERT INTO EMP VALUES(7566,'JONES','MANAGER',7839,'1981-04-02',2975,NULL,20);
INSERT INTO EMP VALUES(7654,'MARTIN','SALESMAN',7698,'1981-09-28',1250,1400,30);
INSERT INTO EMP VALUES(7698,'BLAKE','MANAGER',7839,'1981-05-01',2850,NULL,30);
INSERT INTO EMP VALUES(7782,'CLARK','MANAGER',7839,'1981-06-09',2450,NULL,10);
INSERT INTO EMP VALUES(7788,'SCOTT','ANALYST',7566,'1987-07-03',3000,NULL,20);
INSERT INTO EMP VALUES(7839,'KING','PRESIDENT',NULL,'1981-11-17',5000,NULL,10);
INSERT INTO EMP VALUES(7844,'TURNER','SALESMAN',7698,'1981-09-08',1500,0,30);
INSERT INTO EMP VALUES(7876,'ADAMS','CLERK',7788,'1987-07-13',1100,NULL,20);
INSERT INTO EMP VALUES(7900,'JAMES','CLERK',7698,'1981-12-03',950,NULL,30);
INSERT INTO EMP VALUES(7902,'FORD','ANALYST',7566,'1981-12-03',3000,NULL,20);
INSERT INTO EMP VALUES(7934,'MILLER','CLERK',7782,'1981-01-23',1300,NULL,10);

--工资等级表  salgrade
CREATE TABLE SALGRADE(
   GRADE int, #等级
   LOSAL double, #最低工资
   HISAL double #最高工资
 )ENGINE=INNODB CHARSEt=utf8
插入数据
INSERT INTO SALGRADE VALUES (1,700,1200);
INSERT INTO SALGRADE VALUES (2,1201,1400);
INSERT INTO SALGRADE VALUES (3,1401,2000);
INSERT INTO SALGRADE VALUES (4,2001,3000);
INSERT INTO SALGRADE VALUES (5,3001,9999);
select * from dept;#部门表
select * from emp;#员工表
select * from salgrade; #工资等级表
-- 单表查询
(1)查询部门30中员工的详细信息。
select * from emp where deptno=30;
(2)查询从事clerk工作的员工的编号、姓名、部门号。
select empno,ename,deptno from emp where job='clerk';
(3)查询姓名以A、B、S开始的员工信息。
select * from emp where ename like 'A%' or ename like 'B%' or ename like 'S%';
(4)查询姓名中包含A的员工信息。
select * from emp where  ename like '%A%';
(5)查询从事clerk工作的员工姓名和所在部门名称。
select ename ,dname from emp ,dept  where emp.deptno=dept.deptno and job='clerk';
select e.ename ,d.dname from emp e, dept d where e.deptno=d.deptno and e.job='clerk';
(6)查询10部门的经理、20部门的职员 的员工信息。
select * from emp where deptno = 10 and job='MANAGER' or deptno = 20 and job = 'CLERK';
(7)查询10部门的经理、20部门的职员 或者既不是经理也不是职员但是工资高于2000元的员工信息。
SELECT * FROM EMP WHERE (DEPTNO=10 AND JOB='MANAGER')
OR DEPTNO=20 OR (JOB NOT IN('CLERK','MANAGER') AND SAL>2000);
(8)查询计算员工的日薪(按30天)
select ename,sal/30 as '日薪' from emp;
(9)查询员工的详细信息并按姓名排序。
select * from emp order by ename asc;

-- 多表查询
(1)查询拥有员工的部门名、部门号
select distinct d.dname, d.deptno from dept d,emp e where d.deptno = e.deptno;
(2)查询工资水平多于smith的员工信息
select * from emp where sal > (select sal from emp where ename = 'smith');
(3)查询员工和所属经理的姓名
select e.ename ,(select m.ename from emp m where m.empno = e.mgr) 
ename from emp e;
(4)查询雇员的雇佣日期早于其经理雇佣日期的员工及其经理姓名。
select e.ename,m.ename from emp e,emp m
where e.mgr=m.empno
and e.hiredate < m.hiredate;
(5)查询员工工作及其从事此工作的最低工资。
select deptno ,min(sal) sal from emp group by deptno;
(6)查询员工的姓名、所在部门名及其工资。
select ename,dname,sal from emp ,dept where emp.deptno = dept.deptno;
(7)查询部门号、部门名、部门所在位置及其每个部门的员工总数。
select dept.deptno,dept.dname,dept.loc,count(emp.deptno) number from dept dept,emp
where dept.deptno = emp.deptno
group by emp.deptno;
(8)查询与30部门员工工资水平相同的员工姓名与工资。
select ename,sal from emp where sal in (select sal from emp where deptno = 30);
《二》创建三个表：学生表 && 课程表 && 成绩表：
S (SNO,SNAME,SDD,SAGE)   
SNO,SNAME,SDD,SAGE  分别代表学号、学员姓名、所属单位、学员年龄
C(CNO,CNAME )                  
CNO,CNAME        分别代表课程编号、课程名称
SC(SNO,CNO,SCORE )          
SNO,CNO,SCORE      分别代表学号、所选修的课程编号、学习成绩

--学生表
create table S(
            SNO int,
            SNAME VARCHAR(20),
            SDD VARCHAR(50),
            SA int
)ENGINE=INNODB CHARSEt=utf8
--课程表
CREATE table C(
      CNO VARCHAR(20) NOT  NULL  PRIMARY  KEY,
      CNAME varchar(20),
      CTEACHER varchar(20)
)ENGINE=INNODB CHARSEt=utf8

--成绩表
create table SC(
            SNO int,
            CNO VARCHAR(20),
            SCORE INT
)ENGINE=INNODB CHARSEt=utf8

--学生表
INSERT INTO S(SNO,SNAME,SDD,SA) VALUES(2019001,'托马斯李','运营',26);
INSERT INTO S(SNO,SNAME,SDD,SA) VALUES(2019002,'米高扬','管理',30);
INSERT INTO S(SNO,SNAME,SDD,SA) VALUES(2019003,'蝙蝠侠','安防',22);
INSERT INTO S(SNO,SNAME,SDD,SA) VALUES(2019004,'李嘉诚','投资',45);
INSERT INTO S(SNO,SNAME,SDD,SA) VALUES(2019005,'雷军','开发',34);
INSERT INTO S(SNO,SNAME,SDD,SA) VALUES(2019006,'周小川','管理',56);
INSERT INTO S(SNO,SNAME,SDD,SA) VALUES(2019007,'陆奇','运营',36);
INSERT INTO S(SNO,SNAME,SDD,SA) VALUES(2019008,'普京','安防',67);
--课程表
INSERT  INTO  C(CNO,CNAME,CTEACHER)   VALUES('C1','税收基础','二狗');
INSERT  INTO  C(CNO,CNAME,CTEACHER)   VALUES('C2','金融工程','翠花');
INSERT  INTO  C(CNO,CNAME,CTEACHER)   VALUES('C3','会计','狗蛋');
INSERT  INTO  C(CNO,CNAME,CTEACHER)   VALUES('C4','统计学习方法','虚竹');
INSERT INTO C(CNO,CNAME,CTEACHER) VALUES('C5','大数据','铁柱');
INSERT INTO C(CNO,CNAME,CTEACHER) VALUES('C6','机器学习算法','二蛋');
--成绩表
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019001,'C2',80);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019002,'C2',78);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019003,'C1',89);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019003,'C5',60);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019004,'C4',90);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019005,'C1',87);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019005,'C2',75);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019005,'C3',80);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019005,'C4',90);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019005,'C5',86);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019005,'C6',88);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019006,'C1',99);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019006,'C2',61);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019007,'C1',62);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019007,'C2',78);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019007,'C3',77);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019007,'C4',69);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019007,'C5',98);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019007,'C6',88);
INSERT INTO SC(SNO,CNO,SCORE) VALUES(2019008,'C1',78);
select * from S;  # 学生表
select * from C;  # 课程表
select * from SC; # 成绩表
(1)使用嵌套语句查询选修课程名称为‘税收基础’的学员学号和姓名
select sno,sname from s where sno in(select sno from sc where cno in(select cno from c where cname='税收基础'));
(2)使用嵌套语句查询选修课程编号为‘C2’的学员姓名和所属单位
select sname,sdd from s where sno in(select sno from sc where sc.cno='c2');
(3)使用嵌套语句查询不选修课程编号为‘C5’的学员姓名和所属单位
select sname,sdd from s where sno not in(select sno from sc where sc.cno='c5');
(4)使用嵌套语句查询选修全部课程的学员姓名和所属单位
SELECT *,count(sc.CNO) from s,sc where s.SNO=sc.SNO GROUP BY s.sno HAVING count(sc.CNO)=(SELECT count(cno) from c)---有问题,待研究
(5)查询选修了课程的学员人数
SELECT cno,count(cno) from sc GROUP BY CNO;
(6)查询选修课程超过5门的学员学号和所属单位
select s.sno,sdd,COUNT(cno) from s,sc where s.sno=sc.sno group by s.sno,sdd HAVING COUNT(cno) > 5;

(1)查询没有选修过“翠花”老师讲授课程的所有学生姓名
SELECT * FROM s where sno not in 
(SELECT sno from sc where cno in 
(SELECT cno FROM c where cteacher='翠花'));
(2)列出有二门以上（含两门）不及格课程的学生姓名及其平均成绩
SELECT *,count(sno) from sc where SCGRADE<90 GROUP BY sno HAVING count(sno)>=2;---有问题,待研究
(3)列出既学过“1”号课程，又学过“2”号课程的所有学生姓名
SELECT sno from sc where cno in('c1','c2') GROUP BY SNO HAVING count(sno)>=2;---题目待商榷,待研究
(4)列出“1”号课成绩比“2”号同学该门课成绩高的所有学生的学号  
SELECT a.* from 
(SELECT * FROM sc a where CNO='c1')as a,
(SELECT * FROM sc b where CNO='c2')as b
where a.sno=b.sno and a.score>b.score; 
(5)列出“1”号课成绩比“2”号课成绩高的所有学生的学号及其“1”号课和“2”号课的成绩
select * from S;  # 学生表
select * from C;  # 课程表
select * from SC; # 成绩表
《三》财富港20楼面试题目
--创建cust表(学号、学生名字 、学生姓名、学生住址、电话号码)
create table cust(
      Studentno int(10) primary key,
      Name varchar(20),
      Address varchar(20),
      Telno varchar(20)
)ENGINE=INNODB CHARSEt=utf8

--创建mark表(学号、课程编号、英语成绩、数学成绩、计算机成绩)
create table mark(
      Studentno int(10) primary key,
      Coursenum varchar(20),
      English int(20),
      Math int(20),
      Compute int(20)
)ENGINE=INNODB CHARSEt=utf8

select * from cust;
select * from mark;

(1)现在有五个学生，其学号分别为1，2，3，4，5；请使用一条sql语句实现列出这五个学生的数学成绩以及姓名、学生住址、电话号码；
第一种：select math,NAME,Address,Telno from mark,cust where mark.studentno=cust.studentno;
第二种：select math,NAME,Address,Telno from mark m join cust c on m.studentno=c.studentno 
(2)查询所有计算机成绩大于数学成绩学生的姓名、计算机成绩、按照计算机成绩从高到低排序
select NAME,compute from mark,cust where mark.studentno=cust.studentno and compute>math ORDER BY compute desc;
(3)查询所有总成绩大于240分的学生学号、姓名、总成绩，按照总成绩从高到低排序
SELECT c.Studentno, c.name, SUM(english+Math+Compute) zong from mark m, cust c where m.studentno=c.studentno 
GROUP BY c.studentno ,c.name HAVING  zong>240 ORDER BY  zong desc;
《四》创建学生表和成绩表
--创建学生表(学号、学生姓名)
create table student(
      id int(10) primary key,
      name varchar(20)
)ENGINE=INNODB CHARSEt=utf8

--创建成绩表(学号、课程编号、成绩)
create table sc(
      sid int(10),
      cid int(10),
      score varchar(20)
)ENGINE=INNODB CHARSEt=utf8

select * from student;
select * from sc;

(1)查询平均成绩大于90分的同学的学号和平均成绩
第一种：SELECT Student.id,AVG(SC.score) FROM Student SC where Student.id = SC.sid GROUP BY Student.id
HAVING AVG(SC.score) > 90;
第二种：SELECT Student.id,AVG(SC.score) FROM Student JOIN SC ON Student.id = SC.sid GROUP BY Student.id
HAVING AVG(SC.score) > 90;

以下数据库的题目来源于：柠檬班软件测试800道面试常问题-mysql数据库：《5》～《13》

《五》万年学生表经典面试题
-- student:学生表（id、name、sex、birth、department、address）
-- score:课程表(id、stud_id、c_name、grade)

(1)查询student表的第2条到第4条记录：
select * from student limit 1,3
(2)从student表中查询计算机系和英语系的学生的信息
select * from student where department in('计算机系','英语系')
(3)从student表中查询年龄18～22岁的学生信息
select * from student where birth>=18 and birth<=22
(4)从student表中查询每个院系有多少人
select department，count(id) from student group by department;
(5)从score表中查询每个科目的最高分
select c_name,max(grade) from score group by c_name;
(6)查询李四的考试科目(c_name)和考试成绩(grade)
select c_name,grade from score where stu_id=(select id from student where name='李四')；
(7)计算每个学生的总成绩
select student.id,name,sum(grade) from student,score where student.id=score.stu_id group by id;
(8)查询计算机成绩低于95的学生信息
select * from student where id in(select stu_id from score where c_name='计算机' and grade<95);
(9)查询同时参加计算机和英语考试的学生的信息
select a.* from student a,score b.score c 
where 
a.id=b.stu_id 
and b.c_name='计算机' 
and a.id=c.stu_id 
and c.c_name='英语';

《六》student score
student:st_name(姓名),st_class(班级)，st_no(学号)，st_age(年龄),
score:sc_name(课程名)，sc_no(课程编号)，sc_stno(学号)，sc_sco(成绩)，用SQL语句实现：
(1)将sc_no为345记录的sc_sco加10
(2)查找课程编号为123并且成绩在80分以上的学生学号和姓名
(3)列出所有学生总分以及学生学号和姓名，班级。
a)update table student set sc_sco=sc_sco+10 where sc_no=345;
b)select t.st_no,t.st_name from student t,score s where t.st_no=s.sc_stno and s.sc_no=123 and s.sc_sco>80.
c)select sum(s.sc_sco),t.sc_no,t.st_name,t.st_class from student t,score s  where t.st_no=s.sc_stno group by t.sc_no,t.st_name,t.st_class 

《七》emp work_log
emp:emp_no,ename (员工工号、员工姓名)
work_log:emp_no,work_data,work_hour （员工工号、工作日期、工作时长）
(1)请写出sql:a 查询james 1月份的考勤记录
select e.emp_no,e.ename,w.work_data,w.work_hour from emp e,work_log w where e.emp_no=w.emp_no and e.name='james' and month(w.work_data)=1;
(2)筛选出1月工时总数小于<160的员工及工时
select e.emp_no,e.ename,sum(w.work_hour)as 工时 from emp e,work_log w where e.emp_no=w.emp_no and month(w.work_data)=1 group by  e.emp_no having sum(w.work_hour)<160;

《八》t_student(学生表)  t_score（课程分数表）
t_student:ID  NAME
t_score:ID COURSE SCORE
(1)查出班级中所有重名的学生
select ID，NAME from t_student group by NAME having count(1)>1;

《九》TestScores
 TestScores:Name Course Score
 (1)查询出每门课都大于80分的学生的姓名
 select Name,min(Score) form TestScores group by Name having min(Score)>80

《十》emp
(1)计算各个部门中，出生日期在1981年6月1日之后的员工总数
 select count(1) as 员工总数 from emp where (BIRTHDAY,'dd-mm-yy')>to_date('1981-06-01','dd-mm-yy')
《十一》Student Sourse Sc Teacher
Student(S#,Sname,Sage,Ssex)--学生表
Sourse(C#,Cname,T#)--课程表
SC(S#,C#,score)--成绩表
Teacher(T#,Tname)--教师表
(1)查询每门课程被选修的学生数
 select C#,count(S#) from sc group by c#
(2)查询课程名称为‘编程基础’，且分数不少于90分的学生姓名和分数
    select Student.Sname,Course.Cname,SC.score from student,Course,SC where Course.Cname='编程基础' and Student.S#=SC.S# and Course.C#=SC.C# and SC.score>90;

《十二》分组查询
Student(学号、姓名、届次、专业)
(1)请写出SQL语句查询每个届次各有多少人？
 select '届次'，count(1) as 人数 from Students group by '届次';

《十三》student grade
student(id,no,name,sex,age,province)
grade(id,no,class,grade)
(1)查询班级中英语成绩及格的全部女生
   select s.* from student s join grade g on s.no=g.no where s.sex='女' and g.grade>=60;
posted on   隆江猪脚饭  阅读(182)  评论(0)  编辑  收藏  举报
编辑推荐：
· AI与.NET技术实操系列：基于图像分类模型对图像进行分类
· go语言实现终端里的倒计时
· 如何编写易于单元测试的代码
· 10年+ .NET Coder 心语，封装的思维：从隐藏、稳定开始理解其本质意义
· .NET Core 中如何实现缓存的预热？
阅读排行：
· 分享一个免费、快速、无限量使用的满血 DeepSeek R1 模型，支持深度思考和联网搜索！
· 基于 Docker 搭建 FRP 内网穿透开源项目（很简单哒）
· ollama系列01：轻松3步本地部署deepseek，普通电脑可用
· 25岁的心里话
· 按钮权限的设计及实现
点击右上角即可分享
微信分享提示
SQL AI 助手