数据库初学者经典练习题

数据初学者经典练习题

创建员工表 部门表 工资等级表

下面是建表语句

SET NAMES utf8mb4;
SET FOREIGN_KEY_CHECKS = 0;

---

– Table structure for dept

---

DROP TABLE IF EXISTS dept;
CREATE TABLE dept (
deptno int NOT NULL,
dname varchar(14) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
loc varchar(13) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
PRIMARY KEY (deptno) USING BTREE
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

---

– Records of dept

---

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’);

---

– Table structure for emp

---

DROP TABLE IF EXISTS emp;
CREATE TABLE emp (
empno int NOT NULL,
ename varchar(10) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
job varchar(9) CHARACTER SET utf8 COLLATE utf8_general_ci NULL DEFAULT NULL,
mgr int NULL DEFAULT NULL,
hiredate date NULL DEFAULT NULL,
sal decimal(7, 2) NULL DEFAULT NULL,
comm decimal(7, 2) NULL DEFAULT NULL,
deptno int NULL DEFAULT NULL,
PRIMARY KEY (empno) USING BTREE,
INDEX FK_DEPTNO(deptno) USING BTREE,
CONSTRAINT FK_DEPTNO FOREIGN KEY (deptno) REFERENCES dept (deptno) ON DELETE RESTRICT ON UPDATE RESTRICT
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

---

– Records of emp

---

INSERT INTO emp VALUES (7369, ‘SMITH’, ‘CLERK’, 7902, ‘1980-12-17’, 800.00, NULL, 20);
INSERT INTO emp VALUES (7499, ‘ALLEN’, ‘SALESMAN’, 7698, ‘1981-02-20’, 1600.00, 300.00, 30);
INSERT INTO emp VALUES (7521, ‘WARD’, ‘SALESMAN’, 7698, ‘1981-02-22’, 1250.00, 500.00, 30);
INSERT INTO emp VALUES (7566, ‘JONES’, ‘MANAGER’, 7839, ‘1981-04-02’, 2975.00, NULL, 20);
INSERT INTO emp VALUES (7654, ‘MARTIN’, ‘SALESMAN’, 7698, ‘1981-09-28’, 1250.00, 1400.00, 30);
INSERT INTO emp VALUES (7698, ‘BLAKE’, ‘MANAGER’, 7839, ‘1981-05-01’, 2850.00, NULL, 30);
INSERT INTO emp VALUES (7782, ‘CLARK’, ‘MANAGER’, 7839, ‘1981-06-09’, 2450.00, NULL, 10);
INSERT INTO emp VALUES (7788, ‘SCOTT’, ‘ANALYST’, 7566, ‘1987-06-13’, 3000.00, NULL, 20);
INSERT INTO emp VALUES (7839, ‘KING’, ‘PRESIDENT’, NULL, ‘1981-11-17’, 5000.00, NULL, 10);
INSERT INTO emp VALUES (7844, ‘TURNER’, ‘SALESMAN’, 7698, ‘1981-09-08’, 1500.00, 0.00, 30);
INSERT INTO emp VALUES (7876, ‘ADAMS’, ‘CLERK’, 7788, ‘1987-06-13’, 1100.00, NULL, 20);
INSERT INTO emp VALUES (7900, ‘JAMES’, ‘CLERK’, 7698, ‘1981-12-03’, 950.00, NULL, 30);
INSERT INTO emp VALUES (7902, ‘FORD’, ‘ANALYST’, 7566, ‘1981-12-03’, 3000.00, NULL, 20);
INSERT INTO emp VALUES (7934, ‘MILLER’, ‘CLERK’, 7782, ‘1982-01-23’, 1300.00, NULL, 10);

---

– Table structure for salgrade

---

DROP TABLE IF EXISTS salgrade;
CREATE TABLE salgrade (
grade int NULL DEFAULT NULL,
losal int NULL DEFAULT NULL,
hisal int NULL DEFAULT NULL
) ENGINE = InnoDB CHARACTER SET = utf8 COLLATE = utf8_general_ci ROW_FORMAT = Dynamic;

---

– Records of salgrade

---

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);

