DP Database Programming with SQL Midterm Exam

Test: DP Database Programming with SQL Midterm Exam

Review your answers, feedback, and question scores below. An asterisk (*) indicates a correct answer.

 

1. What would you use in the SELECT clause to return all the columns in the table?

(1/1) Points

The ALL keyword

An asterisk (*) (*)

A plus sign (+)

A minus sign (-)

 

2. In the default order of precedence, which operator would be evaluated first?

(1/1) Points

Multiplications and Divisions are at the same level and would be evaluated first based on left to right order (*)

Subtractions and Additions are at the same level and would be evaluated first based on left to right order

Divisions and Subtractions are at the same level and would be evaluated first based on left to right order

Additions and Multiplications are at the same level and would be evaluated first based on left to right order

 

3. Every row in a relational database table is unique.

(1/1) Points

True (*)

False

 

4. What command can be added to a select statement to return a subset of the data?

(1/1) Points

WHEN

EVERYONE

WHERE (*)

ALL

 

5. Examine the follolowing SELECT statement.

SELECT *

FROM employees;

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

(1/1) Points

True (*)

False

 

6. Which of the following statements will work?

(1/1) Points

SELECT first_name ||' '||last_name NAME, department_id DEPARTMENT, salary*12 'ANNUAL SALARY'

FROM employees

WHERE last_name = 'King';

SELECT first_name ||' '||last_name NAME, department_id DEPARTMENT, salary*12 "ANNUAL SALARY"

FROM employees

WHERE last_name = 'King'; (*)

SELECT first_name ||' '||last_name NAME, department_id DEPARTMENT, salary*12 'ANNUAL SALARY'

FROM employees

WHERE name = 'King';

SELECT first_name ||' '||last_name NAME, department_id DEPARTMENT, salary*12 "ANNUAL SALARY"

FROM employees

WHERE name = 'King';

 

7. You want to retrieve a list of customers whose last names begin with the letters 'Fr' . Which symbol should you include in the WHERE clause of your SELECT statement to achieve the desired result?

(1/1) Points

~

% (*)

*

#

 

8. The EMPLOYEES table contains these columns:

LAST_NAME VARCHAR2(25)

FIRST_NAME VARCHAR2(25)

EMAIL VARCHAR2(50)

You are writing a SELECT statement to retrieve the names of employees that have an email address.

SELECT last_name||', '||first_name "Employee Name"

FROM employees;

Which WHERE clause should you use to complete this statement?

(1/1) Points

WHERE email = NULL;

WHERE email IS NOT NULL; (*)

WHERE email IS NULL;

WHERE email != NULL;

 

9. Which of the following WHERE clauses would not select the number 10?

(1/1) Points

WHERE hours IN (8,9,10)

WHERE hours BETWEEN 10 AND 20

WHERE hours <>10 (*)

WHERE hours <= 10

 

10. Which comparison operator searches for a specified character pattern?

(1/1) Points

BETWEEN...AND...

IS NULL

IN

LIKE (*)

 

11. You need to display employees with salaries that are at least 30000 or higher. Which comparison operator should you use?

(1/1) Points

"=>"

>= (*)

> 

!=

12. The PLAYERS table contains these columns:

PLAYERS TABLE:

LAST_NAME VARCHAR2 (20)

FIRST_NAME VARCHAR2 (20)

SALARY NUMBER(8,2)

TEAM_ID NUMBER(4)

MANAGER_ID NUMBER(9)

POSITION_ID NUMBER(4)

You want to display all players' names with position 6900 or greater.

You want the players names to be displayed alphabetically by last name and then by first name.

Which statement should you use to achieve the required results?

(1/1) Points

SELECT last_name, first_name

FROM players

WHERE position_id > 6900

ORDER BY last_name, first_name;

SELECT last_name, first_name

FROM players

WHERE position_id >= 6900

ORDER BY last_name, first_name; (*)

SELECT last_name, first_name

FROM players

WHERE position_id <= 6900

ORDER BY last_name, first_name;

SELECT last_name, first_name

FROM players

WHERE position_id >= 6900

ORDER BY last_name DESC, first_name;

 

13. Evaluate this SELECT statement:

SELECT first_name, last_name, email

FROM employees

ORDER BY last_name;

Which statement is true?

(1/1) Points

The rows will be sorted alphabetically by the LAST_NAME values. (*)

The rows will not be sorted.

The rows will be sorted alphabetically by the FIRST_NAME and then the LAST_NAME values

The rows will be sorted in reverse alphabetical order by the LAST_NAME values.

 

14. What clause must you place in a SQL statement to have your results sorted from highest to lowest salary?

(1/1) Points

None, the database always sorts from highest to lowest on the salary column.

ORDER BY salary ASC

ORDER salary BY DESC

ORDER BY salary DESC (*)

 

