數(shù)據庫實驗四 查詢

1、華 中 科 技 大 學數(shù)據庫技術與應用實驗報告專業(yè)班級： 學生姓名： 學號：實驗地點： 指導教師： 實驗日期時間：一、實驗項目名稱：數(shù)據查詢二、實驗學時： 三、實驗目的：1. 掌握使用Transact-SQL的SELECT語句進行基本查詢的方法。2. 掌握使用SELECT語句進行條件查詢的方法。3. 掌握嵌套查詢的方法。4. 掌握多表查詢的方法。5. 掌握SELECT語句的GROUP BY和ORDER BY子句的作業(yè)和使用方法。四、實驗工具或環(huán)境一臺裝有SQL sever2008的計算機五、實驗內容、步驟、代碼和結果：0.   創(chuàng)建studentsdb數(shù)據庫及其相應表，并錄

2、入數(shù)據。   在SQL Server管理平臺上，點擊“新建查詢”打開查詢編輯器窗口，復制粘貼附件“創(chuàng)建數(shù)據庫代碼”中的代碼到查詢編輯器窗口，運行即可生成相關數(shù)據庫和表，并有相應的數(shù)據。1. 在studentsdb數(shù)據庫中，使用下列SQL語句將輸出什么？（1） SELECT COUNT(*) FROM grade（2） SELECT SUBSTRING(姓名,1,2) FROM student_info（3） SELECT UPPER('kelly') （4） SELECT Replicate('kelly',3) （5） SELEC

