sql 练习题

学习java呀

已于 2022-11-03 20:58:33 修改

阅读量101

点赞数

文章标签： sql 数据库 java

于 2022-09-16 14:57:44 首次发布

本文链接：https://blog.csdn.net/qq_47700940/article/details/126890583

版权

CREATE TABLE student(
    s_no VARCHAR(20) PRIMARY KEY COMMENT'学生学号',
    s_name VARCHAR(20) NOT NULL COMMENT'学生姓名 不能为空',
    s_sex VARCHAR(10) NOT NULL COMMENT'学生性别',
    s_birthday DATETIME COMMENT'学生生日',
    s_class VARCHAR(20) COMMENT'学生所在的班级'
)comment '学生表';

CREATE TABLE course(
    c_no VARCHAR(20) PRIMARY KEY COMMENT'课程号',
    c_name VARCHAR(20) NOT NULL COMMENT'课程名称',
    t_no VARCHAR(20) NOT NULL COMMENT'教师编号 外键关联teacher表'
)comment '课程表';

CREATE TABLE teacher(
    t_no VARCHAR(20) PRIMARY KEY COMMENT'教师编号',
    t_name VARCHAR(20) NOT NULL COMMENT'教师姓名',
    t_sex VARCHAR(20) NOT NULL COMMENT'教师性别',
    t_birthday DATETIME COMMENT'教师生日',
    t_rof VARCHAR(20) NOT NULL COMMENT'教师职称',
    t_depart VARCHAR(20) NOT NULL COMMENT'教师所在的部门'
)comment '教师表';

CREATE TABLE score (
    s_no VARCHAR(20) NOT NULL COMMENT'成绩表的编号 依赖学生学号',
    c_no VARCHAR(20)  NOT NULL COMMENT'课程号 依赖于课程表中的c_id',
    sc_degree decimal
)comment '成绩表';

-- 学生表数据
INSERT INTO student VALUES('101','曾华','男','1977-09-01','95033');
INSERT INTO student VALUES('102','匡明','男','1975-10-02','95031');
INSERT INTO student VALUES('103','王丽','女','1976-01-23','95033');
INSERT INTO student VALUES('104','李军','男','1976-02-20','95033');
INSERT INTO student VALUES('105','王芳','女','1975-02-10','95031');
INSERT INTO student VALUES('106','陆军','男','1974-06-03','95031');
INSERT INTO student VALUES('107','王尼玛','男','1976-02-20','95033');
INSERT INTO student VALUES('108','张全蛋','男','1975-02-10','95031');
INSERT INTO student VALUES('109','赵铁柱','男','1974-06-03','95031');

-- 教师表数据
INSERT INTO teacher VALUES('804','李诚','男','1958-12-02','副教授','计算机系');
INSERT INTO teacher VALUES('856','张旭','男','1969-03-12','讲师','电子工程系');
INSERT INTO teacher VALUES('825','王萍','女','1972-05-05','助教','计算机系');
INSERT INTO teacher VALUES('831','刘冰','女','1977-08-14','助教','电子工程系');

-- 添加课程表
INSERT INTO course VALUES('3-105','计算机导论','825');
INSERT INTO course VALUES('3-245','操作系统','804');
INSERT INTO course VALUES('6-166','数字电路','856');
INSERT INTO course VALUES('9-888','高等数学','831');

-- 添加成绩表
INSERT INTO score VALUES('103','3-245','86');
INSERT INTO score VALUES('105','3-245','75');
INSERT INTO score VALUES('109','3-245','68');
INSERT INTO score VALUES('103','3-105','92');
INSERT INTO score VALUES('105','3-105','88');
INSERT INTO score VALUES('109','3-105','76');
INSERT INTO score VALUES('103','6-166','85');
INSERT INTO score VALUES('105','6-166','79');
INSERT INTO score VALUES('109','6-166','81');

