DDSQL2-DP8-P10

Which group function would you use to display the average price of all products in the PRODUCTS table?

• MAX

• SUM

• COUNT

• AVG

You need to calculate the average salary of employees in each department. Which group function will you use?

• AVERAGE

• MEAN

• MEAN

• AVG

Which group function would you use to display the highest salary value in the EMPLOYEES table?

• MAX

• MIN

• COUNT

• AVG

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

• VARIANCE

• STDEV

• VAR_SAMP

• STDDEV

The VENDORS table contains these columns:

VENDOR_ID NUMBER Primary Key NAME VARCHAR2(30) LOCATION_ID NUMBER ORDER_DT DATE ORDER_AMOUNT NUMBER(8,2)

Which two clauses represent valid uses of aggregate functions for this table?

(Choose all correct answers)

• SELECT SUM(order_dt)

• FROM MAX(order_dt)

• WHERE MAX(order_dt) = order_dt

• SELECT SUM(order_amount)

• SELECT MIN(AVG(order_amount))

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

• SUM = 1.85 and COUNT =4

• SUM = .85 and COUNT = 4

• SUM = .85 and COUNT = 6

• SUM = 1.85 and COUNT = 6

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 AVG(commission_pct) FROM employees WHERE employee_id IN( 143,144,149,174,176,178);

• 0.2125

• This statement is invalid

• 0.0425

• 1.2125

The PRODUCTS table contains these columns:

PROD_ID NUMBER(4) PROD_NAME VARCHAR2(30) PROD_CAT VARCHAR2(30) PROD_PRICE NUMBER(3) PROD_QTY NUMBER(4)

The following statement is issued:

SELECT AVG(prod_price, prod_qty) FROM products;

What happens when this statement is issued?

• The values in the PROD_PRICE column and the PROD_QTY column are averaged together.

• An error occurs.

• Both the average price and the average quantity of the products are returned.

• Only the average quantity of the products is returned.

Evaluate this SQL statement:

SELECT COUNT (amount) FROM inventory;

What will occur when the statement is issued?

• The statement will count the number of rows in the INVENTORY table where the AMOUNT column is not null.

• The statement will return the greatest value in the INVENTORY table.

• The statement will replace all NULL values that exist in the AMOUNT column.

• The statement will return the total number of rows in the AMOUNT column.

The EMPLOYEES table contains these columns:

EMPLOYEE_ID NUMBER(9) LAST_NAME VARCHAR2(20) FIRST_NAME VARCHAR2(20) SALARY NUMBER(7,2) DEPARTMENT_ID NUMBER(9)

You need to display the number of employees whose salary is greater than $50,000? Which SELECT would you use?

• SELECT COUNT(*) FROM employees WHERE salary < 50000;

• SELECT * FROM employees WHERE salary < 50000;

• SELECT COUNT(*) FROM employees WHERE salary > 50000 GROUP BY employee_id, last_name, first_name, salary, department_id;

• SELECT COUNT(*) FROM employees WHERE salary > 50000;

• SELECT * FROM employees WHERE salary > 50000;