SET FOREIGN_KEY_CHECKS = 1;

下面为题目：

–查询所有员工信息(*通配符，默认查询所有的列)
–查询员工的姓名
–查询员工的薪资
–查询员工的姓名和薪资
–查询员工编号为7369的员工
–查询员工姓名叫做SMITH
–查询薪资大于2000的员工姓名
–查询工作为SALESMAN
–查询部门在20的员工
–查询薪资不等于2000的员工
–查询 员工编号为 7369 7788 7881的员工信息
–查询 员工编号除了 7369 7788 7881之外的所有员工信息
–查询除了10,20部门之外的所有员工
–查询不发放津贴的员工信息
–查询发放津贴的员工信息
–查询薪资范围在1000-4000之间的员工信息 [1000.4000]
–查询名字中有S的员工
–查询名字最后一个字符是S
–查询名字第一个字符是S
–查询名字第二个字符是A
–查询名字中有%的员工
–查询在20部门并且薪资大于2000的员工
–查询在20部门或者薪资大于2000的员工
–查询不在20部门并且薪资小于2000的员工
–按照津贴进行排序(null排在最前面)
–查询每种工作的平均薪资
–查询每个部门的最高薪资和最低薪资
–查询每个部门的人数和每月工资总数
–查询每个部门，每种工作的平均薪资

–查询个人姓名的平均薪资

–查询平均薪资高于2500的部门
–查询20部门的平均薪资
–查询10 20部门中,并且在二月份入职员工中，每个部门中平均薪资高于1500的工作是什么，并按照部门，工作平均薪资进行排序
–查询每个员工所在的部门名称
–查询薪资大于2000的员工姓名和部门名称
–查询每个员工所对应的薪资登记
–查询每个员工与其直属领导的名字
–谁的薪资高于20部门员工的薪资
–查询公司中薪资最低的员工姓名
–查询公司中谁的薪资高于平均薪资
–查询高于自己部门平均薪资的员工信息

01,列出至少有一个员工的所有部门。
02,列出薪金比“SMITH”多的所有员工。（大于最大薪水SMITH员工）
03,列出所有员工的姓名及其直接上级的姓名。
04,列出受雇日期早于其直接上级的所有员工。
05,列出部门名称和这些部门的员工信息，包括那些没有员工的部门。
06,列出所有job为“CLERK”（办事员）的姓名及其部门名称。
07,列出最低薪金大于1500的各种工作。
08,列出在部门“SALES”（销售部）工作的员工的姓名，假定不知道销售部的部门编号。
09,列出薪金高于公司平均薪金的所有员工。
10,列出与“SCOTT”从事相同工作的所有员工。
11,列出薪金等于部门30中员工的薪金的所有员工的姓名和薪金。
12,列出薪金高于在部门30工作的所有员工的薪金的员工姓名和薪金。
13,列出在每个部门工作的员工数量、平均工资和平均服务期限。
14,列出所有员工的姓名、部门名称和工资。
15,列出从事同一种工作但属于不同部门的员工的详细信息。
16,列出所有部门的详细信息和部门人数。
17,列出各种工作的最低工资。
18,列出各个部门的MANAGER（经理）的最低薪金（job为MANAGER）。
19,列出所有员工的年工资，按年薪从低到高排序。
20,列出所有job=‘CLERK’ 的员工平均薪资
21,列出job=‘CLERK’员工的平均薪资 按照部门分组
22,列出job=‘CLERK’员工的平均薪资 按照部门分组 并且部门编号 in(10,30) 按照平均薪资 降序排列
23,

列出job=‘CLERK’员工的平均薪资 按照部门分组 并且部门编号 in(20,30)
并且部门员工数量>=2人 按照平均薪资 降序排列

