Midterm 2 (Chapter 5 - 13)

A commit is issued implicitly when a user exits SQL Developer or SQL*Plus.

A commit is issued implicitly when a DDL command is executed.

A commit is issued automatically when a DML command is executed.

All of the above are correct.

A commit is issued implicitly when a user exits SQL Developer or SQL*Plus, and when a DDL command is executed.

A commit is issued implicitly when a user exits SQL Developer or SQL*Plus. A commit is issued automatically when a DML command is executed.

SELECT * WHERE amid = ‘J100’ FOR UPDATE;

INSERT INTO homework10 VALUES (SELECT * FROM acctmanager);

DELETE amid FROM acctmanager;

rollback;

all of the above

INSERT INTO

ALTER TABLE ADD

UPDATE

SELECT . . . FOR UPDATE

DELETE * FROM homework10;

DELETE *.* FROM homework10;

DELETE FROM homework10;

DELETE FROM homework10 WHERE amid = ‘*’;

Both (DELETE FROM homework10;) and (DELETE FROM homework10 WHERE amid = ‘*’;) delete all rows in the HOMEWORK10 table.

SELECT * FROM homework10 WHERE col2 IS NULL FOR UPDATE;

INSERT INTO homework10 (col1, col2, col3) VALUES (‘A’, ‘B’, ‘C’);

UPDATE homework10 SET col3 = NULL WHERE col1 = ‘A’;

UPDATE homework10 SET col3 = LOWER (col3) WHERE col1 = ‘A’;

all of the above

INSERT INTO homework10 VALUES (‘A’, ‘B’, ‘C’);

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

INSERT INTO homework10 VALUES (NULL, ‘A’, ‘B’);

UPDATE homework10 SET col1 = col3;

&

$

#

_

‘O^hara’

‘O’’hara’ (two single quotes following the O)

‘O’’hara’ (a double quote following the O)

Data values can’t contain quotes.

LOCK TABLE homework10 EXCLUSIVELY;

LOCK TABLE homework10 IN EXCLUSIVE MODE;

LOCK TABLE homework10 TO OTHER USERS;

LOCK homework10 IN EXCLUSIVE MODE;

Both b and d lock the table in EXCLUSIVE mode.

Col1 has a PRIMARY KEY constraint enabled.

Col2 has a UNIQUE constraint enabled.

Col3 is defined as a DATE column.

None of the above would cause the command to fail.

A COMMIT command is issued.

A DDL command is issued to end a transaction.

The user exits the system.

A ROLLBACK command is issued.

all of the above

none of the above

Other users can view the new orders as soon as you execute the INSERT INTO command.

Other users can view the new orders as soon as you issue a ROLLBACK command.

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

Other users can view the new orders only if they place an exclusive lock on the table.

DELETE FROM orders WHERE orderdate < ‘01-APR-09’;

DROP FROM orders WHERE orderdate < ‘01-APR-09’;

REMOVE FROM orders WHERE orderdate < ‘01-APR-09’;

DELETE FROM orders WHERE orderdate > ‘01-APR-09’;

1

2

3

unlimited

ROLLBACK;

ROLLBACK COMMIT;

REGENERATE RECORDS orders;

None of the above restores the deleted orders.

.spt

.srt

.script

.sql

A DDL command is executed.

A DML command is executed.

The user exits the system.

none of the above

1

2

3

unlimited

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.

A subquery nested in the VALUES clause of an INSERT INTO command can return only one value without generating an Oracle 11g error message.

If you attempt to add a record that violates a NOT NULL constraint, a blank space is inserted automatically in the appropriate column so that Oracle 11g can complete the DML operation.

None of the above statements is correct.

1

2

3

unlimited

a number generator

a view

a sequence

an index

a synonym

DROP SYNONYM synonymname;

DELETE PUBLIC SYNONYM synonymname;

DROP PUBLIC SYNONYM synonymname;

DELETE SYNONYM synonymname;

ALTER SESSION

ALTER TABLE

MODIFY INDEX

ALTER INDEX

none of the above

NEXTVAL

