sql查询练习

# 创建数据库school
CREATE DATABASE school;

# 创建表student
DROP TABLE IF EXISTS student;
CREATE TABLE student(
	`s_id` TINYINT(3) UNSIGNED NOT NULL AUTO_INCREMENT comment '学生编号' , 
	`s_name` VARCHAR(4) NOT NULL comment '学生姓名',
	`s_sex` VARCHAR(2) NOT NULL comment '学生性别', 
	`s_birthday` DATETIME comment '学生生日',
	`class` VARCHAR(5) comment '学生班级',
	PRIMARY KEY(`s_id`),
	KEY s_name(`s_name`)
)engine=innodb DEFAULT CHARSET=utf8 comment '学生表';

# 创建表course
CREATE TABLE course(
	c_id VARCHAR(5) NOT NULL comment '课程编号', 
	c_name VARCHAR(10) NOT NULL comment '课程名称', 
	t_id VARCHAR(10) NOT NULL comment '授课老师'
)engine=innodb DEFAULT CHARSET=utf8 comment '课程表';

# 创建表score 
CREATE TABLE score (
	s_id VARCHAR(3) NOT NULL comment '学生编号', 
	c_id VARCHAR(5) NOT NULL comment '课程编号', 
	degree NUMERIC(10, 1) NOT NULL comment '学分'
)engine=innodb DEFAULT CHARSET=utf8 comment '分数表';

# 创建表teacher
CREATE TABLE teacher (
	t_id VARCHAR(3) NOT NULL comment '教师编号', 
	t_name VARCHAR(4) NOT NULL comment '教师名称', 
	t_sex VARCHAR(2) NOT NULL comment '教师性别', 
	t_birthday DATETIME NOT NULL comment '教师生日',
	prof VARCHAR(6) comment '职称', 
	depart VARCHAR(10) NOT NULL comment '院系'
)engine=innodb DEFAULT CHARSET=utf8 comment '教师表';

# 向各表导入数据
INSERT INTO student (s_id,s_name,s_sex,s_birthday,class) VALUES 
(108 ,'曾华' ,'男' ,'1977-09-01',95033),
(105 ,'匡明' ,'男' ,'1975-10-02',95031),
(107 ,'王丽' ,'女' ,'1976-01-23',95033),
(101 ,'李军' ,'男' ,'1976-02-20',95033),
(109 ,'王芳' ,'女' ,'1975-02-10',95031),
(103 ,'陆君' ,'男' ,'1974-06-03',95031);


INSERT INTO course(c_id,c_name,t_id) VALUES 
('3-105' ,'计算机导论',825),
('3-245' ,'操作系统' ,804),
('6-166' ,'数据电路' ,856),
('9-888' ,'高等数学' ,100);

INSERT INTO score(s_id,c_id,degree) VALUES
(103,'3-245',86),
(105,'3-245',75),
(109,'3-245',68),
(103,'3-105',92),
(105,'3-105',88),
(109,'3-105',76),
(101,'3-105',64),
(107,'3-105',91),
(108,'3-105',78),
(101,'6-166',85),
(107,'6-106',79),
(108,'6-166',81);


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






#sql题目(基础)
1、 查询student表中的所有记录的s_name、s_sex和class列。 
	#无别名
	select s_name,s_sex,class from student;
	#有别名
	select s_name as 学生姓名, s_sex as 学生性别, class as 学生班级 from student;


2、 查询教师所有的单位即不重复的depart列。 
	select distinct depart from teacher;
	

3、 查询student表的所有记录。 
	select * from student;
	

4、 查询score表中成绩在60到80之间的所有记录。
	select * from score where degree>=60 and degree<=80;
	
	
5、 查询score表中成绩为85，86或88的记录。 
	#方法1
	select * from score where degree=85 or degree=86 or degree=88;
	
	#方法2
	select * from score where degree in (85,86,88);
	
	
6、 查询student表中“95031”班或性别为“女”的同学记录。 
	select * from student where class=95031 or s_sex='女';
	

7、 以class降序查询student表的所有记录。 
	select * from student order by class desc;
	
	
