7. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
What data will this query generate?
Mark for Review
(1) Points
Sum of salaries for (department_id, job_id) and (department_id, manager_id) (*)
Sum of salaries for (department_id, job_id, manager_id)
Subtotals for (job_id, manager_id)
The statement will fail.
1. 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? Mark for Review
(1) Points
HAVING
ROLLUP
CUBE (*)
GROUP BY ALL COLUMNS
2. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
What data will this query generate?
Mark for Review
(1) Points
Sum of salaries for (department_id, job_id) and (department_id, manager_id) (*)
Sum of salaries for (department_id, job_id, manager_id)
Subtotals for (job_id, manager_id)
The statement will fail.
3. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, manager_id)
What extra data will this query generate?
Mark for Review
(1) Points
Subtotals for department_id, and grand totals for salary
Subtotals for department_id, job_id and grand totals for salary
Subtotals for department_id, job_id, manager_id and grand totals for salary
The statement will fail. (*)
4. You use ROLLUP to: Mark for Review
(1) Points
produce subtotal values (*)
cross-tabulate values
produce a single result set
5. CUBE will cross-reference the columns listed in the ______ clause to create a superset of groups. Mark for Review
(1) Points
GROUP BY (*)
WHERE
SELECT
6. CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and COUNT. True or False? Mark for Review
(1) Points
True (*)
False
7. Which of the following are correct SET operators? (choose two) Mark for Review
(1) Points
(Choose all correct answers)
MINUS, PLUS
UNION, MINUS (*)
UNION ALL, PLUS ALL
UNION ALL, INTERSECT (*)
8. The ___________ operator returns all rows from both tables, after eliminating duplicates. Mark for Review
(1) Points
UNION ALL
UNION (*)
MINUS
INTERSECT
9. To control the order of rows returned using SET operators, the ORDER BY clause is used ______ and is placed in the _____ SELECT statement of the query. Mark for Review
(1) Points
ONCE; LAST (*)
IN ALL; LAST
ONCE; FIRST
TWICE; FIRST
10. If a select list contains both a column as well as a group function then what clause is required? Mark for Review
(1) Points
HAVING clause
GROUP BY clause (*)
ORDER BY clause
JOIN clause
11. 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;
Mark for Review
(1) Points
Yes
No, because the statement is missing salary in the GROUP BY clause (*)
Yes, because Oracle will correct any mistakes in the statement itself
No, beause you cannot have a WHERE-clause when you use group functions.
12. What will the following SQL Statement do?
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;
Mark for Review
(1) Points
Displays each job id and the number of people assigned to that job id (*)
Displays all the jobs with as many people as there are jobs
Displays only the number of job_ids
Displays all the employees and groups them by job
13. The PLAYERS table contains these columns:
PLAYER_ID NUMBER PK
PLAYER_NAME VARCHAR2 (30)
TEAM_ID NUMBER
HIRE_DATE DATE
SALARY NUMBER (8,2)
Which clauses represent valid uses of aggregate functions? (Choose three.)
Mark for Review
(1) Points
(Choose all correct answers)
SELECT AVG(NVL(salary, 0)) (*)
GROUP BY MAX(salary)
ORDER BY AVG(salary) (*)
WHERE hire_date > AVG(hire_date)
HAVING MAX(salary) > 10000 (*)
14. Evaluate this SELECT statement:
SELECT MIN(hire_date), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?
Mark for Review
(1) Points
The hire dates in the EMPLOYEES table that contain NULL values
The latest hire date in the EMPLOYEES table
The earliest hire date in each department (*)
The earliest hire date in the EMPLOYEES table
15. The EMPLOYEES table contains these columns:
ID_NUMBER NUMBER Primary Key
NAME VARCHAR2 (30)
DEPARTMENT_ID NUMBER
SALARY NUMBER (7,2)
HIRE_DATE DATE
Evaluate this SQL statement:
SELECT id_number, name, department_id, SUM(salary)
FROM employees
WHERE salary > 25000
GROUP BY department_id, id_number, name
ORDER BY hire_date;
Why will this statement cause an error?
Mark for Review
(1) Points
The HAVING clause is missing.
The SALARY column is NOT included in the GROUP BY clause.
The WHERE clause contains a syntax error.
The HIRE_DATE column is NOT included in the GROUP BY clause. (*)
1. Which statement about group functions is true? Mark for Review
(1) Points
Group functions can only be used in a SELECT list.
A query that includes a group function in the SELECT list must include a GROUP BY clause.
Group functions ignore null values. (*)
Group functions can be used in a WHERE clause.
2. What is the best explanation as to why this SQL statement will NOT execute?
SELECT department_id "Department", AVG (salary)"Average"
FROM employees
GROUP BY Department;
Mark for Review
(1) Points
The GROUP BY clause must have something to GROUP.
Salaries cannot be averaged as not all the numbers will divide evenly.
You cannot use a column alias in the GROUP BY clause. (*)
The department id is not listed in the departments table.
3. 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?
Mark for Review
(1) Points
SELECT prod_price, MIN (prod_cat)
FROM products
GROUP BY prod_cat;
SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_price;
SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_cat;
(*)
SELECT MIN (prod_price), prod_cat
FROM products
GROUP BY MIN (prod_price), prod_cat;
4. Evaluate this statement:
SELECT department_id, AVG(salary)
FROM employees
WHERE job_id <> 69879
GROUP BY job_id, department_id
HAVING AVG(salary) > 35000
ORDER BY department_id;
Which clauses restricts the result? Choose two.
Mark for Review
(1) Points
(Choose all correct answers)
GROUP BY job_id, department_id
WHERE job_id <> 69879 (*)
SELECT department_id, AVG(salary)
HAVING AVG(salary) > 35000 (*)
5. 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?
Mark for Review
(1) Points
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 SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00
GROUP BY location_id;
SELECT category_id, SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00
GROUP BY category_id, location_id;
(*)
6. Evaluate this SELECT statement:
SELECT COUNT(employee_id), department_id
FROM employees
GROUP BY department_id;
You only want to include employees who earn more than 15000.
Which clause should you include in the SELECT statement?
Mark for Review
(1) Points
HAVING salary > 15000
HAVING SUM(salary) > 15000
WHERE salary > 15000 (*)
WHERE SUM(salary) > 15000
7. You use GROUPING functions to: Mark for Review
(1) Points
Produce subtotal and cross-tabulated values
Identify the extra row values created by either a ROLLUP or CUBE operation (*)
Aggregate rows using SUM, MIN, MAX, and COUNT
8. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
What data will this query generate?
Mark for Review
(1) Points
Sum of salaries for (department_id, job_id) and (department_id, manager_id) (*)
Sum of salaries for (department_id, job_id, manager_id)
Subtotals for (job_id, manager_id)
The statement will fail.
9. CUBE will cross-reference the columns listed in the ______ clause to create a superset of groups. Mark for Review
(1) Points
SELECT
GROUP BY (*)
WHERE
10. You use ROLLUP to: Mark for Review
(1) Points
cross-tabulate values
produce subtotal values (*)
produce a single result set
11. 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? Mark for Review
(1) Points
True (*)
False
12. 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? Mark for Review
(1) Points
ROLLUP
GROUP BY ALL COLUMNS
HAVING
CUBE (*)
13. Which of the following are correct SET operators? (choose two) Mark for Review
(1) Points
(Choose all correct answers)
UNION, MINUS (*)
UNION ALL, PLUS ALL
MINUS, PLUS
UNION ALL, INTERSECT (*)
14. The difference between UNION and UNION ALL is Mark for Review
(1) Points
There is no difference; you get exactly the same result from both.
UNION ALL is more like a NATURAL JOIN.
UNION will remove duplicates; UNION ALL returns all rows from all queries including the duplicates. (*)
UNION is a synomym for UNION ALL.
15. When using SET operators, the names of the matching columns must be identical in all of the SELECT statements used in the query. True or False? Mark for Review
(1) Points
True
False (*)
4. Evaluate this SELECT statement:
SELECT SUM(salary), department_id, manager_id
FROM employees
GROUP BY department_id, manager_id;
Which SELECT clause allows you to restrict the rows returned, based on a group function?
Mark for Review
(1) Points
HAVING salary > 100000
HAVING SUM(salary) > 100000 (*)
WHERE salary > 100000
WHERE SUM(salary) > 100000
5. The PLAYERS and TEAMS tables contain these columns:
PLAYERS
PLAYER_ID NUMBER NOT NULL, PRIMARY KEY
LAST_NAME VARCHAR2 (30) NOT NULL
FIRST_NAME VARCHAR2 (25) NOT NULL
TEAM_ID NUMBER
POSITION VARCHAR2 (25)
TEAMS
TEAM_ID NUMBER NOT NULL, PRIMARY KEY
TEAM_NAME VARCHAR2 (25)
You need to create a report that lists the names of each team with more than three goal keepers.
Which SELECT statement will produce the desired result?
Mark for Review
(1) Points
SELECT t.team_name, COUNT(p.player_id)
FROM players p, teams t
ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name
HAVING COUNT(p.player_id) > 3;
SELECT t.team_name, COUNT(p.player_id)
FROM players p
JOIN teams t ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name
HAVING COUNT(p.player_id) > 3;
(*)
SELECT t.team_name, COUNT(p.player_id)
FROM players
JOIN teams t ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
HAVING COUNT(p.player_id) > 3;
SELECT t.team_name, COUNT(p.player_id)
FROM players p, teams t
ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name;
6. Evaluate this statement:
SELECT department_id, AVG(salary)
FROM employees
WHERE job_id <> 69879
GROUP BY job_id, department_id
HAVING AVG(salary) > 35000
ORDER BY department_id;
Which clauses restricts the result? Choose two.
Mark for Review
(1) Points
(Choose all correct answers)
WHERE job_id <> 69879 (*)
SELECT department_id, AVG(salary)
HAVING AVG(salary) > 35000 (*)
GROUP BY job_id, department_id
8. You use GROUPING functions to ______ database rows from tabulated rows. Mark for Review
(1) Points
COMPUTE
COUNT
DISTINGUISH (*)
CREATE
9. 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:
Mark for Review
(1) Points
GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_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)
GROUP BY GROUPING SETS (department_id, salary), (department_id, job_id), (department_id, manager_id)
1. How would you alter the following query to list only employees where two or more employees have the same last name?
SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
Mark for Review
(1) Points
SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
WHERE COUNT(*) > 1
GROUP BY last_name
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING COUNT(last_name) > 1;
(*)
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
EXISTS COUNT(last_name) > 1;
SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING last_name > 1;
2. Which statement about the GROUP BY clause is true? Mark for Review
(1) Points
To exclude rows before dividing them into groups using the GROUP BY clause, you should use a WHERE clause. (*)
You can use a column alias in a GROUP BY clause.
You must use the HAVING clause with the GROUP BY clause.
By default, rows are not sorted when a GROUP BY clause is used.
3. 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?
Mark for Review
(1) Points
SELECT SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00
GROUP BY location_id;
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
FROM products
WHERE price < 25.00;
10. CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and COUNT. True or False? Mark for Review
(1) Points
True (*)
False
14. MINUS will give you rows from the first query that are not present in the second query. (True or False?) Mark for Review
(1) Points
True (*)
False
15. 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. Mark for Review
(1) Points
True (*)
False
11. 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? Mark for Review
(1) Points
True (*)
False
12. 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? Mark for Review
(1) Points
HAVING
ROLLUP
CUBE (*)
GROUP BY ALL COLUMNS
13. The ___________ operator returns all rows from both tables, after eliminating duplicates. Mark for Review
(1) Points
MINUS
UNION ALL
UNION (*)
INTERSECT
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
What data will this query generate?
Mark for Review
(1) Points
Sum of salaries for (department_id, job_id) and (department_id, manager_id) (*)
Sum of salaries for (department_id, job_id, manager_id)
Subtotals for (job_id, manager_id)
The statement will fail.
1. 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? Mark for Review
(1) Points
HAVING
ROLLUP
CUBE (*)
GROUP BY ALL COLUMNS
2. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
What data will this query generate?
Mark for Review
(1) Points
Sum of salaries for (department_id, job_id) and (department_id, manager_id) (*)
Sum of salaries for (department_id, job_id, manager_id)
Subtotals for (job_id, manager_id)
The statement will fail.
3. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY ROLLUP(department_id, manager_id)
What extra data will this query generate?
Mark for Review
(1) Points
Subtotals for department_id, and grand totals for salary
Subtotals for department_id, job_id and grand totals for salary
Subtotals for department_id, job_id, manager_id and grand totals for salary
The statement will fail. (*)
4. You use ROLLUP to: Mark for Review
(1) Points
produce subtotal values (*)
cross-tabulate values
produce a single result set
5. CUBE will cross-reference the columns listed in the ______ clause to create a superset of groups. Mark for Review
(1) Points
GROUP BY (*)
WHERE
SELECT
6. CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and COUNT. True or False? Mark for Review
(1) Points
True (*)
False
7. Which of the following are correct SET operators? (choose two) Mark for Review
(1) Points
(Choose all correct answers)
MINUS, PLUS
UNION, MINUS (*)
UNION ALL, PLUS ALL
UNION ALL, INTERSECT (*)
8. The ___________ operator returns all rows from both tables, after eliminating duplicates. Mark for Review
(1) Points
UNION ALL
UNION (*)
MINUS
INTERSECT
9. To control the order of rows returned using SET operators, the ORDER BY clause is used ______ and is placed in the _____ SELECT statement of the query. Mark for Review
(1) Points
ONCE; LAST (*)
IN ALL; LAST
ONCE; FIRST
TWICE; FIRST
10. If a select list contains both a column as well as a group function then what clause is required? Mark for Review
(1) Points
HAVING clause
GROUP BY clause (*)
ORDER BY clause
JOIN clause
11. 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;
Mark for Review
(1) Points
Yes
No, because the statement is missing salary in the GROUP BY clause (*)
Yes, because Oracle will correct any mistakes in the statement itself
No, beause you cannot have a WHERE-clause when you use group functions.
12. What will the following SQL Statement do?
SELECT job_id, COUNT(*)
FROM employees
GROUP BY job_id;
Mark for Review
(1) Points
Displays each job id and the number of people assigned to that job id (*)
Displays all the jobs with as many people as there are jobs
Displays only the number of job_ids
Displays all the employees and groups them by job
13. The PLAYERS table contains these columns:
PLAYER_ID NUMBER PK
PLAYER_NAME VARCHAR2 (30)
TEAM_ID NUMBER
HIRE_DATE DATE
SALARY NUMBER (8,2)
Which clauses represent valid uses of aggregate functions? (Choose three.)
Mark for Review
(1) Points
(Choose all correct answers)
SELECT AVG(NVL(salary, 0)) (*)
GROUP BY MAX(salary)
ORDER BY AVG(salary) (*)
WHERE hire_date > AVG(hire_date)
HAVING MAX(salary) > 10000 (*)
14. Evaluate this SELECT statement:
SELECT MIN(hire_date), department_id
FROM employees
GROUP BY department_id;
Which values are displayed?
Mark for Review
(1) Points
The hire dates in the EMPLOYEES table that contain NULL values
The latest hire date in the EMPLOYEES table
The earliest hire date in each department (*)
The earliest hire date in the EMPLOYEES table
15. The EMPLOYEES table contains these columns:
ID_NUMBER NUMBER Primary Key
NAME VARCHAR2 (30)
DEPARTMENT_ID NUMBER
SALARY NUMBER (7,2)
HIRE_DATE DATE
Evaluate this SQL statement:
SELECT id_number, name, department_id, SUM(salary)
FROM employees
WHERE salary > 25000
GROUP BY department_id, id_number, name
ORDER BY hire_date;
Why will this statement cause an error?
Mark for Review
(1) Points
The HAVING clause is missing.
The SALARY column is NOT included in the GROUP BY clause.
The WHERE clause contains a syntax error.
The HIRE_DATE column is NOT included in the GROUP BY clause. (*)
1. Which statement about group functions is true? Mark for Review
(1) Points
Group functions can only be used in a SELECT list.
A query that includes a group function in the SELECT list must include a GROUP BY clause.
Group functions ignore null values. (*)
Group functions can be used in a WHERE clause.
2. What is the best explanation as to why this SQL statement will NOT execute?
SELECT department_id "Department", AVG (salary)"Average"
FROM employees
GROUP BY Department;
Mark for Review
(1) Points
The GROUP BY clause must have something to GROUP.
Salaries cannot be averaged as not all the numbers will divide evenly.
You cannot use a column alias in the GROUP BY clause. (*)
The department id is not listed in the departments table.
3. 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?
Mark for Review
(1) Points
SELECT prod_price, MIN (prod_cat)
FROM products
GROUP BY prod_cat;
SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_price;
SELECT prod_cat, MIN (prod_price)
FROM products
GROUP BY prod_cat;
(*)
SELECT MIN (prod_price), prod_cat
FROM products
GROUP BY MIN (prod_price), prod_cat;
4. Evaluate this statement:
SELECT department_id, AVG(salary)
FROM employees
WHERE job_id <> 69879
GROUP BY job_id, department_id
HAVING AVG(salary) > 35000
ORDER BY department_id;
Which clauses restricts the result? Choose two.
Mark for Review
(1) Points
(Choose all correct answers)
GROUP BY job_id, department_id
WHERE job_id <> 69879 (*)
SELECT department_id, AVG(salary)
HAVING AVG(salary) > 35000 (*)
5. 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?
Mark for Review
(1) Points
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 SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00
GROUP BY location_id;
SELECT category_id, SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00
GROUP BY category_id, location_id;
(*)
6. Evaluate this SELECT statement:
SELECT COUNT(employee_id), department_id
FROM employees
GROUP BY department_id;
You only want to include employees who earn more than 15000.
Which clause should you include in the SELECT statement?
Mark for Review
(1) Points
HAVING salary > 15000
HAVING SUM(salary) > 15000
WHERE salary > 15000 (*)
WHERE SUM(salary) > 15000
7. You use GROUPING functions to: Mark for Review
(1) Points
Produce subtotal and cross-tabulated values
Identify the extra row values created by either a ROLLUP or CUBE operation (*)
Aggregate rows using SUM, MIN, MAX, and COUNT
8. Examine the following statement:
SELECT department_id, manager_id, job_id, SUM(salary)
FROM employees
GROUP BY GROUPING SETS((department_id, manager_id), (department_id, job_id))
What data will this query generate?
Mark for Review
(1) Points
Sum of salaries for (department_id, job_id) and (department_id, manager_id) (*)
Sum of salaries for (department_id, job_id, manager_id)
Subtotals for (job_id, manager_id)
The statement will fail.
9. CUBE will cross-reference the columns listed in the ______ clause to create a superset of groups. Mark for Review
(1) Points
SELECT
GROUP BY (*)
WHERE
10. You use ROLLUP to: Mark for Review
(1) Points
cross-tabulate values
produce subtotal values (*)
produce a single result set
11. 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? Mark for Review
(1) Points
True (*)
False
12. 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? Mark for Review
(1) Points
ROLLUP
GROUP BY ALL COLUMNS
HAVING
CUBE (*)
13. Which of the following are correct SET operators? (choose two) Mark for Review
(1) Points
(Choose all correct answers)
UNION, MINUS (*)
UNION ALL, PLUS ALL
MINUS, PLUS
UNION ALL, INTERSECT (*)
14. The difference between UNION and UNION ALL is Mark for Review
(1) Points
There is no difference; you get exactly the same result from both.
UNION ALL is more like a NATURAL JOIN.
UNION will remove duplicates; UNION ALL returns all rows from all queries including the duplicates. (*)
UNION is a synomym for UNION ALL.
15. When using SET operators, the names of the matching columns must be identical in all of the SELECT statements used in the query. True or False? Mark for Review
(1) Points
True
False (*)
4. Evaluate this SELECT statement:
SELECT SUM(salary), department_id, manager_id
FROM employees
GROUP BY department_id, manager_id;
Which SELECT clause allows you to restrict the rows returned, based on a group function?
Mark for Review
(1) Points
HAVING salary > 100000
HAVING SUM(salary) > 100000 (*)
WHERE salary > 100000
WHERE SUM(salary) > 100000
5. The PLAYERS and TEAMS tables contain these columns:
PLAYERS
PLAYER_ID NUMBER NOT NULL, PRIMARY KEY
LAST_NAME VARCHAR2 (30) NOT NULL
FIRST_NAME VARCHAR2 (25) NOT NULL
TEAM_ID NUMBER
POSITION VARCHAR2 (25)
TEAMS
TEAM_ID NUMBER NOT NULL, PRIMARY KEY
TEAM_NAME VARCHAR2 (25)
You need to create a report that lists the names of each team with more than three goal keepers.
Which SELECT statement will produce the desired result?
Mark for Review
(1) Points
SELECT t.team_name, COUNT(p.player_id)
FROM players p, teams t
ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name
HAVING COUNT(p.player_id) > 3;
SELECT t.team_name, COUNT(p.player_id)
FROM players p
JOIN teams t ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name
HAVING COUNT(p.player_id) > 3;
(*)
SELECT t.team_name, COUNT(p.player_id)
FROM players
JOIN teams t ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
HAVING COUNT(p.player_id) > 3;
SELECT t.team_name, COUNT(p.player_id)
FROM players p, teams t
ON (p.team_id = t.team_id)
WHERE UPPER(p.position) = 'GOAL KEEPER'
GROUP BY t.team_name;
6. Evaluate this statement:
SELECT department_id, AVG(salary)
FROM employees
WHERE job_id <> 69879
GROUP BY job_id, department_id
HAVING AVG(salary) > 35000
ORDER BY department_id;
Which clauses restricts the result? Choose two.
Mark for Review
(1) Points
(Choose all correct answers)
WHERE job_id <> 69879 (*)
SELECT department_id, AVG(salary)
HAVING AVG(salary) > 35000 (*)
GROUP BY job_id, department_id
8. You use GROUPING functions to ______ database rows from tabulated rows. Mark for Review
(1) Points
COMPUTE
COUNT
DISTINGUISH (*)
CREATE
9. 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:
Mark for Review
(1) Points
GROUP BY GROUPING SETS ((department_id, manager_id), (department_id, job_id), (manager_id, job_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)
GROUP BY GROUPING SETS (department_id, salary), (department_id, job_id), (department_id, manager_id)
1. How would you alter the following query to list only employees where two or more employees have the same last name?
SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
GROUP BY last_name;
Mark for Review
(1) Points
SELECT last_name, COUNT(employee_id)
FROM EMPLOYEES
WHERE COUNT(*) > 1
GROUP BY last_name
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING COUNT(last_name) > 1;
(*)
SELECT last_name, COUNT(last_name)
FROM EMPLOYEES
GROUP BY last_name
EXISTS COUNT(last_name) > 1;
SELECT employee_id, DISTINCT(last_name)
FROM EMPLOYEES
GROUP BY last_name
HAVING last_name > 1;
2. Which statement about the GROUP BY clause is true? Mark for Review
(1) Points
To exclude rows before dividing them into groups using the GROUP BY clause, you should use a WHERE clause. (*)
You can use a column alias in a GROUP BY clause.
You must use the HAVING clause with the GROUP BY clause.
By default, rows are not sorted when a GROUP BY clause is used.
3. 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?
Mark for Review
(1) Points
SELECT SUM(cost * quantity) TOTAL, location_id
FROM products
WHERE price < 25.00
GROUP BY location_id;
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
FROM products
WHERE price < 25.00;
10. CUBE can be applied to all aggregate functions including AVG, SUM, MIN, MAX, and COUNT. True or False? Mark for Review
(1) Points
True (*)
False
14. MINUS will give you rows from the first query that are not present in the second query. (True or False?) Mark for Review
(1) Points
True (*)
False
15. 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. Mark for Review
(1) Points
True (*)
False
11. 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? Mark for Review
(1) Points
True (*)
False
12. 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? Mark for Review
(1) Points
HAVING
ROLLUP
CUBE (*)
GROUP BY ALL COLUMNS
13. The ___________ operator returns all rows from both tables, after eliminating duplicates. Mark for Review
(1) Points
MINUS
UNION ALL
UNION (*)
INTERSECT
Thanks for taking the time to discuss this, I feel strongly about it and love reading more on this topic. Check out my blog if you want a staff for hire
ReplyDelete