求四个SQL语句

[red_berries]

同学发给我的笔试题，本以为没什么难度，可看了看，一个也不会，求高人给写出来学习一下。

1. 查询所有成绩大于或等于60分的学生各科成绩名单，查询结果排列次序：班级名称、科目名称、 学生姓名成绩，按班级和科目的升序及成绩降序排序；

2、查询没有录入成绩数据的学生及科目名单，查询结果数据排序次数：班级名称、科目名称、学生 名称；（这个应该是指有的学生只录入了部分课程）

3、计算每个班级各科的平均成绩；

4、查询各科成绩排前3名的学生名单，查询结果排列次序：科目名称、班级名称、学生姓名、成绩；


学生表stud：stud_no(学号,主键)，stud_name(学生姓名),stud_sex(性别),class_no(班级编号)
班级表class：class_no(班级编号,主键)，class_name(班级名称)
科目表subj: subj_no(科目编号,主键),subj_name(科目名称)
成绩表achi: stud_no(学号),subj_no(科目编号),achi_num(成绩数据)

[jasonren]

多做做就通了.并不难.

[gicjoe]

这个题我的面试过，比较的简单

[zhvsby]

-- 学生表stud：stud_no(学号,主键)，stud_name(学生姓名),stud_sex(性别),class_no(班级编号)
-- 班级表class：class_no(班级编号,主键)，class_name(班级名称)
-- 科目表subj: subj_no(科目编号,主键),subj_name(科目名称)
-- 成绩表achi: stud_no(学号),subj_no(科目编号),achi_num(成绩数据)
create table stud( stud_no int not null,stud_name varchar(50),stud_sex varchar(50),class_no int)
insert into stud
select 100,'JIM','boy',1
union
Select 101,'Harry','girl',2
union
Select 102,'Tom','boy',2
union
Select 103,'Berry','girl',1
create table class(class_no int not null,class_name varchar(50))
-- truncate table class
insert into class
Select 1,'一年级'
union
Select 2,'二年级'
create table subj(subj_no int not null,subj_name varchar(50))
insert into subj
Select 90,'YW'
union
Select 91,'SS'
union
Select 92,'YY'
union
Select 93,'RY'

create table achi(stud_no int,subj_no int, achi_num int)
--delete from achi where stud_no =103
insert into achi
Select 103,90,50
union
Select 103,91,51
union
Select 103,92,662
union
Select 103,93,663

(1)
Select class.class_name,subj.subj_name,stud.stud_name,achi.achi_num
From achi
join stud
On stud.stud_no=achi.stud_no
Join subj
On subj.subj_no=achi.subj_no
Join class
On class.class_no=stud.class_no
Where achi.achi_num >=60
Order by class.class_name,subj.subj_name,achi.achi_num DESC
(3)
Select class.class_no,subj.subj_no, Avg(achi.achi_num) pingjun
From achi
join stud
On stud.stud_no=achi.stud_no
join class
on class.class_no=stud.class_no
join subj
on subj.subj_no=achi.subj_no
group by class.class_no,subj.subj_no
order by class.class_no

[icelattice]

没有写对的。都怎么乐？那么难嘛？

[忆轩辕]

[Quote=引用 12 楼 roy_88 的回复:]
HTML code查询没有录入成绩数据的学生及科目名单



只指一个学生，5科只考了3科?
subj科目表的所有课堂都是针对每一个学生的?


要确定以上条件，才好写..


1. 查询所有成绩大于或等于60分的学生各科成绩名单?

结果是学生必须是考了5科，并都大于60分.......
[/Quote]

断句问题-_,-

查询所有 成绩大于或等于60分的学生各科成绩名单

[lanyuxcm]

上面SQL语句中有许多表关联:
在设计表的时候不妨设计点冗余字段,这样,在查询的时候不用联表查询,
效率会提高点;
这也许就是空间换时间吧!

[ChinaJiaBing]

1. select d.班级名称,c.学号,c..学生姓名 ,b.科目名称,a.成绩数据 
from 成绩表 a  inner join 科目表 b  on a.科目编号=b.科目编号
right  join 学生表 c on a.学号= c．学号 inner join 班级表 d 
on c.班级编号=d.班级编号 
where a.成绩数据>=60
order by d.班级名称，b.科目名称,a.成绩数据 desc