24.列出员工的姓名和工资以及对应的工资等级
25:列出工资大于10部门的平均薪资的员工信息以及工资等级和部门位置
26：列出员工的所在位置在’CHICAGO’的部门平均薪资

/1:建立学生表/
create table student (
sno char(3) primary key,
sname char(4),
sgender char(2),
sage int,
sclass char(5)
);
insert into student values(‘108’,‘曾华’,‘男’,19,‘95033’);
insert into student values(‘105’,‘匡明’,‘男’,20,‘95031’);
insert into student values(‘107’,‘王丽’,‘女’,20,‘95033’);
insert into student values(‘101’,‘李军’,‘男’,19,‘95033’);
insert into student values(‘109’,‘王芳’,‘女’,22,‘95031’);
insert into student values(‘103’,‘陆君’,‘男’,20,‘95031’);
/2:建立教师表/
create table teacher(
tno char(3) primary key,
tname char(4),
tgender char(2),
tage int ,
tlevel char(6),
tlesson char(8)
);
insert into teacher values(‘804’,‘李成’,‘男’,42,‘副教授’,‘计算机系’);
insert into teacher values(‘856’,‘张旭’,‘男’,35,‘讲师’,‘电子工程’);
insert into teacher values(‘825’,‘王萍’,‘女’,28,‘助教’,‘计算机系’);
insert into teacher values(‘831’,‘刘冰’,‘女’,25,‘助教’,‘电子工程’);
/3:建立课程表/
create table course (
cno char(5) primary key,
cname char(10),
tno char(3),
foreign key(tno) references teacher(tno)
);
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’);
/4:建立选课表/
create table sc(
sno char(3),
cno char(5),
primary key(sno,cno),
score int,
foreign key(sno) references student(sno),
foreign key(cno) references course(cno)
);
insert into sc values(‘103’,‘3-245’,86);
insert into sc values(‘105’,‘3-245’,75);
insert into sc values(‘109’,‘3-245’,68);
insert into sc values(‘103’,‘3-105’,92);
insert into sc values(‘105’,‘3-105’,88);
insert into sc values(‘109’,‘3-105’,76);
insert into sc values(‘101’,‘3-105’,64);
insert into sc values(‘107’,‘3-105’,91);
insert into sc values(‘108’,‘3-105’,78);
insert into sc values(‘101’,‘6-166’,85);
insert into sc values(‘107’,‘6-166’,79);
insert into sc values(‘108’,‘6-166’,81);
/5:所有表内容/
select * from student;
select * from course;
select * from teacher;
select * from sc;

所有表信息如下:

student(学生)表

作业题:
1.查询选修课程’3-105’且成绩在60到80之间的所有记录。
注释:用于指定某个范围使用between and,也可以使用and连接符;
答案:

2.查询成绩为85、86或88的记录。
注释:用于制定某个集合使用 in 关键字,也可以使用 or 连接符;
答案:

3.查询’95031’班的学生人数。
注释:count(*)用于计算结果总数;
答案:

4.查询最低分大于70,且最高分小于90的学号列。
注释:having后面跟聚合函数:avg,min,max,count;having语句只能跟在:group by语句后面;
答案:

5.查询至少有5名学生选修并以3开头的课程的平均成绩。
注释:group by 语句置于where语句后面表示根据什么条件来分组;
答案:
select avg(成绩) as 平均成绩 from sc where 课程号 like ‘3%’ group by 课程号 having count(课程号) >= 5;
6.查询平均分大于80分的学生的成绩表
注释:having后面跟聚合函数avg();
答案:

7.查询’95033’班每个学生所选课程的平均分。
注释:此题是根据 '95033’班学生的学号进行的分组,使用where语句限制group by语句的分组条件;
答案:

8.以选修 '3-105’为例,查询成绩高于’109’号同学的所有同学的记录。
注释:此题使用典型的嵌套查询,层层深入;
答案:

