Section 8 Quiz Tüm Soruların Cevapları
1. The AVG, SUM, VARIANCE, and STDDEV functions can be used with which of the following?
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?
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?
(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.
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?
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);
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?
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?
COUNT
SUM (*)
VARIANCE
MAX
9. Evaluate this SELECT statement:
SELECT COUNT(*)
FROM products;
Which statement is true?
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?
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?
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:
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?
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?
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;
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?
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?
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?
SUM (*)
MAX
VARIANCE
COUNT
4. You need to calculate the average salary of employees in each department. Which group function will you use?
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?
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?
MIN (*)
MAX
COUNT
AVG
7. The AVG, SUM, VARIANCE, and STDDEV functions can be used with which of the following?
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?
COUNT
MAX
AVG
SUM (*)
9. Evaluate this SELECT statement:
SELECT COUNT(*)
FROM products;
Which statement is true?
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?
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?
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?
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?
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?
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?
7 (*)
6
The statement will NOT execute successfully.
0
1. 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);
1.2125
This statement is invalid
0.2125 (*)
0.0425
2. Which group function would you use to display the average price of all products in the PRODUCTS table?
MAX
COUNT
AVG (*)
SUM
3. Which group function would you use to display the lowest value in the SALES_AMOUNT column?
AVG
MAX
MIN (*)
COUNT
4. 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.
Both the average price and the average quantity of the products are returned.
Only the average quantity of the products is returned.
An error occurs. (*)
6. The EMPLOYEES table contains these columns:
EMPLOYEE_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.)
(Tüm doğru yanıtları seçin)
MAX (*)
SUM
MIN (*)
COUNT (*)
AVG
7. 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?
(Tüm doğru yanıtları seçin)
SELECT MIN(AVG(order_amount)) (*)
SELECT SUM(order_amount) (*)
FROM MAX(order_dt)
WHERE MAX(order_dt) = order_dt
SELECT SUM(order_dt)
8. You can use GROUP functions in all clauses of a SELECT statement. True or False?
Doğru
Yanlış (*)
9. To include null values in the calculations of a group function, you must:
Precede the group function name with NULL
Group functions can never use null values
Count the number of null values in that column using COUNT
Convert the null to a value using the NVL( ) function (*)
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?
SELECT COUNT(*)
FROM employees
WHERE salary > 50000;
(*)
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;
11. 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;
Yes
The first statement is invalid
No (*)
The second statement is invalid
12. Examine the data from the LINE_ITEM table:
LINE_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?
SELECT SUM(discount)
FROM line_item;
SELECT COUNT(discount)
FROM line_item;
SELECT AVG(discount)
FROM line_item;
SELECT COUNT(*)
FROM line_item;
(*)
13. Which SELECT statement will calculate the number of rows in the PRODUCTS table?
SELECT ROWCOUNT FROM products;
SELECT COUNT (*) FROM products; (*)
SELECT COUNT FROM products;
SELECT COUNT(products);
14. Evaluate this SELECT statement:
SELECT COUNT(*)
FROM employees
WHERE salary > 30000;
Which result will the query display?
The number of employees that have a salary less than 30000
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 total of the SALARY column for all employees that have a salary greater than 30000
15. What would the following SQL statement return?
SELECT COUNT(DISTINCT salary)
FROM employees;
The total amount of salaries in the employees table
A listing of all unique salaries in the employees table
The number of unique salaries in the employees table (*)
The total number of rows in the employees table
Yorumlar
Yorum Gönder