--插入学生表测试数据
10年积累的网站建设、做网站经验，可以快速应对客户对网站的新想法和需求。提供各种问题对应的解决方案。让选择我们的客户得到更好、更有力的网络服务。我虽然不认识你，你也不认识我。但先网站设计后付款的网站建设流程，更有城中免费网站建设让你可以放心的选择与我们合作。
insert into Student values('01' , '赵雷' , '1990-01-01' , '男');
insert into Student values('02' , '钱电' , '1990-12-21' , '男');
insert into Student values('03' , '孙风' , '1990-05-20' , '男');
insert into Student values('04' , '李云' , '1990-08-06' , '男');
insert into Student values('05' , '周梅' , '1991-12-01' , '女');
insert into Student values('06' , '吴兰' , '1992-03-01' , '女');
insert into Student values('07' , '郑竹' , '1989-07-01' , '女');
insert into Student values('08' , '王菊' , '1990-01-20' , '女');
--课程表测试数据
insert into Course values('01' , '语文' , '02');
insert into Course values('02' , '数学' , '01');
insert into Course values('03' , '英语' , '03');
--教师表测试数据
insert into Teacher values('01' , '张三');
insert into Teacher values('02' , '李四');
insert into Teacher values('03' , '王五');
--成绩表测试数据
insert into Score values('01' , '01' , 80);
insert into Score values('01' , '02' , 90);
insert into Score values('01' , '03' , 99);
insert into Score values('02' , '01' , 70);
insert into Score values('02' , '02' , 60);
insert into Score values('02' , '03' , 80);
insert into Score values('03' , '01' , 80);
insert into Score values('03' , '02' , 80);
insert into Score values('03' , '03' , 80);
insert into Score values('04' , '01' , 50);
insert into Score values('04' , '02' , 30);
insert into Score values('04' , '03' , 20);
insert into Score values('05' , '01' , 76);
insert into Score values('05' , '02' , 87);
insert into Score values('06' , '01' , 31);
insert into Score values('06' , '03' , 34);
insert into Score values('07' , '02' , 89);
insert into Score values('07' , '03' , 98);
-- 1、查询"01"课程比"02"课程成绩高的学生的信息及课程分数
select c.*,a.s_score as 01课程score,b.s_score as 02课程score from
score a,score b
left join student c
on b.s_id = c.s_id
where a.s_id = b.s_id and a.c_id = '01' and b.c_id = '02' and a.s_score b.s_score;
-- 2、查询"01"课程比"02"课程成绩低的学生的信息及课程分数
select a.* ,b.s_score as 01课程,c.s_score as 02课程 from student a
join score b
on a.s_id=b.s_id and b.c_id = '01'
left join score c
on b.s_id = c.s_id and c.c_id = '02'
where b.s_score c.s_score ;
-- 3、查询平均成绩大于等于60分的同学的学生编号和学生姓名和平均成绩
select a.s_id,a.s_name,round(avg(b.s_score),2) as 平均成绩 from student a
join score b
on a.s_id = b.s_id
group by b.s_id having 平均成绩 = 60;
备注：round[avg(成绩),1]里,round是四舍五入函数，1代表保留1位小数
-- 4、查询平均成绩小于60分的同学的学生编号和学生姓名和平均成绩
-- (包括有成绩的和无成绩的)
select b. ,round(avg(a.s_score),2) as 平均成绩 from
student b
left join score a on b.s_id = a.s_id group by a.s_id having 平均成绩 60
union
select b. ,0 as 平衡成绩 from student b where b.s_id not in (select s_id from score);
-- 5、查询所有同学的学生编号、学生姓名、选课总数、所有课程的总成绩
select a.s_id,a.s_name,count(b.c_id) as 选课总数 ,sum(b.s_score) as 总分 from student a
left join score b
on a.s_id = b.s_id group by s_id ;
-- 6、查询"李"姓老师的数量
select count(*) as 李姓老师数量 from teacher where t_name like '李%';
-- 7、查询学过"张三"老师授课的同学的信息
select a.* from student a join score b
on a.s_id = b.s_id
where b.c_id in (select c.c_id from course c
join teacher d on c.t_id = d.t_id where d.t_name = '张三');
-- 8、查询没学过"张三"老师授课的同学的信息
select a.* from student a left join score b on a.s_id = b.s_id where a.s_id not in
(select s_id from score where c_id =
(select c_id from course where t_id =
(select t_id from teacher where t_name = '张
三'))) group by a.s_id;
-- 9、查询学过编号为"01"并且也学过编号为"02"的课程的同学的信息
select * from student where s_id in
(select a.s_id from score a join score b on a.s_id = b.s_id
where a.c_id = '01' and b.c_id = '02');
-- 10、查询学过编号为"01"但是没有学过编号为"02"的课程的同学的信息
select * from student where s_id in
(select s_id from score where c_id = '01' )
and s_id not in (select s_id from score where c_id = '02' );
-- 11、查询没有学全所有课程的同学的信息
select * from student where s_id not in
(select s_id from score group by s_id having count(c_id) = 3);
-- 12、查询至少有一门课与学号为"01"的同学所学相同的同学的信息
select distinct a.* from student a left join score b
on a.s_id = b.s_id where b.c_id in
(select c_id from score where s_id = '01') and a.s_id != '01' ;
注意：distinct是去重的
-- 13、查询和"01"号的同学学习的课程完全相同的其他同学的信息
select * from student where s_id in
(select s_id from score group by s_id having count(c_id) =
(select count(c_id) from score where s_id = '01') and s_id not in
(select s_id from score where c_id not in
(select c_id from score where s_id = '01')) and s_id != '01');
-- 14、查询没学过"张三"老师讲授的任一门课程的学生姓名
select s_name from student where s_id not in
(select s_id from score where c_id in
(select c_id from course where t_id in
(select t_id from teacher where t_name ='张三')));
-- 15、查询两门及其以上不及格课程的同学的学号，姓名及其平均成绩
select a.s_id ,b.s_name,round(avg(a.s_score),2) as 平均成绩 from score a
left join student b on a.s_id = b.s_id
where s_score 60 group by s_id having count(1) =2;
或者试试
select a.s_id ,b.s_name,round(avg(a.s_score),2) as 平均成绩 from score a
left join student b on a.s_id = b.s_id
where a.s_score 60 group by a.s_id having count(*) =2;
-- 16、检索"01"课程分数小于60，按分数降序排列的学生信息
select a.* ,b.c_id ,b.s_score from student a
left join score b on a.s_id = b.s_id
where b.c_id = '01' and b.s_score 60
order by b.s_score desc;
-- 17、按平均成绩从高到低显示所有学生的所有课程的成绩以及平均成绩
select a.s_name ,
sum(case when b.c_id = '01' then s_score else null end ) as 语文,
sum(case when b.c_id = '02' then s_score else null end ) as 数学,
sum(case when b.c_id = '03' then s_score else null end ) as 英语,
round(avg(s_score),2) as 平均成绩
from student a left join score b on a.s_id = b.s_id group by a.s_name
order by 平均成绩 desc;
-- 18.查询各科成绩最高分、最低分和平均分：以如下形式显示：课程ID，课程name，最高分，最低分，平均分，及格率，中等率，优良率，优秀率
--及格为=60，中等为：70-80，优良为：80-90，优秀为：=90
select b.c_id,b.c_name,
max(a.s_score) as 最高分,
min(a.s_score) as 最低分,
round(avg(a.s_score),2) as 平均分,
round(sum(case when a.s_score= 60 then 1 else 0 end)/count(s_id),2) as 及格率 ,
round(sum(case when a.s_score= 70 and a.s_score 80 then 1 else 0 end)/count(s_id),2) as 中等率,
round(sum(case when a.s_score= 80 and a.s_score 90 then 1 else 0 end)/count(s_id),2) as 优良率,
round(sum(case when a.s_score= 90 then 1 else 0 end)/count(s_id),2) as 优秀率
from score a
left join course b
on a.c_id = b.c_id group by b.c_id;
-- 19、按各科成绩进行排序，并显示排名
第一种：
set @pre_c_id:= '01';
set @rank:=0;
select tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2;
如果看不懂用第二种方法：
SELECT a.c_id,a.s_id,a.s_score,COUNT(b.s_score)+1 AS 排名
FROM score a LEFT JOIN score b ON a.s_scoreb.s_score AND a.c_id = b.c_id
GROUP BY a.c_id,a.s_id,a.s_score ORDER BY a.c_id,排名,a.s_id ASC
-- 20、查询学生的总成绩并进行排名
set @rank:=0;
select * ,(@rank:=@rank+1) as rank from
(select s_id ,sum(s_score) as 总成绩 from score
group by s_id order by 总成绩 desc) tb1;
-- 21、查询不同老师所教不同课程平均分从高到低显示
select a.c_id, d.t_name,round(avg(a.s_score)) as 平均分 from score a
left join student b on a.s_id = b.s_id
left join course c on a.c_id = c.c_id
left join teacher d on c.t_id = d.t_id group by a.c_id
order by 平均分 desc;
-- 22、查询所有课程的成绩第2名到第3名的学生信息及该课程成绩
set @pre_c_id:= '01';
set @rank:=0;
select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 = 2 or 排名 =3;
-- 23、统计各科成绩各分数段人数：课程编号,课程名称,[100-85],(85-70],(70-60],(0-60]及所占百分比
select b.c_id,b.c_name ,
sum(case when a.s_score =85 then 1 else 0 end) as 100-85 ,
concat(round(100 sum(case when a.s_score =85 then 1 else 0 end)/count( ),2), '%') as 百分比,
sum(case when a.s_score 85 and a.s_score =70 then 1 else 0 end) as 85-70 ,
concat(round(100 sum(case when a.s_score 85 and a.s_score =70 then 1 else 0 end)/count( ),2),'%') as 百分比,
sum(case when a.s_score 70 and a.s_score =60 then 1 else 0 end) as 70-60 ,
concat(round(100 sum(case when a.s_score 70 and a.s_score =60 then 1 else 0 end)/count( ),2) ,'%')as 百分比,
sum(case when a.s_score 60 and a.s_score =0 then 1 else 0 end) as 60-0 ,
concat(round(100 sum(case when a.s_score 60 and a.s_score =0 then 1
else 0 end)/count( ),2),'%') as 百分比
from score a left join course b on a.c_id = b.c_id group by b.c_id;
-- 24、查询学生平均成绩及其名次
select tb1.*,(@rank:=@rank +1 ) as rank from
(select s_id ,round(avg(s_score),2) as 平均成绩 from score
group by s_id order by 平均成绩 desc) tb1,(select @rank:=0) b;
-- 25、查询各科成绩前三名的记录
set @pre_c_id:= '01';
set @rank:=0;
select b.s_name,tb2.s_id ,tb2.c_id,tb2.s_score,tb2.排名 from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2 join student b on tb2.s_id = b.s_id where 排名 4;
-- 26、查询每门课程被选修的学生数
select c_id ,count(s_id) as 选修人数 from score group by c_id;
-- 27、查询出只有两门课程的全部学生的学号和姓名
select a.s_id ,b.s_name from score a left join student b on a.s_id = b.s_id group by s_id having count(*) = 2;
-- 28、查询男生、女生人数
select sum(case s_sex when '男' then 1 else 0 end) as 男生人数,
sum(case s_sex when '女' then 1 else 0 end) as 女生人数 from student;
-- 29、查询名字中含有"风"字的学生信息
select * from student where s_name like '%风%';
-- 30、查询同名同性学生名单，并统计同名人数
--略，不想写
-- 31、查询1990年出生的学生名单
select * from student where s_birth like '1990%';
-- 32、查询每门课程的平均成绩，结果按平均成绩降序排列，平均成绩相同时，按课程编号升序排列
select c_id ,round(avg(s_score),2) as 平均成绩 from score group by c_id order by 平均成绩 desc, c_id asc;
-- 33、查询平均成绩大于等于85的所有学生的学号、姓名和平均成绩
select a.s_id,b.s_name ,round(avg(s_score),2) as 平均成绩 from score a
left join student b on a.s_id = b.s_id group by a.s_id having 平均成绩=85;
-- 34、查询课程名称为"数学"，且分数低于60的学生姓名和分数
select b.s_name ,a.s_score from score a
left join student b on a.s_id = b.s_id
where a.c_id=(select c_id from course where c_name = '数学')and a.s_score 60;
-- 35、查询所有学生的课程及分数情况；
select b.s_name,
sum(case when a.c_id = '01' then a.s_score else null end) as 语文,
sum(case when a.c_id = '02' then a.s_score else null end) as 数学,
sum(case when a.c_id = '03' then a.s_score else null end) as 英语
from score a right join student b on a.s_id = b.s_id group by b.s_name
-- 36、查询任何一门课程成绩在70分以上的姓名、课程名称和分数；
select b.s_name,
sum(case when a.c_id = '01' then a.s_score else null end) as 语文,
sum(case when a.c_id = '02' then a.s_score else null end) as 数学,
sum(case when a.c_id = '03' then a.s_score else null end) as 英语
from score a right join student b on a.s_id = b.s_id group by b.s_name having 语文= 70 or 数学= 70 or 英语= 70 ;
-- 37、查询不及格的课程
select a.s_id,a.c_id,b.c_name,a.s_score from score a
left join course b on a.c_id = b.c_id where a.s_score60;
--38、查询课程编号为01且课程成绩在80分以上的学生的学号和姓名；
select a.s_id,b.s_name from score a left join student b on a.s_id = b.s_id where a.c_id = '01' and a.s_score=80;
-- 39、求每门课程的学生人数
select c_id,count(*) as 学生人数 from score group by c_id ;
-- 40、查询选修"张三"老师所授课程的学生中，成绩最高的学生信息及其成绩
select a.*,b.c_id,max(b.s_score) as 最高成绩 from student a
right join score b on a.s_id = b.s_id
group by b.c_id
having b.c_id = (select c_id from course
where t_id = (select t_id from teacher where t_name = '张三'));
-- 41、查询不同课程成绩相同的学生的学生编号、课程编号、学生成绩
--（这题我搞不清题目是什么意思,是指查找学生个体参加了的所有课程的成绩各不相同的那个学生信息呢？还是所有课程之间做对比呢，我更倾向于理解为前者）
--理解为前者的写法
select * from
(select * from score group by s_id,s_score) tb1
group by s_id having count(*) = 1;
--理解为后者的写法
select distinct a.s_id,a.c_id,b.s_score from score a,score b where a.c_id != b.c_id and a.s_score = b.s_score;
-- 42、查询每门课程成绩最好的前两名
set @pre_c_id:= '01';
set @rank:=0;
select tb2.s_id ,tb2.c_id,tb2.s_score from
(select *,(case when tb1.c_id = @pre_c_id then @rank:=@rank+1 else @rank:=1 end) as 排名,
(case when @pre_c_id = tb1.c_id then @pre_c_id else @pre_c_id:=tb1.c_id end ) as pre_c_id
from
(select * from score order by c_id,s_score desc) tb1 )tb2
join student b on tb2.s_id = b.s_id where 排名 3;
-- 43、统计每门课程的学生选修人数（超过5人的课程才统计）。要求输出课程号和选修人数，查询结果按人数降序排列，若人相同，按课程号升序排列
select c_id ,count(*) as 选修人数 from score group by c_id having 选修人数5 order by 选修人数 desc , c_id asc;
-- 44、检索至少选修两门课程的学生学号
select s_id from score group by s_id having count(*) = 2;
-- 45、查询选修了全部课程的学生信息
select * from student where s_id in
(select s_id from score group by s_id having count(*) = 3)
--46、查询各学生的年龄
select s_name ,(date_format(now(),'%Y')-date_format(s_birth,'%Y') + (CASE when date_format(now(),'%m%d')=date_format(s_birth,'%m%d') then 0 else 1 end)) as age
from student
-- 47、查询本周过生日的学生
---(实现得并不完全，因为例如出生月日为‘01-01’在每一年可能会输入不同周）
select * from student where week(date_format(s_birth,'%m%d'))=week(date_format(now(),'%m%d')) ;
-- 48、查询下周过生日的学生
select * from student
where week(date_format(s_birth,'%m%d'))=week(date_format(date_add(now(),interval 7-dayofweek(now())+1 day),'%m%d'));
-- 49、查询本月过生日的学生
select * from student where date_format(s_birth,'%m') = date_format(now(),'%m')
-- 50、查询下月过生日的学生
select * from student where date_format(s_birth,'%m') = date_format(date_add(now(),interval 1 month),'%m')
题那么多，分这么少，没有积极性，没人给你做啊。
我有时间再给你看看吧。
简答读程题
1列出Sql Server 2000数据库中常见的数据库对象。
表、视图、存储过程、触发器、索引
2简述INSERT触发器的工作原理
当insert一条或多条数据的时候，即触发该触发器。
单选题
1、关于主键描述正确的是：（ c ）
A、包含一列 B、包含两列 C、包含一列或者多列 D、以上都不正确
2、模式查找like '_a%',下面哪个结果是可能的( b )。
A、aili B、bai
C、bba D、cca
3、下列途径哪个不是实现值域完整性约束的 __D___ 。
A 、rule( 规则 ) B 、default C 、not null D 、trancation
4 关于视图和索引，下列说法那个是正确的___B__ 。
A 、视图是虚表，观察到的数据是实际基本表中的数据。
B 、索引查找法是比表扫描法查询更快的一种方法。
C 、视图是查询数据的一种方法，只能基于基本表建立。
D 、索引的创建只和数据的存储有关系。
5在SQL SERVER服务器上，存储过程是一组预先定义并（D）的Transact-SQL语句。
A、另保存 B、编写 C、解释说明 D、编译
6、SQL Server 2000是一个（C）的数据库系统。
A、层次型 B、 网状型 C、关系型 D、组合型
7、SQL语言中，条件“年龄BETWEEN 40 AND 50”表示年龄在40至50之间，且( a )。
A、包括40岁和50岁 B、不包括40岁和50岁
C、包括40岁但不包括50岁 D、包括50岁但不包括40岁
8、面对索引的相关描述正确的是：（B）。
A、经常被查询的列不适合建索引。 B、 列值唯一的列适合建索引。
C、有很多重复值的列适合建索引。 D、 是外键或主键的列不适合建索引。
--) 用SQL语句建立P表。
USE [SPJ]
GO
/****** Object: Table [dbo].[P] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
IF EXISTS(SELECT * FROM SYSOBJECTS WHERE NAME = 'P')
DROP TABLE P
GO
CREATE TABLE [dbo].[P](
[PNO] [nvarchar](50) NOT NULL,
[PNAME] [nvarchar](50) NOT NULL,
[COLOR] [nvarchar](50) NOT NULL,
[WEIGHT] [int] NOT NULL
)
GO
--2)用SQL语句找出所有零件的名称、颜色、重量。
SELECT PNAME,COLOR,WEIGHT FROM P
--3)用SQL语句把全部红色零件的颜色改为黄色。
UPDATE P SET COLOR = 'YELLOW' WEHRE COLOR = 'RED'
--4) 用SQL语句由S5供给J4的零件P6改为由S3供应。
UPDATE SPJ SET SNO = 'S3' WEHRE PNO = 'P6' AND JNO = 'J4' AND SNO = 'S5'
重庆分公司
重庆分公司
