1 / 21

Logging into the gateway server

Lab 3: Aggregate Functions, Group By & Having Clauses TA City University of Hong Kong. Logging into the gateway server. In our lab, we are going to do operations to Oracle Server through Unix System, so you are required to log into Unix first:

gigi
Download Presentation

Logging into the gateway server

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Lab 3: Aggregate Functions, Group By & Having ClausesTA City University of Hong Kong

  2. Logging into the gateway server In our lab, we are going to do operations to Oracle Server through Unix System, so you are required to log into Unix first: • Launch the SSH Secure Shell Client software; • Click the “Quick Connect” button; • Enter gateway.cs.cityu.edu.hkfor Host Name; • Enter your CSLab account’s username (eg. your student ID) for the User Name, click the Connect button; • Enter your CSLab account’s password for the Password, answer Yes to the Host Identification question.

  3. Starting SQL*Plus • To start SQL*Plus, type the following command first: sqlplus • SQL*Plus will display the version of the Oracle Server and JServer already built in: SQL*Plus: Release 9.0.1.0.0 - Production on Tue Feb 10 12:39:51 2009 • Enter user-name: c50123456@ora10g 50123456= Your student ID • Enter password: c50123456 50123456= Your student ID • Then if you have successfully logged into your Oracle account, you should see the following information: Connected to: Oracle Database 10g Enterprise Edition Release 10.2.0.1.0 – Production With the Partitioning, OLAP and Data Mining options

  4. Outline • Aggregate Functions • Selecting Summary Information From Groups – GROUP BY • Specifying a Search-Conditions For Groups – HAVING • Effects of Null Values On Group Functions • Formatting Query Results Into a Report

  5. Aggregate Functions • In all the examples so far, we have selected values stored in each row of a table (like SAL), or values calculated for each row (like SAL*12). That is, we have selected information about individual rows stored in the database. You can also select “summary” information about groups of rows. • Oracle provides five aggregate functions that can be applied to data retrieved in a query: • AVG: Computes the average value • SUM: Computes the total value • MIN: Finds the minimum value • MAX: Finds the maximum value • COUNT: Counts the number of values

  6. AVG() • AVG function is used to get the average value of column/ columns within one group. • Example: Compute the average annual salary plus commission for all salesmen. SQL > SELECT AVG(SAL + COMM)*12 2 FROM EMP 3 WHERE JOB = ‘SALESMAN’; • Here, Oracle first find out all the rows that satisfy the search-condition to form the target group, and then apply the arithmetic functions as well as AVG function to this group to get the result.

  7. SUM() • SUM function is used to get the summary value of some columns within one group. • Example: Find the total salary and total commission for salesmen . SQL > SELECT SUM(SAL), SUM(COMM) 2 FROM EMP 3 WHERE JOB = ‘SALESMAN’;

  8. MAX() & MIN() • MAX and MIN function is used to get the maximum/minimum value of some columns within one group. • Example: Find the highest and lowest paid employee salaries and the difference between them. SQL > SELECT MAX(SAL), MIN(SAL), MAX(SAL)-MIN(SAL) 2 FROM EMP;

  9. COUNT() • The COUNT function can be used to count the number of values, number of distinct values using COUNT (DISTINCT column_name), or number of rows using COUNT (*) selected by the query. • Example: Count the number of different jobs held by employees in department 30. SQL > SELECT COUNT(DISTINCT JOB) 2 FROM EMP 3 WHERE DEPTNO = 30;

  10. Outline • Aggregate Functions • Selecting Summary Information From Groups – GROUP BY • Specifying a Search-Conditions For Groups – HAVING • Effects of Null Values On Group Functions • Formatting Query Results Into a Report

  11. GROUP BY Clause • GROUP BY clause divides a table into groups of rows with matching values in the same column (or columns). It always follows the WHERE clause, or the FROM clause when there is no WHERE clause in the command. • Example: Find each department’s average annual salary for all its employees except the managers and the president. SQL > SELECT DEPTNO, AVG(SAL)*12 2 FROM EMP 3 WHERE JOB NOT IN (‘MANAGER’, ‘PRESIDENT’) 4 GROUP BY DEPTNO;

  12. GROUP BY Clause (cont.) • You can partition rows of a table into groups based on the values in more than one column. • Also, you can have join-conditions and group functions in the same query. • Example:􀂃 Divide all employees into groups by department name and jobs within department. Count the employees in each group and compute each group’s average annual salary. SQL > SELECT DNAME, JOB, COUNT(*), AVG(SAL)*12 2 FROM EMP, DEPT 3 WHERE DEPT.DEPTNO = EMP.DEPTNO 4 GROUP BY DNAME, JOB;

  13. Outline • Aggregate Functions • Selecting Summary Information From Groups – GROUP BY • Specifying a Search-Conditions For Groups – HAVING • Effects of Null Values On Group Functions • Formatting Query Results Into a Report

  14. HAVING Clause • You can specify search-conditions for groups just as you can specify conditions for individual rows. As you know, search-conditions for individual rows are specified in the WHERE clause, for groups, you should use HAVING clause to do specification. And a query can contain both a WHERE and a HAVING clause. • Example: List all the departments that have at least two clerks. SQL > SELECT DEPTNO 2 FROM EMP 3 WHERE JOB = ‘CLERK’ 4 GROUP BY DEPTNO 5 HAVING COUNT(*) >=2;

  15. HAVING Clause (cont.) • A HAVING clause can also compare one attribute of the group with another attribute of the group. • Example:􀂃 Find all departments with an average commission greater than 25% of average salary. SQL > SELECT DEPTNO, AVG(SAL), AVG(COMM), AVG(SAL)*0.25 2 FROM EMP 3 GROUP BY DEPTNO 4 HAVING AVG(COMM) > AVG(SAL)*0.25;

  16. Outline • Aggregate Functions • Selecting Summary Information From Groups – GROUP BY • Specifying a Search-Conditions For Groups – HAVING • Effects of Null Values On Group Functions • Formatting Query Results Into a Report

  17. NULL VALUES ON GROUP FUNCTIONS • Null values do not participate in the computation of any group functions. • Example: SQL > SELECT SUM(SAL), COUNT(SAL), AVG(SAL), SUM(COMM), COUNT (COMM), AVG(COMM) 2 FROM EMP 3 WHERE DEPTNO = 30; • In the result, you can see that the count of people who receive a salary is 6, while the people who receive a commission is only 3. This is because null commissions were not counted.

  18. NULL VALUES ON GROUP FUNCTIONS (cont.) • Example: List the average commission of employees who receive a commission, and the average commission of all employees (treating employees who do not receive a commission as receiving a zero commission). SQL > SELECT AVG(COMM),AVG(NVL(COMM, 0)) 2 FROM EMP 3 WHERE DEPTNO = 30; • Q: why the results for these two expressions are different? • A: Although the values of SUM(COMM) for these two expressions are the same, but here for AVG(COMM), SUM(COMM) is divided by 3, while for AVG(NVL(COMM, 0)), SUM(COMM) is divided by 6.

  19. Outline • Aggregate Functions • Selecting Summary Information From Groups – GROUP BY • Specifying a Search-Conditions For Groups – HAVING • Effects of Null Values On Group Functions • Formatting Query Results Into a Report

  20. Formatting Query Results Into a Report • In this section, you will learn some more SQL*Plus commands to improve the appearance (format) of a query result. The commands to be covered in this section are: • COLUMN: Format a column’s heading and data • TTITLE: Put a title on the top of the page • BTITLE: Put a title on the bottom of the page • BREAK: Break the report up into groups of rows • COMPUTE: Perform various computation in each group

  21. Summary • Aggregate Functions • Selecting Summary Information From Groups – GROUP BY • Specifying a Search-Conditions For Groups – HAVING • Effects of Null Values On Group Functions • Formatting Query Results Into a Report

More Related