15. You need to change the default sort order of the ORDER BY clause so that the data is displayed in reverse alphabetical order. Which keyword should you include in the ORDER BY clause?

(1/1) Points

DESC (*)

CHANGE

ASC

SORT

 

 

16. You want to create a report that displays all orders and their amounts that were placed during the month of January. You want the orders with the highest amounts to appear first. Which query should you issue?

(1/1) Points

SELECT orderid, total

FROM orders

WHERE order_date BETWEEN '01-Jan-2002' AND '31-Jan-2002'

ORDER BY total DESC; (*)

SELECT orderid, total

FROM orders

WHERE order_date BETWEEN '31-Jan-2002' AND '01-Jan-2002'

ORDER BY total DESC;

SELECT orderid, total

FROM orders

WHERE order_date LIKE '01-Jan-2002' AND '31-Jan-2002'

ORDER BY total DESC;

SELECT orderid, total

FROM orders

WHERE order_date IN ( 01-Jan-2002 , 31-Jan-2002 )

ORDER BY total;

 

17. Which SQL function can be used to remove heading or trailing characters (or both) from a character string?

(1/1) Points

CUT

LPAD

TRIM (*)

NVL2

 

18. What does the following SQL SELECT statement return?

SELECT UPPER( SUBSTR('Database Programming', INSTR('Database Programming','P'),20))

FROM dual;

 

(1/1) Points

DATABASE

Programming

PROGRAMMING (*)

Database

Correct

19. You issue this SQL statement:

SELECT TRUNC(751.367,-1) FROM dual;

Which value does this statement display?

 

(1/1) Points

750 (*)

700

751

751.3

 

20. Which statement about group functions is true?

(1/1) Points

NVL, NVL2, and COALESCE can be used with group functions to replace null values. (*)

COALESCE, but not NVL and NVL2, can be used with group functions to replace null values.

NVL and NVL2, but not COALESCE, can be used with group functions to replace null values.

NVL and COALESCE, but not NVL2, can be used with group functions to replace null values.

 

21. The PRODUCT table contains this column: PRICE NUMBER(7,2)

Evaluate this statement:

SELECT NVL(10 / price, '0')

FROM PRODUCT;

What would happen if the PRICE column contains null values?

(1/1) Points

The statement would fail because values cannot be divided by null.

The statement would fail because values cannot be divided by 0.

A value of 0 would be displayed. (*)

A value of 10 would be displayed.

 

22. You need to display the HIRE_DATE values in this format: 25th of July 2002. Which SELECT statement would you use?

(1/1) Points

SELECT TO_CHAR(hire_date, 'ddth "of" Month YYYY')

FROM employees; (*)

SELECT enroll_date(hire_date, 'DDspth "of" Month YYYY')

FROM employees;

SELECT TO_CHAR(hire_date, 'DDspth 'of' Month RRRR')

FROM employees;

SELECT TO_CHAR(hire_date, 'DDTH "of" Month YYYY')

FROM employees;

 

23. Which arithmetic operation will return a numeric value?

(1/1) Points

TO_DATE('01-Jun-2004') - TO_DATE('01-Oct-2004') (*)

SYSDATE + 30 / 24

NEXT_DAY(hire_date) + 5

SYSDATE - 6

 

24. The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER(9)

LAST_NAME VARCHAR2 (25)

FIRST_NAME VARCHAR2 (25)

SALARY NUMBER(6)

You need to create a report to display the salaries of all employees. Which SQL Statement should you use to display the salaries in format: "$45,000.00"?

 

(1/1) Points

SELECT TO_NUM(salary, '$999,990.99')

FROM employees;

SELECT TO_CHAR(salary, '$999,999')

FROM employees;

SELECT TO_CHAR(salary, '$999,999.00')

FROM employees; (*)

SELECT TO_NUM(salary, '$999,999.00')

FROM employees;

 

25. Which statement will return a listing of last names, salaries, and a rating of 'Low', 'Medium', 'Good' or 'Excellent' depending on the salary value?

(1/1) Points

SELECT last_name,salary,

(CASE WHEN salary<5000 THEN 'Low'

     WHEN sal <10000 THEN 'Medium'

     WHEN sal <20000 THEN 'Good'

     ELSE 'Excellent'

END) qualified_salary

FROM employees;

SELECT last_name,salary,

(CASE WHEN salary<5000 THEN 'Low'

     WHEN salary<10000 THEN 'Medium'

     WHEN salary<20000 THEN 'Good'

     ELSE 'Excellent'

END) qualified_salary

FROM employees; (*)

SELECT last_name,salary,

(RATING WHEN salary<5000 THEN 'Low'

     WHEN salary<10000 THEN 'Medium'

     WHEN salary<20000 THEN 'Good'

     ELSE 'Excellent'

END) qualified_salary

