Which of the following is a correct statement?

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 implicitly when a DDL command is executed.

A commit is issued automatically when a DML 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);

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

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 (col3, col1, col2) VALUES (NULL, ‘A’, ‘B’);

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

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

UPDATE homework10 SET col1 = col3;

Which of the following input values results in a successful INSERT of 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 IN EXCLUSIVE MODE;

LOCK TABLE homework10 EXCLUSIVELY;

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

Col3 is defined as a DATE column.

Col1 has a PRIMARY KEY constraint enabled.

Col2 has a UNIQUE constraint enabled.

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 DDL command is issued to end a transaction.

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.

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

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 a correct statement?

None of the above statements is correct.

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.

Which syntax is correct for removing a public synonym?

DROP PUBLIC SYNONYM synonymname;

DELETE PUBLIC SYNONYM synonymname;

Which of the following is a valid SQL statement?

CREATE SYNONYM pub FOR publisher;

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

CREATE INDEX a_new_index ON (firstcolumn*.02);

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 PUBLIC SYNONYM thetable FOR mytable;

CREATE SYNONYM thetable FOR juan.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 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 low selectivity, this means:

The column contains a small number of distinct values.

The column contains many distinct values.

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

The selectivity of a column can’t be determined.

Which node of the b-tree index contains ROWIDs?

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

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 isn’t a valid option for the CREATE SEQUENCE command?

All of the above are valid options.

Which of the following statements assigns the role CUSTOMERREP as the default role for Maurice Cain?

ALTER USER mcain DEFAULT ROLE customerrep

ALTER ROLE mcain DEFAULT ROLE customerrep;

ALTER USER mcain TO customerrep;

SET DEFAULT ROLE customerrep FOR mcain;

SET ROLE customerrep FOR mcain;

Which of the following statements is most accurate?

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

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

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 USER depthead IDENTIFIED BY apassword;

CREATE ROLE depthead IDENTIFIED BY apassword;

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 INTO FROM elopez;

DROP insert INTO books FROM elopez;

Quiz

Midterm 2 (Chapter 5 - 13)

Yorria Raine 180 perguntas

Fazer o quiz

Perguntas do quiz (180)

1Which of the following is a correct statement?
1. AA commit is issued implicitly when a user exits SQL Developer or SQL*Plus.
2. BA commit is issued implicitly when a DDL command is executed.
3. CA commit is issued automatically when a DML command is executed.
4. DAll of the above are correct.
5. EA commit is issued implicitly when a user exits SQL Developer or SQL*Plus, and when a DDL command is executed.
6. FA commit is issued implicitly when a user exits SQL Developer or SQL*Plus. A commit is issued automatically when a DML command is executed.
2Which of the following is a valid SQL statement?
1. ASELECT * WHERE amid = ‘J100’ FOR UPDATE;
2. BINSERT INTO homework10 VALUES (SELECT * FROM acctmanager);
3. CDELETE amid FROM acctmanager;
4. Drollback;
5. Eall of the above
3Which of the following commands can be used to add rows to a table?
1. AINSERT INTO
2. BALTER TABLE ADD
3. CUPDATE
4. DSELECT . . . FOR UPDATE
4Which of the following statements deletes all rows in the HOMEWORK10 table?
1. ADELETE * FROM homework10;
2. BDELETE *.* FROM homework10;
3. CDELETE FROM homework10;
4. DDELETE FROM homework10 WHERE amid = ‘*’;
5. EBoth (DELETE FROM homework10;) and (DELETE FROM homework10 WHERE amid = ‘*’;) delete all rows in the HOMEWORK10 table.
5Which of the following statements places a shared lock on at least a portion of a table named
HOMEWORK10?
1. ASELECT * FROM homework10 WHERE col2 IS NULL FOR UPDATE;
2. BINSERT INTO homework10 (col1, col2, col3) VALUES (‘A’, ‘B’, ‘C’);
3. CUPDATE homework10 SET col3 = NULL WHERE col1 = ‘A’;
4. DUPDATE homework10 SET col3 = LOWER (col3) WHERE col1 = ‘A’;
5. Eall of the above
6Assuming 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?
1. AINSERT INTO homework10 VALUES (‘A’, ‘B’, ‘C’);
2. BINSERT INTO homework10 (col3, col1, col2) VALUES (NULL, ‘A’, ‘B’);
3. CINSERT INTO homework10 VALUES (NULL, ‘A’, ‘B’);
4. DUPDATE homework10 SET col1 = col3;
7Which of the following symbols designates a substitution variable?
1. A&
2. B$
3. C#
4. D_
8Which of the following input values results in a successful INSERT of O’hara?
1. A‘O^hara’
2. B‘O’’hara’ (two single quotes following the O)
3. C‘O’’hara’ (a double quote following the O)
4. DData values can’t contain quotes.
9Which of the following commands locks the HOMEWORK10 table in EXCLUSIVE mode?
1. ALOCK TABLE homework10 EXCLUSIVELY;
2. BLOCK TABLE homework10 IN EXCLUSIVE MODE;
3. CLOCK TABLE homework10 TO OTHER USERS;
4. DLOCK homework10 IN EXCLUSIVE MODE;
5. EBoth b and d lock the table in EXCLUSIVE mode.
10You 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
1. ACol1 has a PRIMARY KEY constraint enabled.
2. BCol2 has a UNIQUE constraint enabled.
3. CCol3 is defined as a DATE column.
4. DNone of the above would cause the command to fail.

