Oracle 12c SQL 3rd Edition Joan Casteel - Solutions

Why might a MERGE statement be used?
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?
Which SQL clause(s) can’t be used in a subquery in the WHERE or HAVING clauses?
What happens if a single-row subquery returns more than one row of results?
What comparison operators are required for multiple-row subqueries?
What’s the difference between a single-row subquery and a multiple-row subquery?
Which clause is used to restrict the records retrieved from a table? Which clause restricts groups displayed in the query results?
In what order are output results displayed if a SELECT statement contains a GROUP BY clause and no ORDER BY clause?
What’s the maximum depth allowed when nesting group functions?
What’s the basic difference between the ROLLUP and CUBE extensions of the GROUP BY clause?
In which clause should you include the condition MAX (cost) >39 to restrict groups displayed in the query results?
In which clause should you include the condition "pubid=4" to restrict the rows processed by a query?
What’s the retail price of the most expensive book written by Lisa White?
List the customers living in Georgia or Florida who have recently placed an order totaling more than $80.
Determine the average retail price of books by publisher name and category. Include only the categories Children and Computer and the groups with an average retail price greater than $50.
Determine how many orders have been placed by each customer. Do not include in the results any customer who hasn’t recently placed an order with JustLee Books.
Determine the average profit generated by orders in the ORDERS table.
List the retail price of the least expensive book in the Computer category.
Determine the total profit generated by sales to customer 1017.
Display the most recent publication date of all books sold by JustLee Books.
Display the number of books with a retail price of more than $30.00.
Which of the following is not a valid SELECT statement?a. SELECT STDDEV (retail)FROM books;b. SELECT AVG (SUM (retail))FROM ordersNATURAL JOIN orderitems NATURAL JOIN booksGROUP BY customer#;c. SELECT order#, TO_CHAR (SUM (retail), '999.99')FROM orderitems JOIN books USING (isbn)GROUP BY order#;d.
Which of the following functions can be used to determine the earliest ship date for all orders recently processed by JustLee Books?a. COUNT functionb. MAX functionc. MIN functiond. STDDEV functione. VARIANCE function
Use the following SELECT statement to answer.1 SELECT customer#, COUNT(*)2 FROM customers JOIN orders USING (customer#)3 WHERE orderdate > '02-APR-09'4 GROUP BY customer#5 HAVING COUNT(*) > 2;The COUNT(*) function in the SELECT clause is used to return:a. the number of records in the
Use the following SELECT statement to answer.1 SELECT customer#, COUNT(*)2 FROM customers JOIN orders USING (customer#)3 WHERE orderdate > '02-APR-09'4 GROUP BY customer#5 HAVING COUNT(*) > 2;Because the SELECT clause contains the Customer* column, which clause must be included for the query
Use the following SELECT statement to answer.1 SELECT customer#, COUNT(*)2 FROM customers JOIN orders USING (customer#)3 WHERE orderdate > '02-APR-09'4 GROUP BY customer#5 HAVING COUNT(*) > 2;Which line of the SELECT statement is used to group data stored in the database?a. 1b. 3c. 4d. 5
Use the following SELECT statement to answer1 SELECT customer#, COUNT(*)2 FROM customers JOIN orders USING (customer#)3 WHERE orderdate > '02-APR-09'4 GROUP BY customer#5 HAVING COUNT(*) > 2;Which line of the SELECT statement is used to restrict groups displayed in the query results?a. 1b.
Use the following SELECT statement to answer. 1 SELECT customer#, COUNT(*)2 FROM customers JOIN orders USING (customer#)3 WHERE orderdate > '02-APR-09'4 GROUP BY customer#5 HAVING COUNT(*) > 2;Which line of the SELECT statement is used to restrict the number of records the query processes?a.
Which of the following SQL statements determines how many total customers were referred by other customers?a. SELECT customer#, SUM (referred)FROM customersGROUP BY customer#;b. SELECT COUNT (referred)FROM customers;c. SELECT COUNT(*)FROM customers;d. SELECT COUNT(*)FROM customersWHERE referred IS
Which of the following is not a valid statement?a. You must enter the ALL keyword in a group function to include all duplicate values.b. The AVG function can be used to find the average calculated difference between two dates.c. The MIN and MAX functions can be used on any type of data.d. All of
Which of the following functions can be used to include NULL values in calculations?a. SUMb. NVLc. MAXd. MIN
Which of the following SELECT statements lists the highest retail price of all books in the Family category?a. SELECT MAX (retail)FROM booksWHERE category = 'FAMILY';b. SELECT MAX (retail)FROM booksHAVING category = 'FAMILY';c. SELECT retailFROM booksWHERE category = 'FAMILY'HAVING MAX (retail);d.
Which of the following functions is used to calculate the total value stored in a specified column?a. COUNTb. MINc. TOTALd. SUMe. ADD
Which of the following is correct?a. A group function can be nested inside a group function.b. A group function can be nested inside a single-row function.c. A single-row function can be nested inside a group function.d. a and be. a, b, and c
Which of the following SELECT statements lists only the book with the largest profit?a. SELECT title, MAX (retail-cost)FROM booksGROUP BY title;b. SELECT title, MAX (retail-cost)FROM booksGROUP BY titleHAVING MAX (retail-cost);c. SELECT title, MAX (retail-cost)FROM books;d. None of the above
Which of the following is a valid SQL statement?a. SELECT customer#, order#, MAX (shipdate-orderdate)FROM ordersGROUP BY customer#WHERE customer# = 1001;b. SELECT customer#, COUNT (order#)FROM ordersGROUP BY customer#;c. SELECT customer#, COUNT (order#)FROM ordersGROUP BY COUNT (order#);d. SELECT
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 included in the SELECT clause.c. The HAVING clause restricts which rows are processed.d.
Which of the following isnota valid SQL statement?a. SELECT MIN (pubdate)FROM booksGROUP BY categoryHAVING pubid = 4;b. SELECT MIN (pubdate)FROM booksWHERE category = 'COOKING';c. SELECT COUNT(*)FROM ordersWHERE customer# = 1005;d. SELECT MAX(COUNT (customer#))FROM ordersGROUP BY customer#;
Which of the following statements is correct?a. The WHERE clause can contain a group function only if the function isn’t also listed in the SELECT clause.b. Group functions can’t be used in the SELECT, FROM, or WHERE clauses.c. The HAVING clause is always processed before the WHERE clause.d.
Which of the following is a valid SELECT statement?a. SELECT AVG (retail-cost)FROM booksGROUP BY category;b. SELECT category, AVG (retail-cost)FROM books;c. SELECT category, AVG (retail-cost)FROM booksWHERE AVG (retail-cost) > 8.56GROUP BY category;d. SELECT category, AVG (retail-cost)
Which of the following statements is true?a. The MIN function can be used only with numeric data.b. The MAX function can be used only with date values.c. The AVG function can be used only with numeric data.d. The SUM function can’t be part of a nested function.
The city’s Crimes Analysis unit has submitted the following data requests. Provide the SQL statements to satisfy these requests. Test the statements and show the query results.1. Show the average number of crimes reported by an officer.2.Show the total number of crimes by status.3. List the
JustLee Books has a problem: Book storage space is filling up. As a solution, management is considering limiting the inventory to only those books returning at least a 55% profit. Any book returning less than a 55% profit would be dropped from inventory and not reordered. This plan could, however,
Determine how many books are in the Cooking category.
Under what circumstances must you include a GROUP BY clause in a query?
Which clause can be used to restrict or filter the groups returned by a query based on a group function?
Which group function can be used to perform a count that includes NULL values?
Explain the difference between single-row and group functions.
The following list reflects current data requests from city managers. Provide the SQL statement to satisfy each request. Test the statements and show execution results.1. List the following information for all crimes that have a period greater than 14 days between the date charged and the hearing
Management is proposing to increase the price of each book. The amount of the increase will be based on each book’s category, according to the following scale: Computer books, 10%; Fitness books, 15%; Self-Help books, 25%; all other categories, 3%. Create a list that displays each book’s title,
Produce a list of each customer number and the third and fourth digits of his or her zip code. The query should also display the position of the first occurrence of a 3 in the customer number, if it exists.
Determine the calendar date of the next occurrence of Wednesday, based on today’s date.
Using today’s date, determine the age (in months) of each book that JustLee sells. Make sure only whole months are displayed; ignore any portions of months. Display the book title, publication date, current date, and age.
Determine the length of data stored in the ISBN field of the BOOKS table. Make sure each different length value is displayed only once (not once for each book).
Create a list of all book titles and costs. Precede each book’s cost with asterisks so that the width of the displayed Cost field is 12.
Display the current day of the week, hour, minutes, and seconds of the current date setting on the computer you’re using.
Display a list of all book titles and the percentage of markup for each book. The percentage of markup should be displayed as a whole number (that is, multiplied by 100) with no decimal position, followed by a percent sign (for example, .2793 = 28%). (The percentage of markup should reflect the
Determine the amount of total profit generated by the book purchased on order 1002. Display the book title and profit. The profit should be formatted to display a dollar sign and two decimal places. Take into account that the customer might not pay the full retail price, and each item ordered can
Create a list of all customer numbers along with text indicating whether the customer has been referred by another customer. Display the text “NOT REFERRED” if the customer wasn’t referred to JustLee Books by another customer or “REFERRED” if the customer was referred.
Produce a list of all customer names in which the first letter of the first and last names is in uppercase and the rest are in lowercase.
If an integer is multiplied by a NULL value, the result is:a. An integerb. A whole numberc. A NULL valued. None of the above—a syntax error message is returned.
What’s the name of the table provided by Oracle 12c for completing queries that don’t involve a table?a. DUMDUMb. DUALc. ORACd. SYS
Which of the following functions can be used to display the character string FLORIDA in the query results whenever FL is entered in the State field?a. SUBSTRb. NVL2c. REPLACEd. TRUNCe. None of the above
Which of the following SQL statements displays a customer’s name in all uppercase characters?a. SELECT UPPER ('firstname', 'lastname')FROM customers;b. SELECT UPPER (firstname, lastname)FROM customers;c. SELECT UPPER (lastname, ',' firstname)FROM customers;d. None of the above
Which of the following format elements causes months to be displayed as a three-letter abbreviation?a. MMMb. MONTHc. MONd. none of the above
Which of the following functions can’t be used to convert the letter case of a character string?a. UPPERb. LOWERc. INITIALCAPd. All of the above can be used for case conversion.
Which of the following is a valid SQL statement?a. SELECT TRUNC (ROUND (125.38, 1), 0) FROM dual;b. SELECT ROUND (TRUNC (125.38, 0)FROM dual;c. SELECT LTRIM(LPAD (state, 5, ' '), 4, -3, "*")FROM dual;d. SELECT SUBSTR (ROUND (14.87, 2, 1), -4, 1)FROM dual;
Which of the following SELECT statements returns 30 as the result?a. SELECT ROUND (24.37, 2) FROM dual;b. SELECT TRUNC (29.99, 2) FROM dual;c. SELECT ROUND (29.01, -1) FROM dual;d. SELECT TRUNC (29.99, -1) FROM dual;
Which function can be used to add spaces to a column until it’s a specific width?a. TRIMLb. PADLc. LWIDTHd. None of the above
Which of the following SQL statements is not valid?a. SELECT TO_CHAR (orderdate, '99/9999')FROM orders;b. SELECT INITCAP (firstname), UPPER (lastname)FROM customers;c. SELECT cost, retail,TO_CHAR(retail-cost, '$999.99') profitFROM books;d. All of the above
Which of the following functions can produce different results, depending on the value of a specified column?a. NVLb. DECODEc. UPPERd. SUBSTR
Which of the following displays the order date for order 1000 as 03/31?a. SELECT TO_CHAR (orderdate, 'MM/DD')FROM ordersWHERE order# = 1000;b. SELECT TO_CHAR (orderdate, 'Mth/DD')FROM ordersWHERE order# =1000;c. SELECT TO_CHAR (orderdate, 'MONTH/YY')FROM ordersWHERE order# =1000;d. Both a and be.
Which of the following functions can be used to determine how many months a book has been available?a. MONTHb. MONc. MONTH_BETWEENd. None of the above
Which of the following lists only the last four digits of the contact person’s phone number at American Publishing?a. SELECT EXTRACT (phone, -4, 1)FROM publisherWHERE name = 'AMERICAN PUBLISHING';b. SELECT SUBSTR (phone, -4, 1)FROM publisherWHERE name = 'AMERICAN PUBLISHING';c. SELECT EXTRACT
Which of the following is not a valid format argument for displaying the current time?a. 'HH:MM:SS'b. 'HH24:SS'c. 'HH12:MI:SS'd. All of the above are valid.
Which of the following functions can be used to substitute a value for a NULL value?a. NVLb. TRUNCc. NVL2d. SUBSTRe. Both a and df. Both a and c
Which of the following SQL statements produces “Hello World” as the output?a. SELECT "Hello World" FROM dual;b. SELECT INITCAP ('HELLO WORLD') FROM dual;c. SELECT LOWER ('HELLO WORLD') FROM dual;d. Both a and be. None of the above
Which of the following determines how long ago orders that haven’t shipped were received?a. SELECT order#, shipdate-orderdate delayFROM orders;b. SELECT order#, SYSDATE - orderdateFROM ordersWHERE shipdate IS NULL;c. SELECT order#, NVL (shipdate, 0)FROM ordersWHERE orderdate is NULL;d. SELECT
Which of the following functions can be used to extract a portion of a character string?a. EXTRACTb. TRUNCc. SUBSTRd. INITCAP
Which of the following is a valid SQL statement?a. SELECT SYSDATE;b. SELECT UPPER (Hello) FROM dual;c. SELECT TO_CHAR (SYSDATE, 'Month DD, YYYY') FROM dual;d. All of the abovee. None of the above
Why does the function NVL(shipdate, 'Not Shipped') return an error message?
What format model should you use to display the date 25-DEC-09 as Dec. 25?
Describe a situation that calls for using the DECODE function.
Which functions can be used to convert the letter case of character values?
What’s the difference between using the CONCAT function and the concatenation operator (||) in a SELECT clause?
What functions can be used to search character strings for specific patterns of data?
How is the TRUNC function different from the ROUND function?
What’s the difference between the TO_CHAR and TO_DATE functions when working with date values?
What’s the difference between the NVL and NVL2 functions?
Why are functions referred to as “single-row” functions?
The following list reflects the current data requests from city managers. Provide the SQL statements that satisfy the requests. For each request, include one solution using the traditional method and one using an ANSI JOIN statement. Test the statements and show execution results.1. List all
The Marketing Department of JustLee Books is preparing for its annual sales promotion. Each customer who places an order during the promotion will receive a free gift with each book purchased. Each gift will be based on the book’s retail price. JustLee Books also participates in coop advertising
An EMPLOYEES table was added to the JustLee Books database to track employee information. Display a list of each employee’s name, job title, and manager’s name. Use column aliases to clearly identify employee and manager name values. Include all employees in the list and sort by manager name.
Display a list ofall books in the BOOKS table. If a book has been ordered by a customer, also list the corresponding order number and the state in which the customer resides.
Identify the authors of the books Becca Nelson ordered. Perform the search using the customer name.
What gift will a customer who orders the book Shortest Poems receive? Use the actual book retail value to determine the gift.
Which books were written by an author with the last name Adams? Perform the search using the author name.
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
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.
Produce a list of all customers who live in the state of Florida and have ordered books about computers.

Showing 100 - 200 of 566

Oracle 12c SQL 3rd Edition Joan Casteel - Solutions

Step by Step Answers