华南农业大学数据库系统概念实验报告材料的三.docxVIP

实用标准文案 《数据库系统》实验报告三 学 姓名 实验时间 2014-11-26 号 实 数据查询 实验学时 4 验 名 称 准 1. SQL Plus 命令手册备 2. Oracle 数据字典 材 料 扩 1. 利用企业管理器的图形界面构造查询语句，并察看查询结果展 2. 利用企业管理器完成视图、索引的创建与使用。 实 3. 利用 DBMS进行对第三章习题所设计 SQL语句的检查 验 ( 此部分内容不要求在实验室完成，不用写入实验报告。 ) 实 Oracle 9i( 及以上版本 ) 服务器 验 SQL Plus/ SQL Plus work sheet 客户端 环 境 实 1．掌握使用 SQL语句进行数据查询的方法验 2. 掌握视图的创建与使用方法 目 3. 观察索引的使用效果 的 实 1. 使用 University 数据库的数据库结构和数据（ smallRelations 即可），验 完成下列查询： 内 (1) Find the names of courses in Computer science department which 容 have 3 credits 及 步 SELECT title 骤 FROM course WHERE dept_name = Comp. Sci. AND credits = 3 精彩文档 实用标准文案 For the student with ID 12345 (or any other value), show all course_id and title of all courses registered for by the student. SELECT course_id,title FROM takes NATURAL JOIN course WHERE id = 12345 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 student. 精彩文档 实用标准文案 SELECT id,SUM(credits) FROM takes NATURAL JOIN student NATURAL JOIN course WHERE id = 12345 GROUP 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 registered for any course, they can be omitted) SELECT id,SUM(credits) FROM takes NATURAL JOIN student NATURAL JOIN course GROUP BY id 精彩文档 实用标准文案 Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names) SELECT DISTINCT id,NAME FROM takes NATURAL JOIN student WHERE 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 id FROM instructor WHERE id NOT IN (SELECT DISTINCT id FROM teaches ) (6) As above, but disp