11Which of the following releases a lock currently held by a user on the HOMEWORK10 table?
1. AA COMMIT command is issued.
2. BA DDL command is issued to end a transaction.
3. CThe user exits the system.
4. DA ROLLBACK command is issued.
5. Eall of the above
6. Fnone of the above
12Assume you have added eight new orders to the ORDERS table. Which of the following is true?
1. AOther users can view the new orders as soon as you execute the INSERT INTO command.
2. BOther users can view the new orders as soon as you issue a ROLLBACK command.
3. COther users can view the new orders as soon as you exit the system or execute a COMMIT command.
4. DOther users can view the new orders only if they place an exclusive lock on the table.
13Which of the following commands removes all orders placed before April 1, 2009?
1. ADELETE FROM orders WHERE orderdate < ‘01-APR-09’;
2. BDROP FROM orders WHERE orderdate < ‘01-APR-09’;
3. CREMOVE FROM orders WHERE orderdate < ‘01-APR-09’;
4. DDELETE FROM orders WHERE orderdate > ‘01-APR-09’;
14How many rows can be added to a table by executing the INSERT INTO . . . VALUES command?
1. A1
2. B2
3. C3
4. Dunlimited
15You accidentally deleted all the orders in the ORDERS table. How can the error be corrected after a COMMIT command has been issued?
1. AROLLBACK;
2. BROLLBACK COMMIT;
3. CREGENERATE RECORDS orders;
4. DNone of the above restores the deleted orders.
16Which of the following is the standard extension used for a script file?
1. A.spt
2. B.srt
3. C.script
4. D.sql
17A rollback occurs automatically when:
1. AA DDL command is executed.
2. BA DML command is executed.
3. CThe user exits the system.
4. Dnone of the above
18What is the maximum number of rows that can be deleted from a table at one time?
1. A1
2. B2
3. C3
4. Dunlimited
19Which of the following is a correct statement?
1. AIf 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.
2. BA subquery nested in the VALUES clause of an INSERT INTO command can return only one value without generating an Oracle 11g error message.
3. CIf 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.
4. DNone of the above statements is correct.
20What is the maximum number of records that can be modified with a single UPDATE command?
1. A1
2. B2
3. C3
4. Dunlimited
21Which of the following generates a series of integers that can be stored in a database?
1. Aa number generator
2. Ba view
3. Ca sequence
4. Dan index
5. Ea synonym
22Which syntax is correct for removing a public synonym?
1. ADROP SYNONYM synonymname;
2. BDELETE PUBLIC SYNONYM synonymname;
3. CDROP PUBLIC SYNONYM synonymname;
4. DDELETE SYNONYM synonymname;
23Which of the following commands can you use to modify an index?
1. AALTER SESSION
2. BALTER TABLE
3. CMODIFY INDEX
4. DALTER INDEX
5. Enone of the above
24Which of the following generates an integer in a sequence?
1. ANEXTVAL
2. BCURVAL
3. CNEXT_VALUE
4. DCURR_VALUE
5. ENEXT_VAL
6. FCUR_VAL
25Which of the following is a valid SQL statement?
1. AINSERT INTO publisher
  VALUES (pubsequence.nextvalue, 'HAPPY
  PRINTING', 'LAZY LARRY', NULL);
2. BCREATE INDEX a_new_index
  ON (firstcolumn*.02);
3. CCREATE SYNONYM pub
  FOR publisher;
4. Dall of the above
5. Enone of the above
26Suppose 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?
1. A0
2. B1
3. C2
4. D3
5. E4
27Given 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?
1. ACREATE SYNONYM thetable
  FOR juan.mytable;
2. BCREATE PUBLIC SYNONYM thetable
  FOR mytable;
3. CCREATE SYNONYM juan
  FOR mytable;
4. Dnone of the above
28Which of the following statements is true?
1. AA gap can appear in a sequence created with the NOCACHE option if the system crashes before a user can commit a transaction.
2. BAny unassigned sequence values appears in the USER_SEQUENCE data dictionary table as unassigned.
3. COnly the user who creates a sequence is allowed to delete it.
4. DOnly the user who created a sequence is allowed to use the value generated by the sequence.
29When is creating an index manually inappropriate?
1. Awhen queries return a large percentage of rows in the results
2. Bwhen the table is small
3. Cwhen the majority of table operations are updates
4. Dall of the above
5. Ewhen queries return a large percentage of rows in the results and when the majority of table operations are updates
30If a column has high selectivity or cardinality, which index type is most appropriate?
1. AIOT
2. BB-tree
3. Cbitmap
4. Dfunction-based index
31If a column has low selectivity, this means:
1. AThe column contains many distinct values.
2. BThe column contains a small number of distinct values.
3. CA WHERE clause is always used in a query on the column.
4. DThe selectivity of a column can’t be determined.
32Oracle 11g automatically creates an index for which type of constraints?
1. ANOT NULL
2. BPRIMARY KEY
3. CFOREIGN KEY
4. DUNIQUE KEY
5. Enone of the above
6. FNOT NULL and PRIMARY KEY
7. GPRIMARY KEY and UNIQUE KEY
33Which of the following settings can’t be modified with the ALTER SEQUENCE command?
1. AINCREMENT BY
2. BMAXVALUE
3. CSTART WITH
4. DMINVALUE
5. ECACHE
34Which node of the b-tree index contains ROWIDs?
1. Abranch blocks
2. Broot block
3. Cleaf blocks
4. DNone of the above because the primary key is used to identify rows.
35If the CACHE or NOCACHE options aren’t included in the CREATE SEQUENCE command, which of the following statements is correct?
1. AOracle 11g generates 20 integers automatically and stores them in memor
2. BNo integers are cached by default.
3. COnly one integer is cached at a time
4. DThe command will fail.
5. EOracle 11g generates 20 three-digit decimal numbers automatically and stores them in memory.
36Which of the following is a valid command?
1. ACREATE INDEX book_profit_idx
  ON (retail-cost) WHERE (retail-cost) > 10;
2. BCREATE INDEX book_profit_idx
  ON (retail-cost);
3. CCREATE FUNCTION INDEX book_profit_idx
  ON books WHERE (retail-cost) > 10;
4. Dnone of the above
37Which of the following can be used to determine whether an index exists?
1. ADESCRIBE indexname;
2. Bthe USER_INDEXES view
3. Cthe USER_INDEX view
4. Dall of the above
5. Enone of the above
38Which of the following isn’t a valid option for the CREATE SEQUENCE command?
1. AORDER
2. BNOCYCLE
3. CMINIMUMVAL
4. DNOCACHE
5. EAll of the above are valid options.
39What can be referenced to determine whether an index is used to perform a query?
1. AUSER_INDEXES view
2. Bquery source code
3. Cexplain plan
4. Ddatabase access plan
40Which of the following commands creates a private synonym?
1. ACREATE PRIVATE SYNONYM
2. BCREATE NONPUBLIC SYNONYM
3. CCREATE SYNONYM
4. DCREATE PUBLIC SYNONYM
41Which of the following commands can be used to change a password for a user account?
1. AALTER PASSWORD
2. BCHANGE PASSWORD
3. CMODIFY USER PASSWORD
4. DALTER USER . . . PASSWORD
5. Enone of the above
42Which of the following statements assigns the role CUSTOMERREP as the default role for Maurice Cain?
1. AALTER ROLE mcain
  DEFAULT ROLE customerrep;
2. BALTER USER mcain
  TO customerrep;
3. CSET DEFAULT ROLE customerrep
  FOR mcain;
4. DALTER USER mcain
  DEFAULT ROLE customerrep
5. ESET ROLE customerrep
  FOR mcain;
43Which of the following statements is most accurate?
1. AAuthentication procedures prevent any data stored in the Oracle 11g database from being stolen or damaged.
2. BAuthentication procedures are used to limit unauthorized access to the Oracle 11g database.
3. COracle 11g authentication doesn’t prevent users from accessing data in the database if they have a valid operating system account.
4. DAuthentication procedures restrict the type of data manipulation operations that a user can perform.
44Which of the following statements creates a user account named DeptHead?
1. ACREATE ROLE depthead
  IDENTIFIED BY apassword;
2. BCREATE USER depthead
  IDENTIFIED BY apassword;
3. CCREATE ACCOUNT depthead;
4. DGRANT ACCOUNT depthead;
45Which of the following privileges must be granted to a user’s account before the user can connect to the Oracle 11g database?
1. ACONNECT
2. BCREATE SESSION
3. CCONNECT ANY DATABASE
4. DCREATE ANY TABLE
46Which of the following privileges allows a user to truncate tables in a database?
1. ADROP ANY TABLE
2. BTRUNCATE ANY TABLE
3. CCREATE TABLE
4. DTRUNC TABLE
47Which of the following tables or views displays the current enabled privileges for a user?
1. ASESSION_PRIVS
2. BSYSTEM_PRIVILEGE_MAP
3. CUSER_ASSIGNED_PRIVS
4. DV$ENABLED_PRIVILEGES
48Which of the following commands eliminates only the user ELOPEZ’s ability to enter new books in the BOOKS table?
1. AREVOKE insert
  ON books
  FROM elopez;
2. BREVOKE insert
  FROM elopez;
3. CREVOKE INSERT INTO
  FROM elopez;
4. DDROP insert
  INTO books
  FROM elopez;
49Which of the following commands is used to assign a privilege to a role?
1. ACREATE ROLE
2. BCREATE PRIVILEGE
3. CGRANT
4. DALTER PRIVILEGE
50Which of the following options requires a user to change his or her password at the next login?
1. ACREATE USER
2. BALTER USER
3. CIDENTIFIED BY
4. DPASSWORD EXPIRE
51Which of the following options allows a user to grant system privileges to other users?
1. AWITH ADMIN OPTION
2. BWITH GRANT OPTI
3. CDBA
4. DASSIGN ROLES
5. ESET ROLE
52Which of the following is an object privilege?
1. ACREATE SESSION
2. BDROP USER
3. CINSERT ANY TABLE
4. DUPDATE
53Which of the following privileges can be granted only to a user, not to a role?
1. ASELECT
2. BCREATE ANY
3. CREFERENCES
4. DREAD
5. EWRITE
54Which of the following is used to grant all object privileges for an object to a specified user?
1. AALL
2. BPUBLIC
3. CANY
4. DOBJECT
55Which of the following identifies a collection of privileges?
1. Aan object privilege
2. Ba system privilege
3. CDEFAULT privilege
4. Da role
56Which of the following is true?
1. AIf the DBA changes the password for a user while the user is connected to the database, the connection terminates automatically.
2. BIf the DBA revokes the CREATE SESSION privilege from a user account, the user can’t connect to the database.
3. CIf 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.
4. Dall of the above
57Which of the following commands can be used to eliminate the RECEPTIONIST role?
1. ADELETE ROLE receptionist;
2. BDROP receptionist;
3. CDROP ANY ROLE;
4. Dnone of the above
58Which of the following displays a list of all system privileges available in Oracle 11g?
1. ASESSION_PRIVS
2. BSYS_PRIVILEGE_MAP
3. CV$SYSTEM_PRIVILEGES
4. DSYSTEM_PRIVILEGE_MAP
59Which of the following can be used to change the role that’s currently enabled for a user?
1. ASET DEFAULT ROLE
2. BALTER ROLE
3. CALTER SESSION
4. DSET ROLE
60Which of the following is an object privilege?
1. ADELETE ANY
2. BINSERT ANY
3. CUPDATE ANY
4. DREFERENCE
61Which of the following SQL statements isn’t valid?
1. ASELECT address || city || state || zip "Address" FROM customers
  WHERE lastname ¼ 'SMITH';
2. BSELECT * FROM publisher ORDER BY contact;
3. CSELECT address, city, state, zip FROM customers
  WHERE lastname ¼ "SMITH";
4. DAll the above statements are valid and return the expected results.
62Which clause is used to restrict rows or perform selection?
1. ASELECT
2. BFROM
3. CWHERE
4. DORDER BY
63Which of the following SQL statements is valid?
1. ASELECT order# FROM orders WHERE shipdate ¼ NULL;
2. BSELECT order# FROM orders WHERE shipdate ¼ 'NULL';
3. CSELECT order# FROM orders WHERE shipdate ¼ "NULL";
4. DNone of the statements are valid.
64Which of the following returns a list of all customers’ names sorted in descending order by city within state?
1. ASELECT name FROM customers
  ORDER BY desc state, city;
2. BSELECT firstname, lastname FROM customers
  SORT BY desc state, city;
3. CSELECT firstname, lastname FROM customers
  ORDER BY state desc, city;
4. DSELECT firstname, lastname FROM customers
  ORDER BY state desc, city desc;
5. ESELECT firstname, lastname FROM customers
  ORDER BY 5 desc, 6 desc;
65Which of the following doesn’t return a customer with the last name THOMPSON in the query results?
1. ASELECT lastname FROM customers WHERE lastname ¼ "THOMPSON";
2. BSELECT * FROM customers;
3. CSELECT lastname FROM customers WHERE lastname 4 'R';
4. DSELECT * FROM customers WHERE lastname 5 'V';
66Which of the following displays all books published by Publisher 1 with a retail price of at least $25.00?
1. ASELECT * FROM books WHERE pubid = 1 AND retail >= 25;
2. BSELECT * FROM books WHERE pubid = 1 OR retail >= 25;
3. CSELECT * FROM books WHERE pubid = 1 AND WHERE retail > 25;
4. DSELECT * FROM books WHERE pubid = 1, retail >= 25;
5. ESELECT * FROM books WHERE pubid = 1, retail >= $25.00;
67What’s the default sort sequence for the ORDER BY clause?
1. Aascending
2. Bdescending
3. Cthe order in which records are stored in the table
4. DThere’s no default sort sequence.
68Which of the following doesn’t include the display of books published by Publisher 2 and having a retail price of at least $35.00?
1. ASELECT * FROM books WHERE pubid = 2, retail >= $35.00;
2. BSELECT * FROM books WHERE pubid = 2 AND NOT retail < 35;
3. CSELECT * FROM books WHERE pubid IN (1, 2, 5)
  AND retail NOT BETWEEN 1 AND 29.99;
4. DAll the above statements display the specified books.
5. ENone of the above statements display the specified boo
69Which of the following includes a customer with the first name BONITA in the results?
1. ASELECT * FROM customers WHERE firstname = 'B%';
2. BSELECT * FROM customers WHERE firstname LIKE '%N%';
3. CSELECT * FROM customers WHERE firstname = '%N%';
4. DSELECT * FROM customers WHERE firstname LIKE '_B%';
70Which of the following represents exactly one character in a pattern search?
1. AESCAPE
2. B?
3. C_
4. D%
5. Enone of the above
71Which of the following returns the book HANDCRANKED COMPUTERS in the results?
1. ASELECT * FROM books WHERE title = 'H_N_%';
2. BSELECT * FROM books WHERE title LIKE "H_N_C%";
3. CSELECT * FROM books WHERE title LIKE 'H_N_C%';
4. DSELECT * FROM books WHERE title LIKE '_H%';
72Which of the following clauses is used to display query results in a sorted order?
1. AWHERE
2. BSELECT
3. CSORT
4. DORDER
5. Enone of the above
73Which of the following SQL statements returns all books published after March 20, 2005?
1. ASELECT * FROM books WHERE pubdate > 03–20–2005;
2. BSELECT * FROM books WHERE pubdate > '03–20–2005';
3. CSELECT * FROM books WHERE pubdate > '20–MAR–05';
4. DSELECT * FROM books WHERE pubdate > 'MAR–20–05';
74Which of the following lists all books published before June 2, 2004 and all books published by Publisher 4 or in the Fitness category?
1. ASELECT * FROM books WHERE category = 'FITNESS' OR pubid = 4
  AND pubdate < '06–02–2004';
2. BSELECT * FROM books WHERE category = 'FITNESS' AND pubid = 4
  OR pubdate < '06–02–2004';
3. CSELECT * FROM books WHERE category = 'FITNESS' OR (pubid = 4
  AND pubdate < '06–02–2004');
4. DSELECT * FROM books WHERE category ¼ 'FITNESS'
  OR pubid = 4, pubdate < '06–02–04';
5. Enone of the above
75Which of the following finds all orders placed before April 5, 2009 that haven’t yet shipped?
1. ASELECT * FROM orders WHERE orderdate < '04–05–09'
  AND shipdate = NULL;
2. BSELECT * FROM orders WHERE orderdate < '05–04–09'
  AND shipdate IS NULL;
3. CSELECT * FROM orders WHERE orderdate < 'APR–05–09'
  AND shipdate IS NULL;
4. DSELECT * FROM orders WHERE orderdate < '05–APR–09'
  AND shipdate IS NULL;
5. Enone of the above
76Which of the following symbols represents any number of characters in a pattern search?
1. A*
2. B?
3. C%
4. D_
77Which of the following lists books generating at least $12.00 in profit?
1. ASELECT * FROM books WHERE retail-cost > 12;
2. BSELECT * FROM books WHERE retail-cost <= 12;
3. CSELECT * FROM books WHERE profit >= 12;
4. DSELECT * FROM books WHERE retail-cost =>12.00;
5. Enone of the above
78Which of the following lists each book having a profit of at least $10.00 in descending order by profit?
1. ASELECT * FROM books WHERE profit => 10.00
  ORDER BY "Profit" desc;
2. BSELECT title, retail-cost "Profit" FROM books
  WHERE profit => 10.00
  ORDER BY "Profit" desc;
3. CSELECT title, retail-cost "Profit" FROM books
  WHERE "Profit" => 10.00
  ORDER BY "Profit" desc;
4. DSELECT title, retail-cost profit FROM books
  WHERE retail-cost >= 10.00
  ORDER BY "PROFIT" desc;
5. ESELECT title, retail-cost "Profit" FROM books
  WHERE profit => 10.00
  ORDER BY 3 desc;
79Which of the following includes the book HOW TO GET FASTER PIZZA in the query results?
1. ASELECT * FROM books WHERE title LIKE '%AS_E%';
2. BSELECT * FROM books WHERE title LIKE 'AS_E%';
3. CSELECT * FROM books WHERE title = '%AS_E%'
4. DSELECT * FROM books WHERE title = 'AS_E%';
80Which of the following returns all books published after March 20, 2005?
1. ASELECT * FROM books WHERE pubdate > 03–20–2005;
2. BSELECT * FROM books WHERE pubdate > '03–20–2005';
3. CSELECT * FROM books WHERE pubdate NOT < '20–MAR–05';
4. DSELECT * FROM books WHERE pubdate NOT < 'MAR–20–05';
5. Enone of the above
81Which of the following queries creates a Cartesian join?
1. ASELECT title, authorid
  FROM books, bookauthor;
2. BSELECT title, name
  FROM books CROSS JOIN publisher;
3. CSELECT title, gift
  FROM books NATURAL JOIN promotion;
4. Dall of the above
82Which of the following operators is not allowed in an outer join?
1. AAND
2. B=
3. COR
4. D>
83Which of the following queries contains an equality join?
1. ASELECT title, authorid
  FROM books, bookauthor
  WHERE books.isbn = bookauthor.isbn
  AND retail > 20;
2. BSELECT title, name
  FROM books CROSS JOIN publisher;
3. CSELECT title, gift
  FROM books, promotion
  WHERE retail >= minretail
  AND retail <= maxretail;
4. Dnone of the above
84Which of the following queries contains a non-equality join?
1. ASELECT title, authorid
  FROM books, bookauthor
  WHERE books.isbn = bookauthor.isbn
  AND retail > 20;
2. BSELECT title, name
  FROM books JOIN publisher
  USING (pubid);
3. CSELECT title, gift
  FROM books, promotion
  WHERE retail >= minretail
  AND retail <= maxretail;
4. Dnone of the above
85The following SQL statement contains which type of join?
SELECT title, order#, quantity
FROM books FULL JOIN orderitems
ON books.isbn = orderitems.isbn;
1. Aequality
2. Bself-join
3. Cnon-equality
4. Douter join
86Which of the following queries is valid?
1. ASELECT b.title, b.retail, o.quantity
  FROM books b NATURAL JOIN orders od
  NATURAL JOIN orderitems o
  WHERE od.order# = 1005;
2. BSELECT 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;
3. CSELECT b.title, b.retail, o.quantity
  FROM books b, orderitems o
  WHERE o.isbn = b.isbn
  AND o.order#=1005;
4. Dnone of the above
87Given the following query:
SELECT zip, order#
FROM customers NATURAL JOIN orders;

Which of the following queries is equivalent?
1. ASELECT zip, order#
  FROM customers JOIN orders
  WHERE customers.customer# = orders.customer#;
2. BSELECT zip, order#
  FROM customers, orders
  WHERE customers.customer# = orders.customer#;
3. CSELECT zip, order#
  FROM customers, orders
  WHERE customers.customer# = orders.customer# (+);
4. Dnone of the above
88Which 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;
1. Aline 1
2. Bline 2
3. Cline 3
4. Dline 4
5. Eline 5
89Given 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?
1. ASELECT lastname, firstname, order#
  FROM customers c OUTER JOIN orders o
  ON c.customer# = o.customer#
  ORDER BY c.customer#;
2. BSELECT lastname, firstname, order#
  FROM orders o RIGHT OUTER JOIN customers c
  ON c.customer# = o.customer#
  ORDER BY c.customer#;
3. CSELECT lastname, firstname, order#
  FROM customers c, orders o
  WHERE c.customer# = o.customer# (+)
  ORDER BY c.customer#;
4. Dnone of the above
90Given 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?
1. ASELECT zip FROM customers
  UNION
  SELECT category FROM books;
2. BSELECT 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;
3. CSELECT 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;
4. Dall of the above
5. Enone of the above
91Which 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
1. Aline 1
2. Bline 2
3. Cline 3
4. Dline 4
5. Eline 5
92Given the following query:
SELECT title, gift
FROM books CROSS JOIN promotion;

Which of the following queries is equivalent?
1. ASELECT title, gift
  FROM books NATURAL JOIN promotion;
2. BSELECT title
  FROM books INTERSECT
  SELECT gift
  FROM promotion;
3. CSELECT title
  FROM books UNION ALL
  SELECT gift
  FROM promotion;
4. Dall of the above
93If 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;
1. A0
2. B8
3. C7
4. D15
5. E56
94Which of the following SQL statements is not valid?
1. ASELECT b.isbn, p.name
  FROM books b NATURAL JOIN publisher p;
2. BSELECT isbn, name
  FROM books b, publisher p
  WHERE b.pubid = p.pubid;
3. CSELECT isbn, name
  FROM books b JOIN publisher p
  ON b.pubid = p.pubid;
4. DSELECT isbn, name
  FROM books JOIN publisher
  USING (pubid);
5. ENone—all the above are valid SQL statements.
95Which of the following lists all books published by the publisher named Printing Is Us?
1. ASELECT title
  FROM books NATURAL JOIN publisher
  WHERE name = 'PRINTING IS US';
2. BSELECT title
  FROM books, publisher
  WHERE pubname = 1;
3. CSELECT *
  FROM books b, publisher p
  JOIN tables ON b.pubid = p.pubid
  WHERE name = 'PRINTING IS US';
4. Dnone of the above
96Which of the following SQL statements is not valid?
1. ASELECT isbn
  FROM books
  MINUS
  SELECT isbn
  FROM orderitems;
2. BSELECT isbn, name
  FROM books, publisher
  WHERE books.pubid (+) = publisher.pubid (+);
3. CSELECT title, name
  FROM books NATURAL JOIN publisher
4. DAll the above SQL statements are valid.
97Which of the following statements about an outer join between two tables is true?
1. AIf the relationship between the tables is established with a WHERE clause, both tables can include the outer join operator.
2. BTo include unmatched records in the results, the record is paired with a NULL record in the deficient table.
3. CThe RIGHT, LEFT, and FULL keywords are equivalent.
4. Dall of the above
5. Enone of the above
98Which 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);
1. Aline 1
2. Bline 3
3. Cline 4
4. Dline 5
99What is the maximum number of characters allowed in a table alias?
1. A10
2. B30
3. C255
4. D256
100Which of the following SQL statements is valid?
1. ASELECT books.title, orderitems.quantity
  FROM books b, orderitems o
  WHERE b.isbn= o.ibsn;
