Oracle SQL tutorial

SEQUENCES

2011-10-18T12:08:00.000-07:00

SEQUENCES

You create a sequence to generate a unique numbers.

Create sequences

You can create a sequence by using CREATE SEQUENCE statement

For example

Create sequence for the EMPNO column of the NEWEMP table begin the sequence at 100,and maximum value at 1000.

Type

CREATE SEQUENCE NEWEMP_EMPNO

INCREMENT BY 1

START WITH 100

MAXVALUE 1000;

The INCREMENT BY option specifies the gap between sequence numbers

START WITH option specifies the first number in the sequence.

MAXVALUE option specifies the maximum value the sequence can generate.

Other option not included in example

NOMAXVALUE: a default maximum value

NOMAINVALUE: specific the minimum sequence value.

CYCLE: cycle option specifies that the sequence continues to generate values.

CACHE: specifies the number of values the server reallocates in memory

Transaction control

2011-10-17T08:55:00.000-07:00

To complete your DML statements you need to control in it by

1- CMMIT: make all pending changes in a transaction permanent.

2- ROLLBACK: discard pending changes and return to an earlier point in the statement.

3- SAVEPOINT: defines a marker to which you can later rollback.

Examples.
1- Commit

Suppose you inset new row in NEWEMP table and you want to save the latest changes

Type

Commit;
2- ROLLBACK
Suppose you inset new row in NEWEMP table and you want Returns the latest commit
Type
ROLLBACK;
3- SAVEPOINT
As the below example after inserting data you can create a named save point

Insert into newemp

Select empno,ename,deptno

From emp
Where deptno=10;

SAVEPOINT inserting_finish;

You can returned to the latest savepoint named inserting_finish

Type

ROLLBACK INSERTING_FINISH

DML

2011-10-16T06:02:00.000-07:00

Manipulating Data Language (DML)

Before execute DML statements, you must have INSERT,UPDATE, and DELETE privilege.

INSERT EXAMPLES

1- INSERT INTO NEWEMP(EMPNO,ENAME,DEPTNO,SAL)

VALUES (1800,’ASD’, 10, 900);

2- INSERT INTO NEWEMP(EMPNO,ENAME,DEPTNO,SAL)

VALUE (1600,NULL,10,100);

Be sure that before inserting null value in column check if it accepting null value or not.

----

You can insert a special values like user name (must be as character data type) and SYSDATE

For example:

Assume you want insert SYSDATE as using TO_DATE function to change a format

Type

Insert into EMP (ename, hiredate)

Values (‘MEGA’, TO_DATE (’01-JAN-2011 10:00’,’DD-MON-RR HH24: MI’), 20);

------------------

Copy data from another table using SUPUERY

You can insert data from another table using subquery

For example

Assume that you want copy data from dept =10 in emp table to a newemp table

Type

Insert into newemp

Select empno,ename,deptno

From emp

Where deptno=10;

------------

UPDATE

Also you can modify existing rows by using UPDATE statement .

For example

Assume that you want to change SMITH job and his salary

Type

Update EMP

Set job=’PROGRAMMER’, SAL=2500

WHERE ENAME=’SMITH’;

-----------------------------

DELETE

You can delete all rows in the table or delete one row or more by specifying a condition in the where clause rows.

For example

Assume that we have TRAINING table including data for students

If you want delete all rows in table

Type

Delete from TRAINING;

If you want delete specified row assume for trainer id =1700

Type

Delete from TRAINING

Where TI=1700;

NOTE: that if you want deletes row content a primary key referenced as a foreign key in another table the server not accepted.

Create and Alter Tables

2011-10-14T08:46:00.000-07:00

Before create a tables please follow the below rules not for table only but for all database objects.

1- All names begin with letters and contain no more than 30 characters.

2- Names contain only the characters A-Z,a-z,0-9,underscore (_),$,#.

3- You can’t duplicate the name of another object in the same schema.

4- Name can’t an oracle reserved word.

To create table a user must have the create table privilege.

Steps to create table

1- Define a table name using CREATE TABLE statement

2- Define a column names, datatype, and length.

If you want define multiple columns, separate each column with a comma.

3- Define constraints for the respective columns.

For example.

CREATE TABLE TRAINING

(T_NO NUMBER(4) CONSTRAINT TRAINING_T_NO_PK PRIMARY_KEY,T_NAME VARACHAR2(25) CONSTRAINT TRAINING_T_NAME _NN NOT_NULL);

Result

TABLE CREATED

---------------------------------------------

Create table based on another

You can create table based on another table using AS subquery clause within the create table statement.

While create a table based on another table the second table maybe contain data this useful when creating a table to replace another similar table.

If you want to create table based on another without data you need to use a condition in the where keywords. This condition must Non-matching any data in second table

Like where 1=0;

For example

Assume you want to select rows from EMP table and insert them in the new table

Type

Create table NEWEMP as select

EMPNO,ENAME,DEPTNO,SAL

From EMP;

Result

TABLE CREATED

If you want display data in a new table

Type

SELECT * FROM NEWEMP;

EMPNO ENAME DEPTNO SAL

------- ---------- ---------- ----------

7369 SMITH 20 800

7499 ALLEN 30 1600

7521 WARD 30 1250

7566 JONES 20 2975

7654 MARTIN 30 1250

7698 BLAKE 30 2850

7782 CLARK 10 2450

7788 SCOTT 20 3000

7839 KING 10 5000

7844 TURNER 30 1500

7876 ADAMS 20 1100

EMPNO ENAME DEPTNO SAL

------- ---------- ---------- ----------

7900 JAMES 30 950

7902 FORD 20 3000

7934 MILLER 10 1300

Assume that you want create structure only without data

Type

CREATE TABLE NEWEMP2 AS

SELECT * FROM EMP

WHERE 1=0;

Since 1 not equal 0 then the new table created without data

If you want show the result

Type

DESCRIBE NEWEMP2;

Name Null? Type

----------------------------------------- -------- -------------

EMPNO NOT NULL NUMBER (4)

ENAME VARCHAR2 (10)

JOB VARCHAR2 (9)

MGR NUMBER (4)

HIREDATE DATE

SAL NUMBER (7,2)

COMM NUMBER (7,2)

DEPTNO NUMBER (2)
--------------------------------------------------------------------------------------------------

Alter tables

Some time you need to add column, modify column, redfine labels

Add column

It used to add column to an existing table

Suppose that you want add a new column for NEWEMP table this column named ABC

Alter Table NEWEMP

Add(ABC varchar2(20));

----------------

Dropping column

If you want drop colum

Type

Alter table NEWEMP

Drop ABC;

You can also set column as unused by

Typing

Alter table NEWEMP

SET UNUSED ABC;

YOU CAN DROP ALL UNUSED COLUMN

ALTER TABLE NEWEMP

DROUP UNUSED COLUMN;

-----------------------------------------------------------------

MODIFYING COLUMN

to modify the column you need to used alter table statement and the MODIFY clause.

Also you can modify data type, default value, size, add or remove NOT NULL constraint.

FOR EXAMPLE

