华南农业大学数据库系统概念实验报告三.docx
《华南农业大学数据库系统概念实验报告三.docx》由会员分享,可在线阅读,更多相关《华南农业大学数据库系统概念实验报告三.docx(12页珍藏版)》请在冰点文库上搜索。
![华南农业大学数据库系统概念实验报告三.docx](https://file1.bingdoc.com/fileroot1/2023-4/30/33152622-d747-4084-b842-272b394f4c81/33152622-d747-4084-b842-272b394f4c811.gif)
华南农业大学数据库系统概念实验报告三
华南农业大学数据库系统概念实验报告三
————————————————————————————————作者:
————————————————————————————————日期:
ﻩ
《数据库系统》实验报告三
学号
姓名
实验时间
2014-11-26
实验名称
数据查询
实验学时
4
准备材料
1. SQLPlus命令手册
2.Oracle数据字典
扩展实验
1.利用企业管理器的图形界面构造查询语句,并察看查询结果
2. 利用企业管理器完成视图、索引的创建与使用。
3.利用DBMS进行对第三章习题所设计SQL语句的检查
(此部分内容不要求在实验室完成,不用写入实验报告。
)
实验环境
Oracle9i(及以上版本)服务器
SQLPlus/SQLPlusworksheet客户端
实验目的
1.掌握使用SQL语句进行数据查询的方法
2. 掌握视图的创建与使用方法
3.观察索引的使用效果
实验内容及步骤
1.使用University数据库的数据库结构和数据(smallRelations即可),完成下列查询:
(1)Findthenamesofcourses inComputerscience departmentwhichhave3 credits
SELECT title
FROMcourse
WHERE dept_name='Comp.Sci.'ANDcredits = 3
(2)ForthestudentwithID12345(oranyothervalue), showallcourse_idandtitleofall coursesregistered forbythestudent.
SELECTcourse_id,title
FROMtakesNATURAL JOIN course
WHEREid =12345
4.Asabove, but show thetotalnumber ofcredits for suchcourses(taken bythatstudent). Don't display thetot_credsvaluefromthestudenttable, youshoulduseSQLaggregationoncourses takenby the student.
SELECTid,SUM(credits)
FROMtakes NATURALJOINstudentNATURALJOIN course
WHEREid= 12345
GROUPBYid;
(3) Asabove,but displaythe totalcredits foreachofthestudents,alongwiththeID of thestudent;don'tbotheraboutthenameofthestudent.(Don't botheraboutstudentswhohavenotregisteredforany course, theycanbeomitted)
SELECTid,SUM(credits)
FROMtakesNATURALJOINstudentNATURAL JOINcourse
GROUPBYid
(4)Findthenames ofallstudents who have takenanyComp.Sci.courseever (thereshouldbe noduplicatenames)
SELECTDISTINCTid,NAME
FROM takesNATURALJOINstudent
WHERE course_idIN(SELECTcourse_id
FROMcourse
WHEREdept_name='Comp.Sci.')
(5)DisplaytheIDsofall instructorswhohavenever taught acourse (Notesad1) Oracle uses thekeywordminus inplace ofexcept;(2) interpret"taught"as "taughtorisscheduled toteach")
SELECTid
FROMinstructor
WHEREidNOTIN(SELECT DISTINCTid
FROMteaches
)
(6)As above,butdisplaythe names oftheinstructorsalso,notjusttheIDs.
SELECTid,NAME
FROM instructor
WHEREid NOT IN(SELECTDISTINCTid
FROMteaches
)
(7)Findthe maximumand minimumenrollment acrossallsections,consideringonly sectionsthathadsome enrollment,don'tworryabout those that hadnostudentstakingthatsection
SELECTmax(enrollment),min(enrollment)
from(
SELECTsec_id,semester,year,
COUNT(DISTINCT id)asenrollment
FROMtakes
GROUPBYsec_id,semester,YEAR);
(8)Asin inQ1,butnowalsoincludesections withnostudentstakingthem; theenrollment forsuchsectionsshouldbetreatedas0.Dothisintwodifferentways(and createrequiredata fortesting)1).Usingascalarsubquery2).Using aggregation on a leftouter join(usethe SQLnaturalleft outerjoinsyntax)
SELECT DISTINCT sec_id,semester,YEAR,IFNULL(`count`,0)
FROMsection LEFT OUTERJOIN
(SELECT sec_id,semester,YEAR,COUNT(DISTINCT id,sec_id,semester,YEAR)AS 'count'
FROM takes
GROUPBYsec_id,semester,YEAR)ASTUSING(sec_id,semester,YEAR)
(9)Findall courseswhoseidentifier startswiththestring"CS-1"
SELECT*
FROMcourse
WHEREcourse_idLIKE 'CS-1%'
(10) Find instructorswhohavetaughtall theabovecourses1).Usingthe "not exists ... except..."structure 2).Usingmatching ofcountswhichwecoveredin class (don'tforget thedistinctclause!
)
selectdistinctID,namefromteachesnaturaljoininstructor
where notexists((select course_idfrom course)
except(select course_idfromcoursewherecourse_idlike 'CS-1%'));
2.TheuniversityrulesallowanF grade tobeoverriddenbyanypassgrade(A,B,C, D).Now,create a view that listsinformationaboutallfailgrades thathave notbeenoverridden(theviewshouldcontainall attributes fromthetakesrelation).
CREATE VIEWF AS
SELECT *
FROMtakes
WHEREgrade='F'
3.Find allstudentswhohave 2ormorenon-overriddenFgradesasperthe takesrelation, andlist themalongwiththeF
selectname,'F'asfinal_grade
from F naturaljoinstudent
group byname
havingcount(grade)>=2;
选择数量〉=1时有一个结果
出现问题
解决方案
(列出遇到的问题及其解决方法)
ﻬ