Final Exam Semester 2

For comments or requests, please write us at luchiant@gmail.com, or visit our Facebook official page.

Final Exam Semester 2
Comments on tables and columns can be stored for documentation by:
(3) Using the COMMENT ON TABLE or COMMENT on COLUMN

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?
(4) The column named COLOR in the table named PRODUCTS will not be returned in subsequent reads of the table by Oracle, as is has been deleted logically.

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?
(2) ALTER TABLE orders
MODIFY (amount DEFAULT 250);

You need to change the name of the EMPLOYEES table to the EMP table. Which statement should you use?
(2) RENAME employees TO emp;

Which command could you use to quickly remove all data from the rows in a table without deleting the table itself?
(4) TRUNCATE TABLE

Evaluate this statement:
TRUNCATE TABLE employees;
Which statement about this TRUNCATE TABLE statement is true?
(2) You can issue this statement to retain the structure of the employees table.

You need to remove all the data in the SCHEDULE table, the structure of the table, and the indexes associated with the table. Which statement should you use?
(1) DROP TABLE

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?
(1) DROP TABLE

You need to truncate the EMPLOYEES table. The EMPLOYEES table is not in your schema. Which privilege must you have to truncate the table?
(1) The DROP ANY TABLE system privilege

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. You need to allow users to include text characters in the manager identification values. Which statement should you use to implement this?
(2) ALTER TABLE teams
MODIFY (mgr_id VARCHAR2(15));

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?
(4) ALTER TABLE players
MODIFY (first_name VARCHAR2(10));

Which statement about data types is true?
(4) The CHAR data type requires that a maximum size be specified when defining a column of this type.

You need to store the HIRE_DATE value with a time zone displacement value and allow data to be returned in the user's local session time zone. Which data type should you use?
(4) TIMESTAMP WITH LOCAL TIME ZONE

The TIMESTAMP data type allows what?
(2) Time to be stored as a date with fractional seconds.

You are designing a table for the Human Resources department. This table must include a column that contains each employee's hire date. Which data type should you specify for this column?
(2) DATE

To store time with fractions of seconds, which datatype should be used for a table column?
(3) TIMESTAMP

The SPEED_TIME column should store a fractional second value. Which data type should you use?
(3) TIMESTAMP

You are designing a table for the Sales department. You need to include a column that contains each sales total. Which data type should you specify for this column?
(3) NUMBER

Evaluate this CREATE TABLE statement:
CREATE TABLE line_item ( line_item_id NUMBER(9), order_id NUMBER(9), product_id NUMBER(9));
You are a member of the SYSDBA role, but are not logged in as SYSDBA. You issue this CREATE TABLE statement. Which statement is true?
(3) You created the table in your schema.

You are creating the EMPLOYEES table. This table should contain the COMMISSION_PCT column and use a value of 10 percent if no commission value is provided when a record is inserted. Which line should you include in the CREATE TABLE statement to accomplish this task?
(1) commission_pct NUMBER(4,2) DEFAULT 0.10

Which column name is valid?
(3) NUMBER_1$

You want to create a table named TRAVEL that is a child of the EMPLOYEES table. Which of the following statements should you issue?
(4) CREATE TABLE travel(destination_id number primary key, departure_date date, return_date date, emp_id number(10) REFERENCES employees (emp_id));

Which of the following SQL statements will create a table called Birthdays with three columns for storing employee number, name and date of birth?
(3) CREATE TABLE Birthdays (Empno NUMBER, Empname CHAR(20), Birthdate DATE);

Which statement about creating a table is true?
(2) If no schema is explicitly included in a CREATE TABLE statement, the table is created in the current user's schema.

When creating a referential constraint, which keyword(s) identifies the table and column in the parent table?
(2) REFERENCES

Which statement about a non-mandatory foreign key constraint is true?
(4) A foreign key value must either be null or match an existing value in the parent table.

What must exist on the Parent table before Oracle will allow you to create a FOREIGN KEY constraint from a Child table?
(2) A PRIMARY or UNIQUE KEY constraint must exist on the Parent table.

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
Which CREATE TABLE statement should you use to create the DONATIONS table?
(4) CREATE TABLE donations (pledge_id NUMBER PRIMARY KEY, NUMBER CONSTRAINT donor_id_fk REFERENCES donors(donor_id), pledge_dt DATE, amount_pledged NUMBER(7,2), amount_paid NUMBER(7,2), payment_dt DATE);

Evaluate this CREATE TABLE statement:
1. CREATE TABLE part(
2. part_id NUMBER,
3. part_name VARCHAR2(25),
4. manufacturer_id NUMBER(9),
5. retail_price NUMBER(7,2) NOT NULL,
6. CONSTRAINT part_id_pk PRIMARY KEY(part_id),
7. CONSTRAINT cost_nn NOT NULL(cost),
8. CONSTRAINT FOREIGN KEY (manufacturer_id) REFERENCES manufacturer(id));
Which line will cause an error?
(2) 7

Which constraint type enforces uniqueness?
(3) PRIMARY KEY

What is an attribute of data that is entered into a primary key column?
(1) Null and non-unique values cannot be entered into a primary key column.

You need to create the PROJECT_HIST table. The table must meet these requirements:
1. The table must contain the EMPLOYEE_ID and TASKED_HOURS columns for numeric data.
2. The table must contain the START_DATE and END_DATE column for date values.
3. The table must contain the HOURLY_RATE and PROJECT_COST columns for numeric data with precision and scale of 5,2 and 10,2 respectively.
4. The table must have a composite primary key on the EMPLOYEE_ID and START_DATE columns.
Evaluate this CREATE TABLE statement:
CREATE TABLE project_hist
( employee_id NUMBER,
start_date DATE,
end_date DATE,
tasked_hours NUMBER,
hourly_rate NUMBER(5,2),
project_cost NUMBER(10,2),
CONSTRAINT project_hist_pk PRIMARY KEY(employee_id, start_date));
How many of the requirements does the CREATE TABLE statement satisfy?
(2) All four of the requirements

