Functions():
Functions
are of two types they are
1)
Scalar(Single row )
functions
2)
Aggregate functions
Scalar(Single row) Functions: Which is
used to perform the operations on the dual table.
Single-row functions return a single
result row for every row of a queried table or view. These functions can appear
in select lists,
WHERE clauses, START WITH and CONNECT BY clauses, and HAVING clauses.|
Number Functions
|
||
|
Character Functions
|
||
|
UCASE
See UPPER
|
||
|
LCASE See LOWER
|
||
|
Character Functions
Returning Number Values
|
||
|
BIT_LENGTH (See LENGTH)
|
OCTET_LENGTH
(See LENGTH)
|
|
|
CHAR_LENGTH (See LENGTH)
|
||
|
Date Functions
|
||
|
Conversion Functions
|
||
|
Other Functions
|
||
|
Grouping Functions
|
||
Numeric Functions
Numeric
functions accept numeric input and return numeric values. Most numeric
functions that return
NUMBER values that are accurate to 38 decimal digits. The
transcendental functions COS, COSH, EXP, LN, LOG, SIN, SINH, SQRT, TAN, and TANH are accurate to 36 decimal digits. The transcendental functions ACOS,ASIN, ATAN, and ATAN2 are accurate to 30 decimal digits. The numeric functions are:ABS
ACOS
ASIN
ATAN
ATAN2
BITAND
CEIL
COS
COSH
EXP
FLOOR
LN
LOG
MOD
NANVL
POWER
REMAINDER
ROUND (number)
SIGN
SIN
SINH
SQRT
TAN
TANH
TRUNC (number)
WIDTH_BUCKET
Character Functions Returning Character Values
Character functions that
return character values return values of the following datatypes unless otherwise
documented:
·
If the input argument is
CHAR or VARCHAR2, then the value
returned is VARCHAR2.
·
If the input argument is
NCHAR or NVARCHAR2, then the value
returned is NVARCHAR2.
The length of the value
returned by the function is limited by the maximum length of the datatype
returned.
·
For functions that return
CHAR or VARCHAR2, if the length of the
return value exceeds the limit, then Oracle Database truncates it and returns
the result without an error message.
·
For functions that return
CLOB values, if the length of the return values exceeds the limit,
then Oracle raises an error and returns no data.
The character functions
that return character values are:
CHR
CONCAT
INITCAP
LOWER
LPAD
LTRIM
NLS_INITCAP
NLS_LOWER
NLSSORT
NLS_UPPER
REGEXP_REPLACE
REGEXP_SUBSTR
REPLACE
RPAD
RTRIM
SOUNDEX
SUBSTR
TRANSLATE
TREAT
TRIM
UPPER
NLS
Character Functions
The NLS character functions return information about the
character set. The NLS character functions are:
Character
Functions Returning Number Values
Character functions that
return number values can take as their argument any character datatype.
The character functions
that return number values are:
Datetime Functions
Datetime functions operate on date (
DATE), timestamp (TIMESTAMP, TIMESTAMP WITH TIME ZONE, and TIMESTAMP WITH LOCAL TIME ZONE),& interval (INTERVAL DAY
TO SECOND, INTERVAL
YEAR
TO MONTH) values.
Some
of the datetime functions were designed for the Oracle
DATE datatype (ADD_MONTHS, CURRENT_DATE, LAST_DAY, NEW_TIME, and NEXT_DAY). If you provide a
timestamp value as their argument, Oracle Database internally converts the
input type to a DATE value and returns a DATE value. The exceptions
are the MONTHS_BETWEEN function, which returns a number, and the ROUND and TRUNC functions, which do not accept timestamp or interval values at
all.
The remaining datetime functions were designed to accept any of
the three types of data (date, timestamp, and interval) and to return a value
of one of these types.
The datetime functions are:
ADD_MONTHS
CURRENT_DATE
CURRENT_TIMESTAMP
DBTIMEZONE
EXTRACT (datetime)
FROM_TZ
LAST_DAY
LOCALTIMESTAMP
MONTHS_BETWEEN
NEW_TIME
NEXT_DAY
NUMTODSINTERVAL
NUMTOYMINTERVAL
ROUND (date)
SESSIONTIMEZONE
SYS_EXTRACT_UTC
SYSDATE
SYSTIMESTAMP
TO_CHAR (datetime)
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_DSINTERVAL
TO_YMINTERVAL
TRUNC (date)
TZ_OFFSET
General Comparison
Functions
The general comparison functions determine the greatest and or
least value from a set of values. The general comparison functions are:
Conversion Functions
Conversion functions convert a value from one datatype to
another. Generally, the form of the function names follows the convention datatype
TO datatype.
The first datatype is the input datatype. The second datatype is the output
datatype. The SQL conversion functions are:ASCIISTR
BIN_TO_NUM
CAST
CHARTOROWID
COMPOSE
CONVERT
DECOMPOSE
HEXTORAW
NUMTODSINTERVAL
NUMTOYMINTERVAL
RAWTOHEX
RAWTONHEX
ROWIDTOCHAR
ROWIDTONCHAR
SCN_TO_TIMESTAMP
TIMESTAMP_TO_SCN
TO_BINARY_DOUBLE
TO_BINARY_FLOAT
TO_CHAR (character)
TO_CHAR (datetime)
TO_CHAR (number)
TO_CLOB
TO_DATE
TO_DSINTERVAL
TO_LOB
TO_MULTI_BYTE
TO_NCHAR (character)
TO_NCHAR (datetime)
TO_NCHAR (number)
TO_NCLOB
TO_NUMBER
TO_DSINTERVAL
TO_SINGLE_BYTE
TO_TIMESTAMP
TO_TIMESTAMP_TZ
TO_YMINTERVAL
TO_YMINTERVAL
TRANSLATE ... USING
UNISTR
Large Object Functions
The large object functions operate on LOBs. The large object
functions are:
Collection
Functions
The collection functions operate on nested tables and varrays.
The SQL collection functions are:
Aggregate Functions:
Aggregate functions return a single
result row based on groups of rows, rather than on single rows. Aggregate
functions can appear in select lists and in
ORDER BY and HAVING clauses. They are commonly used with the GROUP BY clause in a SELECT statement, where Oracle Database divides
the rows of a queried table or view into groups. In a query containing a GROUP BY clause, the elements of the select list
can be aggregate functions, GROUP BY expressions, constants, or expressions
involving one of these.
AVG
COLLECT
CORR
CORR_*
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
GROUP_ID
GROUPING
GROUPING_ID
LAST
MAX
MEDIAN
MIN
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
RANK
COLLECT
CORR
CORR_*
COUNT
COVAR_POP
COVAR_SAMP
CUME_DIST
DENSE_RANK
FIRST
GROUP_ID
GROUPING
GROUPING_ID
LAST
MAX
MEDIAN
MIN
PERCENTILE_CONT
PERCENTILE_DISC
PERCENT_RANK
RANK
STATS_BINOMIAL_TEST
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MODE
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST_*
STATS_WSR_TEST
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE
STATS_CROSSTAB
STATS_F_TEST
STATS_KS_TEST
STATS_MODE
STATS_MW_TEST
STATS_ONE_WAY_ANOVA
STATS_T_TEST_*
STATS_WSR_TEST
STDDEV
STDDEV_POP
STDDEV_SAMP
SUM
VAR_POP
VAR_SAMP
VARIANCE
select * from emp;
EMPNO ENAME
JOB MGR
HIREDATE SAL COMM
DEPTNO
7369 SMITH CLERK 7902
12/17/1980 800 20
7499 ALLEN
SALESMAN 7698
2/20/1981 1600 300
30
7521 WARD
SALESMAN 7698
2/22/1981 1250 500
30
7566 JONES
MANAGER 7839
4/2/1981 2975 20
7654 MART
SALESMAN 7698 9/28/1981
1250 1400 30
7698 BLAKE
MANAGER 7839
5/1/1981 2850 30
7782 CLARK
MANAGER 7839
6/9/1981 2450 10
7788 SCOTT
ANALYST 7566
12/9/1982 3000 20
7839 KING
PRESIDENT 11/17/1981 5000
10
7844 TURN SALESMAN 7698
9/8/1981 1500 0
30
7876 ADAMS
CLERK 7788
1/12/1983 1100 20
7900 JAMES
CLERK 7698
12/3/1981 950 30
select * from dept;
DEPTNO DNAME LOC
10 ACCOUNTING NEW
YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
40 OPERATIONS BOSTON
select * from salgrade;
GRADE LOSAL HISAL
1 700 1200
2 1201 1400
3 1401 2000
4 2001 3000
5 3001 9999
***********************
DEFINING COLUMN ALIAS:
---------------------------------------
SELECT EMPNO EMPNUMBER,ENAME EMPNAME,SAL
SALARY FROM EMP;
EMPNUMBER EMPNAME SALARY
7369 SMITH
800
7499 ALLEN 1600
7521 WARD
1250
7566 JONES
2975
7654 MARTI 1250
7698 BLAKE
2850
7782 CLARK
2450
7788 SCOTT
3000
7839 KING
5000
7844 TURNE 1500
7876 ADAMS 1100
7900 JAMES
950
7902 FORD
3000
7934 MILLER
1300
SELECT GRADE AS "SALGRADE",HISAL
"HIGH SALARY RANGE",LOSAL "LOW SALARY RANGE" FROM SALGRADE;
SALGRADE HIGH SALARY RANGE LOW
SALARY RANGE
1 1200 700
2 1400 1201
3 2000 1401
4 3000 2001
5 9999 3001
SELECT EMPNO "EMPNUMBER",SAL
"BASIC",SAL*0.25 HRA,SAL*0.20 DA,SAL*0.15
"PF",SAL+SAL*0.25+SAL*0.15"GROSS" FROM EMP;
EMPNUMBER BASIC HRA DA PF GROSS
7369 800 200 160 120 1120
7499 1600 400 320 240 2240
7521 1250 312.5 250 187.5 1750
7566 2975 743.75 595 446.25 4165
7654 1250 312.5 250 187.5 1750
7698 2850 712.5 570 427.5 3990
7782 2450 612.5 490 367.5 3430
7788 3000 750 600 450 4200
7839 5000 1250 1000 750 7000
7844 1500 375 300 225 2100
7876 1100 275 220 165 1540
7900 950 237.5 190 142.5 1330
7902 3000 750 600 450 4200
7934 1300 325 260 195 1820
CONCATENATION OPERATOR:
***************************
SELECT EMPNO||ENAME FROM EMP;
EMPNO||ENAME
7369SMITH
7499ALLEN
7521WARD
7566JONES
7654MARTIN
7698BLAKE
7782CLARK
7788SCOTT
7839KING
7844TURNER
7876ADAMS
7900JAMES
7902FORD
7934MILLER
SELECT 'THE BASIC SALARY OF'||ENAME||'IS
RS'||SAL EMPLOYEE FROM EMP;
EMPLOYEE
THE BASIC SALARY OFSMITHIS RS800
THE BASIC SALARY OFALLENIS RS1600
THE BASIC SALARY OFWARDIS RS1250
THE BASIC SALARY OFJONESIS RS2975
THE BASIC SALARY OFMARTINIS
RS1250
THE BASIC SALARY OFBLAKEIS RS2850
THE BASIC SALARY OFCLARKIS RS2450
THE BASIC SALARY OFSCOTTIS RS3000
THE BASIC SALARY OFKINGIS RS5000
THE BASIC SALARY OFTURNERIS
RS1500
THE BASIC SALARY OFADAMSIS RS1100
THE BASIC SALARY OFJAMESIS RS950
THE BASIC SALARY OFFORDIS RS3000
THE BASIC SALARY OFMILLERIS
RS1300
SELECT EMPNO||ENAME||',DESIGNATION IS'||JOB
"EMPLOYEES INFORMATION" FROM EMP;
EMPLOYEES INFORMATION
7369SMITH,DESIGNATION ISCLERK
7499ALLEN,DESIGNATION ISSALESMAN
7521WARD,DESIGNATION ISSALESMAN
7566JONES,DESIGNATION ISMANAGER
7654MARTIN,DESIGNATION ISSALESMAN
7698BLAKE,DESIGNATION ISMANAGER
7782CLARK,DESIGNATION ISMANAGER
7788SCOTT,DESIGNATION ISANALYST
7839KING,DESIGNATION ISPRESIDENT
7844TURNER,DESIGNATION ISSALESMAN
7876ADAMS,DESIGNATION ISCLERK
7900JAMES,DESIGNATION ISCLERK
7902FORD,DESIGNATION ISANALYST
7934MILLER,DESIGNATION ISCLERK
USING LITERAL CHARACTER STRINGS:
*************************************
SELECT ENAME||':'||'MONTH SALARY='||SAL AS
SALARIES FROM EMP;
SALARIES
SMITH:MONTH SALARY=800
ALLEN:MONTH SALARY=1600
WARD:MONTH SALARY=1250
JONES:MONTH SALARY=2975
MARTIN:MONTH SALARY=1250
BLAKE:MONTH SALARY=2850
CLARK:MONTH SALARY=2450
SCOTT:MONTH SALARY=3000
KING:MONTH SALARY=5000
TURNER:MONTH SALARY=1500
ADAMS:MONTH SALARY=1100
JAMES:MONTH SALARY=950
FORD:MONTH SALARY=3000
MILLER:MONTH SALARY=1300
SELECT 'THE DESIGNATION
OF'||ENAME||'IS'||JOB AS DESIGNATION FROM EMP;
DESIGNATION
THE DESIGNATION OFSMITHISCLERK
THE DESIGNATION OFALLENISSALESMAN
THE DESIGNATION OFWARDISSALESMAN
THE DESIGNATION OFJONESISMANAGER
THE DESIGNATION
OFMARTINISSALESMAN
THE DESIGNATION OFBLAKEISMANAGER
THE DESIGNATION OFCLARKISMANAGER
THE DESIGNATION OFSCOTTISANALYST
THE DESIGNATION OFKINGISPRESIDENT
THE DESIGNATION
OFTURNERISSALESMAN
THE DESIGNATION OFADAMSISCLERK
THE DESIGNATION OFJAMESISCLERK
THE DESIGNATION OFFORDISANALYST
THE DESIGNATION OFMILLERISCLERK
SELECT 'THE EMPLOYEE NAME
IS:'||ENAME||'AND'||'DESIGNATION IS:'||JOB FROM EMP;
'THEEMPLOYEENAMEIS:'||ENAME||'AND'||'DESIGNATIONIS:'||JOB
THE EMPLOYEE NAME
IS:SMITHANDDESIGNATION IS:CLERK
THE EMPLOYEE NAME
IS:ALLENANDDESIGNATION IS:SALESMAN
THE EMPLOYEE NAME
IS:WARDANDDESIGNATION IS:SALESMAN
THE EMPLOYEE NAME
IS:JONESANDDESIGNATION IS:MANAGER
THE EMPLOYEE NAME
IS:MARTINANDDESIGNATION IS:SALESMAN
THE EMPLOYEE NAME
IS:BLAKEANDDESIGNATION IS:MANAGER
THE EMPLOYEE NAME
IS:CLARKANDDESIGNATION IS:MANAGER
THE EMPLOYEE NAME
IS:SCOTTANDDESIGNATION IS:ANALYST
THE EMPLOYEE NAME
IS:KINGANDDESIGNATION IS:PRESIDENT
THE EMPLOYEE NAME
IS:TURNERANDDESIGNATION IS:SALESMAN
THE EMPLOYEE NAME
IS:ADAMSANDDESIGNATION IS:CLERK
THE EMPLOYEE NAME
IS:JAMESANDDESIGNATION IS:CLERK
THE EMPLOYEE NAME
IS:FORDANDDESIGNATION IS:ANALYST
THE EMPLOYEE NAME
IS:MILLERANDDESIGNATION IS:CLERK
SELECT 'THE ANNUAL SALARY OF'||ENAME||'IS'||SAL*12
AS ANNUAL_SALARY FROM EMP;
ANNUAL_SALARY
THE ANNUAL SALARY OFSMITHIS9600
THE ANNUAL SALARY OFALLENIS19200
THE ANNUAL SALARY OFWARDIS15000
THE ANNUAL SALARY OFJONESIS35700
THE ANNUAL SALARY OFMARTINIS15000
THE ANNUAL SALARY OFBLAKEIS34200
THE ANNUAL SALARY OFCLARKIS29400
THE ANNUAL SALARY OFSCOTTIS36000
THE ANNUAL SALARY OFKINGIS60000
THE ANNUAL SALARY OFTURNERIS18000
THE ANNUAL SALARY OFADAMSIS13200
THE ANNUAL SALARY OFJAMESIS11400
THE ANNUAL SALARY OFFORDIS36000
THE ANNUAL SALARY OFMILLERIS15600
SELECT DNAME ||'DEPARTMENT IS LOCATED
AT'||LOC FROM DEPT;
DNAME||'DEPARTMENTISLOCATEDAT'||LOC
ACCOUNTINGDEPARTMENT IS LOCATED
ATNEW YORK
RESEARCHDEPARTMENT IS LOCATED
ATDALLAS
SALESDEPARTMENT IS LOCATED
ATCHICAGO
OPERATIONSDEPARTMENT IS LOCATED
ATBOSTON
SELECT ENAME||'JIONED THE ORGANIZATION
ON'||HIREDATE FROM EMP;
ENAME||'JIONEDTHEORGANIZATIONON'||HIREDATE
SMITHJIONED THE ORGANIZATION
ON17-DEC-80
ALLENJIONED THE ORGANIZATION
ON20-FEB-81
WARDJIONED THE ORGANIZATION
ON22-FEB-81
JONESJIONED THE ORGANIZATION
ON02-APR-81
MARTINJIONED THE ORGANIZATION
ON28-SEP-81
BLAKEJIONED THE ORGANIZATION
ON01-MAY-81
CLARKJIONED THE ORGANIZATION
ON09-JUN-81
SCOTTJIONED THE ORGANIZATION
ON09-DEC-82
KINGJIONED THE ORGANIZATION
ON17-NOV-81
TURNERJIONED THE ORGANIZATION
ON08-SEP-81
ADAMSJIONED THE ORGANIZATION
ON12-JAN-83
JAMESJIONED THE ORGANIZATION
ON03-DEC-81
FORDJIONED THE ORGANIZATION
ON03-DEC-81
MILLERJIONED THE ORGANIZATION
ON23-JAN-82
SELECT ENAME||'WORKS IN DEPARTMENT
NUMBER'||DEPTNO||'AS'||JOB FROM EMP;
ENAME||'WORKSINDEPARTMENTNUMBER'||DEPTNO||'AS'||JOB
SMITHWORKS IN DEPARTMENT
NUMBER20ASCLERK
ALLENWORKS IN DEPARTMENT
NUMBER30ASSALESMAN
WARDWORKS IN DEPARTMENT
NUMBER30ASSALESMAN
JONESWORKS IN DEPARTMENT
NUMBER20ASMANAGER
MARTINWORKS IN DEPARTMENT
NUMBER30ASSALESMAN
BLAKEWORKS IN DEPARTMENT
NUMBER30ASMANAGER
CLARKWORKS IN DEPARTMENT
NUMBER10ASMANAGER
SCOTTWORKS IN DEPARTMENT
NUMBER20ASANALYST
KINGWORKS IN DEPARTMENT
NUMBER10ASPRESIDENT
TURNERWORKS IN DEPARTMENT
NUMBER30ASSALESMAN
ADAMSWORKS IN DEPARTMENT
NUMBER20ASCLERK
JAMESWORKS IN DEPARTMENT
NUMBER30ASCLERK
FORDWORKS IN DEPARTMENT
NUMBER20ASANALYST
MILLERWORKS IN DEPARTMENT
NUMBER10ASCLERK
SELECT 'THE EMPLOYEE NAME
IS:'||ENAME||',DESIGNATION IS'||JOB FROM EMP;
'THEEMPLOYEENAMEIS:'||ENAME||',DESIGNATIONIS'||JOB
THE EMPLOYEE NAME
IS:SMITH,DESIGNATION ISCLERK
THE EMPLOYEE NAME
IS:ALLEN,DESIGNATION ISSALESMAN
THE EMPLOYEE NAME
IS:WARD,DESIGNATION ISSALESMAN
THE EMPLOYEE NAME
IS:JONES,DESIGNATION ISMANAGER
THE EMPLOYEE NAME
IS:MARTIN,DESIGNATION ISSALESMAN
THE EMPLOYEE NAME
IS:BLAKE,DESIGNATION ISMANAGER
THE EMPLOYEE NAME
IS:CLARK,DESIGNATION ISMANAGER
THE EMPLOYEE NAME
IS:SCOTT,DESIGNATION ISANALYST
THE EMPLOYEE NAME
IS:KING,DESIGNATION ISPRESIDENT
THE EMPLOYEE NAME
IS:TURNER,DESIGNATION ISSALESMAN
THE EMPLOYEE NAME IS:ADAMS,DESIGNATION
ISCLERK
THE EMPLOYEE NAME
IS:JAMES,DESIGNATION ISCLERK
THE EMPLOYEE NAME
IS:FORD,DESIGNATION ISANALYST
THE EMPLOYEE NAME
IS:MILLER,DESIGNATION ISCLERK
SELECT 'THE GROSS SALARY
OF'||ENAME||'IS'||(SAL+SAL*.25+SAL*.20-SAL*.15) FROM EMP;
'THEGROSSSALARYOF'||ENAME||'IS'||(SAL+SAL*.25+SAL*.20-SAL*.15)
THE GROSS SALARY OFSMITHIS1040
THE GROSS SALARY OFALLENIS2080
THE GROSS SALARY OFWARDIS1625
THE GROSS SALARY OFJONESIS3867.5
THE GROSS SALARY OFMARTINIS1625
THE GROSS SALARY OFBLAKEIS3705
THE GROSS SALARY OFCLARKIS3185
THE GROSS SALARY OFSCOTTIS3900
THE GROSS SALARY OFKINGIS6500
THE GROSS SALARY OFTURNERIS1950
THE GROSS SALARY OFADAMSIS1430
THE GROSS SALARY OFJAMESIS1235
THE GROSS SALARY OFFORDIS3900
THE GROSS SALARY OFMILLERIS1690
DUPLICATE
ROWS:
********************************
SELECT DISTINCT JOB FROM EMP;
JOB
CLERK
SALESMAN
PRESIDENT
MANAGER
ANALYST
SELECT DISTINCT JOB,DEPTNO FROM EMP;
JOB DEPTNO
MANAGER 20
PRESIDENT 10
CLERK 10
SALESMAN 30
ANALYST 20
MANAGER 30
MANAGER 10
CLERK 30
CLERK 20
SELECT DISTINCT DEPTNO,JOB FROM EMP;
DEPTNO JOB
20 CLERK
30 SALESMAN
20 MANAGER
30 CLERK
10 PRESIDENT
30 MANAGER
10 CLERK
10 MANAGER
20 ANALYST
AND OPERATOR:
********************
SELECT ENAME,SAL,JOB FROM EMP WHERE(SAL>=1500 AND
SAL<=5000)AND JOB='MANAGER';
ENAME SAL JOB
JONES 2975 MANAGER
BLAKE 2850 MANAGER
CLARK 2450 MANAGER
OR OPERATOR:
*********************
SELECT EMPNO, ENAME, SAL,DEPTNO FROM EMP
WHERE SAL>=2000 OR DEPTNO=20;
EMPNO ENAME SAL DEPTNO
7369 SMITH 800 20
7566 JONES 2975 20
7698 BLAKE 2850 30
7782 CLARK 2450 10
7788 SCOTT 3000 20
7839 KING 5000 10
7876 ADAMS 1100 20
7902 FORD 3000 20
SELECT EMPNO,ENAME,JOB,HIREDATE,DEPTNO FROM
EMP WHERE JOB='MANAGER' OR DEPTNO=30;
EMPNO ENAME JOB
HIREDATE DEPTNO
7499 ALLEN SALESMAN 2/20/1981 30
7521 WARD SALESMAN 2/22/1981 30
7566 JONES MANAGER
4/2/1981 20
7654 MARTIN SALESMAN 9/28/1981 30
7698 BLAKE MANAGER
5/1/1981 30
7782 CLARK MANAGER
6/9/1981 10
7844 TURNER SALESMAN 9/8/1981 30
7900 JAMES CLERK
12/3/1981 30
SELECT EMPNO,ENAME,JOB,DEPTNO FROM EMP
WHERE(DEPTNO=10 OR DEPTNO=20) OR JOB='MANAGER';
EMPNO ENAME JOB
DEPTNO
7369 SMITH CLERK
20
7566 JONES MANAGER
20
7698 BLAKE MANAGER
30
7782 CLARK MANAGER
10
7788 SCOTT ANALYST
20
7839 KING PRESIDENT 10
7876 ADAMS CLERK
20
7902 FORD ANALYST 20
7934 MILLER CLERK
10
SELECT ENAME,JOB,HIREDATE,DEPTNO FROM EMP
WHERE(JOB='CLERK'OR JOB='SALESMAN' OR JOB='ANALYST');
ENAME JOB
HIREDATE DEPTNO
SMITH CLERK
12/17/1980 20
ALLEN SALESMAN 2/20/1981 30
WARD SALESMAN 2/22/1981 30
MARTIN SALESMAN 9/28/1981 30
SCOTT ANALYST 12/9/1982 20
TURNER SALESMAN 9/8/1981 30
ADAMS CLERK
1/12/1983 20
JAMES CLERK
12/3/1981 30
FORD ANALYST 12/3/1981 20
MILLER CLERK
1/23/1982 10
COMBINATION OF AND,OR OPERATOR:
*******************************
SELECT ENAME,SAL,JOB FROM EMP
WHERE(JOB='CLERK'OR JOB='PRESIDENT'OR JOB='ANALYST') AND SAL>3000;
KING 5000 PRESIDENT
SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE
(SAL>1500 OR JOB='MANAGER')AND DEPTNO=10;
EMPNO ENAME SAL JOB
7782 CLARK 2450 MANAGER
7839 KING 5000 PRESIDENT
SELECT EMPNO,ENAME,SAL,JOB,DEPTNO FROM EMP
WHERE(DEPTNO=20 0R JOB='MANAGER') AND SAL<3000;--ERROR
NOT OPERATOR:
********************************
SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE
NOT ENAME='SMITH';
EMPNO ENAME SAL JOB
7499 ALLEN 1600 SALESMAN
7521 WARD 1250 SALESMAN
7566 JONES 2975 MANAGER
7654 MARTIN 1250 SALESMAN
7698 BLAKE 2850 MANAGER
7782 CLARK 2450 MANAGER
7788 SCOTT 3000 ANALYST
7839 KING 5000 PRESIDENT
7844 TURNER 1500 SALESMAN
7876 ADAMS 1100 CLERK
7900 JAMES 950 CLERK
7902 FORD 3000 ANALYST
7934 MILLER 1300 CLERK
SELECT EMPNO,ENAME,SAL,JOB FROM EMP WHERE
NOT SAL>=3000;
EMPNO ENAME SAL JOB
7369 SMITH 800 CLERK
7499 ALLEN 1600 SALESMAN
7521 WARD 1250 SALESMAN
7566 JONES 2975 MANAGER
7654 MARTIN 1250 SALESMAN
7698 BLAKE 2850 MANAGER
7782 CLARK 2450 MANAGER
7844 TURNER 1500 SALESMAN
7876 ADAMS 1100 CLERK
7900 JAMES 950 CLERK
7934 MILLER 1300 CLERK
SELECT ENAME,EMPNO,JOB FROM EMP WHERE NOT
JOB='CLERK';
ENAME EMPNO JOB
ALLEN 7499 SALESMAN
WARD 7521 SALESMAN
JONES 7566 MANAGER
MARTIN 7654 SALESMAN
BLAKE 7698 MANAGER
CLARK 7782 MANAGER
SCOTT 7788 ANALYST
KING 7839 PRESIDENT
TURNER 7844 SALESMAN
FORD 7902 ANALYST
SELECT EMPNO,ENAME,JOB,HIREDATE FROM EMP
WHERE NOT HIREDATE='17-DEC-80';
EMPNO ENAME JOB
HIREDATE
7499 ALLEN SALESMAN 2/20/1981
7521 WARD SALESMAN 2/22/1981
7566 JONES MANAGER 4/2/1981
7654 MARTIN SALESMAN 9/28/1981
7698 BLAKE MANAGER 5/1/1981
7782 CLARK MANAGER 6/9/1981
7788 SCOTT ANALYST 12/9/1982
7839 KING PRESIDENT 11/17/1981
7844 TURNER SALESMAN 9/8/1981
7876 ADAMS CLERK
1/12/1983
7900 JAMES CLERK
12/3/1981
7902 FORD ANALYST 12/3/1981
7934 MILLER CLERK 1/23/1982
SELECT EMPNO,ENAME,JOB,HIREDATE FROM EMP
WHERE NOT JOB='CLERK'AND DEPTNO=20;
EMPNO ENAME JOB HIREDATE
7566 JONES MANAGER 4/2/1981
7788 SCOTT ANALYST 12/9/1982
7902 FORD ANALYST 12/3/1981
BETWEEN OPERATOR:
**********************************
SELECT EMPNO,ENAME,COMM FROM EMP
WHERE COMM BETWEEN 5000 AND 1000;--IT IS ERROR
THE BETWEEN OPERATOR IS USED TO 0 DISPLAY ROWS ON A RANGE OF VALUES
--SO THE LOWER LIMIT SHOULD BE
DECLARED FIRST.
SELECT EMPNO,ENAME,COMM FROM EMP WHERE COMM
BETWEEN 1000 AND 5000;
7654 MARTIN
1400
SELECT EMPNO,ENAME,COMM FROM EMP WHERE COMM
NOT BETWEEN 1000 AND 5000;
EMPNO ENAME COMM
7499 ALLEN 300
7521 WARD 500
7844 TURNER 0
SELECT
ENAME,SAL,JOB FROM EMP WHERE JOB NOT BETWEEN 'MANAGER' AND 'SALSMAN';
ENAME SAL JOB
SMITH 800 CLERK
SCOTT 3000 ANALYST
ADAMS 1100 CLERK
JAMES 950 CLERK
FORD 3000 ANALYST
MILLER 1300 CLERK
SELECT ENAME,SAL,JOB,HIREDATE FROM EMP
WHERE HIREDATE NOT BETWEEN '17-FEB-1981' AND '20-JUN-1983';
SMITH 800 CLERK 12/17/1980
No comments:
Post a Comment