Test: DP Database Programming with SQL Midterm

Exam

Section 1

1. What is a NULL value?

A. A blank space
B. A known value less than zero
C. A perfect zero
D. An unknown value
E. All of the above

2. The order of operator precedence is:

A. - + * /
B. / + - *
C. * - + /
D. * / + -
E. None of the above

3. Examine the following SELECT statement:

SELECT *
FROM employees;

True or False: This statement will retrieve all the rows in the employees table.

A. True
B. False

4. What command will return data from the database to you?

A. FETCH
B. GET
C. RETURN
D. SELECT
E. None of the above

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
1
 5. The basic storage structure in a Relational Database is a _________:
A. Field
B. Key
C. Row
D. Set
E. Table

Section 2

6. Which of the following would be returned by this SELECT statement:

SELECT last_name, salary
FROM employees
WHERE salary < 3500;

A. LAST_NAME SALARY
Davies 3100
B. LAST_NAME SALARY
King 5000
C. LAST_NAME SALARY
Rajas 3500
D. All of the above
E. None of the above

7. How can you write "not equal to" in the WHERE clause?
A. !=
B. ^=
C. <>
D. All of the above
E. None of the above

8. In order to eliminate duplicate rows use the ________ keyword

A. ABSTRACT
B. DISTINCT
C. EXCLUSIVE
D. FIRST_ONLY
E. SINGLES_ONLY

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
2
 9. What does the DISTINCT keyword do when it is used in a SELECT clause?
A. Eliminates all unique values and compares values
B. Eliminates duplicate rows in the result
C. Eliminates only unique rows in the result
D. Hides NULL values
E. All of the above

10. Which of the following WHERE clauses would not select the number 10?
A. WHERE hours <= 10
B. WHERE hours <>10
C. WHERE hours BETWEEN 10 AND 20
D. WHERE hours IN (8,9,10)
E. None of the above

11. Which two statements would select salaries that are greater than or equal to 2500 and less
than or equal to 3500? (Choose two)
A. WHERE salary >= 2500 AND salary <= 3500
B. WHERE salary <=2500 AND salary >= 3500
C. WHERE salary BETWEEN 2500 AND 3500
D. WHERE salary BETWEEN 3500 AND 2500
E. WHERE salary IN 3500 AND 2500

Section 3

12. Evaluate this SQL statement:

SELECT e.employee_id, e.last_name, e.first_name, m.manager_id
FROM employees e, employees m
ORDER BY e.last_name, e.first_name
WHERE e.employee_id = m.manager_id;

This statement fails when executed. Which change will correct the problem?

A. Include a SORT clause

B. Include a SORT IN clause
C. Reorder the clauses in the query
D. Remove the table aliases in the ORDER BY clause
E. Remove the table aliases in the WHERE clause

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
3
13. You need to create a report to display all employees that were hired on or before January 1,
1996. The data should display in this format:
Employee Start Date and Salary
14837 - Smith 10-May-1992 / 5000

Which SELECT statement could you use?

A. SELECT employee_id ||' - '|| last_name "Employee",

hire_date ||' / '|| salary "Start Date and Salary"
FROM employees
WHERE hire_date <= '01-Jan-1996';

B. SELECT employee_id ||' '|| last_name "Employee",

hire_date ||' '|| salary "Start Date and Salary"
FROM employees
WHERE hire_date <= 01-Jan-1996';

C. SELECT employee_id ||' - '|| last_name 'Employee',

hire_date ||' / '|| salary 'Start Date and Salary"
FROM employees
WHERE hire_date <= '01-Jan-1996';

D. SELECT employee_id ||'"- "|| last_name "Employee",

hire_date ||" / "|| salary Start Date and Salary"
FROM employees
WHERE hire_date <= '01-Jan-1996';

E. SELECT employee_id || - || last_name "Employee",

hire_date || / || salary "Start Date and Salary
FROM employees
WHERE hire_date <= '01-Jan-1996';

14. What value will the following SQL statement return?

SELECT employee_id
FROM employees
WHERE employee_id BETWEEN 100 AND 150
OR employee_id IN(119, 175, 205)
AND (employee_id BETWEEN 150 AND 200);

A. 19
B. 20
C. 100, 101, 102, 103, 104, 107, 124, 141, 142, 143, 144, 149
D. 200, 201, 202, 203, 204, 205, 206
E. No rows will be returned
Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
4
 15. Find the clause that will give the same results as:
SELECT *
FROM d_cds
WHERE cd_id NOT IN(90, 91, 92);

A. WHERE cd_id <=90 and cd_id >=92;

B. WHERE cd_id != 90 and cd_id != 91 and cd_id != 92;
C. WHERE cd_id != 90 or cd_id != 91 or cd_id!= 92;
D. WHERE cd_id LIKE (90, 91, 92);
E. WHERE cd_id NOT LIKE (90, 91, 92);

Section 4

16. What is the result of the following SQL Statement:

SELECT ROUND(45.923,-1)
FROM DUAL;

A. 45
B. 45.9
C. 46
D. 50
E. None of the above

17. Which of the following SQL statements will correctly display the last name and the number
of weeks employed for all employees in department 90?
A. SELECT last_name, # of WEEKS
FROM employees
WHERE department_id = 90;
B. SELECT last name, (SYSDATE-hire_date)/7 DISPLAY WEEKS
FROM employees
WHERE department id = 90;
C. SELECT last_name, (SYSDATE-hire_date)/7 AS WEEKS
FROM employees
WHERE department_id = 90;
D. SELECT last_name, (SYSDATE-hire_date)AS WEEK
FROM employees
WHERE department_id = 90;

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
5
 18. What does the following SQL SELECT statement return?
SELECT UPPER( SUBSTR('Database Programming',
INSTR('Database Programming','P'),20))
FROM dual;

A. DATABASE
B. Database
C. Database PROGRAMMING
D. PROGRAMMING
E. Programming

19. Which character manipulation function always returns a numerical value?

A. LENGTH
B. LPAD
C. RPAD
D. SUBSTR
E. TRIM

Section 5

20. Which of the following is a conditional expression used in SQL?

A. CASE
B. DESCRIBE
C. NOW
D. NULLIF
E. WHERE

21. You need to replace null values in the DEPT_ID column with the text N/A. Which functions
should you use?
A. TO_CHAR and NULL
B. TO_CHAR and NULLIF
C. TO_CHAR and NVL
D. TO_NUMBER and NULLIF
E. TO_NUMBER and NVL2

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
6
22. Which of the following General Functions will return the first non-null expression in the
expression list?
A. COALESCE
B. NVL
C. NVL2
D. NVL3
E. NULLIF

23. Which arithmetic operation will return a numeric value?

A. SYSDATE - 6
B. SYSDATE + 30 / 24
C. NEXT_DAY(hire_date) + 5
D. TO_DATE('01-Jun-2004') - TO_DATE('01-Oct-2004')
E. All of the above

24. A table has the following definition:

EMPLOYEES(
EMPLOYEE_ID NUMBER(6) NOT NULL,
NAME VARCHAR2(20) NOT NULL,
MANAGER_ID VARCHAR2(6))

and contains the following rows:

(1001, 'Bob Bevan', '200')
(200, 'Natacha Hansen', null)

Will the following query work?

SELECT *
FROM employees
WHERE employee_id = manager_id;

A. No, because the datatypes of EMPLOYEE_ID and MANAGER_ID are different

B. No, you will have to re-write the statement and perform explicit datatype conversion
C. Yes, Oracle will perform implicit datatype conversion, but the WHERE clause will not
find any matching data
D. Yes, Oracle will perform implicit datatype conversion and the query will return one row
of data

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
7
 25. You need to display the HIRE_DATE values in this format: 25th of July 2002. Which
SELECT statement would you use?
A. SELECT enroll_date(hire_date, 'ddth "of" Month YYYY')
FROM employees;
B. SELECT enroll_date(hire_date, 'DDspth "of" Month YYYY')
FROM employees;
C. SELECT TO_CHAR(hire_date, 'DDTH "of" Month YYYY')
FROM employees;
D. SELECT TO_CHAR(hire_date, 'DDspth 'of' Month RRRR')
FROM employees;
E. SELECT TO_CHAR(hire_date, 'ddth "of" Month YYYY')
FROM employees;

Section 6

26. Evaluate this SELECT statement:

SELECT *
FROM employee worker JOIN employee manager
ON worker.mgr_id = manager.emp_id;

Which type of join is created by this SELECT statement?

A. Cross join
B. Full outer join
C. Left outer join
D. Right outer join
E. Self-join