-- 1.查询student表中所有的记录
select * from student;
-- 2查询student表中所有记录的s_name,s_sex和s_class列
select s_name,s_sex,s_class from student;
-- 3查询教师所有的单位但是不重复的t_depart列
select distinct t_depart from teacher;
select t_depart from teacher group by t_depart;
-- 4查询score表中成绩在60-80之间所有的记录(sc_degree)
select * from score where sc_degree>=60 and sc_degree<=80;
select * from score where sc_degree between 60 and 80;
-- 5.查询score表中成绩为85, 86, 或者88的记录(sc_degree)
select * from score where sc_degree in (85,86,88);
select * from score where sc_degree=85 or sc_degree=86 or sc_degree=88;
-- 6.查询student表中’95031’班或者性别为’女’的同学记录
select * from student where s_class='95031' or s_sex='女';
-- 7.以class降序查询student表中所有的记录
select * from student order by s_class desc ;
-- 8.以c_no升序.sc_degree降序插叙score表中所有的数据
select * from score order by c_no,sc_degree desc;
-- 9.查询’95031’班的学生人数
select count(*) from student where s_class='95031';
-- 10.查询score表中的最高分数的学生号和课程号.(子查询或者排序)
select s_no,c_no from score where sc_degree=(select max(sc_degree) from score);
-- 11.查询每门课的平均成绩
select avg(sc_degree) from score group by c_no;
-- 12,查询score表中至少有2名学生选修的,并且以3开头的课程的平均分
select avg(sc_degree)
from score s
    left join (select c_no,count(*) as 'c' from score group by c_no) a
        on s.c_no=a.c_no
where a.c>=2 and a.c_no like '3%';
-- 13.查询分数大于70但是小于90的s_no列:
select s_no from score where sc_degree>70 and sc_degree<90;
select s_no from score where sc_degree between 70 and 90;
-- 14.查询所有的学生 s_name , c_no, sc_degree列
select s_name,c_no,s.sc_degree
from student
    left join score s
        on student.s_no = s.s_no;
-- 15.查询所有学生的s_no, c_name, sc_degree列
select s.s_no,c_name,s.sc_degree
from score s
    left join course c
        on c.c_no = s.c_no;
-- 16.查询所有的学生 s_name , c_name, sc_degree列
select s_name,c_name,sc_degree
from student t
    left join score s
        on t.s_no=s.s_no
    left join course c
        on s.c_no=c.c_no;
-- 17.查询班级是’95031’班学生每门课的平均分
select c_no,avg(sc_degree) from score where s_no in(select s_no from student where s_class='95031') group by c_no;
-- 18.查询选修"3-105"课程的成绩高于’109’号同学’3-105’成绩 的所有同学的记录
select * from score where c_no='3-105' and sc_degree>(select sc_degree from score where s_no='109' and c_no='3-105');
-- 19.查询成绩高于学号为’109’,课程号为’3-105’的成绩的所有记录
select * from score where sc_degree>(select sc_degree from score where s_no='109' and c_no='3-105');
-- 20.查询所有学号为108.101的同学同年出生的所有学生的s_no,s_name和s_birthday
select s_no,s_name,s_birthday from student where s_birthday=(select s_birthday from student where s_no='108' and '101');
-- 21.查询 张旭 教师任课的学生的成绩
select t.t_name, avg(sc_degree)
from teacher t
    left join course c
        on t.t_no=c.t_no
    left join score s
        on c.c_no = s.c_no
where t.t_name='张旭';
-- 22.查询选修课程的同学人数多余 5 人的教师姓名
select t_name,count(*)
from teacher
    left join course c
        on teacher.t_no = c.t_no
    left join score s
        on c.c_no = s.c_no
group by c.c_no
having count(*)>2;
-- 23.查询95033班和95031班全体学生的记录
select * from student where s_class='95033' or s_class='95031';
-- 24.查询存在85分以上成绩的课程c_no
select distinct c_no from score where sc_degree>85;
-- 25.查出所有’计算机系’ 教师所教课程的成绩表
select  t_depart,sc_degree
from teacher
    left join course c
        on teacher.t_no = c.t_no
    left join score s
        on c.c_no = s.c_no
