Update the Treasury department to Cash department.

And this will be managed by the Manager

of Finance department.
Answer: UPDATE departments SET department_name = 'Procurement', manager_id = 100
WHERE department_name = 'Treasury'

Given the SQL command

SELECT employees.first_name, employees.last_name,
employees.salary,departments.department_name
FROM employees, departments
WHERE employees.department_id = departments.department_id;
Which of the following describes the SQL command?
Answer: List of employees name, salary and department name

Remove all Manager positions in the department table

Answer: DELETE FROM jobs WHERE job_title LIKE '%Manager%';

Diana Lorentz was transferred to Administration department. Using the employees and
department table information update the profile of employee.
Answer: UPDATE employees SET manager_id = 200, department_id = 10 WHERE last_name
= 'Lorentz' AND first_name = 'Diana';

Add a 500 pesos increase in salary of all employees who have rendered services 10 years and
above.
Answer:  UPDATE employees SET salary = salary + 500 where TO_CHAR(sysdate,'YYYY') -
TO_CHAR(hire_date,'YYYY') >= 10

Display the manager id and employee id of every employee

Answer: SELECT E.employee_id, D.manager_id FROM employees E JOIN departments D ON
E.department_id = D.department_id

Update the Purchasing department name to Procurement. And this will be managed by the
Manager of Administration department.
Answer: UPDATE departments SET department_name = 'Procurement', manager_id = 200
WHERE department_name = 'Purchasing'

Remove the Shareholder Services department in the department table

Answer: DELETE FROM departments WHERE department_name = ‘Shareholder Services’
Create a SQL command to add a new position Java Developer with job id of JAVA_DEV whose
salary ranges from 7,000 to 8,000.
Answer: INSERT INTO jobs VALUES ('JAVA_DEV', 'Java Developer', 7000, 8000)

Display all the employee id, names of employees ,job id and department nameof all
employees of the Finance Department.
Answer: SELECT A.first_name, A.last_name, A.job_id, B.department_name FROM
employees A JOIN departments B ON ( A.department_id = B.department_id)
WHERE B.department_name = 'Finance'

Create a SQL command to add a new position Database Administrator with job id of
DB_ADMIN whose salary ranges from 10,000 to 18,000.
Answer: INSERT INTO jobs VALUES ('DB_ADMIN', 'Database Administrator', 10000,
18000)

Create a SQL command to update the employees table

Employee id : 200 
Name : Mr. Gary Park 
Date hired : July 1, 2017 
Position :IT Programmer 
Salary : 10000 
Department of assignment : 60 
Reporting to manager : 103
Answer: INSERT INTO employees
(employee_id,first_name,last_name,hire_date,job_id,salary,manager_id,department
_id)
VALUES (200,'Gary','Park', TO_CHAR('Jul 1, 2017', 'MON DD, YYYY'), 'IT_PROG',
10000, 103, 60)

Display the location id of all employee's name and salary whose salary is from 5000 to 10000.
Answer: SELECT first_name, last_name, salary FROM employees JOIN departments
USING (department_id) WHERE salary >= 5000 AND salary <= 10000
A new department name Training with department id 300 was created in the company.
This will be managed by a Manager with ID of 203 and will located at location id 2400. 

Create a SQL command to update the information in the department table.

Answer: INSERT INTO departments VALUES (300, 'Training', 203, 2400)

Given the SQL command

SELECT * FROM employees JOIN departments USING (department_id)
Which of the following describes the SQL command?
Answer: Joined table from the employees and department table

What are the 2 types of security being applied to a database

Answer: GRANT & REVOKE

Which of the following DOES NOT describes the state of the data after the COMMIT command
Answer: None of the choices
Given the DEPARTMENTS table and USER user1, evaluate the SQL command:

GRANT  select
ON     user1
TO     departments;
Answer: The SQL command is incorrect

TRUE OR FALSE.
A FOREIGN KEY is a field in one table that refers to the PRIMARY KEY in another table.
Answer: True

Which of the following will grant a query privileges on the STUDENT table
Answer: GRANT select ON student TO matt

A join between two tables that returns the results of the INNER join as well as the_______________
rows from the left (or right) table is called a left (or right) OUTER join.
Answer: Unmatched

INDEX is an example of _____________________ privilege.

Answer: Object

A _______________ consists of a collection of DML statements that form a logical unit of work.
Answer: Transaction
Which of the following describes the command below? 
SELECT * FROM employees WHERE department = 60 FOR UPDATE
Answer: Locks the rows in the EMPLOYEES table with department id is 60.

Matt wants to change his password from 1234 to abcd.