FROM employees;

SELECT last_name,sal,

(CASE WHEN sal<5000 THEN 'Low'

     WHEN sal<10000 THEN 'Medium'

     WHEN sal<20000 THEN 'Good'

     ELSE 'Excellent'

END) qualified_salary

FROM employees;

 

26. Evaluate this SELECT statement:

SELECT patient.lname || ', ' || patient.fname as "Patient", physician.lname || ', ' || physician.fname as "Physician", admission.admission

FROM patient

JOIN physician

ON (physician.physician_id = admission.physician_id)

JOIN admission

ON (patient.patient_id = admission.patient_id);

Which clause generates an error?

(1/1) Points

JOIN admission

ON (physician.physician_id = admission.physician_id); (*)

JOIN physician

ON (patient.patient_id = admission.patient_id)

 

27. Which of the following database design concepts is implemented with a self join?

(1/1) Points

Supertype

Non-Transferability

Arc

Recursive Relationship (*)

 

28. Which select statement will return the last name and hire date of an employee and his/ her manager for employees that started in the company before their managers?

(1/1) Points

SELECT worker.last_name, worker.hire_date, manager.last_name, manager.hire_date

FROM employees worker JOIN employees manager

ON worker.manager_id != manager.employee_id

WHERE worker.hire_date < manager.hire_date

SELECT worker.last_name, worker.hire_date, manager.last_name, manager.hire_date

FROM employees worker JOIN employees manager

ON worker.manager_id = manager.employee_id

WHERE worker.hire_date > manager.hire_date

SELECT worker.last_name, worker.hire_date, manager.last_name, m.hire_date

FROM employees worker JOIN employees manager

ON worker.manager_id = manager.employee_id

WHERE worker.hire_date < manager.hire_date

(*)

SELECT worker.last_name, worker.hire_date, manager.last_name, manager.hire_date

FROM employees worker JOIN employees worker

ON worker.manager_id = worker.employee_id

WHERE worker.hire_date < worker.hire_date

 

29. 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?

(1/1) Points

Self-join

Natural join (*)

Outer join

Cross join

 

30. The join column must be included in the select statement when you use the NATURAL JOIN clause. True or False?

(1/1) Points

True

False (*)

 

31. What types of joins will return the unmatched values from both tables in the join?

(1/1) Points

Right outer joins

Left outer joins

Full outer joins (*)

Natural joins

 

32. Given the following descriptions of the employees and jobs tables, which of the following scripts will display each employeeﾒs possible minimum and maximum salaries based on their job title?

EMPLOYEES Table:

Name    Null?      Type

EMPLOYEE_ID   NOT NULL           NUMBER (6)

FIRST_NAME                     VARCHAR2 (20)

LAST_NAME       NOT NULL           VARCHAR2 (25)

EMAIL   NOT NULL           VARCHAR2 (25)

PHONE_NUMBER                            VARCHAR2 (20)

HIRE_DATE         NOT NULL           DATE

JOB_ID NOT NULL           VARCHAR2 (10)

SALARY                 NUMBER (8,2)

COMMISSION_PCT                         NUMBER (2,2)

MANAGER_ID                   NUMBER (6)

DEPARTMENT_ID                             NUMBER (4)

JOBS Table:

Name    Null?      Type

JOB_ID NOT NULL           VARCHAR2 (10)

JOB_TITLE           NOT NULL           VARCHAR2 (35)

MIN_SALARY                     NUMBER (6)

MAX_SALARY                    NUMBER (6)

(1/1) Points

SELECT first_name, last_name, job_id, min_salary, max_salary

FROM employees

NATURAL JOIN jobs; (*)

SELECT employees.first_name, employees.last_name, employees.job_id, jobs.min_salary, jobs.max_salary

FROM employees

NATURAL JOIN jobs ON (employees.job_title = jobs.job_title);

SELECT employees.first_name, employees.last_name, employees.job_id, jobs.min_salary, jobs.max_salary

FROM employees

NATURAL JOIN jobs;

SELECT employees.first_name, employees.last_name, employees.job_id, jobs.min_salary, jobs.max_salary

FROM employees

NATURAL JOIN jobs

USING (job_id);

SELECT first_name, last_name, job_id, min_salary, max_salary

FROM employees

FULL JOIN jobs (job_id);

33. What is produced when a join condition is not specified in a multiple-table query using Oracle proprietary Join syntax?

(1/1) Points

A Cartesian product (*)

An outer join

An equijoin

A self-join

 

34. 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?

(1/1) Points

SELECT e.first_name, e.last_name, s.sales

FROM employees, sales

WHERE e.employee_id = s.employee_id AND revenue >= 100000;

SELECT e.first_name, e.last_name, s.sales

FROM employees e, sales s

