Halaman

Thursday, August 8, 2019

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


Section 9


The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER(9) PK
CATEGORY_ID VARCHAR2(10)
LOCATION_ID NUMBER(9)
DESCRIPTION VARCHAR2(30)
COST NUMBER(7,2)
PRICE NUMBER(7,2)
QUANTITY NUMBER

You display the total of the extended costs for each product category by location.
You need to include only the products that have a price less than $25.00.
The extended cost of each item equals the quantity value multiplied by the cost value.
Which SQL statement will display the desired result?
                                                                
                                               
                SELECT SUM(cost * quantity) TOTAL
FROM products
WHERE price < 25.00;

                                               
                SELECT category_id, SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00
GROUP BY category_id, location_id;
(*)
  
                                               
                SELECT category_id, SUM(cost * quantity) TOTAL,location_id
FROM products
WHERE price > 25.00
GROUP BY category_id, location_id;

                                               
                SELECT SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00
GROUP BY location_id;

                                                                               
Is the following statement correct?
SELECT first_name, last_name, salary, department_id, COUNT(employee_id)
FROM employees
WHERE department_id = 50
GROUP BY last_name, first_name, department_id;
                                                               
                                               
                Yes, because Oracle will correct any mistakes in the statement itself

                                               
                No, beause you cannot have a WHERE-clause when you use group functions.

                                               
                No, because the statement is missing salary in the GROUP BY clause (*)

                                               
                Yes

                                                                               
Which of the following SQL statements could display the number of people with the same last name:                                                        
                                               
                SELECT employee_id, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;

                                               
                SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name;

                                               
                SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name;
(*)
                                               
                SELECT first_name, last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;

                                                                               
What will the following SQL Statement do?
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;
                                                             
                                               
                Displays only the number of job_ids

                                               
                Displays all the jobs with as many people as there are jobs

                                               
                Displays all the employees and groups them by job

                                               
                Displays each job id and the number of people assigned to that job id (*)

                                                                               
                                                               
The PRODUCTS table contains these columns:
PROD_ID NUMBER(4)
PROD_NAME VARCHAR(20)
PROD_CAT VARCHAR2(15)
PROD_PRICE NUMBER(5)
PROD_QTY NUMBER(4)

You need to identify the minimum product price in each product category.
Which statement could you use to accomplish this task?
                                                               
                                               
                SELECT MIN (prod_price), prod_cat
FROM products
GROUP BY MIN (prod_price), prod_cat;

                                               
                SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_cat;
(*)
                                               
                SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_price;

                                               
                SELECT prod_price, MIN (prod_cat)
FROM products
GROUP BY prod_cat;

Which statement about the GROUP BY clause is true?    
                                                                               
                                               
                You must use the HAVING clause with the GROUP BY clause.

                                               
                To exclude rows before dividing them into groups using the GROUP BY clause, you should use a WHERE clause. (*)

                                               
                By default, rows are not sorted when a GROUP BY clause is used.

                                               
                You can use a column alias in a GROUP BY clause.

                                                                               
When using SET operators, the number of columns and the data types of the columns must be identical in all of the SELECT statements used in the query. True or False.   

                                               
                True (*)

                                               
                False

                                                                               
MINUS will give you rows from the first query that are not present in the second query. (True or False?)                                                                     
                                               
                True (*)

                                               
                False

                                                                               
INTERSECT will give you the common rows found in both queries. (True or False?)                                                                  
                                               
                True (*)

                                               
                False

                                                                               
If you want to include subtotals and grand totals for all columns mentioned in a GROUP BY clause, you should use which of the following extensions to the GROUP BY clause?   

                                               
                CUBE (*)

                                               
                ROLLUP

                                               
                GROUP BY ALL COLUMNS

                                               
                HAVING

Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS(.......);

Select the correct GROUP BY GROUPING SETS clause from the following list:
                                                                               
                                               
                GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_id)) (*)

                                               
                GROUP BY GROUPING SETS (department_id, salary), (department_id, job_id), (department_id, manager_id)

                                               
                GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, SUM(salary), (manager_id, job_id))

                                               
                GROUP BY GROUPING SETS (department_id, AVG(salary)), (department_id, job_id), (department_id, manager_id)

                                                                               
CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and COUNT. True or False? 
                                                                               
                                               
                True (*)

                                               
                False

                                                                               
You use GROUPING functions to ______ database rows from tabulated rows.  

                                               
                COUNT

                                               
                CREATE

                                               
                COMPUTE

                                               
                DISTINGUISH (*)

                                                                               
You use ROLLUP to:    
                                                                               
                                               
                produce subtotal values (*)

                                               
                produce a single result set

                                               
                cross-tabulate values

                                                                               
GROUPING SETS is another extension to the GROUP BY clause and is used to specify multiple groupings of data but provide a single result set. True or False? 
                                                                               
                                               
                True (*)

                                               
                False

No comments:

Post a Comment

Final Exam Java Programming 2019 Learner - English

Final Exam