Test: DP Database Programming with SQL - Exam

Final

Review the answers, results, and scores of the questions.

which are shown below. The correct answers are marked
with an asterisk (*)

Section 12
(Answer all the questions in this section)
1. An insert statement into multiple tables can be inserted into more than
a table. (True or false?)
TRUE (*)
FALSE
Correct
(1/1) Points
2. With MERGE, __________ and __________ are executed at the same time.

UPDATE; SELECT
UPDATE; DELETE
INSERT; SELECT
INSERT; UPDATE (*)
Correct
Points
3. An employee has the last name 'King' in the employees table. How many rows
Will they be removed from the employees table with the following statement?
DELETE FROM employees
WHERE last_name = 'king';

No row will be deleted, as there is no employee that matches.

with the WHERE clause. (*)
All rows with the value last_name = 'King' will be deleted.
All rows in the employees table will be deleted.
One will be eliminated, as there is an employee named King.
Incorrect. Please refer to section 12 of lesson 2.
(0/1) Points
4. If you are executing an UPDATE statement with a subquery, MUST you
Is it a correlated subquery? (True or false)
TRUE
FALSE (*)
Correct
(1/1) Points
5. Examine the structures of the PLAYERS, MANAGERS, and TEAMS tables:
PLAYERS:
PLAYER_ID NUMBER Primary Key
LAST_NAME VARCHAR2 (30)
FIRST_NAME VARCHAR2 (25)
TEAM_ID NUMBER
MGR_ID NUMBER
SIGNING_BONUS NUMBER(9,2)
SALARY NUMBER(9,2)

MANAGERS:
MANAGER_ID NUMBER Primary Key
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
TEAM_ID NUMBER

TEAMS:
TEAM_ID NUMBER Primary Key
TEAM_NAME VARCHAR2 (20)
OWNER_LAST_NAME VARCHAR2 (20)
OWNER_FIRST_NAME VARCHAR2 (20)

In which of the situations would a subquery be needed to return the

desired outcome?

To show each player, their manager, and the name of their team for
all teams with an ID value greater than 5000
To show the minimum and maximum salary of the players of each team

To show the names of the managers of all the teams of a

determined owner (*)
To show the names of each player on the Lions team
Correct
Points

One of the saleswomen, Janet Roper, has informed you that she has gotten married.
recently and has requested that you update your name in the database
Employee data. Her new last name is Cooper. Janet is the only person.
with the last name Roper hired by the company. The EMPLOYEES table
it 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)

What UPDATE statement will allow you to achieve your goal?

UPDATE employees
SET last_name = 'cooper'
WHERE last_name = 'roper'; (*)
UPDATE employees
SET cooper = 'last_name'
WHERE last_name = 'roper';
UPDATE employees
SET last_name = 'roper'
WHERE last_name = 'cooper';
UPDATE employees SET last_name = 'cooper'
WHERE last_name = 'roper';
Incorrect. Please refer to section 12 of lesson 2.
(0/1) Points
7. Can more than one row be inserted at once using a statement?
INSERT with a VALUES clause?
No, there is nothing like INSERT... VALUES.
No, you can only create one row at a time when using the clause
VALUES. (*)
Yes, you can show as many rows as you want; you just have to remember to separate

the rows with commas.

Correct
Points
8. When inserting a new row, the null keyword can be included in the list.
of values for any column that allows null values. True or
false?
TRUE (*)
FALSE
Correct
(1/1) Points

Section 13
(Answer all the questions in this section)
9. Evaluate the structure of the EMPLOYEE table:
EMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER(9)
MANAGER_ID NUMBER(9)
SALARY NUMBER(7,2)

What statement should I use to increase the length of the column?

LAST_NAME at 35 if the column currently contains 200 records?

ALTER employee TABLE