ALTER TABLE NEWEMP

MODIFY (ABC VARCHAR2 (50));

------------------------------------------------------------------------------------

TABLE DROPPING

You can drop table using DROP TABLE statement. While drop a table index for these table will be dropped

You can use THE CASCED CONSTRAINT option if there are any referential constraint .

For Example

DROP TABLE NEWEMP

CASCADE CONSTRAINTS;

-------------------

RENAMING TABLES

You can rename a table by using RENAME statement

Syntax

RENAME old_name

To new_name

For example

RENAME newemp

To training;

-------------------------------------------------------------------

TRUNCATE A TABLE

Means that you can remove all the rows immediately without generating any rollback information

Before using the TRUNCATE TABLE statement, you should disable any foreign key constraint.

For example

If you want remove rows from NEWEMP table

Type

Truncate table NEWEMP;

---------------------------------------------------

COMMENT ON TABLE

You can add or remove a comment on the table as a massage

For example

Type

Comment on table TRAINING

Is ‘content information about trainees people ’

To remove a comment

Type

Comment on table TRAINING

Is ‘’

Constraints

2011-10-13T11:28:00.000-07:00

Oracle uses constraints to prevent invalid data entry into a table.

You can add and remove constraint after creating a table.

Constraints ensure that data in a table adhere to the recognized business rules.

Referential integrity

In the foreign key establish a relationship between the data in the foreign key

column in the child table with data in the primary key column in the parent table.

Constraint types.

1- NOT NULL: ensures the column always contains a value.

2- UNIQUE: ensures that a column contains unique values

3- PRIMARY KEY: column serves as the unique identifier for the table and is similar to a unique key, except that it cannot contain a NULL value. Table can have only one primary key.

4- FOREIGN KEY: it establishes a relationship between the child column and parent column.

5- CHECK: defines a condition that each row in a table must satisfy.

COLUMN –LEVEL CONSTRAINT

It references a single column and is defined within the specifications of the column that it constrains.

Not null constrain is must be defined at the column level.

TABLE - LEVEL CONSTRAINT

Defined after the definitions of the columns and references one or more columns. All constraint can be defined to a table level exclude NOT NULL constrain defined at a column level

.you can disable a constraint without removing it.

When you create constraint is important to name the constraint

at the time you create it otherwise the server assign it a name.

Add Constraints

By using alter table with ADD CONSTRAINT clause you can add constraint to an offered table.

Not : before add constraint view

For example

ALTER TABLE NEWEMP

ADD CONSTRAINT newemp_abc_pk

PRIMARY KEY (ABC);

----------------------------------------------------------------

DROP CONSTRAINT

By using ALTER TABLE statement you can drop constraint

For example

ALTER TABLE NEWEMP

DROP CONSTRAINT newemp_abc_pk;

If you want drop primary key you don’t need to determine constraint name you can write PRIMARY KEY only.

CASCADE

The optional cascade disables all dependent integrity constraints. that option applies to PRIMARY KEY constraints.

It causes the dependent FOREIGN.

For example

ALTER TABLE DEPT

Disable CONSTRAINT DEPTNO

Cascade;

If you enable the primary key, the foreign key is not automatic r-enabled you must re-enable the foreign key in a separate alter table statement.

--------------

HOW TO ENABLE CONSTRAINT

You can enable constraint after disabled it by ALTER TABLE statement.

For example

ALTER TABLE DEPT

ENABLE CONSTRAINT DEPTNO;

Common Database structure

2011-10-12T11:11:00.000-07:00

Common Database structure

1- Table: Common structure in database is a table .table consist of columns and rows in which data is stored .the intersection of a column and a row is called a field.

2- View: a view is a logical symbol of subsets of data from one or more database table.

3- Sequence: generate unique numbers. You can it to generate Primary key

4- Synonym: alternate names for tables or for other database object

5- Index: by using the index object you improve the performance of queries.

Note: that you can create and modify table structures while database in used.

Common Date Functions

2011-10-03T12:44:00.000-07:00

DATE FUNCTION IN SQL

1- MONTHS_BETWEEN Function : finds the number of months between two dates

For example assume that you want find number of months between an employee hire date

And sysdate

Type

Select empno,ename,sal,hiredate,MONTHS_BETWEEN(’03-OCT-11’,HIREDATE)

FROM EMP;

EMPNO ENAME SAL HIREDATE MONTHS_BETWEEN('03-OCT-11',HIREDATE)

--------- ---------- ---------- --------- ------------------------------------

7369 SMITH 800 17-DEC-80 369.548387

7499 ALLEN 1600 20-FEB-81 367.451613

7521 WARD 1250 22-FEB-81 367.387097

7566 JONES 2975 02-APR-81 366.032258

7654 MARTIN 1250 28-SEP-81 360.193548

7698 BLAKE 2850 01-MAY-81 365.064516

7782 CLARK 2450 09-JUN-81 363.806452

7788 SCOTT 3000 19-APR-87 293.483871

7839 KING 5000 17-NOV-81 358.548387

7844 TURNER 1500 08-SEP-81 360.83871

7876 ADAMS 1100 23-MAY-87 292.354839

EMPNO ENAME SAL HIREDATE MONTHS_BETWEEN('03-OCT-11',HIREDATE)

--------- ---------- ---------- --------- ------------------------------------

7900 JAMES 950 03-DEC-81 358

7902 FORD 3000 03-DEC-81 358

7934 MILLER 1300 23-JAN-82 356.354839

1- A DD_MONTHS function :by using these function you can adds a number of calendar month to a specified date.

For example

Select empno,ename,sal,hiredate,ADD_MONTHS (HIREDATE,7)

FROM EMP;

EMPNO ENAME SAL HIREDATE ADD_MONTH

------ ---------- ---------- --------- ---------

7369 SMITH 800 17-DEC-80 17-JUL-81

7499 ALLEN 1600 20-FEB-81 20-SEP-81

7521 WARD 1250 22-FEB-81 22-SEP-81

7566 JONES 2975 02-APR-81 02-NOV-81

7654 MARTIN 1250 28-SEP-81 28-APR-82

7698 BLAKE 2850 01-MAY-81 01-DEC-81

7782 CLARK 2450 09-JUN-81 09-JAN-82

7788 SCOTT 3000 19-APR-87 19-NOV-87

7839 KING 5000 17-NOV-81 17-JUN-82

7844 TURNER 1500 08-SEP-81 08-APR-82

7876 ADAMS 1100 23-MAY-87 23-DEC-87

EMPNO ENAME SAL HIREDATE ADD_MONTH

------ ---------- ---------- --------- ---------

7900 JAMES 950 03-DEC-81 03-JUL-82

7902 FORD 3000 03-DEC-81 03-JUL-82

7934 MILLER 1300 23-JAN-82 23-AUG-82

2-NEXT DAY function : by using these function you can return the date of next day.

For example

Select empno, ename, NEXT_DAY (’03-OCT-2011’,’Friday’) Friday

From emp;

select next_day('03-oct-2011','friday')

from dual