Which type of constraint by default requires that a column be both unique and not null?
(2) PRIMARY KEY

A table can only have one unique key constraint defined. True or False?
(2) False

What is the highest number of NOT NULL constraints you can have on a table?
(4) You can have as many NOT NULL constraints as you have columns in your table.

Which two statements about NOT NULL constraints are true
(1) The Oracle Server creates a name for an unnamed NOT NULL constraint.
(5) You CANNOT add a NOT NULL constraint to an existing column using the ALTER TABLE ADD CONSTRAINT statement.

You need to ensure that each value in the SEAT_ID column is unique or null. Which constraint should you define on the SEAT_ID column?
(2) UNIQUE

Primary Key, Foreign Key, Unique Key and Check Constraints can be added at which two levels?
(2) Table
(5) Column

You need to ensure that the LAST_NAME column only contains certain character values. No numbers or special characters are allowed.
Which type of constraint should you define on the LAST_NAME column?
(1) CHECK

Which statement about constraints is true?
(3) NOT NULL constraints can only be specified at the column level.

You need to add a PRIMARY KEY to the DEPARTMENTS table. Which statement should you use?
(3) ALTER TABLE departments
ADD CONSTRAINT dept_id_pk PRIMARY KEY (dept_id);

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?
(3) The ALTER TABLE statement fails because the ADD CONSTRAINT clause contains a syntax error.

You need to remove the EMP_FK_DEPT constraint from the EMPLOYEE table in your schema. Which statement should you use?
(3) ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT;

Evaluate this statement
ALTER TABLE employees
ENABLE CONSTRAINT emp_id_pk;
For which task would you issue this statement?
(4) To activate the previously disabled constraint on the EMPLOYEE_ID column while creating a PRIMARY KEY index

Which statement should you use to add a FOREIGN KEY constraint to the DEPARTMENT_ID column in the EMPLOYEES table to refer to the DEPARTMENT_ID column in the DEPARTMENTS table?
(2) ALTER TABLE employees ADD CONSTRAINT dept_id_fk FOREIGN KEY (department_id) REFERENCES departments(department_id);

You need to add a NOT NULL constraint to the EMAIL column in the EMPLOYEES table. Which clause should you use?
(3) MODIFY

Examine the structures of the PRODUCT and SUPPLIER tables.
PRODUCT
PRODUCT_ID NUMBER NOT NULL, PRIMARY KEY
PRODUCT_NAME VARCHAR2 (25)
SUPPLIER_ID NUMBER FOREIGH 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
SUPPLIER
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?
(5) To disable any dependent integrity constraints on the SUPPLIER_ID column in the SUPPLIERS table

You want to disable the FOREIGN KEY constraint that is defined in the EMPLOYEES table on the DEPARTMENT_ID column. The constraint is referenced by the name FK_DEPT_ID_01. Which statement should you issue?
(4) ALTER TABLE employees
DISABLE CONSTRAINT fk_dept_id_01;

You can view the columns used in a constraint defined for a specific table by looking at which data dictionary table?
(1) USER_CONS_COLUMNS

You need to display the names and definitions of constraints only in your schema. Which data dictionary view should you query?
(2) USER_CONSTRAINTS

Which SQL statement below will correctly create the EMP table based on the structure of the EMPLOYEES table? Include only the EMPLOYEE_ID, FIRST_NAME, LAST_NAME, SALARY, and DEPARTMENT_ID columns.
(4) CREATE TABLE emp AS SELECT employee_id, first_name, last_name, salary, department_id FROM employees;

Which statement about table and column names is true?
(1) Table and column names must begin with a letter.

Evaluate this CREATE TABLE statement:
CREATE TABLE sales
( sales_id NUMBER(9),
customer_id NUMBER(9),
employee_id NUMBER(9),
description VARCHAR2(30),
sale_date TIMESTAMP WITH LOCAL TIME ZONE DEFAULT SYSDATE,
sale_amount NUMBER(7,2));
Which business requirement will this statement accomplish?
(4) Today's date should be used if no value is provided for the sale date.

A table has a column: RESPONSE_TIME. This is used to store the difference between the time the problem was reported and the time the problem was resolved. Data in the RESPONSE_TIME column needs to be stored in days, hours, minutes and seconds. Which data type should you use?
(4) INTERVAL DAY TO SECOND

You need to store the SEASONAL data in months and years. Which data type should you use?
(3) INTERVAL YEAR TO MONTH

Evaluate this CREATE TABLE statement:
CREATE TABLE sales
(sales_id NUMBER,
customer_id NUMBER,
employee_id NUMBER,
sale_date TIMESTAMP WITH LOCAL TIME ZONE,
sale_amount NUMBER(7,2));
Which statement about the SALE_DATE column is true?
(4) Data stored in the column will be returned in the database's local time zone.

A column that will be used to store binary data up to 4 Gigabyes in size should be defined as which datatype?
(3) BLOB

The EMPLOYEES table contains these columns:
LAST_NAME VARCHAR2(15) NOT NULL
FIRST_NAME VARCHAR2(10) NOT NULL
EMPLOYEE_ID NUMBER(4) NOT NULL
HIRE_DATE DATE NOT NULL
You need to remove the EMPLOYEE_ID column from the EMPLOYEES table. Which statement could you use to accomplish this task?
(3) ALTER TABLE employees DROP COLUMN employee_id;

Evaluate this statement:
ALTER TABLE employees SET UNUSED (fax);
Which task will this statement accomplish?
(3) Prevents data in the FAX column from being displayed, by performing a logical drop of the column.

