實驗2-實驗報告

1、數(shù)據(jù)庫實驗實驗二 sql server2008數(shù)據(jù)查詢【實驗?zāi)康摹空莆誷ql server2008數(shù)據(jù)查詢語言的使用，能獨立按要求對數(shù)據(jù)庫進行指定的查詢操作?！緦嶒炓蟆?、每完成一個任務(wù)，截取全屏幕快照13張作為中間步驟和結(jié)果的貼圖，粘貼在最后的實驗報告中。2、自己編一些新的數(shù)據(jù)插入到數(shù)據(jù)表中，豐富每個人的數(shù)據(jù)的同時還要保證每個查詢的結(jié)果集不為空，或結(jié)果不為0?！緦嶒炄蝿?wù)及實驗過程】（內(nèi)附，每題代碼）1、 將自己的信息插入到 stud_info 學(xué)生信息表中。學(xué)號姓名出生性別地址電話郵編成績2011211012孫嘉璐1993女天津市河西區(qū)022-88296868123456624代碼：/*4

2、、查詢“計算機工程系”、“計算機網(wǎng)絡(luò)技術(shù)專業(yè)”、班全體學(xué)生的基本信息程序*/use sunjialu3_datagoinsert into stud_info values ('2011211012','孫嘉璐','1993-01-12 00:00:00.000','女','天津市河西區(qū)','#39;,'100876','624')2、 顯示 stud_grade 學(xué)生成績表中成績?yōu)?85，86 或 88 的記錄。程序：use sunjialu3_d

3、atagoselect * from stud_grade where grade=85 or grade=86 or grade=883、 顯示 stud_grade 學(xué)生成績表中的最高分的學(xué)生學(xué)號和課程號。方法：使用子查詢功能，和計算函數(shù)max程序: use sunjialu3_datagoselect stud_id course_id from stud_gradewhere grade in(select max(grade)from stud_grade )4、 查詢“計算機工程系”、“計算機網(wǎng)絡(luò)技術(shù)專業(yè)”、02 班全體學(xué)生的基本信息方法：先查找出“計算機網(wǎng)絡(luò)技術(shù)專業(yè)”所對應(yīng)的的編

4、號。在查找與此編號比配的學(xué)號，使用了模糊匹配 like。代碼：/*4、查詢“計算機工程系”、“計算機網(wǎng)絡(luò)技術(shù)專業(yè)”、班全體學(xué)生的基本信息程序*/use sunjialu3_datagoselect speccode from specialty_codewhere specname='計算機網(wǎng)絡(luò)技術(shù)'use sunjialu3_datagoselect * from stud_infowhere stud_id like '04010202_'5、 查詢“計算機工程系”、“計算機網(wǎng)絡(luò)技術(shù)專業(yè)”、02 班的學(xué)生人數(shù)。方法：使用了計算函數(shù)count（*）計算元組數(shù)量

5、和模糊匹配like 。程序：use sunjialu3_datagoselect count(*) from stud_infowhere stud_id like '04010202_'6、 顯示出與學(xué)號為“0401040112”的學(xué)生同年出生的所有學(xué)生的學(xué)號、姓名和出生日期。 提示：可以使用函數(shù)year()。方法：使用了year函數(shù)，同時使用子查詢的方法得到與學(xué)號0401040112的出生年份，進行比較，得到出生年份相等的同學(xué)。代碼：/*顯示出與學(xué)號為“0401040112”的學(xué)生同年出生的所有學(xué)生的學(xué)號、姓名和出生日期*/use sunjialu3_datagoselec

6、t stud_id as 學(xué)號, name as 姓名 ,birthday as 出生日期 from stud_infowhere year (birthday)=(select year(birthday) from stud_infowhere stud_id='0401040112')7、 生成績表中，顯示存在有 85 分以上成績的課程號。方法：使用了where的比較運算符。代碼：use sunjialu3_datagoselect course_id from stud_gradewhere grade>858、 在學(xué)生成績表中，顯示所有成績?yōu)榭盏挠涗浶畔ⅰ７椒ǎ?/p>

