Section 2 Database Programmingoracle

Section 2 Database ProgrammingORACLE

S. Sherman Academy Instructor

Additional Problems for Section 2

TRUE/FALSE

1.The only way to override the sort order in a GROUP BY clause is to use a group function.

ANS:F

MULTIPLE CHOICE

1.The outcome of the following script will be:

SELECT department_id, first_name, last_name, hire_date

FROM employees

WHERE max(hire_date) < ‘01-JAN-98’

GROUP BY department_id;

a. / Generates an error because some hire dates might be null
b. / Displays a list of departments whose last person hired was before 01-JAN-98
c. / Generates an error because first name, last name, and hire date must be added to the GROUP BY clause
d. / Generates an error because group functions are invalid in the WHERE clause
e. / Displays a list by department of the employees who were hired before 01-JAN-98

ANS:D

2.The SELECT clause of a query to display the number of unique last names in the EMPLOYEES table is:

a. / SELECT DISTINCT last_name
b. / SELECT DISTINCT COUNT(last_name)
c. / SELECT UNIQUE(last_name)
d. / SELECT COUNT(DISTINCT last_name)
e. / SELECT COUNT UNIQUE(last_name)

ANS:D

3.Put the following terms in the order they must appear if they were all in the same SQL statement: HAVING, GROUP BY, ORDER BY, WHERE

a. / The same as they are listed
b. / No one SQL Statement could have all of those terms
c. / WHERE, GROUP BY, HAVING, ORDER BY
d. / GROUP BY, HAVING, WHERE, ORDER BY

ANS:C

4.All of the following are group functions that are available in SQL, except which one?

a. / NVL / c. / SUM
b. / COUNT / d. / VARIANCE

ANS:A

5.You are interested in determining a career path for yourself. While money isn't everything, it's good to know what the average salary is for various positions. Having access to the Oracle database, you can determine what the average salary is for all the job titles listed in the database. Which of the following queries will display the information you are looking for?

a. / SELECT COUNT (DISTINCT job_id)
FROM employees
GROUP BY AVG(salary);
b. / SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id;
c. / SELECT AVG(salary), DISTINCT (job_id)
FROM employees
GROUP BY AVG(salary);
d. / SELECT job_id, AVG(salary)
FROM employees
GROUP BY AVG(job_id);
e. / Select something from a list then
FROM something else
and then do something wonderful

ANS:B

6.The outcome of the following script is:

SELECT department_id, job_id, max(salary)

FROM employees

GROUP BY department_id;

a. / Generates an error because more than one column must be included in the GROUP BY clause
b. / Displays each employee’s department, job title, and salary, sorted by maximum to minimum salary
c. / Displays the department number, job title, and salary of the maximum salary holder for each department
d. / Generates an error because the GROUP BY clause must have a group function
e. / Generates an error because job_id also needs to be listed in the GROUP BY clause

ANS:E

7.Which of the following scripts will display the average commission percentage for all employees in all departments?

a. / SELECT department_id, AVG(commission_pct) FROM employees GROUP BY department_id; / d. / SELECT department_id, AVG(COALESCE(commission_pct, department_id, 0)) FROM employees GROUP BY department_id;
b. / SELECT department_id, AVG(NVL(commission_pct, 0)) FROM employees GROUP BY department_id; / e. / SELECT department_id, NVL(AVG(commission_pct), 0) FROM employees GROUP BY department_id
c. / SELECT department_id, AVG(commission_pct) FROM employees WHERE commission_pct > 0 GROUP BY department_id;

ANS:B

8.The SELECT clause of a query to display the average number of employees per department rounded to the nearest integer is:

a. / SELECT ROUND(AVG(COUNT * ))
b. / SELECT AVG(ROUND(COUNT(*)))
c. / SELECT COUNT(AVG(ROUND(*)))
d. / SELECT ROUND(COUNT(AVG(*)))
e. / SELECT ROUND(AVG(COUNT(*)))

ANS:E

9.Since group functions ignore null values, how would you make the group function include the null value?

a. / Use the TO_CHAR function / c. / Use the NVL function
b. / Use the COALESCE function / d. / It’s not true. Group functions always include null values so there’s no problem

ANS:C

10.Based on the table below, what will be the response to the following SELECT statement?

SELECT COUNT (DISTINCT department_id)

FROM employees;

first_name / last_name / department_id
John / Smith / 10
John / Smithy / 12
John / Smith / 11
Jennifer / Tyler / 10
Jennifer / Smith / 10
Jennifer / Smithy / 11
Eric / Foster / 10
Eric / Forester / 11
Mary / Smith / 11
a. / 0 / c. / 5
b. / 3 / d. / 9

ANS:B