where t_depart='计算机系';
-- 26.查询’计算机系’与’电子工程系’ 不同职称的教师的name和rof
select t_name,t_rof from teacher where t_depart='计算机系' and t_rof not in(select t_rof from teacher where t_depart='电子工程系')
union all
select t_name,t_rof from teacher where t_depart='电子工程系' and t_rof not in(select t_rof from teacher where t_depart='计算机系');
-- 27, 查询选修编号为"3-105"课程且成绩至少高于选修编号为’3-245’同学的c_no,s_no和sc_degree,并且按照sc_degree从高到地次序排序
select c_no,s_no, sc_degree from score where c_no='3-105' and sc_degree>=(select max(sc_degree)from score where c_no='3-245') order by sc_degree desc;
-- 28.查询选修编号为"3-105"且成绩高于选修编号为"3-245"课程的同学c_no.s_no和sc_degree
select c_no,s_no, sc_degree from score where c_no='3-105' and sc_degree>=(select max(sc_degree)from score where c_no='3-245');
-- 29. 查询所有教师和同学的 name ,sex, birthday
select s_name as '名字',s_sex as '性别',s_birthday as '生日' from student
union all
select t_name,t_sex,t_birthday from teacher;
-- 30.查询所有’女’教师和’女’学生的name,sex,birthday
select s_name as '名字',s_sex as '性别',s_birthday as '生日' from student where s_sex='女'
union all
select t_name,t_sex,t_birthday from teacher where t_sex='女';
-- 31.查询成绩比该课程平均成绩低的同学的成绩表 (比较难)
select c_no,avg(sc_degree)from score group by c_no;
select s_name,s1.s_no,c_no,sc_degree
from score s1
    left join student s
        on s1.s_no = s.s_no
where sc_degree<(select avg(sc_degree)from score s where s1.c_no=s.c_no);
-- 32.查询所有任课教师的t_name 和 t_depart(要在分数表中可以查得到)
select t_name,t_depart
from score
    left join course c
        on score.c_no = c.c_no
    left join teacher t
        on c.t_no = t.t_no
group by t_name;
-- 33.查出至少有2名男生的班号
select s_class,count(s_class) from student where s_sex='男' group by s_class having count(s_class)>=2;
-- 34.查询student 表中 不姓"王"的同学的记录
select * from student where s_name not like '王%';
-- 35 查询student 中每个学生的姓名和年龄(当前时间 - 出生年份)  (注意这个)
select now();
select s_name ,TIMESTAMPDIFF(year,s_birthday,'2022-09-16 00:00:00') as '年龄' from student;
select s_name ,(year(now())-year(s_birthday)) as '年龄' from student;
-- 36. 查询student中最大和最小的 s_birthday的值
select max(s_birthday) from student
union
select min(s_birthday) from student;
-- 37.以班级号和年龄从大到小的顺序查询student表中的全部记录
select * from student order by s_class desc ,s_birthday;
-- 38.查询"男"教师 及其所上的课
select teacher.t_no, t_name, t_sex, t_birthday, t_rof, t_depart,c_name
from teacher
    left join course c
        on teacher.t_no = c.t_no
where t_sex='男';
-- 39.查询最高分同学的s_no c_no 和 sc_degree;
select * from score where sc_degree=(select max(sc_degree) from score);
-- 40. 查询和"李军"同性别的所有同学的s_name
select s_name,s_sex from student where s_sex=(select s_sex from student where s_name='李军');
-- 41.查询和"李军"同性别并且同班的所有同学的s_name
select s_name,s_sex,s_class from student where s_sex=(select s_sex from student where s_name='李军') and s_class=(select s_class from student where s_name='李军');
-- 42. 查询所有选修’计算机导论’课程的’男’同学的成绩表
select s_name,s.s_no, s.c_no,s.sc_degree
from student
    left join score s
        on student.s_no = s.s_no
    left join course c
        on s.c_no = c.c_no
where c_name='计算机导论' and s_sex='男';
-- 43. 假设使用了以下命令建立了一个grade表
-- 44 查询所有同学的s_no , c_no 和grade列