7、所有成績?yōu)榭盏谋硎痉ㄊ?，is null代碼：/*在學(xué)生成績表中，顯示所有成績?yōu)榭盏挠涗浶畔ⅰ?/use sunjialu3_datagoselect * from stud_gradewhere grade is null9、 查詢所有電話號碼的開頭為010的同學(xué)的姓名、電話、地址和郵政編碼。方法:在stud_info中采用模糊匹配。代碼：use sunjialu3_datagoselect name ,telcode, address,zipcode from stud_infowhere telcode like '010%'10、 查詢所有女教師的平均工資。方法：采用計算

8、函數(shù)avg，在teacher_info表中查詢，條件是性別為“女“。代碼：use sunjialu3_datagoselect avg(salary) from teacher_infowhere gender='女'11、 查詢課程號為'0401010103'的課程的最高分、最低分和平均分。方法：采用計算函數(shù)avg,max,min，條件是課程號為0401010103。代碼：use sunjialu3_datagoselect avg( grade)as 平均成績 , max(grade) as 最高成績,min(grade) as 最低成績 from stud

9、_gradewhere course_id='0401010103'12、 查詢選了課且至少有一門課沒有成績的學(xué)生人數(shù)。方法：代碼：/*12、查詢選了課且至少有一門課沒有成績的學(xué)生人數(shù)*/use sunjialu3_datagoselect count ( distinct stud_id) as 學(xué)生人數(shù) from stud_gradewhere grade is null and course_id is not null13、 按職稱統(tǒng)計各個教研室的教師人數(shù)。方法：使用substring函數(shù)選出教研室號，根據(jù)教研室號和教師職稱進行分組。代碼：/*按職稱統(tǒng)計各個教研室的教師

10、人數(shù)*/use sunjialu3_datagoselect substring(teacher_id,3,2) as 教研室, tech_title as 職稱 ,count (*) as 教研人數(shù) from teacher_infogroup by substring(teacher_id,3,2),tech_title 14、 將計算機工程系職稱為講師的教師按年齡由低到高排列。方法：采用子查詢的方法，先選出計算機工程系的系號，在選出對應(yīng)系號的課程號，從而選出教授計算機工程系的講師們，在按年齡由低到高排序，使用order by 的asc語句。代碼：use sunjialu3_datagos

