发布时间 : 星期一 文章数据库系统实验课实验报告更新完毕开始阅读65f04073a1116c175f0e7cd184254b35effd1a78
。
4. 参照表student建立一张空表student1。要求用两种方法实现 (create table; create table as …)。 5. 增加section表上的约束条件，要求所输入section的年号不大于当前年号+1，且不小于当前年号-2。插入一条2009年秋CS-101 开设课的信息，观察并解释实验结果。 6. 选择某个约束，分别设置其有效、失效，观察区别。 出现问题 没有问题 解决方案 （列出遇到的问题及其解决方法）
《数据库系统》实验报告三
学号 姓名 实验时间 实验学时 4 实验名称 数据查询 准备材料 1. SQL Plus命令手册 2. Oracle数据字典 -可编辑修改-
。
扩展实验 1. 利用企业管理器的图形界面构造查询语句，并察看查询结果 2. 利用企业管理器完成视图、索引的创建与使用。 3. 利用DBMS进行对第三章习题所设计SQL语句的检查 (此部分内容不要求在实验室完成，不用写入实验报告。) 实验环境 Oracle 9i(及以上版本)服务器 SQL Plus/ SQL Plus work sheet客户端 实验目的 1．掌握使用SQL语句进行数据查询的方法 2. 掌握视图的创建与使用方法 3. 观察索引的使用效果 实验内容1. 执行文件ddl+drop.sql，创建数据库University中所有表。执行文及步骤 件 smallRelationsInsertFile.sql，插入实验数据。 2. 使用University数据库的数据库结构和数据（smallRelations即可），完成下列查询： (1) Find the names of courses in Computer science department which have 3 credits (2) For the student with ID 12345 (or any other value), show all course_id and title of all courses registered for by the student. (3) As above, but show the total number of credits for such courses (taken by that student). Don't display the tot_creds value from the student table, you should use SQL aggregation on courses taken by the student. -可编辑修改-
。
(4) As above, but display the total credits for each of the students, along with the ID of the student; don't bother about the name of the student. (Don't bother about students who have not registered for any course, they can be omitted) (5) Find the names of all students who have taken any Comp. Sci. course ever (there should be no duplicate names) (6) Display the IDs of all instructors who have never taught a couse (Notes 1) Oracle uses the keyword minus in place of except; 2) interpret \ (7) As above, but display the names of the instructors also, not just the IDs. (8) Find the maximum and minimum enrollment across all sections, considering only sections that had some enrollment, don't worry about those that had no students taking that section (9) As in Question (8), but now also include sections with no students taking them; the 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) (10) Find all courses whose identifier starts with the string \ (11) Find instructors who have taught all the above courses 1). -可编辑修改-
。
Using the \counts which we covered in class (don't forget the distinct clause!) 3. 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). 4. Find all students who have 2 or more non-overridden F grades as per the takes relation, and list them along with the F 出现问题 解决方案 （列出遇到的问题及其解决方法）
-可编辑修改-