100% found this document useful (1 vote)

152 views9 pages

SQL Quiz on Aggregate Functions

Q: When trying to compute the total salary for employees in a specific department, which SQL group function should be used and why?

The SUM function should be used to compute the total salary for employees in a specific department because it returns the total sum of a numeric column, which is suitable to calculate the total salary .

Q: Which SQL SELECT statement accurately returns the count of all rows in a table regardless of the null values present?

The SQL SELECT statement SELECT COUNT(*) FROM table_name accurately returns the count of all rows in a table regardless of the presence of NULL values, as COUNT(*) counts all rows .

Q: What happens when you use the COUNT function to count rows where a certain column contains only NULL values in SQL?

The COUNT function only counts non-null values; thus, if a column contains only NULL values, the COUNT function will return zero because null values are ignored in its computation .

Q: How does the inclusion of the DISTINCT keyword affect the outcome of group functions in SQL?

The DISTINCT keyword affects group functions by ensuring computations only use unique values, thus avoiding duplicate entries in the outcome. This is useful for functions like COUNT, AVG, and SUM, where duplicates might otherwise skew results .

Q: Explain the necessity of using the NVL function with group functions in SQL when dealing with NULL values.

The NVL function is necessary when using group functions in SQL because group functions ignore NULL values by default. To include NULL values in calculations, the NVL function is used to convert NULLs to a specified value, thereby ensuring that all the intended data is part of the computation .

Q: Why might you need to use a WHERE clause when using the AVG function, and how does it affect the result?

Using a WHERE clause with the AVG function is necessary to filter the dataset, ensuring that only specific rows are included when calculating the average. This allows for greater precision and tailoring of results, such as computing the average for specific time periods or categories .

Q: How can you compute the average payment amount for each customer over a specific time range using SQL?

To compute the average payment amount for each customer over a specific time range, you can use the SELECT AVG(payment_amount) statement with a WHERE clause to filter the records within the specified date range, like this: SELECT AVG(payment_amount) FROM payment WHERE payment_date BETWEEN '01-Jan-2003' AND '31-Mar-2003' .

Q: In what scenario would the COUNT function and a similar SELECT COUNT(commission_pct) differ in their results?

The COUNT(*) function counts all rows, including those with NULLs in any columns, while COUNT(commission_pct) counts only rows with a non-null value in the commission_pct column. Their results will differ when the commission_pct column has NULL values, as COUNT(commission_pct) will exclude those .

Q: If you were tasked with finding unique employee attributes exceeding specific salary criteria, which SQL statement would accomplish this and why?

To find unique employee attributes exceeding specific salary criteria, use SELECT DISTINCT employee_id, last_name, first_name FROM employees WHERE salary > 50000. This statement filters out employees whose salary is above the threshold and utilizes DISTINCT to ensure each employee's record is unique in the result set .

The document contains a quiz on SQL aggregate functions and group functions. It asks multiple choice questions about functions like COUNT, SUM, AVG, MIN, and MAX and how they can be used with different data types in aggregate queries. It also contains questions about grouping, null values, and selecting the number of rows that meet certain criteria.

Uploaded by

José Obeniel López

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

100% found this document useful (1 vote)

152 views9 pages

SQL Quiz on Aggregate Functions

Uploaded by

José Obeniel López

We take content rights seriously. If you suspect this is your content, claim it here.

Available Formats

Download as PDF, TXT or read online on Scribd

Section 1: SQL Functions Usage
Section 3: Vendor Table Columns
Section 2: Date Data Type Functions
Section 4: Grouping and Summation
Section 5: Total Salary Calculations
Section 6: Inventory Count Statements
Section 7: Utilizing Existing Knowledge
Section 8: Payment Table Queries
Section 9: Using Aggregate Functions
Section 10: COUNT Functions Exploration

Database Programming with SQL-Section 8 Quiz

(Answer all questions in this section)

1. The AVG, SUM, VARIANCE, and STDDEV functions can be used with which of the
following? Mark for Review
(1) Points