WHERE e.employee_id = s.employee_id AND revenue > 100000;

SELECT first_name, last_name, sales

FROM employees e, sales s

WHERE e.employee_id = s.employee_id AND revenue > 100000;

SELECT e.first_name, e.last_name, s.sales

FROM employees e, sales s

WHERE e.employee_id = s.employee_id AND revenue >= 100000; (*)

 

35. Which symbol is used to perform an outer join?

(1/1) Points

||

#

*

(+) (*)

 

36. You need to calculate the standard deviation for the cost of products produced in the Birmingham facility. Which group function will you use?

(1/1) Points

VAR_SAMP

STDDEV (*)

VARIANCE

STDEV

 

37. You need to compute the total salary amount for all employees in department 10. Which group function will you use?

(1/1) Points

COUNT

SUM (*)

VARIANCE

MAX

 

38. Evaluate this SELECT statement:

SELECT COUNT(*)

FROM products;

Which statement is true?

(1/1) Points

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

The number of unique PRODUCT_IDs in the table is displayed.

The number of rows in the table is displayed. (*)

An error occurs due to an error in the SELECT clause.

 

39. 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?

(1/1) Points

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);

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);

SELECT employee_id, last_name, salary

FROM employees

WHERE salary > 40000

AND hire_date IN (sysdate-hire_date) / 365 > 1);

SELECT employee_id, last_name, hire_date, salary

FROM employees

WHERE salary > 40000

AND (sysdate-hire_date) / 365 > 1; (*)

 

40. Evaluate this SELECT statement:

SELECT COUNT(emp_id), mgr_id, dept_id

FROM employees

WHERE status = 'I'

GROUP BY dept_id

HAVING salary > 30000

ORDER BY 2;

Why does this statement return a syntax error?

(1/1) Points

The HAVING clause must specify an aggregate function.

A single query cannot contain a WHERE clause and a HAVING clause.

MGR_ID must be included in the GROUP BY clause. (*)

The ORDER BY clause must specify a column name in the EMPLOYEE table.

41. Evaluate this SELECT statement:

SELECT SUM(salary), department_id, manager_id

FROM employees

GROUP BY department_id, manager_id;

Which SELECT clause allows you to restrict the rows returned, based on a group function?

(1/1) Points

HAVING SUM(salary) > 100000 (*)

WHERE salary > 100000

WHERE SUM(salary) > 100000

HAVING salary > 100000

 

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

(1/1) Points

True (*)

False

 

43. Examine the following statement:

SELECT department_id, manager_id, job_id, SUM(salary)

FROM employees

GROUP BY ROLLUP(department_id, manager_id)

What extra data will this query generate?

(0/1) Points

Subtotals for department_id, and grand totals for salary

Subtotals for department_id, job_id and grand totals for salary (*)

Subtotals for department_id, job_id, manager_id and grand totals for salary

The statement will fail.

 

 

44. The difference between UNION and UNION ALL is

(1/1) Points

UNION will remove duplicates; UNION ALL returns all rows from all queries including the duplicates. (*)

UNION is a synomym for UNION ALL.

There is no difference; you get exactly the same result from both.

UNION ALL is more like a NATURAL JOIN.

45. Table aliases must be used when you are writing correlated subqueries. (True or false?)

(1/1) Points

True

False (*)

46. Subqueries are limited to four per SQL transaction. True or False?

(1/1) Points

True

False (*)

 

47. Which operator can be used with subqueries that return only one row?

(1/1) Points

IN

LIKE (*)

ALL

ANY

 

48. 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?

(1/1) Points

Remove the single quotes around the date value in the inner query WHERE clause.

Change the outer query WHERE clause to 'WHERE payment_id IN'. (*)

Remove the subquery WHERE clause.

Include the PAYMENT_ID column in the select list of the outer query.

 

49. The salary column of the f_staffs table contains the following values:

4000

5050

6000

11000

23000

Which of the following statements will return the last_name and first_name of those employees who earn more than 5000?

(1/1) Points

SELECT last_name, first_name

FROM f_staffs

WHERE salary IN (SELECT salary FROM f_staffs WHERE salary > 5000); (*)

SELECT last_name, first_name

FROM f_staffs

WHERE salary = (SELECT salary FROM f_staffs WHERE salary > 5000);

SELECT last_name, first_name

FROM f_staffs

WHERE salary = (SELECT salary FROM f_staffs WHERE salary < 5000);

SELECT last_name, first_name

FROM f_staffs

WHERE salary IN

(SELECT last_name, first_name FROM f_staffs WHERE salary <5000);

 

50. 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?

(1/1) Points

No rows would be returned from the STUDENT table. (*)

All the rows in the STUDENT table would be displayed.

A syntax error would be returned.

Only the rows with STUDENT_ID values equal to NULL would be displayed.