8、 以c_id升序、degree降序查询score表的所有记录。
	select * from score order by c_id asc,degree desc;
 
9、 查询“95031”班的学生人数。 
	select count(*) as 95031班学生人数 from student where class=95031;
	

10、查询score表中的最高分的学生学号和课程号。 
	select s_id,c_id from score where degree=(select MAX(degree) from score);
	

11、查询‘3-105’号课程的平均分。 
	select AVG(degree) from score where c_id='3-105';
	

12、查询score表中至少有5名学生选修的并以3开头的课程的平均分数。
	select AVG(degree) as 平均分,c_id as 课程 from score where c_id like '3%' group by c_id having count(s_id)>=5;
 
 
13、查询最低分大于70，最高分小于90的s_id列。 
	select s_id from score where degree>70 and degree <90;


14、查询所有学生的s_name、c_id和degree列。 
	select student.s_name,score.c_id,score.degree from student left join score on student.s_id=score.s_id;
	

15、查询所有学生的s_id、c_name和degree列。 
	select student.s_id,course.c_name,score.degree from student
	join score  on student.s_id=score.s_id
	left join course on score.c_id=course.c_id;


16、查询所有学生的s_name、c_name和degree列。 
	select student.s_name,course.c_name,score.degree from student
	join score  on student.s_id=score.s_id
	left join course on score.c_id=course.c_id;


17、查询“95033”班所选课程的平均分。 
	select AVG(degree) from score 
	join student on score.s_id=student.s_id where class=95033;

18、假设使用如下命令建立了一个grade表： 
	create table grade(
		low varchar(3) not null comment 'low',
		upp varchar(3) not null comment 'upp',
		rank varchar(3) not null comment 'rank'
    )engine=innodb DEFAULT CHARSET=utf8 comment '等级表';
	
	insert into grade values(90,100,'A'); 
	insert into grade values(80,89,'B'); 
	insert into grade values(70,79,'C'); 
	insert into grade values(60,69,'D'); 
	insert into grade values(0,59,'E');
	
	现查询所有同学的s_id、c_id和rank列?
	
	select score.s_id,score.c_id,grade.rank from score,grade 
	where score.degree>=grade.low and score.degree<=grade.upp;
	
	
19、查询选修“3-105”课程的成绩高于“109”号同学成绩的所有同学的记录。
	#**all的意思是“对于子查询返回的列中的所有值，如果比较结果为true，则返回true”
	select * from score where c_id='3-105' and degree>all(select degree from score where s_id=109 and c_id='3-105');


20、查询score中选学一门以上课程的同学中分数为非最高分成绩的记录。 
	select * from score group by degree having count(c_id)>1;
	select * from score 
	where degree not in 
	(select MAX(degree) from score group by s_id)
	and s_id in
	(select s_id from score group by s_id having count(s_id)>1);
	


21、查询成绩高于学号为“109”、课程号为“3-105”的成绩的所有记录。
	select * from score 
	where degree>(select degree from score where s_id=109 and c_id='3-105');
	
 
22、查询和学号为108的同学同年出生的所有学生的s_id、s_name和s_birthday列。 
	select s_id,s_name,s_birthday from student 
	where YEAR(s_birthday)=(select YEAR(s_birthday) from student where s_id=108);
	

23、查询“张旭“教师任课的学生成绩。 
	#方法1
	select s.s_id,s.degree from score s 
	join course c on s.c_id=c.c_id
	left join teacher t on c.t_id=t.t_id
	where t.t_name='张旭';
	
	#方法2
	select s.s_id,s.degree from score s
	join (teacher t,course c) on (s.c_id=c.c_id and c.t_id=t.t_id)
	where t.t_name='张旭';
	
	

24、查询选修某课程的同学人数多于5人的教师姓名。
	#方法1--通过子查询
	select teacher.t_name from teacher where t_id in 
	(select t_id from course where c_id in 
	(select c_id from score group by c_id having count(s_id)>5));
	
	#方法2--通过连接查询（推荐）
	select t.t_name from teacher t join 
	(course c,score s) on (t.t_id=c.t_id and c.c_id=s.c_id)
	group by s.c_id having count(s.s_id)>5;
 