2. select d.班级名称,c.学号,c.学生姓名 ,b.科目名称,a.成绩数据 
from 成绩表 a  inner join 科目表 b  on a.科目编号=b.科目编号
right  join 学生表 c on a.学号= c．学号 inner join 班级表 d 
on c.班级编号=d.班级编号 
where a.成绩数据<60
order by d.班级名称，b.科目名称,a.成绩数据 desc

3. select  d.班级名称,b.科目名称 ,sum(a.成绩数据)/count(*) as 平级成绩
from 成绩表 a  inner join 科目表 b  on a.科目编号=b.科目编号
right  join 学生表 c on a.学号= c．学号 inner join 班级表 d 
on c.班级编号=d.班级编号 
group by d.班级名称, b.科目名称

4. 
select top 3 b.科目名称,d.班级名称,c.学生姓名, A课程成绩数据,B课程成绩数据
from (select 学号,科目编号,case when 科目编号='A' then 成绩数据 else 0 end as A课成绩数据,
   case when 科目编号='B' then 成绩数据 else 0 end as B课程成绩数据
s) a  inner join 科目表 b  on a.科目编号=b.科目编号
right  join 学生表 c on a.学号= c．学号 inner join 班级表 d 
on c.班级编号=d.班级编号 
order by A课程成绩数据 desc,B课程成绩数据 desc

[comszsoft]

lz好好学习，天天向上

[三下鱼]

[Quote=引用 28 楼 comszsoft 的回复:]
lz好好学习，天天向上
[/Quote]

[Lyw110]

create table stud
(
    stud_no varchar(10),
    stud_name varchar(10),
    stud_sex varchar(4),
    class_no varchar(10)
)
create table class
(
    class_no varchar(10),
    class_name varchar(20)
)
create table subj
(
    subj_no varchar(10),
    subj_name varchar(20)
)
create table achi
(
    stud_no varchar(10),
    subj_no varchar(10),
    achi_num float
)
go

insert into class
select '1', '1班' union all
select '2', '2班' union all
select '3', '3班'

insert into stud
select '1', 'student1', '男', '1' union all
select '2', 'student2', '女', '1' union all
select '3', 'student3', '男', '1' union all
select '4', 'student4', '男', '2' union all
select '5', 'student5', '女', '2' union all
select '6', 'student6', '男', '3'

insert into subj
select '1', 'Chinese' union all
select '2', 'Math' union all
select '3', 'English'

insert into achi
select '1', '1', 68 union all
select '1', '2', 65 union all
select '1', '3', 80 union all
select '2', '2', 68 union all
select '2', '3', 56 union all
select '5', '1', 68 union all
select '6', '3', 50
go


--1
select c.class_name, b.subj_name, s.stud_name, a.achi_num 
from stud s, class c, subj b, achi a
where not exists (select * from achi a1
                  where a1.achi_num < 60 and a1.stud_no = s.stud_no)
    and s.class_no = c.class_no and s.stud_no = a.stud_no
    and b.subj_no = a.subj_no 
order by class_name, subj_name asc, achi_num desc

-- 2
select c.class_name, b.subj_name, s.stud_name
from stud s inner join class c on s.class_no = c.class_no, subj b
where not exists (select * from achi a
                  where a.stud_no = s.stud_no and a.subj_no = b.subj_no)

-- 3
select c.class_no, b.subj_no, achi_num = avg(a.achi_num) 
from stud s, class c, subj b, achi a
where s.class_no = c.class_no and s.stud_no = a.stud_no
    and b.subj_no = a.subj_no
group by c.class_no, b.subj_no

-- 4
--???

[Lyw110]

-- 4
select c.class_name, b.subj_name, s.stud_name 
from subj b, achi a, class c, stud s
where a.achi_num in (select top 3 achi_num 
                     from achi
                     where subj_no = b.subj_no
                     order by achi_num desc)
    and s.class_no = c.class_no and s.stud_no = a.stud_no
    and b.subj_no = a.subj_no
order by b.subj_name, c.class_name, s.stud_name, a.achi_num asc

