DATA MANIPULATION LANGUAGE (DML)
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.
SELECT INITCAP(last_name),INITCAP( first_name) FROM customers;
List the title and publish date of any computer book published in 2005. Perform the task of searching for the publish date by using three different methods: a) a range operator, b) a logical operator, and; c) a search pattern operation
(a) SELECT title, publish FROM books WHERE category= 'COMPUTER' AND pubdate BETWEEN '01-JAN-05' AND '31-DEC-05'; (b) SELECT title, pubdate FROM books WHERE category= 'COMPUTER' AND pubdate >= '01-JAN-05' AND pubdate <='31-DEC-05'; (c ) SELECT title, pubdate FROM books WHERE category= 'COMPUTER' AND pubdate LIKE '%05';
What is the default position for the ROUND function? A. 0 B. 2 C. -1 D. 1
A. 0
Which of the following operators is used to find all values greater than the highest value returned by a subquery? a. >ALL b. <ALL c. >ANY d. <ANY e. IN a
A. >ALL
7. What's the default sort sequence for the ORDER BY clause? a. ascending b. descending c. the order in which records are stored in the table d. There's no default sort sequence.
A. Ascending
Which function allows you to display numeric data and dates in a specific format? A. TO_CHAR B. CASE C. DECODE D. SOUNDEX
A. TO_CHAR
Which of the following SELECT statements lists the highest retail price of all books in the Family category? a. SELECT MAX(retail) FROM books WHERE category = 'FAMILY'; b. SELECT MAX(retail) FROM books HAVING category = 'FAMILY'; c. SELECT retail FROM books WHERE category = 'FAMILY' HAVING MAX(retail); d. none of the above
A. SELECT MAX(retail) FROM books WHERE category = 'FAMILY';
Which of the following is not a valid SQL statement? a. SELECT MIN(pubdate) FROM books GROUP BY category HAVING pubid = 4; b. SELECT MIN(pubdate) FROM books WHERE category = 'COOKING'; c. SELECT COUNT(*) FROM orders WHERE customer# = 1005; d. SELECT MAX(COUNT(customer#)) FROM orders GROUP BY customer#;
A. SELECT MIN(pubdate) FROM books GROUP BY category HAVING pubid = 4;
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 of the above are valid SQL statements
A. SELECT b.isbn, p.name FROM books b NATURAL JOIN publisher p;
Which of the following queries identifies other customers who were referred to JustLee Books by the same person who referred Jorge Perez? a. SELECT customer# FROM customers WHERE referred = (SELECT referred FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ'); b. SELECT referred FROM customers WHERE (customer#, referred) = (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ'); c. SELECT referred FROM customers WHERE (customer#, referred) IN (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ'); d. SELECT customer# FROM customers WHERE customer# = (SELECT customer# FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');
A. SELECT customer# FROM customers WHERE referred = (SELECT referred FROM customers WHERE firstname = 'JORGE' AND lastname = 'PEREZ');
Which query determines the customers who have ordered the most books from JustLee Books? a. SELECT customer# FROM orders JOIN orderitems USING(order#) HAVING SUM(quantity) = (SELECT MAX(SUM(quantity)) FROM orders JOIN orderitems USING(order#) GROUP BY customer#) GROUP BY customer#; b. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE SUM(quantity) = (SELECT MAX(SUM(quantity)) FROM orderitems GROUP BY customer#); c. SELECT customer# FROM orders WHERE MAX(SUM(quantity)) = (SELECT MAX(SUM(quantity) FROM orderitems GROUP BY order#); d. SELECT customer# FROM orders HAVING quantity = (SELECT MAX(SUM(quantity)) FROM orderitems GROUP BY customer#);
A. SELECT customer# FROM orders JOIN orderitems USING(order#) HAVING SUM(quantity) = (SELECT MAX(SUM(quantity)) FROM orders JOIN orderitems USING(order#) GROUP BY customer#) GROUP BY customer#;
5. Which of the following doesn't return a customer with the last name THOMPSON in the query results? a. SELECT lastname FROM customers WHERE lastname = "THOMPSON"; b. SELECT * FROM customers; c. SELECT lastname FROM customers WHERE lastname > 'R'; d. SELECT * FROM customers WHERE lastname < 'V';
A. SELECT lastname FROM customers WHERE lastname = "THOMPSON";
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';
Which of the following statements is valid? a. SELECT title FROM books WHERE retail <(SELECT cost FROM books WHERE isbn = '9959789321'); b. SELECT title FROM books WHERE retail = (SELECT cost FROM books WHERE isbn = '9959789321' ORDER BY cost); c. SELECT title FROM books WHERE category IN (SELECT cost FROM orderitems WHERE isbn = '9959789321'); d. none of the above statements
A. SELECT title FROM books WHERE retail <(SELECT cost FROM books WHERE isbn = '9959789321');
Given the following query: SELECT title, give 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;
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. The WHERE clause determines which groups are displayed in the query results. e. none of the above
A. The COUNT function can be used to determine how many rows contain a NULL value.
Character manipulation functions manipulate string data by extracting substrings, counting the number of characters, or replacing strings. A. True B. False
A. True
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 the above e. none of the above
A. You must enter the ALL keyword in a group function to include all duplicate values.
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;
What is the maximum number of characters allowed in a table alias? A. 10 B. 30 C. 255 D. 256
B. 30
Which of the following functions searches a string for a specified set of characters or a substring, and then returns a numeric value representing the first character position in which the substring is found? A. SUBSTR B. INSTR C. RPAD D. LPAD
B. INSTR
Which of the following functions is used to pad, or fill in, a character string to a fixed width? A. INSTR B. LPAD C. LTRIM D. REPLACE
B. LPAD
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
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. 1 b. 3 c. 4 d. 5
B. Line 3
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
9. Which of the following includes a customer with the first name BONITA in the results? a. SELECT * FROM customers WHERE firstname = 'B%'; b. SELECT * FROM customers WHERE firstname LIKE '%N%'; c. SELECT * FROM customers WHERE firstname = '%N%'; d. SELECT * FROM customers WHERE firstname LIKE '_B%';
B. SELECT * FROM customers WHERE firstname LIKE '%N%';
Which of the following SQL statements determines how many total customers were referred by other customers? a. SELECT customer#, SUM(referred) FROM customers GROUP BY customer#; b. SELECT COUNT(referred) FROM customers; c. SELECT COUNT(*) FROM customers; d. SELECT COUNT(*) FROM customers WHERE referred IS NULL;
B. SELECT COUNT(referred) FROM customers;
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
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;
Which of the following will display the name of each customer in lower-case characters based upon the contents of the CUSTOMERS table? A. SELECT LOWER(name) FROM customers; B. SELECT LOWER(firstname) || ' ' || LOWER(lastname) FROM customers; C. None of the above
B. SELECT LOWER(firstname) || ' ' || LOWER(lastname) FROM customers;
Which of the following is a valid SQL statement? a. SELECT customer#, order#, MAX(shipdate-orderdate) FROM orders GROUP BY customer# WHERE customer# = 1001; b. SELECT customer#, COUNT(order#) FROM orders GROUP BY customer#; c. SELECT customer#, COUNT(order#) FROM orders GROUP BY COUNT(order#); d. SELECT customer#, COUNT(order#) FROM orders GROUP BY order#;
B. SELECT customer#, COUNT(order#) FROM orders GROUP BY customer#;
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 of the above SQL statements are valid.
B. SELECT isbn, name FROM books, publisher WHERE books.pubid(+) = publisher.pubid(+);
Which of the following determines how long ago orders that haven't shipped were received? A. SELECT order#, shipdate-orderdate delay FROM orders; B. SELECT order#, SYSDATE-orderdate FROM orders WHERE shipdate IS NULL; C. SELECT order#, NVL (shipdate, 0) FROM orders WHERE orderdate is NULL; D. SELECT order#, NULL (shipdate) FROM orders;
B. SELECT order#, SYSDATE-orderdate FROM orders WHERE shipdate IS NULL;
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
B. SELECT zip, order# FROM customers, orders WHERE customers.customer# = orders.customer#;
Given the following SQL statement, which statement is most accurate? SELECT customer# FROM customers JOIN orders USING(customer#) WHERE shipdate-orderdate IN (SELECT MAX(shipdate-orderdate) FROM orders WHERE shipdate IS NULL); a. The SELECT statement fails and returns an Oracle error message. b. The outer query displays no rows in its results because the subquery passes a NULL value to the outer query. c. The customer number is displayed for customers whose orders haven't yet shipped. d. The customer number of all customers who haven't placed an order are displayed.
B. The outer query displays no rows in its results because the subquery passes a NULL value to the outer query.
Given the following query, which statement is correct? SELECT order# FROM orders WHERE order# IN (SELECT order# FROM orderitems WHERE isbn = '9959789321'); a. The statement doesn't execute because the subquery and outer query don't reference the same table. b. The outer query removes duplicates in the subquery's Order# list. c. The query fails if only one result is returned to the outer query because the outer query's WHERE clause uses the IN comparison operator. d. No rows are displayed because the ISBN in the WHERE clause is enclosed in single quotation marks.
B. The outer query removes duplicates in the subquery's Order# list.
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.
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 specified tables b. the number of orders placed by each customer c. the number of NULL values in the specified tables d. the number of customers who have placed an order
B. the number of orders placed by each customer
Which of the following operators is considered a single-row operator? a. IN b. ALL c. <> d. <>ALL
C. <>
Which function will capitalize the first letter of each word? A. LOWER B. UPPER C. INITCAP D. INITIALCAP
C. INITCAP
Which of the following statements is correct? a. If a subquery is used in the outer query's FROM clause, the data in the temporary table can't be referenced by clauses used in the outer query. b. The temporary table created by a subquery in the outer query's FROM clause must be assigned a table alias, or it can't be joined with another table by using the JOIN keyword. c. If a temporary table is created through a subquery in the outer query's FROM clause, the data in the temporary table can be referenced by another clause in the outer query. d. none of the above
C. If a temporary table is created through a subquery in the outer query's FROM clause, the data in the temporary table can be referenced by another clause in the outer query.
What is the purpose of the following query? SELECT isbn, title FROM books WHERE (pubid, category) IN (SELECT pubid, category FROM books WHERE title LIKE '%ORACLE%'); a. It determines which publisher published a book belonging to the Oracle category and then lists all other books published by that same publisher. b. It lists all publishers and categories containing the value ORACLE. c. It lists the ISBN and title of all books belonging to the same category and having the same publisher as any book with the phrase ORACLE in its 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.
C. It lists the ISBN and title of all books belonging to the same category and having the same publisher as any book with the phrase ORACLE in its title.
Which operator is used to process a correlated subquery? a. EXISTS b. IN c. LINK d. MERGE
C. LINK
Which of the following is used to convert all characters to lowercase? A. INITCAP B. UPPER C. LOWER D. LOW
C. LOWER
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. 1 b. 3 c. 4 d. 5
C. Line 4
Which of the following functions can be used to determine the earliest ship date for all orders recently processed by JustLee Books? a. COUNT function b. MAX function c. MIN function d. STDDEV function e. VARIANCE function
C. MIN function
If the two values compared by the NULLIF function are equal, what value does the function return? A. The first value in the comparison B. The second value in the comparison C. NULL D. Both values in the comparison
C. NULL
Which of the following operators in not allowed in an outer join? A. AND B. = C. OR D. >
C. OR
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 above E. None of the above
C. SELECT TO_CHAR (SYSDATE, 'Month DD, YYYY') FROM dual;
1. Which of the following SQL statements isn't valid? a. SELECT address || city || state || zip "Address" FROM customers WHERE lastname = 'SMITH'; b. SELECT * FROM publisher ORDER BY contact; c. SELECT address, city, state, zip FROM customers WHERE lastname = "SMITH"; d. All the above statements are valid and return the expected results.
C. SELECT address, city, state, zip FROM customers WHERE lastname = "SMITH";
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 orders.od, orderitems o WHERE orders.order#=1005 = orderitems.order# AND orderitems.isbn=books.isbn AND od.order#=1005; C. SELECT b.title, b.retail, o.quantity FROM books b, orderitems 0 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;
Which of the following SQL statements is valid? A. SELECT books.title, orderitems. quantity FROM books b, orderitems o WHERE b.isbn=o.isbn; 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;
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#;
Which of the following is a valid SELECT statement? a. SELECT order# FROM orders WHERE shipdate = SELECT shipdate FROM orders WHERE order# = 1010; b. SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders) AND order# = 1010; c. SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders WHERE order# = 1010); d. SELECT order# FROM orders HAVING shipdate = (SELECT shipdate FROM orders WHERE order# = 1010);
C. SELECT order# FROM orders WHERE shipdate = (SELECT shipdate FROM orders WHERE order# = 1010);
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;
Which of the following functions can be used to extract a portion of a character string? A. EXTRACT B. TRUNC C. SUBSTR D. INITCAP
C. SUBSTR
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.
C. The AVG function can be used only with numeric data
Which of the following is a case conversion function? A. ROUND B. TRUNC C. UPPER D. INSTR
C. UPPER
In a MERGE statement, an INSERT is placed in which conditional clause? a. USING b. WHEN MATCHED c. WHEN NOT MATCHED d. INSERTs aren't allowed in a MERGE statement.
C. WHEN NOT MATCHED
In which of the following situations is using a subquery suitable? a. when you need to find all customers living in a particular region of the country b. when you need to find all publishers who have toll-free telephone numbers c. when you need to find the titles of all books shipped on the same date as an order placed by a particular customer d. when you need to find all books published by Publisher 4
C. when you need to find the titles of all books shipped on the same date as an order placed by a particular customer
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 groups displayed in the query results? a. 1 b. 3 c. 4 d. 5
D. Line 5
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
Which of the following is a date function? A. MONTHS_BEFORE B. LAST_DATE C. CURRENT_DAY D. NEXT_DAY
D. NEXT_DAY
Which function substitutes a value for a NULL value? A. NULL B. TO_CHAR C. DECODE D. NVL
D. NVL
Which of the following SELECT statements lists only the book with the largest profit? a. SELECT title, MAX(retail-cost) FROM books GROUP BY title; b. SELECT title, MAX(retail-cost) FROM books GROUP BY title HAVING MAX(retail-cost); c. SELECT title, MAX(retail-cost) FROM books; d. none of the above
D. None of the above
3. Which of the following SQL statements is valid? a. SELECT order# FROM orders WHERE shipdate = NULL; b. SELECT order# FROM orders WHERE shipdate = 'NULL'; c. SELECT order# FROM orders WHERE shipdate = "NULL"; d. None of the statements are valid.
D. None of the statements are valid.
Which query identifies customers living in the same state as the customer named Leila Smith? a. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH'); b. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' OR firstname = 'LEILA'); c. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' AND firstname = 'LEILA' ORDER BY customer); d. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' AND firstname = 'LEILA');
D. SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE lastname = 'SMITH' AND firstname = 'LEILA');
Which of the following queries determines which customers have ordered the same books as customer 1017? a. SELECT order# FROM orders WHERE customer# = 1017; b. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn = (SELECT isbn FROM orderitems WHERE customer# = 1017); c. SELECT customer# FROM orders WHERE order# = (SELECT order# FROM orderitems WHERE customer# = 1017); d. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1017);
D. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1017);
Which of the following queries identifies customers who have ordered the same books as customers 1001 and 1005? a. SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn = (SELECT isbn FROM orderitems JOIN books USING(isbn) WHERE customer# = 1001 OR customer# = 1005)); b. SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn <ANY (SELECT isbn FROM orderitems JOIN books USING(isbn) WHERE customer# = 1001 OR customer# = 1005)); c. SELECT customer# FROM orders JOIN books USING(isbn) WHERE isbn = (SELECT isbn FROM orderitems JOIN orders USING(order#) WHERE customer# = 1001 OR 1005)); d. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orders JOIN orderitems USING(order#) WHERE customer# IN (1001, 1005));
D. SELECT customer# FROM orders JOIN orderitems USING(order#) WHERE isbn IN (SELECT isbn FROM orders JOIN orderitems USING(order#) WHERE customer# IN (1001, 1005));
Which of the following functions is used to calculate the total value stored in a specified column? a. COUNT b. MIN c. TOTAL d. SUM e. ADD
D. SUM
Which function returns the current date and time set on an operating system where the database resides? A. LAST_MONTH B. TRUNC C. CURRENT_DATE D. SYSDATE
D. SYSDATE
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. The GROUP BY clause is always processed before the HAVING clause
D. The GROUP BY clause is always processed before the HAVING clause
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
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
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 b e. a, b, and c
E. A,B, and C
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. line3 D. line 4 E. line 5
E. Line 5
9. Determine the calendar date of the next occurrence of Wednesday, based on today's date
SELECT NEXT_DAY(sysdate, 'Wednesday') FROM dual;
Which books aren't in the Fitness category? List each book title and category.
SELECT book title, category, FROM books WHERE category <> 'fitness';
Use a search pattern to find any book title with "A" for the second letter and "N" for the fourth letter. List each book's ISBN and title. Sort the list by title in descending order.
SELECT isbn, title FROM books WHERE title LIKE '_A_N%' and ORDER BY title DESC;
A customer service representative is trying to identify all books in the Computer or Family Life category and published by Publisher 1 or Publisher 3. However, the results shouldn't include any book selling for less than $45.00.
SELECT isbn, title, category, pubid, retail FROM books WHERE category='COMPUTER' OR 'FAMILY' AND pubid IN '1,3' AND retail >= 45;
List all customers who were referred to the bookstore by another customer. List each customer's last name and the number of the customer who made the referral.
SELECT last_name, Referred FROM customers WHERE referred IS NOT null;
. Which customers live in New Jersey? List each customer's last name, first name, and state
SELECT last_name, first_name, state FROM customers WHERE state= 'NJ';
Which orders were placed on or before April 1, 2009? List each order number and order date. Write this query two different ways.
SELECT order#, orderdate FROM orders WHERE orderdate <= '01-APR-09'; SELECT order#, orderdate FROM orders WHERE orderdate < '02-APR-09';
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 involve multiple copies.
SELECT order#, isbn, TO_CHAR(retail-cost,'$99.99') AS "PROFIT" FROM orders WHERE order# = 1002;
A manager at JustLee Books requests a list of the titles of all books generating a profit of at least $10.00. The manager wants the results listed in descending order, based on each book's profit.
SELECT title, (retail-cost) AS "Profit" FROM books WHERE (retail-cost) >= 10 ORDER BY (retail-cost) DESC;
All and Any
The ANY and ALL operators are used with a WHERE or HAVING clause. The ANY operator returns true if any of the subquery values meet the condition. SELECT column_name(s) FROM table_name WHERE column_name operator ANY (SELECT column_name FROM table_name WHERE condition); The ALL operator returns true if all of the subquery values meet the condition. SELECT column_name(s) FROM table_name WHERE column_name operator ALL (SELECT column_name FROM table_name WHERE condition);
Which of the following is a valid SELECT statement? a. SELECT AVG(retail-cost) FROM books GROUP BY category; b. SELECT category, AVG(retail-cost) FROM books; c. SELECT category, AVG(retail-cost) FROM books WHERE AVG(retail-cost) > 8.56 GROUP BY category; d. SELECT category, AVG(retail-cost) Profit FROM books GROUP BY category HAVING profit > 8.56;
a. SELECT AVG(retail-cost) FROM books GROUP BY category;
Which of the following functions can be used to include NULL values in calculations? a. SUM b. NVL c. MAX d. MIN
b. NVL
Which of the following SQL statements lists all books written by the author of The Wok Way to Cook? a. SELECT title FROM books WHERE isbn IN (SELECT isbn FROM bookauthor HAVING authorid IN 'THE WOK WAY TO COOK); b. SELECT isbn FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK'); c. SELECT title FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK); d. SELECT isbn FROM bookauthor HAVING authorid = SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK'; b
b. SELECT isbn FROM bookauthor WHERE authorid IN (SELECT authorid FROM books JOIN bookauthor USING(isbn) WHERE title = 'THE WOK WAY TO COOK');
A subquery must be placed in the outer query's HAVING clause if: a. The inner query needs to reference the value returned to the outer query. b. The value returned by the inner query is to be compared to grouped data in the outer query. c. The subquery returns more than one value to the outer query. d. None of the above. Subqueries can't be used in the outer query's HAVING clause.
b. The value returned by the inner query is to be compared to grouped data in the outer query.
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 to execute successfully? a. 1 b. 3 c. 4 d. 5
c. Line 4
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.
c. In an uncorrelated subquery, statements in the outer query are executed first, and then statements in the subquery are executed.
Which of the following is not a valid SELECT statement? a. SELECT STDDEV(retail) FROM books; b. SELECT AVG(SUM(retail)) FROM orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#; c. SELECT order#, TO_CHAR(SUM(retail),'999.99') FROM orderitems JOIN books USING (isbn) GROUP BY order#; d. SELECT title, VARIANCE(retail-cost) FROM books GROUP BY pubid;
d. SELECT title, VARIANCE(retail-cost) FROM books GROUP BY pubid;
Which of the following statements is correct? a. If the subquery returns only a NULL value, the only records returned by an outer query are those containing an equivalent NULL value. b. A multiple-column subquery can be used only in the outer query's FROM clause. c. A subquery can contain only one condition in its WHERE clause. d. The order of columns listed in the SELECT clause of a multiple-column subquery must be in the same order as the corresponding columns listed in the outer query's WHERE clause.
d. The order of columns listed in the SELECT clause of a multiple-column subquery must be in the same order as the corresponding columns listed in the outer query's WHERE clause.
