WorksheetsSQL: GROUP Functions
Total questions: 97
Worksheet time: 49mins
Which of the following is NOT a GROUP BY function?
MAX
MIN
NVL
AVG
Which of the following functions can be used without GROUP BY clause in SELECT query? (Select all that apply)
COUNT
MAX
MIN
AVG
Which of the following SELECT query returns the department number with maximum salary compensated to an employee? (Consider the table structure as given)
SELECT department_id , max(salary ) FROM employees ;
SELECT department_id , max(salary ) FROM employees GROUP BY department_id ;
SELECT max(salary ) FROM employees GROUP BY department_id ;
SELECT max(salary ) FROM employees ;
Which of the following statements are true about the COUNT function?
The COUNT function counts the number of rows
The COUNT(*) function counts the number of rows with duplicates and NULL values
The COUNT(DISTINCT) function counts the number of distinct rows
COUNT(*) is equivalent to COUNT(ALL)
What are the appropriate data types accepted by GROUP BY functions?
Nested Tables
NUMBER
CLOB
DATE
A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.
SELECT COUNT (*) FROM t_count;
12
6
9
Throws exception because COUNT function doesn't works with NULL values
A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.
SELECT COUNT (num) FROM t_count;
12
6
9
Throws exception because COUNT function doesn't works with NULL values
A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.
SELECT COUNT (ALL num) FROM t_count;
12
6
9
Throws exception because COUNT function doesn't works with NULL values
A table T_COUNT has 12 number values as 1, 2, 3, 32, 1, 1, null, 24, 12, null, 32, null. Predict the output of the below query.
SELECT COUNT (DISTINCT num) FROM t_count;
12
6
9
Throws exception because COUNT function doesn't works with NULL values
What happens when the below query is executed in SQL* Plus?
SELECT COUNT() FROM dual;
Executes successfully and returns no output
Executes successfully and returns output as '1'
Throws exception "ORA-00909: invalid number of arguments"
Throws exception "ORA-00904: "COUNT": invalid identifier" because COUNT function doesn't works with DUAL table
Here are few statements about VARIANCE function in SQL.:
i. The function accepts multiple numeric inputs and returns variance of all the values
ii. The function accepts a number column and returns variance of all column values including NULLs
iii. The function accepts a number column and returns variance of all column values excluding NULLs
Chose the correct combination from the below options:
i and iii
i and ii
ii
iii
Which of the following is NOT a GROUP BY extension in SQL?
GROUP BY
GROUPING SETS
CUBE
ROLLUP
SUM is a group by function because it processes group of employees working in a department
SUM is an aggregate function because it produces one result per group of data
SUM is a single row function because it returns single value for a group i.e. department
SUM is a group by extension function because it uses GROUP BY clause to logically group the departments
Which clause is used to filter the query output based on aggregated results using a group by function?
WHERE
LIMIT
GROUP WHERE
HAVING
Examine the given table structure and predict the outcome of the following query.
The query returns the number of employees who have no commission
The query throws error because equal sign cannot be used when searching for NULL value
The query returns the number of employees in a department whose commission is NULL value
The query throws error because GROUP BY clause is missing in the query
Which of the following statements is true about the group functions?
The MIN function can be used only with numeric data.
The MAX function can be used only with date values.
The AVG function can be used only with numeric data.
The SUM function can't be part of a nested function.
Which of the following is a valid SELECT statement?
SELECT AVG(retail-cost) FROM books GROUP BY category;
SELECT category, AVG(retail-cost) FROM books;
SELECT category, AVG(retail-cost) FROM books WHERE AVG(retail-cost) > 8.56 GROUP BY category;
SELECT category, AVG(retail-cost) Profit FROM books GROUP BY category HAVING profit > 8.56;
Which of the following statements is correct?
The WHERE clause can contain a group function only if the function isn't also listed in the SELECT clause.
Group functions can't be used in the SELECT, FROM, or WHERE clauses.
The HAVING clause is always processed before the WHERE clause.
The GROUP BY clause is always processed before the HAVING clause.
Which of the following is not a valid SQL statement?
SELECT MIN(pubdate) FROM books GROUP BY category HAVING pubid = 4;
SELECT MIN(pubdate) FROM books WHERE category = 'COOKING';
SELECT COUNT(*) FROM orders WHERE customer# = 1005;
SELECT MAX(COUNT(customer#)) FROM orders GROUP BY customer#;
Which of the following statements is correct? (Select all that apply)
The COUNT function can be used to determine how many rows contain a NULL value.
Only distinct values are included in group functions, unless the ALL keyword is included in the SELECT clause.
The WHERE clause restricts which rows are processed.
The HAVING clause determines which groups are displayed in the query results.
Which of the following is a valid SQL statement?
SELECT customer#, order#, MAX(shipdate-orderdate) FROM orders GROUP BY customer# WHERE customer# = 1001;
SELECT customer#, COUNT(order#) FROM orders GROUP BY customer#;
SELECT customer#, COUNT(order#) FROM orders GROUP BY COUNT(order#);
SELECT customer#, COUNT(order#) FROM orders GROUP BY order#;
Which of the following SELECT statements lists only the book with the largest profit?
SELECT title, MAX(retail-cost) FROM books GROUP BY title;
SELECT title, MAX(retail-cost) FROM books GROUP BY title HAVING MAX(retail-cost);
SELECT title, MAX(retail-cost) FROM books;
None of the above
Which of the following statement(s) is/are correct?
1. A group function can be nested inside a group function.
2. A group function can be nested inside a single-row function.
3. A single-row function can be nested inside a group function.
1
2
3
1 and 3
Which of the following functions is used to calculate the total value stored in a specified column?
COUNT
ADD
TOTAL
SUM
Which of the following SELECT statements lists the highest retail price of all books in the Family category?
SELECT MAX(retail) FROM books WHERE category = 'FAMILY';
SELECT MAX(retail) FROM books HAVING category = 'FAMILY';
SELECT retail FROM books WHERE category = 'FAMILY' HAVING MAX(retail);
None of the above
Which of the following functions can be used to include NULL values in calculations?
SUM
NVL
MAX
MIN
Which of the following is not a valid statement?
You must enter the ALL keyword in a group function to include all duplicate values.
The AVG function can be used to find the average calculated difference between two dates.
The MIN and MAX functions can be used on VARCHAR2 columns.
All of the above
Which of the following SQL statements determines how many total customers were referred by other customers?
SELECT customer#, SUM(referred) FROM customers GROUP BY customer#;
SELECT COUNT(referred) FROM customers;
SELECT COUNT(*) FROM customers;
SELECT COUNT(*) FROM customers WHERE referred IS NULL;
Determine the correct order of execution of following clauses in a SELECT statement.
1.SELECT
2.FROM
3.WHERE
4.GROUP BY
5.HAVING
6.ORDER BY
2-3-4-5-1-6
1-2-3-4-5-6
6-5-4-3-2-1
5-4-2-3-1-6
Which of the below clauses is used to group a set of rows based on a column or set of columns?
HAVING
WHERE
GROUP BY
GROUPING
Which of the following group functions can be used for population variance and population standard deviation problems? (Select all that apply)
VAR_POP
STDDEV_POP
VARIANCE
STDDEV_SASMP
Select the positions in a SELECT query where a group function can appear. (Select all that apply)
SELECT statement
WHERE clause
ORDER BY clause
GROUP BY clause
Examine the structure of the EMPLOYEES table as given. Which query will return the minimum salary in each department?
SELECT department_id , MIN (salary ) from EMPLOYEES ;
SELECT department_id , MIN (salary ) from EMPLOYEES GROUP BY department_id ;
SELECT department_id , MIN (salary ) from EMPLOYEES GROUP BY salary ;
SELECT department_id , MIN (salary ) from EMPLOYEES GROUP BY employee_id ;
Examine the structure for the table EMPLOYEES and Interpret the output of the below query
It throws error because only one aggregate function can be used in a query.
It throws error because GROUP BY clause is missing.
It executes successfully and returns same values for both.
It executes successfully where COUNT(*) including NULLs and COUNT(all comm) excluding NULLs.
Which of the following are true about group functions?
You can use group functions in any clause of a SELECT statement.
You can use group functions only in the column list of the select clause and in the WHERE clause of a SELECT statement.
You can mix single row columns with group functions in the column list of a SELECT statement by grouping on the single row columns.
You can pass column names, expressions, constants, or functions as parameter to an group function.
Examine the structure of the table EMPLOYEES as given. You want to create a "emp_dept_sales" view by executing the following SQL statements. Which statement is true regarding the execution of the above statement?
The view will be created and you can perform DLM operations on the view
The view will not be created because the join statements are not allowed for creating a view
The view will not be created because the GROUP BY clause is not allowed for creating a view
The view will be created but no DML operations will be allowed on the view
Which of the following statements are true regarding views? (Select all that apply)
A sub query that defines a view cannot include the GROUP BY clause
A view is created with the sub query having the DISTINCT keyword can be updated
A Data Manipulation Language (DML) operation can be performed on a view that is created with the sub query having all the NOT NULL columns of a table
A view that is created with the sub query having the pseudo column ROWNUM keyword cannot be updated
Examine the table structure as given.
WHERE
SELECT
ORDER BY
GROUP BY
Examine the table structure as given. Which of the below SELECT query will display the maximum and minimum salary earned by each job category?
SELECT job, MAX(salary ), MIN (salary ) FROM employees GROUP BY department_id ;
SELECT job, MAX(salary ), MIN (salary ) FROM employees GROUP BY job;
SELECT job, MAX(salary ), MIN (salary ) FROM employees ;
Two aggregate functions cannot be used together in SELECT statement.
Consider the table structure as given.
It executes successfully and generates the required result.
It produces an error because COUNT(*) should be specified in the SELECT clause also.
It executes successfully but produces no result because COUNT(prod_id) should be used instead of COUNT(*).
It produces an error because COUNT(*) should be only in the HAVING clause and not in the WHERE clause.
Examine the table structure as given.
It executes successfully and lists the count of employees under each job category but ignores the HAVING clause since "salary " is not in GROUP BY clause.
It throws error because HAVING clause is invalid.
It throws error because "salary " is not included in the GROUP BY clause.
It executes successfully and lists the count of employees under each category having sum of salary greater than 5000.
What is true of using group functions on columns that contain NULL values?
Group functions on columns ignore NULL values.
Group functions on columns returning dates include NULL values.
Group functions on columns returning numbers include NULL values.
Group functions on columns cannot be accurately used on columns that contain NULL values.
Which of the following statetments are true about the usage of GROUP BY columns in a subquery?
Subqueries can contain GROUP BY and ORDER BY clauses.
Subqueries cannot contain GROUP BY and ORDER BY clauses.
Subqueries can contain ORDER BY but not the GROUP BY clause.
Subqueries cannot contain ORDER BY but can have GROUP BY clause.
Predict the outcome of the below query
SELECT avg(max(salary )) FROM employees GROUP BY department_id HAVING avg(max(salary ))>100;
It executes successfully.
It gives an error because the HAVING clause is not valid.
It gives an error because the GROUP BY expression is not valid.
It gives an error because aggregate functions cannot be nested in SELECT statement.
Predict the output of the below query
SELECT avg(salary ), department_id FROM employees GROUP BY department_id ;
It gives error because an aggregate function cannot appear just after SELECT clause.
It gives error because GROUP BY clause is invalid.
It executes without errors but produces no output.
It executes successfully and gives average salary in each department.
Predict the output of the below query
SELECT lower(job),avg(salary ) FROM employees GROUP BY upper(job);
It executes successfully and displays "job" in lower case.
It executes successfully but display "job" in original case.
It throws error because singe row and aggregate functions cannot be used together.
It throws error because case conversion in the SELECT list mismatches with the case conversion GROUP BY clause.
Which of the below query executes successfully? (Select all that apply)
SELECT employee_id , COUNT(hiredate-sysdate) FROM employees ;
SELECT AVG(salary ), MAX(salary ) FROM employees ;
SELECT AVG(salary ), MAX(salary ) FROM employees GROUP BY department_id ;
SELECT AVG(hiredate) FROM employees ;
Identify the error in the below SELECT statement.
SELECT department_id , AVG (salary ) FROM employees GROUP BY department_id HAVING department_id > 10;
It executes successfully and displays average salary of departments higher than 10.
It throws error because non aggregated column cannot be used in HAVING clause.
It executes successfully but displays wrong result for the departments.
It throws error because HAVING clause must be placed before GROUP BY clause.
Predict the output of the below query
SELECT department_id , AVG (salary ) FROM employees GROUP BY department_id HAVING (department_id >10 and AVG(salary )>2000);
It throws error because multiple conditions cannot be given in HAVING clause.
It throws error because a non aggregate column cannot be used in HAVING clause.
It executes successfully and displays average salary of department higher than 10 and greater than 2000.
It executes successfully but no result is displayed.
Which of the following group functions can be used with DATE values? (Select all that apply)
AVG
MIN
SUM
COUNT
Which of the following statements are true? (Select all that apply)
AVG and SUM can be used only with numeric data types.
STDDEV and VARIANCE can be used only with numeric data types.
MAX can be used with LONG data type.
MAX and MIN cannot be used with LOB or LONG data types.
Examine the table structure as given.
Multiple aggregate functions cannot be used in a single SELECT query
GROUP BY clause is invalid
COUNT function cannot be used with DATE values
No errors and it executes successfully
Which of the following group function can be used with LOB data types?
MAX
MIN
COUNT
None of these
Examine the table structure as given.
Both the queries produce same result
Query - 1 and Query - 2 produce different results because Query-1 considers NULL values of COMM and Query-2 substitutes NULL values of COMM with zero
Query - 1 produces error because COMM has NULL values
Query - 2 produces error because NVL cannot be nested with aggregate function.
Choose the correct statements about the GROUP BY clause. (Select all that apply)
Column alias can be used in the GROUP BY clause.
GROUP BY column must be in the SELECT clause.
GROUP BY clause must appear together with HAVING clause a SELECT query.
GROUP BY clause must appear after WHERE clause in a SELECT query.
Examine the table structure as given.
It throws error because GROUP BY column list doesn't matches with SELECT column list.
It executes successfully and produces average salary of a job category in each department.
It executes successfully and produces average salary for a department in each job category.
It throws error because GROUP BY and ORDER BY clause have different list of columns.
Which clause should you use to exclude group results in a query using group functions?
WHERE
HAVING
GROUP BY
ORDER BY
Predict the outcome of the below query
SELECT department_id ,avg(salary ) FROM employees HAVING avg(salary )>2000 GROUP BY department_id ORDER BY department_id
It executes successfully.
It throws error because HAVING clause precedes the GROUP BY clause.
It throws error because HAVING clause uses the aggregate function.
It executes but no results are displayed because HAVING clause precedes the GROUP BY clause.
Predict the outcome of the below query
SELECT department_id , COUNT(first_name ) FROM employees WHERE job IN ('SALESMAN','CLERK','MANAGER','ANALYST') GROUP BY department_id HAVING AVG(salary ) BETWEEN 2000 AND 3000;
It returns an error because the BETWEEN operator cannot be used in the HAVING clause.
It returns an error because WHERE and HAVING clauses cannot be used in the same SELECT statement.
It returns an error because WHERE and HAVING clauses cannot be used to apply conditions on the same column.
It executes successfully.
Which statements are true regarding the WHERE and HAVING clauses in a SELECT statement? (Select all that apply)
The HAVING clause can be used with group functions in subqueries.
The WHERE clause can be used to exclude rows after dividing them into groups.
The WHERE clause can be used to exclude rows before dividing them into groups.
The WHERE and HAVING clauses can be used in the same statement only if they are applied to different columns in the table.
Predict the outcome of the below query.
SELECT department_id , avg(salary ) FROM employees HAVING avg(salary ) > min(salary ) GROUP BY department_id ;
It throws an error because the aggregate functions used in HAVING clause must be in SELECT list.
It throws an error because the HAVING clause appears before GROUP BY clause.
It displays the departments whose average salary is greater than the minimum salary of the department.
It displays the departments whose average salary is greater than the minimum salary of the organization.
Interpret the output of the below query.
SELECT SUM(AVG(LENGTH(first_name ))) FROM employees GROUP BY department_id ;
It calculates the sum of averages of length of employee's name in each department.
It calculates the average length of employee's name in each department.
It throws error because single row function cannot be used with group functions.
It throws error because group column DEPARTMENT_ID is not used in the SELECT list.
Up to how many levels, the group functions can be nested?
1
2
3
No limits
What is the limit of number of groups within the groups created by GROUP BY clause?
1
2
3
No limits
Choose the correct statements about the HAVING clause. (Select all that apply)
The HAVING clause is an optional clause in SELECT statement.
The HAVING clause is a mandatory clause if SELECT statement uses a GROUP BY clause.
The HAVING clause can appear in a SELECT statement only if it uses a GROUP BY clause.
The HAVING clause is a mandatory clause if SELECT statement uses a GROUP BY clause.
What is the output of the below query.
SELECT count(*) FROM dual GROUP BY dummy;
1
0
NULL
Throws error because group functions cannot be applied on DUAL table.
What is the output of the below query?
SELECT SUM (salary ) FROM employees ;
NULL
14000
19000
0
What is the output of the below query?
SELECT AVG (salary ) FROM employees ;
100
736.84
NULL
0
What is the output of the below query?
SELECT AVG (nvl(salary ,0)) FROM employees ;
1000
NULL
736.84
0
What is the output of the below query?
SELECT VARIANCE (salary ) FROM employees ;
100
0
NULL
204678.36
What is the output of the below query?
SELECT VARIANCE (nvl(salary ,0)) FROM employees ;
1000
0
NULL
204678.36
What is the output of the below query?
SELECT STDDEV (salary ) FROM employees ;
1
1000
0
NULL
What is the output of the below query?
SELECT STDDEV (nvl(salary ,0)) FROM employees ;
0
452.41
1000
NULL
What is the output of the below query?
SELECT COUNT(*), COUNT(salary) FROM employees ;
19, 19
14, 19
19, 14
14, 14
Examine the table structure as given. Which of the below query will give the department who have more than 5 employees working in it?
SELECT department_id FROM employees WHERE COUNT(*) > 5 GROUP BY department_id ;
SELECT department_id FROM employees HAVING COUNT(*) > 5;
SELECT department_id FROM employees GROUP BY employee_id HAVING COUNT(*) > 5;
SELECT department_id FROM employees GROUP BY department_id HAVING COUNT(*) > 5;
Which of the following are true about the CUBE extension of GROUP BY?
Enables performing multiple GROUP BY clauses with a single query.
Performs aggregations for all possible combinations of columns included.
Performs increasing levels of cumulative subtotals, based on the provided column list.
None of the above
Which line of the SELECT statement is used to restrict the number of records the query processes?
1
3
4
5
Which line of the SELECT statement is used to restrict groups displayed in the query results?
1
3
4
5
Which line of the SELECT statement is used to group data stored in the database?
1
3
4
5
Which clause must be included for the query to execute successfully?
1
3
4
5
What is the purpose of using COUNT(*) in the SELECT query?
The number of records in the specified tables
The number of orders placed by each customer
The number of NULL values in the specified tables
The number of customers who have placed an order
Which of the following functions can be used to determine the earliest ship date for all orders recently processed by JustLee Books?
COUNT function
MAX function
MIN function
STDDEV function
Which of the following is not a valid SELECT statement?
SELECT STDDEV(retail) FROM books;
SELECT AVG(SUM(retail)) FROM orders NATURAL JOIN orderitems NATURAL JOIN books GROUP BY customer#;
SELECT order#, TO_CHAR(SUM(retail),'999.99') FROM orderitems JOIN books USING (isbn) GROUP BY order#;
SELECT title, VARIANCE(retail-cost) FROM books GROUP BY pubid;
Which of the below statements are true about the nesting of group functions? (Select all that apply)
The inner most function is resolved first.
Oracle allows nesting of group function up to 3 levels.
Single row functions can be nested with group functions.
Oracle allows nesting of group function up to 2 levels.
What are the statistical group functions in Oracle? (Select all that apply)
AVG
STDDEV
VARIANCE
STATS
If the SELECT list contains a column and a group functions, which of the following clause must be mandatorily included?
ORDER BY
HAVING
GROUP BY
None of these
Examine the table structure as given. 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;
Salaries cannot be averaged as not all the numbers will divide evenly.
You cannot use a column alias in the GROUP BY clause.
The GROUP BY clause must have something to GROUP.
The department id is not listed in the departments table.
Which of the following data types are compatible with AVG, SUM, VARIANCE, and STDDEV functions?
Only numeric data types
Integers only
Any data type
All except numeric
Which of the below query will display the number of distinct job categories working in each department?
SELECT department_id , COUNT(DISTINCT job) FROM employees GROUP BY job;
SELECT department_id , COUNT(job) FROM employees GROUP BY employee_id ;
SELECT department_id , COUNT(job) FROM employees GROUP BY department_id ;
SELECT department_id , COUNT(DISTINCT job) FROM employees GROUP BY department_id ;
Evaluate this SQL statement:
SELECT employee_id , first_name , department_id , SUM(salary ) FROM employees WHERE salary > 1000 GROUP BY department_id , employee_id , first_name ORDER BY hiredate;
Why will this statement cause an error?
The HAVING clause is missing.
The WHERE clause contains a syntax error.
The SALARY column is NOT included in the GROUP BY clause.
The HIRE_DATE column is NOT included in the GROUP BY clause.
Which of the following statements is true about the GROUP BY clause? (Select all that apply)
To exclude rows before dividing them into groups using the GROUP BY clause, you use should a WHERE clause.
You must use the HAVING clause with the GROUP BY clause.
Column alias can be used in a GROUP BY clause.
By default, rows are not sorted when a GROUP BY clause is used.
Examine the table structure as given. Interpret the outcome of the below query.
SELECT department_id , MIN (hiredate) FROM employees GROUP by department_id ;
The earliest hire date in the organization.
The latest hire date in the organization.
The earliest hire date in a department.
The latest hire date in a department.
Which statement about group functions is true?
Group functions except COUNT(*), ignore null values.
A query that includes a group function in the SELECT list must include a GROUP BY clause.
Group functions can be used in a WHERE clause.
Group functions can only be used in a SELECT list.
Which of the following clauses represent valid uses of group functions? (Select all that apply)
GROUP BY MAX(salary)
ORDER BY AVG(salary)
HAVING MAX(salary) > 10000
SELECT AVG(NVL(salary, 0))
Which of the following statements are true about the GROUP BY clause? (Select all that apply)
The last column listed in the GROUP BY clause is the most major grouping.
The first column listed in the GROUP BY clause is the most major grouping.
A GROUP BY clause cannot be used without an ORDER BY clause.
The GROUP BY clause do not ensure the sorting of output.
What is difference between WHERE clause and HAVING clause? (Select all that apply)
WHERE clause restrict rows before grouping while HAVING clause restricts groups.
WHERE clause cannot contain a group function but HAVING clause can have.
WHERE clause can join multiple conditions using AND or OR operators but HAVING clause cannot.
WHERE clause can appear in SELECT query without GROUP BY clause but HAVING clause cannot.
Examine the table structure as given. Predict the outcome of the below query.
SELECT department_id ,job,count(*) FROM employees GROUP BY department_id ,job ORDER BY department_id ,count(*);
It executes successfully.
It throws error because ORDER BY clause is invalid.
It throws error because GROUP BY clause is invalid.
It throws error because GROUP BY and ORDER BY clause cannot be used together.
