2 create a view called currentregs that reflects all

  • University of Texas
  • C S 327e
  • Notes
  • Blfontenot
  • 49
  • 100% (6) 6 out of 6 people found this document helpful

This preview shows page 38 - 41 out of 49 pages.

2. Create a view called CURRENT_REGS that reflects all students who registered on January 25, 2007. Grant SELECT privileges on the new view to the REGISTRAR role.
3. Create a view called roster that reflects all students taught by the instructor Marilyn Frantzen. Grant SELECT privileges on the new view to the INSTRUCTOR role.
Version Date: 2/11/2016
CREATE OR REPLACE VIEW roster AS SELECT se.course_no course, se.section_id section, s.first_name first, s.last_name last, e.student_id FROM student s, enrollment e, section se, instructor i WHERE s.student_id = e.student_id AND e.section_id = se.section_id AND se.instructor_id = i.instructor_id AND i.first_name = 'Marilyn' AND i.last_name = 'Frantzen'; GRANT SELECT ON roster TO instructor; 4. Grant the REGISTRAR and INSTRUCTOR roles to the new user called SCHOOL.
5. Log in as the user SCHOOL and select from the two previously created views.
Chapter 16: Regular Expressions and Hierarchical Queries 1. Name other hierarchical relationships you are familiar with.
Solutions to Workshop Exercises - Oracle SQL by Example 4e ; Alice Rischert; Prentice Hall (2009) Web site Page 39 of 45 Version Date: 2/11/2016
2. Change the prerequisite of course number 310 Operating Systems, a root row in the hierarchy, from a null value to 145 Internet Protocols. Write the query to detect the loop in the hierarchy, using the CONNECT_BY_ISCYCLE pseudocolumn.
looks like this: 310 Operating Systems 130 Intro to Unix 132 Basics of Unix Admin 134 Advanced Unix Admin 135 Unix Tips and Techniques 330 Network Administration 145 Internet Protocols The UPDATE statement will create a loop in the hierarchy. UPDATE course SET prerequisite = 145 WHERE course_no = 310 Essentially, the CONNECT_BY_ISCYCLE returns the value of 1 if a row has a child which is its own ancestor. The next query detects the loop. SELECT * FROM (SELECT course_no, prerequisite, SYS_CONNECT_BY_PATH(course_no, '/') AS "Path", LEVEL, CONNECT_BY_ISCYCLE AS cycle FROM course

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture

  • Left Quote Icon

    Student Picture