ALL THE SQL!
Based on the structure of the BOOKS table, which of the following is a valid SQL statement?
CREATE INDEX books_title_idx ON books (INITCAP(title));
(T/F) If one of the data values being entered into a table by the INSERT command violates an existing constraint, the remaining portion of the row will be added, but the data violating the constraint will need to be added at a later time using the UPDATE command.
False
(T/F) If the ON DELETE CASCADE keywords are included when a(n) *PRIMARY KEY* constraint is created, then if a row is deleted from the parent table, any corresponding records in the child table are also deleted.
False
(T/F) If you assign a column alias to a column, it must be enclosed in double quotation marks (" ").
False
(T/F) In a WHERE clause, logical operators are evaluated before comparison operators.
False
(T/F) NOT, ALL, and OR are all considered logical operators.
False
(T/F) NOT, AND, and OR are *comparison* operators.
False
(T/F) Once a privilege has been granted to a role, it cannot be revoked without re-creating the role.
False
(T/F) Only one column can be added to an existing table at one time.
False
(T/F) Only one type of relationship can be represented in an Entity-Relationship Model.
False
(T/F) Single-row functions cannot be included in the UPDATE command.
False
(T/F) Single-row operators can be used with multiple-row subqueries that return only one column of results.
False
(T/F) The *DROP* command can be used to remove rows from an existing table.
False
(T/F) The *ON* clause can be used only if the tables being joined have a common column with the same name.
False
(T/F) The *ORDER BY* clause is used to group data.
False
(T/F) The *ROUND* function can be used to truncate numeric data.
False
(T/F) The *WITH ADMIN OPTION* is required to enable the user to grant the specified object privileges to other users.
False
(T/F) The ALPHANUMERIC datatype can be used to store characters and numbers to a maximum width of 2000.
False
(T/F) The ALTER USER command can be used to change the name assigned to a user account.
False
(T/F) The CREATE TABLE cannot contain a subquery.
False
(T/F) The GROUP BY clause can only be used when a SELECT statement includes a group function.
False
(T/F) The INSTR function is used to return a portion of a character string.
False
(T/F) The NATURAL JOIN keywords can be used to create non-equality joins.
False
(T/F) The TRUNC function can be used to add insignificant zeros to the right of a decimal point for numeric data.
False
(T/F) The UPDATE ANY TABLE privilege is a system privilege.
False
(T/F) The data *warehouse* contains information about objects included in the database.
False
(T/F) The greater than operator, >, is a valid operator for *multiple-row* subqueries.
False
(T/F) The only group function that includes NULL values is the *NVL* function.
False
(T/F) The percent sign (%) is used in a search pattern to indicate "exactly one character in this position".
False
(T/F) The structure of a table can be viewed by issuing the STRUCTURE command.
False
(T/F) To indicate that the numeric value of the day of the year for a date is being specified, include DAY in format argument of the TO_DATE function.
False
(T/F) To retain the structure of a table, while deleting all its data and releasing their storage space, execute the TRUNC command.
False
(T/F) Transitive dependency can only exist if the data is uniquely identified by a composite primary key.
False
(T/F) Use MON in the format argument to spell out the specified month.
False
(T/F) Valid *multiple-row* operators include =, >, =, <=.
False
(T/F) When DDL operations are performed, Oracle 12c will automatically place a(n) *shared* lock on the table.
False
(T/F) When including a subquery in an INSERT command, the syntax of enclosing the subquery in parentheses is *mandatory*.
False
(T/F) When the column names are listed in the INSERT INTO clause of the INSERT command, the order of the names must exactly match the order of the data values in the *UPDATE* clause.
False
(T/F) When using a search pattern, a(n) *?* symbol is used to indicate exactly one character in that position.
False
A negative number can be assigned to the ____ clause to generate sequential numbers in decreasing order.
INCREMENT BY
Which of the following commands will revoke the SELECT privilege for the ORDERS table previously granted to user RTHOMAS?
REVOKE SELECT ON orders FROM rthomas;
Based upon the contents of the CUSTOMERS table, which of the following will display the shipping location as: City, State Zip Note: The ST column name is truncated, this represents the STATE column.
SELECT INITCAP(CONCAT(city, CONCAT(', ', CONCAT(state, CONCAT(' ', zip))))) FROM customers;
Which of the following SQL statements will return the names of all customers who placed an order on April 12, 2003?
SELECT lastname, firstname FROM customers NATURAL JOIN orders WHERE orderdate = '12-APR-03';
Which of the following SQL statements will list the name of each customer stored in the CUSTOMERS table, and, if the customer has placed an order that is contained in the ORDERS table, the order# of any order each customer has placed?
SELECT lastname, firstname, order# FROM customers, orders WHERE orders.customer# (+) = customers.customer#;
(T/F) *Parentheses* can be used to override the order of operations in an arithmetic expression.
True
(T/F) A bridging table can be used to eliminate a many-to-many relationship in a relational database.
True
(T/F) A correlated subquery is a subquery that is executed once for each row in the outer query.
True
(T/F) A correlated subquery references one or more columns in the outer query, and the EXISTS operator is used to test whether the relationship or link is present.
True
(T/F) A function-based index can be used for queries that include searches based upon arithmetic expressions or functions.
True
(T/F) A group function can be used in a(n) *inline view*.
True
(T/F) A many-to-many relationship cannot exist in a relational database.
True
(T/F) A multiple-row subquery can be nested in a HAVING clause.
True
(T/F) A record in the logical design of a database corresponds to a row in the physical table of a relational database.
True
(T/F) A subquery is required when the condition for the outer query is based upon an unknown.
True
(T/F) A subquery nested in a SELECT clause cannot contain an ORDER BY clause.
True
(T/F) A(n) *correlated* subquery references one or more columns from the outer query.
True
(T/F) A(n) *non-equality* join is used when there are no equivalent rows of data in the tables that are being joined.
True
(T/F) A(n) *outer* query is also referred to as a parent query.
True
(T/F) A(n) *percent* sign is used in a search pattern to indicate zero, one, or more characters in a position.
True
(T/F) Authentication is the process of ensuring that the individuals trying to access the system are who they claim to be.
True
(T/F) By default, the *JOIN* keyword creates an inner join.
True
(T/F) Group functions return one result per group of rows processed.
True
(T/F) If a data value violates a(n) *constraint*, the entire row is prevented from being added to the table.
True
(T/F) If a subquery is nested in a HAVING clause, the subquery must be on the right side of the comparison operator.
True
(T/F) If multiple column names are listed in the SELECT clause of a SELECT statement, the columns must be separated by a comma (,).
True
(T/F) If you are joining four tables in a SELECT statement, *three* joining conditions will be required.
True
(T/F) In Oracle12c, a MERGE statement compares data between two tables and can perform a series of DML actions to assist in synchronizing the data of the two tables.
True
(T/F) Including the keywords NULLS FIRST in the ORDER BY clause will result in NULL values appearing first in the results, regardless of the specified sort sequence.
True
(T/F) Multiple-row subqueries are nested queries that can return more than one row of results to the parent query.
True
(T/F) Sequence settings can be altered using the ALTER SEQUENCE command.
True
(T/F) Set operators are used to combine the results of multiple queries.
True
(T/F) The *=ANY* operator yields the same results as using the IN multiple-row operator.
True
(T/F) The *AVG* function only includes non-NULL values in its calculations.
True
(T/F) The *MIN* function can be used with the DISTINCT or ALL keywords.
True
(T/F) The *NVL2* function allows different options to be selected, based upon whether a NULL value exists.
True
(T/F) The *REVOKE* command can be used to revoke a role from a user or another role.
True
(T/F) The *RTRIM* function can be used to remove a specific set of characters from the right side of a set of data values.
True
(T/F) The *syntax* for a SQL statement gives the basic structure required to execute the statement. _________________________
True
(T/F) The <ALL operator indicates that for a value to be included in the results, it must be less than the lowest value returned by the subquery.
True
(T/F) The ALTER TABLE command can be used to *delete* an existing constraint.
True
(T/F) The ALTER TABLE command can be used to disable a constraint.
True
(T/F) The HAVING clause is used to restrict the groups returned by a query.
True
(T/F) The IN logical operator is similar to using *OR*.
True
(T/F) The INSERT, UPDATE, and DELETE object privileges can be applied to tables and views.
True
(T/F) The LOWER function can be used to display upper-case characters in lower-case.
True
(T/F) The NVL function can be used to include records containing null values in calculations.
True
(T/F) The SET clause of the UPDATE command is used to identify the column(s) being updated.
True
(T/F) The START WITH clause of a sequence cannot be reset with the ALTER SEQUENCE command.
True
(T/F) The TO_CHAR function can be used to add a dollar sign ($) to a numeric value.
True
(T/F) The VARIANCE function is used to determine the variance in a group of numeric data.
True
(T/F) The default format of a DATE column in Oracle 12c is DD-MM-YY.
True
(T/F) The equal sign, =, is a valid single-row operator.
True
(T/F) The first column listed in an ORDER BY clause is considered the primary sort.
True
(T/F) The highest possible value for a descending sequence is -1.
True
(T/F) The lowest possible value for an increasing sequence is 1 .
True
(T/F) The number of joining conditions required to join tables is always one less than the number of tables being joined.
True
(T/F) When a column is deleted, the deletion is permanent.
True
(T/F) When a positive value is assigned to the INCREMENT BY clause of the CREATE SEQUENCE command, numeric values are generated in ascending order.
True
(T/F) When a subquery is used in the INSERT command, the subquery does not have to be enclosed in parentheses.
True
(T/F) When a user asks the database a question, the user is issuing a query.
True
(T/F) When the subquery is executed first and the value is passed back as input to the outer query, the subquery is known as an uncorrelated subquery.
True
(T/F) When used with a multiple-row subquery, the IN operator indicates that the records processed by the outer query must match one of the values returned by the subquery.
True
Which of the following serves the same basic purpose as an index in a book, by allowing users to quickly locate specific records?
index
A complete query nested inside another query is called a(n) ____
subquery
Which of the following symbols cannot be used in a table name?
%
Which of the following search patterns could be used to find the word HELLO in Oracle12c?
%H%
Which operator will instruct Oracle12c to list all records with a value that is more than the highest value returned by the subquery?
>ALL
When modifying data in existing columns, which of the following is correct?
A column must be as wide as the data it already contains.
Which of the following functions can be used to determine the date one month after a specified date?
ADD_MONTHS
If the DISTINCT keyword is not included in the STDDEV function, the ____ keyword will be assumed.
ALL
Which command will modify a sequence?
ALTER SEQUENCE sequencename
Which of the following commands will increase the size of the CITY column in the CUSTOMERS table from 12 to 20 and increase size of the LASTNAME column from 10 to 14?
ALTER TABLE customers MODIFY (city VARCHAR2(20), lastname VARCHAR2(14));
The ____ command will assign a default role to a user.
ALTER USER username DEFAULT ROLE rolename;
To instruct Oracle12cto sort data in ascending order, enter ____ after the column name in the ORDER BY clause.
ASC
Which of the following is a correct statement?
An asterisk can be used as the argument for the COUNT function to include NULL values in the results.
What will happen if you try to use the INSERT command to insert a NULL value into a column that has been assigned a PRIMARY KEY constraint?
An error message is returned and the row is not added to the table.
Which of the following functions is similar to using the (| |) symbol to combine the contents of two character strings?
CONCAT
Which command will create an index?
CREATE INDEX indexname ON tablename (columnname)
Which of the following SQL statements will create a new role named PRCLERK?
CREATE ROLE prclerk;
Which of the following commands can be used to create a sequence for the CUSTOMERS table?
CREATE SEQUENCE customers_customer#;
Based on the structure of the ORDERITEMS table, which of the following commands was most likely used to create the table?
CREATE TABLE orderitems (order# NUMBER (4), item# NUMBER(2), isbn VARCHAR2(10), qty NUMBER(3), PRIMARY KEY(order#, item#));
Which of the following search conditions can be used to identify records that have data stored in a column named ColB?
ColB IS NOT NULL
Which of the following format elements will display the value of Friday in a specified date as a 6?
D
Which of the following commands will delete only publisher 4 from the PUBLISHER table?
DELETE FROM publisher WHERE pubid = 4;
To instruct Oracle12c to sort data in descending order, enter ____ after the column name in the WHERE clause.
DESC
Which clause will allow you to disable a constraint?
DISABLE
Which clause will allow you to delete a constraint?
DROP
If a SELECT statement contains HAVING, GROUP BY, and WHERE clauses, the ____ clause will be processed last.
HAVING
If the output is to be restricted based upon a group function, a(n) ____ clause must be included in the SQL statement.
HAVING
The ____ clause is used to restrict the groups returned by a query.
HAVING
Which clause is used when the group results of a subquery need to be restricted, based on some condition?
HAVING
Which of the following keywords can be used to join two tables that do not contain a commonly named and defined column?
JOIN...ON
Which of the following represents the number of days that have passed since January 1, 4712 B.C.?
Julian date
Which of the following functions can be used to convert a character string to lower-case letters?
LOWER
Which of the following lines of the SQL statement contains an error? 1 SELECT title, MAX(retail) 2 FROM books 3 WHERE retail > 30 4 AND pubid = 1 5 GROUP BY retail
Line 5
What clause offers many options to identify measures and patterns to analyze in a query?
MATCH_RECOGNIZE
The ____ function returns the smallest value in a specified column.
MIN
Which of the following can only be used to link tables that have a common column?
NATURAL JOIN
If it is possible for a subquery to return a NULL value to the outer query for comparison, the ____ function should be used to substitute an actual value for the NULL.
NVL
In the USER_CONSTRAINTS view, the value displayed in the CONSTRAINT_TYPE column will be a(n) ____ for a FOREIGN KEY constraint.
R
Which of the following is not a constraint type in Oracle 12c?
REFERENCE
Which of the following privileges will allow a user to reference a table when creating a FOREIGN KEY constraint?
REFERENCES
Which of the following commands will change the name of the PROMOTION table to GIFT?
RENAME promotion TO gift;
Which command is used to rename a table you own?
RENAME...TO
Which of the following can be used to replace a specific set of characters with another set of characters?
REPLACE
Which of the following commands allows a user to "undo" uncommitted changes to data?
ROLLBACK
Which of the following commands is used to issue a query?
SELECT
Based upon the contents of the BOOKS table in the accompanying figure, which of the following SQL statements will retrieve all books published by the publisher assigned Pubid 1?
SELECT * FROM books WHERE pubid = 1;
Based upon the contents of the ORDERS table in the accompanying figure, which of the following queries will display all orders that were not shipped for at least three days after the order was received?
SELECT * FROM orders WHERE shipdate-orderdate >= 3;
Which of the following will display the name of each customer in lower-case characters based upon the contents of the CUSTOMERS table? Note: The ST column name is truncated, this represents the STATE column.
SELECT LOWER(firstname) || ' ' || LOWER(lastname) FROM customers;
Based on the contents of the CUSTOMERS table, which of the following SQL statements will display the customer# of all customers who were referred by the same individual that referred customer# 1003?
SELECT customer# FROM customers WHERE NVL(referred, 0) = (SELECT NVL(referred,0) FROM customers WHERE customer# = 1003);
Based on the contents of the CUSTOMERS table, which SQL statement will display the customers residing in the same state as customer#1013?
SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE customer#=1013);
Which of the following is a valid SQL statement based upon the contents of the CUSTOMERS table? Note: The ST column name is truncated, this represents the STATE column.
SELECT customer#, NVL2(referred, 'Referred', 'Not Referred') FROM customers;
Which of the following SQL statements would most likely be used to generate the partial output shown above?
SELECT name FROM SYSTEM_PRIVILEGE_MAP;
Which of the following is a valid SQL statement to retrieve data from the ORDERS table?
SELECT order#, orderdate, shipdate FROM orders;
Based upon the contents of the ORDERS table in the accompanying figure, which of the following SQL statements will display how long it took to ship order # 1007 (based upon when the order was originally placed)?
SELECT order#, shipdate-orderdate FROM orders WHERE order# = 1007;
Which of the following SQL statements will display books that have not been ordered recently?
SELECT title FROM books MINUS SELECT title FROM books NATURAL JOIN orderitems;
Based upon the contents of the BOOKS table in the accompanying figure, which of the following queries will list the title and retail price of each book stored in the BOOKS table, sorted in order of the book titles?
SELECT title, retail FROM books ORDER BY title;
Based upon the contents of the BOOKS table, which of the following SQL statements will display two columns in its output?
SELECT title, retail-cost "Profit" FROM books;
Which of the following queries will allow hidden columns to be displayed?
SELECT which specifically references the hidden column.
Which of the following format argument elements will display the number of seconds past midnight?
SSSS
All the Oracle12c system privileges can be viewed through the data dictionary view ____.
SYSTEM_PRIVILEGE_MAP
Which of the following statements is correct?
The columns will be listed in the results in the same order they are listed in the SELECT clause of the SELECT statement.
Which of the following statements about privileges is incorrect?
There are five types of privileges, including DATATYPE, ROLETYPE, and USERNAME privileges.
Which of the following set operators will display only the unique results of the combined SQL statements?
UNION
Which of the following commands is used to modify existing data in a table?
UPDATE
Which of the following is not a valid SQL command?
UPDATE acctmanager WHERE amid = 'J500';
Which data dictionary object contains a column named HIDDEN_COLUMN?
USER_TAB_COLS.
Which of the following methods is not used in an INSERT statement to enable the use of a DEFAULT sequence value defined for a column?
Use NEXTVAL to call the value
Which of the following keywords is omitted from the INSERT command if the data to be added to a table is already contained in another table?
VALUES
If the ____ clause of the UPDATE command is omitted, then all the rows in the specified table will be changed.
WHERE
Based upon the contents of the CUSTOMERS table, which of the following would be the most appropriate use of a subquery?
When searching for all customers who live in the same state as customer# 1007. >
A lock arising from a SELECT...FOR UPDATE command will be released when ____.
a COMMIT command is executed
Partial dependency exists if what conditions exist?
a column is dependent only on a portion of a composite primary key
When a constraint is created at the table level, the constraint definition is provided ____ the column definition list.
after
If a new column is added to the PROMOTION table, where will the new column be listed?
after the MAXRETAIL column
A column alias must be contained within double quotation marks (" ") if it contains which of the following?
all of the above
Any type of subquery can be used in the ____ clause of a SELECT statement.
all of the above
Which of the following can be used in a WHERE clause?
all of the above
Which of the following can be used with date columns?
all of the above
Which of the following is a valid comparison operator?
all of the above
A subquery must include a(n) ____ clause.
both a and b
Which of the following queries will display the first and last name of the individual who referred another customer, along with the customer# of the referred customer?
both a and b
Based upon the contents of the ORDERS table in the accompanying figure, which of the following queries will display all orders shipped between April 4, 2003 and April 5, 2003?
both b and c
Which of the following terms is considered the basic unit of data in a database?
character
Which of the following terms refers to a type of subquery that is processed, or executed, once for each row in the outer query?
correlated subquery
Analyzing historical sales data stored in a database is commonly referred to as ____.
data mining
If uncontrolled, what can lead to data anomalies?
data redundancy
A(n) ____ simply identifies the type of data that Oracle 12c will be expected to store in a column.
datatype
Which of the following does not contain repeating groups, but has a primary key and possibly partial dependencies?
first normal form (1NF)
A temporary table that is created when a multiple-column subquery is used in the FROM clause of an outer query is called a(n) ____.
inline view
(SELECT category, AVG(retail) cataverage FROM books GROUP BY category);
multiple-column
A(n) ____ subquery is one that can return several rows of results.
multiple-row
The following SQL statement contains which type of subquery? SELECT title, retail, category FROM books WHERE retail IN (SELECT MAX(retail) FROM books GROUP BY category);
multiple-row
Based on the contents of the BOOKS table, which line of the following SQL statement contains an error?
none of the above
Based on the contents of the BOOKS table, which of the following SQL statements will display the title of all books published by the publisher of SHORTEST POEMS?
none of the above
Which of the following SQL statements will display the gift that should be sent to any customer who orders the book titled THE WOK WAY TO COOK?
none of the above
Which of the following SQL statements will remove the SELECT ANY TABLE privilege from the database so it can never be granted to another user?
none of the above
Which of the following are case sensitive in Oracle 12c?
none of the above
The SUM function can only be used with ____ data.
numeric
Suppose that a patient in a hospital can only be assigned to one room. However, the room may be assigned to more than one patient at a time. This is an example of what type of relationship?
one-to-many
The results of a subquery are passed back as input to the ____ query.
outer
An outer query is also referred to as a(n) ____ query.
parent query
If the PUBLIC keyword is not included when a synonym is created, the command will create a(n) ____ synonym, by default.
private
Based upon the contents of the BOOKS table, which of the following columns will be displayed with a right-aligned column heading?
pubid
A record in the logical design of a database corresponds to a ____ in the physical database.
row
Which of the following types of joins refers to joining a table to itself?
self-join
A(n) ____ is used to combine the results of two queries.
set operator
The <> operator is referred to as a(n) ____ operator.
single-row
The = operator is referred to as a(n) ____ operator.
single-row
The > operator is referred to as a(n) ____ operator.
single-row
The following SQL statement contains what type of subqueries? SELECT isbn, title FROM books WHERE pubid = (SELECT pubid FROM books WHERE title = 'SHORTEST POEMS') AND retail-cost > (SELECT AVG(retail-cost) FROM books);
single-row
The following SQL statement contains which type of subquery? SELECT title, retail, (SELECT AVG(retail) FROM books) FROM books;
single-row
If the data has no partial dependencies, repeating groups, or transitive dependencies, and has a composite primary key, the data is in which form?
third normal
Data in second normal form (2NF) may contain which of the following?
transitive dependencies
Which of the following symbols can be used to combine data from different columns into one column of output?
||
If a PRIMARY KEY constraint, named ORDERITEMS_PK, exists for the ORDER# and ITEM# columns of the ORDERITEMS table, which of the following commands will drop the constraint?
ALTER TABLE orderitems DROP PRIMARY KEY;
If a PRIMARY KEY constraint, named ORDERITEMS_PK, for the ORDER# and ITEM# columns of the ORDERITEMS table have been disabled, which of the following commands will enable the constraint?
ALTER TABLE orderitems ENABLE CONSTRAINT orderitems_pk;
Which of the following SQL statements will assign the DBA role as the default role for user RTHOMAS?
ALTER USER rthomas DEFAULT ROLE dba;
Which of the following SQL commands will require the user RTHOMAS to change the account password the next time the database is accessed?
ALTER USER rthomas PASSWORD EXPIRE ;
(T/F) A *Datatype* is a rule used to ensure the accuracy of data stored in a database.
False
(T/F) A collection of fields is a file.
False
(T/F) A column has low cardinality if it contains many distinct values.
False
(T/F) A constraint can be renamed using the ALTER TABLE command.
False
(T/F) A constraint can only be created as part of the CREATE TABLE command.
False
(T/F) A constraint name can consist of up to 10 characters.
False
(T/F) A database is a physical storage device for data.
False
(T/F) A group function cannot be included in the SELECT clause of a single-row subquery.
False
(T/F) A multiple-column subquery cannot be nested in a WHERE clause.
False
(T/F) A one-to-many relationship cannot be included in a relational database.
False
(T/F) A role can be removed from the database with the *DELETE ROLE* command.
False
(T/F) A role can only be granted to other roles.
False
(T/F) A subquery can only be nested in the WHERE or HAVING clause of the outer query.
False
(T/F) A subquery is required when the condition for the *inner* query is based upon an unknown.
False
(T/F) A subquery nested in a WHERE clause can only be on the *left* side of the comparison operator.
False
(T/F) A table alias is assigned to a table in the WHERE clause.
False
(T/F) A table name can consist of numbers, letters, and blank spaces.
False
(T/F) A(n) *clause* identifies what must exist or a requirement that must be met for a record to be included in the results of a query.
False
(T/F) A(n) *group* is a collection of privileges that can be granted to users.
False
(T/F) A(n) *uncorrelated* subquery is when the outer query is executed first, then the inner query is executed.
False
(T/F) Columns used to group data in the GROUP BY clause must also be listed in the SELECT clause.
False
(T/F) Each clause of a SELECT statement *must* be entered on a new line in SQL*Plus.
False
(T/F) Each section of a SQL command that begins with a keyword is known as a statement.
False
(T/F) A(n) CRBMDS is a software program that can be used to create a relational database and allows you to enter, manipulate, and retrieve data.
False
(T/F) A(n) synonym is a collection of objects.
False
(T/F) An index is implicitly created when a NOT NULL constraint is added to a table.
False
(T/F) An outer join only lists rows that contain a match in both tables.
False
(T/F) An outer join operator can be included in a FROM clause to list all rows from one table that do not have a corresponding row in the other table.
False
(T/F) By default, the COUNT function uses the *DISTINCT* keyword when counting the values contained in a column.
False