ALTER COLUMN (last_name VARCHAR2(35));
ALTER TABLE employee
RENAME last_name VARCHAR2(35);
Cannot increase the width of the LAST_NAME column.
ALTER TABLE employee
MODIFY (last_name VARCHAR2(35)); (*)
Correct
Points
10. You must truncate the EMPLOYEES table. The EMPLOYEES table is not in your
scheme. What privilege must one have to truncate the table?
The privilege of the CREATE ANY TABLE system
The privilege of the TRUNCATE ANY TABLE system
The privilege of the ALTER ANY TABLE system
The privilege of the DROP ANY TABLE system (*)
Incorrect. Please consult section 13 of lesson 3.
(0/1) Points

Section 13
(Answer all the questions in this section)
11. Comments can be added to a table using the statement
COMMENT ON TABLE. The comments that are added are included between:
Braces { }
Double quotes " "
Parenthesis ( )
Single quotes ' ' (*)
Correct
Points
12. The TEAMS table contains these columns:
TEAM_ID NUMBER(4) Primary Key
TEAM_NAME VARCHAR2(20)
MGR_ID NUMBER(9)

The TEAMS table is currently empty. It is necessary to allow for

users include text characters in the identification values of
manager. What sentence should I use to implement this?

ALTER TABLE teams

REPLACE (mgr_id VARCHAR2(15));
ALTER teams TABLE
MODIFY COLUMN (mgr_id VARCHAR2(15));
ALTER TABLE teams
MODIFY (mgr_id VARCHAR2(15)); (*)
ALTER teams
MODIFY (mgr_id VARCHAR2(15));
You cannot modify the data type of the column MGR_ID.
Correct
Points
13. The FLASHBACK TABLE to BEFORE DROP action can restore only the
structure of the table, but not its data in the state it was in
Before deleting the table. True or false?
TRUE
FALSE (*)
Incorrect. Please refer to section 13 of lesson 3.
(0/1) Points
14. Which statement is NOT true about a column?
You can modify the data type of a column if the column contains
non-null data. (*)
You can convert a column of CHAR data type to the data type
VARCHAR2.
You can convert a DATE type column into a column
VARCHAR2.
You can increase the width of a CHAR column.
Correct
Points
15. You want to create a table named TRAVEL that is secondary to the
EMPLOYEES table. Which of the following statements should be issued?
CREATE TABLE travel
(destination_id primary key, departure_date date, return_date date, emp_id
REFERENCES employees (emp_id));
CREATE TABLE travel
(destination_id number primary key, departure_date date, return_date date,
t.emp_id = e.emp_id);
CREATE TABLE travel
(destination_id number primary key, departure_date date, return_date date)
emp_id number(10) REFERENCES employees (emp_id)); (*)
CREATE TABLE travel
(destination_id number primary key, departure_date date, return_date date,
JOIN emp_id number(10) ON employees (emp_id));
Correct
Points
16. Evaluate this CREATE TABLE statement:
CREATE TABLE line_item ( line_item_id NUMBER(9), order_id NUMBER(9),
product_id NUMBER(9));

He is a member of the SYSDBA role, but is connected with his own schema.
Emit this CREATE TABLE statement. Which statement is true?

The table has been created in the SYSDBA schema.

The LINE_ITEM table has been created in the SYS schema.

The LINE_ITEM table has been created in the public schema.

He has created the table in his schema. (*)
Correct
Points
17. Which CREATE TABLE statement will fail?
CREATE TABLE time (time_id NUMBER(9));
CREATE TABLE date (date_id NUMBER(9)); (*)
CREATE TABLE time_date (time NUMBER(9));
CREATE TABLE date_1 (date_1 DATE);
Correct
Points
18. What type of data should be defined for a column that will be used?
to store binary data of up to 4 GB in size?
LONG
NUMBER
LONGRAW
BLOB (*)
Correct
Points
19. What statement is true about data types?
The CHAR data type should be defined with a size that is not
too big for the data it contains (or could contain)
to save space in the database. (*)
The BFILE data type stores character data up to four GB in
the database.
The TIMESTAMP data type is a character data type.
The VARCHAR2 data type should be used for character data of
fixed length.
Correct
Points