All except numeric

Only numeric data types (*)
Integers only
Any data type

2. Which aggregate function can be used on a column of the DATE data type? Mark for Review
(1) Points

MAX (*)
STDDEV
AVG
SUM

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

Mark for Review

(1) Points
(Choose all correct answers)

SELECT SUM(order_dt)
FROM MAX(order_dt)
SELECT SUM(order_amount) (*)
WHERE MAX(order_dt) = order_dt
SELECT MIN(AVG(order_amount)) (*)

4. Group functions return a value for ________________ and ________________ null values in
their computations. Mark for Review
(1) Points

a row set, ignore (*)

each row, include
each row, ignore
a row set, include

5. Which group function would you use to display the lowest value in the SALES_AMOUNT
column? Mark for Review
(1) Points

COUNT
MAX
AVG
MIN (*)

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 SUM(commission_pct), COUNT(salary)

FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);

Mark for Review

(1) Points

SUM = 1.85 and COUNT =4

SUM = 1.85 and COUNT = 6
SUM = .85 and COUNT = 4
SUM = .85 and COUNT = 6 (*)

7. The TRUCKS table contains these columns:

TRUCKS:
TYPE VARCHAR2(30)
YEAR DATE
MODEL VARCHAR2(20)
PRICE NUMBER(10)

Which SELECT statement will return the average price for the 4x4 model?

Mark for Review

(1) Points

SELECT AVG(price), model

FROM trucks
WHERE model IS '4x4';

SELECT AVG(price)
FROM trucks
WHERE model IS 4x4;

SELECT AVG(price)
FROM trucks
WHERE model IS '4x4';

SELECT AVG(price)
FROM trucks
WHERE model = '4x4';
(*)

8. You need to compute the total salary amount for all employees in department 10. Which group
function will you use? Mark for Review
(1) Points

COUNT
SUM (*)
VARIANCE
MAX

9. Evaluate this SELECT statement:

SELECT COUNT(*)
FROM products;

Which statement is true?

Mark for Review

(1) Points

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

An error occurs due to an error in the SELECT clause.
An error occurs because no WHERE clause is included in the SELECT statement.
The number of unique PRODUCT_IDs in the table is displayed.

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

Mark for Review

(1) Points

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;

11. Evaluate this SQL statement:

SELECT COUNT (amount)
FROM inventory;

What will occur when the statement is issued?

Mark for Review

(1) Points

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

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

The statement will replace all NULL values that exist in the AMOUNT column.
The statement will return the greatest value in the INVENTORY table.

12. To include null values in the calculations of a group function, you must: Mark for Review
(1) Points

Group functions can never use null values

Convert the null to a value using the NVL( ) function (*)
Count the number of null values in that column using COUNT
Precede the group function name with NULL

13. Which statement about the COUNT function is true? Mark for Review
(1) Points

The COUNT function always ignores null values by default. (*)

The COUNT function can be used to determine the number of unique, non-null values in a
column.
The COUNT function can be used to find the maximum value in each column.
The COUNT function ignores duplicates by default.

14. Which SELECT statement will calculate the number of rows in the PRODUCTS table?
Mark for Review
(1) Points

SELECT ROWCOUNT FROM products;

SELECT COUNT(products);
SELECT COUNT (*) FROM products; (*)
SELECT COUNT FROM products;

15. Using your existing knowledge of the employees table, would the following two statements
produce the same result?
SELECT COUNT(*)
FROM employees;

SELECT COUNT(commission_pct)
FROM employees;
Mark for Review
(1) Points

The second statement is invalid

The first statement is invalid
Yes
No (*)

1. The TRUCKS table contains these columns:

TRUCKS:
TYPE VARCHAR2(30)
YEAR DATE
MODEL VARCHAR2(20)
PRICE NUMBER(10)

Which SELECT statement will return the average price for the 4x4 model?

Mark for Review

(1) Points

SELECT AVG(price), model

