Database Programming with SQL FINAL Part 2

Section 12
(Answer all questions in this section)

1. What is the quickest way to use today’s date when you are creating a new row? Mark for Review
(1) Points

Use the SYSDATE function. (*)
Use the TODAYS_DATE function.
Simply write today’s date in the format ‘dd-mon-rrrr’.
Simply use the keyword DATE in the insert statement.

Incorrect Incorrect. Refer to Section 12 Lesson 1.
2. Insert statements can be combined with subqueries to create more than one row per statement. True or False? Mark for Review
(1) Points

True (*)
False

Correct Correct
3. The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FAST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)

You want to execute one DML statement to change the salary of all employees in department 10 to equal the new salary of employee number 89898. Currently, all employees in department 10 have the same salary value. Which statement should you execute?

Mark for Review
(1) Points

UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898)
WHERE department_id = 10;
(*)

UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898 AND department_id = 10);
UPDATE employees
SET salary = (SELECT salary FROM employees WHERE employee_id = 89898);
UPDATE employees
SET salary = SELECT salary FROM employees WHERE employee_id = 89898;

Correct Correct
4. Examine the structures of the PRODUCTS and SUPPLIERS tables:
SUPPLIERS:
SUPPLIER_ID NUMBER NOT NULL, Primary Key
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)

PRODUCTS:
PRODUCT_ID NUMBER NOT NULL, Primary Key
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER Foreign key to SUPPLIER_ID of the SUPPLIERS table
CATEGORY_ID NUMBER
QTY_PER_UNIT NUMBER
UNIT_PRICE NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER

You want to delete any products supplied by the five suppliers located in Atlanta. Which script should you use?

Mark for Review
(1) Points

DELETE FROM products
WHERE supplier_id IN
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = ‘ATLANTA’);
(*)

DELETE FROM suppliers
WHERE supplier_id IN
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = ‘ALANTA’);
DELETE FROM products
WHERE UPPER(city) = ‘ATLANTA’;
DELETE FROM products
WHERE supplier_id =
(SELECT supplier_id FROM suppliers WHERE UPPER(city) = ‘ATLANTA’);

Correct Correct
5. Is the following statement valid, i.e. is it allowed to update rows in one table, based on a subquery from another table?
UPDATE copy_emp
SET department_id = (SELECT department_id
FROM employees
WHERE employee_id = 100)
WHERE job_id = (SELECT job_id
FROM employees
WHERE employee_id = 200);

Mark for Review
(1) Points

No, this statement will return an error.
Yes, this is a perfectly valid statement. (*)
The statement will fail because the subqueries are returning data from different rows.
No, this does nothing.

Correct Correct
====

Section 12
(Answer all questions in this section)

6. Which of the following represents the correct syntax for an INSERT statement? Mark for Review
(1) Points

INSERT VALUES INTO customers (3178 J. Smith 123 Main Street Nashville TN 37777;
INSERT INTO customers VALUES ‘3178’ ‘J.’ ‘Smith’ ‘123 Main Street’ ‘Nashville’ ‘TN’ ‘37777’;
INSERT INTO customers VALUES (‘3178’, ‘J.’, ‘Smith’, ‘123 Main Street’, ‘Nashville’, ‘TN’, ‘37777’); (*)
INSERT customers VALUES 3178, J., Smith, 123 Main Street, Nashville, TN, 37777;

Correct Correct
7. Multi-table inserts are used when the same source data should be inserted into _____________ target table. Mark for Review
(1) Points

A data warehouse
A very large
Ten
More than one (*)

Correct Correct
8. A multi-table insert statement must have a subquery at the end of the statement. (True or False?) Mark for Review
(1) Points

True (*)
False

Correct Correct
Section 13
(Answer all questions in this section)

9. To store large amounts of text you should simply create a series of VARCHAR2 columns in a table. True or False? Mark for Review
(1) Points

True
False (*)

Correct Correct
10. The ELEMENTS column is defined as:
NUMBER(6,4)
How many digits to the right of the decimal point are allowed for the ELEMENTS column?

Mark for Review
(1) Points

Zero
Two
Six
Four (*)

Correct Correct

======
Section 13
(Answer all questions in this section)

11. Evaluate this CREATE TABLE statement:
1. CREATE TABLE customer#1 (
2. cust_1 NUMBER(9),
3. sales$ NUMBER(9),
4. 2date DATE DEFAULT SYSDATE);

Which line of this statement will cause an error?

Mark for Review
(1) Points

2
1
4 (*)
3

Correct Correct
12. You want to create a database table that will contain information regarding products that your company released during 2001. Which name can you assign to the table that you create? Mark for Review
(1) Points

PRODUCTS–2001
PRODUCTS_2001 (*)
2001_PRODUCTS
PRODUCTS_(2001)

Correct Correct
13. DCL, which is the acronym for Data Control Language, allows: Mark for Review
(1) Points

The ALTER command to be used.
The CONROL TRANSACTION statement can be used.
A Database Administrator the ability to grant privileges to users. (*)
The TRUNCATE command to be used.

Correct Correct
14. To completely get rid of a table, its contents, its structure, AND release the storage space the keyword is: Mark for Review
(1) Points

TRUNCATE
KILL
DELETE
DROP (*)

Correct Correct
15. You want to issue the following command on a database that includes your company’s inventory information:
ALTER TABLE products SET UNUSED COLUMN color;
What will be the result of issuing this command?

Mark for Review
(1) Points

The column named COLOR in the table named PRODUCTS will be created.
The column named COLOR in the table named PRODUCTS will not be returned in subsequent reads of the table by Oracle, as it has been deleted logically. (*)
The column named COLOR in the table named PRODUCTS will be assigned default values.
The column named COLOR in the table named PRODUCTS will be deleted.

Incorrect Incorrect. Refer to Section 13 Lesson 3.

========
Section 13
(Answer all questions in this section)

16. To do a logical delete of a column without the performance penalty of rewriting all the table datablocks, you can issue the following command: Mark for Review
(1) Points

Alter table modify column
Drop column “columname”
Alter table drop column
Alter table set unused (*)

Incorrect Incorrect. Refer to Section 13 Lesson 3.
17. The data type of a column can never be changed once it has been created. True or False? Mark for Review
(1) Points

True
False (*)

Correct Correct
18. Which of the following will correctly change the name of the LOCATIONS table to NEW_LOCATIONS? Mark for Review
(1) Points

ALTER TABLE LOCATIONS RENAME NEW_LOCATIONS
MODIFY TABLE LOCATIONS RENAME NEW_LOCATIONS
RENAME LOCATIONS TO NEW_LOCATIONS (*)
None of the above; you cannot rename a table, you can only CREATE, ALTER and DROP a table.

