⑴ sql語句得出成績排名為10到20名的學生
select 學生姓名 from 表 order by 學生成績 desc
排序，指定分頁大小是10，然後跳到第二頁。
也就是相當於翻頁嘛。
⑵ 在 SQL server中怎樣設置成績表中成績欄位的檢查約束值為0到100之間
CHECK(成績>=0 AND 成績<=100)
⑶ SQL 約束 怎樣寫能讓分數為空或必須是0~100之間
alter table tablename
add constraint CK_score CHECK(score BETWEEN 0 AND 100 OR score is NULL)
tablename 表名
score 分數
⑷ 在sql語言中，增加約束條件使stud_grade表中的grade在0到100之間
alter table stud_grade add constrant 約束名 check(grade between 1 and 100)
⑸ Sql表達式成績分數約束表達式，空或必須是0~100之間怎麼寫
check(psd like '[NULL|0-100]')
⑹ 1查詢成績表的總分數，平均分，最低分和最高分。用sql語句怎麼寫
---1. 計算每個人的總成績並排名（要求顯示欄位：姓名，總成績）
select name,sum(cast(score as bigint)) as allscore from stuscore group by name order by allscore desc
---2. 計算每個人的總成績並排名（要求顯示欄位: 學號，姓名，總成績）
select stuid,name,sum(cast(score as bigint)) as allscore from stuscore group by stuid,name order by allscore desc
---3. 計算每個人單科的最高成績(要求顯示欄位: 學號，姓名，課程，最高成績)
SELECT t1.stuid,t1.name,t1.subject,t1.score from stuscore t1,(SELECT stuid,max(score) as maxscore from stuscore group by stuid) t2 where t1.stuid=t2.stuid and t1.score=t2.maxscore
---4. 計算每個人的平均成績（要求顯示欄位: 學號，姓名，平均成績）
select distinct t1.stuid,t1.name,t2.avgscore from stuscore t1,(select stuid,avg(cast(score as bigint)) as avgscore from stuscore group by stuid) t2 where t1.stuid=t2.stuid
---5. 列出各門課程成績最好的學生(要求顯示欄位: 學號，姓名,科目，成績)
select t1.stuid,t1.name,t1.subject,t2.maxscore from stuscore t1,(select subject,max(score) as maxscore from stuscore group by subject) t2 where t1.subject=t2.subject and t1.score=t2.maxscore
---6. 列出各門課程成績最好的兩位學生(要求顯示欄位: 學號，姓名,科目，成績)
select distinct t1.* from stuscore t1 where t1.stuid in(select top 2 stuscore.stuid from stuscore where subject = t1.subject order by score desc)order by t1.subject
---7. 統計報表(要求顯示欄位: 學號，姓名，各科成績，總分，平均成績)
select stuid as 學號,name as 姓名,sum(case when subject='語文' then score else 0 end) as 語文,sum(case when subject='數學' then score else 0 end) as 數學,sum(case when subject='英語' then score else 0 end) as 英語,sum(cast(score as bigint)) as 總分,(sum(cast(score as bigint))/count(*)) as 平均分 from stuscore group by stuid,name order by 總分 desc
---8. 列出各門課程的平均成績（要求顯示欄位：課程，平均成績）
select subject,avg(cast(score as bigint)) as avgscore from stuscore group by subject
---9. 列出數學成績的排名（要求顯示欄位：學號，姓名，成績，排名）
select * from stuscore where subject ='數學' order by score desc
---10. 列出數學成績在2-3名的學生（要求顯示欄位：學號，姓名,科目，成績）
select t3.* from(select top 2 t2.* from (select top 3 name,subject,score,stuid from stuscore where subject='數學' order by score desc) t2 order by t2.score) t3 order by t3.score desc
---11. 求出李四的數學成績的排名
declare @tmp table(pm int,name varchar(50),score int,stuid int)
insert into @tmp select null,name,score,stuid from stuscore where subject='數學' order by score desc
declare @id int
set @id=0;
update @tmp set @id=@id+1,pm=@id
select * from @tmp where name='李四'
---12. 統計各科目及格人數
select subject,
(select count(*) from stuscore where score<60 and subject=t1.subject) as 不及格,
(select count(*) from stuscore where score between 60 and 80 and subject=t1.subject) as 良,
(select count(*) from stuscore where score >80 and subject=t1.subject) as 優
from stuscore t1 group by subject
---13．統計如下：數學:張三(50分),李四(90分),王五(90分),趙六(76分)
declare @s varchar(1000)
set @s=''
select @s =@s+','+name+'('+convert(varchar(10),score)+'分)' from stuscore where subject='數學'
set @s=stuff(@s,1,1,'')
print '數學:'+@s
⑺ 用sql語句查將成績小於60分的計算機專業學生的成績設置為0
1、首先在sql軟體附加有成績表的資料庫，然後右鍵新建查詢。
⑻ sql語句列出成績在80分到100分之間的學生名單
select * from 學生表 where 學生表主鍵id in (select 學生表學生id from 成績表 where 成績欄位 between 80 and 100)
或
select * from 學生表 where 學生表主鍵id in (select 學生表學生id from 成績表 where 成績欄位>=80 and 成績欄位<=100)
⑼ SQL查詢學生成績
--代碼如下--
SELECTa.snameAS'姓名'
MAX(CASEb.sidWHEN'0001'THENb.scoreELSE0END)AS'語文'
MAX(CASEb.sidWHEN'0002'THENb.scoreELSE0END)AS'數學'
MAX(CASEb.sidWHEN'0003'THENb.scoreELSE0END)AS'英語'
FROMstudenta,scoresbONa.sid=b.sid
GROUPBYb.sid
⑽ 在 SQL server中怎樣設置成績表中成績欄位的檢查約束值為0到100之間，用邏輯表達式來表示
CHECK(成績>=0 AND 成績<=100)