Oracle by Example Workshop-Solutions

Oracle by Example Workshop-Solutions - Solutions To...

Info icon This preview shows pages 1–4. Sign up to view the full content.

View Full Document Right Arrow Icon
Solutions To Workshop Exercises Chapter 1: SQL and Data ........................................................... 1 Chapter 2: SQL: The Basics ....................................................... 3 Chapter 3: The WHERE and ORDER BY Clauses ............................. 4 Chapter 4: Character, Number, and Miscellaneous Functions .......... 6 Chapter 5: Date and Conversion Functions ................................... 9 Chapter 6: Aggregate Functions, GROUP BY and HAVING ............. 12 Chapter 7: Equijoins ............................................................... 15 Chapter 8: Subqueries ............................................................ 20 Chapter 9: Set Operators ......................................................... 23 Chapter 10: Complex Joins ...................................................... 26 Chapter 11: Insert, Update, and Delete ..................................... 30 Chapter 12: Create, Alter, and Drop Tables ................................ 33 Chapter 13: Indexes, Sequences, and Views ............................... 35 Chapter 14: The Data Dictionary, Scripting, and Reporting ........... 36 Chapter 15: Security ............................................................... 39 Chapter 16: Regular Expressions and Hierarchical Queries ............ 40 Chapter 17: Exploring Data Warehousing Features ...................... 43 Chapter 18: SQL Optimization .................................................. 45 Chapter 1: SQL and Data In this chapter, you learned about data, how data is organized in tables, and how the relationships among the tables are depicted in a schema diagram. Based on your newly acquired knowledge, design a schema diagram based on the fictional ACME Construction Company. Draw on your own work experience to design the following components. 1. Draw boxes for these three tables: EMPLOYEE, POSITION, and DEPARTMENT. Solution: See the solution for Exercise 3. 2. Create at least three columns for each of the tables and designate a primary key for each table. Solutions to Workshop Exercises - Oracle SQL by Example 4e ; Alice Rischert; Prentice Hall (2009) Web site http://www.oraclesqlbyexample.com Page 1 of 47 Version Date: 12/10/2011
Image of page 1

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Solution: See the solution for Exercise 3. 3. Create relationships among the tables that make sense to you. At least one table should have a self-referencing relationship. Hint: Be sure to include the necessary foreign key columns. Solution: 4. Think about which columns should not allow NULL values. Solution: By definition all the primary key columns do not allow null values. In the DEPARTMENT table the DEPARTMENT_NAME column should probably not allow null values. In the EMPLOYEE table the FIRST_NAME and LAST_NAME columns are two more candidates for NOT NULL columns because all employees should have names. The foreign key columns DEPARTMENT_ID and POSITION_ID must be NOT NULL as the relationships in the above diagram indicates. The diagram states that for an individual row in the EMPLOYEE table always a row must exist in the POSITION table and the DEPARTMENT table. The MANAGER_ID column on the other hand must allow nulls as indicated with the optional recursive relationship. If this was not an optional relationship, you would not be able to enter the president of company for instance, because it requires an existing entry for the Solutions to Workshop Exercises - Oracle SQL by Example 4e ; Alice Rischert; Prentice Hall (2009) Web site http://www.oraclesqlbyexample.com Page 2 of 47 Version Date: 12/10/2011
Image of page 2
President's manager. Therefore, the top of the org chart hierarchy (e.g., the president) has a null value in the MANAGER_ID column.
Image of page 3

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
Image of page 4
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

What students are saying

  • Left Quote Icon

    As a current student on this bumpy collegiate pathway, I stumbled upon Course Hero, where I can find study resources for nearly all my courses, get online help from tutors 24/7, and even share my old projects, papers, and lecture notes with other students.

    Student Picture

    Kiran Temple University Fox School of Business ‘17, Course Hero Intern

  • Left Quote Icon

    I cannot even describe how much Course Hero helped me this summer. It’s truly become something I can always rely on and help me. In the end, I was not only able to survive summer classes, but I was able to thrive thanks to Course Hero.

    Student Picture

    Dana University of Pennsylvania ‘17, Course Hero Intern

  • Left Quote Icon

    The ability to access any university’s resources through Course Hero proved invaluable in my case. I was behind on Tulane coursework and actually used UCLA’s materials to help me move forward and get everything together on time.

    Student Picture

    Jill Tulane University ‘16, Course Hero Intern