27. The keywords JOIN _____________ should be used to join tables with the same column
names but different datatypes.
A. ABOUT
B. NATURAL ON
C. OVER
D. USING
E. WHEN

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
8
28. You need to join two tables that have two columns with the same name, datatype, and
precision. Which type of join would you create to join the tables on both of the columns?
A. Cross join
B. Natural join
C. Outer join
D. Self-join
E. None of the above

29. For which of the following tables will all the values be retrieved even if there is no match in
the other?
SELECT employees.last_name, employees.department_id,
departments.department_name
FROM employees
LEFT OUTER JOIN departments
ON (employees.department_id = departments.department_id);

A. DEPARTMENTS
B. EMPLOYEES
C. Both
D. Neither - the LEFT OUTER JOIN limits the value to the matching department ids

30. Which syntax would be used to retrieve all rows in both the EMPLOYEES and DEPARTMENTS
tables, even when there is no match?
A. FULL INNER JOIN
B. FULL OUTER JOIN
C. LEFT OUTER JOIN and RIGHT OUTER JOIN
D. Use any equijoin syntax
E. All of the above

31. Which of the following conditions will cause an error on a NATURAL JOIN?
A. If the columns having the same names have different data types
B. If it selects rows from the two tables that have equal values in all matched columns
C. When the NATURAL JOIN clause is based on all columns in the two tables that have
the same name
D. When you attempt to use two tables that have a common field
E. All of the above

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
9
 32. Which SELECT statement implements a self-join?

A. SELECT item.part_id, type.product_id

FROM part item JOIN part type
ON item.part_id = type.product_id;
B. SELECT item.part_id, type.product_id
FROM part item JOIN product type
ON item.part_id = type.product_id;
C. SELECT item.part_id, type.product_id
FROM part item JOIN product type
ON item.part_id =! type.product_id;
D. SELECT item.part_id, type.product_id
FROM part item JOIN product type
ON item.part_id = type.product_id (+);

Section 7

33. 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?
A. An equijoin
B. A full outer join
C. A non-equijoin
D. All of the above
E. None of the above

34. What is the result of a query that selects from two tables but includes no join condition?
A. A Cartesian product
B. A selection of matched rows from both tables
C. A selection of rows from the first table only
D. A syntax error
E. None of the above

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
10
 35. What happens when you create a Cartesian product?
A. All rows that do not match in the WHERE clause are displayed
B. All rows from one table are joined to all rows of another table
C. The table is joined to another equal table
D. The table is joined to itself, one column to the next column, exhausting all possibilities
E. All of the above

Section 8

36. Which group functions below act on character, number, and date data types? (Choose three)
A. AVG
B. COUNT
C. MAX
D. MIN
E. SUM

37. Given the following data in the employees table (employee_id, salary,
commission_pct)

DATA:
(143, 2600, null
144, 2500, null
149, 10500, .2
174, 11000, .3
176, 8600, .2
178, 7000, .15)

What is the result of the following statement:

SELECT SUM(commission_pct), COUNT(salary)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);

A. SUM = .85 and COUNT = 4

B. SUM = .85 and COUNT = 6
C. SUM = 1.85 and COUNT =4
D. SUM = 1.85 and COUNT = 6

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
11
 38. Evaluate this SELECT statement:
SELECT COUNT(*)
FROM products;

Which statement is true?

A. An error occurs because no WHERE clause is included in the SELECT statement

B. An error occurs due to an error in the SELECT clause
C. The number of rows in the table is displayed
D. The number of unique PRODUCT_IDs in the table is displayed

Section 9

39. True or False: When using SET operators, the number of columns and the data types of the
columns must be identical in all of the SELECT statements used in the query.
A. True
B. False

40. Which of the following SQL statements could display the number of people with the same
last name:
A. SELECT employee_id, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
B. SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
C. SELECT first_name, last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
D. SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
12
41. Is the following statement correct?
SELECT first_name, last_name, salary, department_id,
COUNT(employee_id)
FROM employees
WHERE department_id = 50
GROUP BY last_name, first_name, department_id;

A. No, because the statement is missing salary in the GROUP BY clause

B. No, because you cannot have a WHERE clause when you use group functions
C. Yes, the statement is correct
D. Yes, because Oracle will correct any mistakes in the statement itself

42. True or False: CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX,
and COUNT.
A. True
B. False

43. Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(.......);

Select the correct GROUP BY GROUPING SETS clause from the following list:

