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

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 Oracle 12c SQL, ISBN 9781305251038 Ch. 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; 2 Oracle 12c SQL, ISBN 9781305251038 Ch. 2 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. 3