CH-9-13 Joining Data from Multiple Tables – Oracle 12c: SQL : Book Guide

This Document Contains Chapters 9 to 13 Chapter 9 Solutions Review Questions 1. Explain the difference between an inner join and an outer join. The results of an inner join display only rows that have matches in join operations; outer joins include rows that don’t have matches in the join operations. 2. How many rows are returned in a Cartesian join between one table having 5 records and a second table having 10 records? 50 rows 3. Describe problems you might encounter when using the NATURAL JOIN keywords to perform join operations. A NATURAL JOIN is based on commonly named columns. As tables are modified, commonly named columns could be added but don’t actually represent the same data and are then used incorrectly in a join operation. 4. Why are the NATURAL JOIN keywords not an option for producing a self-join? (Hint: Think about what happens if you use a table alias with the NATURAL JOIN keywords.) To produce a self-join, a copy of the table is created but assigned a table alias to “trick” Oracle 11g into “thinking” it’s working with two tables. Table aliases can’t be assigned when using the NATURAL JOIN keywords. 5. What is the purpose of a column qualifier? When are you required to use one? It’s used to identify the table containing the referenced column. A column qualifier must be used when more than one table contains the referenced column to avoid an ambiguity error. 6. In an outer join query, the outer join operator (+) is placed after which table? It’s placed after the deficient table (one that doesn’t have a row that matches a row from the other table that needs to be displayed). 7. What’s the difference between the UNION and UNION ALL set operators? The UNION set operator eliminates duplicate values in the results by default. The ALL option forces duplicates to remain in the results. 8. How many join conditions are needed for a query that joins five tables? 4 9. What’s the difference between an equality and a non-equality join? An equality join is an inner join based on equivalent data existing in a common column; a non-equality join uses anything other than the equal sign to establish a relationship among the tables. Non-equality joins typically involve relating a data value to a range of values. 10. What are the differences between using the JOIN … USING and JOIN … ON approaches for joining tables? The JOIN … USING approach joins tables based on the specified common columns, which must have the same name; the JOIN … ON approach joins tables based on a stated condition. The JOIN … ON approach must be used if the common columns have different names. Multiple Choice This Document Contains Chapters 9 to 13 1. d 2. c 3. a 4. c 5. d 6. c 7. b 8. e 9. c 10. e 11. b 12. a 13. e 14. e 15. a 16. b 17. b 18. b 19. b 20. c Hands-On Assignments 1. a) SELECT b.title, p.contact, p.phone FROM books b, publisher p WHERE b.pubid = p.pubid; b) SELECT b.title, p.contact, p.phone FROM books b JOIN publisher p USING (pubid); 2. a) SELECT c.firstname, c.lastname, o.order# FROM customers c, orders o WHERE c.customer# = o.customer# AND o.shipdate IS NULL ORDER BY o.orderdate; b) SELECT c.firstname, c.lastname, o.order# FROM customers c JOIN orders o USING (customer#) WHERE o.shipdate IS NULL ORDER BY o.orderdate; 3. a) SELECT DISTINCT c.lastname, c.customer# FROM books b, orders o, orderitems i, customers c WHERE c.customer# = o.customer# AND o.order# = i.order# AND i.isbn = b.isbn AND c.state = 'FL' AND b.category = 'COMPUTER'; b) SELECT DISTINCT c.lastname, customer# FROM books b JOIN orderitems USING (isbn) JOIN orders USING (order#) JOIN customers c USING (customer#) WHERE c.state = 'FL' AND b.category = 'COMPUTER'; 4. a) SELECT DISTINCT b.title FROM customers c, orders o, orderitems i, books b WHERE c.customer# = o.customer# AND o.order# = i.order# AND i.isbn = b.isbn AND c.firstname = 'JAKE' AND c.lastname = 'LUCAS'; b) SELECT DISTINCT b.title FROM customers c JOIN orders USING (customer#) JOIN orderitems USING (order#) JOIN books b USING (isbn) WHERE c.firstname = 'JAKE' AND c.lastname = 'LUCAS'; 5. a) SELECT b.title, i.paideach-cost FROM customers c, orders o, orderitems i, books b WHERE c.customer# = o.customer# AND o.order# = i.order# AND i.isbn = b.isbn AND c.firstname = 'JAKE' AND c.lastname = 'LUCAS' ORDER BY o.orderdate, i.paideach-b.cost DESC; b) SELECT b.title, i.paideach-b.cost FROM customers c JOIN orders o USING (customer#) JOIN orderitems i USING (order#) JOIN books b USING (isbn) WHERE c.firstname = 'JAKE' AND c.lastname = 'LUCAS' ORDER BY o.orderdate, i.paideach-b.cost DESC; 6. a) SELECT b.title FROM books b, bookauthor ba, author a WHERE b.isbn = ba.isbn AND ba.authorid = a.authorid AND a.lname = 'ADAMS'; b) SELECT b.title FROM books b JOIN bookauthor USING (isbn) JOIN author a USING (authorid) WHERE a.lname = 'ADAMS'; 7. a) SELECT p.gift FROM books b, promotion p WHERE b.retail BETWEEN p.minretail AND p.maxretail AND b.title = 'SHORTEST POEMS'; b) SELECT p.gift FROM books b JOIN promotion p ON b.retail BETWEEN p.minretail AND p.maxretail WHERE b.title = 'SHORTEST POEMS'; 8. a) SELECT a.lname, a.fname, b.title FROM books b, orders o, orderitems i, customers c, bookauthor t, author a WHERE c.customer# = o.customer# AND o.order# = i.order# AND i.isbn = b.isbn AND b.isbn = t.isbn AND t.authorid = a.authorid AND c.firstname = 'BECCA' AND c.lastname = 'NELSON'; b) SELECT a.lname, a.fname, b.title FROM customers c JOIN orders USING (customer#) JOIN orderitems USING (order#) JOIN books b USING (isbn) JOIN bookauthor USING (isbn) JOIN author a USING (authorid) WHERE c.firstname = 'BECCA' AND c.lastname = 'NELSON'; 9. a) SELECT b.title, o.order#, c.state FROM books b, orders o, orderitems i, customers c WHERE c.customer# (+) = o.customer# AND o.order# (+) = i.order# AND i.isbn (+) = b.isbn; b) SELECT b.title, order#, c.state FROM books b LEFT OUTER JOIN orderitems i USING (isbn) LEFT OUTER JOIN orders USING (order#) LEFT OUTER JOIN customers c USING (customer#); 10. a) SELECT e.fname || ' ' || e.lname "Employee Name", e.job, m.fname || ' ' || m.lname "Manager Name" FROM employees e, employees m WHERE e.mgr = m.empno (+) ORDER BY "Manager Name"; b) SELECT e.fname || ' ' || e.lname "Employee Name", e.job, m.fname || ' ' || m.lname "Manager Name" FROM employees e LEFT OUTER JOIN employees m ON e.mgr = m.empno ORDER BY "Manager Name"; Advanced Challenge SELECT b.title, pu.name, b.retail-b.cost, p.gift FROM books b JOIN publisher pu USING (pubid) JOIN promotion p ON b.retail BETWEEN p.minretail AND p.maxretail ORDER BY b.retail-b.cost; SELECT isbn FROM books MINUS SELECT isbn FROM orderitems; Case Study: City Jail 1. SELECT c.criminal_ID, c.last, c.first, cc.crime_code, cc.fine_amount FROM criminals c, crimes cr, crime_charges cc WHERE c.criminal_ID = cr.criminal_ID AND cr.crime_ID = cc.crime_ID; SELECT criminal_ID, c.last, c.first, cc.crime_code, cc.fine_amount FROM criminals c JOIN crimes cr USING (criminal_ID) JOIN crime_charges cc USING (crime_ID); 2. SELECT c.criminal_ID, c.last, c.first, cr.classification, cr.date_charged, a.filing_date, a.status FROM criminals c, crimes cr, appeals a WHERE c.criminal_ID = cr.criminal_ID AND cr.crime_ID = a.crime_ID (+); SELECT criminal_ID, c.last, c.first, cr.classification, cr.date_charged, a.filing_date, a.status FROM criminals c JOIN crimes cr USING (criminal_ID) LEFT JOIN appeals a USING (crime_ID); 3. SELECT c.criminal_ID, c.last, c.first, cr.classification, cr.date_charged, cc.crime_code, cc.fine_amount FROM criminals c, crimes cr, crime_charges cc WHERE c.criminal_ID = cr.criminal_ID AND cr.crime_ID = cc.crime_ID AND cr.classification = 'O' ORDER BY c.criminal_ID, cr.date_charged; SELECT criminal_ID, c.last, c.first, cr.classification, cr.date_charged, cc.crime_code, cc.fine_amount FROM criminals c JOIN crimes cr USING (criminal_ID) JOIN crime_charges cc USING (crime_ID) WHERE classification = 'O' ORDER BY criminal_ID, cr.date_charged; 4. SELECT c.criminal_ID, c.last, c.first, c.v_status, c.p_status, a.alias FROM criminals c, aliases a WHERE c.criminal_ID = a.criminal_ID (+); SELECT criminal_ID, c.last, c.first, c.v_status, c.p_status, a.alias FROM criminals c LEFT JOIN aliases a USING (criminal_ID); 5. SELECT c.last, c.first, s.start_date, s.end_date, p.con_freq FROM criminals c, sentences s, prob_contact p WHERE c.criminal_ID = s.criminal_ID AND s.end_date - s.start_date BETWEEN p.low_amt AND p.high_amt AND s.type = 'P' ORDER BY c.last, s.start_date; SELECT c.last, c.first, s.start_date, s.end_date, p.con_freq FROM criminals c JOIN sentences s USING (criminal_ID) JOIN prob_contact p ON s.end_date-s.start_date BETWEEN p.low_amt AND p.high_amt WHERE s.type = 'P' ORDER BY c.last, s.start_date; 6. SELECT a.last "Officer", b.last "Supervisor" FROM prob_officers a, prob_officers b WHERE a.mgr_ID = b.prob_ID ORDER BY a.last; SELECT a.last "Officer", b.last "Supervisor" FROM prob_officers a JOIN prob_officers b ON a.mgr_ID = b.prob_ID ORDER BY a.last; Chapter 10 Solutions Review Questions 1. Why are the functions in this chapter referred to as “single-row” functions? These functions return one row of results for each record processed. 2. What’s the difference between the NVL and NVL2 functions? The NVL function is used to substitute a value for a NULL; the NVL2 function allows taking different actions, depending on the existence of a NULL value. 3. What’s the difference between the TO_CHAR and TO_DATE functions when working with date values? The TO_CHAR function formats a date value for display, and the TO_DATE function changes a user-entered date value to the Oracle format so that it can be entered in the database. 4. How is the TRUNC function different from the ROUND function? The ROUND function rounds data after the specified position, and the TRUNC function drops any data after the specified position 5. What functions can be used to search character strings for specific patterns of data? The REPLACE, TRANSLATE, and REGEXP_LIKE functions involve searching a string value. The REGEXP_LIKE function enables searching for patterns, but the others allow searching only for a specific character string value. 6. What’s the difference between using the CONCAT function and the concatenation operator (| |) in a SELECT clause? To concatenate more than two columns or string literals, the CONCAT function must be nested inside other CONCAT functions. The concatenation operator can be used in a series to concatenate several columns or string literals. 7. Which functions can be used to convert the letter case of character values? The LOWER, UPPER, and INITCAP functions 8. Describe a situation that calls for using the DECODE function. Answers will vary. Example: If a customer list by region is needed and region is assigned to customers based on the state they live in, DECODE could be used to identify the region based on the state value. 9. What format model should you use to display the date 25-DEC-09 as Dec. 25? 'Mon. DD' 10. Why does the function NVL(shipdate, 'Not Shipped') return an error message? The NVL function doesn’t allow substituting text for a date, and the Shipdate column is defined as a DATE datatype. Multiple Choice 1. c 2. c 3. b 4. b 5. f 6. a 7. d 8. d 9. a 10. b 11. a 12. d 13. c 14. a 15. c 16. c 17. d 18. c 19. b 20. c Hands-on Assignments 1. SELECT INITCAP(firstname), INITCAP(lastname) FROM customers; 2. SELECT firstname, lastname, NVL2(referred, 'REFERRED', 'NOT REFERRED') FROM customers; 3. SELECT title, TO_CHAR(quantity*(paideach-cost), '$999.99') FROM books JOIN orderitems USING (isbn) WHERE order# = 1002; 4. SELECT title, ROUND((retail-cost)/cost *100, 0)||'%' FROM books; 5. SELECT TO_CHAR(CURRENT_DATE, 'DAY, HH:MI:SS') FROM dual; 6. SELECT title, LPAD(cost, 12, '*') FROM books; 7. SELECT DISTINCT LENGTH(isbn) FROM books; 8. SELECT title, pubdate, SYSDATE, TRUNC(MONTHS_BETWEEN(SYSDATE, pubdate),0) Age FROM books; 9. SELECT NEXT_DAY(SYSDATE, 'WEDNESDAY') FROM dual; 10. SELECT customer#, SUBSTR(zip, 3, 2), INSTR(customer#, 3) FROM customers; Advanced Challenge SELECT title "Title", category "Category", retail "Current Price", ROUND(DECODE(category, 'COMPUTER', retail*1.1, 'FITNESS', retail*1.15, 'SELF HELP', retail*1.25, retail*1.03), 2) "Revised Price" FROM books ORDER BY category, title; Case Study: City Jail 1. SELECT crime_id, classification, date_charged, hearing_date, hearing_date-date_charged Days FROM crimes WHERE hearing_date-date_charged > 14; 2. SELECT last, DECODE(SUBSTR(precinct, 2, 1), 'A', 'Shady Grove', 'B', 'Center City', 'C', 'Bay Landing', 'Not assigned') FROM officers WHERE status = 'A'; Note: Could use LIKE rather than SUBSTR. 3. SELECT criminal_id, UPPER(c.first||' '||c.last) Name, s.sentence_id, TO_CHAR(s.start_date, 'Month DD, YYYY') "Start Date", ROUND(MONTHS_BETWEEN(s.end_date, s.start_date)) "# Months" FROM criminals c JOIN sentences s USING (criminal_id); 4. SELECT c.last, c.first, cc.charge_id, TO_CHAR(cc.fine_amount+cc.court_fee,'$9,999.99') total, TO_CHAR(NVL(cc.amount_paid, 0),'$999.99') Paid, TO_CHAR((cc.fine_amount+cc.court_fee)-NVL(cc.amount_paid, 0),'$9,999.99') Owed, cc.pay_due_date FROM criminals c JOIN crimes cr USING (criminal_id) JOIN crime_charges cc USING (crime_id) WHERE (cc.fine_amount+cc.court_fee)-NVL(cc.amount_paid, 0) > 0; 5. SELECT c.last, c.first, s.start_date, ADD_MONTHS(s.start_date, 2) Review FROM criminals c JOIN sentences s USING (criminal_id) WHERE s.type = 'P' AND MONTHS_BETWEEN(s.end_date, s.start_date) > 2; 6. INSERT INTO appeals (appeal_id, crime_id, filing_date, hearing_date) VALUES (appeals_id_seq.NEXTVAL, &crime_id, TO_DATE('&filing_date', 'MM DD YYYY'), TO_DATE('&hearing_date', 'MM DD YYYY') ); Chapter 11 Solutions Review Questions 1. Explain the difference between single-row and group functions. A single-row function returns one result for each row processed while a group function returns one result for each group of data processed. 2. Which group function can be used to perform a count that includes NULL values? COUNT(*) 3. Which clause can be used to restrict or filter the groups returned by a query based on a group function? HAVING 4. Under what circumstances must you include a GROUP BY clause in a query? If a single column is listed in a SELECT clause along with a group function 5. In which clause should you include the condition "pubid=4" to restrict the rows processed by a query? WHERE 6. In which clause should you include the condition MAX(cost) > 39 to restrict groups displayed in the query results? HAVING 7. What’s the basic difference between the ROLLUP and CUBE extensions of the GROUP BY clause? The CUBE extension performs aggregations for all possible combinations of columns, and the ROLLUP extension simply calculates cumulative subtotals for the columns. 8. What’s the maximum depth allowed when nesting group functions? 2 9. In what order are output results displayed if a SELECT statement contains a GROUP BY clause and no ORDER BY clause? In ascending order, based on the column specified in the GROUP BY clause 10. Which clause is used to restrict the records retrieved from a table? Which clause restricts groups displayed in the query results? Records: WHERE clause; groups: HAVING clause Multiple Choice 1. c 2. a 3. d 4. a 5. a 6. b 7. d 8. e 9. d 10. a 11. b 12. a 13. b 14. b 15. d 16. c 17. c 18. b 19. c 20. d Hands-on Assignments 1. SELECT COUNT(*) FROM books WHERE category = 'COOKING'; 2. SELECT COUNT(*) FROM books WHERE retail > 30; 3. SELECT MAX(pubdate) FROM books; 4. SELECT SUM((retail-cost)*quantity) FROM books, orders, orderitems WHERE books.isbn = orderitems.isbn AND orderitems.order# = orders.order# AND customer# = 1017; 5. SELECT MIN(retail) FROM books WHERE category = 'COMPUTER'; 6. SELECT AVG(SUM((retail-cost)*quantity)) FROM books, orders, orderitems WHERE books.isbn = orderitems.isbn AND orderitems.order# = orders.order# GROUP BY orders.order#; 7. SELECT customer#, COUNT(*) FROM orders GROUP BY customer#; 8. SELECT name, category, AVG(retail) FROM books JOIN publisher USING (pubid) WHERE category IN('COMPUTER', 'CHILDREN') GROUP BY name, category HAVING AVG(retail) > 50; 9. SELECT DISTINCT firstname, lastname FROM customers, books, orders, orderitems WHERE customers.customer# = orders.customer# AND orders.order# = orderitems.order# AND orderitems.ISBN = books.ISBN AND (state = 'GA' OR state = 'FL') GROUP BY orders.order#, firstname, lastname HAVING SUM(retail*quantity) > 80; 10. SELECT MAX(retail) FROM books JOIN bookauthor USING (isbn) JOIN author USING(authorid) WHERE lname = 'WHITE' AND fname = 'LISA'; Advanced Challenge SELECT title, COUNT(orderitems.isbn) FROM books, orders, orderitems WHERE (retail-cost)/cost 1; 6. SELECT o.precinct, COUNT(cc.charge_id) FROM officers o JOIN crime_officers USING (officer_id) JOIN crimes USING (crime_id) JOIN crime_charges cc USING (crime_id) WHERE cc.charge_status = 'GL' GROUP BY precinct HAVING COUNT(cc.charge_id) >= 7; 7. SELECT cr.classification, SUM(cc.fine_amount+cc.court_fee) fines, SUM(cc.amount_paid) Paid, SUM(cc.fine_amount+cc.court_fee-NVL(cc.amount_paid,0)) Owed FROM crimes cr JOIN crime_charges cc USING (crime_id) GROUP BY cr.classification; 8. SELECT cr.classification, cc.charge_status, COUNT(*) FROM crimes cr JOIN crime_charges cc USING (crime_id) GROUP BY GROUPING SETS((cr.classification, cc.charge_status), () ); 9. SELECT cr.classification, cc.charge_status, COUNT(*) FROM crimes cr JOIN crime_charges cc USING (crime_id) GROUP BY GROUPING SETS(cr.classification, cc.charge_status, (cr.classification, cc.charge_status), () ); SELECT cr.classification, cc.charge_status, COUNT(*) FROM crimes cr JOIN crime_charges cc USING (crime_id) GROUP BY GROUPING SETS (ROLLUP(cr.classification, cc.charge_status), ROLLUP(cc.charge_status)); 10. SELECT cr.classification, cc.charge_status, COUNT(*) FROM crimes cr JOIN crime_charges cc USING (crime_id) GROUP BY GROUPING SETS(cr.classification,(cr.classification, cc.charge_status), () ); SELECT cr.classification, cc.charge_status, COUNT(*) FROM crimes cr JOIN crime_charges cc USING (crime_id) GROUP BY ROLLUP(cr.classification, cc.charge_status); Chapter 12 Solutions Review Questions 1. What’s the difference between a single-row subquery and a multiple-row subquery? A single-row subquery can return only one row of results; a multiple-row subquery can return several rows of results. 2. What comparison operators are required for multiple-row subqueries? IN, ALL, or ANY 3. What happens if a single-row subquery returns more than one row of results? An error message is returned. 4. Which SQL clause(s) can’t be used in a subquery in the WHERE or HAVING clauses? ORDER BY clause 5. If a subquery is used in the FROM clause of a query, how are the subquery’s results referenced in other clauses of the query? By using a table alias assigned to the subquery’s results 6. Why might a MERGE statement be used? Answers will vary. Example: Replicate data for reporting purposes to separate transaction and reporting processing to maintain efficiency of transactional processing. 7. How can Oracle 11g determine whether clauses of a SELECT statement belong to an outer query or a subquery? The subquery is enclosed in parentheses. 8. When should a subquery be nested in a HAVING clause? When the subquery results are compared with a group function or aggregate data 9. What’s the difference between correlated and uncorrelated subqueries? When an uncorrelated subquery is processed, the inner query is executed once, and then the outer query is processed, using the inner query’s results. A correlated subquery references a column from the outer query, so the subquery is executed for each row in the outer query. 10. What type of situation requires using a subquery? When a search is based on an unknown value that can be determined by querying the database Multiple Choice 1. d 2. c 3. c 4. d 5. a 6. c 7. a 8. c 9. d 10. a 11. a 12. b 13. c 14. b 15. b 16. d 17. c 18. b 19. b 20. a Hands-On Assignments 1. SELECT title, retail FROM books WHERE retail < (SELECT AVG(retail) FROM books); 2. SELECT a.title, b.category, a.cost FROM books a, (SELECT category, AVG(cost) averagecost FROM books GROUP BY category) b WHERE a.category = b.category AND a.cost (SELECT SUM(oi.quantity*oi.paideach) FROM orderitems oi, books b WHERE oi.isbn = b.isbn AND oi.order# = 1008); 5. SELECT lname, fname FROM bookauthor JOIN author USING (authorid) WHERE isbn IN (SELECT isbn FROM orderitems GROUP BY isbn HAVING SUM(quantity) = (SELECT MAX(COUNT(*)) FROM orderitems GROUP BY isbn) ); Note: Could have more than one book matching the highest sales 6. SELECT title FROM books WHERE category IN (SELECT DISTINCT category FROM books JOIN orderitems USING (isbn) JOIN orders USING(order#) WHERE customer# = 1007) AND isbn NOT IN (SELECT isbn FROM orders JOIN orderitems USING (order#) WHERE customer# = 1007); 7. SELECT shipcity, shipstate FROM orders WHERE shipdate-orderdate = (SELECT MAX(shipdate-orderdate) FROM orders); 8. SELECT customer# FROM customers JOIN orders USING (customer#) JOIN orderitems USING (order#) JOIN books USING (isbn) WHERE retail = (SELECT MIN(retail) FROM books); 9. SELECT COUNT(DISTINCT customer#) FROM orders JOIN orderitems USING (order#) WHERE isbn IN (SELECT isbn FROM orderitems JOIN bookauthor USING (isbn) JOIN author USING (authorid) WHERE lname = 'AUSTIN' AND fname = 'JAMES'); 10. SELECT title FROM books WHERE pubid = (SELECT pubid FROM books WHERE title = 'THE WOK WAY TO COOK'); Advanced Challenge 1. SELECT SUM(quantity*paideach)*.015 FROM orderitems; 2. SELECT SUM(quantity*paideach)*.04 FROM orderitems WHERE order# IN (SELECT order# FROM orderitems GROUP BY order# HAVING SUM(quantity*paideach) > (SELECT AVG(SUM(quantity*paideach)) FROM orderitems GROUP BY order#) ); Case Study: City Jail 1. SELECT last, first FROM officers JOIN crime_officers USING (officer_id) JOIN crimes USING (crime_id) GROUP BY last, first HAVING COUNT(crime_id) > (SELECT AVG(COUNT(crime_id)) FROM crimes JOIN crime_officers USING (crime_id) GROUP BY officer_id); 2. SELECT first, last FROM criminals JOIN crimes USING (criminal_id) WHERE v_status = 'N' GROUP BY first, last HAVING COUNT(crime_id) < (SELECT AVG(COUNT(crime_id)) FROM crimes GROUP BY criminal_id); 3. SELECT * FROM appeals WHERE hearing_date-filing_date < (SELECT AVG(hearing_date-filing_date) FROM appeals); 4. SELECT first, last FROM prob_officers JOIN sentences USING (prob_id) GROUP BY first, last HAVING COUNT(criminal_id) (SELECT AVG(fine_amount) FROM crime_charges) AND amount_paid < (SELECT AVG(amount_paid) FROM crime_charges); 7. SELECT DISTINCT first, last FROM criminals JOIN crimes USING (criminal_id) JOIN crime_charges USING (crime_id) WHERE crime_code IN (SELECT crime_code FROM crime_charges WHERE crime_id = 10089); 8. SELECT first, last FROM criminals WHERE EXISTS (SELECT sentence_id FROM sentences WHERE criminals.criminal_id = sentences.criminal_id AND prob_id IS NOT NULL); 9. SELECT first, last FROM officers JOIN crime_officers USING (officer_id) GROUP BY first, last HAVING COUNT(crime_id) = (SELECT MAX(COUNT(crime_id)) FROM crime_officers GROUP BY crime_id); 10. MERGE INTO criminals_dw a USING criminals b ON (a.criminal_id = b.criminal_id) WHEN MATCHED THEN UPDATE SET a.last = b.last, a.first=b.first, a.street=b.street, a.city=b.city, a.state=b.state, a.phone=b.phone, a.v_status=b.v_status, a.p_status=b.p_status WHEN NOT MATCHED THEN INSERT (criminal_id, last, first, street, city, state, zip, phone, v_status, p_status) VALUES (b.criminal_id, b.last, b.first, b.street, b.city, b.state, b.zip, b.phone, b.v_status, b.p_status); Chapter 13 Solutions Review Questions 1. How is a simple view different from a complex view? A simple view can’t contain data from multiple tables, grouped data, group functions, DISTINCT or ROWNUM, or expressions, as a complex view can. 2. Under what circumstances is a DML operation not allowed on a simple view? If it violates a constraint on the underlying table, if the view was created with the WITH READ ONLY option, or if the DML operation violates the WITH CHECK OPTION constraint 3. When should the FORCE keyword be used in the CREATE VIEW command? When creating a view based on a table that’s currently unavailable 4. What’s the purpose of the WITH CHECK OPTION constraint? Prevents DML operations that would make a row inaccessible to the view 5. List the guidelines for DML operations on complex views. • DML operations that violate a constraint aren’t permitted. • DML operations aren’t permitted on views that include group functions, a GROUP BY clause, the ROWNUM pseudocolumn, or the DISTINCT keyword. • DML operations aren’t permitted on non-key-preserved tables. • A value can’t be added to a column containing an expression. 6. How do you ensure that no user can change the data displayed by a view? Include the WITH READ ONLY option when creating the table. 7. What’s the difference between a key-preserved and a non-key-preserved table? A key-preserved table has the primary key of the table included in the view, and this value serves as the primary key of the view. 8. What command can be used to modify a view? A view can’t be modified; however, the CREATE OR REPLACE VIEW command can be used to replace an existing view. 9. What’s unique about materialized views compared with other views? The data retrieved by the view is physically stored or replicated. 10. What happens to the data displayed by a view if the view is deleted? Nothing; the data is not affected by deleting the view because it still exists in the underlying tables. Multiple Choice 1. c 2. b 3. b 4. d 5. b 6. e 7. b 8. b 9. a 10. d 11. d 12. f 13. e 14. e 15. d 16. a 17. a 18. b 19. d 20. e Hands-On Assignments 1. CREATE VIEW contact AS SELECT contact, phone FROM publisher; 2. CREATE OR REPLACE VIEW contact AS SELECT contact, phone FROM publisher WITH READ ONLY; 3. CREATE FORCE VIEW homework13 AS SELECT col1, col2 FROM firstattempt; 4. DESC homework13; 5. CREATE VIEW reorderinfo AS SELECT isbn, title, contact, phone FROM books JOIN publisher USING (pubid); 6. UPDATE reorderinfo SET contact = 'YOUR NAME' WHERE contact = 'RENEE SMITH'; Note: This command doesn’t execute because the primary key for the PUBLISHER table isn’t in the view. 7. UPDATE reorderinfo SET isbn = '9876543210' WHERE title = 'SHORTEST POEMS'; Note: This command fails because of constraint violations. Consider all the FOREIGN KEY constraints involving the BOOKS table, including the relationship with the ORDERITEMS and PUBLISHER tables. If this statement didn’t violate any FOREIGN KEY contraints, it would execute successfully because the primary key of BOOKS is included in the view. 8. DELETE FROM reorderinfo WHERE contact = 'RENEE SMITH'; Note: This command attempts to delete rows from the BOOKS table and fails because of FOREIGN KEY constraint violations. If this statement didn’t violate any constraints, rows from the BOOKS table would be deleted because ISBN is considered the view’s primary key. 9. ROLLBACK; 10. DROP VIEW reorderinfo; Advanced Challenge SELECT isbn, totalqty, profitpercent FROM (SELECT isbn, COUNT(isbn) totalqty, ROUND((retail-cost)/cost*100,0) ProfitPercent FROM orderitems JOIN books USING (isbn) GROUP BY isbn, ROUND((retail-cost)/cost*100,0) ORDER BY COUNT(isbn) DESC) WHERE ROWNUM <= 5; Case Study: City Jail 1. SELECT first, last FROM (SELECT first, last, COUNT(crime_id) cnt FROM criminals JOIN crimes USING (criminal_id) GROUP BY first, last ORDER BY cnt DESC) WHERE ROWNUM <= 3; Note: This problem is trying to highlight an issue with the TOP-N analysis. Many criminals have committed one crime. The TOP-N analysis returns the top three rows, even though other criminals have the same number of crimes. 2. CREATE VIEW crimes_vu AS SELECT criminal_id, first, last, p_status, crime_id, date_charged, status, charge_id, crime_code, charge_status, pay_due_date, NVL(court_fee,0) + NVL(fine_amount,0) - NVL(amount_paid,0) due FROM criminals JOIN crimes USING (criminal_id) JOIN crime_charges USING (crime_id) WITH READ ONLY; 3. CREATE MATERIALIZED VIEW officer_info REFRESH COMPLETE START WITH SYSDATE NEXT SYSDATE + 14 AS SELECT officer_id, last, first, precinct, badge, phone, status, COUNT(crime_id) cnt FROM officers JOIN crime_officers USING (officer_id) GROUP BY officer_id, last, first, precinct, badge, phone, status; Solution Manual for Oracle 12c: SQL Joan Casteel 9781305251038