题目:
新入职的程序员冯帅被公司安排去跟一个学生管理系统的项目,项目组长 安排他去主要负责数据库部分的所有操作,日常数据的维护和根据需求进行数 据查询。可冯帅之前并没有接触过数据库,那就抓紧时间学习一下数据库,把 组长安排的 sql 补充一下吧!
数据表:
年级表:年级 id(主键),年级名称
成绩表:id(主键),学员编号,科目 id,分数,考试时间
学生表:学生编号(主键),学生姓名,登录密码,性别,年级 id,电话, 地址,出生日期,email
科目表:科目 id(主键),科目名称,学时,年级 id
需求:
- grade 表增加一个阶段,“就业期”
- 将第三阶段的学生的 gradeid 改为就业期的 id
- 查询所有得了 100 分的学号
- 查询所有 1989 年出生的学生(1989-1-1~1990-1-1)
- 查询学生姓名为“金蝶”的全部信息
- 查询 subjectid 为 8 的科目考试未及格(60 分)的学号和成绩
- 查询第 3 阶段课时大于 50 的课程全部信息
- 查询 S1101001 学生的考试信息
- 查询所有第二阶段的女生信息
- “基于.NET 平台的软件系统分层开发”需要多少课时
- 查询“设计 MySchool 数据库”和“面向对象程序设计”的课时(使用 in)
- 查询所有地址在山东的学生信息
- 查询所有姓凌的单名同学
- 查询 gradeid 为 1 的学生信息,按出生日期升序排序
- 查询 subjectid 为 3 的考试的成绩信息,用降序排序
- 查询 gradeid 为 2 的课程中课时最多的课程信息
- 查询北京的学生有多少个
- 查询有多少个科目学时小于 50
- 查询 gradeid 为 2 的阶段总课时是多少
- 查询 subjectid 为 8 的课程学生平均分
- 查询 gradeid 为 3 的课程中最多的学时和最少的学时
- 查询每个科目有多少人次考试
- 每个阶段课程的平均课时
- 查询每个阶段的男生和女生个数(group by)
习题sql如下:
建表:
CREATE TABLE object (
OBJECT_ID INT (2) NOT NULL PRIMARY KEY,
object_name VARCHAR (20),
object_time INT (2),
grade_id VARCHAR (3)
)ENGINE = INNODB DEFAULT charset = utf8mb4;
CREATE TABLE grade (
grade_id varchar(3) NOT NULL PRIMARY KEY,
grade_name VARCHAR (4)
) ENGINE = INNODB DEFAULT charset = utf8mb4;
CREATE table score(
score_id int not null PRIMARY key AUTO_INCREMENT,
stu_num VARCHAR(20),
OBJECT_ID INT(2),
SCORE_NUM VARCHAR(3),
TEST_TIME DATE
)ENGINE = INNODB DEFAULT charset = utf8mb4;
CREATE TABLE STUDENT (
stu_num VARCHAR (10) NOT NULL PRIMARY KEY,
STU_NAME VARCHAR (10),
STU_PASSWORD VARCHAR (10),
SEX enum ('男', '女'),
grade_id VARCHAR (10),
tel_num VARCHAR (20),
stu_addr VARCHAR (20),
stu_date DATE,
stu_email VARCHAR (20)
) ENGINE = INNODB DEFAULT charset = utf8mb4;
作业:
-- 1、grade 表增加一个阶段, “就业期”
insert into grade VALUES(4,'就业期');
-- 2.将第三阶段的学生的 gradeid 改为就业期的 id
update student a set a.grade_id = '4' where a.grade_id = '3';
SELECT * from student;
-- 3.查询所有得了 100 分的学号
SELECT stu_num from score where SCORE_NUM = 100;
-- 4.查询所有 1989 年出生的学生(1989-1-1~1990-1-1)
SELECT stu_num,stu_name from student WHERE stu_date >= '1989-01-01' and stu_date < '1990-01-01';
-- 5.查询学生姓名为“金蝶” 的全部信息
SELECT * from student where STU_NAME = '金蝶';
-- 6.查询 subjectid 为 8 的科目考试未及格(60 分) 的学号和成绩
SELECT stu_num,score_num from score where OBJECT_ID = 8 and SCORE_NUM < 60;
-- 7.查询第 3 阶段课时大于 50 的课程全部信息
SELECT * from object where grade_id = 3 and object_time > 50;
-- 9.查询所有第二阶段的女生信息
SELECT * from student where grade_id = 2 and sex = '女';
-- 10.“基于.NET 平台的软件系统分层开发” 需要多少课时
SELECT object_time from object where object_name = '基于.NET 平台的软件系统分层开发';
-- 11.查询“设计 MySchool 数据库” 和“面向对象程序设计” 的课时(使用in)
SELECT object_time from object where object_name in ('设计 MySchool 数据库','面向对象程序设计');
-- 12 查询所有地址在山东的学生信息
SELECT * from student where stu_addr = '山东';
-- 13 查询所有姓凌的单名同学
SELECT * from student where STU_NAME LIKE '凌_';
-- 14.查询 gradeid 为 1 的学生信息, 按出生日期升序排序
SELECT * from student where grade_id = 1 ORDER BY stu_date ASC;
-- 15.查询 subjectid 为 3 的考试的成绩信息, 用降序排序
SELECT * from score where OBJECT_ID = 3 ORDER BY SCORE_NUM desc;
-- 16.查询 gradeid 为 2 的课程中课时最多的课程信息
SELECT * from object where grade_id = 2 and object_time = (SELECT max(object_time) from object where grade_id = 2);
-- 17.查询北京的学生有多少个
SELECT COUNT(*) FROM student where stu_addr = '北京';
-- 18.查询有多少个科目学时小于 50
SELECT COUNT(object_time) from object where object_time < 50;
-- 19.查询 gradeid 为 2 的阶段总课时是多少
SELECT sum(object_time) from object where grade_id = 2;
-- 20.查询 subjectid 为 8 的课程学生平均分
SELECT AVG(score_num) from score where OBJECT_ID = 8;
-- 21.查询 gradeid 为 3 的课程中最多的学时和最少的学时
SELECT MAX(object_time) from object where grade_id = 3;
SELECT Min(object_time) from object where grade_id = 3;
-- 22.查询每个科目有多少人次考试
select object_id,count(stu_num) from score where TEST_TIME is not null GROUP BY OBJECT_ID ;
-- 23.每个阶段课程的平均课时
SELECT grade_id, avg(object_time) from object GROUP BY grade_id;
-- 24.查询每个阶段的男生和女生个数(group by 两列)
SELECT grade_id,sex,count(stu_num) from student GROUP BY grade_id,SEX;