11、elect name as 姓名, age as 年齡 from teacher_info where tech_title='講師' and course_id in(select course_id from teach_schedulewhere deptcode in(select deptcode from dept_codewhere deptname='計算機工程系')order by age asc15、 將所有選了課的學(xué)生的學(xué)號按平均成績由高到低排列。代碼：use sunjialu3_datagoselect stud_id as 學(xué)號, av

12、g(grade) as 平均成績 from stud_gradewhere stud_id in(select stud_id from stud_gradewhere course_id in(select course_id from stud_gradewhere course_id is not null ) group by stud_idorder by avg(grade) desc17、將 stud_grade 學(xué)生成績表中課程號為“0401010106”的成績加 5。代碼：use sunjialu3_datagoupdate stud_grade set grade=grad

13、e+5where course_id='0401010106'18、刪除 stud_info 學(xué)生信息表中姓名為“張源”的學(xué)生記錄。方法：使用delete函數(shù)，又條件刪除。代碼：use sunjialu3_datagodelete from stud_info where name='張源'19、求選修了“數(shù)據(jù)庫原理”的學(xué)生的學(xué)號及姓名。方法：子查詢方法。代碼：use sunjialu3_datagoselect stud_id as 學(xué)號, name as 姓名 from stud_infowhere stud_id in(select stud_id fro

14、m stud_gradewhere course_id in(select course_id from teach_schedulewhere room_id in (select room_id from classroom_infowhere room_name='數(shù)據(jù)庫')20、查詢所有沒選修“0401010103”課程的學(xué)生學(xué)號及姓名。代碼：use sunjialu3_datagoselect stud_id as 學(xué)號, name as 姓名 from stud_infowhere stud_id in(select stud_id from stud_gradew

15、here course_id !='0401010103')21、查詢與王剛在同一個系學(xué)習(xí)的學(xué)生的姓名和所在的系。方法：查找王剛計算機工程系的系號。代碼：/*查詢與王剛在同一個系學(xué)習(xí)的學(xué)生的姓名和所在的系*/use sunjialu3_datagoselect deptcode as 系號 from dept_codewhere deptname='計算機工程系' 方法：將dept_code， teach_schedule， stud_grade三個表連接起來，查找，系號為01的學(xué)生姓名和所在系。代碼：/*查詢與王剛在同一個系學(xué)習(xí)的學(xué)生的姓名和所在的系*/use

16、 goselect deptname as 所在系, name as 姓名 from dept_code join teach_schedule on teach_schedule.deptcode=dept_code.deptcodejoin stud_grade on stud_grade.course_id=teach_schedule.course_idwhere dept_code.deptcode='01'22、查詢成績?yōu)樾∮?0分的學(xué)生的學(xué)號、姓名。方法：將stud_info，stud_grade表連接起來，找尋條件為成績小于60分。代碼：/*查詢成績?yōu)樾∮诜值膶W(xué)

17、生的學(xué)號、姓名*/use sunjialu3_datagoselect stud_info.stud_id as 學(xué)號, stud_ as 姓名 from stud_grade join stud_info on stud_grade.stud_id=stud_info.stud_idwhere stud_grade.grade<6023、查詢修了“0401010103”課程且成績高于此課程的平均成績的學(xué)生的學(xué)號和成績。方法：先從grade_id表中將course_id='0401010103'的平均成績選擇出來，在選擇course_id='0

18、401010103'且成績>平均成績的學(xué)號和成績。代碼：/*查詢修了“0401010103”課程且成績高于此課程的平均成績的學(xué)生的學(xué)號和成績*/use sunjialu3_datagoselect stud_id as 學(xué)號,name as 姓名 from stud_gradewhere course_id='0401010103' and grade > (select avg(grade) from stud_gradewhere course_id='0401010103')24、顯示選了課程號為“0401010105”的上課教師姓名。

19、方法：將teacher_info，teach_schedule兩個表連接起來，選擇課程號是0401010105的上課教師姓名即可。代碼：/*顯示選了課程號為“0401010105”的上課教師姓名*/use sunjialu3_datagoselect teacher_ as 姓名 from teach_schedule join teacher_infoon teach_schedule.teacher_id= teacher_info.teacher_idwhere teach_schedule.course_id='0401010105'25、統(tǒng)計不及格人

20、數(shù)在 3 人以上的課程的數(shù)量。方法：子查詢方法，選出成績<60的課程號，再將其根據(jù)課程號分組，選出其中人數(shù)>3的課程號，用計算函數(shù)count輸出此類課程的數(shù)量即可。代碼：/*統(tǒng)計不及格人數(shù)在3 人以上的課程的數(shù)量*/use sunjialu3_datagoselect count(course_id)as 課程數(shù)量 from lesson_infowhere course_id in(select course_id from stud_gradewhere grade<60group by course_idhaving count(stud_id)>3)26、顯示“

21、計算機工程系”老師所教課程的成績表。 方法：將dept_code，teach_schedule，lesson_info，teacher_info四個表連接起來，根據(jù)前面做題查找過計算機工程系系號為01，選擇出相應(yīng)課程表。代碼：/*顯示“計算機工程系”老師所教課程的成績表*/use sunjialu3_datagoselect deptname as 系 ,name as 老師, course_name as 課程 from dept_codejoin teach_schedule on dept_code.deptcode=teach_schedule.deptcodejoin lesson_

22、info on teach_schedule.course_id=lesson_info.course_idjoin teacher_info on lesson_info.course_id=teacher_info.course_idwhere dept_code.deptcode='01'【思考題】1、 顯示課程號為“0401010103”課程成績高于“0401010104”課程的學(xué)生（同時選了兩門課）的學(xué)號、姓名和課程“0401010103”成績。要求給出兩種以上（包括兩種）的答案和相應(yīng)結(jié)果。如果要求同時給出兩門課的成績，你有什么好辦法嗎？方法一：給stud_grade

23、表賦予兩個別名one two，對其進行比較，在one表中選出0401010103課程，同時在two中選出0401010104課程，使其對應(yīng)相等即得到同時選了兩個課程的同學(xué)，最后使one中的03課程成績>two中04課程的成績，即可完成比較。代碼：/*顯示課程號為“”課程成績高于“”課程的學(xué)生（同時選了兩門課）的學(xué)號、姓名和課程“”成績*/use sunjialu3_datagoselect distinct one.stud_id as 學(xué)號, as 姓名,one.grade as 課程成績from stud_grade one,stud_grade twowhere one.course_id='0401010103' and two.course_id='0401010104'and one.stud_id=two.stud_id and one.grade>two.grade方法二：給stud_grade賦予兩個別名，將兩個別名代表的成績表通過學(xué)號相同連接起來，同時選出03課程和04課