PL/SQL Quizz Section #1

SECTION 1: FUNDAMENTALS

---

1.01. Introduction to PL/SQL

1.  You can create a Web site application written entirely in PL/SQL. True or False?
♦True (*)
♦False

---

2.  Procedural constructs give you better control of your SQL statements and their execution. True or False?
♦True (*)
♦False

---

3.  PL/SQL differs from C and Java in which of the following ways? (Choose two.)
♦It requires an Oracle database or tool. (*)
♦It does not support object-oriented programming.
♦It is the most efficient language to use with an Oracle database. (*)
♦It is the most complex programming language to learn.
♦It is not portable to other operating systems.

---

4.  Which of the following can be done using PL/SQL?
♦Create complex applications.
♦Retrieve and modify data in Oracle database tables.
♦Manage database tasks such as security.
♦Create custom reports.
♦All of the above (*)

---

5.  When multiple SQL statements are combined into PL/SQL blocks, performance improves. True or False?
♦True (*)
♦False

---

6.  Which of the following can be compiled as a standalone program outside the database?
♦A program developed in PL/SQL
♦A program developed in Java
♦A program developed in C
♦All the above
♦Programs developed in Java or C, but not in PL/SQL. (*)

1.02. Benefits of PL/SQL

1.  When multiple SQL statements are combined into PL/SQL blocks, performance improves. True or False?
♦True (*)
♦False

---

2.  Which of the following can be compiled as a standalone program outside the database?
♦A program developed in PL/SQL
♦A program developed in Java
♦A program developed in C
♦All the above
♦Programs developed in Java or C, but not in PL/SQL. (*)

---

3.  Procedural constructs give you better control of your SQL statements and their execution. True or False?
♦True (*)
♦False

---

4.  PL/SQL differs from C and Java in which of the following ways? (Choose two.)
♦It requires an Oracle database or tool. (*)
♦It does not support object-oriented programming.
♦It is the most efficient language to use with an Oracle database. (*)
♦It is the most complex programming language to learn.
♦It is not portable to other operating systems.

---

5.  Which of the following can be done using PL/SQL?
♦Create complex applications.
♦Retrieve and modify data in Oracle database tables.
♦Manage database tasks such as security.
♦Create custom reports.
♦All of the above (*)

---

6.  You can create a Web site application written entirely in PL/SQL. True or False?
♦True (*)
♦False

1.03. Creating PL/SQL blocks

1.  Which of the following is a PL/SQL programming environment?
♦Oracle Cdeveloper
♦Java*Plus
♦PL/SQL Express
♦SQL*Workshop in Application Express (*)

---

2.  Which of the following is NOT a PL/SQL programming environment?
♦Oracle jDeveloper
♦SQL*Plus
♦gSQL*Plus (*)
♦SQL Workshop in Application Express

---

3.  What is wrong with this PL/SQL anonymous block?

BEGIN
DBMS_OUTPUT.PUT_LINE(‘Hello’);
DBMS_OUTPUT.PUT_LINE(‘ and Goodbye’);

♦The Declaration section is missing
♦The Exception section is missing
♦There is nothing wrong with the block, it will work fine.
♦The END; statement is missing (*)

---

4.  How can you display results to check that a PL/SQL block is working correctly?

 ♦You don’t need to do anything, the results will display automatically.
♦Use an Exception section
♦Use DBMS_OUTPUT.PUT_LINE (*)
♦Write a C or Java program to display the results

---

5.  Which sections of a PL/SQL block are optional?
♦Declaration and Executable
♦Declaration and Exception (*)
♦Exception only
♦Executable only

---

6.  What are the characteristics of a PL/SQL stored subprogram? (Choose two.)
♦Named (*)
♦Not stored in the database
♦Can be invoked at any time (*)
♦Do not exist after they are executed

---

7.  Which statements are mandatory in a PL/SQL block? (Choose two.)
♦DECLARE
♦BEGIN (*)
♦EXCEPTION
♦END; (*)

---

8.  In a PL/SQL block, which of the following should not be followed by a semicolon?
♦DECLARE (*)
♦END
♦All SQL statements
♦All PL/SQL statements

---

9.  What are the characteristics of an anonymous block? (Choose two.)
♦Unamed (*)
♦Stored in the database
♦Compiled each time the application is executed (*)
♦Can be declared as procedures or as functions

---

10.  Which lines of code will correctly display the message “Hello World” ? (Choose two.)

