# 测试工程师sql笔试题-学生成绩管理

sql语句学习全攻略

1、创建学生表，分数表和课程表

create or replace table student(

sid int(11) primary key not null,

sname char(25) not null,

age int(11) not null,

sex char(2) not null,

department char(40) ,

birthplace varchar(256)

);

create or replace table sc(

sid int(11) not null,

cid int(11) not null,

);

create or replace table course(

cid int(11) not null primary key default 4,

cname char(40),

teacher char(40)

);

#以下是插入课程表的数据

delete from course ;

insert into course values('8108001','math','sandy');

insert into course values('8108002','english','sherry');

insert into course values('8108003','computer','sandy');

insert into course values('8108004','web','sandy');

insert into course values('8108005','java','sandy');

insert into course values('8108006','C languge','sherry');

insert into course values('8108007','python','xiaozhu');

insert into course values('8108008','testing','xiaozhu');

insert into course values('8108009','linux','sherry');

insert into course values('8108010','shell','sherry');

#以下是插入成绩级表的数据

delete from sc;

insert into sc values('3108001','8108010','90');

insert into sc values('3108001','8108003','67');

insert into sc values('3108002','8108003','54');

insert into sc values('3108002','8108010','84');

insert into sc values('3108003','8108003','78');

insert into sc values('3108004','8108004','89');

insert into sc values('3108005','8108006','56');

insert into sc values('3108006','8108005','60');

insert into sc values('3108007','8108004','79');

insert into sc values('3108008','8108008','89');

insert into sc values('3108009','8108002','46');

insert into sc values('3108010','8108003','87');

insert into sc values('3108011','8108001','85');

insert into sc values('3108011','8108002','81');

insert into sc values('3108012','8108001','97');

insert into sc values('3108012','8108002','55');

insert into sc values('3108013','8108002','86');

insert into sc values('3108013','8108001','71');

insert into sc values('3108014','8108002','69');

insert into sc values('3108014','8108001','78');

insert into sc values('3108015','8108002','67');

insert into sc values('3108016','8108001','85');

insert into sc values('3108016','8108003','85');

insert into sc values('3108016','8108002','85');

insert into sc values('3108016','8108004','85');

insert into sc values('3108016','8108005','85');

insert into sc values('3108016','8108006','80');

insert into sc values('3108016','8108007','79');

insert into sc values('3108016','8108009','36');

insert into sc values('3108016','8108010','78');

insert into sc values('3108016','8108008','88');

insert into sc values('3108016','8108021','83');

insert into sc values('3108015','8108001','85');

insert into sc values('3108015','8108003','85');

insert into sc values('3108015','8108004','85');

insert into sc values('3108015','8108005','85');

insert into sc values('3108015','8108006','80');

insert into sc values('3108015','8108007','79');

insert into sc values('3108015','8108009','36');

insert into sc values('3108015','8108010','78');

insert into sc values('3108015','8108008','88');

insert into sc values('3108015','8108021','83');

#以下是插入学生信息数据

delete from student;

insert into student values('3108001','wang min',21,'f','computer-tec','zhongshan road','jiangsu');

1. sandy老师所教的课程号、课程名称；
2. 年龄大于20岁的女学生的学号和姓名；
3. 在学生表中按性别排序，且男在前女在后显示记录。
4. “wuyi”所选修的全部课程名称；
5. 所有成绩都在80分以上的学生姓名及所在系；
6. 没有选修“english”课的学生的姓名；
7. 与“jilian”同乡的男生姓名及所在系；
8. 英语成绩比数学成绩好的学生;
9. 选修同一门课程时，女生比所有男生成绩都好的学生名单；
10. 至少选修两门及以上课程的学生姓名、性别；
11. 选修了sandy老师所讲课程的学生人数；
12. 本校学生中有学生姓名/性别重复的同学，请编写脚本查出本校所有学生的信息，显示学号，姓名，性别，总成绩，对于姓名/性别重复的学生信息只取总成绩最高的那一条记录。
13. “english”课程得最高分的学生姓名、性别、所在系；

165 篇文章70 人订阅

0 条评论

## 相关文章

### BZOJ 1497: [NOI2006]最大获利

Description 新的技术正冲击着手机通讯市场，对于各大运营商来说，这既是机遇，更是挑战。THU集团旗下的CS&T通讯公司在新一代通讯技术血战的前夜，需...

2896

9896

### 只有程序猿才懂的道理

① 面试官：熟悉哪种语言？ 应聘者：C# 面试官：知道什么叫类么？ 应聘者：我这人实在，工作努力，不知道什么叫累。 面试官：知道什么是包？ 应聘者：我这人实在，...

3638

1856

2125

1041

511

2984

2824

### 字节跳动一面凉经

【每日一语】如果这世界上真有奇迹，那只是努力的另一个名字。生命中最难的阶段，不是没有人懂你，而是你不懂你自己。——尼采

5661