Correct Correct
19. Evaluate this statement:
ALTER TABLE inventory
MODIFY backorder_amount NUMBER(8,2);

Which task will this statement accomplish?

Mark for Review
(1) Points

Changes the definition of the BACKORDER_AMOUNT column to NUMBER(8,2) (*)
Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8 2)
Alters the definition of the BACKORDER_AMOUNT column to NUMBER
Alters the definition of the BACKORDER_AMOUNT column to NUMBER(2,8)
Alters the definition of the BACKORDER_AMOUNT column to NUMBER(8.2)

Correct Correct
Section 14
(Answer all questions in this section)

20. A table must have at least one not null constraint and one unique constraint. True or False? Mark for Review
(1) Points

True
False (*)

Correct Correct

===

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

Section 14
(Answer all questions in this section)

21. If the employees table has a UNIQUE constraint on the DEPARTMENT_ID column, we can only have one employee per department. True or False? Mark for Review
(1) Points

True (*)
False

Correct Correct
22. A table can only have one unique key constraint defined. True or False? Mark for Review
(1) Points

True
False (*)

Correct Correct
23. Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table? Mark for Review
(1) Points

REFERENTIAL
ON DELETE CASCADE
RESEMBLES
REFERENCES (*)

Correct Correct
24. Which statement about a non-mandatory foreign key constraint is true? Mark for Review
(1) Points

A foreign key value cannot be null.
A foreign key value must either be null or match an existing value in the parent table. (*)
A foreign key value must be unique.
A foreign key value must match an existing value in the parent table.

Correct Correct
25. The PO_DETAILS table contains these columns:
PO_NUM NUMBER NOT NULL, Primary Key
PO_LINE_ID NUMBER NOT NULL, Primary Key
PRODUCT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCTS table
QUANTITY NUMBER
UNIT_PRICE NUMBER(5,2)

Evaluate this statement:

ALTER TABLE po_details
DISABLE CONSTRAINT product_id_pk CASCADE;

For which task would you issue this statement?

Mark for Review
(1) Points

To disable the constraint on the PO_NUM column while creating a PRIMARY KEY index
To create a new PRIMARY KEY constraint on the PO_NUM column
To drop and recreate the PRIMARY KEY constraint on the PO_NUM column
To disable the PRIMARY KEY and any FOREIGN KEY constraints that are dependent on the PO_NUM column (*)

Correct Correct
==========
Section 14
(Answer all questions in this section)

26. You need to display the names and definitions of constraints only in your schema. Which data dictionary view should you query? Mark for Review
(1) Points

USER_CONS_COLUMNS
ALL_CONS_COLUMNS
USER_CONSTRAINTS (*)
DBA_CONSTRAINTS

Correct Correct
Section 15
(Answer all questions in this section)

27. Which option would you use when creating a view to ensure that no DML operations occur on the view? Mark for Review
(1) Points

WITH ADMIN OPTION
WITH READ ONLY (*)
NOFORCE
FORCE

Correct Correct
28. Given the following view, which operations would be allowed on the emp_dept view?
CREATE OR REPLACE VIEW emp_dept
AS SELECT SUBSTR(e.first_name,1,1) ||’ ‘||e.last_name emp_name,
e.salary,
e.hire_date,
d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id
AND d.department_id >=50;

Mark for Review
(1) Points

SELECT, INSERT
SELECT, DELETE
SELECT, UPDATE of all columns
SELECT, UPDATE of some columns, DELETE (*)

Incorrect Incorrect. Refer to Section 15 Lesson 2.
29. Which action can be performed by using DML statements? Mark for Review
(1) Points

Disabling an index
Creating PRIMARY KEY constraints
Deleting records in a table (*)
Altering a table

Correct Correct
30. Which of the following is true about ROWNUM? Mark for Review
(1) Points

It is the number assigned to each row returned from a query after it is ordered.
It is the number assigned to each row returned from a query as it is read from the table. (*)
It is the number of rows in a table.
None of the above

Correct Correct
Previous Page 6 of 10 Next Summary

=======================

Section 15
(Answer all questions in this section)

31. You must create a view that will display the name, customer identification number, new balance, finance charge, and credit limit of all customers.
You issue this statement:
CREATE OR REPLACE VIEW CUST_CREDIT_V
AS SELECT c.last_name, c.customer_id, a.new_balance, a.finance_charge, a.credit_limit
FROM customers c, accounts a
WHERE c.account_id = a.account_id WITH READ ONLY;

Which type of SQL command can be issued on the CUST_CREDIT_V view?

Mark for Review
(1) Points

INSERT
DELETE
SELECT (*)
UPDATE

Correct Correct
32. Evaluate this view definition:
CREATE OR REPLACE VIEW part_name_v
AS SELECT DISTINCT part_name
FROM parts
WHERE cost >= 45;

Which of the following statements using the PART_NAME_V view will execute successfully?

Mark for Review
(1) Points

DELETE FROM part_name_v
WHERE part_id = 56897;
SELECT *
FROM part_name_v;
(*)

UPDATE part_name_v
SET cost = cost * 1.23
WHERE part_id = 56990;
INSERT INTO part_name_v (part_id, part_name, product_id, cost)
VALUES (857986, ?cylinder?, 8790, 3.45);

Correct Correct
33. In order to query a database using a view, which of the following statements applies? Mark for Review
(1) Points

You can never see all the rows in the table through the view.
Use special VIEW SELECT keywords.
The tables you are selecting from can be empty, yet the view still returns the original data from those tables.
You can retrieve data from a view as you would from any table. (*)

Correct Correct
34. Which of the following statements is a valid reason for using a view? Mark for Review
(1) Points

Views are used when you only want to restrict DML operations using a WITH CHECK OPTION.
Views allow access to the data because the view displays all of the columns from the table.
Views are not valid unless you have more than one user.
Views provide data independence for infrequent users and application programs. One view can be used to retrieve data from several tables. Views can be used to provide data security. (*)

Correct Correct
Section 16
(Answer all questions in this section)

35. The CLIENTS table contains these columns:
CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY
LAST_NAME VARCHAR2(15)
FIRST_NAME VARCHAR2(10)
CITY VARCHAR2(15)
STATE VARCHAR2(2)

You want to create an index named ADDRESS_INDEX on the CITY and STATE columns of the CLIENTS table. You execute this statement:

CREATE INDEX clients
ON address_index (city, state);

Which result does this statement accomplish?

Mark for Review
(1) Points

An index named CLIENTS is created on the CITY and STATE columns.
An index named CLIENTS_INDEX is created on the CLIENTS table.
An error message is produced, and no index is created. (*)
An index named ADDRESS_INDEX is created on the CITY and STATE columns.

Correct Correct
====

Section 16
(Answer all questions in this section)

36. All tables must have indexes on them otherwise they cannot be queried. True or False? Mark for Review
(1) Points