2. BSELECT title, quantity
  FROM books b JOIN orderitems o;
3. CSELECT books.title, orderitems.quantity
  FROM books JOIN orderitems
  ON books.isbn = orderitems.isbn;
4. Dnone of the above
101Which of the following is a valid SQL statement?
1. ASELECT SYSDATE;
2. BSELECT UPPER(Hello) FROM dual;
3. CSELECT TO_CHAR(SYSDATE, 'Month DD, YYYY')
  FROM dual;
4. Dall of the above
5. Enone of the above
102Which of the following functions can be used to extract a portion of a character string?
1. AEXTRACT
2. BTRUNC
3. CSUBSTR
4. DINITCAP
103Which of the following determines how long ago orders that haven’t shipped were received?
1. ASELECT order#, shipdate-orderdate delay
  FROM orders;
2. BSELECT order#, SYSDATE – orderdate
  FROM orders
  WHERE shipdate IS NULL;
3. CSELECT order#, NVL(shipdate, 0)
  FROM orders
  WHERE orderdate is NULL;
4. DSELECT order#, NULL(shipdate)
  FROM orders;
104Which of the following SQL statements produces “Hello World” as the output?
1. ASELECT "Hello World" FROM dual;
2. BSELECT INITCAP('HELLO WORLD') FROM dual;
3. CSELECT LOWER('HELLO WORLD') FROM dual;
4. DSELECT "Hello World" FROM dual;
  (and)
  SELECT INITCAP('HELLO WORLD') FROM dual;
