sql 50题

CREATE DATABASE curriculum charset='utf8';

show databases;

use curriculum;

show TABLES;

-- 创建学生表
create table students(
sid int primary key not null,
name varchar(30) not null,
gender char(2) not null,
age int not null,
department varchar(10) not null,
class varchar(20) not null,
shcoolYear datetime not null);

-- 创建课程表
create table courses(
courseId char(10) primary key,
courseName varchar(20) not null,
selectiveCourse char(10));

-- 创建成绩表
create table score(
sid int not null,
foreign key(sid) references students(sid),
courseId char(10) not null,
foreign key(courseId) references courses(courseId),
score int not null);

-- 创建授课表
create table schedules(
id int auto_increment primary key,
teacherName varchar(30) not null,
courseId char(10) not null,
foreign key(courseId) references courses(courseId),
learnTime int not null);
alter table schedules add class varchar(20) not null;

-- 学生表数据
insert into students values
(200009001,'葛文卿','女',22,'国际贸易','国贸2班','2000-8-29'),
(200104019,'郑秀莉','女',21,'会计学','会计2班','2001-2-9'),
(200203001,'刘成锴','男',18,'计算机','软件2班','2002-8-27'),
(200206001,'李涛','男',19,'电子学','电子1班','2002-8-27'),
(200203002,'沈香娜','女',22,'计算机','软件2班','2002-8-27'),
(200206002,'李涛','男',23,'电子学','电子1班','2002-8-27'),
(200203003,'肖一竹','女',19,'计算机','软件2班','2002-8-27');


-- 课程表数据
insert into courses values
('C801','高等数学','无'),
('C807','离散数学','无'),
('C802','C++语言','C807'),
('C803','数据结构','C802'),
('C804','数据库原理','C803'),
('C805','操作系统','C807'),
('C806','编译原理','C803');

-- 成绩表数据 
insert into score values
(200203001,'C801',98),
(200203002,'C804',70),
(200206001,'C801',85),
(200203001,'C802',99),
(200206002,'C803',82);

-- 授课表数据 
insert into schedules values
(0,'苏亚步','C801',72,'软件2班'),
(0,'王立山','C802',64,'软件2班'),
(0,'何珊','C803',72,'软件2班'),
(0,'王立山','C804',64,'软件2班'),
(0,'苏亚步','C801',72,'软件2班'),
(0,'苏亚步','C801',72,'软件1班');

-- 1.查询学生表中信息
select * from students;

-- 2.查询成绩表中的信息
select * from score;

-- 3.查询全部学生的所有信息,且姓名输出在左边(第一列)
select name,sid,gender,age,department,class,shcoolYear from students;

-- 4.查询学生当前的年龄是多少岁
select name,age from students;

-- 5.要求计算教师授课程的学分(设学分=学时数/16)
select teacherName,(learnTime/16)as '学分' from schedules;

-- 6.要求列出学生所在的所有院系名称
select distinct department from students;
SELECT department from students GROUP BY department;

-- 7.查询姓'李'的学生情况
SELECT * from students where name like '李%';

-- 8.查询成绩不在85 到95 之间的信息
select * from score where score <85 or score > 95;
select * from score where score not in (select score from score where score>=85 and score<95);

-- 	9.列出所有非软件2班的班级名称
select * from students where class not in (select class from students where class='软件2班');

-- 10.列出课程号在'C802'与'C806'之间的所有课程信息
select * from courses where courseId between 'C802' and 'C806';

-- 11.列出无选修课的所有课程的课程名
select courseName from courses where selectiveCourse='无';

-- 12.列出'高等数学','操作系统','编译原理'的全部信息
select * from courses where courseName='高等数学' or courseName='操作系统' or courseName='编译原理';

-- 13.要求查询所有2002年元旦前入学的学生名单.()并按年龄排序
alter table students change shcoolYear schoolYear datetime not null;
select * from students where schoolYear<'2002-01-01' order by age;

-- 14.要求查询年龄在19岁以下或者是女生的学生姓名,年龄,性别
select name,age,gender from students where age<19 or gender='女';

-- 15.同时按学号(从小到大)和课程号(从大到小)排列出所有学生课程成绩
select sid,score from score ORDER BY sid,courseId desc;

-- 16.从学生表中统计每个班级名称的人数
select class,count(class)as'班级人数' from students group by class;

-- 17.统计19岁以上女生的个数
select count(gender) as '个数' from students where gender='女' and age>19;

-- 18.查询所有年龄的总和
SELECT sum(age) from students;

-- 19.从成绩表查询最高分,最低分
select max(score)as'最高分',min(score)as'最低分' from score;

-- 20.要求查询'C801'课程的平均分
select avg(score) from score where courseId='C801';

-- 21.统计选修了课程的学生人数(用成绩表)
select count(sid) from score where courseId not in (SELECT courseId from courses where selectiveCourse='无');

-- 22.根据性别统计学生平均年龄
select gender,avg(age)as'平均年龄' from students GROUP BY gender;

-- 23.根据学生表统计入学年份 
select schoolYear from students group by schoolYear;

-- 24.找出入学人数大于3人的年份及人数 !!!
select schoolYear,count(sid) from students GROUP BY schoolYear HAVING count(sid) > 3;

