sql数据库练习题(涵盖大部分知识点)

一.创建数据库stucourse

没有表结构, 根据数据, 自己决定使用哪种数据类型

学生表(student)

sid	sname	sex	age	dept
1001	宋江	男	25	计算机系
3002	张明	男	23	生物系
1003	李小鹏	男	26	计算机系
1004	郑冬	女	25	计算机系
4005	李晓红	女	27	工商管理
5006	赵紫月	女	24	外语系

教师表(teacher)

tid	tname	title	salary	dept	cid
3102	李明	初级	2500	计算机系	C1
3108	黄晓明	初级	4000	生物系	C3
4105	张晓红	中级	3500	工商管理	C2
5102	宋力跃	高级	3500	物理系	C4
3106	赵明阳	初级	1500	地理系	C2
7108	张丽	高级	3500	生物系	C3
9103	王彬	高级	3500	计算机系	C1
7101	王力号	初级	1800	生物系	C1

课程表(courseinfo)

cid	cname	cbook	ctest	dept
C1	计算机基础	b1231	2009-4-6	计算机系
C2	工商管理基础	b1232	2009-7-16	工商管理
C3	生物科学	b1233	2010-3-6	生物系
C4	大学物理	b1234	2009-4-26	物理系
C5	数据库原理	b1235	2010-2-6	计算机系

选课表(scourse)

sid	score	cid	tid
1001	87	C1	3102
1001	77	C2	4105
1001	63	C3	3108
1001	56	C4	5102
3002	78	C3	3108
3002	78	C4	5102
1003	89	C1	9103
1004	56	C2	3106
4005	87	C4	5102
5006	null	C1	7101

教材表(bookinfo)

bid	bname	bpublish	bprice	quantity
b1231	Image Processing	人民大学出版社	34.56	8
b1212	Signal Processing	清华大学出版社	51.75	10
b1233	Digital Signal Processing	邮电出版社	48.5	11
b1234	The Logic Circuit	北大出版社	49.2	40
b1235	SQL Techniques	邮电出版社	65.4	20

二.查询问题

-- 查询数据stucourse

-- 1.查询全体学生的学号, 姓名和年龄

-- 2.查询选修了课程的学生号

-- 3.查询选修课程号c3的学号和成绩

-- 4.查询成绩高于85分的学生的学号，课程号和成绩

-- 5.查询没有选修C1也没有选修C2的学生学号，课程号和成绩

-- 6.查询工资在1500~2000之间的教师的教师号，姓名和职称

-- 7.查询选修C1或C2的学生的学号，课程号和成绩

-- 8.查询所有姓张的教师的教师号和姓名

-- 9.查询姓名中第2个汉字是力的教师号和姓名

-- 10.查询所有没有成绩的学生的学号和相应的课程号

-- 11.查询选修C1的学生学号和成绩，并按照成绩降序排列

-- 12.查询选修C2,或者C3,或者C4,或者C5课程的学号，课程号和成绩, 查询结果按学号升序排列，学号相同，再按成绩降序排列

-- 13.查询选修C1的学生学号和成绩，并显示成绩前三的学生

-- 14.查询计算机系学生的总数

-- 15.查询每位学生的学号及其选修课的门数

-- 16.在分组查询中使用having条件查询，平均成绩大于85的学生学号及平均成绩

-- 17.查询选课在两门以上且各门课均及格的学生的学号及其总成绩, 查询结果按总成绩降序列出

-- 18.查询所有选课学生的学号，姓名，选课名称及成绩

-- 19.查询选修C1课程且成绩在60以上的所有学生的学号, 姓名和分数

-- 20.查询与李明教师职称相同的教师号, 姓名

三.答案

1.创建数据库stucourse

分析表格数据可得到课表与学生表不作为子健,所以优先创建(主键建立后才能链接子健)

# 建立表
create database library;
use library;
create table booktype(
	typeid int primary key,
	typename varchar(20)

);

create table readerType(
	# 如果这个字段需要进行链接外键，那么这个也没有要求主键约束，那么我们只能弄一个唯一约束
	retypeid int unique not null,
	readerName varchar(20) not null,
	borrowquantity int not null,
	borrowDay int

);

create table book(
	bookid char(10) PRIMARY key,
	bookName varchar(20) not null,
	typeid int,
	bookauthor varchar(20),
	bookPublisher varchar(20),
	bookPrice double,
	borrowsum int,
	FOREIGN key (typeid) REFERENCES booktype(typeid)

);

create table bookstorage(
	bookbarcode char(20) PRIMARY key,
	bookId char(10) not null,
	bookInTime datetime,
	bookStatus varchar(4),
	FOREIGN key (bookId) REFERENCES book(bookId)


);



create table reader(
	readerId char(10) PRIMARY key,
	readerName varchar(20) not null,
	readerPass varchar(20) not null,
	retypeId int not null,
	readerDate dateTime,
	readerStatus varchar(4),
	FOREIGN key (reTypeId) REFERENCES readerType(reTypeId)

);

create table bookBorrow(
	borrowId char(10) PRIMARY key,
	bookBarCode char(20) not null,
	readerId char(10) not null,
	borrowTime datetime,
	returnTime datetime,
	borrowStatus varchar(4),
	FOREIGN key (bookBarCode) REFERENCES bookStorage(bookBarCode),
	Foreign key (readerId) REFERENCES reader(readerId)

);



# 插入数据

insert into booktype VALUES
(1, "自然科学"),
(2, "数学"),
(3, "计算机"),
(4, "建筑水利"),
(5, "旅游地理"),
(6, "励志/自我实现"),
(7, "工业技术"),
(8, "基础医学"),
(9, "室内设计"),
(10, "人文景观");