DBMS_OUTPUT(‘Hello World’);
DBMS_OUTPUT.PUT_LINE(‘Hello World’); (*)
DBMS_OUTPUT.PUT_LINE(‘Hello’ || ‘World’);
DBMS_OUTPUT.PUT_LINE(‘Hello’ || ‘ ‘ || ‘World’); (*)

1.04. Review of SQL Select Statements

1.  Examine the following statement:

SELECT country_name, population, population*.01
FROM wf_countries;

How would you modify this statement to display “Country”, “Population”, and “Expected Growth” as the column headings?

♦SELECT country_name “COUNTRY”, population “POPULATION”, population*.01 “EXPECTED GROWTH”
FROM wf_countries; (*)
♦SELECT country_name COUNTRY, population POPULATION, population*.01 EXPECTED GROWTH
FROM wf_countries;
♦SELECT country_name ‘COUNTRY’, population ‘POPULATION’, population*.01 ‘EXPECTED GROWTH’
FROM wf_countries;
♦SELECT country_name, population, population*.01
FROM wf_countries
AS “COUNTRY”, “POPULATION”, “EXPECTED GROWTH”;

---

 2.  Which statement would select salaries that are greater than or equal to 2500 and less than or equal to 3500? Choose two correct answers.

♦WHERE salary >= 2500 AND salary <= 3500 (*)
♦WHERE salary = 3500
♦WHERE salary BETWEEN 2500 AND 3500 (*)
♦WHERE BETWEEN salary = 2500 AND salary = 3500

---

 3.  The F_FOOD_ITEMS table contains the FOOD_ITEM_NUMBER and the REGULAR_CODE columns. Which statement would display the FOOD_ITEM_NUMBER joined with the REGULAR_CODE without any space in between them?

♦SELECT food_item_number ‘ ‘ regular_code
FROM f_food_items;
♦SELECT food_item_number UNION regular_code
FROM f_food_items;
♦SELECT food_item_number || regular_code
FROM f_food_items; (*)
♦SELECT food_item_numberregularcode
FROM f_food_items;

---

4.  Which statement would display the departments in the EMPLOYEES table without displaying any duplicates?

♦SELECT ALL department_id
FROM employees;
♦SELECT department_id
FROM employees;
♦SELECT department_id
FROM employees
having ROWID=1;
♦SELECT DISTINCT department_id
FROM employees; (*)

---

5.  If you want to SELECT all the columns of data in a table, you use which of the following symbols?

♦&
♦%
♦$
♦* (*)

---

6.  Which of the following statements displays the population of the Republic of Benin (country_id 229) after a 3 percent growth in its population?

♦SELECT country_name, population*.03
FROM wf_countries
WHERE country_id=229;
♦SELECT country_name, population*1.03
FROM wf_countries
WHERE country_id=229; (*)
♦SELECT country_name, population*30
FROM wf_countries
WHERE country_id=229;
♦SELECT country_name, population+population*.3
FROM wf_countries
WHERE country_id=229;

---

7.  The concatenation operator …

♦Brings columns or character strings together
♦Creates a resultant column that is a character expression
♦Is represented by two vertical bars ( || )
♦All of the above (*)

---

8.  When using the LIKE operator, the “%” and “_” symbols can be used to do a pattern-matching, wild card search. True or False?

♦True (*)
♦False
♦Incorrect.

---

9.  What SQL statement will return the ID, name, and area of all countries in the WF_COUNTRIES table, listed in order of greatest area to least area?

♦SELECT country_id, country_name, area
FROM wf_countries
ORDER BY area DESC; (*)
♦SELECT country_id, country_name, area
FROM wf_countries
ORDER BY area ASC;
♦SELECT country_id, country_name, area
FROM wf_countries
ORDER BY country_name;
♦SELECT country_id, country_name, area
FROM wf_countries
GROUP BY area;

---

10.  What can you use to change the column heading of calculated values in a SQL statement?

♦Multiplication operator
♦Column alias (*)
♦Concatenation operator
♦The DISTINCT keyword

---

11.  Which of the following statements lists each employee’s employee_id, salary, and salary plus a 20 percent bonus?

♦SELECT emp_id, salary, salary*.2
FROM employees;
♦SELECT emp_id, salary, salary*1.2
FROM employees; (*)
♦SELECT emp_id, salary, salary*.8
FROM employees;
♦SELECT emp_id, salary, salary*20
FROM employees;

---

