SQL语句|PTA题目集

R10-36 基于视图StuView查询。

作者 李翔坤

单位 大连东软信息学院

从上题创建的视图StuView中查询年龄高于18岁的学生有哪些。

提示：请使用SELECT语句作答。

StuView 视图结构:

create view StuView(No, Name, Age)
as select Sno, sname, year(now())-year(birthdate) from Student;

StuView 视图数据样例

StuView视图：

输出样例:

select No,Name,Age
from StuView
where Age>18;

 R10-37 创建分组统计视图。

作者 李翔坤

单位 大连东软信息学院

创建每个专业学生修课信息的视图PerView，包括每个专业的专业名称、修课的学生人数、平均成绩。

表结构:

create table Student(
  sno char(8)  primary key,
  sname varchar(10) not null,
  gender char(2) check(gender='男' or gender='女'),
  birthdate  date,
  major varchar(20)  default '软件工程'
  );

create table SC(  
  scid  int auto_increment  primary key,
  sno char(8)   references Student(sno),
  cno char(10)  references Course(cno),
  tno char(15)  references Teacher(tno),
  grade int check(grade>=0 and grade<=100),
  gpoint  decimal(2,1),   
  memo  text(100)  
  );

表样例

Student表：

sno	sname	gender	birthdate	major
21012101	李勇	男	2005-10-20	计算机科学
21012102	刘晨	男	2006-5-5	计算机科学
21012103	王晓敏	女	2005-10-6	计算机科学
21021101	李佳睿	男	2006-3-30	软件工程
21021102	吴宾	男	2005-9-21	软件工程
21021103	张海	男	2005-10-20	软件工程
21031101	钱晓萍	女	2006-6-1	网络工程
21031102	王大力	男	2005-11-15	网络工程
21041101	于洋	男	2006-3-15	数据科学
21041102	郭霖	男	2006-3-2	数据科学

SC表：

scid	sno	cno	tno	grade	gpoint	memo
null	21012101	c01	t200306m12132	90	null	null
null	21012101	c02	t200703m12218	86	null	null
null	21012101	c03	t200703m12218	null	null	缺考
null	21012102	c02	t200703m12218	78	null	null
null	21012102	c03	t200703m12218	66	null	null
null	21021102	c01	t200306m12132	82	null	null
null	21021102	c02	t200608f12205	75	null	null
null	21021102	c03	t200306m12132	null	null	缓考
null	21021102	c05	t201803f12405	50	null	null
null	21021103	c02	t200703m12218	68	null	null
null	21021103	c04	t201208m12308	92	null	null
null	21031101	c01	t200306m12132	80	null	null
null	21031101	c02	t200608f12205	95	null	null
null	21041102	c02	t200608f12205	56	null	null
null	21041102	c05	t201803f12405	88	null	null

输出样例:

PerView视图：

create view PerView as
select major as 专业名,count(distinct Student.sno) as 修课人数,avg(grade) as 平均成绩
from Student join SC
    on SC.sno=Student.sno
group by 专业名

 R10-38 从视图PerView中查询数据。

作者 李翔坤

单位 大连东软信息学院

从上题中创建的视图PerView中查询平均成绩超过75分的专业有哪些。

PerView视图结构:

 Create view PerView(专业名, 修课人数, 平均成绩)
AS Select major, count(distinct sc.sno), avg(grade) from student join sc on student.sno=sc.sno group by major;

PerView视图数据样例

PerView视图：

专业名	修课人数	平均成绩
数据科学	1	72.0
网络工程	1	87.5
计算机科学	2	80.0
软件工程	2	73.4

输出样例:

专业名	平均成绩
网络工程	87.5
计算机科学	80.0

select 专业名,平均成绩
from PerView
where 平均成绩>75;

R10-39 查询学时数高于所有课程平均学时数的课程信息

作者 李翔坤

单位 大连东软信息学院

查询学时数高于所有课程平均学时数的课程信息
提示：请使用SELECT语句作答。

表结构:

create table if not exists Course(
  cno char(10) primary key,
  cname varchar(20) not null,
  ccredit int check(ccredit>0), -- 课程学分
  semester int check(semester>0),  -- 学期
  period int  check(period>0)     -- 总学时
  );

表样例

Course表：

输出样例:

select cno,cname,ccredit,semester,period
from Course
where period>(select avg(period)
             from Course
             ); 

 

R10-40 查询平均成绩超过所有学生选课平均成绩的学生学号和平均成绩，列名用中文显示

作者 李翔坤

单位 大连东软信息学院

查询平均成绩超过所有学生选课平均成绩的学生学号和平均成绩，列名用中文显示。

提示：请使用SELECT语句作答。

表结构:

create table if not exists SC(    -- 选课成绩单表
  scid  int auto_increment  primary key,
  sno char(8)   references Student(sno),
  cno char(10)  references Course(cno),
  tno char(15)  references Teacher(tno),
  grade int check(grade>=0 and grade<=100),
  gpoint  decimal(2,1),     -- 学生得到的课程绩点
  memo  text(100)    --  备注
  );

表样例

SC表：

输出样例:

select sno as 学号,avg(grade) as 平均成绩
from SC
group by sno
having avg(grade)>(select avg(grade) from SC);