华南农业大学数据库系统概念实验报告三

数据库系统数据库系统 实验报告三实验报告三 学 号 姓名实验时间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 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 SELECT course id title FROM takes NATURAL JOIN course WHERE id 12345 4 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 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 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 SELECT id SUM credits FROM takes NATURAL JOIN student NATURAL JOIN course GROUP 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 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 display the names of the instructors also not just the IDs SELECT id NAME FROM instructor WHERE id NOT IN SELECT DISTINCT id FROM teaches 7 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 SELECT max enrollment min enrollment from SELECT sec id semester year COUNT DISTINCT id as enrollment FROM takes GROUP BY sec id semester YEAR 8 As in in Q1 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 SELECT DISTINCT sec id semester YEAR IFNULL count 0 FROM section LEFT OUTER JOIN SELECT sec id semester YEAR COUNT DISTINCT id sec id semester YEAR AS count FROM takes GROUP 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 course WHERE course id LIKE CS 1 10 Find instructors who have taught all the above courses 1 Using the not exists except structure 2 Using matching of counts which we covered in class don t forget the distinct clause select distinct ID name from teaches natural join instructor where not exists select course id from course except select course 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 F 3 Find all students who have 2 or more non overridden F grades