實(shí)驗(yàn)2-實(shí)驗(yàn)報(bào)告

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

2、、查詢“計(jì)算機(jī)工程系”、“計(jì)算機(jī)網(wǎng)絡(luò)技術(shù)專(zhuān)業(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é)生成績(jī)表中成績(jī)?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é)生成績(jī)表中的最高分的學(xué)生學(xué)號(hào)和課程號(hào)。方法：使用子查詢功能，和計(jì)算函數(shù)max程序: use sunjialu3_datagoselect stud_id course_id from stud_gradewhere grade in(select max(grade)from stud_grade )4、 查詢“計(jì)算機(jī)工程系”、“計(jì)算機(jī)網(wǎng)絡(luò)技術(shù)專(zhuān)業(yè)”、02 班全體學(xué)生的基本信息方法：先查找出“計(jì)算機(jī)網(wǎng)絡(luò)技術(shù)專(zhuān)業(yè)”所對(duì)應(yīng)的的編

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

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

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

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

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

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

10、人數(shù)*/use sunjialu3_datagoselect substring(teacher_id,3,2) as 教研室, tech_title as 職稱(chēng) ,count (*) as 教研人數(shù) from teacher_infogroup by substring(teacher_id,3,2),tech_title 14、 將計(jì)算機(jī)工程系職稱(chēng)為講師的教師按年齡由低到高排列。方法：采用子查詢的方法，先選出計(jì)算機(jī)工程系的系號(hào)，在選出對(duì)應(yīng)系號(hào)的課程號(hào)，從而選出教授計(jì)算機(jī)工程系的講師們，在按年齡由低到高排序，使用order by 的asc語(yǔ)句。代碼：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='計(jì)算機(jī)工程系')order by age asc15、 將所有選了課的學(xué)生的學(xué)號(hào)按平均成績(jī)由高到低排列。代碼：use sunjialu3_datagoselect stud_id as 學(xué)號(hào), av

12、g(grade) as 平均成績(jī) 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é)生成績(jī)表中課程號(hào)為“0401010106”的成績(jī)加 5。代碼：use sunjialu3_datagoupdate stud_grade set grade=grad

13、e+5where course_id='0401010106'18、刪除 stud_info 學(xué)生信息表中姓名為“張?jiān)础钡膶W(xué)生記錄。方法：使用delete函數(shù)，又條件刪除。代碼：use sunjialu3_datagodelete from stud_info where name='張?jiān)?#39;19、求選修了“數(shù)據(jù)庫(kù)原理”的學(xué)生的學(xué)號(hào)及姓名。方法：子查詢方法。代碼：use sunjialu3_datagoselect stud_id as 學(xué)號(hào), 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ù)庫(kù)')20、查詢所有沒(méi)選修“0401010103”課程的學(xué)生學(xué)號(hào)及姓名。代碼：use sunjialu3_datagoselect stud_id as 學(xué)號(hào), name as 姓名 from stud_infowhere stud_id in(select stud_id from stud_gradew

15、here course_id !='0401010103')21、查詢與王剛在同一個(gè)系學(xué)習(xí)的學(xué)生的姓名和所在的系。方法：查找王剛計(jì)算機(jī)工程系的系號(hào)。代碼：/*查詢與王剛在同一個(gè)系學(xué)習(xí)的學(xué)生的姓名和所在的系*/use sunjialu3_datagoselect deptcode as 系號(hào) from dept_codewhere deptname='計(jì)算機(jī)工程系' 方法：將dept_code， teach_schedule， stud_grade三個(gè)表連接起來(lái)，查找，系號(hào)為01的學(xué)生姓名和所在系。代碼：/*查詢與王剛在同一個(gè)系學(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、查詢成績(jī)?yōu)樾∮?0分的學(xué)生的學(xué)號(hào)、姓名。方法：將stud_info，stud_grade表連接起來(lái)，找尋條件為成績(jī)小于60分。代碼：/*查詢成績(jī)?yōu)樾∮诜值膶W(xué)

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

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

19、方法：將teacher_info，teach_schedule兩個(gè)表連接起來(lái)，選擇課程號(hào)是0401010105的上課教師姓名即可。代碼：/*顯示選了課程號(hào)為“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)計(jì)不及格人

20、數(shù)在 3 人以上的課程的數(shù)量。方法：子查詢方法，選出成績(jī)<60的課程號(hào)，再將其根據(jù)課程號(hào)分組，選出其中人數(shù)>3的課程號(hào)，用計(jì)算函數(shù)count輸出此類(lèi)課程的數(shù)量即可。代碼：/*統(tǒng)計(jì)不及格人數(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、計(jì)算機(jī)工程系”老師所教課程的成績(jī)表。 方法：將dept_code，teach_schedule，lesson_info，teacher_info四個(gè)表連接起來(lái)，根據(jù)前面做題查找過(guò)計(jì)算機(jī)工程系系號(hào)為01，選擇出相應(yīng)課程表。代碼：/*顯示“計(jì)算機(jī)工程系”老師所教課程的成績(jī)表*/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、 顯示課程號(hào)為“0401010103”課程成績(jī)高于“0401010104”課程的學(xué)生（同時(shí)選了兩門(mén)課）的學(xué)號(hào)、姓名和課程“0401010103”成績(jī)。要求給出兩種以上（包括兩種）的答案和相應(yīng)結(jié)果。如果要求同時(shí)給出兩門(mén)課的成績(jī)，你有什么好辦法嗎？方法一：給stud_grade

23、表賦予兩個(gè)別名one two，對(duì)其進(jìn)行比較，在one表中選出0401010103課程，同時(shí)在two中選出0401010104課程，使其對(duì)應(yīng)相等即得到同時(shí)選了兩個(gè)課程的同學(xué)，最后使one中的03課程成績(jī)>two中04課程的成績(jī)，即可完成比較。代碼：/*顯示課程號(hào)為“”課程成績(jī)高于“”課程的學(xué)生（同時(shí)選了兩門(mén)課）的學(xué)號(hào)、姓名和課程“”成績(jī)*/use sunjialu3_datagoselect distinct one.stud_id as 學(xué)號(hào), as 姓名,one.grade as 課程成績(jī)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賦予兩個(gè)別名，將兩個(gè)別名代表的成績(jī)表通過(guò)學(xué)號(hào)相同連接起來(lái)，同時(shí)選出03課程和04課