华南农业大学数据库系统概念实验报告三.doc
《华南农业大学数据库系统概念实验报告三.doc》由会员分享,可在线阅读,更多相关《华南农业大学数据库系统概念实验报告三.doc(10页珍藏版)》请在淘文阁 - 分享文档赚钱的网站上搜索。
1、|数据库系统实验报告三学号姓名 实验时间 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. 观察索引的使
2、用效果实验内容及步骤1. 使用 University 数据库的数据库结构和数据( 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 titl
3、e of all courses registered 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 c
4、ourses taken by the 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 stu
5、dents who have not registered 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
6、NATURAL JOIN studentWHERE course_id IN (SELECT course_idFROM courseWHERE 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
- 配套讲稿:
如PPT文件的首页显示word图标,表示该PPT已包含配套word讲稿。双击word图标可打开word文档。
- 特殊限制:
部分文档作品中含有的国旗、国徽等图片,仅作为作品整体效果示例展示,禁止商用。设计者仅对作品中独创性部分享有著作权。
- 关 键 词:
- 华南 农业大学 数据库 系统 概念 实验 报告
限制150内