SEQUENCES

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

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

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

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

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

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

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