Skip to content
Textbook Guides

Solution Manual For Oracle 12c: SQL, 3rd Edition

Solution Manual For Oracle 12c: SQL, 3rd Edition ensures you understand each concept clearly with textbook-based learning.

Preview 16 of 69 pages

See the first 16 pages exactly as they appear in the document.

  1. Page 1
    Read page 1 as text

    Oracle 12c SQL , ISBN: 9781305251038 Ch. 1 1 Chapter 1 Solutions Review Questions 1. What is the purpose of an E - R m odel? To identify the entities about which the database should store data and the relationship s among those entities. 2. What is an entity? An entity is any person, place, or thing having attributes, or characteristics, of interest to the organization. 3. Give an example of three entities that might exist in a database for a medical office and some attributes that would be stored in a table for each entity. Doctor : name, address, Social Security Number, medical ID number; Patient : name, address, Social Security Number, insurance policy information, medical history; Appointment : date, time, patient, doctor. 4. Define a one - to - many relationship. An occurrence of data in one entity can result in zero, one , or many occurrence s of the data in the other entity. Zero ( or no ) related records can occur only in optional relationships. 5. Discuss the problems that can be caused by data redundancy. Can create data anomalies or inconsistencies in the data , making it unreliable. 6. Explain the role of a primary key. The primary key is used to uniquely identify each row in a table. 7. Describe how a foreign key is different from a primary key . A foreign key is used to reference or join data in different tables. In most cases, the foreign key references a primary key in another table. In a one - to - many relationship, the foreign key is stored in the “many” entity. 8. List the steps of the normalization process. First, a primary key is identified and any repeating groups are identified (1NF) . Second, any partial dependencies are eliminated (2NF) . Third , any transitive dependencies are eliminated (3NF) . 9. What type of relationship can ’ t be stored in a database? Why? A many - to - many relationship can ’ t be stored in a database because there would be no way to restructure or rejoin the data correctly. 10. Identify at least three reasons an organization might analyze historical sales data stored in its database. Answers will vary . To determine the necessary inventory levels to support sales fluctuations, to project employee - scheduling requirements , to determine appropriate marketing campaigns based on historic purchasing patterns , and so forth .

  2. Page 2
  3. Page 3
    Read page 3 as text

    Oracle 12c SQL , ISBN: 9781305251038 Ch. 1 2 Multiple Choice 1. d 2. b 3. d 4. a 5. c 6. a 7. a 8. d 9. d 10. c 11. b 12. b 13. a 14. b 15. b 16. d 17. c 18. c 19. a 20. c Hands - O n Assignments 1. Which tables and field s would you access to determine which book title s have been purchased by a customer and when the order shipped ? CUSTOMERS: Customer # ; ORDERS: Order #, Shipdate , Customer #; ORDERITEMS: Order #, ISBN; BOOKS: ISBN, Title 2. How would you determine which orders have not yet been shipped to the customer? Identify all orders that don ’ t have an entry for the date shipped. 3. If management needed to determine which book category generated the most sale s in April 2009 , which tables and fields would they consult to derive this information? ORDERS: Orderdate , Order #; ORDERITEMS: Order #, ISB N, Quantity, Paideach ; BOOKS: ISBN, Category 4. Explain h ow you would determine how much profit was generated from orders placed in April 2009 . Determine the amount of profi t generated by each book on an order item (Paideach - Cost), multiply the profit for each book by the qu antity purchased , and then total the amount of profit generated by all orders placed in April .

  4. Page 4
    Read page 4 as text

    Oracle 12c SQL , ISBN: 9781305251038 Ch. 1 3 5. If a customer inquired about a book written in 2003 by an author named Thompson, which access path (tables and fields) would you need to follow to find the list of books meeting the customer’s request? AUTHOR: Lname, AuthorID; BOOKAUTHOR: AuthorID, ISBN; BOOKS: ISBN, Pubdate. 6. A college needs to track placement test scores for all incoming students. Each student can take a variety of tests , including English and m ath . Some students are required to take placement tests because of previous coursework. Students Tests 7. Every employee in a company is assigned to one department. Every department can contain many employees . Departments Employees 8. A movie megaplex needs to collect and analyze movie attendance data. The company maintains 16 theaters in a single location. Each movie offered can be shown in one or more of the available theaters and is typically schedule d for three to six showings in a day. The movies are rotated through the theaters to ensure that each is shown in one of the stadium - seating theaters at least once. Movies Showings Theaters 9. An online retailer of coffee beans maintains a long list of unique coffee flavors. The company purchases beans from a number of suppliers; however, each specific flavor of coffee is purchased from only a single supplier. Many of the customers are repeat purchasers and typically order at least five flavors of beans i n each order . Suppliers Products Order_items Orders Customers

  5. Page 5
    Read page 5 as text

    Oracle 12c SQL , ISBN: 9781305251038 Ch. 1 4 10. Data for an information technology conference needs to be collected. The conference has a variety of sessions scheduled over a two - day period. All attendee s must register for the sessions they plan to attend. Some speakers are presenting only one session , whereas others are handling multiple sessions. Each session has only one speaker . Speakers Sessions Registrations Attendees A dvanced Challenge Results of the normalization process will vary , depending on the assumptions made by the student. Unnormalized : first name, last name, billing address, quantity, retail price, shipping address, order date, ship date 1NF: CUSTOMERS: customer # , first name, last name, billing address ORDERS: order # , shipping address, quantity , retail price , order date, ship date 2NF: CUSTOMERS: customer # , first name, last name, billing address ORDERS: order # , shipping address, order date, ship date ORDERITEMS: order # , item# , quantity, retail price, ISBN 3NF: CUSTOMERS: customer # , first name, last name, billing address ORDERS: order # , shipping address, order date, ship date

  6. Page 6
    Read page 6 as text

    Oracle 12c SQL , ISBN: 9781305251038 Ch. 1 5 ORDERITEMS: order # , item# , quantity, ISBN BOOKS: ISBN, retail price Case Study: City Jail The appearance of the E - R m odel will vary depending on the notations or modeling software students are using. An example is shown on the next page . Additional e ntities and/or attributes: Answers will vary greatly. A Jails entity is an example of a possible additional entit y . Image items , such as a criminal photo and fingerprints , are examples of additional attributes that might be required.

  7. Page 7
    Read page 7 as text

    Oracle 12c SQL , ISBN: 9781305251038 Ch. 1 6 Criminal_ ID Last First Street City State Zip Phone V_status P_status Criminals Alias_ ID Criminal_ ID Alias City Jail Database E - R Model Aliases Crime_ ID Criminal_ ID Classification Date_charged Status Hearing_date Appeal_cut_date Crimes Appeal _ ID Crim e _ ID Filing_date Hearing_date Status Appeals Officer _ ID Last First Precinct Badge Phone Status Officers Crim e _ ID Officer_ ID Sentence _ ID Criminal_ ID Type Prob_ ID Start_date End_date Violations Crime_officers Sentences Crime_charges Charge_ ID Crime_ ID Crime_code Charge_status Fine_amount Court_fee Amount_paid Pay_due_date Prob_ ID Last First Street City State Zip Phone Email Status Prob_Officers Crim e_code Code_description Crime_codes

  8. Page 8
    Read page 8 as text

    Oracle 12c SQL , ISBN 9781305251038 Ch. 2 1 Chapter 2 Solutions Review Questions 1. What is a data dictionary? A collection of objects the DBMS manages to maintain information about the database , such as table names, column names , and column data types. This information is often referred as metadata. 2. What are the two required clauses for a SELECT statement? SELECT and FROM 3. What is the purpose of the SELECT statement? It ’ s used to retrieve data from database tables. 4. What does the use of an asterisk (*) in the SELECT clause of a SELECT statement represent? All columns in the referenced table 5. What is the purpose of a column alias? Provides another name for a column that’s displayed as the column heading in the output 6. How do you indicate that a column alias should be used? Include the AS keyword followed by the alias, or list the alias immediately after the column name without a separating comma. 7. When is it appropriate to use a column alias? To provide a more descriptive column heading 8. What are the guidelines to keep in mind when using a column alias? If the column alias includes a blank space or special symbols, or should retain the specified letter case, it must be enclosed in quotation marks. 9. How can you concatenate columns in a query? Separate the column names with two vertical bars ( || ) rather than a comma. 10. What is a NULL value? A NULL value indicates an absence of value. If no value was placed in a field of a row, the field value is empty or NULL. Multiple Choice 1. c 2. d 3. d 4. b 5. b 6. c 7. c 8. c 9. d 10. d 11. a 12. c

  9. Page 9
    Read page 9 as text

    Oracle 12c SQL , ISBN 9781305251038 Ch. 2 2 13. d 14. d 15. c 16. a 17. a 18. b 19. d 20. b Hands - On Assignments 1. SELECT * FROM books; 2. SELECT title FROM books; 3. SELECT title, pubdate "Publication Date" FROM books; 4. SELECT customer#, city, state FROM customers; 5 . SELECT name, contact "Contact Person", phone FROM publisher; 6 . SELECT DISTINCT category FROM books; or SELECT UNIQUE category FROM books; 7 . SELECT DISTINCT customer# FROM orders; or SELECT UNIQUE customer# FROM orders;

  10. Page 10
    Read page 10 as text

    Oracle 12c SQL , ISBN 9781305251038 Ch. 2 3 8 . SELECT category, title FROM books; 9 . SELECT lname || ', ' || fname FROM author; 10 . SELECT order#, item#, isbn, quantity, paideach, quantity*paideach "Item Total" FROM orderitems; Advanced Challenge 1. SELECT lastname || ', ' || firstname "Name", address, city || ', ' || state "Location", zip FROM customers; 2. SELECT title, (retail - cost)/cost*100 "Profit %" FROM books; Case Study: City Jail Resumes in Chapter 3.

  11. Page 11
    Read page 11 as text

    Oracle 12c SQL , ISBN: 9781305251038 Ch. 3 1 Chapter 3 Solutions Review Questions 1. Which command is used to create a table based on data already contained in an existing table? CREATE TABLE ... AS command with a subquery 2. List four datatypes supported by Oracle 1 2c , and provide an example of data that could be stored by each datatype. DATE : 20 - JAN - 0 9, VARCHAR2 : HELLO, NUMBER : 5.23, CHAR : VA 3. What guidelines should you follow when naming tables and columns in Oracle 1 1 g ? A maximum of 30 characters is allowed, the first character must be a letter, no blank spaces or special symbols other than the underscore and dollar sign can be included , and reserve d words can ’ t be used as a table or column name. 4. What is the difference between dropping a column and setting a column as unused? When dropped, the storage space is released immediately. When set as unused, the storage space isn ’ t released until later. 5. How many columns can be dropped in one ALTER TABLE command? Only one at a time 6. What happens to the existing rows of a table if the DEFAULT value of a column is changed? Nothing ; they aren ’ t affected . 7. Explain the difference between truncating a table and deleting a table. Truncating a table remove s all data but retain s the table structure ; deleting (dropping) a table remove s data and the table structure from the database. 8. If you add a new column to an existing table, where does the column appear in relati on to existing columns? It appear s as the last column in the table. 9. What happens if you try to decrease the scale or precision of a NUMBER column to a value less than the data already stored in the field? You can ’ t change the scale or precision of a NUMBER column contain ing data. 10. Are a table and the data contained in the table erased from the system permanently if a DROP TABLE command is issued on the table? The table is erased permanently only if the PURGE option is used in the DROP TABLE command. Otherwise, the table is moved to the recycle bin. Multiple Choice 1. c 2. c 3. a 4. b 5. d 6. d

  12. Page 12
    Read page 12 as text

    Oracle 12c SQL , ISBN: 9781305251038 Ch. 3 2 7. b 8. d 9. b 10. a 11. d 12. a 13. a 14. b 15. d 16. d 17. b 18. d 19. c 20. b Hands - On Assignments 1 . CREATE TABLE category (catcode VARCHAR2(2), catdesc VARCHAR2(10)) ; 2 . CREATE TABLE employees ( emp# NUMBER( 5 ), lastname VARCHAR2( 15 ) , firstname VARCHAR2(10), job_class VARCHAR2(4) ) ; 3 . ALTER TABLE employees ADD ( empd ate DATE DEFAULT SYSDATE , enddate DATE ) ; 4 . ALTER TABLE employees MODIFY job_class VARCHAR2(2) ; 5 . ALTER TABLE employees DROP column enddate; 6 . RENAME employees TO jl_emps ;

  13. Page 13
    Read page 13 as text

    Oracle 12c SQL , ISBN: 9781305251038 Ch. 3 3 7 . CREATE TABLE book_pricing (id, cost, retail, category) AS (SELECT isbn, cost, retail, category FROM books); 8 . ALTER TABLE book_pricing SET UNUSED ( category ) ; SELECT * FROM book_pricing; 9 . TRUNCATE TABLE book_pricing ; SELECT * FROM book_pricing; 10 . DROP TABLE book_pricing PURGE ; DROP TABLE jl_emps; FLASHBACK TABLE jl_emps TO BEFORE DROP; SELECT * FROM jl_emps; Advanced Challenge ALTER TABLE acctmanager ADD (comm _id NUMBER(2) DEFAULT 10, Ben_id NUMBER(2)); CREATE TABLE commrate ( comm_id NUMBER(2), comm_rank VARCHAR2(15), rate NUMBER(2,2)); CREATE TABLE benefits (ben_id NUMBER(2), ben_plan CHAR(1), ben_provider NUMBER(3), active CHAR(1)); Case Study: City Jail

  14. Page 14
    Read page 14 as text

    Oracle 12c SQL , ISBN: 9781305251038 Ch. 3 4 Notes: In Section A, students need to develop CREATE TABLE statements for each table listed. The listing indicates DEFAULT column values whe n applicable. The listing “Coding key for selected columns” is provided only to help student s visualize what type of data will be entered in selected code columns. This information will be a valuable reference for performing queries in later chapters. In Section B, students need to use the ALTER TABLE command to make the requested table modifications. Warning : If you modify this assignment , consider how it will affect case study assignments in subsequent chapters. Recall that the City Jail Case Study is a cumulative case used throughout the text book . Section A CREATE TABLE aliases (alias_id NUMBER(6), criminal_id NUMBER(6), alias VARCHAR2(10)); CREATE TABLE criminals (criminal_id NUMBER(6), last VARCHAR2(15), first VARCHAR2(10), street VARCHAR2(30), city VARCHAR2(20), state CHAR(2), zip CHAR(5), phone CHAR(10), v_status CHAR(1) DEFAULT 'N', p_status CHAR(1) DEFAULT 'N' ); CREATE TABLE crimes (crime_id NUMBER(9), criminal_id NUMBER(6), classification CHAR(1), date_charged DATE, status CHAR(2), hearing_date DATE, appeal_cut_date DATE); CREATE TABLE sentences (sentence_id NUMBER(6), criminal_id NUMBER(6), type CHAR(1), prob_id NUMBER(5),

  15. Page 15
    Read page 15 as text

    Oracle 12c SQL , ISBN: 9781305251038 Ch. 3 5 start_date DATE, end_date DATE, violations NUMBER(3)); CREATE TABLE prob_officers (prob_id NUMBER(5), last VARCHAR2(15), first VARCHAR2(10), street VARCHAR2(30), city VARCHAR2(20), state CHAR(2), zip CHAR(5), phone CHAR(10), email VARCHAR2(30), status CHAR(1) DEFAULT 'A' ); CREATE TABLE crime_charges (charge_id NUMBER(10), crime_id NUMBER(9), crime_code NUMBER(3), charge_status CHAR(2), fine_amount NUMBER(7,2), court_fee NUMBER(7,2), amount_paid NUMBER(7,2), pay_due_date DATE); CREATE TABLE crime_officers (crime_id NUMBER(9), officer_id NUMBER(8)); CREATE TABLE officers (officer_id NUMBER(8), last VARCHAR2(15), first VARCHAR2(10), precinct CHAR(4), badge VARCHAR2(14), phone CHAR(10), status CHAR(1) DEFAULT 'A' ); CREATE TABLE appeals (appeal_id NUMBER(5), crime_id NUMBER(9), filing_date DATE, hearing_date DATE, status CHAR(1) DEFAULT 'P' );

  16. Page 16
    Read page 16 as text

    Oracle 12c SQL , ISBN: 9781305251038 Ch. 3 6 CREATE TABLE crime_codes (crime_code NUMBER(3), code_description VARCHAR2(30)); Section B ALTER TABLE crimes MODIFY (classification DEFAULT 'U'); ALTER TABLE crimes ADD (date_recorded DATE DEFAULT SYSDATE); ALTER TABLE prob_officers ADD (pager# CHAR(10)); ALTER TABLE aliases MODIFY (alias VARCHAR2(20));

53 more pages in the full document

Unlock it once and it stays in your library, ready to chat with or turn into flashcards.

Unlock the full document

More Information Technology study materials

See all

Study this document with CramX

Once it is in your library, every tool can work from it.