The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER(9) Primary Key
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
DEPARTMENT_ID NUMBER(9)
SALARY NUMBER(8,2)
Which statement will permanently remove all the data in the EMPLOYEES table, but will retain the table's structure and storage space?
(2) DELETE employees; COMMIT;

The LINE_ITEM table contains these columns:
LINE_ITEM_ID NUMBER PRIMARY KEY
PRODUCT_ID NUMBER(9) FOREIGN KEY references the ID column of the PRODUCT table
QUANTITY NUMBER(9)
UNIT_PRICE NUMBER(5,2)
You need to disable the FOREIGN KEY constraint. Which statement should you use?
(1) ALTER TABLE line_item DISABLE CONSTRAINT product_id_fk;

What is the syntax for removing a PRIMARY KEY constraint and all its dependent constraints?
(1) ALTER TABLE table_name
DROP CONSTRAINT constraint_name CASCADE;

When dropping a constraint, which keyword(s) specifies that all the referential integrity constraints that refer to the primary and unique keys defined on the dropped columns are dropped as well?
(3) CASCADE

Evaluate this statement:
ALTER TABLE employees
ADD CONSTRAINT employee_id PRIMARY KEY;
Which result will the statement provide?
(1) A syntax error will be returned.

You disabled the EMPLOYEE_ID_PK PRIMARY KEY constraint on the ID column in the EMPLOYEES table and imported 100 records. You need to enable the constraint and verify that the new and existing ID column values do not violate the PRIMARY KEY constraint. Evaluate this statement:
ALTER TABLE employees
ENABLE employee_id_pk;
Which statement is true?
(4) The statement will NOT execute because it contains a syntax error.

Which of the following FOREIGN KEY Constraint keywords identifies the table and column in the parent table?
(4) REFERENCES

You need to create a composite primary key constraint on the EMPLOYEES table. Which statement is true?
(1) The PRIMARY KEY constraint must be defined at the table level.

You need to enforce a relationship between the LOC_ID column in the FACILITY table and the same column in the MANUFACTURER table. Which type of constraint should you define on the LOC_ID column?
(3) FOREIGN KEY

Which clause could you use to ensure that cost values are greater than 1.00?
(2) CONSTRAINT part_cost_ck CHECK (cost > 1.00)

When creating the EMPLOYEES table, which clause could you use to ensure that salary values are 1000.00 or more?
(4) CONSTRAINT employee_salary_min CHECK (salary >= 1000)

Which constraint can only be created at the column level?
(1) NOT NULL

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?
(4) NOT NULL constraints CANNOT be defined at the table level.

You need to ensure that the LAST_NAME column does not contain null values. Which type of constraint should you define on the LAST_NAME column?
(3) NOT NULL

Evaluate this CREATE VIEW statement:
CREATE VIEW emp_view
AS SELECT SUM(salary)
FROM employees;
Which statement is true?
(1) You cannot update data in the EMPLOYEES table using the EMP_VIEW view.

You administer an Oracle database, which contains a table named EMPLOYEES. Luke, a database user, must create a report that includes the names and addresses of all employees. You do not want to grant Luke access to the EMPLOYEES table because it contains sensitive data. Which of the following actions should you perform first?
(2) Create a view.

Which option would you use to modify a view rather than dropping it and recreating it?
(3) CREATE OR REPLACE

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?
(1) SELECT *
FROM part_name_v;

Views must be used to select data from a table. As soon as a view is created on a table, you can no longer select directly from the table. True or False?
(2) False

The FACULTY table contains these columns:
FACULTYID VARCHAR2(5) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(20)
LAST_NAME VARCHAR2(20)
ADDRESS VARCHAR2(35)
CITY VARCHAR2(15)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
STATUS VARCHAR2(2) NOT NULL
The COURSE table contains these columns:
COURSEID VARCHAR2(5) NOT NULL PRIMARY KEY
SUBJECT VARCHAR2(5)
TERM VARCHAR2(6)
FACULTYID VARCHAR2(5) NOT NULL FOREIGN KEY
You have been asked to compile a report that identifies all adjunct professors who will be teaching classes in the upcoming term. You want to create a view that will simplify the creation of this report. Which CREATE VIEW statements will accomplish this task?
(4) CREATE VIEW pt_view AS (SELECT first_name, last_name, status, courseid, subject, term FROM faculty f, course c WHERE f.facultyid = c.facultyid);

Which statement would you use to alter a view?
(4) CREATE OR REPLACE VIEW

Your manager has just asked you to create a report that illustrates the salary range of all the employees at your company. Which of the following SQL statements will create a view called SALARY_VU based on the employee last names, department names, salaries, and salary grades for all employees? Use the EMPLOYEES, DEPARTMENTS, and JOB_GRADES tables. Label the columns Employee, Department, Salary, and Grade, respectively.
(3) CREATE OR REPLACE VIEW salary_vu
AS SELECT e.last_name "Employee", d.department_name "Department", e.salary "Salary", j. grade_level "Grade"
FROM employees e, departments d, job_grades j
WHERE e.department_id = d.department_id AND e.salary BETWEEN j.lowest_sal and j.highest_sal;

You cannot insert data through a view if the view includes ______.
(4) A GROUP BY clause

You administer an Oracle database. Jack manages the Sales department. He and his employees often find it necessary to query 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 staff. The view should not accept INSERT, UPDATE or DELETE operations. Which of the following statements should you issue?
(4) 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;

What is the purpose of including the WITH CHECK OPTION clause when creating a view?
(4) To make sure no rows are updated through the view that will hinder those rows from being returned by the view.

Which action can be performed by using DML statements?
(1) Deleting records in a table

Which statement about performing DML operations on a view is true?
(3) You cannot modify data in a view if the view contains a group function.