FROM trucks
WHERE model IS '4x4';

SELECT AVG(price)
FROM trucks
WHERE model IS 4x4;

SELECT AVG(price)
FROM trucks
WHERE model IS '4x4';

SELECT AVG(price)
FROM trucks
WHERE model = '4x4';
(*)

2. Which group function would you use to display the highest salary value in the EMPLOYEES
table? Mark for Review
(1) Points
MIN
MAX (*)
AVG
COUNT

3. You need to compute the total salary amount for all employees in department 10. Which group
function will you use? Mark for Review
(1) Points

SUM (*)
MAX
VARIANCE
COUNT

4. You need to calculate the average salary of employees in each department. Which group function
will you use? Mark for Review
(1) Points

AVG (*)
AVERAGE
MEDIAN
MEAN

5. 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
You need to determine the average payment amount made by each customer in January, February, and
March of 2003.
Which SELECT statement should you use?

Mark for Review

(1) Points

SELECT AVG(payment_amount)
FROM payment
WHERE payment_date
BETWEEN '01-Jan-2003' AND '31-Mar-2003';
(*)

SELECT AVG(payment_amount)
FROM payment;

SELECT AVG(payment_amount)
FROM payment
WHERE TO_CHAR(payment_date) IN (Jan, Feb, Mar);

SELECT SUM(payment_amount)
FROM payment
WHERE payment_date BETWEEN '01-Jan-2003' and '31-Mar-2003';

6. Which group function would you use to display the lowest value in the SALES_AMOUNT
column? Mark for Review
(1) Points

MIN (*)
MAX
COUNT
AVG

7. The AVG, SUM, VARIANCE, and STDDEV functions can be used with which of the
following? Mark for Review
(1) Points

Integers only
Only numeric data types (*)
Any data type
All except numeric

8. Which group function would you use to display the total of all salary values in the EMPLOYEES
table? Mark for Review
(1) Points

COUNT
MAX
AVG
SUM (*)

9. Evaluate this SELECT statement:

SELECT COUNT(*)
FROM products;

Which statement is true?

Mark for Review

(1) Points

The number of unique PRODUCT_IDs in the table is displayed.

An error occurs due to an error in the SELECT clause.
The number of rows in the table is displayed. (*)
An error occurs because no WHERE clause is included in the SELECT statement.

10. Evaluate this SQL statement:

SELECT COUNT (amount)
FROM inventory;

What will occur when the statement is issued?

Mark for Review

(1) Points

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 statement will count the number of rows in the INVENTORY table where the AMOUNT
column is not null. (*)

11. Group functions can avoid computations involving duplicate values by including which
keyword? Mark for Review
(1) Points

DISTINCT (*)
SELECT
UNLIKE
NULL

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

Mark for Review

(1) Points

SELECT * FROM employees

WHERE salary > 50000;

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;

13. Which statement about the COUNT function is true? Mark for Review
(1) Points

The COUNT function always ignores null values by default. (*)

The COUNT function can be used to find the maximum value in each column.
The COUNT function ignores duplicates by default.
The COUNT function can be used to determine the number of unique, non-null values in a
column.

14. Which SELECT statement will calculate the number of rows in the PRODUCTS table?
Mark for Review
(1) Points

SELECT COUNT FROM products;

SELECT ROWCOUNT FROM products;
SELECT COUNT (*) FROM products; (*)
SELECT COUNT(products);

15. The STYLES table contains this data:

STYLE_ID STYLE_NAME CATEGORY COST
895840 SANDAL 85940 12.00
968950 SANDAL 85909 10.00
869506 SANDAL 89690 15.00
809090 LOAFER 89098 10.00
890890 LOAFER 89789 14.00
857689 HEEL 85940 11.00
758960 SANDAL 86979
You issue this SELECT statement:

SELECT COUNT(category)
FROM styles;

Which value is displayed?

Mark for Review

(1) Points

7 (*)
6
The statement will NOT execute successfully.

Common questions