insert into book values
('TP39/1712','Java程序设计',3,'陈永红','机械工业出版社',35.5,30),
('013452','离散数学',2,'张小新','机械工业出版社',45.5,10),
('TP/3452','JSP程序设计案例',3,'刘城清','电子工业出版社',42.8,8),
('TH/2345','机械设计手册',7,'黄明凡','人民邮电出版社',40,10),
('R/345677','中医的故事',8,'李奇德','国防工业出版社',20.0,5);

insert into bookstorage values
('132782','TP39/1712','2009-08-10 00:00:00','在馆'),
('132789','TP39/1712','2009-08-10 00:00:00','借出'),
('145234','013452','2008-12-06 00:00:00','借出'),
('145321','TP/3452','2007-11-04 00:00:00','借出'),
('156833','TH/2345','2009-12-04 00:00:00','借出'),
('345214','R/345677','2008-11-03 00:00:00','在馆');

insert into readertype values
(1,'学生',10,30),
(2,'教师',20,60),
(3,'管理员',15,30),
(4,'职工',15,20);

insert into reader values
('0016','苏小东',123456,1,'1999-09-09 00:00:00','有效'),
('0017','张明',123456,1,'2010-09-10 00:00:00','有效'),
('0018','梁君红',123456,1,'2010-09-10 00:00:00','有效'),
('0021','赵清远',123456,2,'2010-07-01 00:00:00','有效'),
('0034','李瑞清',123456,3,'2009-08-03 00:00:00','有效'),
('0042','张明月',123456,4,'1997-04-23 00:00:00','有效');

insert into bookborrow values
('001328','132789','0017','2011-01-24 00:00:00','2011-02-28 00:00:00','已还'),
('001356','145234','0018','2011-02-12 00:00:00','2011-02-27 00:00:00','已还'),
('001432','132782','0016','2011-03-04 00:00:00','2011-04-05 00:00:00','已还'),
('001435','145321','0021','2011-08-09 00:00:00','2011-09-02 00:00:00','已还'),
('001578','156833','0034','2011-10-01 00:00:00','2011-11-01 00:00:00','未还'),
('001679','345214','0042','2011-02-21 00:00:00','2011-03-05 00:00:00','未还');

2.查询数据stucourse

-- 1.查询全体学生的学号, 姓名和年龄
select sid , sname , age  from student ;

-- 2.查询选修了课程的学生号
select distinct sid from scourse ;

-- 3.查询选修课程号c3的学号和成绩
select sid , score from scourse where cid ='c3';

-- 4.查询成绩高于85分的学生的学号，课程号和成绩
select  sid , cid, score from scourse where score > '85';

-- 5.查询没有选修C1也没有选修C2的学生学号，课程号和成绩
select sid , cid, score from scourse where cid != 'c1' and cid != 'c2';

-- 6.查询工资在1500~2000之间的教师的教师号，姓名和职称
select tname , tid , title from teacher where salary between '1500' and '2000';

-- 7.查询选修C1或C2的学生的学号，课程号和成绩
select score , b1.sid , cid from student as b1 LEFT JOIN scourse as b2 on b1.sid = b2.sid where cid = 'c1' or cid = 'c2';

-- 8.查询所有姓张的教师的教师号和姓名
select tid ,tname from teacher where tname like '张%';

-- 9.查询姓名中第2个汉字是力的教师号和姓名
select tid , tname from teacher where tname like '_力_';

-- 10.查询所有没有成绩的学生的学号和相应的课程号
#查询空值数据只能用is判断;
select sid , cid from scourse where score is null;

-- 11.查询选修C1的学生学号和成绩，并按照成绩降序排列
select sid , score from scourse  where cid = 'c1' ORDER BY score desc;

-- 12.查询选修C2,或者C3,或者C4,或者C5课程的学号，课程号和成绩, 查询结果按学号升序排列，学号相同，再按成绩降序排列
select sid , cid , score from scourse where cid != 'c1' order by sid asc , score desc ;

-- 13.查询选修C1的学生学号和成绩，并显示成绩前三的学生
select sid , score from scourse where cid = 'c1' order by score limit 3;

-- 14.查询计算机系学生的总数
select count(sid) as people from student where dept  = '计算机系' group by dept;

-- 15.查询每位学生的学号及其选修课的门数
select sid , count(cid) as '选修课门数' from scourse group by sid;

-- 16.在分组查询中使用having条件查询，平均成绩大于85的学生学号及平均成绩
select sid , avg(score) as '平均成绩'  from scourse  GROUP BY sid HAVING avg(score) > '85';

-- 17.查询选课在两门以上且各门课均及格的学生的学号及其总成绩, 查询结果按总成绩降序列出
select sum(score) , sid from scourse where score >= 60 group by sid HAVING count(cid) >= 2 ORDER BY sum(score) desc;

-- 18.查询所有选课学生的学号，姓名，选课名称及成绩
select b1.sid , b1.sname , b3.cname , b2.score  from student as b1 LEFT JOIN scourse as b2 on b1. sid = b2.sid LEFT JOIN courseinfo as b3 on b2.cid = b3.cid ;

-- 19.查询选修C1课程且成绩在60以上的所有学生的学号, 姓名和分数
select * from scourse as b1 LEFT JOIN student as b2 on b1.sid = b2.sid HAVING cid = 'c1' and score > '60';

-- 20.查询与李明教师职称相同的教师号, 姓名
select tid , tname from teacher where title = (select title from teacher where tname = '李明');