Which of the following is TRUE regarding simple views?
(3) They can perform DML operations through the view

Which of the following describes a top-N query?
(4) A top-N query returns a limited result set, returning data based on highest or lowest criteria.

You must create a view that when queried 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?
(4) SELECT

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?
(2) You cannot modify data in the SALES table using the SALES_VIEW view.

You want to create a view based on the SALESREP table. You plan to grant access to this view to members of the Sales department. You want Sales employees to be able to update the SALESREP table through the view, which you plan to name SALESREP_VIEW. What should not be specified in your CREATE VIEW statement?
(4) A GROUP BY clause

Evaluate this SELECT statement:
SELECT ROWNUM "Rank", customer_id, new_balance
FROM (SELECT customer_id, new_balance FROM customer_finance ORDER BY new_balance DESC)
WHERE ROWNUM <= 25;
Which type of query is this SELECT statement?
(1) A Top-n query

Evaluate this statement:
CREATE INDEX sales_idx ON oe.sales (status);
Which statement is true?
(2) The CREATE INDEX statement creates a nonunique index.

Which one of the following statements about indexes is true?
(1) An index is created automatically when a PRIMARY KEY constraint is created.

What would you create to make the following statement execute faster?
SELECT * FROM employees WHERE LOWER(last_name) = 'chang';
(2) An index, either a normal or a function_based index.

Which of the following SQL statements will display the index name, table name, and the uniqueness of the index for all indexes on the EMPLOYEES table?
(3) SELECT index_name, table_name, uniqueness FROM user_indexes WHERE table_name = 'EMPLOYEES';

Which of the following best describes the function of an index?
(1) An index can increase the performance of SQL queries that search large tables.

Which of the following best describes the function of the NEXTVAL virtual column?
(4) The NEXTVAL virtual column increments a sequence by a predetermined value.

Which pseudocolumn returns the latest value supplied by a sequence?
(2) CURRVAL

You need to grant user BOB SELECT privileges on the EMPLOYEES table. You want to allow BOB to grant this privileges to other users. Which statement should you use?
(1) GRANT SELECT ON employees TO bob WITH GRANT OPTION;

Which of the following simplifies the administration of privileges?
(4) A role

To join a table in your database to a table on a second (remote) Oracle database, you need to use:
(4) A database link

Which of the following best describes the purpose of the REFERENCES object privilege on a table?
(1) It allows a user's session to read from the table but only so that foreign key constraints can be checked.

Granting an object privilege WITH GRANT OPTION allows the recipient to grant other object privileges on the table to other users. True or False?
(2) False

Which data dictionary view shows which system privileges have been granted to a user?
(2) USER_SYS_PRIVS

Parenthesis are not used to identify the sub expressions within the expression. True or False?
(2) False

Which of these SQL functions used to manipulate strings is not a valid regular expression function?
(3) REGEXP

Regular expressions are a method of describing both simple and complex patterns for searching and manipulating. True or False?
(1) True

You want to grant user BOB the ability to change other users' passwords. Which privilege should you grant to BOB?
(1) The ALTER USER privilege

Which of the following privileges must be assigned to a user account in order for that user to connect to an Oracle database?
(2) CREATE SESSION

You are the database administrator. You want to create a new user JONES with a password of MARK, and allow this user to create his own tables. Which of the following should you execute?
(2) CREATE USER jones IDENTIFIED BY mark; GRANT CREATE SESSION TO jones; GRANT CREATE TABLE TO jones;

User Kate wants to create indexes on tables in her schema. What privilege must be granted to Kate so that she can do this?
(4) None; users do not need extra privileges to create indexes on tables in their own schema

User JAMES has created a CUSTOMERS table and wants to allow all other users to SELECT from it. Which command should JAMES use to do this?
(3) GRANT SELECT ON customers TO PUBLIC;

If a database crashes, all uncommitted changes are automatically rolled back. True or False?
(1) True

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?
(2) 20

Which of the following best describes the function of an outer join?
(4) An outer join will return all rows that meet the join criteria and will return NULL values from one table if no rows from the other table satisfy the join criteria.

Using Oracle Proprietary join syntax, which operator would you use after one of the column names in the WHERE clause when creating an outer join?
(1) (+)

Nonequijoins are normally used with which of the following?
(1) Ranges of numbers
(3) Ranges of dates

Evaluate this SQL statement:
SELECT e.employee_id, e.last_name, e.first_name, d.department_name
FROM employees e, departments d
WHERE e.department_id = d.department_id AND employees.department_id > 5000
ORDER BY 4;
Which clause contains a syntax error?
(4) AND employees.department_id > 5000

You have two tables named EMPLOYEES and SALES. You want to identify the sales representatives who have generated at least $100,000 in revenue.
Which query should you issue?
(2) SELECT e.first_name, e.last_name, s.sales FROM employees e, sales s WHERE e.employee_id = s.employee_id AND revenue >= 100000;

What is the minimum number of join conditions required to join 5 tables together?
(2) 4

You need to create a report that lists all employees in department 10 (Sales) whose salary is not equal to $25,000 per year. Which query should you issue to accomplish this task?
(4) SELECT last_name, first_name, salary FROM employees WHERE salary != 25000 AND dept_id = 10;

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

You need to remove all the rows from the SALES_HIST table. You want to release the storage space, but do not want to remove the table structure. Which statement should you use?
(4) The TRUNCATE TABLE statement


Which statement about decreasing the width of a column is true?
(3) When a character column contains data, you can decrease the width of the column if the existing data does not violate the new size.

Which statement about a column is NOT true?
(2) You can modify the data type of a column if the column contains non-null data.

