Section 9
The PRODUCTS table contains these columns:
PRODUCT_ID NUMBER(9) PK
CATEGORY_ID VARCHAR2(10)
LOCATION_ID NUMBER(9)
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