SQL语句18~1小时内,答对给最高分~
发布网友
发布时间：2022-04-22 03:53
共9个回答
热心网友
时间：2022-04-10 02:35
3、计算所有学生的年龄，并显示其基本信息（学号、姓名、性别、班级、年龄）
select
fld_id as 学号,fld_name as 姓名,fld_sex as 性别,fld_class as 班级,sum(datediff(yy,fld_birthday,getdate())) as 年龄
from fld_info
group by 学号,姓名,性别,班级 with rollup
having grouping(学号)=1 and grouping(班级)=1
4、显示数字媒体0706班所有女生数学课程成绩，同时包含部分基本信息（学号、姓名）
select
FI.fld_id as 学号,FI.fld_name as 姓名,FS.fld_score as 成绩
from fld_info FI
inner join fld_score FS on FI.fld_id=FS.fld_stid
inner join fld_class FC on FC.fld_id=FS.fld_cid
where FI.fld_class='数字媒体0706班' and FC.fld_name='数学课程' and FI.fld_sex='女'
5、显示姓“张”的同学各课程成绩，包含学号、姓名、性别、班级、课程名称、成绩
select
FI.fld_id as 学号,FI.fld_name as 姓名,FI.fld_sex as 性别,FC.fld_name as 课程名称,FS.fld_score as 成绩
from fld_info FI
inner join fld_score FS on FI.fld_id=FS.fld_stid
inner join fld_class FC on FC.fld_id=FS.fld_cid
where FI.fld_name like N'张%'
6、按班级统计各班级男、女生数量
select
fld_class as 班级名称,sum(case when fld_sex='男' then 1 else 0 end) as 男 ,sum(case when fld_sex='女' then 1 else 0 end) as 女
from fld_info
group by fld_class
7、统计各课程的平均成绩
select
FC.fld_name as 课程,avg(FS.fld_score) as 平均成绩
from fld_class FC
inner join fld_score FS on FC.fld_id=FS.fld_cid
group by FC.fld_name
8、按班级统计各课程的平均成绩
select
FI.fld_class as 班级名称,FC.fld_name as 课程名称,avg(FS.fld_score) as 平均成绩
from fld_info FI
inner join fld_score FS on FI.fld_id=FS.fld_stid
inner join fld_class FC on FC.fld_id=FS.fld_cid
group by FI.fld_class,FC.fld_name
9、统计各班级数学课程平均成绩，并按从高到低排序
select
FI.fld_class as 班级名称,FC.fld_name as 课程名称,avg(FS.fld_score) as 平均成绩
from fld_info FI
inner join fld_score FS on FI.fld_id=FS.fld_stid
inner join fld_class FC on FC.fld_id=FS.fld_cid
where FC.fld_name='数学'
group by FI.fld_class,FC.fld_name
order by 平均成绩 desc
10、计算数字媒体0706班各学生所有课程总成绩，并显示前10名学生基本信息及其总分
;with c--SQL2005以上可用的CTE,SQL2000用嵌套和临时表处理
as
(
select
FI.fld_class as 班级名称,FI.fld_id as 学号,FI.fld_name as 姓名,sum(FS.fld_score)总分
from fld_info FI
inner join fld_score FS on FI.fld_id=FS.fld_stid
where FI.fld_class='数字媒体0706班'
group by FI.fld_class,FI.fld_id,FI.fld_name)
select
*
from
(select *,row=row_number()over(partition by 班级名称 order by 总分 desc) from c)t
where row<=10
11、按班级分组，计算各班各门功课平均成绩，并显示
select
FI.fld_class as 班级名称,FC.fld_name as 课程名称,avg(FS.fld_score) as 平均成绩
from fld_info FI
inner join fld_score FS on FI.fld_id=FS.fld_stid
inner join fld_class FC on FC.fld_id=FS.fld_cid
group by FI.fld_class,FC.fld_name
12、增加一条课程记录
insert fld_class(fld_id,fld_name) values(1,'A')
--or
insert fld_class(fld_id,fld_name) select 1,'A'
13、增加一条学生记录
insert fld_info(fld_id,fld_name,fld_sex,fld_birthday,fld_class) values(1,'张三','男','1999-01-01','数字媒体0706班')
--or
insert fld_info(fld_id,fld_name,fld_sex,fld_birthday,fld_class) select 1,'张三','男','1999-01-01','数字媒体0706班'
14、增加一条学生成绩记录
insert fld_score(fld_stid,fld_cid,fld_score)values(1,1,60)
--or
insert fld_score(fld_stid,fld_cid,fld_score)select 1,1,60
15、修改数字媒体0706班姓名为“张三”同学的基本资料（包括性别，出生日期）
update fld_info
set fld_sex='女',fld_birthday='19-01-01'
where fld_class='数字媒体0706班' and fld_name='张三'
16、修改数字媒体0706班姓名为“张三”同学的数学成绩为60分
update FS
set fld_score=90
from fld_info FI
inner join fld_score FS on FI.fld_id=FS.fld_stid
inner join fld_class FC on FC.fld_id=FS.fld_cid
where FI.fld_name='张三' and FI.fld_class='数字媒体0706班' and FC.fld_name='数学'
17、将所有同学的数学成绩加10分
update FS
set fld_score=FS.fld_score+10
from fld_info FI
inner join fld_score FS on FI.fld_id=FS.fld_stid
inner join fld_class FC on FC.fld_id=FS.fld_cid
where FC.fld_name='数学'
18、删除学号为001同学的数学成绩
delete FS
from fld_info FI
inner join fld_score FS on FI.fld_id=FS.fld_stid
inner join fld_class FC on FC.fld_id=FS.fld_cid
where FC.fld_name='数学' and FI.fld_id='001'
19、删除数字媒体0706班所有同学的数学成绩
delete FS
from fld_info FI
inner join fld_score FS on FI.fld_id=FS.fld_stid
inner join fld_class FC on FC.fld_id=FS.fld_cid
where FC.fld_name='数学' and FI.fld_class='数字媒体0706班'
20、删除数字媒体班级出生日期在1990年后的女生记录
--先delete成绩表
delete FS
from fld_info FI
inner join fld_score FS on FI.fld_id=FS.fld_stid
where FI.fld_birthday>='1990-01-01'
--再删学生信息表
delete fld_info where fld_birthday>='1990-01-01'
热心网友
时间：2022-04-10 03:53
3.
declare @age
set @age=DateDiff(yy,fld_birthday,getDate())
select * from fld_info
4 select fld_id,fld_name,fld_score from fld_score as a
inner join fld_info as b
on a.fld_id=b.fld_stid
where (fld_class='显示数字媒体0706' and fld_name ='数学' and fld_sex ='女')
5.select * from fld_score as a
inner join fld_info as b
on a.fld_id=b.fld_stid
where fld_name like '张%'
6.select count(*) from fld_info where fld_sex ='男'
group by fld_class
select count(*) from fld_info where fld_sex ='女'
group by fld_class
7.select fld_cid avg(fld_score) from fld_score
group by fld_cid
8. select fld_cid avg(fld_score) from fld_score as a
inner join fld_info as b
on a.fld_id=b.fld_stid
groub by fld_class
haveing fld_cid ='数学'
9.select avg(fld_score) from fld_score as a
inner join fld_info as b
on a.fld_id=b.fld_stid
where fld_cid ='数学' desc
groub by fld_class
10.select top10 sum(fld_score) where fld_class='计算数字媒体0706班'
11.select fld_cid avg(fld_score) from fld_score as a
inner join fld_info as b
on a.fld_id=b.fld_stid
groub by fld_class
haveing fld_cid ='数学'
12.insert into fld_info (fld_id,fld_name)
values(3,'历史')
13.insert into fld_class (fld_id,fld_name,fld_sex,fld_birthday,fld_class )
values('4','章小3','女','1987-2-7','计算数字媒体0706班')
14.insert into fld_score (fld_stid,fld_cid,fld_score )
values('3',1,60)
15.update fld_info set fld_sex='男' where fld_name='张三'
update fld_info set fld_birthday='1973-02-07' where fld_name='张三'
16.update fld_score set fld_score=60 where fld_cid='数学的编号'and fld_id='张三的id'
17.updete fld_score set fld_score=(fld_score+10) where fld_cid='数学的编号'
18.delete from fld_score where fld_stid ='001'and fld_cid='数学的编号'
19.delect from fld_score where fld_cid='数学的编号' and fld_stid in(select fld_id from fld_info where fld_class='数字媒体0706班')
20.delect from fld_info where fld_sex='女' and fld_class='数字媒体班级' and (DatePart(yyyy,fld_birthday)>1990)
太麻烦了，我已经尽力了
热心网友
时间：2022-04-10 05:28
3
select fld_id, fld_name,fld_sex, fld_class, DATEDIFF(year,fld_birthday,getData())
from fld_class
4
select fld_info.fld_id, fld_info.fld_name,fld_score
from fld_class, fld_score,fld_info
where fld_info.fld_id=fld_stid and fld_cid=fld_class.fld_id and fld_info.fld_class='数字媒体0706班' and fld_sex='女' and fld_class.fld_name ='数学'
5
select i.fld_id, i.fld_name, i.fld_sex, i.fld_class, c.fld_name,s.fld_score
from fld_class c, fld_score s,fld_info i
where i.fld_id=fld_stid and fld_cid=c.fld_id and i.fld_name like "张%"
6
select fld_class, fld_sex, count(*)
from fld_info
group by fld_class, fld_sex
7
select c.fld_name, avg(fld_score )
from fld_class c, fld_score s
where fld_cid=c.fld_id
group by c.fld_name
8
select i.fld_class, c.fld_name, avg(fld_score )
from fld_class c, fld_score s,fld_info i
where i.fld_id=fld_stid and fld_cid=c.fld_id
group by i.fld_class, c.fld_name
9
select i.fld_class, avg(fld_score ) savg
from fld_class c, fld_score s,fld_info i
where i.fld_id=fld_stid and fld_cid=c.fld_id and c.fld_name='数学'
group by i.fld_class
order by savg desc
10
select top 10 i.fld_id, i.fld_name,i.fld_sex, sum(s.fld_score )
from fld_class c, fld_score s,fld_info i
where i.fld_id=fld_stid and fld_cid=c.fld_id and i.fld_class='数字媒体0706班'
group by i.fld_name
order by sum(s.fld_score ) desc
11
select i.fld_class, c.fld_name, avg(fld_score ) savg
from fld_class c, fld_score s, fld_info i
where i.fld_id=fld_stid and fld_cid=c.fld_id
group by i.fld_class, c.fld_name
12 insert into fld_class values(10,'tiyu')
13
insert into fld_info values('20090312','name','nv','2009-03-01','CS01')
14
insert into fld_score values('20090312',10,60)
15
UPDATA fld_info set fld_sex='' and fld_birthday=''
where fld_class='数字媒体0706班' and fld_name='张三'
16
UPDATA fld_score, set fld_score=60
where fld_stid= (select fld_id from fld_info where fld_class='数字媒体0706班' and fld_name='张三')
17
UPDATA fld_score set fld_score=fld_score+10
where fld_name='shuxue'
18
delete from fld_score
where fld_stid='001' and fld_cno=(select c.fld_name from fld_class where fld_name='shuxue')
19
delete from fld_score
where fld_stid=(select fld_id from fld_info where fld_class='数字媒体0706班')
and fld_cno=(select c.fld_name from fld_class where fld_name='shuxue')
20
delete from fld_info
where fld_class like "数字媒体%' and DATEDIFF(day,fld_birthday,'1990-1-1')<0
热心网友
时间：2022-04-10 07:19
select，insert into，delete，你自己研究呀，别人告诉你你也学不会。自己研究出来的东西才是最好的！
ps：其实这些东西都很简单，很有规律！
热心网友
时间：2022-04-10 09:27
如果你是IT行业的 你应该自己弄懂。。上面说的对，别人告诉你了 你也不会，如果你不是这个行业的，那你就别在去弄这些东西了。浪费时间
热心网友
时间：2022-04-10 11:52
呵呵，好像是某些公司笔试题来的。。找找应该有现成的答案。自己努力。CSDN里面应该有的。。
热心网友
时间：2022-04-10 14:33
看来你自己是不愿意去学习啊，那还搞这个东西干嘛
热心网友
时间：2022-04-10 17:31
的却
热心网友
时间：2022-04-10 20:46
e
