教學思路SQL之入門習題《學員成績》 三、多表複雜子查詢

1、查詢出所有大連地區的學生的成績。
2、查詢從來沒有參加任何考試的學生的資訊。
3、查詢有考試成績紀錄的學生的資訊.
4、將所有大連地區學生的課程編號為3的課程成績加10分。
5、刪除所有C#課的成績。
6、查詢課程編號為3的課程的平均成績，顯示有此門課程成績的學生編號，成績及距平均分之間的差值。
7、查詢參加了所有考試的學生的姓名。
8、查詢有成績紀錄的學生人數。
9、查詢有2門以上成績的學生的姓名和連絡方式
10、查詢哪些學生有c#考試成績，顯示這些學生的姓名
11.查詢出科目中教師的姓名，同時增加編號。
12.隨機顯示學生資訊表中的兩條資料：使用newid（）函數
13.查詢出聯絡電話為null的學生資訊，聯絡電話用‘無’代替。
14.將查詢出的地區為大連的都顯示為null.
15.查詢科目平均成績超過70分的教師姓名。

新知識：

對空值進行顯示操作：

• isnull（A，‘aa’）函數可以將空值轉換為有效值。將列名A的值null改為‘aa’顯示
• nullif（A，‘aa’）函數可以根據指定的條件來產生空值。將列名A的值aa改為null顯示

newid函數：

•     
  使用newid（）函數會產生隨機行 

答案
1、查詢出所有大連地區的學生的成績。
    select s.number,c.score  from chengji as c ,student as s where c.number=s.number and s.diqu='大連'
    select * from chengji where number in( select number from student where diqu='大連')

2、查詢從來沒有參加任何考試的學生的資訊。
select * from student where number not in (select number from chengji)
3、查詢有考試成績紀錄的學生的資訊.
select * from student where number  in (select number from chengji)
4、將所有大連地區學生的課程編號為3的課程成績加10分。
update chengji set score=score+10 where scorenumber=3
5、刪除所有C#課的成績。
delete chengji where scorenumber=(select scorenumber from score where kemu='C#')
6、查詢課程編號為3的課程的平均成績，顯示有此門課程成績的學生編號，成績及距平均分之間的差值。
select number,score-(select avg(score) from chengji where scorenumber=3) from chengji where scorenumber=3
7、查詢參加了所有考試的學生的姓名。
select name from student where number in( select max(number) from chengji group by number)
8、查詢有成績紀錄的學生人數。
  selectnumber ,name from student where number in
( select max(number) from chengji   group by number having count(scorenumber)=(select count(*) from score) )
9、查詢有2門以上成績的學生的姓名和連絡方式。
select name,lianxidianhua from student where number in( select number from chengji group by number having count(scorenumber)>2)
10、查詢哪些學生有c#考試成績，顯示這些學生的姓名。
select name from student where number in( select number from chengji where scorenumber =(select scorenumber from score where kemu='c#'))
給結果集添加編號
11.查詢出科目中教師的編號，同時增加序號。
    select 序號=(select count(*) from score as a where a.scorenumber<=b.scorenumber ),teacherid  from score as b
    select 序號=identity(1,1),teachername into #newtable from score
12.隨機顯示學生資訊表中的兩條資料：使用newid（）函數
select top 2 * from student  order by newid()
13.查詢出聯絡電話為null的學生資訊，聯絡電話用‘無’代替。
select name,isnull(convert(char(20),lianxidianhua),'無') from student  
14.將查詢出的地區為大連的都顯示為null
select name,nullif(diqu,'大連') from student
15.查詢科目平均成績超過70分的教師姓名。
select teachername from teacherinfo where teacherid in 
(select teacherid from score where scorenumber in
(select scorenumber from chengji group by scorenumber having avg(score)>=70))