25、查询95033班和95031班全体学生的记录。 
	select * from student where class in (95033,95031);
	

26、查询存在有85分以上成绩的课程c_id. 
	#方法1
	select distinct c_id from score where degree>85;
	
	#方法2
	select c_id from score group by c_id having MAX(degree)>85;

27、查询出“计算机系“教师所教课程的成绩表。
	select s.degree,t.depart from score s join 
	(course c,teacher t) on (s.c_id=c.c_id and c.t_id=t.t_id)
	where t.depart='计算机系';
 
 
28、查询“计算机系”与“电子工程系“不同职称的教师的t_name和prof。 
	select t_name,prof from teacher 
	where depart='计算机系' 
	and 
	prof not in (select prof from teacher where depart='电子工程系');

	
29、查询选修编号为“3-105“课程且成绩至少高于选修编号为“3-245”的同学的c_id、s_id和degree,并按degree从高到低次序排序。 
	select * from score 
	where c_id='3-105'
	and 
	degree>any(select degree from score where c_id='3-245')
	order by degree desc;


30、查询选修编号为“3-105”且成绩高于选修编号为“3-245”课程的同学的c_id、s_id和degree. 
	select * from score 
	where c_id='3-105'
	and 
	degree>all(select degree from score where c_id='3-245');


31、查询所有教师和同学的name、sex和birthday. 
	select s.s_name 名字,s.s_sex 性别,s.s_birthday 年龄 from student s
	union
	select t.t_name 名字,t.t_sex 性别,t.t_birthday 年龄 from teacher t;

	
32、查询所有“女”教师和“女”同学的name、sex和birthday. 
	select s.s_name 名字,s.s_sex 性别,s.s_birthday 年龄 from student s where s.s_sex='女'
	union
	select t.t_name 名字,t.t_sex 性别,t.t_birthday 年龄 from teacher t where t.t_sex='女';
	
	

33、查询成绩比该课程平均成绩低的同学的成绩表。 
	select * from score a 
	join (select c_id,AVG(degree) average from score group by c_id) b
	on a.c_id=b.c_id
	where a.degree<b.average;
	

34、查询所有任课教师的t_name和depart. (不是所有老师都教课)
	select t.t_name,t.depart from teacher t join course c on t.t_id=c.t_id;


35、查询所有未讲课的教师的t_name和depart. 
	#方法1--子查询
	select t_name,depart from teacher where t_id not in (select t_id from course);
	
	#方法2--exists
	select t_name,depart from teacher where not exists (select * from course where course.t_id=teacher.t_id);

	
36、查询至少有2名男生的班号。 
	select class from student where s_sex='男' group by class having count(s_sex)>1;
	

37、查询student表中不姓“王”的同学记录。 
	select * from student where s_name not like '王%';
	

38、查询student表中每个学生的姓名和年龄。 
	select s_name as 姓名,(YEAR(NOW())-YEAR(s_birthday)) as 年龄 from student;
	

39、查询student表中最大和最小的s_birthday日期值。 
	select DATE_FORMAT(MAX(s_birthday),'%Y-%d-%m') as 最大日期,DATE_FORMAT(MIN(s_birthday),'%Y-%d-%m') as 最小日期 from student;


40、以班号和年龄从大到小的顺序查询student表中的全部记录。 
	select * from student order by class desc,s_birthday desc;
	

41、查询“男”教师及其所上的课程。 
	select * from course c
	join teacher t on c.t_id=t.t_id
	where t.t_sex='男';


42、查询最高分同学的s_id、c_id和degree列。 
	select * from score order by degree desc limit 1;


	
43、查询和“李军”同性别的所有同学的s_name. 
	select s_name from student where s_sex=(select s_sex from student where s_name='李军');


	
44、查询和“李军”同性别并同班的同学s_name. 
	select s_name from student where (s_sex,class)=(select s_sex,class from student where s_name='李军');

	
	
45、查询所有选修“计算机导论”课程的“男”同学的成绩表。
	select sc.* from score sc
	join (student st,course c) on (sc.s_id=st.s_id and sc.c_id=c.c_id)
	where st.s_sex='男' and c.c_name='计算机导论';