IS640 chapter 9

R2. 2. How many rows are returned in a Cartesian join between one table having 5 records and a second table having 10 records?

50 row

Mc 19 19. What is the maximum number of characters allowed in a table alias? a. 10 b. 30 c. 255 d. 256

b. 30

Mc 1 1. Which of the following queries creates a Cartesian join? a. SELECT title, authorid FROM books, bookauthor; b. SELECT title, name FROM books CROSS JOIN publisher; c. SELECT title, gift FROM books NATURAL JOIN promotion; d. all of the above

d. all of the above

Summary 2 A Cartesian join between two tables returns every possible combination of rows from the tables. A Cartesian join can be produced by not including a join operation in the query or by using a CROSS JOIN.

'

R9 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.

R 10 10. What are the differences between 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

R7 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.

Review questions 1 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 opertions; outer joins include rows that don't have matches in the join operations

R4 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

12. Given the following query: SELECT title, gift FROM books CROSS JOIN promotion; Which of the following queries is equivalent? a. SELECT title, gift FROM books NATURAL JOIN promotion; b. SELECT title FROM books INTERSECT SELECT gift FROM promotion; c. SELECT title FROM books UNION ALL SELECT gift FROM promotion; d. all of the above

a. SELECT title, gift FROM books NATURAL JOIN promotion;

16. Which of the following SQL statements is not valid? a. SELECT isbn FROM books MINUS SELECT isbn FROM orderitems; b. SELECT isbn, name FROM books, publisher WHERE books.pubid (+) ¼ publisher.pubid (+); c. SELECT title, name FROM books NATURAL JOIN publisher d. All the above SQL statements are valid.

b. SELECT isbn, name FROM books, publisher WHERE books.pubid (+) ¼ publisher.pubid (+);

Mc 11 11. Which line in the following SQL statement raises an error? 1. SELECT name, title 2. FROM books JOIN publisher 3. WHERE books.pubid ¼ publisher.pubid 4. AND 5. cost < 45.95 a. line 1 b. line 2 c. line 3 d. line 4 e. line 5

b. line 2

18. Which line in the following SQL statement raises an error? 1. SELECT name, title 2. FROM books b, publisher p 3. WHERE books.pubid ¼ publisher.pubid 4. AND 5. (retail > 25 OR retail-cost > 18.95); a. line 1 b. line 3 c. line 4 d. line 5

b. line 3

Mc2 2. Which of the following operators is not allowed in an outer join? a. AND b. = c. OR d. >

c OR

R8 8. How many join conditions are needed for a query that joins five tables?

4

R3 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 name columns could be added but don't actually represent the same data and then used incorrectly in a join operation.

Syntax 10 Outer Join Includes a table's records in the output when there's no matching record in the other table. Use the keyword WHERE; use the outer join operator (+) to create NULL rows in the deficient table for records that don't have a matching row. Example SELECT lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer#(+) ORDER BY c.customer#;

Include the keyword LEFT, RIGHT, or FULL with the OUTER JOIN keywords. Example SELECT lastname, firstname, order# FROM customers c LEFT OUTER JOIN orders o USING (customer#) ORDER BY c.customer#;

Syntax 5 OUTER JOIN keywords Can be a RIGHT, LEFT, or FULL outer join

Indicates that at least one of the tables doesn't have a matching row in the other table.

R6 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.)

R5 5. What's 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 the ambiguity error.

Mc7 7. Given the following query: SELECT zip, order# FROM customers NATURAL JOIN orders; Which of the following queries is equivalent? a. SELECT zip, order# FROM customers JOIN orders WHERE customers.customer# ¼ orders.customer#; b. SELECT zip, order# FROM customers, orders WHERE customers.customer# ¼ orders.customer#; c. SELECT zip, order# FROM customers, orders WHERE customers.customer# ¼ orders.customer# (+); d. none of the above

MC7 c. SELECT zip, order# FROM customers, orders WHERE customers.customer# ¼ orders.customer# (+);

Syntax 4 JOIN... ON keywords The JOIN keyword is used in the FROM clause. The ON clause identifies the column used to join the tables.

SELECT columnname [,...] FROM tablename1 JOIN tablename2 ON tablename1.columnname <comparison operator> tablename2. columnname;

Syntax 3 JOIN... USING keywords The JOIN keyword is used in the FROM clause; combined with the USING clause, it identifies the common column used to join the tables. Normally, it's used if the tables have more than one commonly named column and only one is being used for the join.

SELECT columnname [,...] FROM tablename1 JOIN tablename2 USING (columnname);

Syntax 2 NATURAL JOIN keywords These keywords are used in the FROM clause to join tables containing a common column with the same name and definition.

SELECT columnname [,...] FROM tablename1 NATURAL JOIN tablename2;