Section 14
(Answer all the questions in this section)
20. A table can only have one unique key constraint defined.
True or false?
TRUE
FALSE (*)
Correct
(1/1) Points
21. Which statement is true about the NOT NULL constraint?
The NOT NULL constraint must be defined at the column level.
The NOT NULL constraint requires a column to contain values
alphanumeric.
The NOT NULL constraint can be defined at the column level or at the table level.

table.
The NOT NULL constraint prevents a column from containing values
alphanumeric.
Correct
Points
22. Which of the following is not a valid Oracle constraint type?
EXTERNAL KEY (*)
NOT NULL
UNIQUE KEY
PRIMARY KEY
Correct
(1/1) Points
23. You must add a PRIMARY KEY constraint to the DEPARTMENTS table.
What sentence should I use?
ALTER TABLE departments
ADD CONSTRAINT PRIMARY KEY dept_id_pk (dept_id);
ALTER TABLE departments
ADD CONSTRAINT dept_id_pk PRIMARY KEY (dept_id); (*)
ALTER TABLE departments
ADD PRIMARY KEY dept_id_pk (dept_id);
ALTER TABLE departments
ADD CONSTRAINT dept_id_pk PK (dept_id);
Correct
Points
24. What is the syntax for removing a PRIMARY KEY constraint and
all their dependent restrictions?
ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;
ALTER TABLE table_name
DROP CONSTRAINT constraint_name;
DROP CONSTRAINT table_name (constraint_name);
ALTER TABLE table_name
DROP CONSTRAINT FOREIGN KEY CASCADE;
Incorrect. See section 14 of lesson 3.
(0/1) Points
25. Evaluate the structure of the DONATIONS table.
DONATIONS:
PLEDGE_ID NUMBER NOT NULL, Primary Key
DONOR_ID NUMBER Foreign key to DONOR_ID column of DONORS table
PLEDGE_DT DATE
AMOUNT_PLEDGED NUMBER (7,2)
AMOUNT_PAID NUMBER (7,2)
PAYMENT_DT DATE

What CREATE TABLE statement should be used to create the table

DONATIONS?

CREATE TABLE donations

(pledge_id NUMBER PRIMARY KEY,
donor_id NUMBER FOREIGN KEY REFERENCES donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER,
amount_paid NUMBER,
payment_dt DATE);
CREATE TABLE donations
(pledge_id NUMBER PRIMARY KEY NOT NULL,
donor_id NUMBER FOREIGN KEY donors(donor_id),
pledge_date DATE,
amount_pledged NUMBER(7,2),
amount_paid NUMBER(7,2),
payment_dt DATE);
CREATE TABLE donations
pledge_id NUMBER PRIMARY KEY,
donor_id NUMBER FOREIGN KEY donor_id_fk REFERENCES
donors(donor_id)
pledge_date DATE,
amount_pledged NUMBER(7,2),
amount_paid NUMBER(7,2),
payment_dt DATE;
CREATE TABLE donations
(pledge_id NUMBER PRIMARY KEY,
donor_id NUMBER CONSTRAINT donor_id_fk REFERENCES
donors(donor_id)
pledge_date DATE,
amount_pledged NUMBER(7,2),
amount_paid NUMBER(7,2),
payment_dt DATE); (*)
Correct
Point
26. Which of the following FOREIGN KEY restriction keywords
Identify the table and column in the main table?
REFERENTIAL
REFERENCES (*)
RESEMBLES
ON DELETE CASCADE
Correct
Points

Section 15
(Answer all the questions in this section)
27. The CUSTOMER_FINANCE table contains these columns:
CUSTOMER_ID NUMBER(9)
NEW_BALANCE NUMBER(7,2)
PREV_BALANCE NUMBER(7,2)
PAYMENTS NUMBER(7,2)
FINANCE_CHARGE NUMBER(7,2)
CREDIT_LIMIT NUMBER(7)

Execute this statement:

SELECT ROWNUM "Rank", customer_id, new_balance

