[凡鸽鸽]《课内学习16》MYSQL 实验14

课内学习16 — MYSQL14

实验14 源码如下：

create database xscj;
source C:\Users\Lenovo\Desktop\MYSQL\sy121.sql

create database yggl;
source C:\Users\Lenovo\Desktop\MYSQL\sy122.sql

use xscj;
create or replace view v1 as
select 学号,姓名,成绩 from xs join xs_kc using (学号) where 学号 not in (
select 学号 from xs_kc where 成绩<80 group by 学号) group by 学号 having count(*)>1;

select 学号,(case 性别 when '1' then '男' when '0' then '女' end) as 性别
,课程名,max(成绩) as 最高分 from xs_kc join xs using(学号)  join kc using(课程号) group by 课程名,性别;

create or replace view v2 as
select 学号, (case 性别 when '1' then '男' when '0' then '女' end) as 性别
, 课程名, max(成绩) as 最高分 from xs_kc join xs using(学号)  join kc using(课程号) group by 课程名, 性别;

select 学号, 姓名, v2.性别, 课程名, 最高分 from v2 join xs using (学号)
group by 课程名, v2.性别;