oracle sql tutorials,oracle 10g tutorial
Subscribe:

Friday, January 18, 2019

Find My Code



Please follow the below :

1- Go to https://bit.ly/2sxOWyO

2-Provide the required code 

Tuesday, October 18, 2011

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

Monday, October 17, 2011

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

Sunday, October 16, 2011

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.



Friday, October 14, 2011

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 ‘’

Thursday, October 13, 2011

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;


Wednesday, October 12, 2011

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.