Examine the structure of the DONATIONS table.
DONATIONS:
PLEDGE_ID NUMBER
DONOR_ID NUMBER
PLEDGE_DT DATE
AMOUNT_PLEDGED NUMBER (7,2)
AMOUNT_PAID NUMBER (7,2)
PAYMENT_DT DATE
You need to reduce the precision of the AMOUNT_PLEDGED column to 5 with a scale of 2 and ensure that when inserting a row into the DONATIONS table without a value for the AMOUNT_PLEDGED column, a price of $10.00 will automatically be inserted. The DONATIONS table currently contains NO records. Which statement is true?
(2) Both changes can be accomplished with one ALTER TABLE statement.

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
(2) PRODUCTS_2001

Which statement about the NOT NULL constraint is true?
(1) The NOT NULL constraint must be defined at the column level.

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?
(3) To disable the PRIMARY KEY and any FOREIGN KEY constraints that are dependent on the PO_NUM column

You need to add a PRIMARY KEY constraint on the EMP_ID column of the EMPLOYEES table. Which ALTER TABLE statement should you use?
(2) ALTER TABLE employees
ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY employees(emp_id);

What actions can be performed on or with Constraints?
(1) Add, Drop, Enable, Disable, Cascade

You successfully create a table named SALARY in your company's database. Now, you want to establish a parent/child relationship between the EMPLOYEES table and the SALARY table by adding a FOREIGN KEY constraint to the SALARY table that references its matching column in the EMPLOYEES table. You have not added any data to the SALARY table. Which of the following statements should you issue?
(1) ALTER TABLE salary ADD CONSTRAINT fk_employee_id_01 FOREIGN KEY (employee_id) REFERENCES employees (employee_id);

To do a logical delete of a column without the performance penalty of rewriting all the table datablocks you can issue the following command:
(3) Alter table set unused

Evaluate this statement:
ALTER TABLE inventory MODIFY backorder_amount NUMBER(8,2);
Which task will this statement accomplish?
(5) Changes the definition of the BACKORDER_AMOUNT column to NUMBER(8,2)

Which data types stores variable-length character data? Select two
(3) CLOB
(4) VARCHAR2

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?
(3) Four

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?
(4) 4

Which CREATE TABLE statement will fail?
(2) CREATE TABLE date (date_id NUMBER(9));

You need to add a NOT NULL constraint to the COST column in the PART table. Which statement should you use to complete this task?
(2) ALTER TABLE part MODIFY (cost CONSTRAINT part_cost_nn NOT NULL);

Which of the following best describes the function of a CHECK constraint?
(2) A CHECK constraint defines restrictions on the values that can be entered in a column or combination of columns.

When creating a referential constraint, which keyword(s) identifies the table and column in the parent table
(2) REFERENCES

Which statement about a FOREIGN KEY constraint is true?
(2) A FOREIGN KEY constraint requires the constrained column to contain values that exist in the referenced Primary or Unique key column of the parent table.

Which of the following types of constraints enforces uniqueness?
(3) PRIMARY KEY

This SQL command will do what?
ALTER TABLE employees ADD CONSTRAINT emp_manager_fk FOREIGN KEY(manager_id) REFERENCES employees(employee_id);
(2) Add a FOREIGN KEY constraint to the EMPLOYEES table indicating that a manager must already be an employee.

You need to remove the EMP_FK_DEPT constraint from the EMPLOYEES table in your schema. Which statement should you use?
(3) ALTER TABLE employees DROP CONSTRAINT EMP_FK_DEPT;

The database administrator wants to allow user Marco to create new tables in his own schema. Which privilege should be granted to Marco?
(1) CREATE TABLE

Which statement would you use to grant a role to users
(1) GRANT

Which statement would you use to grant privileges to a role?
(3) GRANT

Which keyword would you use to grant an object privilege to all database users?
(3) PUBLIC

When granting an object privilege, which option would you include to allow the grantee to grant the privilege to another user
(1) WITH GRANT OPTION

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
(2) A savepoint

Which of the following best describes the term "read consistency"?
(3) It prevents other users from seeing changes to a table until those changes have been committed

You are creating the EMPLOYEE table. This table should contain the COMMISSION column and use a value of 10 percent if no commission value is provided when a record is inserted. Which line should you include in the CREATE TABLE statement to accomplish this task?
(1) commission NUMBER(4,2) DEFAULT 0.10

You need to create a composite primary key constraint on the EMPLOYEE table. Which statement is true
(1) The PRIMARY KEY constraint must be defined at the table level.

Evaluate this statement
ALTER TABLE employee
ENABLE CONSTRAINT emp_id_pk;
For which task would you issue this statement?
(4) to activate the previously disabled constraint on the EMP_ID column while creating a PRIMARY KEY index

You need to add a PRIMARY KEY to the DEPARTMENT table. Which statement should you use?
(3) ALTER TABLE department ADD CONSTRAINT dept_id_pk PRIMARY KEY (dept_id);

You need to add a PRIMARY KEY constraint on the EMP_ID column of the EMPLOYEE table. Which ALTER TABLE statement should you use?
(2) ALTER TABLE ADD CONSTRAINT emp_emp_id_pk PRIMARY KEY employee(emp_id);

Which statement about the CREATE VIEW statement is false?
(1) A CREATE VIEW statement CANNOT contain a join query.

A view can be used to keep a history record of old data from the underlying tables, so even if a row is deleted from a table, you can still select the row through the view. True or False?
(2) False

In order to query a database using a view, which of the following statements applies?
(2) You can retrieve data from a view as you would from any table.

You need to create a view that when queried will display the name, employee identification number, first and last name, salary, and department identification number. When queried, the display should be sorted by salary from lowest to highest, then by last name and first name alphabetically. The view definition should be created regardless of the existence of the EMPLOYEE table. No DML may be performed when using this view. Evaluate these statements:
<>CREATE OR REPLACE NOFORCE VIEW EMP_SALARY_V AS SELECT emp_id, last_name, first_name, salary, dept_id FROM employee WITH READ ONLY;
<>SELECT * FROM emp_salary_v ORDER BY salary, last_name, first_name;
Which statement is true?
(2) The CREATE VIEW statement will fail if the EMPLOYEE table does not exist.

