Halaman

Thursday, August 8, 2019

Answer Sections 8 Quiz Database Programming with SQL 2019 Learner - English


Section 8


                What would the following SQL statement return?
SELECT COUNT(DISTINCT salary)
FROM employees;                                                                         
              
                                 
                A listing of all unique salaries in the employees table

                                               
                The number of unique salaries in the employees table (*)

                                               
                The total amount of salaries in the employees table

                                               
                The total number of rows in the employees table


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;

                                                                               
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?                                                                     
                                               
                The statement will NOT execute successfully.

                                               
                7 (*)

                                               
                6

                                               
                0

                                                                               
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(commission_pct)
FROM employees
WHERE employee_id IN( 143,144,149,174,176,178);
                                                                               
                                               
                SUM = .85 and COUNT = 6

                                               
                SUM = 1.85 and COUNT = 4

                                               
                SUM = .85 and COUNT = 4 (*)

                                               
                SUM = 1.85 and COUNT = 6

                                                                               
Evaluate this SQL statement:
SELECT COUNT (amount)
FROM inventory;

What will occur when the statement is issued?
                                                                               
                                               
                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. (*)

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

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

Evaluate this SELECT statement:
SELECT COUNT(*)
FROM products;

Which statement is true?
                                                                  
                                               
                An error occurs due to an error in the SELECT clause.

                                               
                The number of unique PRODUCT_IDs in the table is displayed.

                                               
                An error occurs because no WHERE clause is included in the SELECT statement.

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

                                                                               
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 first statement is invalid

                                               
                No (*)

                                               
                The second statement is invalid

                                               
                Yes

                                                                               
What two group functions can be used with any datatype?       

                                               
                SUM, AVG

                                               
                STDDEV, VARIANCE

                                               
                MIN, MAX (*)

                                               
                COUNT, SUM

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

                                               
                AVG

                                               
                MAX (*)

                                               
                COUNT

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

                                               
                MEDIAN

                                               
                MEAN

                                               
                AVERAGE

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.0425

                                               
                1.2125

                                               
                This statement is invalid

                                               
                0.2125 (*)

                                                                               
The following statement will work, even though it contains more than one GROUP function:
SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary)
FROM employees;
True or False?
                                                                               
                                               
                True (*)

                                               
                False

                                                                               
You can use GROUP functions in all clauses of a SELECT statement. True or False?  
                                                                               
                                               
                True

                                               
                False (*)

                                                                               
Which aggregate function can be used on a column of the DATE data type? 
                                                             
                                               
                AVG

                                               
                STDDEV

                                               
                MAX (*)

                                               
                SUM

                                                                               
You need to compute the total salary amount for all employees in department 10. Which group function will you use? 
                                                                               
                                               
                COUNT

                                               
                MAX

                                               
                SUM (*)

                                               
                VARIANCE

No comments:

Post a Comment

Final Exam Java Programming 2019 Learner - English

Final Exam