NEXT_DAY(

---------

07-OCT-11

3- LAST_DAY function : you can finds the date of the last day of month

For example

select last_day('01-oct-2011')

from dual;

LAST_DAY(

---------

31-OCT-11

3- ROUND And TRUNC functionROUND returns a date rounded to the unit specified, TURANC returns a date truncated to the unit specified.

For example

SELECT ENAME,HIREDATE,

ROUND (HIREDATE,’MONTH’),

TRUNC(HIREDATE,’MONTH’)

FROM EMP;

ENAME HIREDATE ROUND(HIR TRUNC(HIR

---------- --------- --------- ---------

SMITH 17-DEC-80 01-JAN-81 01-DEC-80

ALLEN 20-FEB-81 01-MAR-81 01-FEB-81

WARD 22-FEB-81 01-MAR-81 01-FEB-81

JONES 02-APR-81 01-APR-81 01-APR-81

MARTIN 28-SEP-81 01-OCT-81 01-SEP-81

BLAKE 01-MAY-81 01-MAY-81 01-MAY-81

CLARK 09-JUN-81 01-JUN-81 01-JUN-81

SCOTT 19-APR-87 01-MAY-87 01-APR-87

KING 17-NOV-81 01-DEC-81 01-NOV-81

TURNER 08-SEP-81 01-SEP-81 01-SEP-81

ADAMS 23-MAY-87 01-JUN-87 01-MAY-87

ENAME HIREDATE ROUND(HIR TRUNC(HIR

---------- --------- --------- ---------

JAMES 03-DEC-81 01-DEC-81 01-DEC-81

FORD 03-DEC-81 01-DEC-81 01-DEC-81

MILLER 23-JAN-82 01-FEB-82 01-JAN-82

DATE Arithmetic

2011-10-03T12:31:00.000-07:00

By using arithmetic operators you can perform calculations on dates.

For example

SELECT EMPNO,ENAME,SAL,HIREDATE,HIREDATE+100 “NEW DATE”

FROM EMP;

EMPNO ENAME SAL HIREDATE NEW DATE

---------- ---------- ---------- --------- ---------

7369 SMITH 800 17-DEC-80 27-MAR-81

7499 ALLEN 1600 20-FEB-81 31-MAY-81

7521 WARD 1250 22-FEB-81 02-JUN-81

7566 JONES 2975 02-APR-81 11-JUL-81

7654 MARTIN 1250 28-SEP-81 06-JAN-82

7698 BLAKE 2850 01-MAY-81 09-AUG-81

7782 CLARK 2450 09-JUN-81 17-SEP-81

7788 SCOTT 3000 19-APR-87 28-JUL-87

7839 KING 5000 17-NOV-81 25-FEB-82

7844 TURNER 1500 08-SEP-81 17-DEC-81

7876 ADAMS 1100 23-MAY-87 31-AUG-87

EMPNO ENAME SAL HIREDATE NEW DATE

---------- ---------- ---------- --------- ---------

7900 JAMES 950 03-DEC-81 13-MAR-82

7902 FORD 3000 03-DEC-81 13-MAR-82

7934 MILLER 1300 23-JAN-82 03-MAY-82

Example2: Assume that you want numbers of weeks for each employee are worked for the company

SELECT EMPNO,ENAME,HIREDATE,(SYSDATE-HIREDATE)/7 WEEKS_AMMOUNT

FROM EMP;

EMPNO ENAME HIREDATE WEEKS_AMMOUNT

------- ---------- --------- -------------

7369 SMITH 17-DEC-80 1606.80078

7499 ALLEN 20-FEB-81 1597.51507

7521 WARD 22-FEB-81 1597.22935

7566 JONES 02-APR-81 1591.65792

7654 MARTIN 28-SEP-81 1566.08649

7698 BLAKE 01-MAY-81 1587.51507

7782 CLARK 09-JUN-81 1581.94364

7788 SCOTT 19-APR-87 1276.22935

7839 KING 17-NOV-81 1558.94364

7844 TURNER 08-SEP-81 1568.94364

7876 ADAMS 23-MAY-87 1271.37221

EMPNO ENAME HIREDATE WEEKS_AMMOUNT

------- ---------- --------- -------------

7900 JAMES 03-DEC-81 1556.65792

7902 FORD 03-DEC-81 1556.65792

7934 MILLER 23-JAN-82 1549.37221

SYSDATE Function

2011-10-03T12:27:00.000-07:00

Is a date function returns the current date

The default format is DD-MON-YY

If you want to select current date

Type

Select sysdate

From dual;

SYSDATE

---------

03-OCT-11

-----------------------------------------------------------

Dual is a dummy table. The dual table consists one column name DUMMY and one row containing the value X.

Case conversion functions

2011-10-02T12:34:00.000-07:00

Case conversion function used to display character values in a different case,without changing the case of the data in the table
Types of case conversion function
1- UPPER: that display all characters in lowercase
2- LOWER: displays all character in lowercase.
3- INITCAP: converts the first letter or each word to uppercase and the remaining litters to lowercase.
Syntax of Case Conversion Function
First you type the keyword then the character data that you want to convert, enclosed in parentheses.
Examples
SELECT LOWER(ENAME) EMPLOYEE
FROM EMP;

EMPLOYEE

----------

smith

allen

ward

jones

martin

blake

clark

scott

king

turner

adams

EMPLOYEE

----------

james

ford

miller

SELECT UPPER(ENAME) EMPLOYEE

FROM EMP;

EMPLOYEE

----------

SMITH

ALLEN

WARD

JONES

MARTIN

BLAKE

CLARK

SCOTT

KING

TURNER

ADAMS

EMPLOYEE

----------

JAMES

FORD

MILLER

SELECT INITCAP(ENAME) EMPLOYEE

FROM EMP;

EMPLOYEE

----------

Smith

Allen

Ward

Jones

Martin

Blake

Clark

Scott

King

Turner

Adams

EMPLOYEE

----------

James

Ford

Miller

Case conversion functions used in the WHERE clause

FOR EXAMPLE CHECK THE DIFFERNET IN BELOW

SELECT ENAME

FROM EMP

Where ename=’adams’

no rows selected

SELECT ENAME

FROM EMP

Where lower(ename)=’adams’

ENAME

----------

ADAMS

SQL Functions

2011-10-02T12:25:00.000-07:00

Single row function accepts one or more arguments and returns one value for each row.

A single row functions can be used in SELECT, WHERE, and ORDER BY

Also single functions can be nested

Types of single row functions

1- Number functions: accept numeric input and return numeric values.

2- Character functions: accept character input

3- Date functions :accept date values

4- Data type conversion: convert a value from one data type to another.

5- General functions: improve the power of your SQL statement. this category includes an important function to replace with an actual value If a column is NULL, and another powerful function to run queries based on conditional, IF –THEN-ELSE logic.
---------------------------------------------------------------------------------------------------------------------
Single row functions

S Single row function accepts one or more arguments and returns one value for each row.

A single row functions can be used in SELECT, WHERE, and ORDER BY

Al so single functions can be nested

T types of single row functions

1- 1-Number functions: accept numeric input and return numeric values.

2- 2-Character functions: accept character input

3- 3-Date functions :accept date values

4- 4-Data type conversion: convert a value from one data type to another.

5- 5-General functions: improve the power of your SQL statement. this category includes an important function to replace with an actual value If a column is NULL, and another powerful function to run queries based on conditional, IF –THEN-ELSE logic

----------------------------------------------------------------------------------------------------------------

TO_CHAR string

TO_CHAR function converts a number or date to AVARCHAR2 in a format that you specify.

TO_CHAR with date

You can use the TO_CHAR function to convert a date from the default data format to a format that you identify.

The default format is DD-MON-YY .if you need to change the default format then you can use the TO_CHAR function to convert a date into a new format.

For example

Select empno,ename,to_char(hiredate,’dd/mm/yyyy’) “JOIN DATE”

FROM EMP;

EMPNO ENAME JOIN DATE

---------- ---------- ----------

7369 SMITH 17/12/1980

7499 ALLEN 20/02/1981

7521 WARD 22/02/1981

7566 JONES 02/04/1981

7654 MARTIN 28/09/1981

7698 BLAKE 01/05/1981

7782 CLARK 09/06/1981

7788 SCOTT 19/04/1987

7839 KING 17/11/1981

7844 TURNER 08/09/1981

7876 ADAMS 23/05/1987

EMPNO ENAME JOIN DATE

---------- ---------- ----------

7900 JAMES 03/12/1981

7902 FORD 03/12/1981

7934 MILLER 23/01/1982

Select empno,ename,to_char(hiredate,’DD MONTH YYYY’) “JOIN DATE”

FROM EMP;

EMPNO ENAME JOIN DATE

-------- ---------- --------------------

7369 SMITH 17 DECEMBER 1980

7499 ALLEN 20 FEBRUARY 1981

7521 WARD 22 FEBRUARY 1981

7566 JONES 02 APRIL 1981

7654 MARTIN 28 SEPTEMBER 1981

7698 BLAKE 01 MAY 1981

7782 CLARK 09 JUNE 1981

7788 SCOTT 19 APRIL 1987

7839 KING 17 NOVEMBER 1981

7844 TURNER 08 SEPTEMBER 1981

7876 ADAMS 23 MAY 1987

EMPNO ENAME JOIN DATE

-------- ---------- --------------------

7900 JAMES 03 DECEMBER 1981

7902 FORD 03 DECEMBER 1981

7934 MILLER 23 JANUARY 1982

ALSO THE SEE BELOW EXAMPLE

Select empno,ename,to_char(hiredate,’FMDAY , DD MONTH YYYY’) “JOIN DATE”

FROM EMP;

EMPNO ENAME JOIN DATE

------- ---------- ------------------------------

7369 SMITH WEDNESDAY , 17 DECEMBER 1980

7499 ALLEN FRIDAY , 20 FEBRUARY 1981

7521 WARD SUNDAY , 22 FEBRUARY 1981

7566 JONES THURSDAY , 2 APRIL 1981

7654 MARTIN MONDAY , 28 SEPTEMBER 1981

7698 BLAKE FRIDAY , 1 MAY 1981

7782 CLARK TUESDAY , 9 JUNE 1981

7788 SCOTT SUNDAY , 19 APRIL 1987

7839 KING TUESDAY , 17 NOVEMBER 1981

7844 TURNER TUESDAY , 8 SEPTEMBER 1981

7876 ADAMS SATURDAY , 23 MAY 1987

EMPNO ENAME JOIN DATE

------- ---------- ------------------------------

7900 JAMES THURSDAY , 3 DECEMBER 1981

7902 FORD THURSDAY , 3 DECEMBER 1981

7934 MILLER SATURDAY , 23 JANUARY 1982

TO_CHAR with number

You can use the TO_CHAR function to convert other datatype values to characters.

For example

To display employee salaries with $ (dollar sign)

Type

Select empno,ename,TO_CHAR(sal,’$99,999’)

From emp

Where empno=7499

EMPNO ENAME TO_CHAR(

------- ---------- --------

7499 ALLEN $1,600

------------------------------------------------------------------------------------------------------------------

TO_NUMBER

To convert a character string to a number ,you use the TO_NUMBER function

For example

Select empno,ename,sal

From emp

Where sal > TO_NUMBER (‘$1,600’,’$9,999’);

EMPNO ENAME SAL

------ ---------- ----------

7566 JONES 2975

7698 BLAKE 2850

7782 CLARK 2450

7788 SCOTT 3000

7839 KING 5000

7902 FORD 3000

-----------------------------------------------------------------------------------------------------------------

TO_DATE function

SQL DATE FUNCTION

Used to convert a character string to a date format.

For example

Assume that you have a character string on behalf of a date value. With format February 20, 1981. If you want compare this character string to a date value .you will receive a error

Then you must used TO_DATE function to convert the character string into a date value.

TYPE

SELECT EMPNO,ENAME,HIREDATE

FROM EMP

WHERE HIREDATE = TO_DATE(‘February 20, 1981’,’MONTH DD,YYYY’);

EMPNO ENAME HIREDATE

------ ---------- ---------

7499 ALLEN 20-FEB-81

See below links

SYSDATE Function
DATE Arithmetic
Common Date Functions
-----------------------------------------------------------------------------------------------
LPAD Function

L By using the LPAD function you can place a character or characters on the left of a character value.

In the LPAD function syntax, you specify the name of the column that you want to pad

For example

If you want pad employee names with percentage ‘%’

type

select ename,LPAD(ename,8,’%’)

from emp;

ENAME LPAD(ENA

---------- --------

SMITH %%%SMITH

ALLEN %%%ALLEN

WARD %%%%WARD

JONES %%%JONES

MARTIN %%MARTIN

BLAKE %%%BLAKE

CLARK %%%CLARK

SCOTT %%%SCOTT

KING %%%%KING

TURNER %%TURNER

ADAMS %%%ADAMS

ENAME LPAD(ENA

---------- --------

JAMES %%%JAMES

FORD %%%%FORD

MILLER %%MILLER
------------------------------------------------------------------------------------------------------------------
RPAD function

R RPAD function it use a same Logic for LPAD the different is RPAD function pads the character value on the right

select ename,RPAD(ename,8,’%’)

from emp;

ENAME RPAD(ENA

---------- --------

SMITH SMITH%%%

ALLEN ALLEN%%%

WARD WARD%%%%

JONES JONES%%%

MARTIN MARTIN%%

BLAKE BLAKE%%%

CLARK CLARK%%%

SCOTT SCOTT%%%

KING KING%%%%

TURNER TURNER%%

ADAMS ADAMS%%%

ENAME RPAD(ENA

---------- --------

JAMES JAMES%%%

FORD FORD%%%%

MILLER MILLER%%

-----------------------------------------------------------------------------------------------------------------
Length Function

You used length function to know the number of characters in a string (as a numeric value).

For example

To show the number of character for each job

SQL> select job,length(job)

from emp;

JOB LENGTH(JOB)

--------- -----------

CLERK 5

SALESMAN 8

MANAGER 7

SALESMAN 8

MANAGER 7

ANALYST 7

PRESIDENT 9

SALESMAN 8

CLERK 5

-----------------------------------------------------------------------------------------------------------------

SUBSTR and INSTR functions

That function used to display information about a value

SUBSTR FUNCTION

You can take out a string of a determined length using the SUBSTR function.

Using the SUBSTR function, you specify a character string followed by two numbers. The first number represents the character position from which to begin extracting, and the second number represents the number of characters that you want to extract.

For example

Assume that you want cut the first two letters of an employee’s last name

Select ename,SUBSTER(ENAME,1,2)

FROM EMP;

ENAME SU

---------- --

SMITH SM

ALLEN AL

WARD WA

JONES JO

MARTIN MA

BLAKE BL

CLARK CL

SCOTT SC

KING KI

TURNER TU

ADAMS AD

ENAME SU

---------- --

JAMES JA

FORD FO

MILLER MI

INSTR function

By using INSTR function you can find the numeric position of a named character

Also you specify a character string followed by the character for which you want the numeric position

The function returns a numeric value instead of the placement of the first happening of the individual letter in the string.

For example

Assume that you want know the position of the first S in each employee name.

Then type

SELECT ENAME,INSTR(ENAME,’S’)

FROM EMP;

ENAME INSTR(ENAME,'S')

---------- ----------------

SMITH 1

ALLEN 0

WARD 0

JONES 5

MARTIN 0

BLAKE 0

CLARK 0

SCOTT 1

KING 0

TURNER 0

ADAMS 5

ENAME INSTR(ENAME,'S')

---------- ----------------

JAMES 5

FORD 0

MILLER 0

------------------------------------------------------------------------------------------------------------------
ROUND function

R By using the round function you can round a number to a specified number of decimal

The ROUND function .Accept numeric input and returns numeric values.

If you don’t specify a number of decimal places, or if you specify a zero for the number of decimal places, then the values is rounded to the left of the decimal

For example

If you want to calculate an employee’s daily salary.

Type

SELECT ENAME,ROUND (SAL*12/365,2)

FROM EMP;

ENAME ROUND(SAL*12/365,2)

---------- -------------------

SMITH 26.3

ALLEN 52.6

WARD 41.1

JONES 97.81

MARTIN 41.1

BLAKE 93.7

CLARK 80.55

SCOTT 98.63

KING 164.38

TURNER 49.32

ADAMS 36.16

ENAME ROUND(SAL*12/365,2)

---------- -------------------

JAMES 31.23

FORD 98.63

MILLER 42.74

Note : that you can round the result to the nearest integer by not specifying a number of decimal places in the ROUND function.

-----------------------------------------------------------------------------------------------------------------
TRUNCATE function

SQL Truncate

The SQL TRUNCATE function .Accept numeric input and returns numeric values.

You specify the TRUNC function keyword followed by the column or expression that you want to truncate and the number of decimal places to which you want to truncate in the result.

If you don’t specify a number of decimal places, or if you specify a zero for the number of decimal places, then tha value is truncated to the integer. If you specify a negative number of decimal places, then values to the left of decimal place are truncated to zero.

For example

Assume that you want to calculate an employee daialy salary as sal*12/365 and truncate the result to two decimal places.

Type

SELECT ENAME,TRUNC (SAL*12/365,2)

FROM EMP;

ENAME TRUNC(SAL*12/365,2)

---------- -------------------

SMITH 26.3

ALLEN 52.6

WARD 41.09

JONES 97.8

MARTIN 41.09

BLAKE 93.69

CLARK 80.54

SCOTT 98.63

KING 164.38

TURNER 49.31

ADAMS 36.16

ENAME TRUNC(SAL*12/365,2)

---------- -------------------

JAMES 31.23

FORD 98.63

MILLER 42.73

The result produces the rest of the first value divided by the second value.

For example assume that you want calculate the rest of salary divided by commission

For employee number 7654

Type

Select empno,ename,sal,comm,mod(sal,comm)

From emp

Where empno= 7654 ;

EMPNO ENAME SAL COMM MOD(SAL,COMM)

------- ---------- ---------- ---------- -------------

7654 MARTIN 1250 1400 1250

If you see the result you will find that the function returns the same salary value

Because the comm value is larger than the salary value also if comm. Equal zero the result it will be same first value(in our example sal)

Please check the below example for clarification

Select empno, ename, sal, comm, MOD (SAL, COMM)

FROM EMP

WHERE JOB =’SALESMAN’;

EMPNO ENAME SAL COMM MOD(SAL,COMM)

---------- ---------- ---------- ---------- -------------

7499 ALLEN 1600 300 100

7521 WARD 1250 500 250

7654 MARTIN 1250 1400 1250

7844 TURNER 1500 0 1500

---------------------------------------------------------------------------------------------
MOD function

The result produces the rest of the first value divided by the second value.

For example assume that you want calculate the rest of salary divided by commission

For employee number 7654

Type

Select empno,ename,sal,comm,mod(sal,comm)

From emp

Where empno= 7654 ;

EMPNO ENAME SAL COMM MOD(SAL,COMM)

------- ---------- ---------- ---------- -------------

7654 MARTIN 1250 1400 1250

----------------------------------------------------------------------------------------------------------------

Length Function

L You used length function to know the number of characters in a string (as a numeric value).

For example

To show the number of character for each job

SQL> select job,length(job)

from emp;

JOB LENGTH(JOB)

--------- -----------

CLERK 5

SALESMAN 8

MANAGER 7

SALESMAN 8

MANAGER 7

ANALYST 7

PRESIDENT 9

SALESMAN 8

CLERK 5

JOB LENGTH(JOB)

--------- -----------

CLERK 5

ANALYST 7

CLERK 5

------------------------------------------------------------------------------------------------------------------

NVL function

NVL SQL used to convert a null value to an actual value.

To convert null values in a numeric, character, or date . you must specify a return value that is a number , character ,or date

For example

If you want to calculate annual compensation for all employees

Type

SELECT EMPNO,ENAME, SAL,COMM,(SAL*12)+NVL(COMM,0) Annual

FROM EMP;

EMPNO ENAME SAL COMM ANNUAL

------ ---------- ---------- ---------- ----------

7369 SMITH 800 9600

7499 ALLEN 1600 300 19500

7521 WARD 1250 500 15500

7566 JONES 2975 35700

7654 MARTIN 1250 1400 16400

7698 BLAKE 2850 34200

7782 CLARK 2450 29400

7788 SCOTT 3000 36000

7839 KING 5000 60000

7844 TURNER 1500 0 18000

7876 ADAMS 1100 13200

------------------------------------------------------------------------------------------------------------------

DECODE function
DECODE IN SQL

It a single – row function it do the work of IF –THEN-ELSE . the DECODE function compares a column or expression to each search value.

DECODE Syntax.

DECOD (COLUMN_NAME, SEARCH1, RESULT1, SEARCH2, RESULT2)

FOR EXAMPLE

if you want calculate the salary increases by 10% for employees who working in department 20 using THE DECODE

type

SELECT EMPNO, ENAME,SAL,DEPTNO,DECODE(deptno,10,sal*1.1) “INCREMENT”

From emp;

EMPNO ENAME SAL DEPTNO INCREMENT

------ ---------- ---------- ---------- ----------

7369 SMITH 800 20

7499 ALLEN 1600 30

7521 WARD 1250 30

7566 JONES 2975 20

7654 MARTIN 1250 30

7698 BLAKE 2850 30

7782 CLARK 2450 10 2695

7788 SCOTT 3000 20

7839 KING 5000 10 5500

7844 TURNER 1500 30

7876 ADAMS 1100 20

EMPNO ENAME SAL DEPTNO INCREMENT

------ ---------- ---------- ---------- ----------

7900 JAMES 950 30

7902 FORD 3000 20

7934 MILLER 1300 10 1430

If you check the result you will find that only employees who working in department 10 get increment.

------------------------------------------------------------------------------------------------------------------

NESTING Function

It used to create complex and flexible statements that retrieve exactly the data that you need. NESTING function it functions within functions.

For example

Assume that you want display how earn commission and who’s not by ‘NO COMMISSION’

TYPE

SELECT ENAME, NVL (TO_CHAR (COMM),’No commission’) earn_comm

From emp;

ENAME EARN_COMM

---------- ---------------

SMITH No commission

ALLEN 300

WARD 500

JONES No commission

MARTIN 1400

BLAKE No commission

CLARK No commission

SCOTT No commission

KING No commission

TURNER 0

ADAMS No commission

ENAME EARN_COMM

---------- ---------------

JAMES No commission

FORD No commission

MILLER No commission

SORTING BY MULTIPLE COLUMNS

2011-10-02T12:23:00.001-07:00

You can used more than one column to sort data by identify multiple columns in the ORDER BY clause.

FOR EXAMPLE

SELECT EMPNO,ENAME,JOB,DEPTNO

FROM EMP

ORDER BY DEPTNO,JOB;

EMPNO ENAME JOB DEPTNO

-------- ---------- --------- ----------

7934 MILLER CLERK 10

7782 CLARK MANAGER 10

7839 KING PRESIDENT 10

7788 SCOTT ANALYST 20

7902 FORD ANALYST 20

7369 SMITH CLERK 20

7876 ADAMS CLERK 20

7566 JONES MANAGER 20

7900 JAMES CLERK 30

7698 BLAKE MANAGER 30

7499 ALLEN SALESMAN 30

EMPNO ENAME JOB DEPTNO

-------- ---------- --------- ----------

7654 MARTIN SALESMAN 30

7844 TURNER SALESMAN 30

7521 WARD SALESMAN 30

Sorting by column alias

2011-10-02T12:22:00.000-07:00

You can use the column alias if you want a more expressive title for data

For example

Select ename,sal,empno ,sal+100 bounce

From emp

Order by bounce;

ENAME SAL EMPNO BOUNCE

---------- ---------- ---------- ----------

SMITH 800 7369 900

JAMES 950 7900 1050

ADAMS 1100 7876 1200

WARD 1250 7521 1350

MARTIN 1250 7654 1350

MILLER 1300 7934 1400

TURNER 1500 7844 1600

ALLEN 1600 7499 1700

CLARK 2450 7782 2550

BLAKE 2850 7698 2950

JONES 2975 7566 3075

ENAME SAL EMPNO BOUNCE

---------- ---------- ---------- ----------

SCOTT 3000 7788 3100

FORD 3000 7902 3100

KING 5000 7839 5100

DESC Keyword

2011-10-02T12:16:00.000-07:00

The default sort order is ascending when using the ORDER BY clause , you can reverse the order in which rows are displayed by using the DESC keywords. It placed after column name in the ORDER BY clause
For Example
SELECT EMPNO,ENAME
FROM EMP
ORDER BY SAL DESC;
EMPNO ENAME SAL

----- ---------- ----------

7839 KING 5000

7788 SCOTT 3000

7902 FORD 3000

7566 JONES 2975

7698 BLAKE 2850

7782 CLARK 2450

7499 ALLEN 1600

7844 TURNER 1500

7934 MILLER 1300

7521 WARD 1250

7654 MARTIN 1250

EMPNO ENAME SAL

----- ---------- ----------

7876 ADAMS 1100

7900 JAMES 950

7369 SMITH 800

Select ename,sal,mgr

From emp

Order by mgr DESC;

ENAME SAL MGR

---------- ---------- ----------

KING 5000

SMITH 800 7902

JONES 2975 7839

CLARK 2450 7839

BLAKE 2850 7839

ADAMS 1100 7788

MILLER 1300 7782

ALLEN 1600 7698

MARTIN 1250 7698

WARD 1250 7698

JAMES 950 7698

ENAME SAL MGR

---------- ---------- ----------

TURNER 1500 7698

SCOTT 3000 7566

FORD 3000 7566

Order by

2011-10-02T12:07:00.000-07:00

We used order by clause to sort rows in an order that you specify.

Sorting data is ascending order by .

1- Numeric data is sorted from the lowest to the highest number.

2- Character data is sorted alphabetically from A to Z .

3- Date data is sorted from the earliest to the latest date.

4- Null values are displayed last for ascending sequences.

You can use a column alias in an order by clause to sort information.

You can sort data by expression.

Examples

Select empno,ename,sal

From emp

Order by sal;

EMPNO ENAME SAL

------ ---------- ----------

7369 SMITH 800

7900 JAMES 950

7876 ADAMS 1100

7521 WARD 1250

7654 MARTIN 1250

7934 MILLER 1300

7844 TURNER 1500

7499 ALLEN 1600

7782 CLARK 2450

7698 BLAKE 2850

7566 JONES 2975

EMPNO ENAME SAL

------ ---------- ----------

7788 SCOTT 3000

7902 FORD 3000

7839 KING 5000

Select empno,ename,sal

From emp

Order by ename;

EMPNO ENAME SAL

------- ---------- ----------

7876 ADAMS 1100

7499 ALLEN 1600

7698 BLAKE 2850

7782 CLARK 2450

7902 FORD 3000

7900 JAMES 950

7566 JONES 2975

7839 KING 5000

7654 MARTIN 1250

7934 MILLER 1300

7788 SCOTT 3000

EMPNO ENAME SAL

------- ---------- ----------

7369 SMITH 800

7844 TURNER 1500

7521 WARD 1250

Select empno,ename,sal,hiredate

From emp

Order by hiredate;

EMPNO ENAME SAL HIREDATE

------ ---------- ---------- ---------

7369 SMITH 800 17-DEC-80

7499 ALLEN 1600 20-FEB-81

7521 WARD 1250 22-FEB-81

7566 JONES 2975 02-APR-81

7698 BLAKE 2850 01-MAY-81

7782 CLARK 2450 09-JUN-81

7844 TURNER 1500 08-SEP-81

7654 MARTIN 1250 28-SEP-81

7839 KING 5000 17-NOV-81

7900 JAMES 950 03-DEC-81

7902 FORD 3000 03-DEC-81

EMPNO ENAME SAL HIREDATE

------ ---------- ---------- ---------

7934 MILLER 1300 23-JAN-82

7788 SCOTT 3000 19-APR-87

7876 ADAMS 1100 23-MAY-87

Select empno,ename,sal,hiredate,sal*12

From emp

Order by sal*12;

EMPNO ENAME SAL HIREDATE SAL*12

------- ---------- ---------- --------- ----------

7369 SMITH 800 17-DEC-80 9600

7900 JAMES 950 03-DEC-81 11400

7876 ADAMS 1100 23-MAY-87 13200

7521 WARD 1250 22-FEB-81 15000

7654 MARTIN 1250 28-SEP-81 15000

7934 MILLER 1300 23-JAN-82 15600

7844 TURNER 1500 08-SEP-81 18000

7499 ALLEN 1600 20-FEB-81 19200

7782 CLARK 2450 09-JUN-81 29400

7698 BLAKE 2850 01-MAY-81 34200

7566 JONES 2975 02-APR-81 35700

EMPNO ENAME SAL HIREDATE SAL*12

------- ---------- ---------- --------- ----------

7788 SCOTT 3000 19-APR-87 36000

7902 FORD 3000 03-DEC-81 36000

7839 KING 5000 17-NOV-81 60000

DESC
Sorting by column alias
SORTING BY MULTIPLE COLUMNS

RULES OF PRIORITY

2011-10-02T12:02:00.000-07:00

IN SQL SELECT statement the oracle server evaluates comparison operator’s begor logival operators.

In the evaluation of logical operators ,NOT is evaluated first,AND is evaluated second,and OR

Is evaluated third.

You can override rules of precedence by using parentheses.

For example

Select empno,ename,job

From emp

Where SAL =1500

AND (JOB =’SALESMAN’

OR job =’CLERK’);

EMPNO ENAME JOB

--------- ---------- ---------

7844 TURNER SALESMAN

The result lists employees who are either a CLERK or a SALESMAN and who earn more than $1500.

Logical operators

2011-10-02T11:59:00.000-07:00

Logical operators in SQL

And	Used to define conditions that must all be true for a row to be retrieved.
OR	Used to define two conditions of which only one must be true for a row to be retrieved.
NOT	You can use the NOT operator to invert the result of single condition in the where clause.

Examples.

AND OPERATOR

Suppose you want to display the employee number and name and salary of all employees

Who earn equal and more than 800$ and who are in department 20. Then you will write the next statement

Select empno,ename,deptno,sal

From emp

Where sal >=800

And deptno=20;

EMPNO ENAME DEPTNO SAL

------ ---------- ---------- ----------

7369 SMITH 20 800

7566 JONES 20 2975

7788 SCOTT 20 3000

7876 ADAMS 20 1100

7902 FORD 20 3000

OR Operator

Assume that want to retrieve information about employees who earn a salary greater than or equal to $1250 or who have the job title SALESMAN

Then you can combine these conditions in a where clause by using the or operator.

SELECT ENAME,JOB,SAL

FROM EMP

WHERE SAL >= 1250

OR JOB =’ SALESMAN’;

ENAME JOB SAL

---------- --------- ----------

ALLEN SALESMAN 1600

WARD SALESMAN 1250

JONES MANAGER 2975

MARTIN SALESMAN 1250

BLAKE MANAGER 2850

CLARK MANAGER 2450

SCOTT ANALYST 3000

KING PRESIDENT 5000

TURNER SALESMAN 1500

FORD ANALYST 3000

MILLER CLERK 1300

Notice that you can use the OR logical operator in combination with any comparison operator to specify conditions in a where clause.

NOT operator

You can use the NOT logical operator in combination with the comparison operators IN, BETWWEN, LIKE, AND IS NULL.

To retrieve a list of employees who are not salesman, president, clerk

You can use the not operator IN combination with the in operator.

SQL> SELECT EMPNO,ENAME,JOB,SAL

FROM EMP

WHERE JOB NOT IN ('SALESMAN', 'PRESIDENT', 'CLERK');

EMPNO ENAME JOB SAL

---------- ---------- --------- ----------

7566 JONES MANAGER 2975

7698 BLAKE MANAGER 2850

7782 CLARK MANAGER 2450

7788 SCOTT ANALYST 3000

7902 FORD ANALYST 3000

You place NOT directly before the IN,BETWEEN,and LIKE operators in a sql statement.

However with the IS NULL operator ,you place the not keyword in the middle to produce the operator IS NOT NULL.

EXAMPL

SELECT ENAME,SAL,JOB

FROM EMP

WHERE JOB NOT LIKE 'SALESMAN';

ENAME SAL JOB

---------- ---------- ---------

SMITH 800 CLERK

JONES 2975 MANAGER

BLAKE 2850 MANAGER

CLARK 2450 MANAGER

SCOTT 3000 ANALYST

KING 5000 PRESIDENT

ADAMS 1100 CLERK

JAMES 950 CLERK

FORD 3000 ANALYST

MILLER 1300 CLERK

EXAMPL: for NOT operator with NULL VALUES.

SELECT ENAME,MGR

FROM EMP

WHERE MGR IS NOT NULL;

ENAME MGR

---------- ----------

SMITH 7902

ALLEN 7698

WARD 7698

JONES 7839

MARTIN 7698

BLAKE 7839

CLARK 7839

SCOTT 7566

TURNER 7698

ADAMS 7788

JAMES 7698

ENAME MGR

---------- ----------

FORD 7566

MILLER 7782

13 rows selected.

Is null operator

2011-10-02T11:53:00.000-07:00

COMPARE NULL IN SQL

If you want to find out if a column contains any null values, then you can use the is NULL operator in the where clause.

A null value means that the value is unavailable. You cannot test for null values using an equal sign, because a Null value cannot be equal for unequal any value.

For example you want to retrieve the names of employees who do not have a manager . you can use the IS NULL operator to find all rows that contain a null value in the MGR column.

SQL> select ename

from emp

where mgr is null;

ENAME

----------

KING

LIKE

2011-09-29T08:44:00.000-07:00

When you don’t know what value you need to search then you can use the like operator in where cluse.

Symbols with like.

(%) represents any number of characters

(_) the underscore symbol represents a single character.

The search string is enclosed in single quotation marks.sql wildcard

Examples

Select ename,job

From emp

Where ename like ‘c%’;

Ename job

---------- ---------

Clark manager

Select ename,job

From emp

Where ename like ‘s%’;

Ename job

---------- ---------

Smith clerk

Scott analyst

Sql> select ename

2 from emp

3 where ename like '_a%';

Ename

----------

Ward

Martin

James

The result is a list of employee names .each name includes the letter a as the second letter in the name.

IN

2011-09-29T08:43:00.000-07:00

IN Operator used to restrict rows retrieved to values in specified list

The IN keyword is followed by values separated by commas.

The full list of values is enclosed within a single set of parentheses.

Also you can use IN with any data type (character, numeric, dates).

For example

Select empno,ename,sal

From emp

Where sal in (125,3000,1600);

EMPNO ENAME SAL

------ ---------- ----------

7499 ALLEN 1600

7788 SCOTT 3000

7902 FORD 3000

SELECT ENAME,SAL,JOB

FROM EMP

WHERE JOB IN (‘MANAGER’,’ANALYST’,’PRESIDENT’)

ENAME SAL JOB

---------- ---------- ---------

JONES 2975 MANAGER

BLAKE 2850 MANAGER

CLARK 2450 MANAGER

SCOTT 3000 ANALYST

KING 5000 PRESIDENT

FORD 3000 ANALYST

BETWEEN

2011-09-29T08:39:00.001-07:00

BETWEEN

SQL RANGE used to display rows based on a rang of values,you use the between and keywords .

You specify the between keyword followed by the lower limit of the range, and keyword, and the higher limit of the range.

For example

SELECT EMPNO,ENAME,SAL

FROM EMP

WHERE SAL BETWEEN 1600 AND 3000;

EMPNO ENAME SAL

--------- ---------- ----------

7499 ALLEN 1600

7566 JONES 2975

7698 BLAKE 2850

7782 CLARK 2450

7788 SCOTT 3000

7902 FORD 3000

WHEN YOU USE THE CHARACTER OR DATE VALUES WITH BETWEEN OPERATOR .YOU PLACE EACH VALUE WITHIN SINGLE QUOTATION MARKS.

FOR EXAMPLE

SELECT EMPNO,ENAME,SAL

FROM EMP

WHERE HIREDATE BETWEEN ’02-APR-81’ AND ’17-NOV-81’;

EMPNO ENAME SAL

--------- ---------- ----------

7566 JONES 2975

7654 MARTIN 1250

7698 BLAKE 2850

7782 CLARK 2450

7839 KING 5000

7844 TURNER 1500

SELECT ENAME, SAL

FROM EMP

WHERE ENAME BETWEEN ‘A’ AND ‘S’;

ENAME SAL

---------- ----------

ALLEN 1600

JONES 2975

MARTIN 1250

BLAKE 2850

CLARK 2450

KING 5000

ADAMS 1100

JAMES 950

FORD 3000

MILLER 1300

Comparing Expression

2011-09-29T08:38:00.000-07:00

Comparing in sql

=	Equal
<	Less than
>	Greater than
<=	Less than or equal
>=	Greater than or equal
<>	Not equal

By using these operators you can restrict rows returned by a query based on variety of condition.

Example 1

Assume you want to retrieve a list of employees who earn monthly salary that is more than $2850.

You will write the below

Select ename,sal

From emp

Where sal >2850;

ENAME SAL

---------- ----------

JONES 2975

SCOTT 3000

KING 5000

FORD 3000

EXAMPLES FOR COMPARING EXPRETION

EXAMPL 1

Select ename,sal

From emp

Where sal =2850;

ENAME SAL

---------- ----------

BLAKE 2850

EXAMPL2.

Select ename,sal

From emp

Where sal >=2850;

ENAME SAL

---------- ----------

JONES 2975

BLAKE 2850

SCOTT 3000

KING 5000

FORD 3000

EXAMPLES FOR COMPARING EXPRETION WITH DATES AND CHARACTER VALUES

1-DATES

SELECT ENAME,HIREDATE

FROM EMP

WHERE HIREDATE<’ 01-JUL-81’;

ENAME HIREDATE

---------- ---------

SMITH 17-DEC-80

ALLEN 20-FEB-81

WARD 22-FEB-81

JONES 02-APR-81

BLAKE 01-MAY-81

CLARK 09-JUN-81

2-CHRACTER

SELECT DEPTNO,LOC

FROM DEPT

WHERE LOC <> ‘DALLAS’;

DEPTNO LOC

-------- -------------

10 NEW YORK

30 CHICAGO

40 BOSTON

COMPARTION AND NULL VALUES COMPARE SQL EDITIONS

When you use comparison operators in a where clause ,rowes that contain null result in null ,and are effectively not part of the result a value cannot be equal to ,greater than ,less than, or not equal to Null, because null is not a value.

For example

Assume that you want a list of employees who earn a monthly salary that is greater than or equal to their annual commission. You select ename, Sal, and comm. from the EMP table and add a where clause restricting results to those with a salary greater than or equal to commission.

Check the below cod for more clarification

SELECT ENAME,SAL,COMM

FROM EMP

WHERE SAL >= COMM;

ENAME SAL COMM

---------- ---------- ----------

ALLEN 1600 300

WARD 1250 500

TURNER 1500 0

Where

2011-09-27T13:02:00.000-07:00

Where

You can restrict the rows returned from a query by using a where clause in SQL statement By inserting a WHERE clause into a SQL statement, you can specify a condition that must be met, and only the rows the condition are returned. This is also known as a selection.The where clause directly follows the FROM clause in SQL statement syntax. The WHERE clause consists of the WHERE keyword and condition or conditions.

Check the below example.

Select empno,ename,deptno

From emp

Where deptno=30;

- Content of a condition

1- a column name like depton in above example. That specifies the column that you want to restrict.

2- Comparison operator like = in above example .that specifies which rows are to be returned.

3- Constant like 30 in above example .that specifies which rows are to be restricted.

When you run the above query you will get the results for only department 30

Data type

2011-09-27T12:53:00.000-07:00

Data type SQL DATATYPES

NUMBER: INDICATE THE COLUMN CONTAINS NUMBER.

EXAMPLE: NUMBER (6, 2)

32155.01

The number in parentheses following this data type refers to the maximum of digits that the column may contain. A second number following the data type specifies the number of digits that the right of the decimal point in the number value.

VARCHAR2: INDICATE THE CLOUMN STORS A VARIABLE NUMBER OF CHARACTERS.

Example: VARCHAR2 (10)

‘ADAM’

THE NUMBER IN PARENTHESES FOLLOWING THIS DATATYPE SPECIFIES THE MAXIMUM OF CHARACTERS ALLOWED.

DATE: THE DATE DATATYPE INDICATES THAT A COLUMN CONTAINS A DATE AND TIME THE VAIID DATE RANGE IS BETWEEN JANUARY 1.14712 B.C.AND DECEMBER 31.9999 A.D.

EXAMPLE: 25-SEP-2011

CHAR: THE CHAR DATATYPE INDICATES THAT THE COLUMN STORES A FIXED-LENGTH CHARACTER VALUE . THE NUMBER IN PARENTHRSES FOLLOWING THIS DATATYPE SPECIFIES THE NUMBER OF CHARACTERS ALWAYS STORED.

EXAMPLE : CHAR(3)

EMD

KNY

AD#

DISPLAYING TABLE STRUCTURE

2011-09-26T09:59:00.000-07:00

DISPLAYING TABLE STRUCTURE.

Structure of sql.You can use the DESCRIBE command to see the column names, the data types ,And whether a column must contain data from a specific table.

Not you can write DESCRIBE command abbreviated. Like below and given same results.