True
False (*)

Correct Correct
37. Which of the following best describes the function of an index? Mark for Review
(1) Points

An index can prevent users from viewing certain data in a table.
An index can reduce the time required to grant multiple privileges to users.
An index can run statement blocks when DML actions occur against a table.
An index can increase the performance of SQL queries that search large tables. (*)

Correct Correct
38. A gap can occur in a sequence because a user generated a number from the sequence and then rolled back the transaction. True or False? Mark for Review
(1) Points

True (*)
False

Correct Correct
39. Which statement would you use to modify the EMP_ID_SEQ sequence used to populate the EMPLOYEE_ID column in the EMPLOYEES table? Mark for Review
(1) Points

ALTER TABLE employees ;
CREATE SEQUENCE emp_id_seq;
ALTER SEQUENCE emp_id_seq; (*)
ALTER SEQUENCE emp_id_seq.employee_id;

Incorrect Incorrect. Refer to Section 16 Lesson 1.
40. Which of the following best describes the function of the NEXTVAL virtual column? Mark for Review
(1) Points

The NEXTVAL virtual column displays only the physical locations of the rows in a table.
The NEXTVAL virtual column returns the integer that was most recently supplied by the sequence.
The NEXTVAL virtual column displays the order in which Oracle retrieves row data from a table.
The NEXTVAL virtual column increments a sequence by a predetermined value. (*)

Correct Correct

=====

Section 17
(Answer all questions in this section)

41. Regular expressions used as check constraints are another way to ensure data is formatted correctly prior to being written into the database table. True or False? Mark for Review
(1) Points

True (*)
False

Correct Correct
42. REGULAR EXPRESSIONS can be used as part of a contraint definition. (True or False?) Mark for Review
(1) Points

True (*)
False

Correct Correct
43. You want to grant user BOB the ability to change other users’ passwords. Which privilege should you grant to BOB? Mark for Review
(1) Points

The DROP USER privilege
The ALTER USER privilege (*)
The CREATE USER privilege
The CREATE PROFILE privilege

Correct Correct
44. Which of the following privileges must be assigned to a user account in order for that user to connect to an Oracle database? Mark for Review
(1) Points

OPEN SESSION
ALTER SESSION
CREATE SESSION (*)
RESTRICTED SESSION

Incorrect Incorrect. Refer to Section 17 Lesson 1.
45. Which of the following statements about granting object privileges is false? Mark for Review
(1) Points

Object privileges can only be granted through roles. (*)
To grant privileges on an object, the object must be in your own schema, or you must have been granted the object privileges WITH GRANT OPTION.
The owner of an object automatically acquires all object privileges on that object.
An object owner can grant any object privilege on the object to any other user or role of the database.

Correct Correct
====
Section 17
(Answer all questions in this section)

46. Granting an object privilege WITH GRANT OPTION allows the recipient to grant all object privileges on the table to other users. True or False? Mark for Review
(1) Points

True
False (*)

Incorrect Incorrect. Refer to Section 17 Lesson 2.
47. When a user is logged into one database, he is restricted to working with objects found in that database. True or False? Mark for Review
(1) Points

True
False (*)

Incorrect Incorrect. Refer to Section 17 Lesson 2.
Section 18
(Answer all questions in this section)

48. User BOB’s CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers;

What result will JANE see?

Mark for Review
(1) Points

JANE will receive an error message because she is not allowed to query the table while BOB is updating it.
20 (*)
2
22

Correct Correct
49. You need not worry about controlling your transactions. Oracle does it all for you. True or False? Mark for Review
(1) Points

True
False (*)

Incorrect Incorrect. Refer to Section 18 Lesson 1.
Section 19
(Answer all questions in this section)

50. Unit testing may be a composite of many different possible cases, or approaches, a user would opt to execute a transaction. True or False? Mark for Review
(1) Points

True (*)
False

Database Programming with SQL FINAL Part 1

Section 12
(Answer all questions in this section)

1. The DEFAULT keyword can be used in the following statements: Mark for Review
(1) Points

INSERT and UPDATE (*)
INSERT and DELETE
DELETE and UPDATE
All of the above

Incorrect Incorrect. Refer to Section 12 Lesson 3.
2. Multi-table inserts can be conditional or unconditional. True or False? Mark for Review
(1) Points

True (*)
False

Correct Correct
3. You need to update the area code of employees that live in Atlanta. Evaluate this partial UPDATE statement:
UPDATE employee
SET area_code = 770

Which of the following should you include in your UPDATE statement to achieve the desired results?

Mark for Review
(1) Points

UPDATE city = Atlanta;
SET city = ‘Atlanta’;
LIKE ‘At%’;
WHERE city = ‘Atlanta’; (*)

Correct Correct
4. The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(9,2)
BONUS NUMBER(9,2)

You need to increase the salary for all employees in department 10 by 10 percent. You also need to increase the bonus for all employees in department 10 by 15 percent. Which statement should you use?

Mark for Review
(1) Points

UPDATE employees
SET salary = salary * .10, bonus = bonus * .15
WHERE department_id = 10;
UPDATE employees
SET (salary = salary * 1.10) SET (bonus = bonus * 1.15)
WHERE department_id = 10;
UPDATE employees
SET salary = salary * 1.10 AND bonus = bonus * 1.15
WHERE department_id = 10;
UPDATE employees
SET salary = salary * 1.10, bonus = bonus * 1.15
WHERE department_id = 10;
(*)

Incorrect Incorrect. Refer to Section 12 Lesson 2.
5. One of the sales representatives, Janet Roper, has informed you that she was recently married, and she has requested that you update her name in the employee database. Her new last name is Cooper. Janet is the only person with the last name of Roper that is employed by the company. The EMPLOYEES table contains these columns and all data is stored in lowercase:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT_ID VARCHAR2 (20)
HIRE_DATE DATE
SALARY NUMBER(10)

Which UPDATE statement will accomplish your objective?

Mark for Review
(1) Points

UPDATE employees
SET last_name = ‘cooper’
WHERE last_name = ‘roper’; (*)
UPDATE employees
SET last_name = ‘roper’
WHERE last_name = ‘cooper’;
UPDATE employees last_name = ‘cooper’
WHERE last_name = ‘roper’;
UPDATE employees
SET cooper = ‘last_name’
WHERE last_name = ‘roper’;

Correct Correct
=============

6. You need to remove a row from the EMPLOYEES table. Which statement would you use? Mark for Review
(1) Points

DELETE with a WHERE clause (*)
INSERT with a WHERE clause
UPDATE with a WHERE clause
MERGE with a WHERE clause

Correct Correct
7. Assume all the column names are correct. The following SQL statement will execute which of the following?
INSERT INTO departments
(department_id, department_name, manager_id, location_id)
VALUES (70, ‘Public Relations’, 100, 1700);

