Which of the following is a correct statement?
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.
Which of the following is a valid SQL statement?
SELECT * WHERE amid = ‘J100’ FOR UPDATE;
INSERT INTO homework10 VALUES (SELECT * FROM acctmanager);
DELETE amid FROM acctmanager;
rollback;
all of the above
Which of the following commands can be used to add rows to a table?
INSERT INTO
ALTER TABLE ADD
UPDATE
SELECT . . . FOR UPDATE
Which of the following statements deletes all rows in the HOMEWORK10 table?
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.
Which of the following statements places a shared lock on at least a portion of a table named HOMEWORK10?
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’;
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 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;
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’
‘O’’hara’ (two single quotes following the O)
‘O’’hara’ (a double quote following the O)
Data values can’t contain quotes.
Which of the following commands locks the HOMEWORK10 table in EXCLUSIVE mode?
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.
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
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.
Which of the following releases a lock currently held by a user on the HOMEWORK10 table?
A COMMIT command is issued.
A DDL command is issued to end a transaction.
The user exits the system.
A ROLLBACK command is issued.
none 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 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.
Which of the following commands removes all orders placed before April 1, 2009?
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’;
How many rows can be added to a table by executing the INSERT INTO . . . VALUES command?
1
2
3
unlimited
You accidentally deleted all the orders in the ORDERS table. How can the error be corrected after a COMMIT command has been issued?
ROLLBACK;
ROLLBACK COMMIT;
REGENERATE RECORDS orders;
None of the above restores the deleted orders.
Which of the following is the standard extension used for a script file?
.spt
.srt
.script
.sql
A rollback occurs automatically when:
A DDL command is executed.
A DML command is executed.
What is the maximum number of rows that can be deleted from a table at one time?
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.
What is the maximum number of records that can be modified with a single UPDATE command?
Which of the following generates a series of integers that can be stored in a database?
a number generator
a view
a sequence
an index
a synonym
Which syntax is correct for removing a public synonym?
DROP SYNONYM synonymname;
DELETE PUBLIC SYNONYM synonymname;
DROP PUBLIC SYNONYM synonymname;
DELETE SYNONYM synonymname;
Which of the following commands can you use to modify an index?
ALTER SESSION
ALTER TABLE
MODIFY INDEX
ALTER INDEX
Which of the following generates an integer in a sequence?
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;
Suppose the user Juan creates a table called MYTABLE with four columns. The first column has a PRIMARY KEY constraint, the second column has a NOT NULL constraint, the third column has a CHECK constraint, and the fourth column has a FOREIGN KEY constraint. Given this information, how many indexes does Oracle 11g create automatically when the table and constraints are created?
0
4
Given the table created in Question 6, which of the following commands can Juan use to create a synonym that allows anyone to access the table without having to identify his schema in the table reference?
CREATE SYNONYM thetable FOR juan.mytable;
CREATE PUBLIC SYNONYM thetable FOR mytable;
CREATE SYNONYM juan FOR mytable;
Which of the following statements is true?
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 is creating an index manually inappropriate?
when queries return a large percentage of rows in the results
when the table is small
when the majority of table operations are updates
when queries return a large percentage of rows in the results and when the majority of table operations are updates
If a column has high selectivity or cardinality, which index type is most appropriate?
IOT
B-tree
bitmap
function-based index
If a column has low selectivity, this means:
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.
Oracle 11g automatically creates an index for which type of constraints?
NOT NULL
PRIMARY KEY
FOREIGN KEY
UNIQUE KEY
NOT NULL and PRIMARY KEY
PRIMARY KEY and UNIQUE KEY
Which of the following settings can’t be modified with the ALTER SEQUENCE command?
INCREMENT BY
MAXVALUE
START WITH
MINVALUE
CACHE
Which node of the b-tree index contains ROWIDs?
branch blocks
root block
leaf blocks
None of the above because the primary key is used to identify rows.
If the CACHE or NOCACHE options aren’t included in the CREATE SEQUENCE command, which of the following statements is correct?
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.
Which of the following is a valid command?
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;
Which of the following can be used to determine whether an index exists?
DESCRIBE indexname;
the USER_INDEXES view
the USER_INDEX view
Which of the following isn’t a valid option for the CREATE SEQUENCE command?
ORDER
NOCYCLE
MINIMUMVAL
NOCACHE
All of the above are valid options.
What can be referenced to determine whether an index is used to perform a query?
USER_INDEXES view
query source code
explain plan
database access plan
Which of the following commands creates a private synonym?
CREATE PRIVATE SYNONYM
CREATE NONPUBLIC SYNONYM
CREATE SYNONYM
CREATE PUBLIC SYNONYM
Which of the following commands can be used to change a password for a user account?
ALTER PASSWORD
CHANGE PASSWORD
MODIFY USER PASSWORD
ALTER USER . . . PASSWORD
Which of the following statements assigns the role CUSTOMERREP as the default role for Maurice Cain?
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;
Which of the following statements is most accurate?
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.
Which of the following statements creates a user account named DeptHead?
CREATE ROLE depthead IDENTIFIED BY apassword;
CREATE USER depthead IDENTIFIED BY apassword;
CREATE ACCOUNT depthead;
GRANT ACCOUNT depthead;
Which of the following privileges must be granted to a user’s account before the user can connect to the Oracle 11g database?
CONNECT
CREATE SESSION
CONNECT ANY DATABASE
CREATE ANY TABLE
Which of the following privileges allows a user to truncate tables in a database?
DROP ANY TABLE
TRUNCATE ANY TABLE
CREATE TABLE
TRUNC TABLE
Which of the following tables or views displays the current enabled privileges for a user?
SESSION_PRIVS
SYSTEM_PRIVILEGE_MAP
USER_ASSIGNED_PRIVS
V$ENABLED_PRIVILEGES
Which of the following commands eliminates only the user ELOPEZ’s ability to enter new books in the BOOKS table?
REVOKE insert ON books FROM elopez;
REVOKE insert FROM elopez;
REVOKE INSERT INTO FROM elopez;
DROP insert INTO books FROM elopez;
Which of the following commands is used to assign a privilege to a role?
CREATE ROLE
CREATE PRIVILEGE
GRANT
ALTER PRIVILEGE
Which of the following options requires a user to change his or her password at the next login?
CREATE USER
ALTER USER
IDENTIFIED BY
PASSWORD EXPIRE
Which of the following options allows a user to grant system privileges to other users?
WITH ADMIN OPTION
WITH GRANT OPTI
DBA
ASSIGN ROLES
SET ROLE
Which of the following is an object privilege?
DROP USER
INSERT ANY TABLE
Which of the following privileges can be granted only to a user, not to a role?
SELECT
CREATE ANY
REFERENCES
READ
WRITE
Which of the following is used to grant all object privileges for an object to a specified user?
ALL
PUBLIC
ANY
OBJECT
Which of the following identifies a collection of privileges?
an object privilege
a system privilege
DEFAULT privilege
a role
Which of the following is true?
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.
Which of the following commands can be used to eliminate the RECEPTIONIST role?
DELETE ROLE receptionist;
DROP receptionist;
DROP ANY ROLE;
Which of the following displays a list of all system privileges available in Oracle 11g?
SYS_PRIVILEGE_MAP
V$SYSTEM_PRIVILEGES
Which of the following can be used to change the role that’s currently enabled for a user?
SET DEFAULT ROLE
ALTER ROLE
DELETE ANY
INSERT ANY
UPDATE ANY
REFERENCE
Which of the following SQL statements isn’t valid?
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.
Which clause is used to restrict rows or perform selection?
FROM
WHERE
ORDER BY
Which of the following SQL statements is valid?
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.
Which of the following returns a list of all customers’ names sorted in descending order by city within state?
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;
Which of the following doesn’t return a customer with the last name THOMPSON in the query results?
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';
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;
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;
What’s the default sort sequence for the ORDER BY clause?
ascending
descending
the order in which records are stored in the table
There’s no default sort sequence.
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 = 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
Which of the following includes a customer with the first name BONITA in the results?
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%';
Which of the following represents exactly one character in a pattern search?
ESCAPE
?
%
Which of the following returns the book HANDCRANKED COMPUTERS in the results?
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%';
Which of the following clauses is used to display query results in a sorted order?
SORT
Which of the following SQL statements returns all books published after March 20, 2005?
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';
Which of the following lists all books published before June 2, 2004 and all books published by Publisher 4 or in the Fitness category?
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';
Which of the following finds all orders placed before April 5, 2009 that haven’t yet shipped?
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;
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;
SELECT * FROM books WHERE retail-cost <= 12;
SELECT * FROM books WHERE profit >= 12;
SELECT * FROM books WHERE retail-cost =>12.00;
Which of the following lists each book having a profit of at least $10.00 in descending order by profit?
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;
Which of the following includes the book HOW TO GET FASTER PIZZA in the query results?
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%';
Which of the following returns all books published after March 20, 2005?
SELECT * FROM books WHERE pubdate NOT < '20–MAR–05';
SELECT * FROM books WHERE pubdate NOT < 'MAR–20–05';
Which of the following queries creates a Cartesian join?
SELECT title, authorid FROM books, bookauthor;
SELECT title, name FROM books CROSS JOIN publisher;
SELECT title, gift FROM books NATURAL JOIN promotion;
Which of the following operators is not allowed in an outer join?
AND
=
OR
>
Which of the following queries contains an equality join?
SELECT title, authorid FROM books, bookauthor WHERE books.isbn = bookauthor.isbn AND retail > 20;
SELECT title, gift FROM books, promotion WHERE retail >= minretail AND retail <= maxretail;
Which of the following queries contains a non-equality join?
SELECT title, name FROM books JOIN publisher USING (pubid);
The following SQL statement contains which type of join? SELECT title, order#, quantity FROM books FULL JOIN orderitems ON books.isbn = orderitems.isbn;
equality
self-join
non-equality
outer join
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;
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;
Given the following query: SELECT zip, order# FROM customers NATURAL JOIN orders;
Which of the following queries is equivalent?
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# (+);
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 1
line 2
line 3
line 4
line 5
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#;
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#;
Given the following query: SELECT DISTINCT zip, category FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books;
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;
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
Given the following query: SELECT title, gift FROM books CROSS JOIN promotion;
SELECT title FROM books INTERSECT SELECT gift FROM promotion;
SELECT title FROM books UNION ALL SELECT gift FROM promotion;
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;
8
7
15
56
Which of the following SQL statements is not valid?
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.
Which of the following lists all books published by the publisher named Printing Is Us?
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';
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.
Which of the following statements about an outer join between two tables is true?
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.
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);
What is the maximum number of characters allowed in a table alias?
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;
SELECT SYSDATE;
SELECT UPPER(Hello) FROM dual;
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?
EXTRACT
TRUNC
SUBSTR
INITCAP
Which of the following determines how long ago orders that haven’t shipped were received?
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;
Which of the following SQL statements produces “Hello World” as the output?
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;
Which of the following functions can be used to substitute a value for a NULL value?
NVL
NVL2
NVL & SUBSTR
SUBSTR & NVL2
Which of the following is not a valid format argument for displaying the current time?
'HH:MM:SS'
'HH24:SS'
'HH12:MI:SS'
All of the above are valid.
Which of the following lists only the last four digits of the contact person’s phone number at American Publishing?
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';
Which of the following functions can be used to determine how many months a book has been available?
MONTH
MON
MONTH_BETWEEN
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;
SELECT TO_CHAR(orderdate, 'Mth/DD') FROM orders WHERE order# = 1000;
SELECT TO_CHAR(orderdate, 'MONTH/YY') FROM orders WHERE order# = 1000;
Which of the following functions can produce different results, depending on the value of a specified column?
DECODE
UPPER
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;
Which function can be used to add spaces to a column until it’s a specific width?
TRIML
PADL
LWIDTH
Which of the following SELECT statements returns 30 as the result?
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;
Which of the following functions can’t be used to convert the letter case of a character string?
LOWER
INITIALCAP
All of the above can be used for case conversion.
Which of the following format elements causes months to be displayed as a three-letter abbreviation?
MMM
Which of the following SQL statements displays a customer’s name in all uppercase characters?
SELECT UPPER('firstname', 'lastname') FROM customers;
SELECT UPPER(firstname, lastname) FROM customers;
SELECT UPPER(lastname, ',' firstname) FROM customers;
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 11g for completing queries that don’t involve a table?
DUMDUM
DUAL
ORAC
SYS
If an integer is multiplied by a NULL value, the result is:
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.
Which of the following is a valid SELECT statement?
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;
Which of the following statements is correct?
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.
Which of the following is not a valid SQL statement?
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.
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#;
Which of the following SELECT statements lists only the book with the largest profit?
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;
Which of the following is correct?
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.
Which of the following functions is used to calculate the total value stored in a specified column?
COUNT
MIN
TOTAL
SUM
ADD
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';
SELECT MAX(retail) FROM books HAVING category = 'FAMILY';
SELECT retail FROM books WHERE category = 'FAMILY' HAVING MAX(retail);
Which of the following functions can be used to include NULL values in calculations?
MAX
Which of the following is not a valid statement?
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.
Which of the following SQL statements determines how many total customers were referred by other customers?
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 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?
5
Which line of the SELECT statement is used to restrict groups displayed in the query results?
Which line of the SELECT statement is used to group data stored in the database?
Because the SELECT clause contains the Customer# column, which clause must be included for the query to execute successfully?
The COUNT(*) function in the SELECT clause is used to return:
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
Which of the following functions can be used to determine the earliest ship date for all orders recently processed by JustLee Books?
COUNT function
MAX function
MIN function
STDDEV function
VARIANCE function
Which of the following is not a valid SELECT statement?
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;
Which query identifies customers living in the same state as the customer named Leila Smith?
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);
Which of the following operators is considered a single-row operator?
IN
<>
<>ALL
Which of the following queries determines which customers have ordered the same books as customer 1017?
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);
Which of the following statements is valid?
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.
Which of the following queries identifies other customers who were referred to JustLee Books by the same person who referred Jorge Perez?
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');
In which of the following situations is using a subquery suitable?
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
Which of the following queries identifies customers who have ordered the same books as customers 1001 and 1005?
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));
Which of the following operators is used to find all values greater than the highest value returned by a subquery?
>ALL
<ALL
>ANY
<ANY
Which query determines the customers who have ordered the most books from JustLee Books?
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.
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%');
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.
A subquery must be placed in the outer query's HAVING clause if:
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.
Which of the following SQL statements lists all books written by the author of The Wok Way to Cook?
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.
In a MERGE statement, an INSERT is placed in which conditional clause?
USING
WHEN MATCHED
WHEN NOT MATCHED
INSERTs aren't allowed in a MERGE statement.
Given the following query, which statement is correct? SELECT order# FROM orders WHERE order# IN (SELECT order# FROM orderitems WHERE isbn = '9959789321');
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.
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);
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.
Which operator is used to process a correlated subquery?
EXISTS
LINK
MERGE
CREATE VIEW changeaddress AS SELECT customer#, lastname, firstname, order#, shipstreet, shipcity, shipstate, shipzip FROM customers JOIN orders USING (customer#) WHERE shipdate IS NULL WITH CHECK OPTION;
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.
Assuming there’s only a primary key, and FOREIGN KEY constraints exist on the underlying tables, which of the following commands returns an error message?
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;
Which of the following is the key-preserved table for the CHANGEADDRESS view?
CUSTOMERS table
ORDERS table
Both tables together serve as a composite key-preserved table.
Which of the following columns serves as the primary key for the CHANGEADDRESS view?
Customer#
Lastname
Firstname
Order#
Shipstreet
If a record is deleted from the CHANGEADDRESS view based on the Customer# column, the customer information is then deleted from which underlying table?
CUSTOMERS
ORDERS
CUSTOMERS and ORDER
Neither—the DELETE command can’t be used on the CHANGEADDRESS view.
ROWNUM can’t be used with the view because it isn’t included in the results the subquery returns.
The view is a simple view because it doesn’t include a group function or a GROUP BY clause.
The data in the view can’t be displayed in descending order by customer number because an ORDER BY clause isn’t allowed when working with views.
Assuming one of the orders has shipped, which of the following is true?
The CHANGEADDRESS view can’t be used to update an order’s ship date because of the WITH CHECK OPTION constraint.
The CHANGEADDRESS view can’t be used to update an order’s ship date because the Shipdate column isn’t included in the view.
The CHANGEADDRESS view can’t be used to update an order’s ship date because the ORDERS table is not the key-preserved table.
The CHANGEADDRESS view can’t be used to update an order’s ship date because the UPDATE command can’t be used on data in the view.
CREATE VIEW changename AS SELECT customer#, lastname, firstname FROM customers WITH CHECK OPTION; Assume that the only constraint on the CUSTOMERS table is a PRIMARY KEY constraint.
No DML operations can be performed on the CHANGENAME view.
The CHANGENAME view is a simple view.
The CHANGENAME view is a complex view.
The CHANGENAME view is an inline view.
Which of the following columns serves as the primary key for the CHANGENAME view?
The view doesn’t have or need a primary key.
Which of the following DML operations could never be used on the CHANGENAME view?
INSERT
DELETE
All of the above are valid DML operations for the CHANGENAME view
The INSERT command can’t be used with the CHANGENAME view because:
A key-preserved table isn’t included in the view.
The view was created with the WITH CHECK OPTION constraint.
The inserted record couldn’t be accessed by the view.
None of the above—an INSERT command can be used on the table as long as the PRIMARY KEY constraint isn’t violated.
If the CHANGENAME view needs to include the customer’s zip code as a means of verifying the change (that is, to authenticate the user), which of the following is true?
The CREATE OR REPLACE VIEW command can be used to re-create the view with the necessary column included in the new view.
The ALTER VIEW . . . ADD COLUMN command can be used to add the necessary column to the existing view.
The CHANGENAME view can be dropped, and then the CREATE VIEW command can be used to re-create the view with the necessary column included in the new view.
All of the above can be performed to include the customer’s zip code in the view.
Only (The CREATE OR REPLACE VIEW command can be used to re-create the view with the necessary column included in the new view.) and (The CHANGENAME view can be dropped, and then the CREATE VIEW command can be used to re-create the view with the necessary column included in the new view.) include the customer’s zip code in the view.
None of the above includes the customer’s zip code in the view.
Which of the following DML operations can’t be performed on a view containing a group function?
All of the above can be performed on a view containing a group function
None of the above can be performed on a view containing a group function.
You can’t perform any DML operations on which of the following?
views created with the WITH READ ONLY option
views that include the DISTINCT keyword
views that include a GROUP BY clause
All of the above allow DML operations.
None of the above allow DML operations.
A TOP-N analysis is performed by determining the rows with:
the highest ROWNUM values
a ROWNUM value greater than or equal to N
the lowest ROWNUM values
a ROWNUM value less than or equal to N
To assign names to the columns in a view, you can do which of the following?
Assign aliases in the subquery, and the aliases are used for the column names.
Use the ALTER VIEW command to change column names.
Assign names for up to three columns in the CREATE VIEW clause before the subquery is listed in the AS clause.
None of the above—columns can’t be assigned names for a view; they must keep their original names.
The ORDER BY clause can’t be used in the subquery of a CREATE VIEW command.
The ORDER BY clause can’t be used in an inline view.
The DISTINCT keyword can’t be used in an inline view.
The WITH READ ONLY option must be used with an inline view.
If you try to add a row to a complex view that includes a GROUP BY clause, you get which of the following error messages?
virtual column not allowed here
data manipulation operation not legal on this view
cannot map to a column in a non-key-preserved table
None of the above—no error message is returned.
A simple view can contain which of the following?
data from one or more tables
an expression
a GROUP BY clause for data retrieved from one table
five columns from one table
A complex view can contain which of the following?