[忆轩辕]

--a学生表stud：stud_no(学号,主键)，stud_name(学生姓名),stud_sex(性别),class_no(班级编号)
--b班级表class：class_no(班级编号,主键)，class_name(班级名称)
--d科目表subj: subj_no(科目编号,主键),subj_name(科目名称)
--c成绩表achi: stud_no(学号),subj_no(科目编号),achi_num(成绩数据)
drop table achi,stud,class,subj
create table stud
(stud_no varchar(30),
stud_name varchar(20),
stud_sex bit,
class_no varchar(30),
constraint PK_stud primary key(stud_no))
go
create table class
(class_no varchar(30),
class_name varchar(30)
constraint PK_class primary key(class_no))
go
alter table stud
add constraint FK_stud foreign key(class_no) references class(class_no)
go
create table subj
(subj_no varchar(30),
subj_name varchar(50),
constraint PK_subj primary key(subj_no))
go
create table achi
(stud_no varchar(30),
subj_no varchar(30),
achi_num float,
constraint FK_achi foreign key(subj_no) references subj(subj_no))
go
insert into class
select '01','一班'
union all
select '02','二班'
union all
select '03','三班'
go
insert into subj
select '023','语文'
union all
select '024','英语'
union all
select '025','体育'
union all
select '026','马克思主义哲学'
union all
select '027','物理'
union all
select '028','数学'
union all
select '029','化学'
go
insert into stud
select '001','张三','1','01'
union all
select '002','李四','1','01'
union all
select '003','王六','1','02'
union all
select '007','赵七','1','02'
go
insert into achi
select '001','023','50'
union all
select '001','025','60'
union all
select '001','026','60'
union all
select '002','024','70'
union all
select '002','028','60'
union all
select '002','027','90'
union all
select '003','025','40'
union all
select '003','026','30'
go


--查询所有成绩大于或等于60分的学生各科成绩名单，查询结果排列次序：班级名称、科目名称、 学生姓名成绩，按班级和科目的升序及成绩降序排序

select b.class_name,d.subj_name,a.stud_name,c.achi_num
from stud as a inner join class as b on a.class_no = b.class_no
inner join achi as c on a.stud_no = c.stud_no
inner join subj as d on c.subj_no = d.subj_no
where a.stud_no in
(select stud_no from achi group by stud_no having min(achi_num)>=60)
order by b.class_name,d.subj_name,c.achi_num desc

--查询没有录入成绩数据的学生及科目名单，查询结果数据排序次数：班级名称、科目名称、学生名称；（这个应该是指有的学生只录入了部分课程）

select b.class_name, d.subj_name, a.stud_name
from stud a inner join class b on a.class_no = b.class_no, subj d
where not exists (select * from achi c
where c.stud_no = a.stud_no and c.subj_no = d.subj_no)

[huangqing_80]

路过，不答了，因为结果都贴出N条了

[fish_yht]

学习来了

[ilovewalk]

应该不难吧,以后有空在看.

[red_berries]

太感谢了，以为没有人帮忙呢，这两天把SQL装上再研究

[ws_hgo]

好快的速度啊！

[yo_258]

很经典的题目

[Zgydd]

很基础……看看书吧，不好意思抄书给你看……

[忆轩辕]

--计算每个班级各科的平均成绩

select b.class_name,d.subj_name,avg(achi_num) as 平均成绩 from
stud as a inner join class as b on a.class_no = b.class_no
inner join achi as c on a.stud_no = c.stud_no
inner join subj as d on c.subj_no = d.subj_no
group by b.class_name,subj_name
order by b.class_name,subj_name

--查询各科成绩排前3名的学生名单，查询结果排列次序：科目名称、班级名称、学生姓名、成绩

select d.subj_name,b.class_name,a.stud_name,c.achi_num from
stud as a inner join class as b on a.class_no = b.class_no
inner join achi as c on a.stud_no = c.stud_no
inner join subj as d on c.subj_no = d.subj_no
where c.achi_num in (select top 3 achi_num
from achi
where subj_no = d.subj_no
order by achi_num desc)
order by d.subj_name,c.achi_num desc

补上昨天没贴完的