You cannot create a view if the view subquery contains an inline view. True or False?
(2) False

You create a view on the EMPLOYEES and DEPARTMENTS tables to display salary information per department. What will happen if you issue the following statement:
CREATE OR REPLACE VIEW sal_dept
AS SELECT SUM(e.salary) sal, d.department_name
FROM employees e, departments d
WHERE e.department_id=d.department_id
GROUP BY d.department_name
ORDER BY d.department_name;
(1) A complex view is created that returns the sum of salaries per department, sorted by department name.

Which option would you use when creating a view to ensure that no DML operations occur on the view?
(3) WITH READ ONLY

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?
(2) You cannot modify data in the SALES table using the SALES_VIEW view.

An "inline view" is an unnamed select statement found:
(4) Enclosed in parenthesis within the from clause of a surrounding query

The EMP_HIST_V view is no longer needed. Which statement should you use to the remove this view?
(4) DROP VIEW emp_hist_v;

Evaluate this statement:
CREATE SEQUENCE sales_item_id_seq START WITH 101 MAXVALUE 9000090 CYCLE;
Which statement about this CREATE SEQUENCE statement is true?
(1) The sequence will reuse numbers and will start with 101.

You create a CUSTOMERS table in which CUSTOMER_ID is designated as a primary key. You want the values that are entered into the CUSTOMER_ID column to be generated automatically. Which of the following actions should you perform?
(4) Create a sequence.

Evaluate this statement:
CREATE SEQUENCE line_item_id_seq MINVALUE 100 MAXVALUE 130 INCREMENT BY -10 CYCLE;
What will be the first five numbers generated by this sequence?
(4) The CREATE SEQUENCE statement will fail because a START WITH value was not specified.

You issue this statement:
ALTER SEQUENCE po_sequence INCREMENT BY 2;
Which statement is true?
(2) Future sequence numbers generated will increase by 2 each time a number is generated.

Which of the following best describes the function of the CURRVAL virtual column?
(1) The CURRVAL virtual column will display the integer that was most recently supplied by a sequence.

You want to speed up the following query by creating an index:
SELECT * FROM employees WHERE (salary * 12) > 100000;
Which of the following will achieve this?
(2) Create a function-based index on (salary * 12)

The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER NOT NULL, Primary Key
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
DEPARTMENT_ID NUMBER Foreign Key to PRODUCT_ID column of the PRODUCT table
HIRE_DATE DATE DEFAULT SYSDATE
SALARY NUMBER (8,2) NOT NULL
On which column is an index automatically created for the EMPLOYEES table?
(4) EMPLOYEE_ID

The EMPLOYEE table contains these columns:
EMP_ID NOT NULL, Primary Key
SSNUM NOT NULL, Unique
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPT_ID NUMBER Foreign Key to DEPT_ID column of the DEPARTMENT table
SALARY NUMBER(8,2)
You execute this statement:
CREATE INDEX emp_name_idx
ON employee(last_name, first_name);
Which statement is true?
(4) The statement creates a composite non-unique index.

You need to determine the table name and column name(s) on which the SALES_IDX index is defined. Which data dictionary view would you query
(4) USER_IND_COLUMNS

Which of the following best describes a role in an Oracle database?
(3) A role is a name for a group of privileges.

You create a view named EMPLOYEES_VIEW on a subset of the EMPLOYEES table. User AUDREY needs to use this view to create reports. Only you and Audrey should have access to this view. Which of the following actions should you perform?
(3) GRANT SELECT ON employees_view TO audrey;

Which of the following best describes the function of the CURRVAL virtual column?
(1) The CURRVAL virtual column will display the integer that was most recently supplied by a sequence.

When creating an index on one or more columns of a table, which of the following statements are true?
(1) You should create an index if the table is large and most queries are expected to retrieve less than 2 to 4 percent of the rows.
(3) You should create an index if one or more columns are frequently used together in a join condition.

What is the correct syntax for creating an index?
(1) CREATE INDEX index_name ON table_name(column_name);

The following indexes exist on the EMPLOYEES table:
- a unique index on the EMPLOYEE_ID primary key column
- 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 dropped, which indexes are automatically dropped at the same time?
(5) All Indexes

Unique indexes are automatically created on columns that have which two types of constraints?
(2) UNIQUE and PRIMARY KEY

User Mary's schema contains an EMP table. Mary has Database Administrator privileges and executes the following statement:
CREATE PUBLIC SYNONYM emp FOR mary.emp;
User Susan now needs to SELECT from Mary's EMP table. Which of the following SQL statements can she use?
(2) SELECT * FROM emp;
(4) SELECT * FROM mary.emp;

Evaluate this statement:
CREATE PUBLIC SYNONYM testing FOR chan.testing;
Which task will this statement accomplish?
(4) It eliminates the need for all users to qualify TESTING with its schema.

What is the correct syntax for creating a synonym d_sum for the view DEPT_SUM_VU?
(4) CREATE SYNONYM d_sum FOR dept_sum_vu;

The CUSTOMERS table exists in user Mary's schema. Which statement should you use to create a synonym for all database users on the CUSTOMERS table?
(2) CREATE PUBLIC SYNONYM cust FOR mary.customers;

Evaluate the structure of the EMPLOYEES 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)
Which statement should you use to increase the LAST_NAME column length to 35 if the column currently contains 200 records?
(3) ALTER TABLE employees MODIFY (last_name VARCHAR2(35));

For a View created using the WITH CHECK OPTION keywords, which of the following statements are true?
(2) Prohibits changing rows not returned by the subquery in the view definition.

