SQL练习题

前置操作

• 创建表

create table student(
sno varchar(10) primary key,
sname varchar(20),
sage double(2),
ssex varchar(5)
);
create table teacher(
tno varchar(10) primary key,
tname varchar(20)
);
create table course(
cno varchar(10),
cname varchar(20),
tno varchar(20),
constraint pk_course primary key (cno,tno)
);
create table sc(
sno varchar(10),
cno varchar(10),
score double(4,2),
constraint pk_sc primary key (sno,cno)
);

• 导入数据

/*******初始化学生表的数据******/
insert into student values ('s001','张三',23,'男');
insert into student values ('s002','李四',23,'男');
insert into student values ('s003','吴鹏',25,'男');
insert into student values ('s004','琴沁',20,'女');
insert into student values ('s005','王丽',20,'女');
insert into student values ('s006','李波',21,'男');
insert into student values ('s007','刘玉',21,'男');
insert into student values ('s008','萧蓉',21,'女');
insert into student values ('s009','陈萧晓',23,'女');
insert into student values ('s010','陈美',22,'女');

/******************初始化教师表***********************/
insert into teacher values ('t001', '刘阳');
insert into teacher values ('t002', '谌燕');
insert into teacher values ('t003', '胡明星');

/***************初始化课程表****************************/
insert into course values ('c001','J2SE','t002');
insert into course values ('c002','Java Web','t002');
insert into course values ('c003','SSH','t001');
insert into course values ('c004','Oracle','t001');
insert into course values ('c005','SQL SERVER 2005','t003');
insert into course values ('c006','C#','t003');
insert into course values ('c007','JavaScript','t002');
insert into course values ('c008','DIV+CSS','t001');
insert into course values ('c009','PHP','t003');
insert into course values ('c010','EJB3.0','t002');

/***************初始化成绩表***********************/
insert into sc values ('s001','c001',78.9);
insert into sc values ('s002','c001',80.9);
insert into sc values ('s003','c001',81.9);
insert into sc values ('s004','c001',60.9);
insert into sc values ('s001','c002',82.9);
insert into sc values ('s002','c002',72.9);
insert into sc values ('s003','c002',81.9);
insert into sc values ('s001','c003','59');

练习题

1、查询“c001”课程比“c002”课程成绩高的所有学生的学号；

答案:

select a.* from
(select * from sc a where a.cno='c001') a, -- 获取到"c001"课程的所有学生成绩
(select * from sc b where b.cno='c002') b -- 获取到"c002"课程的所有学生成绩
where a.sno=b.sno and a.score > b.score; -- 在学生学号相同的情况下,成绩a大于成绩b的信息

总结:进行对"c001"课程和"c002"课程的成绩看成俩个独立的数据集,然后在学号相同的情况下,从两数据集中取出唯一的对应的值进行比较,得出符合条件的学号

select * from sc a
where a.cno='c001' -- 查询有课程"c001"成绩的学生
and  exists(select * from sc b where b.cno='c002' and a.score>b.score -- 是否存在课程"c001"的成绩大于课程"c002"
and a.sno = b.sno)  -- 学号相同

总结:对课程"c001"中有成绩的学生中的每一名学生,都对相同学号在课程"c002"中的成绩进行比较,如果大于则记录保留,否则丢弃该记录

2、查询平均成绩大于60 分的同学的学号和平均成绩；

答案:

select sno,avg(score) from sc  group by sno having avg(score)>60;

总结:该平均数指的是分组后单个学生的各科成绩平均分,并且发现在select 后使用avg()不使用分组,将只显示第一个数据,且平均分为所有记录的平均分(使用聚合函数的结果,是单行的,如果进行分组每将会有一行数据)

3、查询所有同学的学号、姓名、选课数、总成绩；

写法一

select a.*,s.sname from (select sno,sum(score),count(cno) from sc 
group by sno) a ,student s where a.sno=s.sno

总结:利用子查询进行学号、选课数、总成绩的查询分组,与主查询进行连接
写法二

select s.sno,s.sname ,count(c.cno), sum(score) from student s ,sc c where s.sno=c.sno 
group by c.sno

总结:直接使用分组和聚合函数进行查询,代码更加简洁

4、查询姓“刘”的老师的个数；

select count(tno) from teacher where tname like "刘%"

总结:使用like 进行模糊查询,聚合函数内使用主键保证唯一

5、查询没学过“谌燕”老师课的同学的学号、姓名；

写法一

SELECT
	s.sno,
	s.sname 
FROM
	student s 
WHERE
	NOT EXISTS (
	SELECT
		* 
	FROM
		sc,
		course c,
		teacher t 
	WHERE
		s.sno = sc.sno 
		AND sc.cno = c.cno 
		AND c.tno = t.tno 
	AND tname LIKE "谌燕" 
	)

总结:查询当前学生,选课中是否存在谌燕的课程不存在就保留.

写法二

SELECT
	a.sno,
	a.sname 
FROM
	student a 
WHERE
	a.sno NOT IN (
	SELECT DISTINCT
		s.sno 
	FROM
		sc s,
		(
		SELECT
			c.* 
		FROM
			course c,
			( SELECT tno FROM teacher t WHERE tname = '谌燕' ) t 
		WHERE
			c.tno = t.tno 
		) b 
	WHERE
	s.cno = b.cno 
	)

总结:魔鬼的代码不解释