Mark for Review
(1) Points

70 will be inserted into the department_id column. (*)
100 will be inserted into the department_id column.
1700 will be inserted into the manager_id column.
‘Public Relations’ will be inserted into the manager_name column.

Correct Correct
8. The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER NOT NULL
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER NOT NULL
LIST_PRICE NUMBER (7,2)
COST NUMBER (5,2)
QTY_IN_STOCK NUMBER(4)
LAST_ORDER_DT DATE DEFAULT SYSDATE NOT NUL

Which INSERT statement will execute successfully?

Mark for Review
(1) Points

INSERT INTO products
VALUES (2958, ‘Cable’, 8690, 7.09, 4.04, SYSDATE);
INSERT INTO products(product_id, product_name)
VALUES (2958, ‘Cable’);
INSERT INTO products (product_id, product_name, supplier_id, list_price, cost, qty_in_stock)
VALUES(2958, ‘Cable’, 8690, 7.09, 4.04, 700) (*)
INSERT INTO products(product_id, product_name, supplier_id
VALUES (2958, ‘Cable’, 8690, SYSDATE);

Correct Correct
Section 13
(Answer all questions in this section)

9. Which data types stores variable-length character data? Select two. Mark for Review
(1) Points

(Choose all correct answers)

NCHAR
VARCHAR2 (*)
CHAR
CLOB (*)

Correct Correct
10. The TIMESTAMP data type allows what? Mark for Review
(1) Points

Time to be stored as an interval of years and months.
Time to be stored as a date with fractional seconds. (*)
Time to be stored as an interval of days to hours, minutes and seconds.
None of the above.

Correct Correct
==============
11. Once they are created, external tables are accessed with normal SQL statements. (True or False?) Mark for Review
(1) Points

True (*)
False

Correct Correct
12. When creating a new table, which of the following naming rules apply. (Choose three) Mark for Review
(1) Points

(Choose all correct answers)

Must be between 1 to 30 characters long (*)
Must begin with a letter (*)
Must contain ONLY A – Z, a – z, 0 – 9, _ (underscore), $, and # (*)
Can have the same name as another object owned by the same user
Must be an Oracle reserved word

Incorrect Incorrect. Refer to Section 13 Lesson 1.
13. CREATE TABLE bioclass
(hire_date DATE DEFAULT SYSDATE,
first_name varchar2(15),
last_name varchar2(15));
The above CREATE TABLE statement is acceptable, and will create a Table named bioclass that contains a hire_date, first_name, and last_name column. True or False?

Mark for Review
(1) Points

True (*)
False

Correct Correct
14. Evaluate this statement:
Which statement about this TRUNCATE TABLE statement is true? Mark for Review
(1) Points

You can produce the same results by issuing the ‘DELETE employees’ statement.
You can reverse this statement by issuing the ROLLBACK statement.
You can issue this statement to retain the structure of the employees table. (*)
You can produce the same results by issuing the ‘DROP TABLE employee’ statement.

Correct Correct
15. You can use the ALTER TABLE statement to: Mark for Review
(1) Points

Add a new column
Modify an existing column
Drop a column
All of the above (*)

Incorrect Incorrect. Refer to Sectio
===========
Section 13
(Answer all questions in this section)

16. Your supervisor has asked you to modify the AMOUNT column in the ORDERS table. He wants the column to be configured to accept a default value of 250. The table contains data that you need to keep. Which statement should you issue to accomplish this task? Mark for Review
(1) Points

DELETE TABLE orders;
CREATE TABLE orders
(orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY,
customerid varchar2(5) REFERENCES customers (customerid),
orderdate date,
amount DEFAULT 250)
ALTER TABLE orders
CHANGE DATATYPE amount TO DEFAULT 250;
DROP TABLE orders;
CREATE TABLE orders
(orderno varchar2(5) CONSTRAINT pk_orders_01 PRIMARY KEY,
customerid varchar2(5) REFERENCES customers (customerid),
orderdate date,
amount DEFAULT 250);
ALTER TABLE orders
MODIFY (amount DEFAULT 250);
(*)

Incorrect Incorrect. Refer to Section 13 Lesson 3.
17. The previous administrator created a table named CONTACTS, which contains outdated data. You want to remove the table and its data from the database. Which statement should you issue? Mark for Review
(1) Points

ALTER TABLE
DELETE
TRUNCATE TABLE
DROP TABLE (*)

Correct Correct
18. The PLAYERS table contains these columns:
PLAYER_ID NUMBER(9) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
TEAM_ID NUMBER(4)
SALARY NUMBER(9,2)

Which statement should you use to decrease the width of the FIRST_NAME column to 10 if the column currently contains 1500 records, but none are longer than 10 bytes or characters?

Mark for Review
(1) Points

ALTER TABLE players
MODIFY (first_name VARCHAR2(10));
(*)
ALTER TABLE players
RENAME first_name VARCHAR2(10);
ALTER players TABLE
MODIFY COLUMN (first_name VARCHAR2(10));
ALTER players TABLE
MODIFY COLUMN first_name VARCHAR2(10);

Incorrect Incorrect. Refer to Section 13 Lesson 3.
19. RENAME old_name to new_name can be used to: Mark for Review
(1) Points

Rename a row.
Rename a column.
Rename a table. (*)
All of the above.

Incorrect Incorrect. Refer to Section 13 Lesson 3.
Section 14
(Answer all questions in this section)

20. The DEPARTMENTS table contains these columns:
DEPARTMENT_ID NUMBER, Primary Key
DEPARTMENT_ABBR VARCHAR2(4)
DEPARTMENT_NAME VARCHAR2(30)
MANAGER_ID NUMBER

The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
JOB_ID NUMBER
MANAGER_ID NUMBER
SALARY NUMBER(9,2)
HIRE_DATE DATE

Evaluate this statement:

ALTER TABLE employees
ADD CONSTRAINT REFERENTIAL (manager_id) TO departments(manager_id);

Which statement is true?

Mark for Review
(1) Points

The ALTER TABLE statement creates a referential constraint from the EMPLOYEES table to the DEPARTMENTS table.
The ALTER TABLE statement succeeds, but does NOT recreate a referential constraint.
The ALTER TABLE statement fails because the ADD CONSTRAINT clause contains a syntax error. (*)
The ALTER TABLE statement creates a referential constraint from the DEPARTMENTS table to the EMPLOYEES table.

Correct Correct

=====

Section 14
(Answer all questions in this section)

21. Examine the structures of the PRODUCTS and SUPPLIERS tables.
PRODUCTS:
PRODUCT_ID NUMBER NOT NULL, PRIMARY KEY
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER FOREIGN KEY to SUPPLIER_ID of the SUPPLIER table
LIST_PRICE NUMBER (7,2)
COST NUMBER (7,2)
QTY_IN_STOCK NUMBER
QTY_ON_ORDER NUMBER
REORDER_LEVEL NUMBER
REORDER_QTY NUMBER

SUPPLIERS:
SUPPLIER_ID NUMBER NOT NULL, PRIMARY KEY
SUPPLIER_NAME VARCHAR2 (25)
ADDRESS VARCHAR2 (30)
CITY VARCHAR2 (25)
REGION VARCHAR2 (10)
POSTAL_CODE VARCHAR2 (11)

Evaluate this statement:

ALTER TABLE suppliers
DISABLE CONSTRAINT supplier_id_pk CASCADE;

For which task would you issue this statement?

Mark for Review
(1) Points

To remove all constraint references to SUPPLIERS table
To disable any dependent integrity constraints on the SUPPLIER_ID column in the PRODUCTS table
To drop the FOREIGN KEY constraint on the PRODUCTS table
To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table (*)
To remove all constraint references to the PRODUCTS table

Correct Correct
22. You need to create a composite primary key constraint on the EMPLOYEES table. Which statement is true? Mark for Review
(1) Points

The PRIMARY KEY constraint must be defined for the first column of the composite primary key.
The PRIMARY KEY constraint must be defined at the table level. (*)
The PRIMARY KEY constraint must be defined at the table level and for each column in the composite primary key.
A PRIMARY KEY constraint must be defined for each column in the composite primary key.

Correct Correct
23. The number of check constraints that can be defined on a column is: Mark for Review
(1) Points

10
5
100
There is no limit (*)

Incorrect Incorrect. Refer to Section 14 Lesson 2.
24. Which statement about the NOT NULL constraint is true? Mark for Review
(1) Points

The NOT NULL constraint can be defined at either the column level or the table level.
The NOT NULL constraint prevents a column from containing alphanumeric values.
The NOT NULL constraint must be defined at the column level. (*)
The NOT NULL constraint requires a column to contain alphanumeric values.

Correct Correct
25. Evaluate this CREATE TABLE statement:
CREATE TABLE customers
(customer_id NUMBER,
customer_name VARCHAR2(25),
address VARCHAR2(25),
city VARCHAR2(25),
region VARCHAR2(25),
postal_code VARCHAR2(11),
CONSTRAINT customer_id_un UNIQUE(customer_id),
CONSTRAINT customer_name_nn NOT NULL(customer_name));

Why does this statement fail when executed?

Mark for Review
(1) Points

The NUMBER data types require precision values.
UNIQUE constraints must be defined at the column level.
The CREATE TABLE statement does NOT define a PRIMARY KEY.
NOT NULL constraints CANNOT be defined at the table level. (*)

Correct Correct

=========
Section 14
(Answer all questions in this section)

26. Which statement about constraints is true? Mark for Review
(1) Points

NOT NULL constraints can only be specified at the column level. (*)
PRIMARY KEY constraints can only be specified at the column level.
A single column can have only one constraint applied.
UNIQUE constraints are identical to PRIMARY KEY constraints.

Correct Correct
Section 15
(Answer all questions in this section)

27. Unlike tables, views contain no data of their own. True or False? Mark for Review
(1) Points

True (*)
False

Correct Correct
28. A view can contain a select statement with a subquery. True or False? Mark for Review
(1) Points

True (*)
False

Correct Correct
29. Which of the following keywords cannot be used when creating a view? Mark for Review
(1) Points

HAVING
WHERE
ORDER BY (*)
They are all valid keywords when creating views.

Incorrect Incorrect. Refer to Section 15 Lesson 1.
30. When you drop a table referenced by a view, the view is automatically dropped as well. True or False? Mark for Review
(1) Points

True
False (*)

Correct Correct
========
Section 15
(Answer all questions in this section)

31. Evaluate this CREATE VIEW statement:
CREATE VIEW sales_view
AS SELECT customer_id, region, SUM(sales_amount)
FROM sales
WHERE region IN (10, 20, 30, 40)
GROUP BY region, customer_id;

Which statement is true?

Mark for Review
(1) Points

You can only insert records into the SALES table using the SALES_VIEW view.
The CREATE VIEW statement generates an error.
You cannot modify data in the SALES table using the SALES_VIEW view. (*)
You can modify data in the SALES table using the SALES_VIEW view.

Correct Correct
32. What is the purpose of including the WITH CHECK OPTION clause when creating a view? Mark for Review
(1) Points

To make sure that data is not duplicated in the view
To make sure that the parent table(s) actually exist
To insure that no rows are updated through the view that would prevent those rows from being returned by the view in the future. (*)
To keep views form being queried by unauthorized persons

Incorrect Incorrect. Refer to Section 15 Lesson 2.
33. Examine the view below and choose the operation that CANNOT be performed on it.
CREATE VIEW dj_view (last_name, number_events) AS
SELECT c.last_name, COUNT(e.name)
FROM d_clients c, d_events e
WHERE c.client_number = e.client_number
GROUP BY c.last_name
Mark for Review
(1) Points

DROP VIEW dj_view;
CREATE OR REPLACE dj_view (last_name, number_events) AS
SELECT c.last_name, COUNT (e.name)
FROM d_clients c, d_events e
WHERE c.client_number=e.client_number
GROUP BY c.last_name;
SELECT last_name, number_events FROM dj_view;
INSERT INTO dj_view VALUES (‘Turner’, 8); (*)

Incorrect Incorrect. Refer to Section 15 Lesson 2.
34. For a View created using the WITH CHECK OPTION keywords, which of the following statements are true? Mark for Review
(1) Points

Allows for DELETES from other tables, including ones not listed in subquery
Prohibits DML actions without administrator CHECK approval
Prohibits changing rows not returned by the subquery in the view definition. (*)
The view will allow the user to check it against the data dictionary

Incorrect Incorrect. Refer to Section 15 Lesson 2.
Section 16
(Answer all questions in this section)

35. Evaluate this statement:
SELECT po_itemid_seq.CURRVAL
FROM dual;

What does this statement accomplish?

Mark for Review
(1) Points

It sets the current value of the PO_ITEM_ID_SEQ sequence to the value of the PO_ITEMID column.
It displays the current value of the PO_ITEM_ID_SEQ sequence. (*)
It resets the current value of the PO_ITEM_ID_SEQ sequence.
It displays the next available value of the PO_ITEM_ID_SEQ sequence.

Correct Correct
============

Section 16
(Answer all questions in this section)

36. You created the LOCATION_ID_SEQ sequence to generate sequential values for the LOCATION_ID column in the MANUFACTURERS table. You issue this statement:
ALTER TABLE manufacturers
MODIFY (location_id NUMBER(6));

Which statement about the LOCATION_ID_SEQ sequence is true?

Mark for Review
(1) Points

The sequence is unchanged. (*)
The sequence is deleted and must be recreated.
The current value of the sequence is reset to zero.
The current value of the sequence is reset to the sequence’s START WITH value.

Incorrect Incorrect. Refer to Section 16 Lesson 1.
37. Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE order_id_seq NOCYCLE NOCACHE;

Which statement is true?

Mark for Review
(1) Points

The sequence will start with 1. (*)
The sequence will continue to generate values after reaching its maximum value.
The sequence preallocates values and retains them in memory.
The sequence has no maximum value.

Incorrect Incorrect. Refer to Section 16 Lesson 1.
38. All tables must have indexes on them otherwise they cannot be queried. True or False? Mark for Review
(1) Points

True
False (*)

Correct Correct
39. Evaluate this statement:
CREATE INDEX sales_idx ON oe.sales (status);

Which statement is true?

Mark for Review
(1) Points

The CREATE INDEX statement fails because of a syntax error.
The CREATE INDEX statement creates a unique index.
The CREATE INDEX creates a function-based index.
The CREATE INDEX statement creates a nonunique index. (*)

Correct Correct
40. The CLIENTS table contains these columns:
CLIENT_ID NUMBER(4) NOT NULL PRIMARY KEY
LAST_NAME VARCHAR2(15)
FIRST_NAME VARCHAR2(10)
CITY VARCHAR2(15)
STATE VARCHAR2(2)

You want to create an index named ADDRESS_INDEX on the CITY and STATE columns of the CLIENTS table. You execute this statement:

CREATE INDEX clients
ON address_index (city, state);

Which result does this statement accomplish?

Mark for Review
(1) Points

An index named CLIENTS is created on the CITY and STATE columns.
An index named ADDRESS_INDEX is created on the CITY and STATE columns.
An index named CLIENTS_INDEX is created on the CLIENTS table.
An error message is produced, and no index is created. (*)

Correct Correct
================

Section 17
(Answer all questions in this section)

41. User Kate wants to create indexes on tables in her schema. What privilege must be granted to Kate so that she can do this? Mark for Review
(1) Points

CREATE INDEX
CREATE ANY INDEX
ALTER TABLE
None; users do not need extra privileges to create indexes on tables in their own schema. (*)

Correct Correct
42. What system privilege must be held in order to login to an Oracle database? Mark for Review
(1) Points

CREATE LOGIN
CREATE SESSION (*)
CREATE LOGON
No special privilege is needed; if your username exists in the database, you can login.

Correct Correct
43. Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ? Mark for Review
(1) Points

REGEXP (*)
REGEXP_REPLACE
REGEXP_LIKE
REGEXP_SUBSTR

Correct Correct
44. REGULAR EXPRESSIONS does exactly the same as LIKE–no more and no less. (True or False?) Mark for Review
(1) Points

True
False (*)

Correct Correct
45. Which statement would you use to add privileges to a role? Mark for Review
(1) Points

ASSIGN
CREATE ROLE
ALTER ROLE
GRANT (*)

Correct Correct

Section 17
(Answer all questions in this section)

41. User Kate wants to create indexes on tables in her schema. What privilege must be granted to Kate so that she can do this? Mark for Review
(1) Points

CREATE INDEX
CREATE ANY INDEX
ALTER TABLE
None; users do not need extra privileges to create indexes on tables in their own schema. (*)

Correct Correct
42. What system privilege must be held in order to login to an Oracle database? Mark for Review
(1) Points

CREATE LOGIN
CREATE SESSION (*)
CREATE LOGON
No special privilege is needed; if your username exists in the database, you can login.

Correct Correct
43. Which of these SQL functions used to manipulate strings is NOT a valid regular expression function ? Mark for Review
(1) Points

REGEXP (*)
REGEXP_REPLACE
REGEXP_LIKE
REGEXP_SUBSTR

Correct Correct
44. REGULAR EXPRESSIONS does exactly the same as LIKE–no more and no less. (True or False?) Mark for Review
(1) Points

True
False (*)

Correct Correct
45. Which statement would you use to add privileges to a role? Mark for Review
(1) Points

ASSIGN
CREATE ROLE
ALTER ROLE
GRANT (*)

Correct Correct
Section 17
(Answer all questions in this section)

46. User1 owns a table and grants select on it WITH GRANT OPTION to User2. User2 then grants select on the same table to User3. If User1 revokes select privileges from User2, will User3 be able to access the table? Mark for Review
(1) Points

No (*)
Yes

Correct Correct
47. When a user is logged into one database, he is restricted to working with objects found in that database. True or False? Mark for Review
(1) Points

True
False (*)

Correct Correct
Section 18
(Answer all questions in this section)

48. If UserB has privileges to see the data in a table, as soon as UserA has entered data into that table, UserB can see that data. True or False? Mark for Review
(1) Points

True
False (*)

Incorrect Incorrect. Refer to Section 18 Lesson 1.
49. COMMIT saves all outstanding data changes? True or False? Mark for Review
(1) Points

True (*)
False

Correct Correct
Section 19
(Answer all questions in this section)

50. Testing is done by programmers. True or False? Mark for Review
(1) Points

True (*)
False

Database Programming with SQL-Section 18 Quiz

Test: Section 18 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Section 18 Quiz
 (Answer all questions in this section)
   
  1.  Steven King's row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:
UPDATE employees
SET salary = salary * 2
WHERE employee_id = 100;
COMMIT;

UPDATE employees
SET salary = 30000
WHERE employee_id = 100;

The user's database session now ends abnormally. What is now King's salary in the table?

 Mark for Review
(1) Points
   
 
 24000

 
 48000 (*)

 
 30000

 
 78000

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  2.  If UserB has privileges to see the data in a table, as soon as UserA has entered data into that table, UserB can see that data. True or False?  Mark for Review
(1) Points
   
 
 True

 
 False (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  3.  Examine the following statements:
INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
SAVEPOINT Ins_Done;
DELETE employees; -- 107 rows deleted
SAVEPOINT Del_Done;
UPDATE emps SET last_name = 'Smith';

How would you undo the last Update only?

 Mark for Review
(1) Points
   
 
 ROLLBACK UPDATE;

 
 There is nothing you can do.

 
 COMMIT Del_Done;

 
 ROLLBACK to SAVEPOINT Del_Done; (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  4.  You need not worry about controlling your transactions. Oracle does it all for you. True or False? Mark for Review
(1) Points
   
 
 True

 
 False (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  5.  When you logout of Oracle, your data changes are automatically rolled back. True or False? Mark for Review
(1) Points
   
 
 True

 
 False (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
Page 1 of 3 Next Summary


Test: Section 18 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Section 18 Quiz
 (Answer all questions in this section)
   
  6.  Which SQL statement is used to remove all the changes made by an uncommitted transaction? Mark for Review
(1) Points
   
 
 UNDO;

 
 ROLLBACK TO SAVEPOINT;

 
 REVOKE;

 
 ROLLBACK; (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  7.  COMMIT saves all outstanding data changes? True or False?  Mark for Review
(1) Points
   
 
 True (*)

 
 False

   
  
Correct  Correct

   
  8.  If a database crashes, all uncommitted changes are automatically rolled back. True or False? Mark for Review
(1) Points
   
 
 True (*)

 
 False

   
  
Correct  Correct

   
  9.  If Oracle crashes, your changes are automatically rolled back. True or False?  Mark for Review
(1) Points
   
 
 True (*)

 
 False

   
  
Correct  Correct

   
  10.  User BOB's CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers;

What result will JANE see?

 Mark for Review
(1) Points
   
 
 22

 
 2

 
 JANE will receive an error message because she is not allowed to query the table while BOB is updating it.

 
 20 (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
Previous Page 2 of 3 Next Summary


Test: Section 18 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Section 18 Quiz
 (Answer all questions in this section)
   
  11.  Examine the following statements:
INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
SAVEPOINT Ins_Done;
CREATE INDEX emp_lname_idx ON employees(last_name);
UPDATE emps SET last_name = 'Smith';

What happens if you issue a Rollback statement?

 Mark for Review
(1) Points
   
 
 The update of last_name is undone, but the insert was committed by the CREATE INDEX statement. (*)

 
 Both the UPDATE and the INSERT will be rolled back.

 
 The INSERT is undone but the UPDATE is committed.

 
 Nothing happens.

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  12.  Examine the following statements:
UPDATE employees SET salary = 15000;
SAVEPOINT upd1_done;
UPDATE employees SET salary = 22000;
SAVEPOINT upd2_done;
DELETE FROM employees;

You want to retain all the employees with a salary of 15000; What statement would you execute next?

 Mark for Review
(1) Points
   
 
 ROLLBACK;

 
 ROLLBACK TO SAVEPOINT upd1_done; (*)

 
 ROLLBACK TO SAVEPOINT upd2_done;

 
 ROLLBACK TO SAVE upd1_done;

 
 There is nothing you can do; either all changes must be rolled back, or none of them can be rolled back.

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  13.  Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
INSERT INTO mytab VALUES ('A');
INSERT INTO mytab VALUES ('B');
COMMIT;
INSERT INTO mytab VALUES ('C');
ROLLBACK;

Which rows does the table now contain?

 Mark for Review
(1) Points
   
 
 A, B, and C

 
 A and B (*)

 
 C

 
 None of the above

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  14.  A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?  Mark for Review
(1) Points
   
 
 A database link

 
 An object privilege

 
 A savepoint (*)

 
 A sequence

 
 An update statement

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  15.  Which of the following best describes the term "read consistency"?  Mark for Review
(1) Points
   
 
 It prevents users from querying tables on which they have not been granted SELECT privilege

 
 It prevents other users from seeing changes to a table until those changes have been committed (*)

 
 It prevents other users from querying a table while updates are being executed on it

 
 It ensures that all changes to a table are automatically committed

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
Previous Page 3 of 3 Summary


Test: Section 18 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Section 18 Quiz
 (Answer all questions in this section)
   
  1.  Steven King's row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:
UPDATE employees
SET salary = salary * 2
WHERE employee_id = 100;
COMMIT;

UPDATE employees
SET salary = 30000
WHERE employee_id = 100;

The user's database session now ends abnormally. What is now King's salary in the table?

 Mark for Review
(1) Points
   
 
 30000

 
 24000

 
 78000

 
 48000 (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  2.  A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?  Mark for Review
(1) Points
   
 
 A sequence

 
 A database link

 
 An object privilege

 
 An update statement

 
 A savepoint (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  3.  Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
INSERT INTO mytab VALUES ('A');
INSERT INTO mytab VALUES ('B');
COMMIT;
INSERT INTO mytab VALUES ('C');
ROLLBACK;

Which rows does the table now contain?

 Mark for Review
(1) Points
   
 
 A, B, and C

 
 A and B (*)

 
 C

 
 None of the above

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  4.  Which SQL statement is used to remove all the changes made by an uncommitted transaction? Mark for Review
(1) Points
   
 
 UNDO;

 
 ROLLBACK TO SAVEPOINT;

 
 ROLLBACK; (*)

 
 REVOKE;

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  5.  User BOB's CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers;

What result will JANE see?

 Mark for Review
(1) Points
   
 
 2

 
 22

 
 JANE will receive an error message because she is not allowed to query the table while BOB is updating it.

 
 20 (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
Page 1 of 3 Next Summary


Test: Section 18 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Section 18 Quiz
 (Answer all questions in this section)
   
  6.  Examine the following statements:
INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
SAVEPOINT Ins_Done;
CREATE INDEX emp_lname_idx ON employees(last_name);
UPDATE emps SET last_name = 'Smith';

What happens if you issue a Rollback statement?

 Mark for Review
(1) Points
   
 
 The update of last_name is undone, but the insert was committed by the CREATE INDEX statement. (*)

 
 Both the UPDATE and the INSERT will be rolled back.

 
 The INSERT is undone but the UPDATE is committed.

 
 Nothing happens.

   
  
Correct  Correct

   
  7.  COMMIT saves all outstanding data changes? True or False?  Mark for Review
(1) Points
   
 
 True (*)

 
 False

   
  
Correct  Correct

   
  8.  Examine the following statements:
UPDATE employees SET salary = 15000;
SAVEPOINT upd1_done;
UPDATE employees SET salary = 22000;
SAVEPOINT upd2_done;
DELETE FROM employees;

You want to retain all the employees with a salary of 15000; What statement would you execute next?

 Mark for Review
(1) Points
   
 
 ROLLBACK;

 
 ROLLBACK TO SAVEPOINT upd1_done; (*)

 
 ROLLBACK TO SAVEPOINT upd2_done;

 
 ROLLBACK TO SAVE upd1_done;

 
 There is nothing you can do; either all changes must be rolled back, or none of them can be rolled back.

   
  
Correct  Correct

   
  9.  If a database crashes, all uncommitted changes are automatically rolled back. True or False? Mark for Review
(1) Points
   
 
 True (*)

 
 False

   
  
Correct  Correct

   
  10.  Examine the following statements:
INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
SAVEPOINT Ins_Done;
DELETE employees; -- 107 rows deleted
SAVEPOINT Del_Done;
UPDATE emps SET last_name = 'Smith';

How would you undo the last Update only?

 Mark for Review
(1) Points
   
 
 ROLLBACK UPDATE;

 
 ROLLBACK to SAVEPOINT Del_Done; (*)

 
 COMMIT Del_Done;

 
 There is nothing you can do.

   
  
Correct  Correct

   
Previous Page 2 of 3 Next Summary


Test: Section 18 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Section 18 Quiz
 (Answer all questions in this section)
   
  11.  Which of the following best describes the term "read consistency"?  Mark for Review
(1) Points
   
 
 It ensures that all changes to a table are automatically committed

 
 It prevents users from querying tables on which they have not been granted SELECT privilege

 
 It prevents other users from seeing changes to a table until those changes have been committed (*)

 
 It prevents other users from querying a table while updates are being executed on it

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  12.  If Oracle crashes, your changes are automatically rolled back. True or False?  Mark for Review
(1) Points
   
 
 True (*)

 
 False

   
  
Correct  Correct

   
  13.  If UserB has privileges to see the data in a table, as soon as UserA has entered data into that table, UserB can see that data. True or False?  Mark for Review
(1) Points
   
 
 True

 
 False (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  14.  When you logout of Oracle, your data changes are automatically rolled back. True or False? Mark for Review
(1) Points
   
 
 True

 
 False (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  15.  You need not worry about controlling your transactions. Oracle does it all for you. True or False?  Mark for Review
(1) Points
   
 
 True

 
 False (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
Previous Page 3 of 3 Summary


Test: Section 18 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Section 18 Quiz
 (Answer all questions in this section)
   
  1.  Which of the following best describes the term "read consistency"?  Mark for Review
(1) Points
   
 
 It ensures that all changes to a table are automatically committed

 
 It prevents other users from seeing changes to a table until those changes have been committed (*)

 
 It prevents other users from querying a table while updates are being executed on it

 
 It prevents users from querying tables on which they have not been granted SELECT privilege

   
  
Correct  Correct

   
  2.  User BOB's CUSTOMERS table contains 20 rows. BOB inserts two more rows into the table but does not COMMIT his changes. User JANE now executes:
SELECT COUNT(*) FROM bob.customers;

What result will JANE see?

 Mark for Review
(1) Points
   
 
 20 (*)

 
 JANE will receive an error message because she is not allowed to query the table while BOB is updating it.

 
 22

 
 2

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  3.  When you logout of Oracle, your data changes are automatically rolled back. True or False? Mark for Review
(1) Points
   
 
 True

 
 False (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  4.  If Oracle crashes, your changes are automatically rolled back. True or False?  Mark for Review
(1) Points
   
 
 True (*)

 
 False

   
  
Correct  Correct

   
  5.  If a database crashes, all uncommitted changes are automatically rolled back. True or False? Mark for Review
(1) Points
   
 
 True (*)

 
 False

   
  
Correct  Correct

   
Page 1 of 3 Next Summary


Test: Section 18 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Section 18 Quiz
 (Answer all questions in this section)
   
  6.  Examine the following statements:
UPDATE employees SET salary = 15000;
SAVEPOINT upd1_done;
UPDATE employees SET salary = 22000;
SAVEPOINT upd2_done;
DELETE FROM employees;

You want to retain all the employees with a salary of 15000; What statement would you execute next?

 Mark for Review
(1) Points
   
 
 ROLLBACK;

 
 ROLLBACK TO SAVEPOINT upd1_done; (*)

 
 ROLLBACK TO SAVEPOINT upd2_done;

 
 ROLLBACK TO SAVE upd1_done;

 
 There is nothing you can do; either all changes must be rolled back, or none of them can be rolled back.

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  7.  Table MYTAB contains only one column of datatype CHAR(1). A user executes the following statements in the order shown.
INSERT INTO mytab VALUES ('A');
INSERT INTO mytab VALUES ('B');
COMMIT;
INSERT INTO mytab VALUES ('C');
ROLLBACK;

Which rows does the table now contain?

 Mark for Review
(1) Points
   
 
 A, B, and C

 
 A and B (*)

 
 C

 
 None of the above

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  8.  Which SQL statement is used to remove all the changes made by an uncommitted transaction? Mark for Review
(1) Points
   
 
 ROLLBACK TO SAVEPOINT;

 
 ROLLBACK; (*)

 
 REVOKE;

 
 UNDO;

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  9.  Examine the following statements:
INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
SAVEPOINT Ins_Done;
DELETE employees; -- 107 rows deleted
SAVEPOINT Del_Done;
UPDATE emps SET last_name = 'Smith';

How would you undo the last Update only?

 Mark for Review
(1) Points
   
 
 ROLLBACK UPDATE;

 
 There is nothing you can do.

 
 COMMIT Del_Done;

 
 ROLLBACK to SAVEPOINT Del_Done; (*)

   
  
Correct  Correct

   
  10.  Steven King's row in the EMPLOYEES table has EMPLOYEE_ID = 100 and SALARY = 24000. A user issues the following statements in the order shown:
UPDATE employees
SET salary = salary * 2
WHERE employee_id = 100;
COMMIT;

UPDATE employees
SET salary = 30000
WHERE employee_id = 100;

The user's database session now ends abnormally. What is now King's salary in the table?

 Mark for Review
(1) Points
   
 
 48000 (*)

 
 30000

 
 24000

 
 78000

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
Previous Page 2 of 3 Next Summary


Test: Section 18 Quiz
Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 Section 18 Quiz
 (Answer all questions in this section)
   
  11.  Examine the following statements:
INSERT INTO emps SELECT * FROM employees; -- 107 rows inserted.
SAVEPOINT Ins_Done;
CREATE INDEX emp_lname_idx ON employees(last_name);
UPDATE emps SET last_name = 'Smith';

What happens if you issue a Rollback statement?

 Mark for Review
(1) Points
   
 
 The update of last_name is undone, but the insert was committed by the CREATE INDEX statement. (*)

 
 Both the UPDATE and the INSERT will be rolled back.

 
 The INSERT is undone but the UPDATE is committed.

 
 Nothing happens.

   
  
Correct  Correct

   
  12.  If UserB has privileges to see the data in a table, as soon as UserA has entered data into that table, UserB can see that data. True or False?  Mark for Review
(1) Points
   
 
 True

 
 False (*)

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  13.  A transaction makes several successive changes to a table. If required, you want to be able to rollback the later changes while keeping the earlier changes. What must you include in your code to do this?  Mark for Review
(1) Points
   
 
 An update statement

 
 A savepoint (*)

 
 A sequence

 
 A database link

 
 An object privilege

   
  
Incorrect  Incorrect. Refer to Section 18 Lesson 1.

   
  14.  COMMIT saves all outstanding data changes? True or False?  Mark for Review
(1) Points
   
 
 True (*)

 
 False

   
  
Correct  Correct

   
  15.  You need not worry about controlling your transactions. Oracle does it all for you. True or False?  Mark for Review
(1) Points
   
 
 True

 
 False (*)