SQL FInal Exam
Which command will modify a sequence?
ALTER SEQUENCE sequencename
Which of the following commands can be used to make structural changes to an existing table?
ALTER TABLE
Based on the structure of the PROMOTION table, which of the following commands will ensure that the value entered for the MINRETAIL column is less than the value entered for the MAXRETAIL column?
ALTER TABLE promotion ADD CHECK ( minretail < maxretail);
If the MAXRETAIL column of the PROMOTION table has been set as unused, which of the following commands is valid?
ALTER TABLE promotion DROP UNUSED COLUMNS;
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
Which statement is true about the view created with the following command?
IT IS NOT DML operations are NOT allowed on the data displayed by the view.
In the USER_CONSTRAINTS view, the value displayed in the CONSTRAINT_TYPE column will be a(n) ____ for a NOT NULL constraint.
IT is NOT N
Which of the following serves the same basic purpose as an index in a book, by allowing users to quickly locate specific records?
Index
Which of the following commands will change the name of the LASTNAME column to LAST_NAME in the CUSTOMERS table?
None of the above
Which of the following is not a constraint type in Oracle?
Reference
Which constraint ensures that the data in a view cannot be changed?
WITH READ ONLY
Who can remove a public synonym from the database?
a user with DBA privileges
What is the procedure for assigning new names for the columns that are displayed by a view?
all of the above
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?
authentication
The NOT NULL constraint can only be created at the ____ level.
column
Which of the following terms refers to commands that are used to create or modify database tables?
data definition language (DDL)
Which of the following statements about views is incorrect?
it is NOT Views assist users who do not have the training to issue complex SQL inquiries.
Which of the following is defined in Oracle as simply anything that has a name and a defined structure?
object
The last value generated by a sequence is stored in the ____ pseudocolumn.
CURRVAL
Which command will remove an existing role from a database?
DROP ROLE rolename;
Which command will delete a sequence?
DROP SEQUENCE sequencename
Which of the following SQL statements will delete the private synonym named MYSYNON?
DROP SYNONYM mysynon;
Which command will delete a user account from Oracle?
DROP USER username;
Which of the following types of constraints is used to enforce referential integrity?
FOREIGN KEY
In a "one-to-many" relationship, which constraint is usually added to the "many" table?
Foreign Key
Which of the following SQL statements will grant all object privileges for the ORDERS table to user RTHOMAS?
GRANT ALL ON orders TO rthomas;
Which of the following commands will allow a user to connect to the Oracle database?
GRANT CREATE SESSION TO username;
Which of the following SQL statements will grant the SELECT privilege for the ORDERS table to all database users?
GRANT SELECT ON orders TO PUBLIC;
Which command will grant a system privilege to a user?
GRANT systemprivilege TO username;
A negative number can be assigned to the ____ clause to generate sequential numbers in decreasing order.
INCREMENT BY
Which of the following commands will grant a role to a user?
It is NOT GRANT rolename FOR username;
aren't allowed in the CREATE VIEW command.
It is NOT aren't allowed in the CREATE VIEW command.
How can constraints be added to a table?
It is NOt as part of the ALTER TABLE command
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
Which of the following keywords can be used to change the size, datatype, and/or default value of an existing column?
MODIFY
Which of the following is a valid table name?
MYTABLE
Which of the following statements about creating constraints is incorrect?
The answer is NOT NONE OF THE ABOVE
A(n) ____ stores a query and is used to access data in the underlying tables.
View
Which of the following statements is incorrect?
Views can be modified by using the ALTER VIEW...MODIFY command.
Which of the following keywords must be included with the GRANT command to allow the user to grant system privileges to other users?
WITH ADMIN OPTION
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.
WITH CHECK OPTION
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
Which of the following are used to enforce business rules?
constraints
Which of the following is the standard abbreviation for the constraint FOREIGN KEY?
fk
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
Which of the following is a valid SQL statement when referencing a sequence?
it is NOT INSERT INTO orderitems VALUES (nextvalue, 1, 811794939, 1);
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 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
Which of the following statements about creating a username is incorrect?
A user name cannot contain numbers.
A PRIMARY KEY constraint can be added to an existing table by using the ____ clause of the ALTER TABLE command.
ADD
Which of the following commands is used to establish a user account?
CREATE USER username IDENTIFIED BY password;
Based on the structure of the CUSTOMERS table, which of the following is a valid SQL statement?
CREATE INDEX customers_name_idx ON customers (lastname, firstname);
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 command will establish a new role?
CREATE ROLE rolename;
When creating a sequence, which of the following is not an optional clause?
CREATE SEQUENCE
Which of the following symbols cannot be used in a table name?
%
If a sequence is set to pre-generate sequential values, ____ values will be generated at one time by default.
20
Which of the following SQL statements will change the password for user RTHOMAS to ANEW5PASSWORD?
ALTER USER rthomas IDENTIFIED BY anew5password;
Which of the following commands will change a user's password?
ALTER USER username IDENTIFIED BY newpassword;
Which of the following commands can be used to create a sequence for the CUSTOMERS table?
CREATE SEQUENCE customers_customer#;
Which command will create a sequence named NEWSEQUENCE to generate a series of integers?
CREATE SEQUENCE newsequence;
The ____ privilege will allow a user to connect to the Oracle11g database.
CREATE SESSION
Which command will create a synonym for a table?
CREATE SYNONYM synonymname FOR tablename;
Which command instructs Oracle to create a new table?
CREATE TABLE
Which of the following is a pseudocolumn?
NEXTVAL
Which of the following keywords is used to actually generate a sequence value?
NEXTVAL
An index is not implicitly created when which type of constraint is created?
NOT NULL
Which of the following clauses is required when granting an objective privilege to a user
ON
Only one ____ constraint can exist for each table.
PRIMARY KEY
Which of the following commands will change the name of the PROMOTION table to GIFT?
RENAME promotion TO gift;
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;
Which of the following will revoke a user's object privileges?
REVOKE objectprivilege ON objectname FROM username;
What is the syntax for the default constraint name provided by the Oracle server?
SYS_Cn
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 by the following command? CREATE VIEW OR REPLACE outstanding AS SELECT customer#, order#, orderdate, shipdate FROM orders WHERE shipdate IS NULL;
Simple
Which of the following can be used to provide an alternative, simplified name, to identify database objects?
Synonym
The UNIQUE constraint differs from the PRIMARY KEY constraint in what way?
The UNIQUE constraint allows NULL values.
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
A PRIMARY KEY that consists of more than one column is called a(n) ____ key.
composite
