CS246 Lab 3: Joins and Sub-queries Objectives The objectives of Lab 3 are to: . Create and execute queries involving CROSS, NATURAL, INNER, OUTER, and SELF joins Create and execute queries involving UNION, INTERSECT, and EXCEPT set operators Create and execute queries using sub-queries with the IN, ALL, ANY, and EXISTS operators Requirements 1. For each book display its title and the advances and royalty rates paid on the book. Order the results by title in ascending order. Click the messages tab. How many records were retrieved? 2. Modify query one to create a left (titles) outer join query. Click the message tab. How many records were retrieved? Why are more records returned by query two than by query one? 3. Modify query two to display only those titles whose publishers are located in the United States. 4. Display the last and first name of all authors along with the title and price of all the books they have published. Order the results by the author's last name in ascending order, author's first name in ascending order, and book title in ascending order. 5. Display the publication dates and titles of all books published after the book titled "How About Never?". Order the results by publication date in descending order and then by title in ascending order. 6. Display the type and total sales for each type from the titles tables. Name the columns "Category" and "Total Sales". Display only those types whose total sales are greater than the average sales of any type. Order the results by type in ascending order. Use a sub- query 7. Display the title and price of all books whose price is greater that the average price of all books. Order the results by title in ascending order. Use a sub-query. 8. Display the publisher name of all publishers who have published books in 1999. Order the results by publisher name in ascending order. Use a sub-query. 9. Display the title and price of all books whose price is greater than the price of any history book. Order the results by price in descending order. Use a sub-query in the WHERE clause of the outer query. 10. Display the publisher name of all publishers who have published books that contain the word "The" in the title and whose country is equal to the minimum value of country in the publisher table. Order the results by publisher name. Use a sub-query. 11. Display the last name and first name of all authors who are not located in the same state as any of the publishers. Order the results by last name in ascending order and then by first name in ascending order. 12. Display the author's name (concatenate the first name to the last name separated by a space) and the names (concatenate the first name to the last name separated by a space) of all authors who live in the same state. Write the query so that no information is duplicated in the report. Name the columns Author" and "Authors in the Same State". Order the results by column one in ascending order and then by column two in ascending order. 13. Display the names (first concatenated to last separated by a space) of all authors who have not published a book. Use the EXISTS operator. Name the column Author Name" Order the result by Author Name" in ascending order. 14. Display the names (first concatenated to last separated by a space) of all authors who appear in both the authors and title_authors table. Use the IN operator. Name the column "Author Name". Order the results by "Author Name". 15. Display the category, title, and sales of books that have sales greater than or equal to the average sales of books of its type. Use a correlated sub-query. Order the results by category and title. 16. Display the titles of all books that where published before any book published by "Core Dump Books". Order the result by title in ascending order. 17. Display the names (first concatenated to last separated by a space) of all authors who live in the same state as "Klee Hull". Name the column "Author. Exclude "Klee Hull". Use a sub-query. Order the results by "Author". 18. Display the names of all publishers who have published books for authors who live in "CA". Order the result by publisher name in ascending order. Eliminate duplicate rows in the results. 19. Display the names, last and then first, of all authors who have published a book. Use the EXISTS operator. Order the results by last name in ascending order and then by first name in ascending order. 20. Display all columns in the authors and publishers table. Cross join the two tables. Order the results by author last name in ascending order and them by publisher name in ascending order. 21. Display the title of each book whose revenue (price * sales) is more than 10 times greater than its advance. Order the result by title in ascending order. 22. Display the author last name and publisher name of all authors and publishers who live in the same city. Display all authors even if no publisher lives in the same city. Order the results by author last name in ascending order and then by publisher name in ascending order. 23. Display the author last and first name and the latest date he published a book. Order the results by author last name in ascending order. 24. Display the TitleID title, sales, and running sum of all former book sales. Order the results by TitleID in ascending order. 25. Display the greatest number of titles written (or co-written) by any author. 26. Display the last name of all authors who have participated in writing at least one biography. Order the results by last name in ascending order. 27. Display the last name of all authors who are the sole author of a book. Order the results by last name in ascending order. 28. Display the last name of all authors who wrote (or co-wrote) three or more books. Order the results by last name in ascending order. 29. Display the last name of all authors who wrote (or co-wrote) a children's book and also wrote (or co-wrote) a psychology book. Order the results by last name in ascending order. Turn-in Requirements 1. Word document containing: a. Cover page with your name, course number, section, lab number, and date b. Screen print image of each query and it results from each step above. CS246 Lab 3: Joins and Sub-queries Objectives The objectives of Lab 3 are to: . Create and execute queries involving CROSS, NATURAL, INNER, OUTER, and SELF joins Create and execute queries involving UNION, INTERSECT, and EXCEPT set operators Create and execute queries using sub-queries with the IN, ALL, ANY, and EXISTS operators Requirements 1. For each book display its title and the advances and royalty rates paid on the book. Order the results by title in ascending order. Click the messages tab. How many records were retrieved? 2. Modify query one to create a left (titles) outer join query. Click the message tab. How many records were retrieved? Why are more records returned by query two than by query one? 3. Modify query two to display only those titles whose publishers are located in the United States. 4. Display the last and first name of all authors along with the title and price of all the books they have published. Order the results by the author's last name in ascending order, author's first name in ascending order, and book title in ascending order. 5. Display the publication dates and titles of all books published after the book titled "How About Never?". Order the results by publication date in descending order and then by title in ascending order. 6. Display the type and total sales for each type from the titles tables. Name the columns "Category" and "Total Sales". Display only those types whose total sales are greater than the average sales of any type. Order the results by type in ascending order. Use a sub- query 7. Display the title and price of all books whose price is greater that the average price of all books. Order the results by title in ascending order. Use a sub-query. 8. Display the publisher name of all publishers who have published books in 1999. Order the results by publisher name in ascending order. Use a sub-query. 9. Display the title and price of all books whose price is greater than the price of any history book. Order the results by price in descending order. Use a sub-query in the WHERE clause of the outer query. 10. Display the publisher name of all publishers who have published books that contain the word "The" in the title and whose country is equal to the minimum value of country in the publisher table. Order the results by publisher name. Use a sub-query. 11. Display the last name and first name of all authors who are not located in the same state as any of the publishers. Order the results by last name in ascending order and then by first name in ascending order. 12. Display the author's name (concatenate the first name to the last name separated by a space) and the names (concatenate the first name to the last name separated by a space) of all authors who live in the same state. Write the query so that no information is duplicated in the report. Name the columns Author" and "Authors in the Same State". Order the results by column one in ascending order and then by column two in ascending order. 13. Display the names (first concatenated to last separated by a space) of all authors who have not published a book. Use the EXISTS operator. Name the column Author Name" Order the result by Author Name" in ascending order. 14. Display the names (first concatenated to last separated by a space) of all authors who appear in both the authors and title_authors table. Use the IN operator. Name the column "Author Name". Order the results by "Author Name". 15. Display the category, title, and sales of books that have sales greater than or equal to the average sales of books of its type. Use a correlated sub-query. Order the results by category and title. 16. Display the titles of all books that where published before any book published by "Core Dump Books". Order the result by title in ascending order. 17. Display the names (first concatenated to last separated by a space) of all authors who live in the same state as "Klee Hull". Name the column "Author. Exclude "Klee Hull". Use a sub-query. Order the results by "Author". 18. Display the names of all publishers who have published books for authors who live in "CA". Order the result by publisher name in ascending order. Eliminate duplicate rows in the results. 19. Display the names, last and then first, of all authors who have published a book. Use the EXISTS operator. Order the results by last name in ascending order and then by first name in ascending order. 20. Display all columns in the authors and publishers table. Cross join the two tables. Order the results by author last name in ascending order and them by publisher name in ascending order. 21. Display the title of each book whose revenue (price * sales) is more than 10 times greater than its advance. Order the result by title in ascending order. 22. Display the author last name and publisher name of all authors and publishers who live in the same city. Display all authors even if no publisher lives in the same city. Order the results by author last name in ascending order and then by publisher name in ascending order. 23. Display the author last and first name and the latest date he published a book. Order the results by author last name in ascending order. 24. Display the TitleID title, sales, and running sum of all former book sales. Order the results by TitleID in ascending order. 25. Display the greatest number of titles written (or co-written) by any author. 26. Display the last name of all authors who have participated in writing at least one biography. Order the results by last name in ascending order. 27. Display the last name of all authors who are the sole author of a book. Order the results by last name in ascending order. 28. Display the last name of all authors who wrote (or co-wrote) three or more books. Order the results by last name in ascending order. 29. Display the last name of all authors who wrote (or co-wrote) a children's book and also wrote (or co-wrote) a psychology book. Order the results by last name in ascending order. Turn-in Requirements 1. Word document containing: a. Cover page with your name, course number, section, lab number, and date b. Screen print image of each query and it results from each step above