Which of the following will perform the task?
Answer: ALTER USER matt IDENTIFIED abcd;

SQL command to create a marker in the current transaction.

Answer: SAVEPOINT

Which of the following command will delete all records in the table employees
Answer: DELETE FROM employees

Which of the following will erase all records in the departments table
Answer: TRUNCATE TABLE departments

A Cartesian product is formed when:

Answer: All of the choices

Which of the following will is the correct command to create a role.

Answer: GRANT ROLE gen_manager

Angelica is the Oracle Database Administrator. She was been assigned to create an
access for the newly hired employee named Matt to the Oracle Database. 

Which of the following SQL command Angelica will execute?

Answer: CREATE USER matt IDENTIFIED BY 1234

You want to cancel the privilege of matt to add records from the employees table.
Answer: REVOKE insert ON employees FROM matt;

Evaluate the given SQL syntax

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...)
WHERE condition;
Answer: This will produce an error.
Which of the following SQL command that the DBA will run to provide Matt to create a table in
the Oracle Database.
Answer: GRANT create table TO matt

TRUE OR FALSE.
Multiple fields in NOT allowed in a Foreign key.
Answer: False

GRANT is a ___________________ command.

Answer: DCL

The DBA will create a new user name sales.

Which of the following SQL command will perform the creation?
Answer: CREATE sales ACCESS 1234

These are privileges that performs a particular action within the database.
Answer: System Privileges

Nathaniel had accidentally deleted all the records in the newly updated ORACLE database
using the DELETE SQL command.
Answer: Run the ROLLBACK command

You want to cancel the privilege of matt to retrieve records from the employees table.
Answer: REVOKE select ON matt FROM employees;

This has the highest level of privileges for task such as creating new users, removing users and tables and
backing up tables.
Answer: DBA

These are collection of objects such as tables, views, and sequences.

Answer: Schema

A join between two tables that returns the results of an INNER join as well as the results of a
left and right join is
Answer: FULL OUTER JOIN
Which of the following PL/SQL will execute successfully?
Answer: DECLARE
v_salary INTEGER(20);
BEGIN
SELECT salary INTO v_salary FROM employees WHERE employee_id = 150;
END;

This is a type of cursor which is created and managed internally by the Oracle server to process
SQL statements
Answer: Implicit

You want to display all the records of employee the same with the salary employee number 103.
Answer: SELECT * FROM employees WHERE salary = (SELECT salary from employees where
employee_id= 103)

You have been tasked to update the database by creating a PL/SQL to increase the salary of all
IT Programmer employees by twice of their existing salary. Which of the following will execute
successfully?
Answer: DECLARE
v_job_id employees.job_id%TYPE := 'IT_PROG';
BEGIN
UPDATE employees SET salary = salary * 2 WHERE job_id = v_job_id;
END;

Evaluate the SQL command

SELECT employee_id, job_id, salary from employees where salary < ALL (SELECT salary FROM
employees WHERE job_id = 'FI_ACCOUNT') AND job_id = 'IT_PROG'
Answer: This has no error.

Evaluate the SQL command

SELECT employee_id, salary from employees where salary = ANY (SELECT salary FROM
employees WHERE job_id = 'IT_PROG') AND job_id = 'ST_CLERK'
Answer: This has no error.

How do you test the output of a PL/SQL block?

Answer: Use a predefined Oracle package and its procedure

In the DECLARE section of the PL/SQL block

Answer: All of the choices
PL/SQL stands for
Answer: Procedural Language extension to SQL

PL/SQL Provides a block structure for executable units of ________________

Answer: Code

Which of the following PL/SQL that will display the total number employees whose salary is
10000 and above?
Answer: DECLARE
v_salary employees.salary%TYPE := 10000;
BEGIN
SELECT COUNT(*) INTO v_salary FROM employees WHERE salary >= v_salary;
DBMS_OUTPUT.PUT_LINE(v_salary);
END;

What are the three PL/SQL block types?

Answer: Anonymous, Procedure, Function

Evaluate the PL/SQL 

1.      DECLARE
2.      v_first_name VARCHAR2(50);
3.      v_last_name VARCHAR2(50);
4.      v_salary INTEGER(20);
5.      BEGIN
6.      SELECT first_name, last_name, salary INTO v_first_name, v_last_name, v_salary
FROM employees WHERE department_id = 60;
7.      DBMS_OUTPUT.PUT_LINE('Firstname : '|| v_first_name);
8.      DBMS_OUTPUT.PUT_LINE('Lastname : '|| v_last_name);
9.      DBMS_OUTPUT.PUT_LINE('Salary : '|| v_salary);
10.    END;
Answer: Error in Line 6.