3、T SQRT(分數(shù)) FROM grade WHERE 分數(shù)>=85（6） SELECT 2,3,POWER(2,3) 選做（7） SELECT YEAR(GETDATE(),MONTH(GETDATE(),DAY(GETDATE()2. 在studentsdb數(shù)據庫中使用SELECT語句進行基本查詢。（1） 在student_info表中，查詢每個學生的學號、姓名、出生日期信息。（2） 查詢學號為0002的學生的姓名和家庭住址。select 姓名,家庭住址 from student_info where 學號='0002' （3） 找出所有男同學的學號和姓名。selec

4、t 姓名, 家庭住址 from student_info where 性別='男'3. 使用SELECT語句進行條件查詢（1） 在grade表中查找分數(shù)在8090分為內的學生的學號和分數(shù)。select 學號,分數(shù) from grade where 分數(shù)>='80'and 分數(shù)<='90'（2） 在grade表中查詢課程編號為0003的學生的平均分。select AVG (分數(shù)) as 平均分 from grade where 課程編號='0003'（3） 在grade表中查詢學習各門課程的人數(shù)select COUNT

5、(*) from grade（4） 將學生按出生日期由大到小排序。select 學號,姓名,出生日期 from student_info order by 出生日期,學號 desc （5） 查詢所有姓“張”的學生的學號和姓名。SELECT 學號,姓名 FROM student_info WHERE 姓名 LIKE '張%'4. 對student_info表，按性別順序列出學生的學號、姓名、性別、出生日期及家庭住址，性別相同的按學號由小到大排序。select 學號,姓名,性別,出生日期,家庭住址 from student_info order by student_info.性別

6、,學號 asc5. 使用GROUP BY查詢子句列出各個學生的平均成績。select a.學號,a.姓名,AVG (b.分數(shù))as 平均成績 from studentsdb.dbo.student_info ainner joingrade bon a.學號=b.學號 group by a.姓名, a.學號6. 使用UNION運算符將student_info表中姓“劉”的學生的學號、姓名與姓“張”的學生的學號、姓名返回在一個表中，如圖4-1所示。圖 4-1select 學號,姓名 from studentsdb .dbo .student_info where 姓名 like '劉%&

7、#39;union allselect 學號,姓名 from studentsdb .dbo .student_info where 姓名 like '張%7. 嵌套查詢（1） 在student_info表中查找與“劉衛(wèi)平”性別相同的所有學生的姓名、出生日期。select 姓名,出生日期 from studentsdb .dbo .student_info where 性別= (select 性別 from student_info where 姓名 ='劉衛(wèi)平' )（2） 使用IN子查詢查找所修課程編號為0002、0005的學生學號、姓名、性別。select 學號,姓

8、名,性別 from studentsdb .dbo .student_info where 學號 in(select 學號 from grade where 課程編號 ='0002' or 課程編號 ='0005')（3） 列出學號為0001的學生的分數(shù)比0002號的學生的最低分數(shù)高的課程編號和分數(shù)。select 課程編號,分數(shù) from grade where 學號 ='0001'and 分數(shù)> (select MIN (分數(shù)) from grade where 學號='0002')（4） 列出學號為0001的學生的分數(shù)比

9、0002的學生的最高成績還要高的課程編號和分數(shù)。select 課程編號,分數(shù) from grade where 學號 ='0001'and 分數(shù)> (select max (分數(shù)) from grade where 學號='0002')8. 多表查詢（1） 查詢分數(shù)在8090范圍內的學生的學號、姓名、分數(shù)。SELECT student_info.學號,姓名,分數(shù)FROM student_info,gradeWHERE student_info.學號=grade.學號 AND 分數(shù) BETWEEN 80 AND 90（2） 查詢學習“C語言程序設計”課程的學

10、生的學號、姓名、分數(shù)。SELECT student_info.學號,姓名,分數(shù)FROM student_info inner join grade on student_info.學號=grade.學號where 課程編號=(select 課程編號 from curriculum where 課程名稱='C語言程序設計')（3）查詢所有男同學的選課情況，要求列出學號、姓名、課程名稱、分數(shù)。SELECT student_info.學號,姓名,curriculum.課程名稱,分數(shù)FROM student_info , grade ,curriculumwhere 性別='男

11、'and student_info.學號=grade.學號 and curriculum.課程編號=grade.課程編號 （4） 查詢每門課程的最高成績，要求列出課程編號、分數(shù)。USE studentsdbSELECT DISTINCT student_info.學號,姓名,課程編號,分數(shù)FROM grade,student_infoWHERE student_info.學號=grade.學號 AND 分數(shù)=(SELECT MAX(分數(shù)) FROM grade WHERE student_info.學號=grade.學號) （5） 查詢所有學生的總成績，要求列出學號、姓名、總成績，沒有

12、選修課程的學生的總成績?yōu)榭铡Ｌ崾荆菏褂米笸膺B接。select student_info.學號,姓名,sum(分數(shù))as總成績 from student_info left outer join grade on student_info.學號=grade.學號 group by student_info.學號,姓名（6） 為grade表添加數(shù)據行：學號0004、課程編號為0006、成績?yōu)?6。查詢所有課程的選修情況，要求列出課程編號、課程名稱、選修人數(shù)，curriculum表中沒有的課程列值為空。提示：使用右外連接。goinsert studentsdb.dbo.grade (學號,課程編號,

13、分數(shù) )values('0004','0006','76');select grade.課程編號,課程名稱,count(*)from studentsdb.dbo.curriculum right outer join studentsdb.dbo.grade on grade.課程編號=curriculum.課程編號 group by grade.課程編號,課程名稱六、實驗思考1 查詢所有沒有選修課程的學生信息，返回結果包括學號、姓名、性別。select 學號,姓名,性別 from studentsdb .dbo.student_info where 學號 not in (select 學號 from studentsdb .dbo.grade ) 2 在student_info表和grade表之間實現(xiàn)交叉連接。select * from studentsdb .dbo.student_info a cross join studentsdb .dbo.grade 3 查詢每個學生的所選課程的成績，并列出學號生成分組匯總行（總成績）和明細行（各課成績）。select 學號,分數(shù) from studentsdb.dbo.grade order by 學號 compute sum(分數(shù))by 學號提示：使用SELECT語句的