Oracle by Example Workshop-Solutions

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

Info iconThis preview shows pages 1–3. Sign up to view the full content.

View Full Document Right Arrow Icon

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

View Full DocumentRight Arrow Icon
This is the end of the preview. Sign up to access the rest of the document.

Unformatted text preview: 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 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....
View Full Document

Page1 / 47

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

This preview shows document pages 1 - 3. Sign up to view the full document.

View Full Document Right Arrow Icon
Ask a homework question - tutors are online