Syntax 1 WHERE clause In the traditional approach, the WHERE clause indicates which columns should be used to join tables.

SELECT columnname [,...] FROM tablename1, tablename2 WHERE tablename1.columnname <comparison operator> tablename2.columnname;

Set Operators These operators include UNION, UNION ALL, INTERSECT, and MINUS. Combine results of multiple SELECT statements.

SELECT customer# FROM customers UNION SELECT customer# FROM orders;

Syntax 6 Cartesian Join Also known as a Cartesian product or cross join; matches each record in one table with each record in another table SELECT title, name FROM books, publisher;

Use keywords CROSS JOIN. Example SELECT title, name FROM books CROSS JOIN publisher;

Syntax 7 Equality Join Also known as an equijoin, an inner join, or a simple join. Joins data in tables having equivalent data in a common column. With the traditional approach, you must qualify common columns with a table name. Use the keyword WHERE. Example SELECT title, books.pubid, name FROM publisher, books WHERE publisher.pubid = books.pubid AND publisher.pubid = 4;

Use keywords NATURAL JOIN or JOIN ... USING to join tables having a commonly defined field. Use keywords JOIN ... ON when tables don't have a commonly defined field. The column qualifier ON tells Oracle 12c how tables are related. Examples SELECT title, pubid, name FROM publisher NATURAL JOIN books; SELECT title, pubid, name FROM publisher JOIN books USING (pubid); SELECT title, name FROM books b JOIN publisher p ON b.pubid = p.pubid;

Syntax 8 Non-Equality Join Joins tables when there are no equivalent rows in the tables to be joined. Often used to match values in one column with a range of values in another column. Can use any comparison operator except the equal sign (¼). Use the keyword WHERE. Example SELECT title, gift FROM books, promotion WHERE retail BETWEEN minretail AND maxretail;

Use the keyword WHERE. Example SELECT title, gift FROM books, promotion WHERE retail BETWEEN minretail AND maxretail;

Syntax 9 Self-Join Joins a table to itself so that columns in the table can be joined. Must create table aliases. Use the keyword WHERE. Example SELECT r.firstname, r.lastname, c.lastname "Referred" FROM customers c, customers r WHERE c.referred = r.customers#;

Use the keywords JOIN ... ON. Example SELECT r.firstname, r.lastname, c.lastname "Referred" FROM customers c JOIN customers r ON c.referred = r.customers#;

Hoa 7 7. What gift will a customer who orders the book Shortest Poems receive? Use the actual book retail value to determine the gift.

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';

Hoa 2 2. Determine which orders haven't yet shipped and the name of the customer who placed the order. Sort the results by the date on which the order was placed.

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;

Hoa 4 4. Determine which books customer Jake Lucas has purchased. Perform the search using the customer name, not the customer number. If he has purchased multiple copies of the same book, unduplicate the results.

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';

Hoa 3 3. Produce a list of all customers who live in the state of Florida and have ordered books about computers.

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';

hoa 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';

Hoa 6 6. Which books were written by an author with the last name Adams? Perform the search using the author name.

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';

Hoa 5 5. Determine the profit of each book sold to Jake Lucas, using the actual price the customer paid (not the book's regular retail price). Sort the results by order date. If more than one book was ordered, sort the results by profit amount in descending order. Perform the search using the customer name, not the customer number.

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;

hoa 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#);

Hands on assignment 1 1. Create a list that displays the title of each book and the name and phone number of the contact at the publisher's office for reordering each book.

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);

hoa 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";

15. Which of the following lists all books published by the publisher named Printing Is Us? a. SELECT title FROM books NATURAL JOIN publisher WHERE name ¼ 'PRINTING IS US'; b. SELECT title FROM books, publisher WHERE pubname ¼ 1; c. SELECT * FROM books b, publisher p JOIN tables ON b.pubid ¼ p.pubid WHERE name ¼ 'PRINTING IS US'; d. none of the above

a. SELECT title FROM books NATURAL JOIN publisher WHERE name ¼ 'PRINTING IS US';

Mc3 3. Which of the following queries contains an equality join? a. SELECT title, authorid FROM books, bookauthor WHERE books.isbn ¼ bookauthor.isbn AND retail > 20; b. SELECT title, name FROM books CROSS JOIN publisher; c. SELECT title, gift FROM books, promotion WHERE retail >¼ minretail AND retail <¼ maxretail; d. none of the above

a. SELECT title, authorid FROM books, bookauthor WHERE books.isbn ¼ bookauthor.isbn AND retail > 20;

Mc 17 17. Which of the following statements about an outer join between two tables is true? a. If the relationship between the tables is established with a WHERE clause, both tables can include the outer join operator. b. To include unmatched records in the results, the record is paired with a NULL record in the deficient table. c. The RIGHT, LEFT, and FULL keywords are equivalent. d. all of the above e. none of the above

