-一--1create database testcreate table TestInfo(TestID int, Stage int, TestName varchar(50))create table TestScore(TestID int, Class varchar(50), StudentName varchar(20), Score float)--2select stage, c 展开
-一--1create database testcreate table TestInfo(TestID int, Stage int, TestName varchar(50))create table TestScore(TestID int, Class varchar(50), StudentName varchar(20), Score float)--2select stage, count(1) 次数 from testinfo group by stage--3select class, avg(score) 平均成绩 from testscore where class = 'S1161' group by class--4update testscore set studentname = '张丽华' where studentname = '张立华'--5insert testscorevalues(1, 'S1162', '杨飞', 80)--6select a.class 班级, a.studentname 姓名, b.testname 内部测试名称, a.score 成绩 from testscore a join testinfo b on a.testid = b.testid where a.score <60--二--1select a.mname 姓名, max(case when b.fname = '语文' then c.score else 0 end) 语文, max(case when b.fname = '数学' then c.score else 0 end) 数学, max(case when b.fname = '英语' then c.score else 0 end) 英语, max(case when b.fname = '历史' then c.score else 0 end) 历史 from 学生表 a left join 成绩表 c on a.mid = c.mid join 课程表 b on b.fid = c.fid group by a.mname --2select a.mname 姓名, b.fname 课程, c.score 成绩 from 学生表 a join 成绩表 c on a.mid = c.mid join 课程表 b on b.fid = c.fid where c.score <70 --3select a.mname 姓名, avg(b.score) 平均分 from 学生表 a join 成绩表 b on a.mid = b.midorder by 平均分 desc--4create procedure usp1 @cishu intasselect a.mname 姓名, a.mid 学号 from 学生表 a join 成绩表 b on a.mid = b.midgroup by a.mname, a.midhaving count(1) = @cishu 收起
