JustLee Books Database SQL Quiz PDF

JustLee Books Database SQL Quiz Joining Data from Multiple Tables 1. Which of the following queries creates a Cartesian join? ○ a) A query that selects the title and author ID from the books and bookauthor tables. ○ b) A query that selects the title and name from...

JustLee Books Database SQL Quiz Joining Data from Multiple Tables 1. Which of the following queries creates a Cartesian join? ○ a) A query that selects the title and author ID from the books and bookauthor tables. ○ b) A query that selects the title and name from the books and publisher tables using a CROSS JOIN. ○ c) A query that selects the title and gift from the books and promotion tables using a NATURAL JOIN. ○ d) All of the above 2. Which of the following operators is not allowed in an outer join? ○ a) AND ○ b) Equals (=) ○ c) OR ○ d) Greater than (>) 3. Which of the following queries contains an equality join? ○ a) A query that selects the title and author ID from the books and bookauthor tables where the ISBNs match and the retail price is greater than 20. ○ b) A query that selects the title and name from the books and publisher tables using a CROSS JOIN. ○ c) A query that selects the title and gift from the books and promotion tables where the retail price falls within a specific range. ○ d) None of the above 4. Which of the following queries contains a non-equality join? ○ a) A query that selects the title and author ID from the books and bookauthor tables where the ISBNs match and the retail price is greater than 20. ○ b) A query that selects the title and name from the books and publisher tables using a JOIN on the publisher ID. ○ c) A query that selects the title and gift from the books and promotion tables where the retail price falls within a specific range. ○ d) None of the above 5. What type of join is used when a query selects the title, order number, and quantity from the books and order items tables, matching them on ISBNs with a FULL JOIN? ○ a) Equality join ○ b) Self-join ○ c) Non-equality join ○ d) Outer join 6. Which of the following queries is valid? ○ a) A query that selects the title, retail price, and quantity of books from the books, orders, and order items tables using a NATURAL JOIN and filters by a specific order number.  ○ b) A query that selects the title, retail price, and quantity of books from the books, orders, and order items tables using a WHERE clause to match ISBNs and order numbers, filtering by a specific order number. ○ c) A query that selects the title, retail price, and quantity of books from the books and order items tables, matching ISBNs and filtering by a specific order number. ○ d) None of the above 7. Given a query that selects the zip code and order number from the customers and orders tables using a NATURAL JOIN, which of the following queries is equivalent? ○ a) A query that joins the customers and orders tables where customer numbers match. ○ b) A query that joins the customers and orders tables using a WHERE clause where customer numbers match. ○ c) A query that joins the customers and orders tables using a LEFT OUTER JOIN where customer numbers match. ○ d) None of the above 8. Which line in a SQL statement that selects the name and title from the books and publisher tables using a NATURAL JOIN raises an error if the WHERE clause filters by a specific category or publisher ID? ○ a) The SELECT clause ○ b) The FROM clause ○ c) The WHERE clause for the category filter ○ d) The OR clause in the WHERE statement ○ e) The WHERE clause for the publisher ID filter 9. Given a query that selects the last name, first name, and order number from the customers and orders tables using a LEFT OUTER JOIN on customer numbers and orders the results by customer number, which of the following queries returns the same results? ○ a) A query that uses an OUTER JOIN on customer numbers. ○ b) A query that uses a RIGHT OUTER JOIN on customer numbers. ○ c) A query that uses a LEFT OUTER JOIN where customer numbers match and orders the results by customer number. ○ d) None of the above 10. Given a query that selects distinct zip codes and categories from the customers, orders, order items, and books tables using NATURAL JOINs, which of the following queries is equivalent? ○ a) A query that selects distinct zip codes from customers and categories from books using a UNION. ○ b) A query that selects distinct zip codes and categories from customers, orders, order items, and books using JOINs where the customer, order, and item numbers match. ○ c) A query that selects distinct zip codes and categories from customers, orders, order items, and books using multiple JOINs and matching customer, order, and item numbers. ○ d) All of the above ○ e) None of the above  11. Which line in a SQL statement that selects the name and title from the books and publisher tables using a JOIN on publisher IDs and filters by cost raises an error? ○ a) The SELECT clause ○ b) The FROM clause ○ c) The WHERE clause matching publisher IDs ○ d) The AND clause ○ e) The WHERE clause filtering by cost 12. Given a query that selects the title and gift from the books and promotion tables using a CROSS JOIN, which of the following queries is equivalent? ○ a) A query that selects the title and gift using a NATURAL JOIN. ○ b) A query that selects the title from books and the gift from promotion using an INTERSECT. ○ c) A query that selects the title from books and the gift from promotion using a UNION ALL. ○ d) All of the above 13. If the CUSTOMERS table contains seven records and the ORDERS table has eight records, how many records does a query that uses a CROSS JOIN between these tables produce? ○ a) 0 ○ b) 8 ○ c) 7 ○ d) 15 ○ e) 56 14. Which of the following SQL statements is not valid? ○ a) A query that selects ISBNs and publisher names from the books and publisher tables using a NATURAL JOIN. ○ b) A query that selects ISBNs and publisher names from the books and publisher tables using a WHERE clause to match publisher IDs. ○ c) A query that selects ISBNs and publisher names from the books and publisher tables using a JOIN on publisher IDs. ○ d) A query that selects ISBNs and publisher names from the books and publisher tables using a JOIN and matching publisher IDs directly. ○ e) None—all the above are valid SQL statements. 15. Which of the following lists all books published by the publisher named "Printing Is Us"? ○ a) A query that selects titles from the books and publisher tables using a NATURAL JOIN and filters by the publisher name. ○ b) A query that selects titles from the books and publisher tables where the publisher name is matched to an ID. ○ c) A query that selects all columns from the books and publisher tables using a JOIN on publisher IDs where the publisher name matches. ○ d) None of the above Selected Single-Row Functions  16. Which of the following functions can be used to extract a portion of a character string? ○ a) A function that extracts a specific part of a date or number. ○ b) A function that truncates a number to a specified number of decimal places. ○ c) A function that returns a substring from a string. ○ d) A function that converts the first letter of each word to uppercase. 17. Which of the following SQL statements produces "Hello World" as the output? ○ a) A query that selects "Hello World" from a dual table. ○ b) A query that selects "HELLO WORLD" and converts it to "Hello World" using INITCAP. ○ c) A query that selects "HELLO WORLD" and converts it to lowercase using LOWER. ○ d) Both a and b ○ e) None of the above 18. Which of the following functions can be used to substitute a value for a NULL value? ○ a) A function that substitutes a value for NULL. ○ b) A function that truncates a number to a specified number of decimal places. ○ c) A function that returns one value if a column is NULL and another value if it is not. ○ d) A function that returns a substring from a string. ○ e) Both a and d ○ f) Both a and c 19. Which of the following functions is used to calculate the total value stored in a specified column? ○ a) A function that counts the number of rows in a column. ○ b) A function that returns the minimum value in a column. ○ c) A function that returns the sum of values in a column. ○ d) A function that adds two numbers together. 20. Which of the following functions can be used to include NULL values in calculations? ○ a) A function that returns the sum of values in a column. ○ b) A function that substitutes a value for NULL. ○ c) A function that returns the maximum value in a column. ○ d) A function that returns the minimum value in a column. Group Functions 21. Which of the following is a valid SQL statement? ○ a) A query that selects the minimum publication date from books grouped by category where the publisher ID equals 4. ○ b) A query that selects the minimum publication date from books where the category is "Cooking".  ○ c) A query that counts the total number of orders where the customer number equals 1005. ○ d) A query that selects the maximum count of customers from orders grouped by customer number. 22. Which of the following SQL statements lists only the book with the largest profit? ○ a) A query that selects the title and maximum profit (retail price minus cost) from books grouped by title. ○ b) A query that selects the title and maximum profit from books grouped by title and filters by the maximum profit. ○ c) A query that selects the title and maximum profit from books without grouping. ○ d) None of the above 23. Which line of the SELECT statement is used to restrict the number of records the query processes? ○ a) The SELECT clause that includes the customer number and count of all records. ○ b) The WHERE clause that filters records based on the order date. ○ c) The GROUP BY clause that groups the data by customer number. ○ d) The HAVING clause that filters the results based on the count of records. 24. Which of the following statements is correct? ○ a) The COUNT function can be used to determine how many rows contain a NULL value. ○ b) Only distinct values are included in group functions unless the ALL keyword is used. ○ c) The HAVING clause restricts which rows are processed. ○ d) The WHERE clause determines which groups are displayed in the query results. ○ e) None of the above 25. Which of the following functions is used to determine the earliest ship date for all orders recently processed by JustLee Books? ○ a) A function that counts the number of rows in a column. ○ b) A function that returns the maximum value in a column. ○ c) A function that returns the minimum value in a column. ○ d) A function that calculates the standard deviation of a column. ○ e) A function that calculates the variance of a column. Subqueries and Merge Statements 26. Which query identifies customers living in the same state as the customer named Leila Smith? ○ a) A query that selects customer numbers where the state matches that of a customer with the last name Smith. ○ b) A query that selects customer numbers where the state matches that of a customer with the last name Smith or first name Leila.  ○ c) A query that selects customer numbers where the state matches that of a customer named Leila Smith, ordered by customer number. ○ d) A query that selects customer numbers where the state matches that of a customer named Leila Smith. 27. Which of the following statements is correct? ○ a) The IN comparison operator can't be used with a subquery that returns only one row of results. ○ b) The equals (=) comparison operator can't be used with a subquery that returns more than one row of results. ○ c) In an uncorrelated subquery, statements in the outer query are executed first, and then statements in the subquery are executed. ○ d) A subquery can be nested only in the outer query's SELECT clause. 28. In a MERGE statement, an INSERT is placed in which conditional clause? ○ a) The clause that specifies the table to be used. ○ b) The clause that specifies the action to take when records match. ○ c) The clause that specifies the action to take when records do not match. ○ d) INSERTs aren't allowed in a MERGE statement. 29. Which of the following operators is used to process a correlated subquery? ○ a) An operator that checks for the existence of records in a subquery. ○ b) An operator that checks for a match in a list of values. ○ c) An operator used to link two queries. ○ d) An operator used in a MERGE statement. 30. What is the purpose of a query that selects ISBNs and titles from books where the publisher ID and category match those of books with "ORACLE" in the title? ○ a) To determine which publisher published a book in the Oracle category and then list all other books published by that same publisher. ○ b) To list all publishers and categories containing the word "ORACLE". ○ c) To list the ISBN and title of all books in the same category and published by the same publisher as any book with "ORACLE" in the title. ○ d) None of the above. The query contains a multiple-row operator, and because the inner query returns only one value, the SELECT statement will fail and return an error message.