9.查询与学号为’108’的同学同岁的所有学生的学号、姓名和年龄。
注释:当查询的结果集返回只有一个时关键字in的作用等价于’=‘的作用,但是注意’='只能用与返回结果集只有一个,而 in 可以有多个结果;
答案:

10.查询每个老师授课不及格学生名单

11.查询所有学生的平均成绩

12.查询每个课程的最高分，显示课程名字和学生姓名和分数

13.查询每个学生的授课老师

14.查询每个班级的及格率

学生： (XXX 班级)
老师： (等级、课程名称)
课程表：（课程编号，老师编号，课程名称）
选课表(学生编号，课程号，分数)

1: 查询95001的学生的平均成绩
2：查询刘冰老师课程内容中不及格学生的详细信息
3：查询操作系统课程中未考试的学生的信息
4：查询等级是助教的老师对应课程的平均分数
5：按照考试成绩进行排序，查询年龄大于20 <操作系统课程成绩高于80分的学生>
考试科目、授课老师以及对应所有科目平均成绩。

下面为答案与题目一一对应

– select * from emp
– select distinct ename from emp
– select sal from emp
– select ename,sal from emp
– select * from emp where empno = 7369
– select * from emp where ename =‘SMITH’
– select * from emp where deptno =20
– select name from emp where sal >2000
– select * from emp where job = ‘SALESMAN’
– select * from emp where sal !=2000
– select * from emp where empno not in (7369,7788,7881)
– select * from emp where deptno not in(10,20)
– select * from emp where comm is null
– select * from emp where comm is not null
– select * from emp where sal between 1000 and 4000
– select * from emp where ename like ‘%S%’
– select * from emp where ename like ‘%S’
– select * from emp where ename like ‘A%’
– select * from emp where ename like ‘__A’
– select * from emp where ename like ‘%%%’
– select * from emp where deptno =20 and sal >2000
– select * from emp where deptno =20 or sal > 2000
– select * from emp where deptno !=20 and sal <2000
– select * from emp order by comm asc
– select job,avg(sal) from emp group by job
– select deptno,max(sal),min(sal) from emp group by deptno
– select deptno ,count(*),sum(sal)from emp group by deptno

– select deptno from emp group by deptno
– select deptno,job from emp where deptno =10
– select deptno,job,avg(sal) from emp where deptno in(10) group by job
– select deptno,job from emp where deptno =30

– select deptno ,count()from emp group by deptno having count()>=1

– select e1.ename,e2.ename
– from emp e1 join emp e2
– on e1.mgr =e2.empno

– select e1.ename
– from emp e1 join emp e2
– on e1.mgr =e2.empno where e1.hiredate < e2.hiredate

– select dept.dname,emp.*
– from dept left join emp
– on dept.deptno=emp.deptno

– select emp.ename,dept.dname
– from emp join dept
– on emp.deptno=dept.deptno
– where emp.job = ‘CLERK’

– select job,min(sal)
– from emp
– group by job
– having min(sal)>1500

– select ename
– from emp
– where deptno =(select deptno
– from dept
– where dname=‘SALES’)

– select ename,sal
– from emp
– where sal in(select sal
– from emp
– where deptno =30) and deptno != 30

– select ename,sal
– from emp
– where sal>(select max(sal)
– from emp
– group by deptno
– having deptno =30)

– select deptno,count(*),avg(sal)
– from emp
– group by deptno

– select ename,dname,sal
– from emp join dept
– on emp.deptno=dept.deptno

– select *
– from emp
– where job in(
– select job
– from (select job,deptno
– from emp
– group by job,deptno) e
– group by job
– having count(*)>1)

– select dept.,count()
– from dept join emp
– on dept.deptno=emp.deptno
– group by emp.deptno

– select job,min(sal)
– from emp
– group by job

– select deptno,job,min(sal)
– from emp
– where job=‘MANAGER’
– group by deptno,job

– select ename,(sal12 + IFNULL(comm,0)) ‘年工资’
– from emp
– order by (sal12 + IFNULL(comm,0))

