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.
0
The EMPLOYEES table contains these columns:
ReplyDeleteEMPLOYEE_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 COUNT(*)
FROM employees
WHERE salary < 50000;
SELECT COUNT(*)
FROM employees
WHERE salary > 50000
GROUP BY employee_id, last_name, first_name, salary, department_id;
SELECT * FROM employees
WHERE salary > 50000;
Given the following data in the employees table (employee_id, salary, commission_pct)
ReplyDeleteDATA: (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(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);
Mark for Review
(1) Points
SUM = 1.85 and COUNT = 4
SUM = .85 and COUNT = 4 (*)
SUM = 1.85 and COUNT = 6
SUM = .85 and COUNT = 6
I cannot thank lemeridian funding service enough and letting people know how grateful I am for all the assistance that you and your team staff have provided and I look forward to recommending friends and family should they need financial advice or assistance @ 1,9% Rate for Business Loan .Via Contact : . lfdsloans@lemeridianfds.com / lfdsloans@outlook.com. WhatsApp...+ 19893943740. Keep up the great work.
ReplyDeleteThanks, Busarakham.
6. What would the following SQL statement return?
ReplyDeleteSELECT COUNT(DISTINCT salary)
FROM employees;
Mark for Review
(1) Points
The number of unique salaries in the employees table (*)
The total number of rows in the employees table
The total amount of salaries in the employees table
A listing of all unique salaries in the employees table
8. The PRODUCTS table contains these columns:
ReplyDeletePROD_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?
Mark for Review
(1) Points
Only the average quantity of the products is returned.
The values in the PROD_PRICE column and the PROD_QTY column are averaged together.
Both the average price and the average quantity of the products are returned.
An error occurs. (*)
10. You can use GROUP functions in all clauses of a SELECT statement. True or False?
ReplyDeleteMark for Review
(1) Points
True
False (*)
11. Given the following data in the employees table (employee_id, salary, commission_pct)
ReplyDeleteDATA: (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);
Mark for Review
(1) Points
1.2125
0.0425
This statement is invalid
0.2125 (*)
11. Which group function would you use to display the average price of all products in the PRODUCTS table?
ReplyDeleteMark for Review
(1) Points
COUNT
AVG (*)
SUM
MAX
12. What two group functions can be used with any datatype?
Mark for Review
(1) Points
MIN, MAX (*)
COUNT, SUM
STDDEV, VARIANCE
SUM, AVG
14. You need to calculate the standard deviation for the cost of products produced in the Birmingham facility. Which group function will you use?
Mark for Review
(1) Points
STDDEV (*)
STDEV
VARIANCE
VAR_SAMP
This comment has been removed by the author.
ReplyDeleteWhat would the following SQL statement return?
ReplyDeleteSELECT SUPERIOR (name)
Of employees
Mark for Review
(1) Points
A listing of all unique names in the employees table
Employees of the total number of rows in the table
A listing of all non-null names in the employees table
The total number of non-null names in the employees table (*)
13. Which of the following group functions acts on character, numeric, and date data types? (Select all correct options).
ReplyDeleteMark for review
(1) Points
MAX (*)
AVG
MIN (*)
TELL (*)
SUM
15. Debe calcular la desviación estándar para el costo de los productos fabricados en Birmingham. ¿Qué función de grupo utilizará?
ReplyDeleteMarcar para revisión
(1) Puntos
VAR_SAMP
STDEV
DIFERENCIA
STDDEV (*)
4. Evaluate this SELECT statement:
ReplyDeleteSELECT COUNT(*)
FROM employees
WHERE salary > 30000;
What result will the query show?
Mark for Review
(1) Points
The number of rows in the EMPLOYEES table that have a salary greater than 30,000
The number of employees with a salary of less than 30,000
The query generates an error and returns no results.
The total in the SALARY column for all employees with a salary greater than 30,000
help me I don't know the answer
The number of rows in the EMPLOYEES table that have a salary greater than 30,000
DeleteEvalúe esta sentencia SELECT:SELECT COUNT(*)FROM employeesWHERE salary > 30000;
ReplyDelete¿Qué resultado mostrará la consulta?
Marcar para Revisión
(1) Puntos
El número de empleados que tengan un salario inferior a 30.000
El número de filas de la tabla EMPLOYEES que tengan un salario superior a 30.000 (*)
La consulta genera un error y no devuelve resultados.
El total de la columna SALARY para todos los empleados que tengan un salario superior a 30.000
This comment has been removed by the author.
ReplyDeleteThe CUSTOMER table contains these columns:
ReplyDeleteCUSTOMER_ID NUMBER(9)
FIRST_NAME VARCHAR2(25)
LAST_NAME VARCHAR2(30)
CREDIT_LIMIT NUMBER (7,2)
CATEGORY VARCHAR2(20)
You need to calculate the average credit limit for all the customers in each category. The average should be calculated based on all the rows in the table excluding any customers who have not yet been assigned a credit limit value.
Which group function should you use to calculate this value?
Mark for Review
(1) Points
SUM
STDDEV
COUNT
AVG (*)
Examine the data from the LINE_ITEM table:
ReplyDeleteLINE_ITEM_ID ORDER_ID PRODUCT_ID PRICE DISCOUNT
890898 847589 848399 8.99 0.10
768385 862459 849869 5.60 0.05
867950 985490 945809 5.60
954039 439203 438925 5.25 0.15
543949 349302 453235 4.50
You query the LINE_ITEM table and a value of 5 is returned. Which SQL statement did you execute?
Mark for Review
(1) Points
SELECT COUNT(discount)
FROM line_item;
SELECT SUM(discount)
FROM line_item;
SELECT COUNT(*)
FROM line_item;(*)
SELECT AVG(discount)
FROM line_item
Evaluate this SELECT statement:
ReplyDeleteSELECT COUNT(*)
FROM employees
WHERE salary > 30000;
Which result will the query display?
Mark for Review
(1) Points
The number of rows in the EMPLOYEES table that have a salary greater than 30000 (*)
The query generates an error and returns no results.
The number of employees that have a salary less than 30000
The total of the SALARY column for all employees that have a salary greater than 30000
I want to share a testimony of how Mr. Pedro helped me with a $ 2,000,000.00 loan to fund my marijuana cultivation project, I am very grateful and promised to share this legitimate funding company with anyone looking for a way to expand their business project. . financing company. Anyone seeking financial support should contact them at pedroloanss@gmail.com.
ReplyDelete4. Given the following data in the employees table (employee_id, salary, commission_pct)
ReplyDeleteDATA: (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 sentence?
SELECT SUM (commission_pct), COUNT (commission_pct)
FROM employees
WHERE employee_id IN (143,144,149,174,176,178);
SUM = .85 and COUNT = 6
SUM = .85 and COUNT = 4 (*)
SUM = 1.85 and COUNT = 4
SUM = 1.85 and COUNT = 6
7. Which statement is true about the COUNT function?
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.
The COUNT function always ignores null values by default. (*)
The COUNT function can be used to find the maximum value in each column.
The EMPLOYEES table contains these columns:
ReplyDeleteEMPLOYEE_ID NUMBER(9)
LAST_NAME VARCHAR2(20)
FIRST_NAME VARCHAR2(20)
SALARY NUMBER(9,2)
HIRE_DATE DATE
BONUS NUMBER(7,2)
COMM_PCT NUMBER(4,2)
Which three functions could be used with the HIRE_DATE, LAST_NAME, or SALARY columns? (Choose three.)
(Choose all correct answers)
SUM
AVG
MIN (*)
MAX (*)
COUNT (*)
Which group functions below act on character, number, and date data types? (Choose three)
ReplyDelete(Choose all correct answers)
COUNT (*)
AVG
SUM
MIN (*)
MAX (*)