--一個題目涉及到的50個Sql語句
--（下面表的結(jié)構(gòu)以給出,自己在數(shù)據(jù)庫中建立表.并且添加相應(yīng)的數(shù)據(jù),數(shù)據(jù)要全面些. 其中Student表中,SId為學(xué)生的ID）
------------------------------------表結(jié)構(gòu)--------------------------------------
--學(xué)生表tblStudent（編號StuId,、姓名StuName、年齡StuAge,、性別StuSex）
--課程表tblCourse（課程編號CourseId,、課程名稱CourseName、教師編號TeaId）
--成績表tblScore（學(xué)生編號StuId,、課程編號CourseId,、成績Score）
--教師表tblTeacher（教師編號TeaId、姓名TeaName）
---------------------------------------------------------------------------------
--問題：
--1,、查詢“001”課程比“002”課程成績高的所有學(xué)生的學(xué)號,；
Select StuId From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId='001')>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId='002')
--2、查詢平均成績大于60分的同學(xué)的學(xué)號和平均成績,；
Select StuId,Avg(Score) as AvgScore From tblScore
Group By StuId
Having Avg(Score)>60
--3,、查詢所有同學(xué)的學(xué)號,、姓名,、選課數(shù)、總成績,；
Select StuId,StuName,
SelCourses=(Select Count(CourseId) From tblScore t1 Where t1.StuId=s1.StuId),
SumScore=(Select Sum(Score) From tblScore t2 Where t2.StuId=s1.StuId)
From tblStudent s1
--4,、查詢姓“李”的老師的個數(shù)；
Select Count(*) From tblTeacher Where TeaName like '李%'
--5,、查詢沒學(xué)過“葉平”老師課的同學(xué)的學(xué)號,、姓名；
Select StuId,StuName From tblStudent
Where StuId Not In
(
Select StuID From tblScore sc
Inner Join tblCourse cu ON sc.CourseId=cu.CourseId
Inner Join tblTeacher tc ON cu.TeaId=tc.TeaId
Where tc.TeaName='葉平'
)
--6,、查詢學(xué)過“001”并且也學(xué)過編號“002”課程的同學(xué)的學(xué)號,、姓名；
Select StuId,StuName From tblStudent st
Where (Select Count(*) From tblScore s1 Where s1.StuId=st.StuId And s1.CourseId='001')>0
And
(Select Count(*) From tblScore s2 Where s2.StuId=st.StuId And s2.CourseId='002')>0
--7,、查詢學(xué)過“葉平”老師所教的所有課的同學(xué)的學(xué)號,、姓名,；
Select StuId,StuName From tblStudent st Where not exists
(
Select CourseID From tblCourse cu Inner Join tblTeacher tc On cu.TeaID=tc.TeaID
Where tc.TeaName='葉平' And CourseID not in
(Select CourseID From tblScore Where StuID=st.StuID)
)
--8、查詢課程編號“002”的成績比課程編號“001”課程低的所有同學(xué)的學(xué)號,、姓名,；
Select StuId,StuName From tblStudent s1
Where (Select Score From tblScore t1 Where t1.StuId=s1.stuId And t1.CourseId='001')>
(Select Score From tblScore t2 Where t2.StuId=s1.stuId And t2.CourseId='002')
--9、查詢所有課程成績小于60分的同學(xué)的學(xué)號,、姓名,；
Select StuId,StuName From tblStudent st
Where StuId Not IN
(Select StuId From tblScore sc Where st.StuId=sc.StuId And Score>60)
--10、查詢沒有學(xué)全所有課的同學(xué)的學(xué)號,、姓名,；
Select StuId,StuName From tblStudent st
Where (Select Count(*) From tblScore sc Where st.StuId=sc.StuId)<
(Select Count(*) From tblCourse)
--11、查詢至少有一門課與學(xué)號為“1001”的同學(xué)所學(xué)相同的同學(xué)的學(xué)號和姓名,；
------運用連接查詢
Select DistInct st.StuId,StuName From tblStudent st
Inner Join tblScore sc ON st.StuId=sc.StuId
Where sc.CourseId IN (Select CourseId From tblScore Where StuId='1001')
------嵌套子查詢
Select StuId,StuName From tblStudent
Where StuId In
(
Select Distinct StuId From tblScore Where CourseId In (Select CourseId From tblScore Where StuId='1001')
)
--12,、查詢至少學(xué)過學(xué)號為“1001”同學(xué)所有課程的其他同學(xué)學(xué)號和姓名；
Select StuId,StuName From tblStudent
Where StuId In
(
Select Distinct StuId From tblScore Where CourseId Not In (Select CourseId From tblScore Where StuId='1001')
--13,、把“SC”表中“葉平”老師教的課的成績都更改為此課程的平均成績,； (從子查詢中獲取父查詢中的表名，這樣也行,？,？？,？)
--創(chuàng)建測試表
Select * Into Sc From tblScore
go
Update Sc Set Score=(Select Avg(Score) From tblScore s1 Where s1.CourseId=sc.CourseId)
Where CourseId IN
(Select CourseId From tblCourse cs INNER JOIN tblTeacher tc ON cs.TeaID=tc.TeaID WHERE TeaName ='葉平')
--14,、查詢和“1002”號的同學(xué)學(xué)習(xí)的課程完全相同的其他同學(xué)學(xué)號和姓名；
Select StuID,StuName From tblStudent st
Where StuId <> '1002'
And
Not Exists(Select * From tblScore sc Where sc.StuId=st.StuId And CourseId Not In (Select CourseId From tblScore Where StuId='1002'))
And
Not Exists(Select * From tblScore Where StuId='1002' And CourseId Not In (Select CourseId From tblScore sc Where sc.StuId=st.StuId))
--15,、刪除學(xué)習(xí)“葉平”老師課的SC表記錄,；
Delete From tblScore Where CourseId IN
(Select CourseId From tblCourse cs INNER JOIN tblTeacher tc ON cs.TeaId=tc.TeaId Where tc.TeaName='葉平')
--16、向SC表中插入一些記錄,，這些記錄要求符合以下條件：沒有上過編號“003”課程的同學(xué)學(xué)號,、'002'號課的平均成績；
Insert Into tblScore (StuId,CourseId,Score)
Select StuId,'002',(Select Avg(Score) From tblScore Where CourseId='002') From tblScore Where
StuId Not In (Select StuId From tblScore Where CourseId='003')
--17,、按平均成績從高到低顯示所有學(xué)生的“數(shù)據(jù)庫”,、“企業(yè)管理”、“英語”三門的課程成績,，按如下形式顯示： 學(xué)生ID,,數(shù)據(jù)庫,企業(yè)管理,英語,有效課程數(shù),有效平均分
Select StuId
,數(shù)據(jù)庫=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='數(shù)據(jù)庫' And sc.StuID=st.StuId)
,企業(yè)管理=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='企業(yè)管理' And sc.StuID=st.StuId)
,英語=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='英語' And sc.StuID=st.StuId)
,有效課程數(shù)=(Select Count(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='數(shù)據(jù)庫' or CourseName='企業(yè)管理' or CourseName='英語') And sc.StuID=st.StuId)
,有效平均分=(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='數(shù)據(jù)庫' or CourseName='企業(yè)管理' or CourseName='英語') And sc.StuID=st.StuId)
From tblStudent st
Order by 有效平均分 Desc
--18,、查詢各科成績最高和最低的分：以如下形式顯示：課程ID，最高分,，最低分
Select CourseId as 課程ID, 最高分=(Select Max(Score) From tblScore sc Where sc.CourseId=cs.CourseId ),
最低分=(Select Min(Score) From tblScore sc Where sc.CourseId=cs.CourseId )
From tblCourse cs
--19,、按各科平均成績從低到高和及格率的百分?jǐn)?shù)從高到低順序 (百分?jǐn)?shù)后如何格式化為兩位小數(shù)??)
Select 課程ID,平均分,及格率 From
(Select CourseId as 課程ID, 平均分=(Select Avg(Score) From tblScore sc Where sc.CourseId=cs.CourseId ),
及格率=Convert(varchar(10),((Select Count(*) From tblScore sc Where sc.CourseId=cs.CourseId And sc.Score>=60)*10000/(Select Count(*) From tblScore sc Where sc.CourseId=cs.CourseId))/100)+'%'
From tblScore cs) as tmp
Group by 課程ID,平均分,及格率
Order by 平均分, Convert(float,substring(及格率,1,len(及格率)-1)) Desc
--20、查詢?nèi)缦抡n程平均成績和及格率的百分?jǐn)?shù)(用"1行"顯示): 企業(yè)管理（001）,，馬克思（002）,，OO&UML （003）,，數(shù)據(jù)庫（004）
Select 課程ID=sc.CourseId,課程名稱=cs.CourseName,平均成績=Avg(Score)
,及格率 =Convert(varchar(10),((Select Count(Score) From tblScore Where CourseId=sc.CourseId And Score>=60)*10000/Count(Score))/100.0)+'%'
From tblScore sc
Inner Join tblCourse cs ON sc.CourseId=cs.CourseId
Where sc.CourseId like '00[1234]'
Group By sc.CourseId,cs.CourseName
--21、查詢不同老師所教不同課程平均分從高到低顯示
Select 課程ID=CourseId,課程名稱=CourseName,授課教師=TeaName,平均成績=(Select Avg(Score) From tblScore Where CourseId=cs.CourseId)
From tblCourse cs
Inner Join tblTeacher tc ON cs.TeaId=tc.TeaId
Order by 平均成績 Desc
--22,、查詢?nèi)缦抡n程成績第 3 名到第 6 名的學(xué)生成績單：企業(yè)管理（001）,，馬克思（002），UML （003）,，數(shù)據(jù)庫（004） 格式：[學(xué)生ID],[學(xué)生姓名],企業(yè)管理,馬克思,UML,數(shù)據(jù)庫,平均成績
Select * From
(
Select Top 6 學(xué)生ID=StuId,學(xué)生姓名=StuName
,企業(yè)管理=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='企業(yè)管理' And sc.StuID=st.StuId)
,馬克思=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='馬克思' And sc.StuID=st.StuId)
,UML=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='UML' And sc.StuID=st.StuId)
,數(shù)據(jù)庫=(Select Score From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where CourseName='數(shù)據(jù)庫' And sc.StuID=st.StuId)
,平均成績=(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='數(shù)據(jù)庫' or CourseName='企業(yè)管理' or CourseName='UML'or CourseName='馬克思') And sc.StuID=st.StuId)
,排名=Row_Number() Over(Order by(Select Avg(Score) From tblScore sc Inner Join tblCourse cs On sc.CourseId=cs.CourseId Where (CourseName='數(shù)據(jù)庫' or CourseName='企業(yè)管理' or CourseName='UML'or CourseName='馬克思') And sc.StuID=st.StuId) DESC)
From tblStudent st
Order by 排名
) as tmp
Where 排名 between 3 And 6
--23,、統(tǒng)計列印各科成績,各分?jǐn)?shù)段人數(shù):課程ID,課程名稱,[100-85],[85-70],[70-60],[ <60]
Select 課程ID=CourseId, 課程名稱=CourseName
,[100-85]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 85 And 100)
,[85-70]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 70 And 84)
,[70-60]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score between 60 And 69)
,[<60]=(Select Count(*) From tblScore sc Where CourseId=cs.CourseId And Score <60)
From tblCourse cs
--24、查詢學(xué)生平均成績及其名次
Select 學(xué)號=st.StuId, 姓名=StuName,平均成績=sc.AvgScore,名次=(Dense_Rank() Over(Order by sc.AvgScore Desc)) From tblStudent st
Inner Join (Select StuId,Avg(Score) as AvgScore From tblScore Group by StuId) as sc On sc.StuId=st.StuId
Order by 學(xué)號
--25,、查詢各科成績前三名的記錄:(不考慮成績并列情況)
Select 學(xué)號=StuId,課程號=CourseId,分?jǐn)?shù)=Score
From
(Select Row_Number() Over(order by CourseId,Score Desc) as i,* From tblScore) as tmp --得到一個臨時的排名表,其中i表示編號
Where i In
(
Select Top 3 i From (Select Row_Number() Over(order by CourseId,Score Desc) as i,* From tblScore) as t1 Where t1.CourseId=tmp.CourseId
)
--26,、查詢每門課程被選修的學(xué)生數(shù)
Select 課程ID=CourseId,選修人數(shù)=(Select Count(*) From (Select Distinct StuId From tblScore Where CourseId=cs.CourseId) as tmp)
From tblCourse cs
--27、查詢出只選修了一門課程的全部學(xué)生的學(xué)號和姓名
Select 學(xué)號=StuId,姓名=StuName
From tblStudent st
Where (Select Count(*) From (Select Distinct CourseId From tblScore Where StuId=st.StuId) as tmp)=1
--28,、查詢男生,、女生人數(shù)
Select 男生人數(shù)=(select Count(*) From tblStudent Where StuSex='男'),
女生人數(shù)=(select Count(*) From tblStudent Where StuSex='女')
--29、查詢姓“張”的學(xué)生名單
Select * From tblStudent Where StuName like '張%'
--30,、查詢同名同性學(xué)生名單,，并統(tǒng)計同名人數(shù)
Select Distinct 學(xué)生姓名=StuName,同名人數(shù)=(Select Count(*) From tblStudent s2 Where s2.StuName=st.StuName) From tblStudent st
Where (Select Count(*) From tblStudent s2 Where s2.StuName=st.StuName)>=2
--31、1981年出生的學(xué)生名單(注：Student表中Sage列的類型是datetime)
Select * From tblStudent Where Year(Sage)=1981
--32,、查詢每門課程的平均成績,，結(jié)果按平均成績升序排列，平均成績相同時,，按課程號降序排列
Select 課程ID=CourseId,課程名稱=CourseName,平均成績=(Select Avg(Score) From tblScore Where CourseId=cs.CourseId)
From tblCourse cs
Order by 平均成績,CourseId Desc
--33,、查詢平均成績大于85的所有學(xué)生的學(xué)號、姓名和平均成績
Select 學(xué)號=StuId,姓名=StuName,平均成績=(Select Avg(Score) From tblScore Where StuId=st.StuId) From tblStudent st
Where (Select Avg(Score) From tblScore Where StuId=st.StuId)>85
--34,、查詢課程名稱為“數(shù)據(jù)庫”,，且分?jǐn)?shù)低于60的學(xué)生姓名和分?jǐn)?shù)
Select 姓名=StuName,分?jǐn)?shù)=Score From tblScore sc
Inner Join tblStudent st On sc.StuId=st.StuId
Inner Join tblCourse cs On sc.CourseId=cs.CourseId
Where CourseName='數(shù)據(jù)庫' And Score<60
--35、查詢所有學(xué)生的選課情況,；
Select 學(xué)號=StuId,選課數(shù)=(Select Count(*) From (Select Distinct CourseId From tblScore Where StuId=st.StuId) as tmp)
From tblStudent st
Select distinct 姓名=StuName,選修課程=CourseName From tblScore sc
Inner Join tblStudent st On sc.StuId=st.StuId
Inner Join tblCourse cs On sc.CourseId=cs.CourseId
--36,、查詢?nèi)魏我婚T課程成績在70分以上的姓名、課程名稱和分?jǐn)?shù),；
Select 姓名=StuName,課程名稱=CourseName,分?jǐn)?shù)=Score From tblScore sc
Inner Join tblStudent st On sc.StuId=st.StuId
Inner Join tblCourse cs On sc.CourseId=cs.CourseId
Where Score>=70
--37,、查詢不及格的課程,，并按課程號從大到小排列
Select * From tblScore Where Score<60 order by CourseId Desc
--38,、查詢課程編號為003且課程成績在80分以上的學(xué)生的學(xué)號和姓名；
Select StuId,StuName From tblStudent
Where StuId in
(Select StuId From tblScore Where CourseId='003' And Score>=80)
--39,、求選了課程的學(xué)生人數(shù)
Select 選了課程的學(xué)生人數(shù)=Count(*) From tblStudent st Where StuId IN (Select StuID From tblScore)
--40,、查詢選修“葉平”老師所授課程的學(xué)生中，成績最高的學(xué)生姓名及其成績
Select CourseId,CourseName
,該科最高學(xué)生=(Select StuName From tblStudent Where StuId in (Select Top 1 StuID From tblScore Where CourseId=cs.CourseId Order by Score Desc))
,成績=(Select Top 1 Score From tblScore Where CourseId=cs.CourseId Order by Score Desc)
From tblCourse cs Inner Join tblTeacher tc ON cs.TeaId=tc.TeaId
Where TeaName='葉平'
--41,、查詢各個課程及相應(yīng)的選修人數(shù)
Select 課程ID=CourseId,選修人數(shù)=(Select Count(*) From (Select Distinct StuId From tblScore Where CourseId=cs.CourseId) as tmp)
From tblCourse cs
--42,、查詢不同課程成績相同的學(xué)生的學(xué)號,、課程號、學(xué)生成績
Select 學(xué)號=StuId, 課程號=CourseId, 成績=Score From tblScore sc
Where Exists (Select * From tblScore Where Score=sc.Score And StuId=sc.StuId And CourseId <>sc.CourseId)
Order by 學(xué)號,成績
--43,、查詢每門功成績最好的前兩名
Select 課程號=CourseId,
第1名=(Select Top 1 StuId From tblScore Where CourseId=cs.CourseId Order by Score DESC),
第2名=(Select Top 1 StuID From (Select Top 2 StuId,Score From tblScore Where CourseId=cs.CourseId Order by Score DESC) as tmp Order by Score)
From tblCourse cs
--44,、統(tǒng)計每門課程的學(xué)生選修人數(shù)（超過10人的課程才統(tǒng)計）。要求輸出課程號和選修人數(shù),，查詢結(jié)果按人數(shù)降序排列,，若人數(shù)相同，按課程號升序排列
Select 課程ID=CourseId,選修人數(shù)=(Select Count(*) From (Select Distinct StuId From tblScore Where CourseId=cs.CourseId) as tmp)
From tblCourse cs
Where (Select Count(*) From (Select Distinct StuId From tblScore Where CourseId=cs.CourseId) as tmp)>=10
Order by 選修人數(shù) DESC, 課程ID
--45,、檢索至少選修兩門課程的學(xué)生學(xué)號
Select StuId from tblScore Group by Stuid having Count(*)>=2 --沒有重復(fù)課程數(shù)據(jù)時可用此方法
--有重復(fù)課程時用此方法(如補考)
Select StuId from tblStudent st Where
(Select Count(*) From (Select Distinct CourseId From tblScore Where StuId=st.StuId) as tmp)>=2
--46,、查詢?nèi)繉W(xué)生都選修的課程的課程號和課程名
Select CourseId,CourseName From tblCourse cs
Where Not Exists
(
Select * From tblStudent Where StuId Not In --沒學(xué)過本課程的學(xué)生是否存在
(Select StuId From tblScore Where CourseId=cs.CourseId)
)
--47、查詢沒學(xué)過“葉平”老師講授的任一門課程的學(xué)生姓名
Select StuId,StuName From tblStudent
Where StuId Not In
(
Select StuID From tblScore sc
Inner Join tblCourse cu ON sc.CourseId=cu.CourseId
Inner Join tblTeacher tc ON cu.TeaId=tc.TeaId
Where tc.TeaName='葉平'
)
--48,、查詢兩門以上不及格課程的同學(xué)的學(xué)號及其平均成績
Select StuID as 學(xué)號,Avg(Score) as 平均成績 From tblScore sc
Where (Select Count(*) From tblScore s1 Where s1.StuId=sc.StuId And Score<60)>=2
Group By StuId
--49,、檢索“004”課程分?jǐn)?shù)小于60，按分?jǐn)?shù)降序排列的同學(xué)學(xué)號 (ok)
Select StuID,Score From tblScore Where CourseId='004' And Score<60 Order by Score Desc
--50,、刪除“002”同學(xué)的“001”課程的成績
Delete From SC Where StuId='1002' And CourseId='001'
----------------------SC為刪除數(shù)據(jù)臨時表
Select * INTO SC From tblScore
Select * from sc Where stuId='1018'
Insert Sc(Stuid,courseId,Score) Select StuID,'009',74 From tblStudent
/********************************* 建庫建表建約束,，插入測試數(shù)據(jù) ******************************************/
Use master
go
if db_id('MySchool') is not null
Drop Database MySchool
Create Database MySchool
go
Use MySchool
go
create table tblStudent
(
StuId varchar(5) primary key,
StuName nvarchar(10) not null,
StuAge int,
StuSex nchar(1) not null
)
create table tblTeacher
(
TeaId varchar(3) primary key,
TeaName varchar(10) not null
)
create table tblCourse
(
CourseId varchar(3) primary key,
CourseName nvarchar(20) not null,
TeaId varchar(3) not null foreign key references tblTeacher(teaId)
)
create table tblScore
(
StuId varchar(5) not null foreign key references tblStudent(stuId),
CourseId varchar(3) not null foreign key references tblCourse(CourseId),
Score float
)
----------------------------------表結(jié)構(gòu)----------------------------------------------------
--學(xué)生表tblStudent（編號StuId、姓名Stuname,、年齡Stuage,、性別Stusex）
--課程表tblCourse（課程編號CourseId、課程名稱CourseName,、教師編號TeaId）
--成績表tblScore（學(xué)生編號StuId,、課程編號CourseId、成績Score）
--教師表tblTeacher（教師編號TeaId,、姓名TeaName）
--------------------------------插入數(shù)據(jù)-------------------------------------------------
insert into tblStudent
select '1000','張無忌',18,'男' union
select '1001','周芷若',19,'女' union
select '1002','楊過',19,'男' union
select '1003','趙敏',18,'女' union
select '1004','小龍女',17,'女' union
select '1005','張三豐',18,'男' union
select '1006','令狐沖',19,'男' union
select '1007','任盈盈',20,'女' union
select '1008','岳靈珊',19,'女' union
select '1009','韋小寶',18,'男' union
select '1010','康敏',17,'女' union
select '1011','蕭峰',19,'男' union
select '1012','黃蓉',18,'女' union
select '1013','郭靖',19,'男' union
select '1014','周伯通',19,'男' union
select '1015','瑛姑',20,'女' union
select '1016','李秋水',21,'女' union
select '1017','黃藥師',18,'男' union
select '1018','李莫愁',18,'女' union
select '1019','馮默風(fēng)',17,'男' union
select '1020','王重陽',17,'男' union
select '1021','郭襄',18,'女'
go
insert into tblTeacher
select '001','姚明' union
select '002','葉平' union
select '003','葉開' union
select '004','孟星魂' union
select '005','獨孤求敗' union
select '006','裘千仞' union
select '007','裘千尺' union
select '008','趙志敬' union
select '009','阿紫' union
select '010','郭芙蓉' union
select '011','佟湘玉' union
select '012','白展堂' union
select '013','呂輕侯' union
select '014','李大嘴' union
select '015','花無缺' union
select '016','金不換' union
select '017','喬丹'
go
insert into tblCourse
select '001','企業(yè)管理','002' union
select '002','馬克思','008' union
select '003','UML','006' union
select '004','數(shù)據(jù)庫','007' union
select '005','邏輯電路','006' union
select '006','英語','003' union
select '007','電子電路','005' union
select '008','毛澤東思想概論','004' union
select '009','西方哲學(xué)史','012' union
select '010','線性代數(shù)','017' union
select '011','計算機基礎(chǔ)','013' union
select '012','AUTO CAD制圖','015' union
select '013','平面設(shè)計','011' union
select '014','Flash動漫','001' union
select '015','Java開發(fā)','009' union
select '016','C#基礎(chǔ)','002' union
select '017','Oracl數(shù)據(jù)庫原理','010'
go
insert into tblScore
select '1001','003',90 union
select '1001','002',87 union
select '1001','001',96 union
select '1001','010',85 union
select '1002','003',70 union
select '1002','002',87 union
select '1002','001',42 union
select '1002','010',65 union
select '1003','006',78 union
select '1003','003',70 union
select '1003','005',70 union
select '1003','001',32 union
select '1003','010',85 union
select '1003','011',21 union
select '1004','007',90 union
select '1004','002',87 union
select '1005','001',23 union
select '1006','015',85 union
select '1006','006',46 union
select '1006','003',59 union
select '1006','004',70 union
select '1006','001',99 union
select '1007','011',85 union
select '1007','006',84 union
select '1007','003',72 union
select '1007','002',87 union
select '1008','001',94 union
select '1008','012',85 union
select '1008','006',32 union
select '1009','003',90 union
select '1009','002',82 union
select '1009','001',96 union
select '1009','010',82 union
select '1009','008',92 union
select '1010','003',90 union
select '1010','002',87 union
select '1010','001',96 union
select '1011','009',24 union
select '1011','009',25 union
select '1012','003',30 union
select '1013','002',37 union
select '1013','001',16 union
select '1013','007',55 union
select '1013','006',42 union
select '1013','012',34 union
select '1000','004',16 union
select '1002','004',55 union
select '1004','004',42 union
select '1008','004',34 union
select '1013','016',86 union
select '1013','016',44 union
select '1000','014',75 union
select '1002','016',100 union
select '1004','001',83 union
select '1008','013',97
go
