Database Programming Oracle Final
If the results of a SELECT statement include more than one column of data, the column names must be separated in the SELECT clause by which of the following symbols?
' '
If a new table is being created based upon a subquery, the subquery must be enclosed in which of the following symbols?
( )
Which of the following must be used to separate a subquery from the outer query?
( )
The DROP COLUMN clause of the ALTER TABLE command can be used to drop a maximum of ____________________ column(s) at a time.
1
The maximum number of columns that can be specified for sorting data is ____.
255
What is the correct answer for the arithmetic expression 9+2*(3-4) using the order of operations employed by Oracle 12c when solving equations?
7
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 customersMODIFY (city VARCHAR2(20), lastname VARCHAR2(14));
In a WHERE clause, logical operators are evaluated before comparison operators.
FALSE
To indicate which database table contains the data to be selected by a query, the table name should be listed in the ____ clause.
FROM
A database is a physical storage device for data.
False
The percent sign (%) is used in a search pattern to indicate "exactly one character in this position".
False
The ____________________ TABLE command is used to prevent other users from making changes to a portion of a table.
LOCK
Which of the following functions can be used to fill a character string to a specific width?
LPAD, RPAD
To indicate that minutes should be displayed, include ____________________ in the time element format argument.
MI
The ____________________ logical operator is evaluated first in a sequence of logical operators.
NOT
When sorting the results in ascending order, which of the following values will be presented last in the output?
NULL
Only one ____ constraint can exist for each table.
Primary Key
In the USER_CONSTRAINTS view, the value displayed in the CONSTRAINT_TYPE column will be a(n) ____ for a FOREIGN KEY constraint.
R
Commands used to modify data are known as DML commands.
True
A PRIMARY KEY constraint can be added to an existing table by using the ____ clause of the ALTER TABLE command.
add
The default keyword for group functions is ____.
all
A user can perform a DML operation (add, modify, delete) on a simple view if it does not violate which type of existing constraint on the underlying base table?
all of the above
Which of the following can be used with date columns?
all of the above
Which statement is true about the view created with the following command? CREATE VIEW inventory AS SELECT isbn, title, retail price FROM books WITH READ ONLY;
all of the above
How can constraints be added to a table?
as part of the create table command, as part of the alter table command
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?
b.SELECT lastname, firstname, order# FROM customers c LEFT OUTER JOIN orders o ON c.customer# = o.customer#;
The order in which NULL values appear in the results can be overridden by which of the following keywords?
both a and b
Which of the following queries will return the same results as the following SQL statement? SELECT c.customer#, lastname, firstname, order# FROM customers c, orders o WHERE c.customer# = o.customer#;
both a and b
Which of the following types of columns will automatically left-align the column heading when it is displayed?
character and date
Any type of constraint can be created at the ____________________ level, unless the constraint is being defined for more than one column.
column
If more than one column is listed in the INSERT INTO clause, the column names must be separated by ____.
commas
A(n) ____ is used to indicate how data should relate to a given search condition.
comparison operator
Which type of view is created from the following SQL command? CREATE OR REPLACE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit, name FROM books NATURAL JOIN publisher;
complex
Which of the following functions is similar to using the (| |) symbol to combine the contents of two character strings?
concat
Which command will create a synonym for a table?
create synonym synonymname for tablename
A ____ is a group of interrelated files.
database
A ____ is a storage structure designed to hold a collection of data.
database
The ____________________ TABLE command can be used to delete a table from a database.
delete
The ____________________ keyword can be used with the COUNT function to specify that only unique occurrences should be included in the results.
distinct
If a column alias contains a blank space, it must be enclosed in ____.
double quotation marks ("")
Which of the following commands can be used to remove a sequence from a database?
drop sequence
A join based upon a column from each table containing equivalent data is known as a(n) ____.
equality join
A COMMIT is explicitly issued when the user exits SQL*Plus. _________________________
false
A column qualifier is used to indicate the table containing the column being referenced.
false
A constraint can only be created as part of the CREATE TABLE command.
false
A database must be shut down before the ALTER TABLE command can be executed.
false
A role can be removed from the database with the DELETE ROLE command. _________________________
false
A sequence can be used to speed up row retrieval.
false
A table must contain a minimum of two columns. _________________________
false
DML operations can be performed through a simple view even if the operation will violate constraints on the underlying table.
false
Each column can only be included in one constraint.
false
NOT, ALL, and OR are all considered logical operators.
false
Once a privilege has been granted to a role, it cannot be revoked without re-creating the role.
false
SYSTEMDATE can be used to include the computer's date in a SQL statement. _________________________
false
When functions are nested, the outer function is solved or executed first.
false
In a "one-to-many" relationship, which constraint is usually added to the "many" table?
foreign key
If you are creating a new table containing four columns from an existing table and want to change the name of one of the columns when it is created in the new table, add a column list containing ____ names in the CREATE TABLE clause.
four
The name of the table containing the data to be retrieved is specified in the ____________________ clause.
from
The ____ keywords are used to grant object privileges to users and roles.
grant... on... to
Functions that return one result per group of rows are called ____ functions.
group, multiple-row, aggregate
Any type of subquery can be used in the FROM, WHERE, or ____________________ clause of a SELECT statement.
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 operators is used with a multiple-row subquery?
in, any, all
If a column contains a large number of NULL values, a(n) ____________________ can quickly help determine the rows that contain NULL values and those that do not.
index
The subquery used to create a(n) ____ view can contain an ORDER BY clause.
inline
The following SQL statement contains what type of subquery? SELECT b.title, b.retail, a.category, a.cataverage FROM books b, (SELECT category, AVG(retail) cataverage FROM books GROUP BY category) a WHERE b.category = a.category;
inline view
When an SQL statement contains nested functions, the ____________________ function is always resolved first.
inner
Which of the following set operators can be used to make certain that only the rows returned by both queries are displayed in the results?
intersect
A(n) ____ has a predefined meaning in Oracle 12c.
keyword
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 3
The ____ command will prevent two users from trying to make changes to the same table at the same time.
lock table
When the WHERE clause contains multiple types of operators, which of the following is resolved last?
logical operators
A foreign key is usually found on which side of a relationship?
many
If the last values generated by a sequence were 277 and 278, which of the following changes cannot be initiated by the ALTER SEQUENCE command?
maxvalue 200
A table alias cannot be assigned in the FROM clause if which of the following keywords is used to join tables?
natural join
A subquery is a(n) ____________________ query — one complete query inside another query
nested
A(n) ____ table is a table that does not contain the primary key that a view uses to uniquely identify each record being displayed by the view.
non-key-preserved
table is a table that does not contain the primary key being used by a complex view to uniquely identify each row displayed by the view.
non-key-preserved
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
Data in third normal form (3NF) contains which of the following?
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 SQL statements will return an error message, based upon the contents of the CUSTOMERS table?
none of the above
Which of the following types of columns will automatically right-align the data when it is displayed?
numeric
A subquery, except one in the FROM clause, cannot contain a(n) ____ clause.
order by
A(n) ____ synonym is used by an individual to reference objects owned by that person.
private
A SELECT statement is also referred to as a ____________________.
query
If a view was created with the WITH READ ONLY constraint, to remove the constraint you will need to ____.
re-create the view without the option
The ____ command is used to revoke a role.
revoke rolename from username;
The system privileges assigned to roles can be seen from the ____________________ data dictionary view.
role_sys_privs
The ____________________ function is used to round numeric fields to a stated position.
round
A record in the logical design of a database corresponds to a ____ in the physical database.
row
Which of the following are examples of object privileges?
select and update
A(n) ____________________ join is required when a table must be joined to itself.
self
Which of the following types of joins refers to joining a table to itself?
self-join
A(n) ____ generates sequential integers that can be used by organizations to assist with internal controls or simply to serve as a primary key for a table
sequence
Which type of view is created with the following command? CREATE VIEW inventory AS SELECT isbn, title, retail price FROM books WITH READ ONLY;
simple
A(n) ____ in a SQL command instructs Oracle 12c to use a substituted value in place of the variable at the time the command is actually executed.
substitution variable
Which of the following is used to return a portion of a character string?
substr
Which of the following keywords will return the value of the date according to the computer?
sysdate
The multi-step process used when creating a new system is referred to as ____.
systems development life cycle
The ____ function can be used to convert dates and numbers to a formatted character string.
to_char
A user who is issuing DML commands can save modified data or undo uncommitted changes by issuing ____ statements.
transaction control
A collection of records is a file.
true
A group function can be used in a(n) inline view. _________________________
true
SUM, AVG, and COUNT are all considered group functions. _________________________
true
The IN operator can be used with single-row, multiple-row, or multiple-column subqueries. _________________________
true
The highest possible value for a descending sequence is -1.
true
The majority of operations performed on a typical database table utilize the SELECT statement.
true
A table name can contain the number sign and ____________________ symbols.
underscore
A full outer join cannot be created in the ____________________ clause.
where
If the ____ clause of the UPDATE command is omitted, then all the rows in the specified table will be changed.
where
The ____________________ keywords are used to make certain any DML operations do not prevent a row from being accessible to the view after the DML operation is executed.
with check option
Commands used to modify data are called ____ commands.
Data manipulation language
Analyzing historical sales data stored in a database is commonly referred to as ____.
Data mining
A field in the logical design of a database corresponds to a ____ in the physical database.
COLUMN
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 cannot be used with date columns?
AVG
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
Which command will delete a sequence?
DROP SEQUENCE sequencename
Which of the following SQL statements will display the gift or gifts that should be sent with order# 1003?
SELECT gift FROM promotion, orderitems oi, books b WHERE retail BETWEEN minretail AND maxretail AND oi.isbn = b.isbn AND order# = 1003;
Which of the following SQL statements would most likely be used to generate the partial output shown above?
SELECT name FROM SYSTEM_PRIVILEGE_MAP;
The ____ function is used to calculate the total amount stored in a numeric field.
SUM
Which of the following can be used to provide an alternative, simplified name, to identify database objects?
SYNONYM
If a constraint applies to more than one column, the constraint must be created at the ____________________ level.
TABLE
If a user has a shared lock on a table, this will prevent any other user from obtaining a shared or exclusive lock on the same table.
TRUE
Retrieving only specific rows from a table is a process known as selection.
TRUE
The CONSTRAINT keyword is required if the user is going to assign a name to a constraint.
TRUE
The CREATE TABLE command is used to create a table in Oracle 12c. _________________________
TRUE
If a column is defined as NUMBER(5,2), which of the following is true?
The column can store a total of 5 digits, with 3 on the left side of the decimal point, and 2 on the right