12.  Which of the following statements will generate a sentence such as the following:
The national holiday for United Arab Emirates is Independence Day.
for every country in the WF_COUNTRIES table?

♦SELECT ‘The national holiday for ‘|| country_name || ‘ is ‘ || national_holiday_name
FROM wf_countries;
♦SELECT “The national holiday for “|| country_name || ” is ” || national_holiday_name || “.”
FROM wf_countries;
♦SELECT ‘The national holiday for ‘|| country_name || ‘ is ‘ || national_holiday_name || ‘.’
FROM wf_countries; (*)
♦SELECT ‘The national holiday for || country_name || is || national_holiday_name || .’
FROM wf_countries;

---

13.  Which of the following statements will display a sentence such as the following:
Aruba has an area of 193.
for every country in the WF_COUNTRIES table?

♦SELECT country_name || ‘ has an area of ‘ || area
FROM wf_countries;
♦SELECT country_name || ‘has an area of’ || area
FROM wf_countries;
♦SELECT country_name || ‘ has an area of ‘ || area || ‘.’
FROM wf_countries; (*)
♦SELECT country_name ” has an area of ” area “.”
FROM wf_countries;

---

1.05. Review of SQL Single-row Functions

1.  What is returned by the following statement?
SELECT CONCAT(‘Today is’,’Thursday!’) FROM DUAL;

♦TodayisThursday!
♦Today isThursday! (*)
♦today is thursday!
♦Today is Thursday!
♦Incorrect

---

2.  The following SQL statement will display the value: 456. True or False?
SELECT TRUNC(ROUND(456.98))
FROM dual;

♦True
♦False (*)

---

3.  Which statement returns a user password combining the ID of an employee and the first 4 characters of their last name?

♦SELECT CONCAT (employee_id, SUBSTR(last_name,4,1))
AS “User Passwords”
FROM employees;
♦SELECT CONCAT (employee_id, INSTR(last_name,4,1))
AS “User Passwords”
FROM employees;
♦SELECT CONCAT (employee_id, INSTR(last_name,1,4))
AS “User Passwords”
FROM employees;
♦SELECT CONCAT (employee_id, SUBSTR(last_name,1,4))
AS “User Passwords”
FROM employees; (*)

---

4.  NULL means the same thing as a space or 0 (zero). True or False?

♦True
♦False (*)

---

5.  Which of the following is not a number function?

♦TO_DATE (*)
♦ROUND
♦MOD
♦TRUNC

---

6.  Assume that today is December 31, 2007. What would be the output of the following statement?
SELECT TO_CHAR(SYSDATE, ‘DD/MM/Y’) FROM DUAL;

♦12/31/7
♦31-12-07
♦31/12/2007
♦31/12/7 (*)

---

7.  What does the following SQL SELECT statement return?
SELECT UPPER( SUBSTR(‘Database Programming’, INSTR(‘Database Programming’,’P’),20))
FROM dual;

♦Programming
♦PROGRAMMING (*)
♦Database
♦DATABASE

---

8.  Which SQL statement will display each country’s name with the first letter (only) of each word in uppercase?

♦SELECT UPPER(country_name)
FROM wf_countries;
♦SELECT lower(country_name)
FROM wf_countries;
♦SELECT INITCAP(country_name)
FROM wf_countries; (*)
♦SELECT country_name
FROM wf_countries
ORDER BY INITCAP(country_name);

---

9.  Which function compares two expressions?

♦NVL
♦NULLIF (*)
♦NVL2
♦NULL

---

10.  Assume that today is January 10, 2008. What would be the output of the following statement?
SELECT TO_CHAR(SYSDATE, ‘ddth “of” Month, YYYY’) FROM DUAL;

♦10th of January, 2008 (*)
♦10 January, 2008
♦10-January-2008
♦January 10th, 2008

---

11.  What function would you use to return the highest date in a month?

♦FINAL_DAY
♦END_DAY
♦HIGHEST_DAY
♦LAST_DAY (*)

---

12.  Which query would return a whole number if today’s date is 26-MAY-04?

♦SELECT TRUNC(MONTHS_BETWEEN(SYSDATE,’19-MAR-79′) /12)
AS YEARS
FROM DUAL; (*)
♦SELECT TRUNC(YEARS_BETWEEN(SYSDATE,’19-MAR-79′) /12)
AS YEARS
FROM DUAL;
♦SELECT MONTHS_BETWEEN(SYSDATE,’19-MAR-79′) /12
AS YEARS
FROM DUAL;
♦None of the above