6、查询学过“c001”并且也学过编号“c002”课程的同学的学号、姓名；

写法一(子查询)

SELECT
	s.sno,
	s.sname 
FROM
	student s 
WHERE
	s.sno IN ( SELECT sno FROM sc WHERE sc.cno IN ( "c001", "c002" ) )

总结:先进行对查询的条件建立子查询,然后根据条件过滤原表数据.
写法二

select distinct
	s.sno,
	s.sname
FROM
	student s,
	sc c
where 
	s.sno=c.sno and c.cno in ("c001","c002")

总结:将两表连接书写连接条件,然后对应该进行条件限制的表进行限制.

追加题

• 前置

-- 部门表
CREATE TABLE dept (
  id INT PRIMARY KEY PRIMARY KEY, -- 部门id
  dname VARCHAR(50), -- 部门名称
  loc VARCHAR(50) -- 部门所在地
);
-- 添加4个部门
INSERT INTO dept(id,dname,loc) VALUES 
(10,'教研部','北京'),
(20,'学工部','上海'),
(30,'销售部','广州'),
(40,'财务部','深圳');

-- 职务表，职务名称，职务描述
CREATE TABLE job (
  id INT PRIMARY KEY,
  jname VARCHAR(20),
  description VARCHAR(50)
);
-- 添加4个职务
INSERT INTO job (id, jname, description) VALUES
(1, '董事长', '管理整个公司，接单'),
(2, '经理', '管理部门员工'),
(3, '销售员', '向客人推销产品'),
(4, '文员', '使用办公软件');

-- 员工表
CREATE TABLE emp (
  id INT PRIMARY KEY, -- 员工id
  ename VARCHAR(50), -- 员工姓名
  job_id INT, -- 职务id
  mgr INT , -- 上级领导
  joindate DATE, -- 入职日期
  salary DECIMAL(7,2), -- 工资
  bonus DECIMAL(7,2), -- 奖金
  dept_id INT, -- 所在部门编号
  CONSTRAINT emp_jobid_ref_job_id_fk FOREIGN KEY (job_id) REFERENCES job (id),
  CONSTRAINT emp_deptid_ref_dept_id_fk FOREIGN KEY (dept_id) REFERENCES dept (id)
);

-- 添加员工
INSERT INTO emp(id,ename,job_id,mgr,joindate,salary,bonus,dept_id) VALUES 
(1001,'孙悟空',4,1004,'2000-12-17','8000.00',NULL,20),
(1002,'卢俊义',3,1006,'2001-02-20','16000.00','3000.00',30),
(1003,'林冲',3,1006,'2001-02-22','12500.00','5000.00',30),
(1004,'唐僧',2,1009,'2001-04-02','29750.00',NULL,20),
(1005,'李逵',4,1006,'2001-09-28','12500.00','14000.00',30),
(1006,'宋江',2,1009,'2001-05-01','28500.00',NULL,30),
(1007,'刘备',2,1009,'2001-09-01','24500.00',NULL,10),
(1008,'猪八戒',4,1004,'2007-04-19','30000.00',NULL,20),
(1009,'罗贯中',1,NULL,'2001-11-17','50000.00',NULL,10),
(1010,'吴用',3,1006,'2001-09-08','15000.00','0.00',30),
(1011,'沙僧',4,1004,'2007-05-23','11000.00',NULL,20),
(1012,'李逵',4,1006,'2001-12-03','9500.00',NULL,30),
(1013,'小白龙',4,1004,'2001-12-03','30000.00',NULL,20),
(1014,'关羽',4,1007,'2002-01-23','13000.00',NULL,10);

-- 工资等级表
CREATE TABLE salarygrade (
  grade INT PRIMARY KEY,   -- 级别
  losalary INT,  -- 最低工资
  hisalary INT -- 最高工资
);

-- 添加5个工资等级
INSERT INTO salarygrade(grade,losalary,hisalary) VALUES 
(1,7000,12000),
(2,12010,14000),
(3,14010,20000),
(4,20010,30000),
(5,30010,99990);

查询员工姓名，工资，工资等级

SELECT
	e.ename,
	e.salary,
	s.grade 
FROM
	emp AS e,
	salarygrade s 
WHERE
	e.salary BETWEEN s.losalary 
	AND s.hisalary

总结:将两表之间限制关系,加在where后面,进行无主外键关系的连接操作

查询出部门编号、部门名称、部门位置、部门人数

• 内连接实现

SELECT
	d.id,
	d.dname,
	d.loc,
	count( e.id ) 
FROM
	emp e,
	dept d 
WHERE
	e.dept_id = d.id 
GROUP BY
	d.id 
ORDER BY
	count( e.id ) DESC

• 子查询实现

SELECT
	d.id,
	d.dname,
	d.loc,
	e.total 
FROM
	dept d,
	( SELECT dept_id, count( id ) total FROM emp GROUP BY dept_id ) e 
WHERE
	d.id = e.dept_id

总结:在多数情况下使用内连接比使用子查询要更加简洁清晰.

查询所有员工的姓名及其直接上级的姓名,没有领导的员工也需要查询(自关联)

SELECT
	e1.ename,
	e2.ename 
FROM
	emp e1
	LEFT JOIN emp e2 ON e1.mgr = e2.id

总结:使用自关联只需要对一张表进行不同的别名,进行连接等操作就行了