introduction to 4gl and oracle … selec… · web viewintroduction to 4gl and oracle...

THE STRUCTURED QUERY LANGUAGE: SQL

SELECT: Selects rows and columns from one

or more tables. May embedded in other SQL

commands.

The Structured Query Language SQL is the ANSI standard language for manipulating relational databases. It is :

n Easy to learn, understand and use by professional programmers and by non-technical experts too.n Assertive or non-procedural.n A complete language to manage the database; it is at the same time a

� Query language� Data Definition Language� Data Manipulation Language� Data Control Language

This document explains the Query Language ( SELECT command). It uses the following Oracle sample database.

Table: EMP

EMPNO ENAME JOB MGR# HIREDATE SAL COMM DEPTNO#7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 307521 WARD SALESMAN 7698 22-FEB-81 1250 500 307566 JONES MANAGER 7839 02-APR-81 2975 207654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 307782 CLARK MANAGER 7839 09-JUN-81 2450 107788 SCOTT ANALYST 7566 09-DEC-82 3000 207839 KING PRESIDENT 17-NOV-81 5000 107844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 12-JAN-83 1100 207900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 207934 MILLER CLERK 7782 23-JAN-82 1300 10

Table: DEPT

DEPTNO DNAME LOC10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON

Table : SALGRADE

GRADE LOSAL HISAL1 700 12002 1201 14003 1401 20004 2001 30005 3001 9999

SQL Language The SELECT Statement

DATA RETRIEVAL

The command used to extract (retrieve) data from a table is the SELECT-FROM-WHERE (SFW).

Simplified syntax:SELECT { col1, col2, . . . . . , coln | * }

FROM table-name

[WHERE search-condition ] ; ß optional

The SFW command is useful to :

select columns select rows join tables sort the result use built-in functions (row functions and aggregate functions) treat the NULL values connect records in a tree

A complete syntax is given in the last page of the current document.

The SELECT indicates the list of information we want to obtain in the result. This list is a combination of :

� Column names separated by commas� SQL built-in functions� Arithmetic expressions� Literal

The FROM indicates the involved (needed) tables to built the result.

The WHERE announces the search condition which can use :

� comparison operators < , <= , = , > , >= , !=� logical connectives AND, OR, NOT� belonging operator IN, NOT IN� etc.

EXAMPLE

FOR EACH EMPLOYEE WORKING IN THE DEPARTMENT 10, DISPLAY THE EMPLOYEE NUMBER.

SELECT EMPNOFROM EMPWHERE DEPTNO =10;

Remarks

� If all columns ( the entire row) of the concerned table are needed in the result then use the asterisk (*) after the

select. Thus to display all columns for each employee working in the department 10 we write :

SELECT *FROM EMPWHERE DEPTNO =10;

Besides, to list for each employee his number, name and job, we write :

SELECT EMPNO, ENAME , JOBFROM EMP;

Finally, to list all columns and all rows from the employee table, use the following:

SELECT *FROM EMP;

© Prof. Jamel FEKI, University of Jeddah, Jeddah, FCIT – IS dept. KSA 2


� When the WHERE clause is absent, the SFW becomes as the algebraic PROJECTION.

� Beware! The result of a SFW query may contain redundant tuples (records). To eliminate the redundancy we should use the UNIQUE (or its synonym DISTINCT) option.Look at the difference. The query

SELECT DEPTNO SELECT UNIQUE DEPTNOFROM EMP; FROM EMP;

whereasreturns 10 20 10 30 20 10 returns 10 20 30

The UNIQUE eliminates redundant rows in the result.

THE COMPARISON OPERATORS

The WHERE clause may use the following operators:

= equal!= different> greater than>= greater or equal< less than<= less or equal

BETWEEN ..AND.. within a scope[NOT] IN : [not] belong

LIKE resembles

IS NULL undefined value (empty field)

IS NOT NULL Not undefined (defined or known) value

{ =| != | > | < | >= | <= } ANY at least one ...

{ =| != | > | < | >= | <= } ALL all ...(each element)

EXAMPLES

DISPLAY THE NAME, SALARY and COMMISSION FOR EMPLOYEES HAVING A COMMISSION GREATER

THAN THE SALARY.

SELECT ENAME, SAL, COMMFROM EMPWHERECOMM > SAL;

