1、數(shù)據(jù)庫(kù)系統(tǒng)實(shí)驗(yàn)報(bào)告三學(xué)號(hào)姓名實(shí)驗(yàn)時(shí)間2014-11-26實(shí)驗(yàn)名稱數(shù)據(jù)查詢實(shí)驗(yàn)學(xué)時(shí)4準(zhǔn)備材料1. SQL Plus命令手冊(cè)2. Oracle數(shù)據(jù)字典擴(kuò)展實(shí)驗(yàn)1. 利用企業(yè)管理器的圖形界面構(gòu)造查詢語(yǔ)句，并察看查詢結(jié)果2. 利用企業(yè)管理器完成視圖、索引的創(chuàng)建與使用。3. 利用DBMS進(jìn)行對(duì)第三章習(xí)題所設(shè)計(jì)SQL語(yǔ)句的檢查 (此部分內(nèi)容不要求在實(shí)驗(yàn)室完成，不用寫(xiě)入實(shí)驗(yàn)報(bào)告。)實(shí)驗(yàn)環(huán)境Oracle 9i(及以上版本)服務(wù)器SQL Plus/ SQL Plus work sheet客戶端實(shí)驗(yàn)?zāi)康?掌握使用SQL語(yǔ)句進(jìn)行數(shù)據(jù)查詢的方法2. 掌握視圖的創(chuàng)建與使用方法3. 觀察索引的使用效果實(shí)驗(yàn)內(nèi)容及步驟1.

2、使用University數(shù)據(jù)庫(kù)的數(shù)據(jù)庫(kù)結(jié)構(gòu)和數(shù)據(jù)（smallRelations即可），完成下列查詢：(1) Find the names of courses in Computer science department which have 3 creditsSELECT titleFROM courseWHERE dept_name = Comp. Sci. AND credits = 3(2) For the student with ID 12345 (or any other value), show all course_id and title of all courses re

3、gistered for by the student. SELECT course_id,titleFROM takes NATURAL JOIN courseWHERE id = 123454. As above, but show the total number of credits for such courses (taken by that student). Dont display the tot_creds value from the student table, you should use SQL aggregation on courses taken by the

4、 student. SELECT id,SUM(credits)FROM takes NATURAL JOIN student NATURAL JOIN courseWHERE id = 12345GROUP BY id;(3) As above, but display the total credits for each of the students, along with the ID of the student; dont bother about the name of the student. (Dont bother about students who have not r

5、egistered for any course, they can be omitted) SELECT id,SUM(credits)FROM takes NATURAL JOIN student NATURAL JOIN courseGROUP BY id(4) Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names) SELECT DISTINCT id,NAMEFROM takes NATURAL JOIN studentW

6、HERE course_id IN (SELECT course_id FROM course WHERE dept_name=Comp. Sci.)(5) Display the IDs of all instructors who have never taught a course (Notesad1) Oracle uses the keyword minus in place of except; (2) interpret taught as taught or is scheduled to teach)SELECT idFROM instructorWHERE id NOT I

7、N (SELECT DISTINCT id FROM teaches ) (6) As above, but display the names of the instructors also, not just the IDs.SELECT id,NAMEFROM instructorWHERE id NOT IN (SELECT DISTINCT id FROM teaches )(7) Find the maximum and minimum enrollment across all sections, considering only sections that had some e

8、nrollment, dont worry about those that had no students taking that sectionSELECT max(enrollment),min(enrollment)from(SELECT sec_id,semester,year,COUNT(DISTINCT id) as enrollmentFROM takesGROUP BY sec_id,semester,YEAR); (8) As in in Q1, but now also include sections with no students taking them; the

9、enrollment for such sections should be treated as 0. Do this in two different ways (and create require data for testing) 1). Using a scalar subquery 2). Using aggregation on a left outer join (use the SQL natural left outer join syntax)SELECT DISTINCT sec_id,semester,YEAR,IFNULL(count,0)FROM section

10、 LEFT OUTER JOIN(SELECT sec_id,semester,YEAR,COUNT(DISTINCT id,sec_id,semester,YEAR) AS countFROM takesGROUP BY sec_id,semester,YEAR) AS T USING (sec_id,semester,YEAR)(9) Find all courses whose identifier starts with the string CS-1 SELECT *FROM courseWHERE course_id LIKE CS-1%(10) Find instructors

11、who have taught all the above courses 1). Using the not exists . except . structure 2). Using matching of counts which we covered in class (dont forget the distinct clause!) select distinct ID,name from teaches natural join instructor where not exists (select course_id from course)except (select cou

12、rse_id from course where course_id like CS-1%);2. The university rules allow an F grade to be overridden by any pass grade (A, B, C, D). Now, create a view that lists information about all fail grades that have not been overridden (the view should contain all attributes from the takes relation).CREATE VIEW F AS SELECT * FROM takes WHERE grade = F3. Find all students who have 2 or more non-overrid