b. To include unmatched records in the results, the record is paired with a NULL record in the deficient table.

Mc 6 6. Which of the following queries is valid? a. SELECT b.title, b.retail, o.quantity FROM books b NATURAL JOIN orders od NATURAL JOIN orderitems o WHERE od.order# ¼ 1005; b. SELECT b.title, b.retail, o.quantity FROM books b, orders od, orderitems o WHERE orders.order# ¼ orderitems.order# AND orderitems.isbn ¼ books.isbn AND od.order# ¼ 1005; c. SELECT b.title, b.retail, o.quantity FROM books b, orderitems o WHERE o.isbn ¼ b.isbn AND o.order# ¼ 1005; d. none of the above

c. SELECT b.title, b.retail, o.quantity FROM books b, orderitems o WHERE o.isbn ¼ b.isbn AND o.order# ¼ 1005;

Mc 20 20. Which of the following SQL statements is valid? a. SELECT books.title, orderitems.quantity FROM books b, orderitems o WHERE b.isbn ¼ o.ibsn; b. SELECT title, quantity FROM books b JOIN orderitems o; c. SELECT books.title, orderitems.quantity FROM books JOIN orderitems ON books.isbn ¼ orderitems.isbn; d. none of the above

c. SELECT books.title, orderitems.quantity FROM books JOIN orderitems ON books.isbn ¼ orderitems.isbn;

Mc9 Given the following query: SELECT lastname, firstname, order# FROM customers LEFT OUTER JOIN orders USING (customer#) ORDER BY customer#; Which of the following queries returns the same results? a. SELECT lastname, firstname, order# FROM customers c OUTER JOIN orders o ON c.customer# ¼ o.customer# ORDER BY c.customer#; b. SELECT lastname, firstname, order# FROM orders o RIGHT OUTER JOIN customers c ON c.customer# ¼ o.customer# ORDER BY c.customer# c. SELECT lastname, firstname, order# FROM customers c, orders o WHERE c.customer# ¼ o.customer# (+) ORDER BY c.customer#; d. none of the above

c. SELECT lastname, firstname, order# FROM customers c, orders o WHERE c.customer# ¼ o.customer# (+) ORDER BY c.customer#;

Mc4 4. Which of the following queries contains a non-equality join? a. SELECT title, authorid FROM books, bookauthor WHERE books.isbn ¼ bookauthor.isbn AND retail > 20; b. SELECT title, name FROM books JOIN publisher USING (pubid); c. SELECT title, gift FROM books, promotion WHERE retail >¼ minretail AND retail <¼ maxretail; d. none of the above

c. SELECT title, gift FROM books, promotion WHERE retail >¼ minretail AND retail <¼ maxretail;

Mc 5 5. The following SQL statement contains which type of join? SELECT title, order#, quantity FROM books FULL JOIN orderitems ON books.isbn ¼ orderitems.isbn; a. equality b. self-join c. non-equality d. outer join

d. outer join

Mc 13 13. If the CUSTOMERS table contains seven records and the ORDERS table has eight records, how many records does the following query produce? SELECT* FROM customers CROSS JOIN orders; a. 0 b. 8 c. 7 d. 15 e. 56

e. 56

Mc 14 14. Which of the following SQL statements is not valid? a. SELECT b.isbn, p.name FROM books b NATURAL JOIN publisher p; b. SELECT isbn, name FROM books b, publisher p WHERE b.pubid ¼ p.pubid; c. SELECT isbn, name FROM books b JOIN publisher p ON b.pubid ¼ p.pubid; d. SELECT isbn, name FROM books JOIN publisher USING (pubid); e. None—all the above are valid SQL statements.

e. None—all the above are valid SQL statements.

Mc8 8. Which line in the following SQL statement raises an error? 1. SELECT name, title 2. FROM books NATURAL JOIN publisher 3. WHERE category ¼ 'FITNESS' 4. OR 5. books.pubid ¼ 4; a. line 1 b. line 2 c. line 3 d. line 4 e. line 5

e. line 5

Mc 10 10. Given the following query: SELECT DISTINCT zip, category FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books; Which of the following queries is equivalent? a. SELECT zip FROM customers UNION SELECT category FROM books; b. SELECT DISTINCT zip, category FROM customers c, orders o, orderitems oi, books b WHERE c.customer# ¼ o.customer# AND o.order# ¼ oi.order# AND oi.isbn ¼ b.isbn; c. SELECT DISTINCT zip, category FROM customers c JOIN orders o JOIN orderitems oi JOIN books b ON c.customer# ¼ o.customer# AND o.order# ¼ oi.order# AND oi.isbn ¼ b.isbn; d. all of the above e. none of the above

e. none of the above