FROM (SELECT customer_id, new_balance FROM customer_finance)
WHERE ROWNUM <= 25
ORDER BY new_balance DESC;

Which statement is true?

The 25 highest new balance values were displayed from highest to lowest.

The statement has not been executed because the ORDER BY clause does NOT use the
Top-n column.
The statement has not been executed because an online view has been used.

The sentence will not necessarily return the 25 new balance values.
higher, since the view does not have an ORDER BY clause. (*)
Correct
Points
28. An analysis of the top N is capable of classifying a game of
superior or inferior results. True or false?
TRUE (*)
FALSE
Correct
Points
29. A view can be used to maintain a historical record of the
old data from the underlying tables, so that, even though it
If you delete a row from a table, you will still be able to select the row in the view.
True or false?
TRUE
FALSE (*)
Correct
Points
30. Which of the following keywords cannot be used when creating a
view?
HAVING
WHERE
ORDER BY
All are valid keywords when creating views. (*)
Correct
Points
31. Manage an Oracle database that contains a table
called EMPLOYEES. Luke, a database user, must create
a report that includes the names and addresses of all
employees. You do not wish to grant Luke access to the EMPLOYEES table.
because it contains confidential data. Which of the following actions
What should I do first?
Create a view.
Create a subquery.
Create an index.
Create a report for him.
Correct
Points
32. You must create a new view in the EMPLOYEES table to update the
salary information for employees of department 50. Must
ensure that DML operations on the view cannot change the
salary values from other departments. What clause should be included in
the CREATE VIEW statement?
WITH READ ONLY
WITH CHECK OPTION (*)
FORCE
OR REPLACE
Correct
Points
33. Manages an Oracle database. Jack manages the department of
sales. He and his employees often find it appropriate to consult the database
to identify customers and their orders. He has asked you to
create a view that will simplify this procedure for himself and his
personal. The view should not accept INSERT, UPDATE, or operations.
DELETE. Which of the following statements should be issued?
CREATE VIEW sales_view
(SELECT c.companyname, c.city, o.orderid, o.orderdate, o.total
FROM customers c, orders o
WHERE c.custid = o.custid)
WITH READ ONLY;
CREATE VIEW sales_view
AS (SELECT c.companyname, c.city, o.orderid, o.orderdate, o.total
FROM customers c, orders o
WHERE c.custid = o.custid
WITH READ ONLY;
CREATE VIEW sales_view
AS (SELECT c.companyname, c.city, o.orderid, o.orderdate, o.total
FROM customers c, orders o
WHERE c.custid = o.custid);
CREATE VIEW sales_view
AS (SELECT companyname, city, orderid, orderdate, total
FROM customers, orders
WHERE custid = custid)
WITH READ ONLY;
Correct
Points
34. Which of the following DML operations is not allowed when using
a simple view created as read-only?
INSERT
UPDATE
DELETE
All of the above (*)
Correct
Points

Section 16
(Answer all the questions in this section)
35. Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE line_item_id_seq INCREMENT BY -1;

Which statement is true?

The sequence will generate sequential descending values. (*)

The initial value of the LINE_ITEM_ID_SEQ sequence will be -1.
The minimum value of LINE_ITEM_ID_SEQ will be the smallest integer value.
possible.
The sentence will not execute correctly.
Correct
Points
36. Create a CUSTOMERS table in which the CUSTOMER_ID value is designated
as the primary key. You want the values that are entered in the
column CUSTOMER_ID is generated automatically. Which of the
What actions should I take next?
Specify a UNIQUE constraint on the CUSTOMER_ID column.
Create a sequence.
Do nothing. Oracle automatically generates unique values for the
columns defined as primary keys.
Create a synonym.
Correct
Points
37. Ver el valor más reciente recuperado de una secuencia denominada
the sequence "my_seq" that must be referenced:

my_seq.(currval)
my_seq.currval (*)
my_seq.nextval
my_seq.(lastval)
Correct
Points
38. What statement should I use to drop the index LAST_NAME_IDX
in the LAST_NAME column of the EMPLOYEES table?
DROP INDEX last_name_idx;
DROP INDEX last_name_idx(last_name);
ALTER TABLE employees
DROP INDEX last_name_idx;
DROP INDEX last_name_idx(employees.last_name);
Correct
(1/1) Points
39. The following indexes exist in the EMPLOYEES table:

A unique index on the primary key column EMPLOYEE_ID

A non-unique index on the JOB_ID column
A composite index on the FIRST_NAME and LAST_NAME columns.

If the EMPLOYEES table is deleted, which indexes are automatically deleted?

same time?

Only EMP_ID
Only JOB_ID
Only DEPT_ID
EMP_ID y JOB_ID
All indexes (*)
Correct
Points
40. You want to create a composite index on the FIRST_NAME and columns.
LAST_NAME from the EMPLOYEES table. What SQL statement will be used to perform this?
this task?
CREATE INDEX fl_idx
ON employees(first_name || last_name);
CREATE INDEX fl_idx
ON employees(first_name, last_name); (*)
CREATE INDEX fl_idx
ON employees(first_name), employees(last_name);
CREATE INDEX fl_idx ON employees(first_name);
CREATE INDEX fl_idx ON employees(last_name);
Correct
Points

Section 17
(Answer all the questions in this section)

41. The user SUSAN creates a table EMPLOYEES and then creates a view.
EMP_VIEW that only shows the FIRST_NAME and LAST_NAME columns of
EMPLOYEES. The user RUDI must be able to access the names of
the employees, but to no other data of EMPLOYEES. What statement should
run SUSAN to allow this?

CREATE SYNONYM emp_view FOR employees;

GRANT SELECT ON emp_view ONLY TO rudi;

SELECT * FROM emp_view FOR rudi;

GRANT SELECT ON emp_view TO rudi;

Correct

Points

42. A scheme is:

Collection of objects, such as tables, views, and sequences. (*)

Necessary to manipulate the content of the database objects.

Necesario para obtener acceso a la base de datos.

Group with a name of privileges related granted to a user.

Correct

Points

43. The REGULAR EXPRESSION functions perform exactly the same action
that LIKE, neither more nor less. (True or false?)

TRUE

FALSE (*)

Correct

Points

44. The REGULAR EXPRESSION functions can be used on CHAR data types,
CLOB and VARCHAR2? (True or false)

TRUE (*)

FALSE

Correct

Points

45. User BOB's schema contains a table EMPLOYEES. BOB executes the
next sentence:

GRANT SELECT ON employees TO mary WITH GRANT OPTION;

Which of the following sentences can MARY execute now?

correctly? (Select two options)

(Select all the correct answers)

REVOKE SELECT ON bob.employees FROM bob;

SELECT FROM bob.employees; (*)

DROP TABLE bob.employees;

GRANT SELECT ON bob.employees TO PUBLIC;

Correct

Points
46. What Oracle function simplifies the process of granting and revoking
privileges?

Outline

Object

Data dictionary

Role (*)

Correct

(1/1) Points

47. What data dictionary view shows the system privileges that are
has been granted to a user?

USER_TAB_PRIVS

USER_SYSTEM_PRIVILEGES

USER_SYSTEM_PRIVS

USER_SYS_PRIVS (*)

Incorrect. Please refer to section 17 of lesson 2.

(0/1) Points

Section 18
(Answer all the questions in this section)

48. If Oracle crashes, the user's changes are automatically undone.

True or false?

TRUE (*)

FALSE

Correct

(1/1) Points

49. The row of Steven King in the EMPLOYEES table has an EMPLOYEE_ID value of 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 is now terminating abnormally.

What is King’s current salary in the table?

78000

24000

48000 (*)

30000

Correct

Points

Section 19
(Answer all the questions in this section)

50. Unit tests are performed before the database goes to the
production phase to ensure that a random number of requirements of
Business functions correctly. True or false?

TRUE

FALSE (*)

Incorrect. Please refer to section 19 of lesson 1.

(0/1) Points