(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.
0
No comments:
Post a Comment