Intro to Database Midterm
Which of the following statements about roles is incorrect?
*A role is a group, or collection, of privileged passwords and user names.* A role is a group, or collection, of privileges. Rather than assign the same privileges again and again to different users, a simpler approach is to assign the privileges to a role, and then assign the role to the users. Users can be assigned several different roles, based on the tasks they perform.
Which of the following statements is incorrect?
*A view can be created with the CREATE VIEW command.* Views can be modified by using the ALTER VIEW...MODIFY command. A view cannot be modified; if you need to change it, you must use the CREATE OR REPLACE VIEW keywords. A view cannot be given the same name as another database object in the same schema.
Which of the following commands can be used to modify an index?
*ALTER INDEX...MODIFY* ALTER INDEX...CHANGE ALTER TABLE...INDEX none of the above
Which of the following SQL statements will assign the DBA role as the default role for user RTHOMAS?
*ALTER USER rthomas SET DEFAULT TO DBA;* SET ROLE DBA; ALTER USER rthomas DEFAULT ROLE dba; none of the above
____ aren't allowed in the CREATE VIEW command.
*Arithmetic expressions* ORDER BY clauses Group functions GROUP BY clauses
What is the default mode for the CREATE VIEW command?
*COMPILE* *NOCOMPILE* FORCE NOFORCE
What is the default mode for the CREATE VIEW command?
*COMPILE* NOCOMPILE FORCE NOFORCE
Which command will create a sequence named NEWSEQUENCE to generate a series of integers?
*CREATE SEQUENCE newsequence;* CREATE SEQUENCE STARTING WITH 5; CREATE SEQUENTIAL newsequence STARTING WITH 5; none of the above
Which of the following is a pseudocolumn?
*CURRENTVAL* DUAL *NEXTVAL* *both a and c*
A negative number can be assigned to the ____ clause to generate sequential numbers in decreasing order.
*DECREMENT BY* INCREMENT BY DECREASE BY GENERATE BY
Which of the following commands can be used to remove a sequence from a database?
*DELETE SEQ* DROP SEQ REMOVE SEQUENCE DROP SEQUENCE
Which of the following statements about performing DML operations on complex views is correct?
*DML operations cannot be performed on a non primary key-locked table* DML operations cannot be performed on a non key-preserved table. DML operations can be performed if a view contains a group function or a GROUP BY clause. none of the above
Which of the following SQL statements will delete the private synonym named MYSYNON?
*DROP PRIVATE SYNONYM mysynon;* DROP PUBLIC SYNONYM mysynon; DROP SYNONYM mysynon; DELETE PRIVATE SYNONYM mysynon;
If you want to create a view based upon a table or tables that do not yet exist, or are currently unavailable (e.g.,off-line), what keyword can you use to avoid receiving an error message?
*FORCE* NOERROR OVERRIDE none of the above
Which operators can be combined with other comparison operators to treat the results of a subquery as a set of values, rather than as individual values?
*IN and ANY* IN and ALL ALL and ANY EXISTS and IN
The ____ operator indicates that the records processed by the outer query must match one of the values returned by the subquery.
*IN* >ANY <ALL >ALL
When creating a sequence, which of the following is not an optional clause?
*INCREMENT BY value* CYCLE | NOCYCLE MAXVALUE value | NOMAXVALUE CREATE SEQUENCE
Which of the following is a valid SQL statement when referencing a sequence?
*INSERT INTO orderitems VALUES (currval, 1, 811794939, 1);* INSERT INTO orderitems VALUES (currentval, 1, 811794939, 1); INSERT INTO orderitems VALUES (nextvalue, 1, 811794939, 1); none of the above
Which statement about the view created from the following SQL statement is correct? CREATE VIEW balancedue AS SELECT customer#, order#, SUM(quantity*retail) amtdue FROM customers NATURAL JOIN orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#, order#;
*No DML operations can be performed through the view.* DML operations can only be performed on the CUSTOMERS table since it is the primary key for the view. Only rows can be added through the view — no other DML operations are allowed. none of the above
The ____ constraint ensures that any DML operations performed on the view (e.g., adding rows, changing data) will not prevent the row from being accessed by the view because it no longer meets the condition in the WHERE clause.
*ON UPDATE CHECK* WITH CHECK OPTION WITH READ ONLY READ-WRITE ONLY
The ____ command is used to revoke a role.
*REVOKE rolename FROM username;* ALTER rolename REVOKE username; DROP rolename FROM username; DROP rolename REVOKE FROM username;
Which of the following are examples of object privileges?
*SELECT and UPDATE* CREATE USER and CREATE TABLE UPDATE ANY TABLE and ALTER ANY TABLE all of the above
Which of the following SQL statements would most likely be used to generate the partial output shown above?
*SELECT name FROM SYSTEM_PRIVILEGE_MAP;* SELECT * FROM USER_CURRENT_PRIVILEGES; SELECT * FROM SESSION_PRIVS; SELECT name FROM SESSION_PRIVS;
Oracle10g will begin each sequence with the value of one, unless another value is specified in the ____ clause.
*START WITH* INTERVAL SIZE INCREMENT BY VALUE START
Which type of view is created by the following command? CREATE VIEW OR REPLACE outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL;
*Simple* complex Inline none of the above
Which statement is true about the view created with the following command? CREATE OR REPLACE VIEW inventory AS SELECT isbn, title, retail price FROM books;
*The command creates a complex view.* DML operations are not allowed on the data displayed by the view. A database object named INVENTORY may already exist. all of the above
Which of the following statements about NEXTVAL and CURRVAL is incorrect?
*The pseudocolumn NEXTVAL is used to actually generate the sequence value.* After the value is generated, it is stored in the CURRVAL pseudocolumn so it can be referenced again by a user. A reference to CURRVAL will not cause Oracle10g to generate a new sequence number; a reference to NEXTVAL, however, will generate a new number. NEXTVAL and CURRVAL are pseudocolumns that store unique names for database objects.
Which statement about the view created from the following command is correct, assuming no constraints exist on the underlying table? CREATE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit FROM books;
*The retail price of a book can be changed.* The profit generated by a book can be changed. The profit generated by a book can be added. Values for a new book can be added for all five columns displayed by the view.
To verify individual settings for the clauses of a sequence, you can query the ____ table in the data dictionary.
*USER_SEQUENCES* V$SEQUENCES DISPLAY_SEQUENCES VIEW_SEQUENCES
Which of the following keywords must be included with the GRANT command to allow the user to grant object privileges to other users?
*WITH GRANT OPTION* WITH ADMIN OPTION WITH SYSADMIN OPTION WITH DBA OPTION
Which of the following keywords must be included with the GRANT command to allow the user to grant system privileges to other users?
*WITH GRANT OPTION* WITH ADMIN OPTION WITH SYSADMIN OPTION WITH DBA OPTION
The >ALL operator indicates that a value must be ____ value returned by the subquery.
*more than the highest* less than the highest more than the lowest less than the lowest
Partial list of privileges The partial listing of privileges shown above displays what type of privileges?
*object* role system data dictionary
An outer query is also referred to as a(n) ____ query.
*parent query* outer view outline view all of the above
Which of the following terms refers to a collection of privileges?
*role* grouping groupset accesset
Which of the following describes a subquery used in a FROM clause to create a "temporary" table that can be referenced by the SELECT and WHERE clauses of the outer query?
*simple view* complex view inline view "TOP-N" analysis
Which of the following describes a type of view that is based upon a subquery that only references one table and does not include any group functions, expressions, or GROUP BY clauses?
*simple view* complex view inline view "TOP-N" analysis
Which of the following types of views cannot contain grouped data?
*simple* complex inline view all of the above
Which of the following can be used in a WHERE clause?
*single-row subquery* multiple-column subquery multiple-row subquery all of the above
The <> operator is referred to as a(n) ____ operator.
*single-row* multiple row multiple-column correlated
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);
*single-row* multiple-row multiple-column correlated
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* multiple-row multiple-column inline view
A(n) ____ stores a query and is used to access data in the underlying tables.
*view* constraint function argument
Which of the following statements about creating a username is incorrect?
A user name can contain up to 30 characters. A user name can contain the symbols _ , $, and #. *A user name cannot contain numbers.* A user name can contain upper-case letters.
Which command will delete a sequence?
ALTER SEQUENCE...DELETE *ALTER SEQUENCE...DROP* DELETE SEQUENCE sequencename DROP SEQUENCE sequencename
Which command will delete a view?
ALTER TABLE...DROP VIEW *DROP VIEW* ALTER TABLE...DELETE VIEW DELETE VIEW
Based on the structure of the BOOKS table, which of the following is a valid SQL statement?
CREATE INDEX books_profit_idx ON books "retail-cost"; *CREATE INDEX books_title_idx ON books (INITCAP(title));* CREATE INDEX books_profit_idx FOR books "retail-cost"; CREATE INDEX books_title_idx FOR books (INITCAP(title);
Which command will establish a new role?
CREATE NEW ROLE rolename; *CREATE ROLE rolename;* CREATE ROLE rolename FOR username; CREATE ROLE rolename WITH privilege;
Which of the following commands is used to establish a user account?
CREATE NEW USER username IDENTIFIED BY password; *CREATE USERNAME username IDENTITY password;* CREATE USER username PASSWORD password; CREATE USER username IDENTIFIED BY password;
Which of the following clauses is required when granting an objective privilege to a user?
FOR *ON* OBJECT none of the above
Which clause is used when the group results of a subquery need to be restricted, based on some condition?
GROUP BY *HAVING* WHERE ORDER BY
Which SQL statement can be executed based upon the view created from the following command, assuming no constraints exist on the underlying table? CREATE VIEW prices AS SELECT isbn, title, cost, retail, retail-cost profit FROM books;
INSERT INTO prices (title, cost, retail, profit) VALUES ('A NEW BOOK', 49.99, 69.99, 20); INSERT INTO prices (title, cost, retail, profit) VALUES ('A SECOND BOOK', 49.99, 39.99, -10); INSERT INTO prices (title, cost, retail) VALUES ('A NEW BOOK', 49.99, 69.99); *none of the above*
Which option is used to have Oracle11g pre-generate a set of values and store those values in the server's memory?
INTERVAL STORE *CACHE VALUE* CACHE INCREMENT BY
In Oracle11g, a(n) ____ allows a series of DML actions to occur.
Inline view *MERGE statement* DML subquery None of the above
What is the definition of an inline view?
It is a permanent database object that can be referenced by subsequent queries. It is a view that retrieves data from one or more tables, and can contain functions and grouped data. *It is a temporary data source that exists only while a command is being executed.* It is a temporary pseudo column.
Which of the following statements about complex views is incorrect?
It is created with the same CREATE VIEW command as a simple view. It retrieves or derives data from one or more tables. *All DML operations can be performed on complex views, just like simple views.* It may contain functions or grouped data.
DML operations are not allowed on a view that is created with the ____ keyword.
JOIN NATURAL JOIN *DISTINCT* REPLACE
Based on the contents of the BOOKS table, which line of the following SQL statement contains an error? 1 SELECT isbn, title 2 FROM books 3 WHERE pubid = 4 (SELECT pubid 5 FROM books 6 WHERE title = 'SHORTEST POEMS') 7 AND retail-cost > 8 (SELECT AVG(retail-cost) 9 FROM books);
Line 3 Line 5 Line 7 *none of the above*
The last value generated by a sequence is stored in the ____ pseudocolumn.
NEXTVAL DUAL *CURRENTVAL* CURRVAL
The last value generated by a sequence is stored in the ____ pseudocolumn.
NEXTVAL DUAL CURRENTVAL *CURRVAL*
The last value generated by a sequence is stored in the ____ pseudocolumn.
NEXTVAL DUAL *CURRENTVAL* CURRVAL
Which of the following keywords is used to actually generate a sequence value?
NEXTVALUE *NEXTVAL* GENERATEVAL GENERATE
If the PUBLIC keyword is not included when a synonym is created, the command will create a(n) ____ synonym, by default.
PUBLIC functional *private* objective
Which of the following commands will revoke the SELECT privilege for the ORDERS table previously granted to user RTHOMAS?
REVOKE SELECT FROM rthomas; *REVOKE SELECT ON orders FROM rthomas;* REVOKE SELECT FOR orders FROM rthomas; UNGRANT SELECT FROM rthomas;
Which of the following will revoke a user's object privileges?
REVOKE objectprivilege ON OBJECT objectname FROM username; *REVOKE objectprivilege ON objectname FROM username;* REVOKE objectprivilege ON objectname FOR username; DROP objectprivilege ON objectname FROM username;
DML operations are not allowed on a view that includes the pseudo column ____.
ROWNUMBER *DISTINCTROW* ROWNUM NUMROW
A subquery must include a(n) ____ clause.
SELECT FROM WHERE *both a and b*
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 customer# = (SELECT state FROM customers WHERE state = 'NJ'); *SELECT customer# FROM customers WHERE state = (SELECT state FROM customers WHERE customer#=1013);* SELECT customer# FROM customers WHERE customer# IN (SELECT state FROM customers WHERE state = 'NJ'); SELECT customer# FROM customers WHERE state EXISTS (SELECT state FROM customers WHERE customer#=1013);
Based on the contents of the BOOKS table, which of the following SQL statements will return an error message?
SELECT title FROM books WHERE retail = (SELECT MAX(retail) FROM books); SELECT title FROM books WHERE retail IN (SELECT MAX(retail) FROM books); SELECT title FROM books WHERE retail > (SELECT MAX(retail) FROM books); *none of the above*
When working with cached sequence values, which of the following is correct?
The cache option instructs Oracle10g to return the sequence values in the same order in which the user requests were received. *The cache option instructs Oracle10g not to generate any more numbers after the minimum or maximum value has been reached.* When a value has been generated and cached, that value has been assigned and cannot be regenerated until the sequence begins a new cycle. The highest value for a cached number is 10^44.
Which statement is not true about the view created with the following command? CREATE VIEW inventory AS SELECT isbn, title, retail price FROM books;
The command creates a simple view. *DML operations are not allowed on the data displayed by the view.* A view named INVENTORY did not previously exist. all of the above
Which of the following statements about granting object privileges is incorrect?
The grantor has a choice to either grant individual privileges, or to use the ALL keyword in the GRANT clause. *The FROM clause is used to identify the password of the user to which the privilege(s) applies.* The TO clause identifies the user or role receiving the privilege. The WITH GRANT OPTION gives the user the ability to grant the same object privileges to other users.
Which statement is true about the view created with the following command? CREATE VIEW outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL WITH CHECK OPTION;
The order# of an order cannot be changed through the view. *The shipping date of an order cannot be changed through the view.* No DML operations are permitted through the view. none of the above
Which of the following statements about granting system privileges is incorrect?
The system privilege being assigned is identified in the GRANT clause. The user(s) or role(s) receiving the system privilege is identified in the TO clause. *WITH ADMIN OPTION is used to allow any user or role identified in the TO clause to grant the system privilege(s) to any other database users.* WITH EXCLUSIVE MODE is used to forbid any user not identified in the TO clause from granting the system privilege(s) to any other database users.
Which of the following statements about privileges is incorrect?
There are five types of privileges, including DATATYPE, ROLETYPE, and USERNAME privileges. System privileges allow access to the Oracle10g database and let users perform DDL operations such as CREATE, ALTER, and DROP on database objects. Object privileges allow users to perform DML operations on the data contained within the database objects. *When a user creates an object, he or she automatically has all the object privileges associated with that object.*
Which of the following commands can be used to revoke system or object privileges previously granted to a user?
UNGRANT REMOVE *REVOKE* DROP
A function-based index can be created based on which of the following?
UPPER(lastname) (retail-cost) UPPER(firstname) *all of the above*
Which of the following statements about performing DML operations on complex views is correct?
Values cannot be inserted into columns containing date data. *Values cannot be inserted into columns that are based on arithmetic expressions.* Values cannot be inserted into columns containing an ORDER BY clause. both b and c
Which of the following statements about views is incorrect?
Views assist users who do not have the training to issue complex SQL inquiries. *Views restrict users' access to sensitive data.* Views are database objects that actually store data. A view can be referenced in a SELECT...FROM statement, just like any table.
Which constraint ensures that the data in a view cannot be changed?
WITH CHECK OPTION WITH READ OPTION *WITH READ ONLY* NO WRITE OPTION
Which of the following refers to the process of ensuring that individuals trying to access the system are who they say they are, thus preventing them from illegally accessing data?
authorization *authentication* individualization accreditation
A(n) ____ subquery is one that can return several rows of results.
correlated single-row *multiple-row* uncorrelated
The results of a subquery are passed back as input to the ____ query.
inner *outer* correlated uncorrelated
A complete query nested inside another query is called a(n) ____.
inner view *subquery* child view all of the above
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) ____.
inner view outer view *inline view* natural view
The <ALL operator indicates that a value must be ____ value returned by the subquery.
more than the highest less than the highest more than the lowest *less than the lowest*
Which of the following can be considered potential threats to an organization's data?
natural disaster disgruntled employees computer criminals *all of the above*
A view based on the contents of one table that uses an expression for one of the columns is considered a(n) ____ view.
simple *complex* inline outer
Which of the following describes a subquery used in a FROM clause to create a "temporary" table that can be referenced by the SELECT and WHERE clauses of the outer query?
simple view complex view inline view *"TOP-N" analysis*
The following SQL statement contains which type of subquery? SELECT title, retail, (SELECT AVG(retail) FROM books) FROM books;
single-row *multiple-row* multiple-column inline view
A(n) ____ synonym is used by others to access an individual's database objects.
unique duplicated *public* private
A(n) ____ synonym is used by an individual to reference objects owned by that person.
unique duplicated public *private*
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.
view *index* synonym sequence