Evaluate the SQL Command 

SELECT job_id, job_title  FROM jobs J  WHERE INCLUDES

 (SELECT * FROM employees WHERE J.job_id = e.job_id );

Answer: The SQL will return an error. Invalid "INCLUDES" parameter.

You want to display all records in the database whose salary is above the salary of Alexander
Hunold.
Answer: SELECT * from employees WHERE salary < (SELECT salary FROM employees
WHERE first_name = 'Alexander' AND last_name = 'Hunold')

In PL/SQL Block Structure, which of the following are mandatory?

Answer: BEGIN and END

Which of the following does NOT describes SELECT Statement in a PL/SQL.

Answer: Queries must return only one column.

Actions are being performed when error occurs during PL/SQL execution in the
Answer: EXCEPTION

Which of the following is CORRECT about sub-queries?

Answer: Subquery execute before the main query executes.

You have been tasked to update the database by creating a PL/SQL to increase the
salary of all IT Programmer employees by 50% of their existing salary. 

Which of the following will execute successfully?

Answer: DECLARE
v_job_id employees.job_id%TYPE := 'IT_PROG';
BEGIN
UPDATE employees SET salary = salary *0.50 WHERE job_id = v_job_id;
END;
Evaluate the following PL/SQL. 

1 DECLARE 
2 v_employee_id employees.employee_id%TYPE := 114; 
3 BEGIN 
4 DELETE employees WHERE employee_id = v_employee_id; 
5 END;
Answer: The PL/SQL will delete employee number 114.
Which of the following is INCORRECT?
Answer:  Use single-row operators with multiple-row subqueries

ou want to display all employee id, name, hired date and salary who are hired after employee
104 was hired.
Answer: SELECT employee_id, last_name, hire_date, salary FROM employees WHERE
TO_NUMBER(TO_CHAR(hire_date, 'YYYY')) > (SELECT TO_NUMBER(TO_CHAR(hire_date,
'YYYY')) FROM employees WHERE employee_id = 104)

In PL/SQL Block Structure, which of the following are OPTIONAL?

Answer: None of the choices

Which of the folllowing is TRUE?

Answer: SQL code are embedded withing PL/SQL statements

Evaluate the SQL command

SELECT employee_id, last_name, first_name, job_id FROM employees WHERE department_id =
(SELECT max(department_id) FROM employees GROUP BY department_id)
Answer: This will return an error. Single-row subquery returns more than one row.

Which of the folllowing statement describes PL/SQL?

Asnwer: PL/SQL is an Oracle proprietary, procedural, 3GL programming language

Which of the folllowing is required in a subquery?

Answer: SELECT

What is the exception name when PL/SQL has an internal problem

Answer: PROGRAM_ERROR

Evaluate the following PL/SQL.

DECLARE
v_email VARCHAR(20);
BEGIN
SELECT email INTO v_email FROM EMPLOYEES WHERE email like 'D%';
DBMS_OUTPUT.PUT_LINE ('Employees whose email address starts with letter D :'
|| v_email);
EXCEPTION
WHEN TOO_MANY_ROWS THEN
DBMS_OUTPUT.PUT_LINE (' Your select statement retrieved multiple rows.');
END;
Answer: The PL/SQL block will run successfully.
What is the last clause in trapping exceptions?
Answer: WHEN OTHERS

Which of the following DOES NOT describes an exception?

Answer: Exception is a PL/SQL error that is raised before program execution.

What is the error trapping function that returns the numeric value of the error code?
Answer: SQLCODE

You can use this procedure to issue user-defined error messages from stored subprograms.
Answer: RAISE_APPLICATION_ERROR

When an exception is predefined by Oracle server, the exception is raised

Answer: Explicitly

Which of the following syntax to declare EXCEPTION named e_invalid_id?

Answer:  e_invalid_id EXCEPTION;

In the DECLARE section of the PL/SQL block,

Answer: All of the choices

When an exception is user defined, the exception is raised

Answer: Explicitly

When an exception is predefined by Oracle server, the exception is raised ____________ .

Answer: Explicitly

Complete the diagram in Trapping Non-Predefined Oracle Server Errors.

Answer: Declare, Associate, Reference

What is the exception name when single row SELECT returned no data.
Answer: NO_DATA_FOUND

RAISE_APPLICATION_ERROR is used in two different places. These are ___________________.

Answer: Executable and exceptions section

When an exception is user defined, the exception is raised 

Answer: Explicitly

You can trap any error by including a corresponding handler within the exception-handling

section of the PL/SQL block.

Answer: True