-- 25.求相同月份入学的人数(不考虑年份)
SELECT month(schoolYear),count(sid) FROM students GROUP BY month(schoolYear);

-- 26.筛选出平均成绩在80分以上的课程及平均成绩
SELECT courseId,avg(score) from score GROUP BY courseId having avg(score) > 80;

-- 27.查询学生 姓名,课程号,成绩
SELECT score.sid,name,courseId,score from students join score on students.sid=score.sid;

-- 28.列出所有课程的选修课程名称
select courseName as '课程名称',selectiveCourse as '选修课编号',
(case 
	when selectiveCourse = 'C807' then '离散数学'
	when selectiveCourse = 'C802' then 'C++语言'
	when selectiveCourse = 'C803' then '数据结构'
	end) as '选修课名称'
from courses where selectiveCourse !='无';


-- 29.查询课程号为'C801'并且成绩大于90分的学生姓名,学号和成绩  
insert into students values(200203008,'章三','女',11,'计算机','软件9班','2002-8-17');
insert into score values(200203008,'C802',99);
select name,students.sid,score from students join score on students.sid = score.sid where score>90 and courseId='C801';

-- 30.查询学生姓名,学号,课程名,成绩 
select name,students.sid,courseName,score from students join score on students.sid=score.sid join courses on score.courseId=courses.courseId;

-- 31.要求列出选修了高等数学的学生学号,姓名,成绩,课程名
select students.sid,name,score,score.courseId from students join score on students.sid=score.sid join courses on score.courseId=courses.courseId where score.courseId='C801';

-- 32.查询与'沈香娜' 同院系的学生信息
select * from students where department in (SELECT department FROM students where name='沈香娜');

-- 33.查询选修了'C801'与'C804' 这2门课程的学生姓名,课程名和成绩
select name,courseName,score from students join score on students.sid=score.sid join courses on score.courseId=courses.courseId where score.courseId='C801' or score.courseId='C804';

-- 34.查询所有与计算机系学生年龄不相同的学生信息
SELECT * from students where age not in (select age from students where department='计算机');

-- 35.查询所有年龄大于计算机系的平均年龄的非计算机系学生的学生信息
select * from students where age>(select avg(age) from students where department='计算机') and department not in (SELECT department from students where department='计算机');

-- 36.查询所有年龄大于计算机系的最小年龄的非计算机系学生的学生信息
select * from students where age>(select min(age) from students where department='计算机') and department not in (select department from students where department='计算机');

-- 37.查询所有年龄小于计算机系的最小年龄的非计算机系学生的学生信息
select * from students where age<(select min(age) from students where department='计算机') and department not in (select department from students where department='计算机');

-- 38.查询''所教班级的所有任课教师的姓名,所教班级名
select teacherName,class from schedules where class in (SELECT class from schedules where teacherName='何珊');

-- 39.按年龄降序列出所有超过平均年龄的学生姓名和年龄
select name,age from students where age > (select avg(age) from students) order by age desc;

-- 40.检索学号不是[200108011]且入学月份为8月的学生信息
select * from students where sid!=200108011 and month(schoolYear)=8;

-- 41.列出年龄在18~20岁之间且选修了高等数学的男同学的详细信息
select * 
from students 
join score on  students.sid=score.sid 
join courses on score.courseID=courses.courseId 
where age between 18 and 20 and courses.courseName='高等数学' and gender='男';

-- 42.列出所有不是姓'刘'的学生信息
select * from students where name not like '刘%';


-- 43.统计学生表中一共有多少名20岁以上的女同学
select count(sid) as '20岁以上女同学人数' from students where age >20 and gender='女';

-- 44.查找年龄最大和最小的男学生年龄
select max(age),min(age) from students where gender='男';

-- 45.计算选修课门数在1门以上的学生号,姓名, 选课门数 
select students.sid,name,count(selectiveCourse) 
from students 
join score on students.sid=score.sid 
join courses on score.courseId =courses.courseId
where selectiveCourse !='无' GROUP BY sid;


-- 46.计算每个班学生的平均年龄和最小年龄
select class,avg(age),min(age) from students GROUP BY class;

-- 47.列出所有成绩大于学号为的学生平均成绩的学生姓名,课程名和成绩
select name,courseName,score 
from students 
join score on students.sid=score.sid 
join courses on score.courseId=courses.courseId 
where score>(select score from score where score.sid=200206002);

-- 48.创建一个[简单学生表]要求包含4个字段"学号,姓名,性别和所在院系",
-- --然后将[学生表]中"国际贸易"或者男学生的记录全部插入到简单学生表中,最后显示插入的记录
create table simpleStu(
sid int primary key not null,
name varchar(30) not null,
gender char(2) not null,
department varchar(10) not null);

insert into simpleStu select sid,name,gender,department from students where department='国际贸易' or gender='男';

-- 49.列出所有没有选修课程'C804'的学生清单
select * from students join score on students.sid=score.sid join courses on score.courseId=courses.courseId where selectiveCourse!='c804';

-- 50.计算每个学生的选修课门数,要求显示学生学号和选修课门数
select sid,count(selectiveCourse) 
from score 
join courses on score.courseId=courses.courseId  
GROUP BY sid;

-- 51.将计算级系的学生成绩均加5分
select score+5 from students join score on students.sid=score.sid where department='计算机';