You can create a view if the view subquery contains an inline view. True or False?
(1) True

Which statement about performing DML operations on a view is true?
(1) You can perform DML operations on simple views.

You need to create a new view on the EMPLOYEES table to update salary information for employees in Department 50. You need to ensure that DML operations through the view do not change the result set of the view. Which clause should include in the CREATE VIEW statement?
(4) WITH CHECK OPTION

You need to create a view on the SALES table, but the SALES table has not yet been created. Which statement is true?
(4) You can use the FORCE option to create the view before the SALES table has been created.

Evaluate this CREATE VIEW statement:
CREATE VIEW pt_view AS (SELECT first_name, last_name, status, courseid, subject, term FROM faculty f, course c WHERE f.facultyid = c.facultyid);
Which type of view will this statement create?
(4) Complex

Which of the following keywords cannot be used when creating a view?
(4) They are all valid keywords when creating views.

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)
You created a Top-n query report that displays the account numbers and new balance of the 800 accounts that have the highest new balance value. The results are sorted by payments value from highest to lowest. Which SELECT statement clause is included in your query?
(1) Inner query: ORDER BY new_balance DESC

The EMPLOYEES table contains these columns:
EMPLOYEE_ID NUMBER
LAST_NAME VARCHAR2(25)
FIRST_NAME VARCHAR2(25)
DEPARTMENT_ID NUMBER
JOB_ID VARCHAR(25)
MANAGER_ID NUMBER
SALARY NUMBER(9,2)
COMMISSOIN NUMBER(7,2)
HIRE_DATE DATE
Which SELECT statement could be used to display the 10 lowest paid clerks that belong to department 70?
(2) SELECT ROWNUM "Ranking",last_name||','||first_name "Employee", salary "Salary" FROM SELECT last_name, first_name, salary, job_id FROM employees WHERE job_id LIKE 'CLERK' AND department_id = 70 ORDER BY salary) WHERE ROWNUM<=10;

Which statement about an inline view is true?
(2) An inline view is a subquery in the FROM clause, often named with an alias.

When creating a sequence, which keyword or option specifies the minimum sequence value?
(2) MINVALUE

Evaluate this CREATE SEQUENCE statement:
CREATE SEQUENCE line_item_id_seq CYCLE;
Which statement is true?
(4) The sequence will continue to generate values after the maximum sequence value has been generated.

When used in a CREATE SEQUENCE statement, which keyword specifies that a range of sequence values will be preloaded into memory?
(3) CACHE

Which statement about an index is true?
(4) An index created on multiple columns is called a composite or concatenated index.

You want to create a composite index on the FIRST_NAME and LAST_NAME columns of the EMPLOYEES table. Which SQL statement will accomplish this task?
(3) CREATE INDEX fl_idx ON employees(first_name,last_name);

What is the correct syntax for creating a private synonym d_sum for the view DEPT_SUM_VU?
(4) CREATE SYNONYM d_sum FOR dept_sum_vu;

You create a table named CUSTOMERS and define a PRIMARY KEY constraint on the CUST_ID column. Which actions occur automatically?
(3) A unique index is created on the CUST_ID column, if one does not already exist.

As user Julie, you issue this statement:
CREATE SYNONYM emp FOR sam.employees;
Which task was accomplished by this statement?
(4) You created a private synonym on the EMPLOYEES table owned by user Sam.

User Mary's schema contains an EMPLOYEES table. Mary has Database Administrator privileges and executes the following statement:
CREATE PUBLIC SYNONYM employees FOR mary.employees;
User Susan now needs to SELECT from Mary's EMPLOYEES table. Which of the following SQL statements can she use?
(2) SELECT * FROM employees;
(4) SELECT * FROM mary.employees;

Which statement would you use to remove the LAST_NAME_IDX index on the LAST_NAME column of the EMPLOYEES table?
(1) DROP INDEX last_name_idx;

For which column would you create an index?
(3) A column with a large number of null values

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?
(4) An error message is produced, and no index is created.

Evaluate this statement:
ALTER USER bob IDENTIFIED BY jim;
Which statement about the result of executing this statement is true?
(1) A new password is assign to user BOB.

User SUSAN creates an EMPLOYEES table, and then creates a view EMP_VIEW which shows only the FIRST_NAME and LAST_NAME columns of EMPLOYEES. User RUDI needs to be able to access employees' names but no other data from EMPLOYEES. Which statement should SUSAN execute to allow this?
(3) GRANT SELECT ON emp_view TO rudi;

User CHANG has been granted SELECT, UPDATE, INSERT and DELETE privileges on the EMPLOYEES table. You now want to prevent Chang from adding or deleting rows from the table, while still allowing him to read and modify existing rows. Which statement should you use to do this?
(2) REVOKE INSERT, DELETE ON employees FROM chang;

Which statement would you use to add privileges to a role?
(3) GRANT

Which statement would you use to remove an object privilege granted to a user?
(2) REVOKE

Which of the following statements is a valid reason for using a view?
(2) 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.

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)
You 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;
What statement is true?
(2) The statement will not necessarily return the 25 highest new balance values, as the inline view has no ORDER BY.

An inline view is an unnamed select statement found:
(4) Enclosed in parentheses within the from clause of a surrounding query

To see the most recent value that you fetched from a sequence named 'my_seq' you should reference:
(4) my_seq.currval

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?
(1) True

User CRAIG creates a view named INVENTORY_V, which is based on the INVENTORY table. CRAIG wants to make this view available for querying to all database users. Which of the following actions should CRAIG perform?
(3) He should assign the SELECT privilege to all database users for INVENTORY_V view.

You grant user AMY the CREATE SESSION privilege. Which type of privilege have you granted to AMY?
(1) A system privilege

