4-1 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Oracle OCPOracle OCPOracle OCPOracle OCP 考试系列培训之
1Z0-007 Lesson41Z0-007 Lesson41Z0-007 Lesson41Z0-007 Lesson4wwwwwwwwwwww....OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux....cncncncn
4444Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.4-2
Reporting Aggregated DataReporting Aggregated DataReporting Aggregated DataReporting Aggregated DataUsing the Group Functions Using the Group Functions Using the Group Functions Using the Group Functions
4-3 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
ObjectivesObjectivesObjectivesObjectives
After completing this lesson, you should be able to do After completing this lesson, you should be able to do After completing this lesson, you should be able to do After completing this lesson, you should be able to do the following:the following:the following:the following:• Identify the available group functionsIdentify the available group functionsIdentify the available group functionsIdentify the available group functions• Describe the use of group functionsDescribe the use of group functionsDescribe the use of group functionsDescribe the use of group functions• Group data by using the Group data by using the Group data by using the Group data by using the GROUP BYGROUP BYGROUP BYGROUP BY clause clause clause clause• Include or exclude grouped rows by using the Include or exclude grouped rows by using the Include or exclude grouped rows by using the Include or exclude grouped rows by using the
HAVINGHAVINGHAVINGHAVING clause clause clause clause
4-4 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
What Are Group Functions?What Are Group Functions?What Are Group Functions?What Are Group Functions?
Group functions operate on sets of rows to give one Group functions operate on sets of rows to give one Group functions operate on sets of rows to give one Group functions operate on sets of rows to give one result per group.result per group.result per group.result per group.
EMPLOYEESEMPLOYEESEMPLOYEESEMPLOYEES
Maximum salary in Maximum salary in Maximum salary in Maximum salary in EMPLOYEESEMPLOYEESEMPLOYEESEMPLOYEES tabletabletabletable
…………
4-5 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Types of Group FunctionsTypes of Group FunctionsTypes of Group FunctionsTypes of Group Functions
• AVG AVG AVG AVG • COUNT COUNT COUNT COUNT • MAXMAXMAXMAX• MIN MIN MIN MIN • STDDEV STDDEV STDDEV STDDEV • SUMSUMSUMSUM• VARIANCEVARIANCEVARIANCEVARIANCE
GroupGroupGroupGroupfunctionsfunctionsfunctionsfunctions
4-6 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
SELECT [SELECT [SELECT [SELECT [columncolumncolumncolumn,] ,] ,] ,] group_function(column), ...group_function(column), ...group_function(column), ...group_function(column), ...FROMFROMFROMFROM tabletabletabletable[WHERE[WHERE[WHERE[WHERE conditionconditionconditioncondition]]]][GROUP BY [GROUP BY [GROUP BY [GROUP BY columncolumncolumncolumn]]]][ORDER BY [ORDER BY [ORDER BY [ORDER BY columncolumncolumncolumn];];];];
Group Functions: SyntaxGroup Functions: SyntaxGroup Functions: SyntaxGroup Functions: Syntax
4-7 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
SELECT AVG(salary), MAX(salary),SELECT AVG(salary), MAX(salary),SELECT AVG(salary), MAX(salary),SELECT AVG(salary), MAX(salary), MIN(salary), SUM(salary) MIN(salary), SUM(salary) MIN(salary), SUM(salary) MIN(salary), SUM(salary)FROM employeesFROM employeesFROM employeesFROM employeesWHERE job_id LIKE '%REP%';WHERE job_id LIKE '%REP%';WHERE job_id LIKE '%REP%';WHERE job_id LIKE '%REP%';
Using the Using the Using the Using the AVGAVGAVGAVG and and and and SUMSUMSUMSUM Functions Functions Functions Functions
You can use You can use You can use You can use AVGAVGAVGAVG and and and and SUMSUMSUMSUM for numeric data. for numeric data. for numeric data. for numeric data.
4-8 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
SELECT MIN(hire_date), MAX(hire_date)SELECT MIN(hire_date), MAX(hire_date)SELECT MIN(hire_date), MAX(hire_date)SELECT MIN(hire_date), MAX(hire_date)FROMFROMFROMFROM employees; employees; employees; employees;
Using the Using the Using the Using the MINMINMINMIN and and and and MAXMAXMAXMAX Functions Functions Functions Functions
You can use You can use You can use You can use MINMINMINMIN and and and and MAXMAXMAXMAX for numeric, character, and for numeric, character, and for numeric, character, and for numeric, character, and date data types.date data types.date data types.date data types.
4-9 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
COUNT(COUNT(COUNT(COUNT(****)))) returns the number of rows in a table: returns the number of rows in a table: returns the number of rows in a table: returns the number of rows in a table:
COUNT(COUNT(COUNT(COUNT(exprexprexprexpr)))) returns the number of rows with non- returns the number of rows with non- returns the number of rows with non- returns the number of rows with non-null values for the null values for the null values for the null values for the exprexprexprexpr::::
SELECT COUNT(commission_pct)SELECT COUNT(commission_pct)SELECT COUNT(commission_pct)SELECT COUNT(commission_pct),COUNT(salary),COUNT(,COUNT(salary),COUNT(,COUNT(salary),COUNT(,COUNT(salary),COUNT(****))))FROM employeesFROM employeesFROM employeesFROM employees;;;;
SELECT COUNT(SELECT COUNT(SELECT COUNT(SELECT COUNT(****))))FROM employeesFROM employeesFROM employeesFROM employeesWHERE department_id = 50;WHERE department_id = 50;WHERE department_id = 50;WHERE department_id = 50;
Using the Using the Using the Using the COUNTCOUNTCOUNTCOUNT Function Function Function Function
1111
2222
4-10 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
SELECT COUNT(DISTINCT department_id)SELECT COUNT(DISTINCT department_id)SELECT COUNT(DISTINCT department_id)SELECT COUNT(DISTINCT department_id)FROM employees;FROM employees;FROM employees;FROM employees;
Using the Using the Using the Using the DISTINCTDISTINCTDISTINCTDISTINCT Keyword Keyword Keyword Keyword
• COUNT(DISTINCT expr)COUNT(DISTINCT expr)COUNT(DISTINCT expr)COUNT(DISTINCT expr) returns the number of returns the number of returns the number of returns the number of distinct non-null values of the distinct non-null values of the distinct non-null values of the distinct non-null values of the exprexprexprexpr....
• To display the number of distinct department To display the number of distinct department To display the number of distinct department To display the number of distinct department values in the values in the values in the values in the EMPLOYEESEMPLOYEESEMPLOYEESEMPLOYEES table: table: table: table:
4-11 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Group functions ignore null values in the columnGroup functions ignore null values in the columnGroup functions ignore null values in the columnGroup functions ignore null values in the column::::
The The The The NVLNVLNVLNVL function forces group functions to include function forces group functions to include function forces group functions to include function forces group functions to include null values:null values:null values:null values:
SELECT AVG(commission_pct)SELECT AVG(commission_pct)SELECT AVG(commission_pct)SELECT AVG(commission_pct)FROM employees;FROM employees;FROM employees;FROM employees;
SELECT SELECT SELECT SELECT SUM(commission_pct)/COUNT(commission_pct)SUM(commission_pct)/COUNT(commission_pct)SUM(commission_pct)/COUNT(commission_pct)SUM(commission_pct)/COUNT(commission_pct)FROM employees;FROM employees;FROM employees;FROM employees;
Group Functions and Null ValuesGroup Functions and Null ValuesGroup Functions and Null ValuesGroup Functions and Null Values
1111
2222 SELECT SELECT SELECT SELECT SUMSUMSUMSUM(commission_pct)(commission_pct)(commission_pct)(commission_pct)/COUNT(/COUNT(/COUNT(/COUNT(****))))FROM employees;FROM employees;FROM employees;FROM employees;
3333
SELECT AVG(NVL(commission_pct, 0))SELECT AVG(NVL(commission_pct, 0))SELECT AVG(NVL(commission_pct, 0))SELECT AVG(NVL(commission_pct, 0))FROM employees;FROM employees;FROM employees;FROM employees;4444
4-12 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Creating Groups of Data Creating Groups of Data Creating Groups of Data Creating Groups of Data
EMPLOYEESEMPLOYEESEMPLOYEESEMPLOYEES
…………
4400440044004400
9500950095009500
3500350035003500
6400640064006400
10033100331003310033
AverageAverageAverageAveragesalary in salary in salary in salary in EMPLOYEESEMPLOYEESEMPLOYEESEMPLOYEEStable for each table for each table for each table for each departmentdepartmentdepartmentdepartment
4-13 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
You can divide rows in a table into smaller groups by You can divide rows in a table into smaller groups by You can divide rows in a table into smaller groups by You can divide rows in a table into smaller groups by using the using the using the using the GROUPGROUPGROUPGROUP BYBYBYBY clause. clause. clause. clause.
Creating Groups of Data: Creating Groups of Data: Creating Groups of Data: Creating Groups of Data: GROUPGROUPGROUPGROUP BYBYBYBY Clause Syntax Clause Syntax Clause Syntax Clause Syntax
SELECT SELECT SELECT SELECT columncolumncolumncolumn, , , , group_function(column)group_function(column)group_function(column)group_function(column)FROM FROM FROM FROM tabletabletabletable[WHERE [WHERE [WHERE [WHERE conditionconditionconditioncondition]]]][GROUP BY [GROUP BY [GROUP BY [GROUP BY group_by_expressiongroup_by_expressiongroup_by_expressiongroup_by_expression]]]][ORDER BY [ORDER BY [ORDER BY [ORDER BY columncolumncolumncolumn];];];];
4-14 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
SELECT department_id, AVG(salary)SELECT department_id, AVG(salary)SELECT department_id, AVG(salary)SELECT department_id, AVG(salary)FROM employeesFROM employeesFROM employeesFROM employeesGROUP BY department_id ;GROUP BY department_id ;GROUP BY department_id ;GROUP BY department_id ;
Using the Using the Using the Using the GROUPGROUPGROUPGROUP BYBYBYBY Clause Clause Clause Clause
All columns in the All columns in the All columns in the All columns in the SELECTSELECTSELECTSELECT list that are not in group list that are not in group list that are not in group list that are not in group functions must be in the functions must be in the functions must be in the functions must be in the GROUPGROUPGROUPGROUP BYBYBYBY clause. clause. clause. clause.
4-15 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Using the Using the Using the Using the GROUPGROUPGROUPGROUP BYBYBYBY Clause Clause Clause Clause
The The The The GROUPGROUPGROUPGROUP BYBYBYBY column does not have to be in the column does not have to be in the column does not have to be in the column does not have to be in the SELECTSELECTSELECTSELECT list. list. list. list.
SELECT AVG(salary)SELECT AVG(salary)SELECT AVG(salary)SELECT AVG(salary)FROM employeesFROM employeesFROM employeesFROM employeesGROUP BY department_id ;GROUP BY department_id ;GROUP BY department_id ;GROUP BY department_id ;
4-16 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Grouping by More Than One ColumnGrouping by More Than One ColumnGrouping by More Than One ColumnGrouping by More Than One Column
EMPLOYEESEMPLOYEESEMPLOYEESEMPLOYEES
Add the Add the Add the Add the salaries in salaries in salaries in salaries in
the the the the EMPLOYEESEMPLOYEESEMPLOYEESEMPLOYEES table for table for table for table foreach job,each job,each job,each job,
grouped bygrouped bygrouped bygrouped bydepartmentdepartmentdepartmentdepartment
…………
4-17 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
SELECT department_id dept_id, job_id, SUM(salary)SELECT department_id dept_id, job_id, SUM(salary)SELECT department_id dept_id, job_id, SUM(salary)SELECT department_id dept_id, job_id, SUM(salary)FROM employeesFROM employeesFROM employeesFROM employeesGROUP BY department_id, job_id ;GROUP BY department_id, job_id ;GROUP BY department_id, job_id ;GROUP BY department_id, job_id ;
Using the Using the Using the Using the GROUPGROUPGROUPGROUP BYBYBYBY Clause Clause Clause Clause on Multiple Columnson Multiple Columnson Multiple Columnson Multiple Columns
4-18 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Illegal Queries Illegal Queries Illegal Queries Illegal Queries Using Group FunctionsUsing Group FunctionsUsing Group FunctionsUsing Group Functions
Any column or expression in the Any column or expression in the Any column or expression in the Any column or expression in the SELECTSELECTSELECTSELECT list that is not list that is not list that is not list that is not an aggregate function must be in the an aggregate function must be in the an aggregate function must be in the an aggregate function must be in the GROUPGROUPGROUPGROUP BYBYBYBY clause clause clause clause::::
SELECT department_id, COUNT(last_name)SELECT department_id, COUNT(last_name)SELECT department_id, COUNT(last_name)SELECT department_id, COUNT(last_name)FROM employees;FROM employees;FROM employees;FROM employees;
SELECT department_id, COUNT(last_name)SELECT department_id, COUNT(last_name)SELECT department_id, COUNT(last_name)SELECT department_id, COUNT(last_name) ****ERROR at line 1:ERROR at line 1:ERROR at line 1:ERROR at line 1:ORA-00937: not a single-group group functionORA-00937: not a single-group group functionORA-00937: not a single-group group functionORA-00937: not a single-group group function
Column missing in the Column missing in the Column missing in the Column missing in the GROUPGROUPGROUPGROUP BYBYBYBY clause clause clause clause
4-19 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Illegal Queries Illegal Queries Illegal Queries Illegal Queries Using Group FunctionsUsing Group FunctionsUsing Group FunctionsUsing Group Functions
• You cannot use the You cannot use the You cannot use the You cannot use the WHEREWHEREWHEREWHERE clause to restrict groups. clause to restrict groups. clause to restrict groups. clause to restrict groups.• You use the You use the You use the You use the HAVINGHAVINGHAVINGHAVING clause to restrict groups. clause to restrict groups. clause to restrict groups. clause to restrict groups.• You cannot use group functions in the You cannot use group functions in the You cannot use group functions in the You cannot use group functions in the WHEREWHEREWHEREWHERE clause. clause. clause. clause.
SELECT department_id, AVG(salary)SELECT department_id, AVG(salary)SELECT department_id, AVG(salary)SELECT department_id, AVG(salary)FROM employeesFROM employeesFROM employeesFROM employeesWHERE AVG(salary) > 8000WHERE AVG(salary) > 8000WHERE AVG(salary) > 8000WHERE AVG(salary) > 8000GROUP BY department_id;GROUP BY department_id;GROUP BY department_id;GROUP BY department_id;
WHERE AVG(salary) > 8000WHERE AVG(salary) > 8000WHERE AVG(salary) > 8000WHERE AVG(salary) > 8000 ****ERROR at line 3:ERROR at line 3:ERROR at line 3:ERROR at line 3:ORA-00934: group function is not allowed hereORA-00934: group function is not allowed hereORA-00934: group function is not allowed hereORA-00934: group function is not allowed here
Cannot use the Cannot use the Cannot use the Cannot use the WHEREWHEREWHEREWHERE clause to restrict groups clause to restrict groups clause to restrict groups clause to restrict groups
4-20 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Restricting Group ResultsRestricting Group ResultsRestricting Group ResultsRestricting Group Results
EMPLOYEESEMPLOYEESEMPLOYEESEMPLOYEES
…………
The maximumThe maximumThe maximumThe maximumsalarysalarysalarysalary
per departmentper departmentper departmentper departmentwhen it iswhen it iswhen it iswhen it is
greater thangreater thangreater thangreater than$10,000$10,000$10,000$10,000
4-21 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
SELECT SELECT SELECT SELECT columncolumncolumncolumn, , , , group_functiongroup_functiongroup_functiongroup_functionFROM FROM FROM FROM tabletabletabletable[WHERE [WHERE [WHERE [WHERE conditionconditionconditioncondition]]]][GROUP BY [GROUP BY [GROUP BY [GROUP BY group_by_expressiongroup_by_expressiongroup_by_expressiongroup_by_expression]]]][HAVING [HAVING [HAVING [HAVING group_conditiongroup_conditiongroup_conditiongroup_condition]]]][ORDER BY [ORDER BY [ORDER BY [ORDER BY columncolumncolumncolumn];];];];
Restricting Group Results Restricting Group Results Restricting Group Results Restricting Group Results with the with the with the with the HAVINGHAVINGHAVINGHAVING Clause Clause Clause Clause
When you use the When you use the When you use the When you use the HAVINGHAVINGHAVINGHAVING clause, the Oracle server clause, the Oracle server clause, the Oracle server clause, the Oracle server restricts groups as follows:restricts groups as follows:restricts groups as follows:restricts groups as follows:1.1.1.1. Rows are grouped.Rows are grouped.Rows are grouped.Rows are grouped.2.2.2.2. The group function is applied.The group function is applied.The group function is applied.The group function is applied.3.3.3.3. Groups matching the Groups matching the Groups matching the Groups matching the HAVINGHAVINGHAVINGHAVING clause are displayed. clause are displayed. clause are displayed. clause are displayed.
4-22 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
SELECT department_id, MAX(salary)SELECT department_id, MAX(salary)SELECT department_id, MAX(salary)SELECT department_id, MAX(salary)FROM employeesFROM employeesFROM employeesFROM employeesGROUP BY department_idGROUP BY department_idGROUP BY department_idGROUP BY department_idHAVING MAX(salary)>10000 ;HAVING MAX(salary)>10000 ;HAVING MAX(salary)>10000 ;HAVING MAX(salary)>10000 ;
Using the Using the Using the Using the HAVINGHAVINGHAVINGHAVING Clause Clause Clause Clause
4-23 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
SELECT job_id, SUM(salary) PAYROLLSELECT job_id, SUM(salary) PAYROLLSELECT job_id, SUM(salary) PAYROLLSELECT job_id, SUM(salary) PAYROLLFROM employeesFROM employeesFROM employeesFROM employeesWHERE job_id NOT LIKE '%REP%'WHERE job_id NOT LIKE '%REP%'WHERE job_id NOT LIKE '%REP%'WHERE job_id NOT LIKE '%REP%'GROUP BY job_idGROUP BY job_idGROUP BY job_idGROUP BY job_idHAVING SUM(salary) > 13000HAVING SUM(salary) > 13000HAVING SUM(salary) > 13000HAVING SUM(salary) > 13000ORDER BY SUM(salary);ORDER BY SUM(salary);ORDER BY SUM(salary);ORDER BY SUM(salary);
Using the Using the Using the Using the HAVINGHAVINGHAVINGHAVING Clause Clause Clause Clause
4-24 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
SELECT MAX(AVG(salary))SELECT MAX(AVG(salary))SELECT MAX(AVG(salary))SELECT MAX(AVG(salary))FROM employeesFROM employeesFROM employeesFROM employeesGROUP BY department_id;GROUP BY department_id;GROUP BY department_id;GROUP BY department_id;
Nesting Group FunctionsNesting Group FunctionsNesting Group FunctionsNesting Group Functions
Display the maximum average salary: Display the maximum average salary: Display the maximum average salary: Display the maximum average salary:
4-25 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
SELECT SELECT SELECT SELECT columncolumncolumncolumn, , , , group_functiongroup_functiongroup_functiongroup_functionFROM FROM FROM FROM tabletabletabletable[WHERE [WHERE [WHERE [WHERE conditionconditionconditioncondition]]]][GROUP BY [GROUP BY [GROUP BY [GROUP BY group_by_expressiongroup_by_expressiongroup_by_expressiongroup_by_expression]]]][HAVING [HAVING [HAVING [HAVING group_conditiongroup_conditiongroup_conditiongroup_condition]]]][ORDER BY [ORDER BY [ORDER BY [ORDER BY columncolumncolumncolumn];];];];
SummarySummarySummarySummary
In this lesson, you should have learned how to: In this lesson, you should have learned how to: In this lesson, you should have learned how to: In this lesson, you should have learned how to: • Use the group functionsUse the group functionsUse the group functionsUse the group functions COUNTCOUNTCOUNTCOUNT, , , , MAXMAXMAXMAX, , , , MINMINMINMIN, and , and , and , and AVGAVGAVGAVG• Write queries that use the Write queries that use the Write queries that use the Write queries that use the GROUPGROUPGROUPGROUP BYBYBYBY clause clause clause clause• Write queries that use the Write queries that use the Write queries that use the Write queries that use the HAVINGHAVINGHAVINGHAVING clause clause clause clause
4-26 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Practice 4: OverviewPractice 4: OverviewPractice 4: OverviewPractice 4: Overview
This practice covers the following topics:This practice covers the following topics:This practice covers the following topics:This practice covers the following topics:• Writing queries that use the group functionsWriting queries that use the group functionsWriting queries that use the group functionsWriting queries that use the group functions• Grouping by rows to achieve more than one resultGrouping by rows to achieve more than one resultGrouping by rows to achieve more than one resultGrouping by rows to achieve more than one result• Restricting groups by using the Restricting groups by using the Restricting groups by using the Restricting groups by using the HAVINGHAVINGHAVINGHAVING clause clause clause clause
4-27 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Practice Practice Practice Practice 4444
Q1:Q1:Q1:Q1:The CUSTOMERS table has these columns:The CUSTOMERS table has these columns:The CUSTOMERS table has these columns:The CUSTOMERS table has these columns: CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_ID NUMBER(4) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL CUSTOMER_NAME VARCHAR2(100) NOT NULL STREET_ADDRESS VARCHAR2(150) STREET_ADDRESS VARCHAR2(150) STREET_ADDRESS VARCHAR2(150) STREET_ADDRESS VARCHAR2(150) CITY_ADDRESS VARCHAR2(50) CITY_ADDRESS VARCHAR2(50) CITY_ADDRESS VARCHAR2(50) CITY_ADDRESS VARCHAR2(50) STATE_ADDRESS VARCHAR2(50) STATE_ADDRESS VARCHAR2(50) STATE_ADDRESS VARCHAR2(50) STATE_ADDRESS VARCHAR2(50) PROVINCE_ADDRESS VARCHAR2(50) PROVINCE_ADDRESS VARCHAR2(50) PROVINCE_ADDRESS VARCHAR2(50) PROVINCE_ADDRESS VARCHAR2(50) COUNTRY_ADDRESS VARCHAR2(50) COUNTRY_ADDRESS VARCHAR2(50) COUNTRY_ADDRESS VARCHAR2(50) COUNTRY_ADDRESS VARCHAR2(50) POSTAL_CODE VARCHAR2(12) POSTAL_CODE VARCHAR2(12) POSTAL_CODE VARCHAR2(12) POSTAL_CODE VARCHAR2(12) CUSTOMER_PHONE VARCHAR2(20) CUSTOMER_PHONE VARCHAR2(20) CUSTOMER_PHONE VARCHAR2(20) CUSTOMER_PHONE VARCHAR2(20) The CUSTOMER_ID column is the primary key for the The CUSTOMER_ID column is the primary key for the The CUSTOMER_ID column is the primary key for the The CUSTOMER_ID column is the primary key for the table. table. table. table.
4-28 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Practice Practice Practice Practice 4444
Q1: Q1: Q1: Q1: You need to determine how dispersed your customer You need to determine how dispersed your customer You need to determine how dispersed your customer You need to determine how dispersed your customer base is. Which expression finds the number of base is. Which expression finds the number of base is. Which expression finds the number of base is. Which expression finds the number of different countries represented in the CUSTOMERS different countries represented in the CUSTOMERS different countries represented in the CUSTOMERS different countries represented in the CUSTOMERS table? table? table? table?
A. COUNT(UPPER(country_address)) A. COUNT(UPPER(country_address)) A. COUNT(UPPER(country_address)) A. COUNT(UPPER(country_address)) B. COUNT(DIFF(UPPER(country_address))) B. COUNT(DIFF(UPPER(country_address))) B. COUNT(DIFF(UPPER(country_address))) B. COUNT(DIFF(UPPER(country_address))) CCCC. COUNT(UNIQUE(UPPER(country_address))) . COUNT(UNIQUE(UPPER(country_address))) . COUNT(UNIQUE(UPPER(country_address))) . COUNT(UNIQUE(UPPER(country_address))) D. COUNT DISTINCT UPPER(country_address) D. COUNT DISTINCT UPPER(country_address) D. COUNT DISTINCT UPPER(country_address) D. COUNT DISTINCT UPPER(country_address) EEEE. COUNT(DISTINCT (UPPER(country_address))) . COUNT(DISTINCT (UPPER(country_address))) . COUNT(DISTINCT (UPPER(country_address))) . COUNT(DISTINCT (UPPER(country_address)))
4-29 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Practice Practice Practice Practice 4444
Q2: Q2: Q2: Q2: Which clause should you use to exclude group Which clause should you use to exclude group Which clause should you use to exclude group Which clause should you use to exclude group results? results? results? results?
A. WHERE A. WHERE A. WHERE A. WHERE BBBB. HAVING . HAVING . HAVING . HAVING C. RESTRICT C. RESTRICT C. RESTRICT C. RESTRICT D. GROUP BY D. GROUP BY D. GROUP BY D. GROUP BY E. ORDER BY E. ORDER BY E. ORDER BY E. ORDER BY
4-30 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Practice Practice Practice Practice 4444
Q3: Which two are true about aggregate functions? Q3: Which two are true about aggregate functions? Q3: Which two are true about aggregate functions? Q3: Which two are true about aggregate functions? (Choose two.) (Choose two.) (Choose two.) (Choose two.)
A. You can use aggregate functions in any clause of A. You can use aggregate functions in any clause of A. You can use aggregate functions in any clause of A. You can use aggregate functions in any clause of a SELECT statement. a SELECT statement. a SELECT statement. a SELECT statement.
B. You can use aggregate functions only in the B. You can use aggregate functions only in the B. You can use aggregate functions only in the B. You can use aggregate functions only in the column list of the SELECT clause and in the WHERE column list of the SELECT clause and in the WHERE column list of the SELECT clause and in the WHERE column list of the SELECT clause and in the WHERE clause of a SELECT statement. clause of a SELECT statement. clause of a SELECT statement. clause of a SELECT statement.
CCCC. You can mix single row columns with aggregate . You can mix single row columns with aggregate . You can mix single row columns with aggregate . You can mix single row columns with aggregate functions in the column list of a SELECT statement functions in the column list of a SELECT statement functions in the column list of a SELECT statement functions in the column list of a SELECT statement by grouping on the single row columns. by grouping on the single row columns. by grouping on the single row columns. by grouping on the single row columns.
4-31 Copyright © 2012, www.OracleOnLinuxOracleOnLinuxOracleOnLinuxOracleOnLinux.cn. All rights reserved.
Practice Practice Practice Practice 4444
Q3: Q3: Q3: Q3:
DDDD. You can pass column names, expressions, . You can pass column names, expressions, . You can pass column names, expressions, . You can pass column names, expressions, constants, or functions as parameters to an constants, or functions as parameters to an constants, or functions as parameters to an constants, or functions as parameters to an aggregate function. aggregate function. aggregate function. aggregate function.
E. You can use aggregate functions on a table, only E. You can use aggregate functions on a table, only E. You can use aggregate functions on a table, only E. You can use aggregate functions on a table, only by grouping the whole table as one single group. by grouping the whole table as one single group. by grouping the whole table as one single group. by grouping the whole table as one single group.
F. You cannot group the rows of a table by more F. You cannot group the rows of a table by more F. You cannot group the rows of a table by more F. You cannot group the rows of a table by more than one column while using aggregate functions. than one column while using aggregate functions. than one column while using aggregate functions. than one column while using aggregate functions.
Top Related