A. GROUP BY GROUPING SETS (department_id, salary), (department_id,

job_id), (department_id, manager_id)
B. GROUP BY GROUPING SETS (department_id, AVG(salary)),
(department_id, job_id), (department_id, manager_id)
C. GROUP BY GROUPING SETS ((department_id, manager_id),
(department_id, job_id), (manager_id, job_id))
D. GROUP BY GROUPING SETS ((department_id, manager_id),
(department_id, SUM(salary), (manager_id, job_id))

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
13
44. The EMPLOYEES table contains the following columns:
EMPLOYEE_ID NUMBER(10) PRIMARY KEY
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
DEPARTMENT VARCHAR2(20)
HIRE_DATE DATE
SALARY NUMBER(10)

You want to create a report that includes each employee's last name, employee identification
number, date of hire, and salary. The report should include only those employees who have
been with the company for more than one year and whose salary exceeds $40,000.

Which of the following SELECT statements will accomplish this task?

A. SELECT employee_id, last_name, hire_date, salary

FROM employees
WHERE salary > 40000
AND (sysdate-hire_date) / 365 > 1;
B. SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 40000
AND hire_date IN (sysdate-hire_date) / 365 > 1);
C. SELECT employee_id, last_name, salary
FROM employees
WHERE salary > 40000
AND hire_date =
(SELECT hire_date
FROM employees
WHERE (sysdate-hire_date) / 365 > 1);
D. SELECT employee_id, last_name, hire_date, salary
FROM employees
WHERE salary > 40000 AND hire_date =
(SELECT hire_date
FROM employees
WHERE (sysdate-hire_date) / 365 > 1);

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
14
Section 10

45. If a single-row subquery returns a null value and uses the equality comparison operator,
what will the outer query return?
A. A null value
B. All the rows in the table
C. An error
D. No rows
E. None of the above

46. Which of the following is TRUE regarding the order of subquery execution?
A. The outer query is executed first
B. The result of the main query is used with the subquery
C. The subquery executes once after the main query
D. The subquery executes once before the main query
E. None of the above

47. True or False: The WITH clause enables a SELECT statement to define the subquery block
at the start of the query, process the block just once, label the results, and then refer to the
results multiple times.
A. True
B. False

48. Evaluate this SELECT statement:

SELECT student_id, last_name, first_name
FROM student
WHERE major_id NOT IN
(SELECT major_id
FROM majors
WHERE department_head_id = 30 AND title = 'ADJUNCT');

What would happen if the inner query returned a NULL value row?

A. A syntax error would be returned

B. All the rows in the STUDENT table would be displayed
C. Only the rows with STUDENT_ID values equal to NULL would be displayed
D. No rows would be returned from the STUDENT table
E. None of the above
Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
15
49. Examine the data in the PAYMENT table:

PAYMENT_ID CUSTOMER_ID PAYMENT_DATE PAYMENT_TYPE PAYMENT_AMOUNT

86590586 8908090 10-Jun-2003 BASIC 859.00

89453485 8549038 15-Feb-2003 INTEREST 596.00
85490345 5489304 20-Mar-2003 BASIC 568.00

This statement fails when executed:

SELECT payment_date, customer_id, payment_amount
FROM payment
WHERE payment_id =
(SELECT payment_id
FROM payment
WHERE payment_date >= '05-Jan-2002' OR payment_amount > 500.00);

Which change could correct the problem?

A. Change the outer query WHERE clause to 'WHERE payment_id IN'

B. Include the PAYMENT_ID column in the select list of the outer query
C. Remove the single quotes around the date value in the inner query WHERE clause
D. Remove the subquery WHERE clause
E. All of the above

50. You are looking for Executive information using a subquery.

What will the following SQL statement display?

SELECT department_id, last_name, job_id
FROM employees
WHERE department_id IN
(SELECT department_id FROM departments WHERE department_name =
'Executive');

A. The department ID, department name, and last name for every employee in the
Executive department
B. The department ID, last name, and department name for every Executive in the
employees table
C. The department ID, last name, and job ID from departments for Executive
employees
D. The department ID, last name, and job ID for every employee in the Executive
department

Copyright © 2024, Oracle and/or its affiliates. Oracle®, Java, MySQL and NetSuite are registered trademarks of Oracle and/or its affiliates. Other
names may be trademarks of their respective owners.
16