5. Enone of the above
105Which of the following functions can be used to substitute a value for a NULL value?
1. ANVL
2. BTRUNC
3. CNVL2
4. DSUBSTR
5. ENVL & SUBSTR
6. FSUBSTR & NVL2
106Which of the following is not a valid format argument for displaying the current time?
1. A'HH:MM:SS'
2. B'HH24:SS'
3. C'HH12:MI:SS'
4. DAll of the above are valid.
107Which of the following lists only the last four digits of the contact person’s phone number at American Publishing?
1. ASELECT EXTRACT(phone, -4, 1)
  FROM publisher
  WHERE name ¼ 'AMERICAN PUBLISHING';
2. BSELECT SUBSTR(phone, -4, 1)
  FROM publisher
  WHERE name = 'AMERICAN PUBLISHING';
3. CSELECT EXTRACT(phone, -1, 4)
  FROM publisher
  WHERE name = 'AMERICAN PUBLISHING';
4. DSELECT SUBSTR(phone, -4, 4)
  FROM publisher
  WHERE name = 'AMERICAN PUBLISHING';
108Which of the following functions can be used to determine how many months a book has been available?
1. AMONTH
2. BMON
3. CMONTH_BETWEEN
4. Dnone of the above
109Which of the following displays the order date for order 1000 as 03/31?
1. ASELECT TO_CHAR(orderdate, 'MM/DD')
  FROM orders
  WHERE order# = 1000;
