PTA之SQL训练题

10-24 创建视图计算学生平均绩点

现有一个学生数据库，内包含学生表（Student）、课程表（Course）和选修表（SC）。

通常在学生毕业前，教务工作人员需要统计每位学生的课程平均绩点以检查其是否达到毕业条件和授位条件。由于数据库中只有课程百分制成绩，因此需要先将课程成绩转换为课程绩点后再计算其平均绩点。课程成绩与绩点之间的转换规则如下：

请设计一个视图V_average_point，计算学生平均绩点。

提示：请使用CREATE VIEW语句作答，并请注意数据表名、列名大小写。

表结构:

学生表（Student）、课程表（Course）和选修表（SC）结构如下：

CREATE TABLE `Student` (
  `Sno` varchar(20) NOT NULL,
  `Sname` varchar(10) DEFAULT NULL,
  `Ssex` varchar(2) DEFAULT NULL,
  `Sage` int(3) DEFAULT NULL,
  `Sdept` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`Sno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `Course` (
  `Cno` varchar(10) NOT NULL,
  `Cname` varchar(20) DEFAULT NULL,
  `Cpno` varchar(10) DEFAULT NULL,
  `Ccredit` int(3) DEFAULT NULL,
  PRIMARY KEY (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

CREATE TABLE `SC` (
  `Sno` varchar(20) NOT NULL,
  `Cno` varchar(10) NOT NULL,
  `Grade` int(3) DEFAULT NULL,
  PRIMARY KEY (`Sno`,`Cno`),
  KEY `Cno` (`Cno`),
  CONSTRAINT `sc_ibfk_1` FOREIGN KEY (`Sno`) REFERENCES `student` (`Sno`),
  CONSTRAINT `sc_ibfk_2` FOREIGN KEY (`Cno`) REFERENCES `course` (`Cno`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

表样例

Student

表：

Course

表：

SC

表：

输出样例:

视图

V_average_point

输出：

取消

create view V_average_point
as select Sdept,Sno,avg(Grade) as Average_point
from 
(
    select a.Sdept,a.Sno,(case when Grade<60 then 0 else (Grade-50)/10 end) as Grade
    from Student as a
    right join SC as b on a.Sno = b.Sno
) as c
group by Sno

0-14 创建视图，包含拥有属性值个数大于1的商品信息

本题目要求编写SQL语句，
结合

sh_goods

表和

sh_goods_attr_value

表创建视图

view_goods_2

，包含拥有属性值的数量大于1个的商品对应的商品

id

和

name

。

表结构:

CREATE TABLE sh_goods (
  id INT  PRIMARY KEY,                         --商品id
  category_id INT  NOT NULL DEFAULT 0 ,        -- 商品分类id
  name VARCHAR(120) NOT NULL,                  --商品名称
  keyword VARCHAR(255) NOT NULL,               -- 关键词编号
  content TEXT NOT NULL ,                      --商品详情
  price DECIMAL(10, 2)  NOT NULL DEFAULT 0 ,   --价格
  stock INT  NOT NULL DEFAULT 0,               -- 库存
  score DECIMAL(3, 2)  NOT NULL DEFAULT 0 ,    -- 用户评分
  comment_count INT  NOT NULL DEFAULT 0        -- 评论数量
) ;

CREATE TABLE sh_goods_attr_value (
id INT PRIMARY KEY ,-- 属性值id,
goods_id INT NOT NULL DEFAULT 0 , --商品id
attr_id INT  NOT NULL DEFAULT 0 ,-- 属性id
attr_value VARCHAR(80) NOT NULL  --属性值
);

表样例

sh_goods

表：

sh_goods_attr_value

表：

视图样例:

create view view_goods_2
as select
id,name
from sh_goods
where id in(
    select goods_id
    from sh_goods_attr_value
    group by goods_id
    having count(*) > 1
)