– select avg(sal)
– from emp
– where job=‘CLERK’
– group by job

– select deptno,avg(sal)
– from emp
– where job=‘CLERK’
– group by deptno

– select avg(sal),job,deptno
– from emp
– where job =‘CLERK’
– and deptno in(10,30)
– group by deptno
– order by avg(sal) desc

– select avg(sal),deptno
– from emp
– where job =‘CLERK’
– group by deptno
– having count(*)>=2

– select emp.ename,salgrade.grade
– from emp join salgrade
– on emp.sal >salgrade.losal and emp.sal<salgrade.hisal

– select emp.*,dept.loc
– from emp join dept
– on emp.deptno=dept.deptno
– where emp.sal>(select avg(sal)
– from emp
– where deptno =10
– group by deptno)

– select avg(sal),deptno
– from emp
– where deptno =(select deptno
– from dept
– where loc=‘CHICAGO’)
– group by deptno

– select *
– from sc
– where cno =‘3-105’ and score between 60 and 80

– select *
– from sc
– where score in (85,86,88)

– select count(*),sclass
– from student
– where sclass=95031
– group by sclass

– select sno
– from sc
– group by sno
– having min(score)>70 and max(score)<90

– select avg(score)
– from sc
– where cno like ‘3%’
– group by cno
– having count(*) >=5

– select student.sno,avg(sc.score)
– from student join sc
– on student.sno =sc.sno
– where student.sclass =95033
– group by student.sno

– select student.*,score
– from student join sc
– on student.sno = sc.sno
– where sc.cno =‘3-105’ and
– sc.score>(
– select score
– from sc
– where sc.sno =109 and cno =‘3-105’)

– select s1.sno,s1.sname,s1.sage
– from student s1 join student s2
– on s1.sage =s2.sage
– where s2.sno=108

– select teacher.tname,student.sname
– from student join sc
– on student.sno =sc.sno
– join course
– on sc.cno =course.cno
– join teacher
– on course.tno = teacher.tno
– where sc.score<80
– group by teacher.tno,student.sno

– select student.sname,sc.sno,avg(sc.score)
– from sc join student
– on student.sno =sc.sno
– group by sno

– select student.sname,course.cname,max(sc.score)
– from student join sc
– on student.sno = sc.sno
– join course
– on sc.cno = course.cno
– group by sc.cno

– select student.sname,teacher.tname
– from student join sc
– on student.sno = sc.sno
– join course
– on course.cno = sc.cno
– join teacher
– on course.tno = teacher.tno
– group by student.sno,teacher.tname

– select student.sclass,sum(if(sc.score>60,1,0))/count(*)
– from student join sc
– on student.sno = sc.sno
– group by student.sclass

– select student.sclass,avg(sc.score)
– from student join sc
– on student.sno = sc.sno
– where student.sclass =95031
– group by student.sclass

– select student.*
– from student join sc
– on student.sno = sc.sno
– join course
– on course.cno = sc.cno
– join teacher
– on course.tno= teacher.tno
– where teacher.tname=‘张旭’ and sc.score<90

– select student.*
– from student join sc
– on student.sno = sc.sno
– join course
– on course.cno = sc.cno
– where course.cname=‘操作系统’ and sc.score is null

– select teacher.tname ,teacher.tlevel,sc.cno,avg(sc.score)
– from sc join course
– on sc.cno = course.cno
– join teacher
– on teacher.tno = course.tno
– where teacher.tlevel = ‘助教’
– group by teacher.tlevel,sc.cno

– select student.sname,avg(sc.score)
– from student join sc
– on student.sno = sc.sno
– join course
– on sc.cno = course.cno
– join teacher
– on course.tno = teacher.tno
– where student.sno in(
– select student.sno
– from student join sc
– on student.sno = sc.sno
– join course
– on sc.cno = course.cno
– join teacher
– on course.tno = teacher.tno
– where student.sage >19 and course.cname = ‘操作系统’
– and sc.score> 60
– )
– group by sc.sno