-- 部门表
create table dept(
	deptno int primary key auto_increment comment '部门编号',
	dname varchar(14) comment '部门名字',
	loc varchar(13) comment '地址'
) comment '部门表';
-- 员工表
create table emp(
	empno int primary key auto_increment comment '员工编号',
	ename varchar(10) comment '员工姓名',
	job varchar(9) comment '岗位',
	mgr int comment '直接领导编号',
	hiredate date comment '雇佣日期，入职日期',
	sal int comment '薪水',
	comm int comment '提成',
	deptno int comment '部门编号'
)comment '员工表';

insert into dept values(10,'财务部','北京');
insert into dept values(20,'研发部','上海');
insert into dept values(30,'销售部','广州');
insert into dept values(40,'行政部','深圳');

insert into emp values(7369,'刘一','职员',7902,'1980-12-17',800,null,20);
insert into emp values(7499,'陈二','推销员',7698,'1981-02-20',1600,300,30);
insert into emp values(7521,'张三','推销员',7698,'1981-02-22',1250,500,30);
insert into emp values(7566,'李四','经理',7839,'1981-04-02',2975,null,20);
insert into emp values(7654,'王五','推销员',7698,'1981-09-28',1250,1400,30);
insert into emp values(7698,'赵六','经理',7839,'1981-05-01',2850,null,30);
insert into emp values(7782,'孙七','经理',7839,'1981-06-09',2450,null,10);
insert into emp values(7788,'周八','分析师',7566,'1987-06-13',3000,null,20);
insert into emp values(7839,'吴九','总裁',null,'1981-11-17',5000,null,10);
insert into emp values(7844,'郑十','推销员',7698,'1981-09-08',1500,0,30);
insert into emp values(7876,'郭十一','职员',7788,'1987-06-13',1100,null,20);
insert into emp values(7900,'钱多多','职员',7698,'1981-12-03',950,null,30);
insert into emp values(7902,'大锦鲤','分析师',7566,'1981-12-03',3000,null,20);
insert into emp values(7934,'木有钱','职员',7782,'1983-01-23',1300,null,10);

-- 1．列出至少有一个员工的所有部门。(超纲)
select dname from emp e left join dept d on e.deptno=d.deptno group by dname;
-- 2．列出薪金比"刘一"多的所有员工。
select * from emp where sal>(select sal from emp where ename='刘一');
-- 3．***** 列出所有员工的姓名及其直接上级的姓名。(超纲)
select e.ename,b.ename
from emp e
    left join (select empno,ename from emp where empno in(select mgr from emp)) as b
        on e.mgr=b.empno;
-- 4．列出受雇日期早于其直接上级的所有员工。
select e.ename
from emp e
    left join (select empno,ename,hiredate from emp where empno in(select mgr from emp)) as b
        on e.mgr=b.empno
where e.hiredate<b.hiredate;
-- 5．列出部门名称和这些部门的员工信息，同时列出那些没有员工的部门。(超纲)
select e.deptno,dname,empno, ename, job, mgr, hiredate, sal, comm
from dept d
    left join emp e
        on e.deptno = d.deptno;
-- 6．列出所有job为“职员”的姓名及其部门名称。(超纲)
select e.ename,job,d.dname
from emp e
    left join dept d
        on e.deptno = d.deptno