CURVAL

NEXT_VALUE

CURR_VALUE

NEXT_VAL

CUR_VAL

INSERT INTO publisher
VALUES (pubsequence.nextvalue, 'HAPPY
PRINTING', 'LAZY LARRY', NULL);

CREATE INDEX a_new_index
ON (firstcolumn*.02);

CREATE SYNONYM pub
FOR publisher;

all of the above

none of the above

0

1

2

3

4

CREATE SYNONYM thetable
FOR juan.mytable;

CREATE PUBLIC SYNONYM thetable
FOR mytable;

CREATE SYNONYM juan
FOR mytable;

none of the above

A gap can appear in a sequence created with the NOCACHE option if the system crashes before a user can commit a transaction.

Any unassigned sequence values appears in the USER_SEQUENCE data dictionary table as unassigned.

Only the user who creates a sequence is allowed to delete it.

Only the user who created a sequence is allowed to use the value generated by the sequence.

when queries return a large percentage of rows in the results

when the table is small

when the majority of table operations are updates

all of the above

when queries return a large percentage of rows in the results and when the majority of table operations are updates

IOT

B-tree

bitmap

function-based index

The column contains many distinct values.

The column contains a small number of distinct values.

A WHERE clause is always used in a query on the column.

The selectivity of a column can’t be determined.

NOT NULL

PRIMARY KEY

FOREIGN KEY

UNIQUE KEY

none of the above

NOT NULL and PRIMARY KEY

PRIMARY KEY and UNIQUE KEY

INCREMENT BY

MAXVALUE

START WITH

MINVALUE

CACHE

branch blocks

root block

leaf blocks

None of the above because the primary key is used to identify rows.

Oracle 11g generates 20 integers automatically and stores them in memor

No integers are cached by default.

Only one integer is cached at a time

The command will fail.

Oracle 11g generates 20 three-digit decimal numbers automatically and stores them in memory.

CREATE INDEX book_profit_idx
ON (retail-cost) WHERE (retail-cost) > 10;

CREATE INDEX book_profit_idx
ON (retail-cost);

CREATE FUNCTION INDEX book_profit_idx
ON books WHERE (retail-cost) > 10;

none of the above

DESCRIBE indexname;

the USER_INDEXES view

the USER_INDEX view

all of the above

none of the above

ORDER

NOCYCLE

MINIMUMVAL

NOCACHE

All of the above are valid options.

USER_INDEXES view

query source code

explain plan

database access plan

CREATE PRIVATE SYNONYM

CREATE NONPUBLIC SYNONYM

CREATE SYNONYM

CREATE PUBLIC SYNONYM

ALTER PASSWORD

CHANGE PASSWORD

MODIFY USER PASSWORD

ALTER USER . . . PASSWORD

none of the above

ALTER ROLE mcain
DEFAULT ROLE customerrep;

ALTER USER mcain
TO customerrep;

SET DEFAULT ROLE customerrep
FOR mcain;

ALTER USER mcain
DEFAULT ROLE customerrep

SET ROLE customerrep
FOR mcain;

Authentication procedures prevent any data stored in the Oracle 11g database from being stolen or damaged.

Authentication procedures are used to limit unauthorized access to the Oracle 11g database.

Oracle 11g authentication doesn’t prevent users from accessing data in the database if they have a valid operating system account.

Authentication procedures restrict the type of data manipulation operations that a user can perform.

CREATE ROLE depthead
IDENTIFIED BY apassword;

CREATE USER depthead
IDENTIFIED BY apassword;

CREATE ACCOUNT depthead;

GRANT ACCOUNT depthead;

CONNECT

CREATE SESSION

CONNECT ANY DATABASE

CREATE ANY TABLE

DROP ANY TABLE

TRUNCATE ANY TABLE

CREATE TABLE

TRUNC TABLE

SESSION_PRIVS

SYSTEM_PRIVILEGE_MAP

USER_ASSIGNED_PRIVS

V$ENABLED_PRIVILEGES

REVOKE insert
ON books
FROM elopez;

REVOKE insert
FROM elopez;

REVOKE INSERT INTO
FROM elopez;

DROP insert
INTO books
FROM elopez;

CREATE ROLE

CREATE PRIVILEGE

GRANT

ALTER PRIVILEGE

CREATE USER

ALTER USER

IDENTIFIED BY

PASSWORD EXPIRE

WITH ADMIN OPTION

WITH GRANT OPTI

DBA

ASSIGN ROLES

SET ROLE

CREATE SESSION

DROP USER

INSERT ANY TABLE

UPDATE

SELECT

CREATE ANY

REFERENCES

READ

WRITE

ALL

PUBLIC

ANY

OBJECT

an object privilege

a system privilege

DEFAULT privilege

a role

If the DBA changes the password for a user while the user is connected to the database, the connection terminates automatically.

If the DBA revokes the CREATE SESSION privilege from a user account, the user can’t connect to the database.

If a user is granted the privilege to create a table and the privilege is revoked after the user creates a table, the table is dropped from the system automatically.

all of the above

DELETE ROLE receptionist;

DROP receptionist;

DROP ANY ROLE;

none of the above

SESSION_PRIVS

SYS_PRIVILEGE_MAP

V$SYSTEM_PRIVILEGES

SYSTEM_PRIVILEGE_MAP

SET DEFAULT ROLE

ALTER ROLE

ALTER SESSION

SET ROLE

DELETE ANY

INSERT ANY

UPDATE ANY

REFERENCE

SELECT address || city || state || zip "Address" FROM customers
WHERE lastname ¼ 'SMITH';

SELECT * FROM publisher ORDER BY contact;

SELECT address, city, state, zip FROM customers
WHERE lastname ¼ "SMITH";

All the above statements are valid and return the expected results.

SELECT

FROM

WHERE

ORDER BY

SELECT order# FROM orders WHERE shipdate ¼ NULL;

SELECT order# FROM orders WHERE shipdate ¼ 'NULL';

SELECT order# FROM orders WHERE shipdate ¼ "NULL";

None of the statements are valid.

SELECT name FROM customers
ORDER BY desc state, city;

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

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

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

SELECT firstname, lastname FROM customers
ORDER BY 5 desc, 6 desc;

SELECT lastname FROM customers WHERE lastname ¼ "THOMPSON";

SELECT * FROM customers;

SELECT lastname FROM customers WHERE lastname 4 'R';

SELECT * FROM customers WHERE lastname 5 'V';

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

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

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

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

SELECT * FROM books WHERE pubid = 1, retail >= $25.00;

ascending

descending

the order in which records are stored in the table

There’s no default sort sequence.

SELECT * FROM books WHERE pubid = 2, retail >= $35.00;

SELECT * FROM books WHERE pubid = 2 AND NOT retail < 35;

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

All the above statements display the specified books.

None of the above statements display the specified boo

SELECT * FROM customers WHERE firstname = 'B%';

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

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

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

ESCAPE

?

_

%

none of the above

SELECT * FROM books WHERE title = 'H_N_%';

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

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

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

WHERE

SELECT

SORT

ORDER

none of the above

SELECT * FROM books WHERE pubdate > 03–20–2005;

SELECT * FROM books WHERE pubdate > '03–20–2005';

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

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

SELECT * FROM books WHERE category = 'FITNESS' OR pubid = 4
AND pubdate < '06–02–2004';

SELECT * FROM books WHERE category = 'FITNESS' AND pubid = 4
OR pubdate < '06–02–2004';

SELECT * FROM books WHERE category = 'FITNESS' OR (pubid = 4
AND pubdate < '06–02–2004');

SELECT * FROM books WHERE category ¼ 'FITNESS'
OR pubid = 4, pubdate < '06–02–04';

none of the above

SELECT * FROM orders WHERE orderdate < '04–05–09'
AND shipdate = NULL;

SELECT * FROM orders WHERE orderdate < '05–04–09'
AND shipdate IS NULL;

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

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

none of the above

*

?

%

_

SELECT * FROM books WHERE retail-cost > 12;

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

SELECT * FROM books WHERE profit >= 12;

SELECT * FROM books WHERE retail-cost =>12.00;

none of the above

SELECT * FROM books WHERE profit => 10.00
ORDER BY "Profit" desc;

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

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

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

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

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

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

SELECT * FROM books WHERE title = '%AS_E%'

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

SELECT * FROM books WHERE pubdate > 03–20–2005;

SELECT * FROM books WHERE pubdate > '03–20–2005';

SELECT * FROM books WHERE pubdate NOT < '20–MAR–05';

SELECT * FROM books WHERE pubdate NOT < 'MAR–20–05';

none of the above

SELECT title, authorid
FROM books, bookauthor;

SELECT title, name
FROM books CROSS JOIN publisher;

SELECT title, gift
FROM books NATURAL JOIN promotion;

all of the above

AND

=

OR

>

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

SELECT title, name
FROM books CROSS JOIN publisher;

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

none of the above

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

SELECT title, name
FROM books JOIN publisher
USING (pubid);

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

none of the above

equality

self-join

non-equality

outer join

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

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

SELECT b.title, b.retail, o.quantity
FROM books b, orderitems o
WHERE o.isbn = b.isbn
AND o.order#=1005;

none of the above

SELECT zip, order#
FROM customers JOIN orders
WHERE customers.customer# = orders.customer#;

SELECT zip, order#
FROM customers, orders
WHERE customers.customer# = orders.customer#;

SELECT zip, order#
FROM customers, orders
WHERE customers.customer# = orders.customer# (+);

none of the above

line 1

line 2

line 3

line 4

line 5

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

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

SELECT lastname, firstname, order#
FROM customers c, orders o
WHERE c.customer# = o.customer# (+)
ORDER BY c.customer#;

none of the above

SELECT zip FROM customers
UNION
SELECT category FROM books;

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;

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;

all of the above

none of the above

line 1

line 2

line 3

line 4

line 5

SELECT title, gift
FROM books NATURAL JOIN promotion;

SELECT title
FROM books INTERSECT
SELECT gift
FROM promotion;

SELECT title
FROM books UNION ALL
SELECT gift
FROM promotion;

all of the above

0

8

7

15

56

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

SELECT isbn, name
FROM books b, publisher p
WHERE b.pubid = p.pubid;

SELECT isbn, name
FROM books b JOIN publisher p
ON b.pubid = p.pubid;

SELECT isbn, name
FROM books JOIN publisher
USING (pubid);

None—all the above are valid SQL statements.

SELECT title
FROM books NATURAL JOIN publisher
WHERE name = 'PRINTING IS US';

SELECT title
FROM books, publisher
WHERE pubname = 1;

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

none of the above

SELECT isbn
FROM books
MINUS
SELECT isbn
FROM orderitems;

SELECT isbn, name
FROM books, publisher
WHERE books.pubid (+) = publisher.pubid (+);

SELECT title, name
FROM books NATURAL JOIN publisher

All the above SQL statements are valid.

If the relationship between the tables is established with a WHERE clause, both tables can include the outer join operator.

To include unmatched records in the results, the record is paired with a NULL record in the deficient table.

The RIGHT, LEFT, and FULL keywords are equivalent.

all of the above

none of the above

line 1

line 3

line 4

line 5

10

30

255

256

SELECT books.title, orderitems.quantity
FROM books b, orderitems o
WHERE b.isbn= o.ibsn;

SELECT title, quantity
FROM books b JOIN orderitems o;

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

none of the above

SELECT SYSDATE;

SELECT UPPER(Hello) FROM dual;

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

all of the above

none of the above

EXTRACT

TRUNC

SUBSTR

INITCAP

SELECT order#, shipdate-orderdate delay
FROM orders;

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

SELECT order#, NVL(shipdate, 0)
FROM orders
WHERE orderdate is NULL;

SELECT order#, NULL(shipdate)
FROM orders;

SELECT "Hello World" FROM dual;

SELECT INITCAP('HELLO WORLD') FROM dual;

SELECT LOWER('HELLO WORLD') FROM dual;

SELECT "Hello World" FROM dual;
(and)
SELECT INITCAP('HELLO WORLD') FROM dual;

none of the above

NVL

TRUNC

NVL2

SUBSTR

NVL & SUBSTR

SUBSTR & NVL2

'HH:MM:SS'

'HH24:SS'

'HH12:MI:SS'

All of the above are valid.

SELECT EXTRACT(phone, -4, 1)
FROM publisher
WHERE name ¼ 'AMERICAN PUBLISHING';

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

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

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

MONTH

MON

MONTH_BETWEEN

none of the above

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

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

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

none of the above

NVL

DECODE

UPPER

SUBSTR

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

SELECT INITCAP(firstname), UPPER(lastname)
FROM customers;

SELECT cost, retail,
TO_CHAR(retail-cost, '$999.99') profit
FROM books;

all of the above

TRIML

PADL

LWIDTH

none of the above

SELECT ROUND(24.37, 2) FROM dual;

SELECT TRUNC(29.99, 2) FROM dual;

SELECT ROUND(29.01, -1) FROM dual;

SELECT TRUNC(29.99, -1) FROM dual;

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

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

SELECT LTRIM(LPAD(state, 5, ' '), 4, -3, "*")
FROM dual;

SELECT SUBSTR(ROUND(14.87, 2, 1), -4, 1)
FROM dual;

UPPER

LOWER

INITIALCAP

All of the above can be used for case conversion.

MMM

MONTH

MON

none of the above

SELECT UPPER('firstname', 'lastname')
FROM customers;

SELECT UPPER(firstname, lastname)
FROM customers;

SELECT UPPER(lastname, ',' firstname)
FROM customers;

none of the above

SUBSTR

NVL2

REPLACE

TRUNC

none of the above

DUMDUM

DUAL

ORAC

SYS

an integer

a whole number

a NULL value

None of the above—a syntax error message is returned.

The MIN function can be used only with numeric data.

The MAX function can be used only with date values.

The AVG function can be used only with numeric data.

The SUM function can’t be part of a nested function.

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

SELECT category, AVG(retail-cost)
FROM books;

SELECT category, AVG(retail-cost)
FROM books
WHERE AVG(retail-cost) > 8.56
GROUP BY category;

SELECT category, AVG(retail-cost) Profit
FROM books
GROUP BY category
HAVING profit > 8.56;

The WHERE clause can contain a group function only if the function isn’t also listed in the SELECT clause.

Group functions can’t be used in the SELECT, FROM, or WHERE clauses.

The HAVING clause is always processed before the WHERE clause.

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

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

SELECT MIN(pubdate)
FROM books
WHERE category = 'COOKING';

SELECT COUNT(*)
FROM orders
WHERE customer# = 1005;

SELECT MAX(COUNT(customer#))
FROM orders
GROUP BY customer#;

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

Only distinct values are included in group functions, unless the ALL keyword is included in the SELECT clause.

The HAVING clause restricts which rows are processed.

The WHERE clause determines which groups are displayed in the query results.

none of the above

SELECT customer#, order#, MAX(shipdate-orderdate)
FROM orders
GROUP BY customer#
WHERE customer# = 1001;

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

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

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

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

SELECT title, MAX(retail-cost)
FROM books
GROUP BY title
HAVING MAX(retail-cost);

SELECT title, MAX(retail-cost)
FROM books;

none of the above

A group function can be nested inside a group function.

A group function can be nested inside a single-row function.

A single-row function can be nested inside a group function.

A group function can be nested inside a group function, and A group function can be nested inside a single-row function.

A group function can be nested inside a group function, and A group function can be nested inside a single-row function, and A single-row function can be nested inside a group function.

COUNT

MIN

TOTAL

SUM

ADD

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

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

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

none of the above

SUM

NVL

MAX

MIN

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

The AVG function can be used to find the average calculated difference between two dates.

The MIN and MAX functions can be used on any type of data.

all of the above

none of the above

SELECT customer#, SUM(referred)
FROM customers
GROUP BY customer#;

SELECT COUNT(referred)
FROM customers;

SELECT COUNT(*)
FROM customers;

SELECT COUNT(*)
FROM customers
WHERE referred IS NULL;

1

3

4

5

1

3

4

5

1

3

4

5

1

3

4

5

the number of records in the specified tables

the number of orders placed by each customer

the number of NULL values in the specified table

the number of customers who have placed an order

COUNT function

MAX function

MIN function

STDDEV function

VARIANCE function

SELECT STDDEV(retail)
FROM books;

SELECT AVG(SUM(retail))
FROM orders
NATURAL JOIN orderitems NATURAL JOIN books
GROUP BY customer#;

SELECT order#, TO_CHAR(SUM(retail),'999.99')
FROM orderitems JOIN books USING (isbn)
GROUP BY order#;

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

SELECT customer# FROM customers
WHERE state = (SELECT state FROM customers
WHERE lastname = 'SMITH');

SELECT customer# FROM customers
WHERE state = (SELECT state FROM customers
WHERE lastname = 'SMITH'
OR firstname = 'LEILA');

SELECT customer# FROM customers
WHERE state = (SELECT state FROM customers
WHERE lastname = 'SMITH'
AND firstname = 'LEILA'
ORDER BY customer);

SELECT customer# FROM customers
WHERE state = (SELECT state FROM customers
WHERE lastname = 'SMITH'
AND firstname = 'LEILA');

SELECT order# FROM orders
WHERE shipdate = SELECT shipdate FROM orders
WHERE order# = 1010;

SELECT order# FROM orders
WHERE shipdate = (SELECT shipdate FROM orders)
AND order# = 1010;

SELECT order# FROM orders
WHERE shipdate = (SELECT shipdate FROM orders
WHERE order# = 1010);

SELECT order# FROM orders
HAVING shipdate = (SELECT shipdate FROM orders
WHERE order# = 1010);

IN

ALL

<>

<>ALL

SELECT order# FROM orders
WHERE customer# = 1017;

SELECT customer# FROM orders
JOIN orderitems USING(order#)
WHERE isbn = (SELECT isbn FROM orderitems
WHERE customer# = 1017);

SELECT customer# FROM orders
WHERE order# = (SELECT order# FROM orderitems
WHERE customer# = 1017);

SELECT customer# FROM orders
JOIN orderitems USING(order#)
WHERE isbn IN (SELECT isbn FROM orderitems
JOIN orders USING(order#)
WHERE customer# = 1017);

SELECT title FROM books
WHERE retail <(SELECT cost FROM books
WHERE isbn = '9959789321');

SELECT title FROM books
WHERE retail = (SELECT cost FROM books
WHERE isbn = '9959789321' ORDER BY cost);

SELECT title FROM books
WHERE category IN (SELECT cost FROM orderitems
WHERE isbn = '9959789321');

none of the above statements

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.

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.

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.

none of the above

SELECT customer# FROM customers
WHERE referred = (SELECT referred FROM customers
WHERE firstname = 'JORGE'
AND lastname = 'PEREZ');

SELECT referred FROM customers
WHERE (customer#, referred) = (SELECT customer#
FROM customers WHERE firstname = 'JORGE'
AND lastname = 'PEREZ');

SELECT referred FROM customers
WHERE (customer#, referred) IN (SELECT customer#
FROM customers WHERE firstname = 'JORGE'
AND lastname = 'PEREZ');

SELECT customer# FROM customers
WHERE customer# = (SELECT customer#
FROM customers WHERE firstname = 'JORGE'
AND lastname = 'PEREZ');

when you need to find all customers living in a particular region of the country

when you need to find all publishers who have toll-free telephone numbers

when you need to find the titles of all books shipped on the same date as an order placed by a particular customer

when you need to find all books published by Publisher

SELECT customer# FROM orders
JOIN books USING(isbn)
WHERE isbn = (SELECT isbn FROM orderitems
JOIN books USING(isbn)
WHERE customer# = 1001 OR customer# = 1005));

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));

SELECT customer# FROM orders
JOIN books USING(isbn)
WHERE isbn = (SELECT isbn FROM orderitems
JOIN orders USING(order#)
WHERE customer# = 1001 OR 1005));

SELECT customer# FROM orders
JOIN orderitems USING(order#)
WHERE isbn IN (SELECT isbn FROM orders
JOIN orderitems USING(order#)
WHERE customer# IN (1001, 1005));

>ALL

<ALL

>ANY

<ANY

IN

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#;

SELECT customer# FROM orders
JOIN orderitems USING(order#)
WHERE SUM(quantity) = (SELECT
MAX(SUM(quantity)) FROM orderitems
GROUP BY customer#);

SELECT customer# FROM orders
WHERE MAX(SUM(quantity)) = (SELECT
MAX(SUM(quantity) FROM orderitems
GROUP BY order#);

SELECT customer# FROM orders
HAVING quantity = (SELECT MAX(SUM(quantity))
FROM orderitems
GROUP BY customer#);

The IN comparison operator can't be used with a subquery that returns only one row of results.

The equals (=) comparison operator can't be used with a subquery that returns more than one row of results.

In an uncorrelated subquery, statements in the outer query are executed first, and then statements in the subquery are executed.

A subquery can be nested only in the outer query's SELECT clause.

It determines which publisher published a book belonging to the Oracle category and then lists all other books published by that same publisher.

It lists all publishers and categories containing the value ORACLE.

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.

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.

The inner query needs to reference the value returned to the outer query.

The value returned by the inner query is to be compared to grouped data in the outer
query.

The subquery returns more than one value to the outer query.

None of the above. Subqueries can't be used in the outer query's HAVING clause.

SELECT title FROM books
WHERE isbn IN (SELECT isbn FROM bookauthor
HAVING authorid IN 'THE WOK WAY TO COOK);

SELECT isbn FROM bookauthor
WHERE authorid IN (SELECT authorid FROM books
JOIN bookauthor USING(isbn)
WHERE title = 'THE WOK WAY TO COOK');

SELECT title FROM bookauthor
WHERE authorid IN (SELECT authorid FROM books
JOIN bookauthor USING(isbn)
WHERE title = 'THE WOK WAY TO COOK);

SELECT isbn FROM bookauthor
HAVING authorid = SELECT authorid FROM books
JOIN bookauthor USING(isbn)
WHERE title = 'THE WOK WAY TO COOK';

If the subquery returns only a NULL value, the only records returned by an outer query are those containing an equivalent NULL value.

A multiple-column subquery can be used only in the outer query's FROM clause.

A subquery can contain only one condition in its WHERE clause.

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.

USING

WHEN MATCHED

WHEN NOT MATCHED

INSERTs aren't allowed in a MERGE statement.

The statement doesn't execute because the subquery and outer query don't reference the same table.

The outer query removes duplicates in the subquery's Order# list.

The query fails if only one result is returned to the outer query because the outer query's WHERE clause uses the IN comparison operat

No rows are displayed because the ISBN in the WHERE clause is enclosed in single quotation marks.

The SELECT statement fails and returns an Oracle error message.

The outer query displays no rows in its results because the subquery passes a NULL value to the outer query

The customer number is displayed for customers whose orders haven't yet shipped.

The customer number of all customers who haven't placed an order are displayed.

EXISTS

IN

LINK

MERGE

No DML operations can be performed on the CHANGEADDRESS view.

The CHANGEADDRESS view is a simple view.

The CHANGEADDRESS view is a complex view.

The CHANGEADDRESS view is an inline view.

UPDATE changeaddress
SET shipstreet = '958 ELM ROAD'
WHERE customer# = 1020;

INSERT INTO changeaddress
VALUES (9999, 'LAST', 'FIRST', 9999,
'123 HERE AVE', 'MYTOWN', 'AA', 99999);

DELETE FROM changeaddress
WHERE customer# = 1020;

all of the above

none of the above

CUSTOMERS table

ORDERS table

Both tables together serve as a composite key-preserved table.

none of the above