You
want to display all the job position titles whose salary is salary from
5,000 to 12,000 arrange from highest to lowest
- SELECT job_title FROM jobs WHERE min_salary >= 5000 AND
max_salary<= 10000
What
is the SQL command to count the number of records in the employees table?
- SELECT COUNT(*) FROM employees
True
or False. The AND, OR, NOT are comparison operators.
-False
Evaluate
the following SQL command
SELECT * FROM jobs WHERE job_title LIKE 'Manager%'
- No records will be displayed
You
want to display all the employee id and the month an employee was hired.
Which SQL statement give the required output?
- SELECT employee_id, hire_date, TO_CHAR(hire_date,'Month') AS "Hired
Month", job_id FROM employees
You
want to generate the total salary per month of every department in the company.
-SELECT department_id, SUM(salary) FROM employees GROUP BY department_id
You want to
display the employee's last name whose salary is below 10,000 and whose
lastname starts with letter D.
Which
SQL statement give the required output format of the salary?
- SELECT last_name, TO_INTEGER(salary, $999,999.99) AS "MONTHLY
SALARY" FROM employees WHERE salary < 10000 WHERE last_name IN 'D%'
SELECT
last_name, TO_INTEGER(salary, $999,999.99) AS "MONTHLY SALARY" FROM
employees WHERE salary < 10000 WHERE last_name IN 'D%'
- SELECT CONCAT(first_name, last_name) AS Fullname FROM employees
Display
the first 5 letter in the surname of all the employees whose firstname starts
with letter 'N'
- SELECT SUBSTR(last_name,1,5), first_name FROM employees WHERE first_name IN
'N'
Austin
David was transferred to Purchasing Department. You are assigned to update the
database.
Which
of the following SQL command will satisfy the requirements?
-
UPDATE employees SET department_id = 30 WHERE first_name = ‘David’ AND
last_name = ‘Austin’
John want to
know how many employees receiving salary below 10,000.
What
SQL command he need to run?
- SELECT COUNT(*) FROM employees WHERE salary < 10000;
Evaluate
the following SQL command
SELECT employee_id, salary, department_id FROM employees WHERE department_id IN
(60,70)
- The SQL command will display employees with department id 60 or 70.
You
want to display the employee's last name and date hired in year 2000 to2006
whose salary is above 5000. Which SQL statement give the required output?
- SELECT last_name, hire_date FROM employees WHERE hire_date>=
TO_DATE('01-Jan-2006', 'DD-Mon-RR') AND hire_date<= TO_DATE('31-Dec-2006',
'DD-Mon-RR') AND salary > 5000;
List
all employees except for IT_PROG job id.
- All of the choices
Evaluate
the following SQL command
SELECT employee_id, min_salary, max_salary FROM employees, departments WHERE
salary>= 10000 && salary <= 20000
- The SQL command will produce an error.
Display
all the records in the employee table. Arrange the output in by lastname from
A-Z order.
- SELECT * FROM employees ORDER BY lastname
Display
employee's name and id whose firstname starts with letter D and job id is
IT_PROG.
Sort
the output by department.
- SELECT employee_id, first_name, last_name FROM employees WHERE first_name
LIKE 'D%' and job_id = 'IT_PROG' ORDER BY department_id
You what to
generate the average salary of all employees whose job function is
FI_ACCOUNT.
Which
of the following SQL command will produce the output.
- SELECT AVG(salary) FROM employees WHERE job_id = 'FI_ACCOUNT';
Display
all location id between 1000 to 2000.
- SELECT location_id FROM departments WHERE location_id BETWEEN 1000 AND 2000
What
will be the SQL command if every employee will be given a productivity bonus
which is equivalent to 3% of the monthly salary?
Display the employee id, salary and the productivity bonus.
-ELECT employee_id, salary, salary * .03 FROM employees
Display
the employee id, salary, number of years and the hiring date of every employee
in the company
- SELECT employee_id, salary, hire_date, ROUND((SYSDATE - hire_date) /365,0)
FROM employees;
Display
the employee's name, job title, job_id and the department name of employees
with department id of 100.
- SELECT E.employee_id, J.job_title, e.job_id, D.department_name
FROM employees E
JOIN jobs J ON E.job_id = J.job_id
JOIN departments D ON E.department_id = D.department_id
WHERE E.department_id = 100
In
creating Joins,the NATURALJOIN and USING clauses are____________
- Mutually Exclusive
Display
employee id, lastname, firstname, department name, annual salary, location id
of employees whose location id is 1700 and working in Finance Department. Label
the annual salary to ANNUAL SALARY.
Sort
the output in from highest to lowest salary. (4 POINTS)
-SELECT E.employee_id, E.last_name, E.first_name, D.department_name,
E.salary*12 AS "ANNUAL SALARY", D.location_id
FROM employees E
JOIN jobs J ON E.job_id = J.job_id
JOIN departments D ON E.department_id = D.department_id
WHERE D.location_id = 1700 AND D.department_name = 'Finance'
ORDER BY "ANNUAL SALARY" DESC
LA2
Display the
name, jobs id and salary of the all the employees whose department id is 100 and
salary is below 8000. Arrange the output by salary in ascending order.
SELECT
first_name, last_name, salary FROM employees WHERE department_id = 100 AND
salary < 8000 ORDER BY salary
Display
employee's name and id whose firstname starts with letter D and job id is
SA_REP. Sort the output by department.
SELECT
employee_id, first_name, last_name FROM employees WHERE first_name LIKE 'D%'
and job_id = 'IT_PROG' ORDER BY department_id
List all the
employee_id of all employees whose salary is 5000 and below and belong to
department 60 or 100.
SELECT
employee_id,salary, department_id FROM employees WHERE salary < 5000 AND
department_id IN (60,100)
SQ2
Display all
the records sorted by price from most expensive to the cheapest parts.
SELECT * FROM
parts ORDER BY price DESC
Display all
the records whose stock is below 20 and in warehouse number 3.
SELECT * FROM
parts WHERE onhand< 20 AND warehouse = 3;
Ronnie is the
stockman in the warehouse of ATR Corporation. The General Manager wants to know
the parts whose price is above 10000 and above. Which of the following SQL
command that Ronnie will run to generate the list.
SELECT * FROM
parts WHERE price >= 10000;
Which of the
following SQL command will display all records with class code of AP?
SELECT * FROM
parts WHERE class = ‘AP’;
Which of the
following SQL commands will display all stocks whose class is HW or AP.
SELECT * FROM
parts WHERE IN class ('HW', 'AP');
LA3
Display the
first 5 letter in the surname of all the employees whose firstname starts with
letter 'D'
SELECT
SUBSTR(last_name,1,5), first_name FROM employees WHERE SUBSTR(first_name,1,1) =
'D'
Display the
employee id, number of years and the hiring date of every employee in the
company.
SELECT
employee_id, hire_date, ROUND((SYSDATE - hire_date) /365,0) FROM employees;
Every
employee will get a bonus of 150% of his/her current salary. Display the
employee id, salary and the bonus of every employee. Label the computed bonus
with Bonus
The correct
answer is: SELECT employee_id, salary, salary * 1.5 AS Bonus FROM employees
SQ3
Display the
montly salary of every employee. Round the salary in 2 decimal places.
SELECT ROUND(
(salary/12),2 ) FROM employees;
Display the
total number of characters of the last name of all the employees.
SELECT
LENGTH(last_name) FROM employees;
Display the
first 3 letter in the first name of all the employees.
SELECT
SUBSTR(first_name,1,3) FROM employees;
Display the
last day of the month and the hiring date when the employees are hired in the
company.
SELECT
LAST_DAY(hire_date), hire_date FROM employees;
LA4
You want to
display the employee id and the year when an employee was hired.
Which SQL
statement give the required output?
SELECT
employee_id, TO_CHAR(hire_date,'YYYY') FROM employees;
You want to
display the employee id and the month an employee was hired.
Which SQL
statement give the required output?
SELECT
employee_id, hire_date, TO_CHAR(hire_date,'Month') AS "Hired Month"
FROM employees;
You want to
display the employee's last name whose salary is below 10,000.
Which SQL
statement give the required output format of the salary?
Required
output :
SELECT
last_name, TO_CHAR(salary, '$999,999.99') AS "MONTHLY SALARY" FROM
employees WHERE salary < 10000
SQ4
You want to
display the last name and the year when an employee was hired whose job id is
IT_PROG.
Which SQL
statement give the required output?
SELECT
last_name, TO_CHAR(hire_date,'YYYY') FROM employees WHERE job_id = ‘IT_PROG’;
You want to
display all the employee id and the month an employee was hired excluding
employees whose job id is AD_VP. Which SQL statement give the required output?
SELECT
employee_id, hire_date, TO_CHAR(hire_date,'Month') AS "Hired Month",
job_id FROM employees WHERE job_id NOT IN ('AD_VP');
You want to
display the employee's id and formatted date hired as shown below.
Which SQL
statement give the required output?
Required
output :
SELECT
employee_id, TO_CHAR(hire_date, 'fmMonth DD, YYYY') AS "Hired Date"
FROM employees;
LA5
The General
Manager request to the Database Administrator to generate the total salary per
month of every department in the company.
SELECT
department_id, SUM(salary) FROM employees GROUP BY department_idMs. Ella what
to generate the average salary of all employees whose job function is IT_PROG.
Which of the
following SQL command will produce the output.
SELECT
AVG(salary) FROM employees WHERE job_id = 'IT_PROG';
Aldrin wants
to know the highest salary in every department. Which of the following SQL
command will display the required output?
SELECT
department_id, MAX(salary) FROM employees GROUP BY department_id
What is the
SQL command to display the date of the first employee that was hired?
SELECT
MIN(hire_date) FROM employees;
John want to
know how many employees receiving salary below 10,000. What SQL command he need
to run?
SELECT
COUNT(*) FROM employees WHERE salary < 10000;
LQ1
John want to
know how many part items are there in warehouse number 3.
What SQL
command he need to run?
SELECT
COUNT(*) FROM parts WHERE warehouse = 3;
Which of the
following SQL command will display all records with part number contains the
number 9?
SELECT * from
parts WHERE partnum LIKE '%9%'
What will be
the output of the following SQL?
SELECT * FROM
parts WHERE (warehouse = 1 or warehouse = 2) AND class IN ('HW', 'AP') AND
(price > 200 AND price < 500);
2 rows
returned
What will be
the output of the SQL command?
SELECT
SUM(onhand) FROM PARTS where class = 'HW' OR class = 'AP' AND warehouse = 1;
137
There was 10%
price increase in the all the parts in warehouse number 3. The Store Manager
asked the Database Administrator to generate a report showing the part number,
the old and new price.
Which of the
following SQL statement would satisfy the requirement of the Store Manager.
SELECT
partnum, price, price * 1.1 FROM parts WHERE warehouse = 3
Which of the
following SQL command will display the summary table showing the total quantity
on hand per class.
SELECT class,
sum(onhand) AS "QTY ON HAND" FROM parts GROUP BY class
Aldrin wants
to know the outstanding total balance on hand on every class per warehouse.
SELECT
warehouse, class, sum(onhand) FROM parts GROUP BY warehouse, class
LA6
The HR
Manager instruct the Database Officer to display employee's name and the
minimum and maximum salary the employee can received. Which of the following
SQL command will generate the output?
SELECT
first_name, last_name, job_id, min_salary, max_salary FROM employees JOIN jobs
USING (job_id);
What will be
the output of the SQL command below?
SELECT
first_name, last_name, job_title FROM employees CROSS JOIN jobs ORDER BY
last_name;
A
Cartesian product
What will be
the output of the SQL command below?
SELECT
last_name, job_id, job_title FROM employees NATURAL JOIN jobs
List of names
and job title of matched records from the employees and jobs table
SQ6
Given the SQL
command
SELECT * FROM
employees JOIN departments USING (department_id)
Which of the
following describes the SQL command?
Joined table
from the employees and department table
Display the
location id of all employee's name and salary whose salary is from 5000 to
10000.
SELECT
first_name, last_name, salary FROM employees JOIN departments USING
(department_id) WHERE salary >= 5000 AND salary <= 10000
Display
the manager id and employee id of every employee
SELECT
E.employee_id, D.manager_id FROM employees E JOIN departments D ON
E.department_id = D.department_id
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?
List of
employees name, salary and department name
LA7
Remove the
Shareholder Services department in the department table
DELETE FROM
departments WHERE department_name = ‘Shareholder Services’
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.
INSERT INTO
departments VALUES (300, 'Training', 203, 2400)
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.
INSERT INTO
jobs VALUES ('DB_ADMIN', 'Database Administrator', 10000, 18000)
Remove all
Manager positions in the department table.
DELETE FROM
jobs WHERE job_title LIKE '%Manager%';
SQ7
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.
INSERT INTO
jobs VALUES ('JAVA_DEV', 'Java Developer', 7000, 8000)
Add a 500
pesos increase in salary of all employees who have rendered services 10 years
and above.
UPDATE
employees SET salary = salary + 500 where TO_CHAR(sysdate,'YYYY') -
TO_CHAR(hire_date,'YYYY') >= 10
Which of the
following DOES NOT describes the state of the data after the COMMIT command
None of the
choices
Which of the
following describes the command below?
SELECT * FROM
employees WHERE department = 60 FOR UPDATE
Locks the
rows in the EMPLOYEES table with department id is 60.
Which of the
following will erase all records in the departments table
TRUNCATE
TABLE departments
SQL command
to create a marker in the current transaction.
SAVEPOINT
Nathaniel had
accidentally deleted all the records in the newly updated ORACLE database using
the DELETE SQL command. What is the best solution that he can do to restore all
the deleted records in the database.
Run the
ROLLBACK command
LA8
Which of the
following will grant a query privileges on the STUDENT table
GRANT select
ON student TO matt
You want to
cancel the privilege of matt to add records from the employees table.
REVOKE insert
ON employees FROM matt;
This has the
highest level of privileges for task such as creating new users, removing users
and tables and backing up tables.
DBA
SQ8
TRUE OR
FALSE.
An owner has
all the privileges on the object.
True
Which of the
following provide privilege to update the employees table?
GRANT update
(salary) ON employees TO matt
Which of the
following will is the correct command to create a role.
CREATE ROLE
gen_manager
Matt wants to
change his password from 1234 to abcd.
Which of the
following will perform the task?
ALTER USER
matt IDENTIFIED abcd;
The DBA will
create a new user name sales.
Which of the
following SQL command will perform the creation?
CREATE sales
ACCESS 1234
Which of the
following SQL command that the DBA will run to provide Matt to create a table
in the Oracle Database.
GRANT create
table TO matt
What
privileges that manipulates the content of the database objects.
Object
Privileges
1st Q Exam
Evaluate the
following SQL command
SELECT
employee_id, salary, department_id FROM employees WHERE department_id IN
(60,70)
The SQL
command will display employees with department id 60 or 70.
What will be
the SQL command if every employee will be given a productivity bonus which is
equivalent to 3% of the monthly salary?
Display the
employee id, salary and the productivity bonus.
SELECT
employee_id, salary, salary * .03 FROM employees
Display the
employee id, salary, number of years and the hiring date of every employee in
the company.
SELECT
employee_id, salary, hire_date, ROUND((SYSDATE - hire_date) /365,0) FROM
employees;
You what to
generate the average salary of all employees whose job function is FI_ACCOUNT.
Which of the
following SQL command will produce the output.
SELECT
AVG(salary) FROM employees WHERE job_id = 'FI_ACCOUNT';
List all
employees except for IT_PROG job id.
All of the
choices
Austin David
was transferred to Purchasing Department. You are assigned to update the
database.
Which of the
following SQL command will satisfy the requirements?
UPDATE
employees SET department_id = 30 WHERE first_name = ‘David’ AND last_name =
‘Austin’
Display the
lastname of every employee in the company. Display the output in a single
column and label it as Fullname
Format:
JuanReyes
SELECT
CONCAT(first_name, last_name) AS Fullname FROM employees
Display all
the records in the employee table. Arrange the output in by lastname from A-Z
order.
SELECT * FROM
employees ORDER BY lastname
You want to
generate the total salary per month of every department in the company.
SELECT
department_id, SUM(salary) FROM employees GROUP BY department_id
You want to
display the employee's last name whose salary is below 10,000 and whose
lastname starts with letter D.
Which SQL
statement give the required output format of the salary?
SELECT
last_name, TO_INTEGER(salary, $999,999.99) AS "MONTHLY SALARY" FROM
employees WHERE salary < 10000 WHERE last_name IN In creating Joins,the
NATURALJOIN and USING clauses are____________
Mutually
Exclusive
Display
employee id, lastname, firstname, department name, annual salary, location id
of employees whose location id is 1700 and working in Finance Department. Label
the annual salary to ANNUAL SALARY.
Sort the
output in from highest to lowest salary. (4 POINTS)
SELECT
E.employee_id, E.last_name, E.first_name, D.department_name, E.salary*12 AS
"ANNUAL SALARY", D.location_id
FROM
employees E
JOIN jobs J
ON E.job_id = J.job_id
JOIN
departments D ON E.department_id = D.department_id
WHERE
D.location_id = 1700 AND D.department_name = 'Finance'
ORDER BY
"ANNUAL SALARY" DESC
Which of the following does NOT describes SELECT Statement in a PL/SQL.
Answer: Queries must return only one column.
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)
Which of the following stored procedure to create a procedure to that will be
used to display the employee id and salary of Steven King?
Answer: CREATE OR REPLACE PROCEDURE query_emp
(p_last_name IN employees.last_name%TYPE,
p_first_name IN employees.first_name%TYPE,
p_employee_id OUT employees.employee_id%TYPE,
p_salary OUT employees.salary%TYPE) IS
BEGIN
SELECT employee_id, salary INTO p_employee_id, p_salary
FROM employees
WHERE last_name = p_last_name AND first_name = p_first_name;
END query_emp;
Which of the folllowing is TRUE?
Answer: SQL code are embedded within PL/SQL statements
When an exception is user defined, the exception is raised ____________ .
Answer: Explicitly
Which of the folllowing is TRUE?
Answer: SQL code are embedded withing PL/SQL statements
Weak REF CURSOR is very flexible.
Answer: True
How do you test the output of a PL/SQL block?
Answer: Use a predefined Oracle package and its procedure
Restrictive, specifies a RETURN type, associates only with type-compatible
queries are description of a ________________.
Answer: Strong REF CURSOR
Which of the following DOES NOT describes an exception?
Answer: Exception is a PL/SQL error that is raised before program execution.
These are local variables declared in the parameter list of a subprogram
specification.
Answer: Formal parameter
Which of the following rules is INCORRECT about cursor variables?
Answer: None of the choices.
When an exception is predefined by Oracle server, the exception is raised ____________
.
Answer: Explicitly
Which of the following DOES NOT describes an exception?
Answer: Exception is a PL/SQL error that is raised before program execution.
Evaluate the following PL/SQL.
CREATE OR REPLACE PROCEDURE query_employee
(p_id IN employees.employee_id%TYPE,
p_name OUT employees.last_name%TYPE,
p_salary OUT employees.salary%TYPE) IS
BEGIN SELECT last_name, salary INTO p_name, p_salary
FROM employeesWHERE employee_id = p_id;
END query_employee
Answer: No error
You want to know the total number of employees whose firstname starts with
letter D.
Which of the folllowing PLS/SQL executes successfully?
Answer: DECLARE
v_first_name employees.first_name%TYPE := 'D%';
BEGIN
SELECT COUNT(*) INTO v_first_name FROM employees WHERE first_name LIKE v_first_name;
DBMS_OUTPUT.PUT_LINE(v_first_name);
END;
What is the error trapping function that returns the numeric value of the error
code?
Answer: SQLCODE
You want to display the name, salary and tax of employee #150. Which of the
PL/SQL will execute successfully? Note tax is computed as 2% of the salary.
Answer: DECLARE
v_first_name VARCHAR2(50);
v_last_name VARCHAR2(50);
v_salary INTEGER(20);
v_tax INTEGER(10);
BEGIN
SELECT first_name, last_name, salary, salary * 0.02 INTO v_first_name,
v_last_name, v_salary, v_tax FROM employees WHERE employee_id = 150;
DBMS_OUTPUT.PUT_LINE('Firstname : '|| v_first_name);
DBMS_OUTPUT.PUT_LINE('Lastname : '|| v_last_name);
DBMS_OUTPUT.PUT_LINE('Salary : '|| v_salary);
DBMS_OUTPUT.PUT_LINE('Tax : '|| v_tax);
END;
Which of the following is the syntax to close a cursor?
Answer: CLOSE cursor_variable_name;
Which of the following rules is INCORRECT about cursor variables?
Answer: None of the choices.
What are the three PL/SQL block types?
Answer: Anonymous, Procedure, Function
You can trap any error by including a corresponding handler within the
exception-handling section of the PL/SQL block.
Answer: True
Which of the following describes weak REF CURSOR?
Answer: Associates with any query
Given the answer in item __________, which of the folllowing stored procedure
will display the employee id and salary of Steven King?
Answer: DECLARE
v_employee_id employees.employee_id%TYPE;
v_emp_sal employees.salary%TYPE;
BEGIN
query_emp('King', 'Steven', v_employee_id, v_emp_sal);
DBMS_OUTPUT.PUT_LINE('Employee ID ' || v_employee_id ||' earns '||
to_char(v_emp_sal, '$999,999.00'));
END;
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;
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
In the DECLARE section of the PL/SQL block,
Answer: All of the choices
Actions are being performed when error occurs during PL/SQL execution in the
Answer: EXCEPTION
What is the exception name when PL/SQL has an internal problem
Answer: PROGRAM_ERROR
Which of the folllowing statement describes PL/SQL?
Answer: PL/SQL is an Oracle proprietary, procedural, 3GL programming language
Evaluate the following PL/SQL.
DECLARE
v_employee_id employees.employee_id%TYPE := 114;
BEGIN
DELETE employees WHERE employee_id = v_employee_id;
END;
Answer: The PL/SQL will delete employee number 114.
Which of the following command is used to create a stand-alone procedure that
is stored in the Oracle database?
Answer: CREATE PROCEDURE
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.
Which of the following is the syntax to define a REF CURSOR type?
Answer: TYPE ref_type_name IS REF CURSOR
[RETURN return_type];
The PL/SQL code block helps modularize code by using:
Answer: All of the choices
Which of the following is the syntax to fetch from a cursor variable?
Answer: FETCH cursor_variable_name INTO variable_name1
[,variable_name2,. . .] | record_name;
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')
Procedure can be stored in the database as a schema object.
Answer: True
In PL/SQL Block Structure, which of the following are mandatory?
Answer: BEGIN and END
PL/SQL stands for
Answer: Procedural Language extension to SQL
Which of the following is CORRECT about sub-queries?
Answer: Subquery execute before the main query executes.
Which of the following does NOT describes SELECT Statement in a PL/SQL.
Answer: Queries must return only one column.
PL/SQL Provides a block structure for executable units of ________________.
Answer: Code
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.
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;
In PL/SQL Block Structure, which of the following are OPTIONAL?
Answer: None of the choices
Evaluate the following PL/SQL. At what line number is the error of the PL/SQL?
DECLARE
v_deptno NUMBER := 800;
e_invalid EXCEPTION;
BEGIN
DELETE FROM departments
WHERE department_id = v_deptno;
IF SQL % NOT_FOUND THEN
RAISE e_invalid;
END IF;
COMMIT;
EXCEPTION
WHEN e_invalid THEN
DBMS_OUTPUT.PUT_LINE('No such department id.');
END;
Answer: 7
What is the error trapping function that returns the numeric value of the error
code?
Answer: SQLCODE
Fetch into a record when fetching from a cursor.
Answer: True
Which
of the following is the syntax to open a cursor varial
Answer: OPEN cursor_variable_name
FOR select_statement;
Which of the following is the syntax to define a REF CURSOR type?
Answer: TYPE ref_type_name IS REF CURSOR
[RETURN return_type];
Weak REF CURSOR is very flexible.
Answer: True
Evaluate the following. What will be the output?
DECLARE
SUBTYPE Accumulator IS NUMBER (4,2);
v_amount accumulator;
v_num1 NUMBER;
v_num2 NUMBER;
v_num3 NUMBER;
BEGIN
v_amount := 10.50;
v_num1 := 1;
v_num2 := 2;
v_num3 := 3;
v_num1 := v_amount;
v_num2 := v_num1 + v_amount;
v_num2 := v_num2 - v_num3;
dbms_output.put_line('Total is: ' || v_num2);
END;
Answer: 18
Which of the following is the syntax to fetch from a cursor variable?
Answer: FETCH cursor_variable_name INTO variable_name1
[,variable_name2,. . .] | record_name;
Fetch into a record when fetching from a cursor.
Answer: True
Which of the following is the syntax to close a cursor?
Answer: CLOSE cursor_variable_name;
This is a subset of an existing data type that may place a constraint on its
base type.
Answer: Subtype
Which of the following rules is INCORRECT about cursor variables?
Answer: None of the choices.
Use column aliases in cursors for calculated columns fetched into records
declared with %COLUMNTYPE.
Answer: False
PROG-113A
/ Week 18: Designing PL/SQL / Learning Activity 14
Which
of the following rules is INCORRECT about cursor variables?
Answer: None of the choices.
Which of the following describes weak REF CURSOR?
Answer: Associates with any query
Restrictive, specifies a RETURN type, associates only with type-compatible
queries are description of a ________________.
Answer: Strong REF CURSOR
Which of the following is INCORRECT about the guidelines for cursor design?
Answer: Use column aliases in cursors for calculated columns fetched into
records declared with %COLUMNTYPE.
This is a subset of an existing data type that may place a constraint on its
base type.
Answer: Subtype
PROG-113A
/Week 17: / Short Quiz 13
Evaluate the following PL/SQL.
CREATE OR REPLACE PROCEDURE query_employee
(p_id IN employees.employee_id%TYPE,
p_name OUT employees.last_name%TYPE,
p_salary OUT employees.salary%TYPE) IS
BEGIN SELECT last_name, salary INTO p_name, p_salary
FROM employeesWHERE employee_id = p_id;
END query_employee
Answer: No error
Evaluate the following PL/SQL. Which of the following will line creates an
error?
CREATE OR REPLACE PROCEDURE query_emp
(
p_department_id IN employees.department_id%TYPE,
p_name OUT employees.last_name%TYPE,
p_salary OUT employees.salary%TYPE
)
IS
BEGIN
SELECT last_name, salary, department_id INTO p_name, p_salary, p_department_id
FROM employees
WHERE salary >= p_salary AND department_id = p_department_id ;
END query_emp;
Answer: Line 3
These are local variables declared in the parameter list of a subprogram
specification.
Answer: Formal parameter
Given the answer in item __________, which of the folllowing stored procedure will
display the employee id and salary of Steven King?
Answer: DECLARE
v_employee_id employees.employee_id%TYPE;
v_emp_sal employees.salary%TYPE;
BEGIN
query_emp('King', 'Steven', v_employee_id, v_emp_sal);
DBMS_OUTPUT.PUT_LINE('Employee ID ' || v_employee_id ||' earns '||
to_char(v_emp_sal, '$999,999.00'));
END;
Which
of the following stored procedure to create a procedure to that will be used to
display the employee id and salary of Steven King?
Answer: CREATE OR REPLACE PROCEDURE query_emp
(p_last_name IN employees.last_name%TYPE,
p_first_name IN employees.first_name%TYPE,
p_employee_id OUT employees.employee_id%TYPE,
p_salary OUT employees.salary%TYPE) IS
BEGIN
SELECT employee_id, salary INTO p_employee_id, p_salary
FROM employees
WHERE last_name = p_last_name AND first_name = p_first_name;
END query_emp;
What is the default parameter mode when no mode is specified?
Answer: IN
PROG-113A
/ ► Week
16: Creating Procedures / ► Learning
Activity 13
Procedure can be stored in the database as a schema object.
Answer: True
The PL/SQL code block helps modularize code by using:
Answer: All of the choices
Which of the following command is used to create a stand-alone procedure that
is stored in the Oracle database?
Answer: CREATE PROCEDURE
Which of the folllowing does NOT describes subprogram?
i. Compiled only once
ii. Stored in the database
iii. Do not return values
iv. Can take parameters
v. Unnamed PL/SQL blocks
Answer: iii & v
Which if the following is NOT a benefits of using modular program constructs?
Answer: None of the choices
PROG-113A
/ ► Week
18: Designing PL/SQL / ► Short
Quiz 14
Which of the following is the syntax to open a cursor varial
Answer: OPEN cursor_variable_name
FOR select_statement;
Which of the following is the syntax to define a REF CURSOR type?
Answer: TYPE ref_type_name IS REF CURSOR
[RETURN return_type];
Weak REF CURSOR is very flexible.
Answer: True
Evaluate the following. What will be the output?
DECLARE
SUBTYPE Accumulator IS NUMBER (4,2);
v_amount accumulator;
v_num1 NUMBER;
v_num2 NUMBER;
v_num3 NUMBER;
BEGIN
v_amount := 10.50;
v_num1 := 1;
v_num2 := 2;
v_num3 := 3;
v_num1 := v_amount;
v_num2 := v_num1 + v_amount;
v_num2 := v_num2 - v_num3;
dbms_output.put_line('Total is: ' || v_num2);
END;
Answer: 18
Which of the following is the syntax to fetch from a cursor variable?
Answer: FETCH cursor_variable_name INTO variable_name1
[,variable_name2,. . .] | record_name;
Fetch into a record when fetching from a cursor.
Answer: True
Which of the following is the syntax to close a cursor?
Answer: CLOSE cursor_variable_name;
This is a subset of an existing data type that may place a constraint on its
base type.
Answer: Subtype
Which of the following rules is INCORRECT about cursor variables?
Answer: None of the choices.
Use column aliases in cursors for calculated columns fetched into records
declared with %COLUMNTYPE.
Answer: False
PROG-113A
/ ► Week
18: Designing PL/SQL / ► Learning
Activity 14
Which
of the following rules is INCORRECT about cursor variables?
Answer: None of the choices.
Which of the following describes weak REF CURSOR?
Answer: Associates with any query
Restrictive, specifies a RETURN type, associates only with type-compatible
queries are description of a ________________.
Answer: Strong REF CURSOR
Which of the following is INCORRECT about the guidelines for cursor design?
Answer: Use column aliases in cursors for calculated columns fetched into
records declared with %COLUMNTYPE.
This is a subset of an existing data type that may place a constraint on its
base type.
Answer: Subtype
PROG-113A
/ ► Week
15: Long Quiz / ► Long
Quiz 3 (30/30)
(Correction)
How do you test the output of a PL/SQL block?
Answer: Use a predefined Oracle package and its procedure
Which of the folllowing statement describes PL/SQL?
Answer: PL/SQL is an Oracle proprietary, procedural, 3GL programming language
Which of the following syntax to declare EXCEPTION named e_invalid_id?
Answer: e_invalid_id EXCEPTION;
In PL/SQL Block Structure, which of the following are mandatory?
Answer: BEGIN and END
This is a type of cursor which is created and managed internally by the Oracle
server to process SQL statements
Answer: Implicit
PL/SQL stands for
Answer: Procedural Language extension to SQL
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;
What is the last clause in trapping exceptions?
Answer: WHEN OTHERS
PL/SQL Provides a block structure for executable units of ________________.
Answer: Code
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.
Which of the folllowing is TRUE?
Answer: SQL code are embedded within PL/SQL statements
You can trap any error by including a corresponding handler within the
exception-handling section of the PL/SQL block.
Answer: True
When an exception is user defined, the exception is raised ____________ .
Answer: Explicitly
Evaluate the PL/SQL
DECLARE
v_first_name VARCHAR2(50);
v_last_name VARCHAR2(50);
v_salary INTEGER(20);
BEGIN
SELECT first_name, last_name, salary INTO v_first_name, v_last_name, v_salary
FROM employees WHERE department_id = 60;
DBMS_OUTPUT.PUT_LINE('Firstname : '|| v_first_name);
DBMS_OUTPUT.PUT_LINE('Lastname : '|| v_last_name);
DBMS_OUTPUT.PUT_LINE('Salary : '|| v_salary);
END;
Answer: Error in Line 6.
Evaluate the following PL/SQL.
DECLARE
v_employee_id employees.employee_id%TYPE := 114;
BEGIN
DELETE employees WHERE employee_id = v_employee_id;
END;
Answer: The PL/SQL will delete employee number 114.
Which of the following does NOT describes SELECT Statement in a PL/SQL.
Answer: Queries must return only one column.
What are the three PL/SQL block types?
Answer: Anonymous, Procedure, Function
What is the exception name when PL/SQL has an internal problem
Answer: PROGRAM_ERROR
You have been tasked to update the database by creating a PL/SQL to increase
the salary of all IT Programmer employees by 100% 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;
What is the error trapping function that returns the numeric value of the error
code?
Answer: SQLCODE
What is the exception name when single row SELECT returned no data.
Answer: NO_DATA_FOUND
Which of the following DOES NOT describes an exception?
Answer: Exception is a PL/SQL error that is raised before program execution.
Evaluate the following PL/SQL.
DECLARE
v_employee_id employees.employee_id%TYPE := 114;
BEGIN
DELETE employees WHERE employee_id = v_employee_id;
END;
Answer: The PL/SQL will delete employee number 114.
In the DECLARE section of the PL/SQL block,
Answer: All of the choices
When an exception is predefined by Oracle server, the exception is raised
____________ .
Answer: Explicitly
Which of the will display the Employee ID and number of years in service with
employee ID 150?
Answer: DECLARE
v_salaryemployees.salary%TYPE := 10000;
v_employee_idemployees.employee_id%TYPE := 150;
v_years INTEGER(10);
BEGIN
SELECT employee_id, ROUND((SYSDATE - hire_date) /365,0) INTO v_employee_id,
v_years FROM employees WHERE employee_id = v_employee_id ;
DBMS_OUTPUT.PUT_LINE('Employee ID:' || v_employee_id);
DBMS_OUTPUT.PUT_LINE('Number of years : ' || v_years);
END;
Actions are being performed when error occurs during PL/SQL execution in the
Answer: EXCEPTION
You can use this procedure to issue user-defined error messages from stored
subprograms.
Answer: RAISE_APPLICATION_ERROR
PROG-113A
/ ► Week
14: Handling Exceptions / ► Short
Quiz 12
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.
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 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
What is the error trapping function that returns the numeric value of the error
code?
Answer: SQLCODE
What is the exception name when PL/SQL has an internal problem
Answer: PROGRAM_ERROR
Evaluate the following PL/SQL. At what line number is the error of the PL/SQL?
DECLARE
v_deptno NUMBER := 800;
e_invalid EXCEPTION;
BEGIN
DELETE FROM departments
WHERE department_id = v_deptno;
IF SQL % NOT_FOUND THEN
RAISE e_invalid;
END IF;
COMMIT;
EXCEPTION
WHEN e_invalid THEN
DBMS_OUTPUT.PUT_LINE('No such department id.');
END;
Answer: 7
What is the last clause in trapping exceptions?
Answer: WHEN OTHERS
Complete the diagram in Trapping Non-Predefined Oracle Server Errors.
Answer: Declare, Associate, Reference
PROG-113A
/ ► Week
14: Handling Exceptions / ► Learning
Activity 12
You
can use this procedure to issue user-defined error messages from stored
subprograms.
Answer: RAISE_APPLICATION_ERROR
Which of the following syntax to declare EXCEPTION named e_invalid_id?
Answer: e_invalid_id EXCEPTION;
When an exception is predefined by Oracle server, the exception is raised
____________ .
Answer: Explicitly
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
PROG-113A
/ ► Week
13: Interacting with Oracle Server / ► Learning Activity 11
Which
of the following does NOT describes SELECT Statement in a PL/SQL.
Answer: Queries must return only one column.
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 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;
This is a type of cursor which is created and managed internally by the Oracle
server to process SQL statements
Answer: Implicit
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;
PROG-113A
/ ► Week
12: Introduction to PLSQL / ► Short
Quiz 10
Which
of the folllowing is TRUE?
Answer: SQL code are embedded withing PL/SQL statements
In the DECLARE section of the PL/SQL block
Answer: All of the choices
In PL/SQL Block Structure, which of the following are OPTIONAL?
Answer: None of the choices
What are the three PL/SQL block types?
Answer: Anonymous, Procedure, Function
How do you test the output of a PL/SQL block?
Answer: Use a predefined Oracle package and its procedure
You want to display the name, salary and tax of employee #150. Which of the
PL/SQL will execute successfully? Note tax is computed as 2% of the salary.
Answer: DECLARE
v_first_name VARCHAR2(50);
v_last_name VARCHAR2(50);
v_salary INTEGER(20);
v_tax INTEGER(10);
BEGIN
SELECT first_name, last_name, salary, salary * 0.02 INTO v_first_name,
v_last_name, v_salary, v_tax FROM employees WHERE employee_id = 150;
DBMS_OUTPUT.PUT_LINE('Firstname : '|| v_first_name);
DBMS_OUTPUT.PUT_LINE('Lastname : '|| v_last_name);
DBMS_OUTPUT.PUT_LINE('Salary : '|| v_salary);
DBMS_OUTPUT.PUT_LINE('Tax : '|| v_tax);
END;
Which of the folllowing statement describes PL/SQL?
Answer: PL/SQL is an Oracle proprietary, procedural, 3GL programming language
PROG-113A
/ ► Week
12: Introduction to PLSQL / ► Learning
Activity 10
PL/SQL stands for
Answer: Procedural Language extension to SQL
PL/SQL Provides a block structure for executable units of ________________.
Answer: Code
In PL/SQL Block Structure, which of the following are mandatory?
Answer: BEGIN and END
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;
Actions are being performed when error occurs during PL/SQL execution in the
Answer: EXCEPTION
PROG-113A
/ ► Week
11: Using Subqueries to Solve Queries / ► Short Quiz 9
You
want to display the department name the same with the location of the
Purchasing department.
Answer: SELECT department_name from departments where location_id = (SELECT
location_id from departments where department_name = 'Purchasing')
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.
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')
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.
You
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)
PROG-113A
/ ► Week
11: Using Subqueries to Solve Queries / ► Learning Activity 9
Which of the following is INCORRECT?
Answer: Use single-row operators with multiple-row subqueries
Which of the folllowing is required in a subquery?
Answer: SELECT
Which of the following is CORRECT about sub-queries?
Answer: Subquery execute before the main query executes.
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.
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