ISM FINAL

Which of the following is a correct statement?

Both a and b are correct

Which of the following is a SQL statement?

rollback;

Which of the following commands can be used to add new rows to a table?

INSERT INTO

Which of the following statements deletes all rows in the HOMEWORK10 table?

DELETE FROM homework10;

Which of the following statements places a shared lock on at least a portion of a table named HOMEWORK10?

all of the above

Assuming the HOMEWORK10 table has three columns (Col1, Col2, and Col3, in this order), which of the following commands stores a NULL value in Col3 of the HOMEWORK10 table?

INSERT INTO homework10 (col3, col1, col2) VALUES (NULL, 'A', 'B');

Which of the following symbols designates a substitution variable?

&

Which of the following input values results in a successful INSERT of O'hara?

'O' 'hara' (two single quotes following the O)

Which of the following commands locks the HOMEWORK10 table in EXCLUSIVE mode?

LOCK TABLE homework10 IN EXCLUSIVE MODE;

You issue the following command: INSERT INTO homework10 (col1, col2, col3) VALUES ('A', NULL, 'C'). The command will fail if which of the following statements is true?

Col3 is defined as a DATE column.

Which of the following releases a lock currently held by a user on the HOMEWORK10 table?

all of the above

Assume you have added eight new orders to the ORDERS table. Which of the following
is true?

Other users can view the new orders as soon as you exit the system or execute a COMMIT command.

Which of the following commands removes all orders placed before April 1, 2009?

DELETE FROM orders WHERE orderdate < '01-APR-09';

How many rows can be added to a table by executing the INSERT INTO ... command?

1

You accidentally deleted all the orders in the ORDERS table. How can the error be corrected after a COMMIT command has been issued?

None of the above restores the deleted orders.

Which of the following is the standard extension used for a script file?

.sql

rollback occurs automatically when
17.

none of the above

What is the maximum number of rows that can be deleted from a table at one time?

unlimited

Which of the following is a correct statement?

If you attempt to add a record that violates a constraint for one of the table's columns, only the valid columns for the row are added.

What is the maximum number of records that can be modified with a single UPDATE command?

unlimited

Which of the following SQL statements isn't valid?

SELECT address, city, state, zip FROM customers
WHERE lastname 1?4 "SMITH";

Which clause is used to restrict rows or perform selection?

WHERE

Which of the following SQL statements is valid?

None of the statements are valid.

Which of the following returns a list of all customers' names sorted in descending order by city within state?

SELECT firstname, lastname FROM customers ORDER BY state desc, city desc;

Which of the following doesn't return a customer with the last name THOMPSON in the query results?

SELECT lastname FROM customers WHERE lastname 1?4 "THOMPSON";

Which of the following displays all books published by Publisher 1 with a retail price of at least $25.00?

SELECT * FROM books WHERE pubid= 1 AND retail >=25;

What's the default sort sequence for the ORDER BY clause?

ascending

Which of the following doesn't include the display of books published by Publisher 2 and
having a retail price of at least $35.00?

SELECT * FROM books WHERE pubid IN (1, 2, 5) AND retail NOT BETWEEN 1 AND 29.99;

Which of the following includes a customer with the first name BONITA in the results?

SELECT * FROM customers WHERE firstname LIKE '%N%';

Which of the following represents exactly one character in a pattern search?

-

Which of the following returns the book HANDCRANKED COMPUTERS in the results?

SELECT * FROM books WHERE title LIKE 'H_N_C%';

Which of the following clauses is used to display query results in a sorted order?

none of the above

Which of the following SQL statements returns all books published after March 20, 2005?

SELECT * FROM books WHERE pubdate > '20-MAR-05';

Which of the following lists all books published before June 2, 2004 and all books
published by Publisher 4 or in the Fitness category?

none of the above

Which of the following finds all orders placed before April 5, 2009 that haven't yet shipped?

SELECT * FROM orders WHERE orderdate < '05-APR-09' AND shipdate IS NULL;

Which of the following symbols represents any number of characters in a pattern search?

%

Which of the following lists books generating at least $12.00 in profit?

SELECT * FROM books WHERE retail-cost <=12;

Which of the following lists each book having a profit of at least $10.00 in descending order by profit?

SELECT title, retail-cost profit FROM books
WHERE retail-cost >=10.00
ORDER BY "PROFIT" desc;

Which of the following includes the book HOW TO GET FASTER PIZZA in the query results?

SELECT * FROM books WHERE title LIKE '%AS_E%';

Which of the following returns all books published after March 20, 2005?

none of the above

Which of the following queries creates a Cartesian join?

all of the above

Which of the following operators is not allowed in an outer join?

OR

Which of the following queries contains an equality join?

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

Which of the following queries contains a non-equality join?

ELECT title, gift
FROM books, promotion
WHERE retail >= minretail
AND retail <=maxretail;

The following SQL statement contains which type of join?
SELECT title, order#, quantity
FROM books FULL JOIN orderitems
ON books.isbn= orderitems.isbn;

equality

Which of the following queries is valid?