GET THE NAME AND THE SALARY FOR EMPLOYEES HAVING A SALARY BETWEEN 1200 AND 1300

(limits included).

SELECT ENAME, SALFROM EMPWHERE SAL BETWEEN 1200 AND 1300 ;

The IN operator

It enables to compare one value with a set of values.

GET EMPLOYEE NUMBER, NAME AND SALARY FOR ALL 'CLERK' OR 'ANALYST'.



SELECT ENAME, ENAME, JOB, SALFROM EMPWHEREJOB IN ('CLERK', 'ANALYST');

F The parentheses are compulsory.

The IN could be replaced by JOB = 'CLERK' OR JOB = 'ANALYST'.

The LIKE operator

This operator is used with strings.

EXAMPLE

GET EMPLOYEES HAVING THEIR NAME START WITH 'M'.

SELECT ENAMEFROM EMPWHERE ENAME LIKE 'M%';

ENAME---------------MARTINMILLER F The % means a string of any length ( may be null string). The underline character ( _ ) replaces one character.

EXAMPLE

GET EMPLOYEES HAVING 'A' IN THE THIRD POSITION OF THEIR NAME.

SELECT ENAMEFROM EMPWHEREENAME LIKE '_ _A%';

ENAME---------------BLAKECLARKADAMS

SORTING THE RESULT

We can sort the result of a select statement by ending the query with the ORDER BY clause.

SELECT col1, col8, col3FROM table-nameWHERE. . . . . ORDER BY col8, col3 [DESC] column names

or

ORDER BY 2 , 3 [DESC] position of the column in theSELECT

Each sorting column could have its own order ( ASCending or DESCending). The default is ASC (which is assumed for

the col8 above).


SQL Language The SELECT Statement F ORDER BY is always the last in the query

ASC is the default.

EXAMPLE

GET THE NAME , JOB AND SALARY FOR ALL EMPLOYEES. SORT THE RESULT BY JOB ALPHABETICALLY AND BY SALARY DESCENDING.

ENAME JOB SALSELECT ENAME, JOB, SAL --------------- --------------- -----FROM EMP SCOTT ANALYST 3000ORDER BY JOB, SAL DESC; FORD ANALYST 3000

MILLER CLERK 1300ó ADAMS CLERK 1100

JAMES CLERK 950SELECT ENAME, JOB, SAL SMITH CLERK 800FROM EMP JONES MANAGER 2975ORDER BY 2, 3 DESC; BLAKE MANAGER 2850

CLARK MANAGER 2450........ ......... .....



JOINING TABLES

GOALS

Combine rows in order to retrieve associated data coming from many tables. To accomplish a join, we specify the

following:

n The table names in the FROM clause, separated by commas.

n The joining condition in the WHERE clause. Because more than one table is involved, we qualify the

column names by preceding them with the appropriate table name, followed by a period.

Syntax

SELECT .......

FROM table1 , table2

WHERE predicate

The relational algebra requires that the tables to be joined must have two comparable columns (defined on the same

domain). With SQL, the two columns must be only of the same data type.

EXAMPLE

GET THE DEPARTMENT LOCALITY WHERE THE EMPLOYEE SMITH WORKS.

SELECT LOCFROM EMP, DEPTWHERE ENAME = 'SMITH'AND EMP.DEPTNO = DEPT.DEPTNO; ß Join condition

LOC------------DALLAS

JOIN PROCESSING