2. BSELECT TO_CHAR(orderdate, 'Mth/DD')
  FROM orders
  WHERE order# = 1000;
3. CSELECT TO_CHAR(orderdate, 'MONTH/YY')
  FROM orders
  WHERE order# = 1000;
4. Dnone of the above
110Which of the following functions can produce different results, depending on the value of a specified column?
1. ANVL
2. BDECODE
3. CUPPER
4. DSUBSTR
111Which of the following SQL statements is not valid?
1. ASELECT TO_CHAR(orderdate, '99/9999')
  FROM orders;
2. BSELECT INITCAP(firstname), UPPER(lastname)
  FROM customers;
3. CSELECT cost, retail,
  TO_CHAR(retail-cost, '$999.99') profit
  FROM books;
4. Dall of the above
112Which function can be used to add spaces to a column until it’s a specific width?
1. ATRIML
2. BPADL
3. CLWIDTH
4. Dnone of the above
113Which of the following SELECT statements returns 30 as the result?
1. ASELECT ROUND(24.37, 2) FROM dual;
2. BSELECT TRUNC(29.99, 2) FROM dual;
3. CSELECT ROUND(29.01, -1) FROM dual;
4. DSELECT TRUNC(29.99, -1) FROM dual;
114Which of the following is a valid SQL statement?
1. ASELECT TRUNC(ROUND(125.38, 1), 0) FROM dual;
2. BSELECT ROUND(TRUNC(125.38, 0)
  FROM dual;
3. CSELECT LTRIM(LPAD(state, 5, ' '), 4, -3, "*")
  FROM dual;
4. DSELECT SUBSTR(ROUND(14.87, 2, 1), -4, 1)
  FROM dual;
115Which of the following functions can’t be used to convert the letter case of a character string?
1. AUPPER
2. BLOWER
3. CINITIALCAP
4. DAll of the above can be used for case conversion.
116Which of the following format elements causes months to be displayed as a three-letter abbreviation?
1. AMMM
2. BMONTH
3. CMON
4. Dnone of the above
117Which of the following SQL statements displays a customer’s name in all uppercase characters?
1. ASELECT UPPER('firstname', 'lastname')
  FROM customers;
2. BSELECT UPPER(firstname, lastname)
  FROM customers;
3. CSELECT UPPER(lastname, ',' firstname)
  FROM customers;
4. Dnone of the above
118Which 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?
1. ASUBSTR
2. BNVL2
3. CREPLACE
4. DTRUNC
5. Enone of the above
119What’s the name of the table provided by Oracle 11g for completing queries that don’t involve a table?
1. ADUMDUM
2. BDUAL
3. CORAC
4. DSYS
120If an integer is multiplied by a NULL value, the result is:
1. Aan integer
2. Ba whole number
3. Ca NULL value
4. DNone of the above—a syntax error message is returned.
121Which of the following statements is true?
1. AThe MIN function can be used only with numeric data.
2. BThe MAX function can be used only with date values.
3. CThe AVG function can be used only with numeric data.
4. DThe SUM function can’t be part of a nested function.
122Which of the following is a valid SELECT statement?
1. ASELECT AVG(retail-cost)
  FROM books
  GROUP BY category;
2. BSELECT category, AVG(retail-cost)
  FROM books;
3. CSELECT category, AVG(retail-cost)
  FROM books
  WHERE AVG(retail-cost) > 8.56
  GROUP BY category;
4. DSELECT category, AVG(retail-cost) Profit
  FROM books
  GROUP BY category
  HAVING profit > 8.56;
123Which of the following statements is correct?
1. AThe WHERE clause can contain a group function only if the function isn’t also listed in the SELECT clause.
2. BGroup functions can’t be used in the SELECT, FROM, or WHERE clauses.
3. CThe HAVING clause is always processed before the WHERE clause.
4. DThe GROUP BY clause is always processed before the HAVING clause.
124Which of the following is not a valid SQL statement?
1. ASELECT MIN(pubdate)
  FROM books
  GROUP BY category
  HAVING pubid = 4;
2. BSELECT MIN(pubdate)
  FROM books
  WHERE category = 'COOKING';
3. CSELECT COUNT(*)
  FROM orders
  WHERE customer# = 1005;
4. DSELECT MAX(COUNT(customer#))
  FROM orders
  GROUP BY customer#;
125Which of the following statements is correct?
1. AThe COUNT function can be used to determine how many rows contain a NULL value.
2. BOnly distinct values are included in group functions, unless the ALL keyword is included in the SELECT clause.
3. CThe HAVING clause restricts which rows are processed.
4. DThe WHERE clause determines which groups are displayed in the query results.
5. Enone of the above
126Which of the following is a valid SQL statement?
1. ASELECT customer#, order#, MAX(shipdate-orderdate)
  FROM orders
  GROUP BY customer#
  WHERE customer# = 1001;
2. BSELECT customer#, COUNT(order#)
  FROM orders
  GROUP BY customer#;
3. CSELECT customer#, COUNT(order#)
  FROM orders
  GROUP BY COUNT(order#);
4. DSELECT customer#, COUNT(order#)
  FROM orders
  GROUP BY order#;
127Which of the following SELECT statements lists only the book with the largest profit?
1. ASELECT title, MAX(retail-cost)
  FROM books
  GROUP BY title;
2. BSELECT title, MAX(retail-cost)
  FROM books
  GROUP BY title
  HAVING MAX(retail-cost);
3. CSELECT title, MAX(retail-cost)
  FROM books;
4. Dnone of the above
128Which of the following is correct?
1. AA group function can be nested inside a group function.
2. BA group function can be nested inside a single-row function.
3. CA single-row function can be nested inside a group function.
4. DA group function can be nested inside a group function, and A group function can be nested inside a single-row function.
5. EA 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.
129Which of the following functions is used to calculate the total value stored in a specified column?
1. ACOUNT
2. BMIN
3. CTOTAL
4. DSUM
5. EADD
130Which of the following SELECT statements lists the highest retail price of all books in the Family category?
1. ASELECT MAX(retail)
  FROM books
  WHERE category = 'FAMILY';
2. BSELECT MAX(retail)
  FROM books
  HAVING category = 'FAMILY';
3. CSELECT retail
  FROM books
  WHERE category = 'FAMILY'
  HAVING MAX(retail);
4. Dnone of the above
131Which of the following functions can be used to include NULL values in calculations?
1. ASUM
2. BNVL
3. CMAX
4. DMIN
132Which of the following is not a valid statement?
1. AYou must enter the ALL keyword in a group function to include all duplicate value
2. BThe AVG function can be used to find the average calculated difference between two dates.
3. CThe MIN and MAX functions can be used on any type of data.
4. Dall of the above
5. Enone of the above
133Which of the following SQL statements determines how many total customers were referred by other customers?
1. ASELECT customer#, SUM(referred)
  FROM customers
  GROUP BY customer#;
2. BSELECT COUNT(referred)
  FROM customers;
3. CSELECT COUNT(*)
  FROM customers;
4. DSELECT COUNT(*)
  FROM customers
  WHERE referred IS NULL;
1341 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?
1. A1
2. B3
3. C4
4. D5
1351 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?
1. A1
2. B3
3. C4
4. D5
1361 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?
1. A1
2. B3
3. C4
4. D5
1371 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?
1. A1
2. B3
3. C4
4. D5
1381 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:
1. Athe number of records in the specified tables
2. Bthe number of orders placed by each customer
3. Cthe number of NULL values in the specified table
4. Dthe number of customers who have placed an order
139Which of the following functions can be used to determine the earliest ship date for all orders recently processed by JustLee Books?
1. ACOUNT function
2. BMAX function
3. CMIN function
4. DSTDDEV function
5. EVARIANCE function
140Which of the following is not a valid SELECT statement?
1. ASELECT STDDEV(retail)
  FROM books;
2. BSELECT AVG(SUM(retail))
  FROM orders
  NATURAL JOIN orderitems NATURAL JOIN books
  GROUP BY customer#;
3. CSELECT order#, TO_CHAR(SUM(retail),'999.99')
  FROM orderitems JOIN books USING (isbn)
  GROUP BY order#;
4. DSELECT title, VARIANCE(retail-cost)
  FROM books
  GROUP BY pubid;
141Which query identifies customers living in the same state as the customer named Leila Smith?
1. ASELECT customer# FROM customers
  WHERE state = (SELECT state FROM customers
  WHERE lastname = 'SMITH');
2. BSELECT customer# FROM customers
  WHERE state = (SELECT state FROM customers
  WHERE lastname = 'SMITH'
  OR firstname = 'LEILA');
3. CSELECT customer# FROM customers
  WHERE state = (SELECT state FROM customers
  WHERE lastname = 'SMITH'
  AND firstname = 'LEILA'
  ORDER BY customer);
4. DSELECT customer# FROM customers
  WHERE state = (SELECT state FROM customers
  WHERE lastname = 'SMITH'
  AND firstname = 'LEILA');
142Which of the following is a valid SELECT statement?
1. ASELECT order# FROM orders
  WHERE shipdate = SELECT shipdate FROM orders
  WHERE order# = 1010;
2. BSELECT order# FROM orders
  WHERE shipdate = (SELECT shipdate FROM orders)
  AND order# = 1010;
3. CSELECT order# FROM orders
  WHERE shipdate = (SELECT shipdate FROM orders
  WHERE order# = 1010);
4. DSELECT order# FROM orders
  HAVING shipdate = (SELECT shipdate FROM orders
  WHERE order# = 1010);
143Which of the following operators is considered a single-row operator?
1. AIN
2. BALL
3. C<>
4. D<>ALL
144Which of the following queries determines which customers have ordered the same books as customer 1017?
1. ASELECT order# FROM orders
  WHERE customer# = 1017;
2. BSELECT customer# FROM orders
  JOIN orderitems USING(order#)
  WHERE isbn = (SELECT isbn FROM orderitems
  WHERE customer# = 1017);
3. CSELECT customer# FROM orders
  WHERE order# = (SELECT order# FROM orderitems
  WHERE customer# = 1017);
4. DSELECT customer# FROM orders
  JOIN orderitems USING(order#)
  WHERE isbn IN (SELECT isbn FROM orderitems
  JOIN orders USING(order#)
  WHERE customer# = 1017);
145Which of the following statements is valid?
1. ASELECT title FROM books
  WHERE retail <(SELECT cost FROM books
  WHERE isbn = '9959789321');
2. BSELECT title FROM books
  WHERE retail = (SELECT cost FROM books
  WHERE isbn = '9959789321' ORDER BY cost);
3. CSELECT title FROM books
  WHERE category IN (SELECT cost FROM orderitems
  WHERE isbn = '9959789321');
4. Dnone of the above statements
146Which of the following statements is correct?
1. AIf 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.
2. BThe 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.
3. CIf 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.
4. Dnone of the above
147Which of the following queries identifies other customers who were referred to JustLee Books by the same person who referred Jorge Perez?
1. ASELECT customer# FROM customers
  WHERE referred = (SELECT referred FROM customers
  WHERE firstname = 'JORGE'
  AND lastname = 'PEREZ');
2. BSELECT referred FROM customers
  WHERE (customer#, referred) = (SELECT customer#
  FROM customers WHERE firstname = 'JORGE'
  AND lastname = 'PEREZ');
3. CSELECT referred FROM customers
  WHERE (customer#, referred) IN (SELECT customer#
  FROM customers WHERE firstname = 'JORGE'
  AND lastname = 'PEREZ');
4. DSELECT customer# FROM customers
  WHERE customer# = (SELECT customer#
  FROM customers WHERE firstname = 'JORGE'
  AND lastname = 'PEREZ');
148In which of the following situations is using a subquery suitable?
1. Awhen you need to find all customers living in a particular region of the country
2. Bwhen you need to find all publishers who have toll-free telephone numbers
3. Cwhen you need to find the titles of all books shipped on the same date as an order placed by a particular customer
4. Dwhen you need to find all books published by Publisher
149Which of the following queries identifies customers who have ordered the same books as customers 1001 and 1005?
1. ASELECT customer# FROM orders
  JOIN books USING(isbn)
  WHERE isbn = (SELECT isbn FROM orderitems
  JOIN books USING(isbn)
  WHERE customer# = 1001 OR customer# = 1005));
2. BSELECT customer# FROM orders
  JOIN books USING(isbn)
  WHERE isbn <ANY (SELECT isbn FROM orderitems
  JOIN books USING(isbn)
  WHERE customer# = 1001 OR customer# = 1005));
3. CSELECT customer# FROM orders
  JOIN books USING(isbn)
  WHERE isbn = (SELECT isbn FROM orderitems
  JOIN orders USING(order#)
  WHERE customer# = 1001 OR 1005));
4. DSELECT customer# FROM orders
  JOIN orderitems USING(order#)
  WHERE isbn IN (SELECT isbn FROM orders
  JOIN orderitems USING(order#)
  WHERE customer# IN (1001, 1005));
150Which of the following operators is used to find all values greater than the highest value returned by a subquery?
1. A>ALL
2. B<ALL
3. C>ANY
4. D<ANY
5. EIN
151Which query determines the customers who have ordered the most books from JustLee Books?
1. ASELECT 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#;
2. BSELECT customer# FROM orders
  JOIN orderitems USING(order#)
  WHERE SUM(quantity) = (SELECT
  MAX(SUM(quantity)) FROM orderitems
  GROUP BY customer#);
3. CSELECT customer# FROM orders
  WHERE MAX(SUM(quantity)) = (SELECT
  MAX(SUM(quantity) FROM orderitems
  GROUP BY order#);
4. DSELECT customer# FROM orders
  HAVING quantity = (SELECT MAX(SUM(quantity))
  FROM orderitems
  GROUP BY customer#);
152Which of the following statements is correct?
1. AThe IN comparison operator can't be used with a subquery that returns only one row of results.
2. BThe equals (=) comparison operator can't be used with a subquery that returns more than one row of results.
3. CIn an uncorrelated subquery, statements in the outer query are executed first, and then statements in the subquery are executed.
4. DA subquery can be nested only in the outer query's SELECT clause.
153What 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%');
1. AIt determines which publisher published a book belonging to the Oracle category and then lists all other books published by that same publisher.
2. BIt lists all publishers and categories containing the value ORACLE.
3. CIt 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.
4. DNone 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.
154A subquery must be placed in the outer query's HAVING clause if:
1. AThe inner query needs to reference the value returned to the outer query.
2. BThe value returned by the inner query is to be compared to grouped data in the outer
  query.
3. CThe subquery returns more than one value to the outer query.
4. DNone of the above. Subqueries can't be used in the outer query's HAVING clause.
155Which of the following SQL statements lists all books written by the author of The Wok Way to Cook?
1. ASELECT title FROM books
  WHERE isbn IN (SELECT isbn FROM bookauthor
  HAVING authorid IN 'THE WOK WAY TO COOK);
2. BSELECT isbn FROM bookauthor
  WHERE authorid IN (SELECT authorid FROM books
  JOIN bookauthor USING(isbn)
  WHERE title = 'THE WOK WAY TO COOK');
3. CSELECT title FROM bookauthor
  WHERE authorid IN (SELECT authorid FROM books
  JOIN bookauthor USING(isbn)
  WHERE title = 'THE WOK WAY TO COOK);
4. DSELECT isbn FROM bookauthor
  HAVING authorid = SELECT authorid FROM books
  JOIN bookauthor USING(isbn)
  WHERE title = 'THE WOK WAY TO COOK';
156Which of the following statements is correct?
1. AIf the subquery returns only a NULL value, the only records returned by an outer query are those containing an equivalent NULL value.
2. BA multiple-column subquery can be used only in the outer query's FROM clause.
3. CA subquery can contain only one condition in its WHERE clause.
4. DThe 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.
157In a MERGE statement, an INSERT is placed in which conditional clause?
1. AUSING
2. BWHEN MATCHED
3. CWHEN NOT MATCHED
4. DINSERTs aren't allowed in a MERGE statement.
158Given the following query, which statement is correct?
SELECT order# FROM orders
WHERE order# IN (SELECT order# FROM orderitems
WHERE isbn = '9959789321');
1. AThe statement doesn't execute because the subquery and outer query don't reference the same table.
2. BThe outer query removes duplicates in the subquery's Order# list.
3. CThe query fails if only one result is returned to the outer query because the outer query's WHERE clause uses the IN comparison operat
4. DNo rows are displayed because the ISBN in the WHERE clause is enclosed in single quotation marks.
159Given 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);
1. AThe SELECT statement fails and returns an Oracle error message.
2. BThe outer query displays no rows in its results because the subquery passes a NULL value to the outer query
3. CThe customer number is displayed for customers whose orders haven't yet shipped.
4. DThe customer number of all customers who haven't placed an order are displayed.
160Which operator is used to process a correlated subquery?
1. AEXISTS
2. BIN
3. CLINK
4. DMERGE
161CREATE 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;

Which of the following statements is correct?
1. ANo DML operations can be performed on the CHANGEADDRESS view.
2. BThe CHANGEADDRESS view is a simple view.
3. CThe CHANGEADDRESS view is a complex view.
4. DThe CHANGEADDRESS view is an inline view.
162CREATE 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;

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?
1. AUPDATE changeaddress
  SET shipstreet = '958 ELM ROAD'
  WHERE customer# = 1020;
2. BINSERT INTO changeaddress
  VALUES (9999, 'LAST', 'FIRST', 9999,
  '123 HERE AVE', 'MYTOWN', 'AA', 99999);
3. CDELETE FROM changeaddress
  WHERE customer# = 1020;
4. Dall of the above
5. Enone of the above
163CREATE 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;

Which of the following is the key-preserved table for the CHANGEADDRESS view?
1. ACUSTOMERS table
2. BORDERS table
3. CBoth tables together serve as a composite key-preserved table.
4. Dnone of the above
164CREATE 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;

Which of the following columns serves as the primary key for the CHANGEADDRESS view?
1. ACustomer#
2. BLastname
3. CFirstname
4. DOrder#
5. EShipstreet
165CREATE 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;

If a record is deleted from the CHANGEADDRESS view based on the Customer# column, the customer information is then deleted from which underlying table?
1. ACUSTOMERS
2. BORDERS
3. CCUSTOMERS and ORDER
4. DNeither—the DELETE command can’t be used on the CHANGEADDRESS view.
166CREATE 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;

Which of the following is correct?
1. AROWNUM can’t be used with the view because it isn’t included in the results the subquery returns.
2. BThe view is a simple view because it doesn’t include a group function or a GROUP BY clause.
3. CThe 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.
4. Dall of the above
5. Enone of the above
167CREATE 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;

Assuming one of the orders has shipped, which of the following is true?
1. AThe CHANGEADDRESS view can’t be used to update an order’s ship date because of the WITH CHECK OPTION constraint.
2. BThe CHANGEADDRESS view can’t be used to update an order’s ship date because the Shipdate column isn’t included in the view.
3. CThe CHANGEADDRESS view can’t be used to update an order’s ship date because the ORDERS table is not the key-preserved table.
4. DThe 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.
168CREATE 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.

Which of the following is a correct statement?
1. ANo DML operations can be performed on the CHANGENAME view.
2. BThe CHANGENAME view is a simple view.
3. CThe CHANGENAME view is a complex view.
4. DThe CHANGENAME view is an inline view.
169CREATE 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.

Which of the following columns serves as the primary key for the CHANGENAME view?
1. ACustomer#
2. BLastname
3. CFirstname
4. DThe view doesn’t have or need a primary key.
170CREATE 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.

Which of the following DML operations could never be used on the CHANGENAME view?
1. AINSERT
2. BUPDATE
3. CDELETE
4. DAll of the above are valid DML operations for the CHANGENAME view
171CREATE 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.

The INSERT command can’t be used with the CHANGENAME view because:
1. AA key-preserved table isn’t included in the view.
2. BThe view was created with the WITH CHECK OPTION constraint.
3. CThe inserted record couldn’t be accessed by the view.
4. DNone of the above—an INSERT command can be used on the table as long as the PRIMARY KEY constraint isn’t violated.
172CREATE 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.

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?
1. AThe CREATE OR REPLACE VIEW command can be used to re-create the view with the necessary column included in the new view.
2. BThe ALTER VIEW . . . ADD COLUMN command can be used to add the necessary column to the existing view.
3. CThe 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.
4. DAll of the above can be performed to include the customer’s zip code in the view.
5. EOnly (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.
6. FNone of the above includes the customer’s zip code in the view.
173Which of the following DML operations can’t be performed on a view containing a group function?
1. AINSERT
2. BUPDATE
3. CDELETE
4. DAll of the above can be performed on a view containing a group function
5. ENone of the above can be performed on a view containing a group function.
174You can’t perform any DML operations on which of the following?
1. Aviews created with the WITH READ ONLY option
2. Bviews that include the DISTINCT keyword
3. Cviews that include a GROUP BY clause
4. DAll of the above allow DML operations.
5. ENone of the above allow DML operations.
175A TOP-N analysis is performed by determining the rows with:
1. Athe highest ROWNUM values
2. Ba ROWNUM value greater than or equal to N
3. Cthe lowest ROWNUM values
4. Da ROWNUM value less than or equal to N
176To assign names to the columns in a view, you can do which of the following?
1. AAssign aliases in the subquery, and the aliases are used for the column names.
2. BUse the ALTER VIEW command to change column names.
3. CAssign names for up to three columns in the CREATE VIEW clause before the subquery
  is listed in the AS clause.
4. DNone of the above—columns can’t be assigned names for a view; they must keep their
  original names.
177Which of the following is correct?
1. AThe ORDER BY clause can’t be used in the subquery of a CREATE VIEW command.
2. BThe ORDER BY clause can’t be used in an inline view.
3. CThe DISTINCT keyword can’t be used in an inline view.
4. DThe WITH READ ONLY option must be used with an inline view.
178If you try to add a row to a complex view that includes a GROUP BY clause, you get which of the following error messages?
1. Avirtual column not allowed here
2. Bdata manipulation operation not legal on this view
3. Ccannot map to a column in a non-key-preserved table
4. DNone of the above—no error message is returned.
179A simple view can contain which of the following?
1. Adata from one or more tables
2. Ban expression
3. Ca GROUP BY clause for data retrieved from one table
4. Dfive columns from one table
5. Eall of the above
6. Fnone of the above
180A complex view can contain which of the following?
1. Adata from one or more tables
2. Ban expression
3. Ca GROUP BY clause for data retrieved from one table
4. Dfive columns from one table
5. Eall of the above
6. Fnone of the above

Informações

Descrição: Computer Science Quiz sobre Midterm 2 (Chapter 5 - 13), criado por Yorria Raine em 29-03-2015.
Autor: Yorria Raine
Idioma: English
Visualizações: 793
Etiquetas: oracle database sql casteel computer science
Avaliação: Ainda sem avaliações
Avalie este quiz
Avalie este quiz

Midterm 2 (Chapter 5 - 13)

Crie uma conta grátis para terminar este quiz

Perguntas do quiz (180)

Informações

Fale Conosco

O que você acha do novo visual?