SELECT b.title, b.retail, o.quantity
FROM books b NATURAL JOIN orders od
NATURAL JOIN orderitems o
WHERE od.order# = 1005;

Given the following query:
SELECT zip, order#
FROM customers NATURAL JOIN orders;
Which of the following queries is equivalent?

none of the above

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;

line 4

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?

SELECT lastname, firstname, order#
FROM customers c OUTER JOIN orders o
ON c.customer# =o.customer#
ORDER BY c.customer#;

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?

SELECT DISTINCT zip, category
FROM customers c, orders o, orderitems oi, books b
WHERE c.customer# 1?4 o.customer# AND o.order# 1?4 oi.order#
AND oi.isbn 1?4 b.isbn;

Which line in the following SQL statement raises an error?
SELECT name, title
FROM books JOIN publisher
WHERE books.pubid = publisher.pubid AND
cost < 45.95

line 1

Given the following query:
SELECT title, gift
FROM books CROSS JOIN promotion;
Which of the following queries is equivalent?

all of the above

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;

7

Which of the following SQL statements is not valid?

SELECT b.isbn, p.name
FROM books b NATURAL JOIN publisher p;

Which of the following lists all books published by the publisher named Printing Is Us?

SELECT *
FROM books b, publisher p
JOIN tables ON b.pubid 1?4 p.pubid WHERE name 1?4 'PRINTING IS US';

Which of the following SQL statements is not valid?

SELECT title, name
FROM books NATURAL JOIN publisher

Which of the following statements about an outer join between two tables is true?

all of the above

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

line 4

What is the maximum number of characters allowed in a table alias?

30

Which of the following SQL statements is valid?

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

Which of the following is a valid SQL statement?

SELECT TO_CHAR(SYSDATE, 'Month DD, YYYY') FROM dual;

Which of the following functions can be used to extract a portion of a character string?

SUBSTR

Which of the following determines how long ago orders that haven't shipped were received?

SELECT order#, SYSDATE - orderdate
FROM orders
WHERE shipdate IS NULL;

Which of the following SQL statements produces "Hello World" as the output?

SELECT INITCAP('HELLO WORLD') FROM dual;

Which of the following functions can be used to substitute a value for a NULL value?

botha and d

Which of the following is not a valid format argument for displaying the current time?

'HH:MM:SS'

Which of the following lists only the last four digits of the contact person's phone number at American Publishing?

SELECT SUBSTR(phone, -4, 4)
FROM publisher
WHERE name = 'AMERICAN PUBLISHING';

Which of the following functions can be used to determine how many months a book has
been available?

none of the above

Which of the following displays the order date for order 1000 as 03/31?

SELECT TO_CHAR(orderdate, 'MM/DD')
FROM orders
WHERE order#= 1000;

Which of the following functions can produce different results, depending on the value of a specified column?

DECODE

Which of the following SQL statements is not valid?

SELECT TO_CHAR(orderdate, '99/9999')
FROM orders;

Which function can be used to add spaces to a column until it's a specific width?

none of the above

Which of the following SELECT statements returns 30 as the result?

SELECT ROUND(29.01, -1) FROM dual;

Which of the following is a valid SQL statement?

SELECT TRUNC(ROUND(125.38, 1), 0) FROM dual;

Which of the following functions can't be used to convert the letter case of a character string?

INITIALCAP

Which of the following format elements causes months to be displayed as a three-letter abbreviation?

MON

Which of the following SQL statements displays a customer's name in all uppercase characters?

none of the above

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?

REPLACE

What's the name of the table provided by Oracle 12c for completing queries that don't involve a table?

DUAL

If an integer is multiplied by a NULL value, the result is:

a NULL value

Which of the following statements is true?

The AVG function can be used only with numeric data.

Which of the following is a valid SELECT statement?

SELECT AVG(retail-cost)
FROM books
GROUP BY category;

Which of the following statements is correct?

The GROUP BY clause is always processed before the HAVING clause.

Which of the following is not a valid SQL statement?

SELECT MIN(pubdate)
FROM books
GROUP BY category HAVING pubid = 4;

Which of the following statements is correct?

The COUNT function can be used to determine how many rows contain a NULL value.

Which of the following is a valid SQL statement?

SELECT customer#, COUNT(order#)
FROM orders
GROUP BY customer#;

Which of the following SELECT statements lists only the book with the largest profit?

none of the above

Which of the following is correct?

a,b,andc

Which of the following functions is used to calculate the total value stored in a specified column?

SUM

Which of the following SELECT statements lists the highest retail price of all books in the Family category?

SELECT MAX(retail)
FROM books
WHERE category = 'FAMILY';

Which of the following functions can be used to include NULL values in calculations?

NVL

Which of the following is not a valid statement?

You must enter the ALL keyword in a group function to include all duplicate values.

Which of the following SQL statements determines how many total customers were referred by other customers?

SELECT COUNT(referred)
FROM customers;

Which of the following functions can be used to determine the earliest ship date for all orders recently processed by JustLee Books?

STDDEV function

Which of the following is not a valid SELECT statement?

SELECT title, VARIANCE(retail-cost)
FROM books
GROUP BY pubid;