Which of the following are system privileges?
(1) CREATE TABLE
(3) CREATE SYNONYM

_________________ are special characters that have a special meaning, such as a wildcard character, a repeating character, a non-matching character, or a range of characters. You can use several of these symbols in pattern matching.
(2) Meta characters

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?
(2) A and B

What happens when you create a Cartesian product?
(1) All rows from one table are joined to all rows of another table

You have been asked to create a report that lists all corporate customers and all orders that they have placed. The customers should be listed alphabetically beginning with the letter 'A', and their corresponding order totals should be sorted from the highest amount to the lowest amount.
Which of the following statements should you issue?
(2) SELECT c.custid, c.companyname, o.orderdate, o. custid, o.amount FROM customers c, orders o WHERE c.custid = o.custid ORDER BY companyname, amount DESC;

The PATIENTS and DOCTORS tables contain these columns:
PATIENTS
PATIENT_ID NUMBER(9)
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
DOCTORS
DOCTOR_ID NUMBER(9)
LAST_NAME VARCHAR2 (20)
FIRST_NAME VARCHAR2 (20)
You issue this statement:
SELECT patient_id, doctor_id
FROM patients, doctors;
Which result will this statement provide?
(1) A report containing all possible combinations of the PATIENT_ID and DOCTOR_ID values

Which statement about outer joins is true?
(3) The OR operator cannot be used to link outer join conditions.

Using Oracle Proprietary join syntax, which two operators can be used in an outer join condition using the outer join operator (+)?
(1) AND and =

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)
The EMPLOYEE_ID column currently contains 500 employee identification numbers. Business requirements have changed and you need to allow users to include text characters in the identification values. Which statement should you use to change this column's data type?
(4) You CANNOT modify the data type of the EMPLOYEE_ID column, as the table is not empty.

How many PRIMARY KEY constraints can be created for each table?
(2) One and only one

Which statement about performing DML operations on a view is true?
(1) You can perform DML operations on simple views.

Which statement about the CREATE VIEW statement is true?
(1) A CREATE VIEW statement CAN contain a join query.

Which keyword(s) would you include in a CREATE VIEW statement to create the view regardless of whether or not the base table exists?
(1) FORCE

Which statement would you use to modify the EMP_ID_SEQ sequence used to populate the EMPLOYEE_ID column in the EMPLOYEES table?
(4) ALTER SEQUENCE emp_id_seq ...;

The EMPLOYEES table has an index named LN_IDX on the LAST_NAME column. You want to change this index so that it is on the FIRST_NAME column instead. Which SQL statement will do this?
(4) None of the above; you cannot ALTER an index.

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?
(1) True

User BOB's schema contains an EMPLOYEES table. BOB executes the following statement: GRANT SELECT ON employees TO mary WITH GRANT OPTION;
Which of the following statements can MARY now execute successfully?
(1) SELECT FROM bob.employees;
(3) GRANT SELECT ON bob.employees TO PUBLIC;

You want to grant privileges to user CHAN that will allow CHAN to update the data in the EMPLOYEES table. Which type of privileges will you grant to CHAN?
(2) Object privileges

You need to join the EMPLOYEES table and the SCHEDULES table, but the two tables do not have any corresponding columns. Which type of join will you create?
(3) A non-equijoin

You need to provide a list of the first and last names of all employees who work in the Sales department who earned a bonus and had sales over $50,000. The company president would like the sales listed starting with the highest amount first. The EMPLOYEES table and the SALES_DEPT table contain the following columns:
EMPLOYEES
EMP_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPTARTMENT_ID VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)
SALES_DEPT
SALES_ID NUMBER(10) PRIMARY KEY
SALES NUMBER(20)
QUOTA NUMBER(20)
MANAGER VARCHAR2(30)
BONUS NUMBER(10)
EMPLOYEE_ID NUMBER(10) FOREIGN KEY
Which SELECT statement will accomplish this task?
(4) SELECT e.employee_id, e.last_name, e.first_name, s.employee_id, s.bonus, s. sales FROM employees e, sales_dept s WHERE e.employee_id = s.employee_id AND s.bonus IS NOT NULL AND sales > 50000 ORDER BY sales DESC;

Which statement about the join syntax of an Oracle Proprietary join syntax SELECT statement is true?
(4) The WHERE clause represents the join criteria.

Unit testing is done prior to a database going into production to ensure a random number of business requirements functions properly. True or False?
(2) False

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)
The EMPLOYEE_ID column currently contains 500 employee identification numbers. Business requirements have changed and you need to allow users to include text characters in the identification values. Which statement should you use to change this column's data type?
(4) You CANNOT modify the data type of the EMPLOYEE_ID column, as the table is not empty.

How many PRIMARY KEY constraints can be created for each table?
(2) One and only one
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?
(1) 48000

You have the following EMPLOYEES table:
EMPLOYEE_ID NUMBER(5) NOT NULL PRIMARY KEY
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(25)
ADDRESS VARCHAR2(35)
CITY VARCHAR2(25)
STATE VARCHAR2(2)
ZIP NUMBER(9)
TELEPHONE NUMBER(10)
DEPARTMENT_ID NUMBER(5) NOT NULL FOREIGN KEY
The BONUS table includes the following columns:
BONUS_ID NUMBER(5) NOT NULL PRIMARY KEY
ANNUAL_SALARY NUMBER(10)
BONUS_PCT NUMBER(3, 2)
EMPLOYEE_ID VARCHAR2(5) NOT NULL FOREIGN KEY
You want to determine the amount of each employee's bonus as a calculation of salary times bonus. Which of the following queries should you issue?
(1) SELECT e.first_name, e.last_name, b.annual_salary * b. bonus_pct FROM employees e, bonus b WHERE e.employee_id = b.employee_id;

A software verification and validation method.
(2) Unit testing