where job='职员';
-- 7．列出最低薪金大于1500的各种工作。
select job from emp where sal>1500 group by job;
select distinct job from emp where sal>1500;
-- 8．列出在部门 "销售部" 工作的员工的姓名，假定不知道销售部的部门编号。
-- 作为条件时，查询到的必须是一行一列
select * from emp where deptno=(select deptno from dept where dname='销售部');
-- 9．列出薪金高于公司平均薪金的所有员工。
select * from emp where sal>(select avg(ifnull(sal,0)) from emp);
-- 10．列出与"周八"从事相同工作的所有员工。
select * from emp where job=(select job from emp where ename='周八');
-- 11．列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
select ename,sal from emp where sal in(select sal from emp where deptno='30');
-- 12．列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
select ename,sal from emp where sal>(select max(sal) from emp where deptno='30');
-- 13．列出在每个部门工作的员工数量、平均工资。
select avg(sal),count(*) from emp group by deptno;
-- 14．列出所有员工的姓名、部门名称和工资。
select ename,dname,sal from emp e left join dept d on e.deptno = d.deptno;
-- 15．列出所有部门的详细信息和部门人数。
select d.deptno,dname,loc,count(empno) from dept d left join emp e on d.deptno = e.deptno group by d.deptno;
-- 16．列出各种工作的最低工资。
select job, min(sal) from emp group by job;
-- 17．列出各个部门的 经理 的最低薪金。
select ename,job, min(sal) from emp where job='经理' group by deptno ;
-- 18．列出所有员工的年工资,按年薪从低到高排序。
select sal from emp order by sal asc ;
-- 19.查出emp表中薪水在3000以上（包括3000）的所有员工的员工号、姓名、薪水。
select empno,ename,sal from emp where sal>=3000;
-- 20.查询出所有薪水在'陈二'之上的所有人员信息。
select * from emp where sal>(select sal from emp where ename='陈二');
-- 21.查询出emp表中部门编号为20，薪水在2000以上（不包括2000）的所有员工，显示他们的员工号，姓名以及薪水，以如下列名显示：员工编号 员工名字 薪水
select empno,ename,sal from emp where deptno='20' and sal>2000;
-- 22.查询出emp表中所有的工作种类（无重复）
select job from emp group by job;
-- 23.查询出所有奖金（comm）字段不为空的人员的所有信息。
select * from emp where comm IS NOT NULL;
-- 24.查询出薪水在800到2500之间（闭区间）所有员工的信息。（注：使用两种方式实现and以及between and）
select * from emp where sal>=800 and sal<=2500;
select * from emp where sal between 800 and 2500;
-- 25.查询出员工号为7521，7900，7782的所有员工的信息。（注：使用两种方式实现，or以及in）
select * from emp where empno=7521 or empno=7900 or empno=7782;
select * from emp where empno in (7521,7900,7782);
-- 26.查询出名字中有“张”字符，并且薪水在1000以上（不包括1000）的所有员工信息。
select * from emp where ename like '%张%' and sal>1000;
-- 27.查询出名字第三个汉字是“多”的所有员工信息。
select * from emp where ename like '__多%';
-- 28.将所有员工按薪水升序排序，薪水相同的按照入职时间降序排序。
select * from emp order by sal,hiredate desc ;
-- 29.将所有员工按照名字首字母升序排序，首字母相同的按照薪水降序排序。 order by convert(name using gbk) asc;
select left(ename,1) str from emp order by str desc ,sal desc ;
-- 30.查询出最早工作的那个人的名字、入职时间和薪水。
select ename,hiredate,sal from emp where hiredate=(select min(hiredate) from emp);
-- 31.显示所有员工的名字、薪水、奖金，如果没有奖金，暂时显示100.
update emp set comm=100 where comm is null;
select ename,sal,comm from emp ;
-- 32.显示出薪水最高人的职位。
select job from emp where sal=(select max(sal) from emp);
-- 33.查出emp表中所有部门的最高薪水和最低薪水，部门编号为10的部门不显示。
select max(sal),min(sal) from emp where deptno !=10 group by deptno;
-- 34.删除10号部门薪水最高的员工。
delete from emp where sal=(select a from (select max(sal) as 'a' from emp where deptno='10') as ea);
-- 35.将薪水最高的员工的薪水降30%。
-- 更新和删除操作中的where语句不能直接用和需要操作的表相关的查找语句
update emp set sal=sal*0.7 where sal=(select a from (select max(sal) as 'a' from emp) as e);
-- 36.查询员工姓名，工资和 工资级别(工资>=3000 为3级，工资>2000 为2级，工资<=2000 为1级)
-- 语法：case when ... then ... when ... then ... else ... end
select ename,sal,(case when sal>=3000 then '3级' when sal>2000 then '2级' when sal<=2000 then '1级' end) as '工资级别' from emp;