The trivial method - but not the best - is to compute the Cartesian product then to apply a selection on it (since the Cartesian product consists of all possible combinations of the two tables' rows).

Because the Cartesian product costs too much, SQL will process otherwise beginning by reducing the number of rows to be joined.



JOINING A TABLE TO ITSELF

It is sometimes useful to join a given table to itself in the purpose to obtain on the same row some attributes coming from one row and others coming from another row of the same table.

EXAMPLES

GET THE NAME OF THE BOSS OF THE EMPLOYEE 'FORD'.

Since each row of the table EMP has a column MGR (manager number), and since this attribute is defined on the same domain with EMPNO, it will be possible to find for any employee the name of his/her manager. This needs to join the table EMP to itself.

SELECT BOSS.ENAMEFROM EMP , EMP BOSSWHERE EMP.ENAME= 'FORD'AND EMP.MGR = BOSS.EMPNO;

ENAME------------JONESF This example involves a join of table EMP with itself; the join condition is that the manager number of the selected

employee (FORD) is equal to the employee number in another row.Table EMP appears twice in the FROM clause. To distinguish between the two appearances, we introduce arbitrary the

synonym BOSS and use it as a qualifier in the SELECT and WHERE clauses.

FIND ALL EMPLOYEES HAVING A SALARY MORE THAN JONES.



?EXTERNAL JOIN

GOAL

The external join -of two tables T1 and T2- is a particular join returning the normal join result augmented by all rows

from T1 (respectively T2) those don't match any row in T2 (respectively T1).

HOW ?

The (+) concatenates an empty row (null valued row) to tuples those don't participate to the join.

EXAMPLE

CAR PERSONCNO COLOR OWNER PNO PNAME1234 Red 1 1 Ali1235 Yellow 3 2 Hesham1236 White 2 3 Salah1237 Green 4 Hamad

Normal join

DISPLAY THE CAR NUMBERS AND THEIR OWNERS.

SELECT CNO, PNAMEFROM CAR, PERSONWHEREOWNER = PNO;

CNO PNAME1234 Ali1235 Salah1236 Hesham

Note that persons without cars will not appear in the result (respectively cars without owners); this occurs because there is no corresponding rows for such records.

External join

DISPLAY ALL CARS OWNED OR NOT yet OWNED.

SELECT CNO, COLOR, PNAMEFROM CAR, PERSONWHERE OWNER = PNO (+);

CNO COLOR PNAME1234 Red Ali1235 Yellow Salah1236 White Hesham1237 Green

DISPLAY CNO,COLOR,PNAME FOR BOTH OWNERS AND NOT OWNERS OF CARS.

SELECT CNO , COLOR, PNAMEFROM CAR, PERSON



WHEREPNO = OWNER (+);

CNO COLOR PNAME1234 Red Ali1235 Yellow Salah1236 White Hesham

Hamad

Despite the fact that the person Hamad does not have a car, he exists in the result.

SET OPERATORS

ORACLE offers the following collection of operators working on sets.

Algebraic operators

ORACLE operators

union UNIONintersect INTERSECTdifference MINUS

Syntax

SELECT . . . . . . FROM . . . . . .

{ UNION | INTERSECT | MINUS }

SELECT . . . . . . FROM . . . . . .

{ UNION | INTERSECT | MINUS }

SELECT . . . . . . FROM . . . . . .

The UNION operator

EXAMPLE

SELECT EMPNO , ENAME ,SAL, COMM, DEPTNOFROM EMPWHERE SAL < 1500UNIONSELECT EMPNO , ENAME ,SAL, COMM, DEPTNOFROM EMPWHERE COMM IS NOT NULL;



Result of the first SELECT Result of the second SELECT

EMPNO ENAME SAL COMM DEPTNO EMPNO ENAME SAL COMM DEPTNO7369 SMITH 800 20 7499 ALLEN 1600 300 307521 WARD 1250 500 30 7521 WARD 1250 500 307654 MARTIN 1250 1400 30 7654 MARTIN 1250 1400 307876 ADAMS 1100 20 7844 TURNER 1500 0 307900 JAMES 950 307934 MILLER 1300 10

ß ß union

of the two results

ßEMPNO ENAME SAL COMM DEPTNO

7369 SMITH 800 207521 WARD 1250 500 307654 MARTIN 1250 1400 307876 ADAMS 1100 207900 JAMES 950 307934 MILLER 1300 107499 ALLEN 1600 300 307844 TURNER 1500 0 30

Rule1: The two SELECTs must have the same number of columns. These columns must match in types.

Rule2: No redundant rows in the result (since the result is a set in mathematics meaning).

Think:

Write down the above query otherwise?

Look at this example:

SELECT ENAME NAME, 'SAL' TYPE, SAL AMOUNTFROM EMPWHERECOMM IS NOT NULLUNIONSELECT ENAME ,'COMM' , COMMFROM EMPWHERECOMM IS NOT NULL;

Note that the UNION is used to split on many rows data coming from the same record.

NAME TYPE AMOUNT------------ ------------ -------------ALLEN COMM 300ALLEN SAL 1600MARTIN COMM 1400MARTIN SAL 1250TURNER COMM 0TURNER SAL 1500WARD COMM 500



WARD SAL 1250

The MINUS operator

EXAMPLE

SELECT DEPTNOFROM DEPTMINUSSELECT DEPTNOFROM EMP;

T1 : result of the first SELECT T2 : result of the second SELECT

DEPTNO DEPTNO10 1020 2030 3040

ß ß

T1 minus T2

ß DEPTNO

40

Think

What would be the result of the MINUS in the reverse order ( T2 minus T1)?

The INTERSECT operator

EXAMPLES

SELECT EMPNO , ENAME ,SAL, COMM, DEPTNO



FROM EMPWHERE SAL < 1500INTERSECTSELECT EMPNO , ENAME ,SAL, COMM, DEPTNOFROM EMPWHERE COMM IS NOT NULL;

Result of the first SELECT Result of the second SELECT

EMPNO ENAME SAL COMM DEPTNO EMPNO ENAME SAL COMM DEPTNO7369 SMITH 800 20 7499 ALLEN 1600 300 307521 WARD 1250 500 30 7521 WARD 1250 500 307654 MARTIN 1250 1400 30 7654 MARTIN 1250 1400 307876 ADAMS 1100 20 7844 TURNER 1500 0 307900 JAMES 950 307934 MILLER 1300 10

ß ß

intersect of the two results

ß EMPNO ENAME SAL COMM DEPTNO7521 WARD 1250 500 307654 MARTIN 1250 1400 30

SELECT ENAME , JOB, SAL FROM EMP WHERE JOB ='MANAGER'INTERSECTSELECT ENAME , JOB, SAL FROM EMP WHERE SAL >= 2000;

ENAME JOB SALBLAKE MANAGER 2850CLARK MANAGER 2450JONES MANAGER 2975

Think

Could you write this query without using the INTERSECT operator ?

NESTED QUERIES

Nested queries are queries within queries. This occurs when the WHERE clause of a given SFW block contains itself a sub query (SFW command ). The sub query ( or inner query/block) :

o May return only one column.o is able to return data from one row or several.o is typical coded as part of a predicate containing =, which is used when it returns a single value, or IN when it

returns several values.o often can be used as a substitute for a join when we want to extract columns from a single table.

We distinguish two kinds of nested queries :



o simple nested queries : the inner query is independent - from the outer query - in the sense that it is completely processed before using its result within the outer block.

o correlated (synchronized) queries: the inner query and the outer one have an inter-block reference so that the inner subselect must be processed more than once.

General form

SELECT ..........

FROM table-name

WHEREcol operator ( SELECT .....

FROM table-name

WHERE ...);

F Sub query empty Þ error.

SIMPLE NESTED QUERIES

EXAMPLE

DISPLAY NAME , SALARY , JOB FOR EMPLOYEES HAVING A SALARY > JONES ' SALARY.

SELECT ENAME , SAL , JOB ¬outer queryFROM EMPWHERESAL > ( SELECT SAL ¬inner query

FROM EMP (sub query)WHERE ENAME = 'JONES' );

ENAME SAL JOB------------ -------- ---------------SCOTT 3000 ANALYSTKING 5000 PRESIDENTFORD 3000 ANALYST

Sub query processing

Note that :o The sub query is independent with the outer query.o Oracle ( RDBMS) evaluates the whole sub query, then the outer query using the obtained result.

Think

Could you write the query otherwise (without nested blocks) ?



Sub queries RETURNING MANY ROWS

When the sub query may return more than one row, we must use the operators able to compare one value to a set of values:

IN{ = | != | > | >= | < | <= | } { ANY | ALL }

as expression < ANY sub queryor expression < ALL sub query

When < is used with ALL, it means the sub query will return several values, and the expression's value should be smaller than all of those values.If the statement is of the form expression <ANY sub query, the value of the expression should be less than at least one of the returned values. The other operators ( >, = , ... ) work in a similar manner.

EXAMPLE

DISPLAY NAME , SAL FOR EMPLOYEES EARNING MORE THAN ALL EMPLOYEES IN THE DEPT. 30.

SELECT ENAME, SALFROM EMPWHERE SAL > ALL ( SELECT SAL

FROM EMPWHERE DEPTNO =30);

ENAME SAL---------- --------JONES 2975SCOTT 3000KING 5000FORD 3000

DISPLAY NAME , JOB FOR EMPLOYEES IN DEPT 10 HAVING THE SAME JOB AS AN EMPLOYEE (anyone) OF THE 'SALES' DEPT.



MULTILEVEL NESTED QUERIES

EXAMPLE

DISPLAY NAME , JOB, SAL FOR EMPLOYEES HAVING THE SAME JOB AS 'JONES' OR EARNING MORE THAN 'FORD'.Result should be sorted on job and sal.

Think: Could you express the same query without using nested blocks?

CORRELATED SUB QUERIES

For all the sub queries we have examined thus far, ORACLE(RDBMS) evaluated the innermost (last) query first. The evaluation of the sub query returned a single value ( or a set of values). It then substituted this value into the outer level's predicate. Only then did oracle use those results to evaluate the next query.

There are times, however, when this isn't desirable - situations where the inner query depends on a value or values determined by an outer query. In this case, the outer query is repeatedly evaluated and a value determined. Then the inner query is evaluated based on the previously determined value. This type of query is called correlated sub query.

EXAMPLE

LIST OF EMPLOYEES ( ENAME) WORKING IN A DEPT. OTHER THAN THE DEPT. OF THEIR MANAGER.

SELECT ENAMEFROM EMP X ß correlation nameWHERE DEPTNO != ( SELECT DEPTNO

FROM EMPWHEREX.MGR = EMPNO )

AND MGR IS NOT NULL;

ENAME------------JONESBLAKE

F Necessary to use correlation name for the table.

CORRELATED Sub query processing

Think: Could you write this query differently? (without synchronized queries).

The EXISTS operatorThe EXISTS operator is useful to test for the existence of rows in a table that match some specified condition. Such a test is usually performed within a sub query and is connected to the outer query by using the syntax below:



SELECT ....FROM .....WHERE [ NOT] EXISTS ( SELECT...

FROM ....WHERE ...);

EXISTS here represents the existential quantifier. The expression "EXISTS (SELECT ...)" evaluates to true if and only if the result of evaluating its "SELECT..." is not empty (at least one record returned by the sub query following EXISTS).

NOT EXISTS is the opposite, and then evaluates to true if the result of the sub query is empty.

EXISTS is not empty

NOT EXISTS is empty

EXAMPLE

DISPLAY EMPLOYEES WORKING IN DEPTs. HAVING AT LEAST ONE "ANALYST".

SELECT *FROM EMP EWHERE EXISTS ( SELECT *

FROM EMPWHERE JOB = 'ANALYST'AND E.DEPTNO = DEPTNO );

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO7369 SMITH CLERK 7902 17-DEC-80 800.00 207566 JONES MANAGER 7839 02-APR-81 2975.00 207788 SCOTT ANALYST 7566 09-DEC-82 3000.00 207876 ADAMS CLERK 7788 12-JAN-83 1100.00 207902 FORD ANALYST 7566 03-DEC-81 3000.00 20

For each employee E, the sub query finds a set of all ANALYSTs working in the same department as the employee E. Since the set is not empty then EXISTS evaluates to true and the employee E is displayed.

Think:Could you write the query without using the EXISTS operator ? Try to use IN.

DISPLAY DEPARTMENTs WITHOUT EMPLOYEES.

SELECT *FROM DEPTWHERE NOT EXISTS (SELECT*

FROM EMPWHERE DEPT.DEPTNO = DEPTNO

);Think:

This query could be formulated twice differently using :- MINUS- NOT IN.

SUB QUERIES RETURNING MANY COLUMNS

EXAMPLE



DISPLAY NAME , JOB , SALARY FOR EMPLOYEES HAVING THE SAME JOB , SAME SALARY AND SAME DEPT. AS 'FORD'.

SELECT ENAME, JOB , SALFROM EMPWHERE (JOB , SAL, DEPTNO) =

( SELECT JOB, SAL, DEPTNOFROM EMPWHERE ENAME = 'FORD' )

AND ENAME != 'FORD';

ENAME JOB SAL---------- ---------- ------SCOTT ANALYST 3000

EXPRESSIONS AND FUNCTIONS

Although "complete" as the relational algebra requires, the retrieval language described so far is still inadequate for many practical problems. For example, even a query as simple as "How many employees are there ?" cannot be expressed using the constructs given till now. SQL therefore provides a number of special built-in functions to enhance its basic retrieval power.

The functions currently supported are :

COUNT number of valuesSUM sum of the valuesAVG average of the valuesMAX largest value in a columnMIN smallest value in a column

Other functions may be encountered in different SQL implementations, some of them are :

NVL check on the null valueDECODE multiple choice testTO_CHAR Date to char conversionTO_DATE Char to date conversionEtc.,

THE NULL VALUE FUNCTION

Sometimes it is necessary to detect the null values in a column and work with an alternate (substitute) value. For example, the expression SAL + COMM may involve a problem if the commission column is null: naturally we cannot add an unknown value. To solve this problem, the NVL function should be applied on the COMM field to substitute the null by another value - let be zero - in the computation.

Syntax

NVL ( expression , val )

IF expression is null THEN the function returns the value val

ELSE (* expression is not null *)the function returns the value of expression;

ENDIF



EXAMPLE

SELECT ENAME, SAL, COMM, SAL+NVL(COMM,0) 'MONTHLY INCOME'FROM EMP;

ENAME SAL COMM MONTHLY INCOMEADAMS 1100 1100SMITH 800 800ALLEN 1600 300 1900WARD 1250 500 1750JONES 2975 2975MARTIN 1250 1400 2650BLAKE 2850 2850CLARK 2450 2450SCOTT 3000 3000KING 5000 5000TURNER 1500 0 1500JAMES 950 950FORD 3000 3000MILLER 1300 1300

This enables to substitute - in the expression- the value zero each time the employee's commission is null.F without using NVL, the expression takes an undefined value for employees without commissions.

GROUP FUNCTIONS

Summary information (such as average or total) may be obtained for groups of rows through the use of group functions (aggregate functions). Group functions are:

AVG( [DISTINCT | ALL ] n) : Average value of n, ignoring null values.COUNT( [DISTINCT | ALL ] expr|*) : Number of times the numeric expression expr evaluates to something other than

NULL. The asterisk(*) makes COUNT counts all selected rows.MAX( [DISTINCT | ALL ] expr) : Maximum value of expr.MIN( [DISTINCT | ALL ] expr) : Minimum value of expr.SUM( [DISTINCT | ALL ] , n) : Sum values of n, ignoring null values.

USING GROUP FUNCTIONS

A group function can be applied to a subset of the rows in a table by using the WHERE clause.

EXAMPLES

To calculate the average salary of all employees,SELECT AVG(SAL)FROM EMP;

To find the minimum salary earned by a clerk,SELECT MIN(SAL)FROM EMPWHERE JOB='CLERK';

To count the number of employees in department 20, SELECT COUNT(*)FROM EMPWHERE DEPTNO= 20;



THE GROUP BY CLAUSE

Group functions may be used with a GROUP BY clause, that splits the table into the specified groups, returning one summary row for each group.

SyntaxSELECTFROM WHEREGROUP BY expressionHAVING predicate

EXAMPLES

FIND THE NUMBER OF EMPLOYEES BY JOB.

SELECT JOB , COUNT(*) NB_EMPFROM EMPGROUP BY JOB;

First step: Grouping employees BY JOB The GROUP BY clause demands SQL to construct groups of employees with the same value for JOB as :

EMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO7902 FORD ANALYST 7566 03-DEC-81 3000.00 20 7788 SCOTT ANALYST 7566 09-DEC-82 3000.00 20

7876 ADAMS CLERK 7788 01-JUN-86 1100.00 207900 JAMES CLERK 7698 03-DEC-81 950.00 307934 MILLER CLERK 7788 23-JAN-82 1300.00 107369 SMITH CLERK 7902 01-JUN-86 800.00 20

7698 BLAKE MANAGER 7839 01-MAY-81 2850.00 307782 CLARK MANAGER 7839 09-JUN-81 2450.00 107566 JONES MANAGER 7839 01-JUN-86 2975.00 20

7839 KING PRESIDENT 17-NOV-81 5000.00 10

7499 ALLEN SALESMAN 7698 20-FEB-81 1600.00 300.00 307654 MARTIN SALESMAN 7698 28-SEP-81 1250.00 1400.00 307844 TURNER SALESMAN 7698 08-SEP-81 1500.00 0.00 307521 WARD SALESMAN 7698 22-FEB-81 1250.00 500.00 30F 5 groups of rows ( ANALYST , CLERK , MANAGER , PRESIDENT , SALESMAN )

Second step: Counting the number of rows BY GROUP

The aggregate ( group) function COUNT is applied to each subset of employees. COUNT(*) counts the number of rows within each group.

JOB NB_EMP--------------- ----------------ANALYST 2CLERK 4MANAGER 3PRESIDENT 1 SALESMAN 4

FIND THE SUM OF SALARIES BY DEPARTMENT

SELECT DEPTNO , SUM(SAL)FROM EMPGROUP BY DEPTNO;



DEPTNO ---------------

SUM(SAL)---------------

10 875020 10875 30 9400

FIND THE AVERAGE SALARY FOR CLERKS IN EACH DEPARTMENT

SELECT DEPTNO , AVG(SAL) AVERAGEFROM EMPWHERE JOB = 'CLERK'GROUP BY DEPTNO;

THE HAVING CLAUSE

Use a HAVING clause if you wish to specify which groups are to be displayed, i.e. depending on the values of a group function.

SyntaxSELECT column (s)FROM table (s)WHERE row condition (s)GROUP BY column (s)HAVING group of rows condition (s)ORDER BY column (s);

EXAMPLES

To show the average salary for all DEPARTMENTs employing more than three people, execute:

SELECT DEPTNO , AVG(SAL)FROM EMPGROUP BY DEPTNOHAVING COUNT (*) > 3;

DEPTNO-----------

AVG(SAL)-------------

20 217530 1566.66667

To display only those jobs, where the maximum salary is greater than or equal to $3000, enter:

SELECT JOB , MAX(SAL)FROM EMPHAVING MAX (SAL) >= 3000GROUP BY JOB;

JOB-----------

MAX(SAL)-------------

ANALYST 3000PRESIDENT 5000

The WHERE clause may still be used to exclude rows from consideration. It processes the rows one by one, whereas the HAVING works on each group of rows.Note: The HAVING clause may preceed GROUP BY clause.



Complete syntax of the SELECT command

SELECT [ALL|DISTINCT] { [table]*|expr[alias],[expr[alias],..}FROM table[alias], table[alias],...[WHERE condition][CONNECT BY condition [START WITH condition]][GROUP BY expr,expr,...] [HAVING condition][{UNION | INTERSECT | MINUS } SELECT ...][ORDER BY{expr|posn} [ASC | DESC],{expr|posn} [ASC|DESC],.

| the horizontal bar indicates a choice between alternatives{ } compulsory choice[ ] for optionsUnderlining for default options

Table: EMPEMPNO ENAME JOB MGR HIREDATE SAL COMM DEPTNO 7369 SMITH CLERK 7902 17-DEC-80 800 207499 ALLEN SALESMAN 7698 20-FEB-81 1600 300 30 7521 WARD SALESMAN 7698 22-FEB-81 1250 500 30 7566 JONES MANAGER 7839 02-APR-81 2975 20 7654 MARTIN SALESMAN 7698 28-SEP-81 1250 1400 307698 BLAKE MANAGER 7839 01-MAY-81 2850 30 7782 CLARK MANAGER 7839 09-JUN-81 2450 10 7788 SCOTT ANALYST 7566 09-DEC-82 3000 20 7839 KING PRESIDENT 17-NOV-81 5000 10 7844 TURNER SALESMAN 7698 08-SEP-81 1500 0 307876 ADAMS CLERK 7788 12-JAN-83 1100 20 7900 JAMES CLERK 7698 03-DEC-81 950 307902 FORD ANALYST 7566 03-DEC-81 3000 20 7934 MILLER CLERK 7782 23-JAN-82 1300 10

Table: DEPTDEPTNO DNAME LOC10 ACCOUNTING NEW YORK20 RESEARCH DALLAS30 SALES CHICAGO40 OPERATIONS BOSTON

Table : SALGRADEGRADE LOSAL HISAL1 700 12002 1201 14003 1401 20004 2001 30005 3001 9999


introduction to 4gl and oracle … selec… · web viewintroduction to 4gl and oracle...

Documents