Database Normalisation

      Database normalization is the process of organising the data in a relational database to minimise data redundancy. Normalization is a systematic approach of decomposing tables to eliminate data redundancy and undesirable characteristics like Insertion, Update and Deletion Anomalies.

There are two reasons of the normalization process:

  • To eliminating redundant data, for example, storing the same data in more then one table.
  • Ensuring data dependencies make scene.

      Both of these worthy goals as they reduce the amount of space a database consumes and ensure that data is logically stored. 

      Normalization of a database is achieved by following set of rules called “forms” in creating a database.

Normalization rule are divided into following normal form.

  1. First normal form
  2. Second normal form
  3. Third normal form
  4. Boyce – codd Normal form
  5. Fourth normal form
  6. Fifth normal form

1. First Normal form

      As per First Normal Form, no two Rows of data must contain same information i.e each set of column must have a unique value, such that multiple columns cannot be used to fetch the same row. 

      Also, as per First normal form, a column of a table cannot hold multiple values like separated. It should hold only atomic values.

2. Second Normal form

      As per Second Normal form, A table that is in first normal form (1NF) must meet additional criteria if it is to qualify for second normal form. Specifically: a table is in 2NF if it is in 1NF and no non-prime attribute is dependent on any proper subset of any candidate key of the table. A non-prime attribute of a table is an attribute that is not a part of any candidate key of the table.

3. Third Normal form

      As per the Third Normal for, The entity should be in Second Normal form and no column entry should be dependent on any other entry other than the key column for the table. If such entry exists, move it outside into a new table.

4. Boyce – codd Normal form

      As per Boyce – codd Normal form, A table is said to be in BCNF if it is in 3NF and contains each and every determinant as a candidate key.

5. Fourth Normal form

      As per Fourth Normal form,  A table is said to be in 4NF if it is in BCNF and contains no multi-valued dependencies.

6. Fifth Normal form

      As per Fifth Normal form, A table is said to be in 5NF if it is in 4NF and contains no join dependencies.

 

System Control Statement

      ALTER SYSTEM statement dynamically alter the database instance and user must have ALTER SYSTEM system privilege.

Examples :

Restricting sessions

     While performing application maintenance, generally all sessions need to close and restricts new session. Use below statement to restrict sessions.

SQL> ALTER SYSTEM ENABLE RESTRICTED SESSION;

      You can kill any existing sessions by  KILL SESSION clause of the ALTER SYSTEM statement. You can see all active sessions by issuing below statement.

SQL> SELECT sid, serial#, username FROM v$session;
ALTER SYSTEM KILL SESSION '29, 34';

     After completion of maintenance,  You can allow users to create sessions by issuing below statement.

SQL> ALTER SYSTEM DISABLE RESTRICTED SESSION;

Clearing the Shared Pool

      Before beginning performance analysis, you might  want to clear the shared pool. Issue the below statement to clear shared spool.

SQL> ALTER SYSTEM FLUSH SHARED_POOL;

Forcing a Checkpoint 

      Issue the below statement to force check point.

SQL> ALTER SYSTEM CHECKPOINT;

Enabling Resource Limits 

     Issue the below statement to enable resource limit.

SQL> ALTER SYSTEM SET RESOURCE_LIMIT = TRUE;

Forcing a Log Switch

      While renaming or moving the log file, it is not possible for the current logging file. Then we need to switch the log file manually. Issue the below statement to force log switch.

SQL> ALTER SYSTEM SWITCH LOGFILE;

Disconnecting a Session

      Issue the below statement to disconnects user’s session, using the SID and SERIAL# values from V$SESSION

SQL> ALTER SYSTEM DISCONNECT SESSION '10, 18' POST_TRANSACTION;

Session Control Statements

      Session control statements are dynamically manages the properties of a user session and don’t implicitly commit the current transaction.

Note : PL/SQL doesn’t support session control statements.

Following are the Session control statements.

ALTER SESSION : This statement is used  set or modify  the conditions or parameters that affect your connection to the database. The statement stays in effect until session closed.

Example :

Changing the date format dynamically

SQL> SELECT TO_CHAR(SYSDATE) TODAY FROM DUAL;

TODAY
---------
01-JAN-17

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY MM DD HH24:MI:SS';

Session altered.

SQL> SELECT TO_CHAR(SYSDATE) TODAY FROM DUAL;

TODAY
-------------------
2017 01 01 20:12:31

SQL> ALTER SESSION SET NLS_DATE_FORMAT='YYYY-MM-DD HH:MI:SS';

Session altered.

SQL> SELECT TO_CHAR(SYSDATE) TODAY FROM DUAL;

TODAY
-------------------
2017-01-01 08:13:20

SQL>

      We can not modified all parameters by using ALTER  SESSION. We can check what parameters can be modify by looking at V$PARAMETER view.

SQL> DESC V$PARAMETER;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 NUM                                                NUMBER
 NAME                                               VARCHAR2(80)
 TYPE                                               NUMBER
 VALUE                                              VARCHAR2(4000)
 DISPLAY_VALUE                                      VARCHAR2(4000)
 ISDEFAULT                                          VARCHAR2(9)
 ISSES_MODIFIABLE                                   VARCHAR2(5)
 ISSYS_MODIFIABLE                                   VARCHAR2(9)
 ISINSTANCE_MODIFIABLE                              VARCHAR2(5)
 ISMODIFIED                                         VARCHAR2(10)
 ISADJUSTED                                         VARCHAR2(5)
 ISDEPRECATED                                       VARCHAR2(5)
 ISBASIC                                            VARCHAR2(5)
 DESCRIPTION                                        VARCHAR2(255)
 UPDATE_COMMENT                                     VARCHAR2(255)
 HASH                                               NUMBER

SQL>

We can see all parameters in V$PARAMETER view using SELECT NAME FROM V$PARAMETER;. Also we can see the parameters which can be modified with ALTER SESSION statements  by using below query.

SQL> SELECT NAME FROM V$PARAMETER WHERE ISSES_MODIFIABLE='TRUE';

Also, we can see if any parameter can be modified with ALTER SESSION or if it is modifiable at the instance level by using below query.

SQL> SELECT NAME,ISSES_MODIFIABLE,ISSYS_MODIFIABLE,ISINSTANCE_MODIFIABLE
 2 FROM V$PARAMETER WHERE NAME='nls_date_format';

NAME
--------------------------------------------------------------------------------
ISSES ISSYS_MOD ISINS
----- --------- -----
nls_date_format
TRUE  FALSE     FALSE


SQL>

 

SET ROLE : This statement is used to enable or disable roles which have been granted. 

      When use logs on, Oracle Database enables all privileges granted explicitly to the user and all privileges in the user’s default roles. During the session, the user can use SET ROLE statement to change the roles which are currently enabled  in any number of times for the current session.

Note : You cannot enable more than 148 user-defined roles at one time.

Setting roles:

Enable particular role for current  session:

SET ROLE Dev_Role IDENTIFIED BY DevP12#$;

Enable all roles which are granted for current  session. 

SET ROLE dw_manager IDENTIFIED BY warehouse;

Enable all roles except particular one for current  session.

SET ROLE ALL EXCEPT Prog_Role;

Disable all roles for current  session.

SET ROLE NONE;

Data Control Language (DCL) Statements

      DCL statements are used to control access to data stored in the database. GRANT statement is used to allow specified users to perform specified task and REVOKE statement is used to cancel the permission which are previously granted.

GRANT : It is used provide the access or privileges on database objects to users.

Syntax : GRANT privilege_name  ON object_name  TO {user_name |PUBLIC |role_name [WITH GRANT OPTION];

SQL> sho user
USER is "MARK"
SQL>


SQL> select * from scott.employees;
select * from scott.employees
 *
ERROR at line 1:
ORA-00942: table or view does not exist
SQL> show user;
USER is "SCOTT"
SQL>
SQL> GRANT SELECT ON EMPLOYEES TO MARK;

Grant succeeded.

SQL>
SQL> show user;
USER is "MARK"
SQL>
SQL> select * from scott.employees where rownum <5;

 EMPNO     ENAME      JOB       MGR        HIREDATE  SAL        COMM
---------- ---------- --------- ---------- --------- ---------- ----------
 DEPTNO
----------
 7369      SMITH      CLERK     7902       17-DEC-80 800
 20

 7499      ALLEN      SALESMAN  7698       20-FEB-81 1600       300
 30

 7521      WARD       SALESMAN  7698       22-FEB-81 1250       500
 30


 EMPNO     ENAME      JOB       MGR        HIREDATE  SAL        COMM
---------- ---------- --------- ---------- --------- ---------- ----------
 DEPTNO
----------
 7566      JONES      MANAGER   7839       02-APR-81 2975
 20


SQL>

REVOKE : It is used to remove privileges on database object from users.

Syntax : REVOKE privilege_name  ON object_name  FROM {user_name |PUBLIC |role_name}

SQL> show user;
USER is "SCOTT"
SQL>
SQL> REVOKE SELECT ON EMPLOYEES FROM MARK;

Revoke succeeded.

SQL>
SQL> show user;
USER is "MARK"
SQL>
SQL> select * from scott.employees where rownum <5;
select * from scott.employees where rownum 
 *
ERROR at line 1:
ORA-00942: table or view does not exist


SQL>

Click here for more on privileges and roles.

Transaction Control Statements

      Transaction control statements manage changes made by DML statements i.e apply the changes permanently or restore to original. Below are Transaction Control Statements.

COMMIT : Use commit statement at end of your transactions to apply the changes permanently.

SQL> SELECT * FROM STUDENT;  

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 1111      AAA                            IT   A 97         A
 1112      AAB                            CS   B 86         A
 1113      AAC                            ECE  A 78         A
 1114      AAD                            EE   A 89         A
 1115      AAE                            ME   A 74         B

SQL>
SQL> UPDATE STUDENT SET GRADE='C' WHERE PERCENTAGE BETWEEN 75 AND 80;

1 row updated.

SQL> UPDATE STUDENT SET GRADE='B' WHERE PERCENTAGE BETWEEN 80 AND 85;

0 rows updated.

SQL> UPDATE STUDENT SET GRADE='C' WHERE PERCENTAGE<75; 1 row updated. SQL> COMMIT;

Commit complete.

SQL>

ROLLBACK : Use rollback statement to restore all changes since last commit.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 1111      AAA                            IT   A 97         A
 1112      AAB                            CS   B 86         A
 1113      AAC                            ECE  A 78         C
 1114      AAD                            EE   A 89         A
 1115      AAE                            ME   A 74         C

SQL>
SQL> INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE');
Enter value for si_no: 1116
Enter value for full_name: BBB
Enter value for branch: CV
Enter value for section: A
Enter value for percentage: 76
Enter value for grade: B
old 1: INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE')
new 1: INSERT INTO STUDENT VALUES(1116,'BBB','CV','A',76,'B')

1 row created.

SQL>
SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 1111      AAA                            IT   A 97         A
 1112      AAB                            CS   B 86         A
 1113      AAC                            ECE  A 78         C
 1114      AAD                            EE   A 89         A
 1115      AAE                            ME   A 74         C
 1116      BBB                            CV   A 76         B

6 rows selected.

SQL> UPDATE STUDENT SET BRANCH='EC' WHERE SI_NO=1113;

1 row updated.

SQL>
SQL>
SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 1111      AAA                            IT   A 97         A
 1112      AAB                            CS   B 86         A
 1113      AAC                            EC   A 78         C
 1114      AAD                            EE   A 89         A
 1115      AAE                            ME   A 74         C
 1116      BBB                            CV   A 76         B

6 rows selected.

SQL> ROLLBACK;

Rollback complete.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 1111      AAA                            IT   A 97         A
 1112      AAB                            CS   B 86         A
 1113      AAC                            ECE  A 78         C
 1114      AAD                            EE   A 89         A
 1115      AAE                            ME   A 74         C

SQL>

SAVEPOINT : It is used create a point within a groups of transactions to rollback to particular transaction.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 1111      AAA                            IT   A 97         A
 1112      AAB                            CS   B 86         A
 1113      AAC                            ECE  A 78         C
 1114      AAD                            EE   A 89         A
 1115      AAE                            ME   A 74         C

SQL>
SQL> DELETE FROM STUDENT WHERE SECTION='B';

1 row deleted.

SQL>
SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 1111      AAA                            IT   A 97         A
 1113      AAC                            ECE  A 78         C
 1114      AAD                            EE   A 89         A
 1115      AAE                            ME   A 74         C

SQL>
SQL>
SQL> SAVEPOINT delete_section_B;

Savepoint created.

SQL> INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE');
Enter value for si_no: 1116
Enter value for full_name: BBB
Enter value for branch: CS
Enter value for section: A
Enter value for percentage: 87
Enter value for grade: B
old 1: INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE')
new 1: INSERT INTO STUDENT VALUES(1116,'BBB','CS','A',87,'B')

1 row created.

SQL>
SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 1111      AAA                            IT   A 97         A
 1113      AAC                            ECE  A 78         C
 1114      AAD                            EE   A 89         A
 1115      AAE                            ME   A 74         C
 1116      BBB                            CS   A 87         B

SQL>
SQL> SAVEPOINT insert_BBB;

Savepoint created.

SQL>
SQL> UPDATE STUDENT SET GRADE='A';

5 rows updated.

SQL>
SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 1111      AAA                            IT   A 97         A
 1113      AAC                            ECE  A 78         A
 1114      AAD                            EE   A 89         A
 1115      AAE                            ME   A 74         A
 1116      BBB                            CS   A 87         A

SQL>
SQL>
SQL> ROLLBACK TO insert_BBB;

Rollback complete.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 1111      AAA                            IT   A 97         A
 1113      AAC                            ECE  A 78         C
 1114      AAD                            EE   A 89         A
 1115      AAE                            ME   A 74         C
 1116      BBB                            CS   A 87         B

SQL> ROLLBACK TO delete_section_B;

Rollback complete.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 1111      AAA                            IT   A 97         A
 1113      AAC                            ECE  A 78         C
 1114      AAD                            EE   A 89         A
 1115      AAE                            ME   A 74         C

SQL>
SQL> ROLLBACK;

Rollback complete.

SQL> SELECT * FROM STUDENT;

 SI_NO     FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 1111      AAA                            IT   A 97         A
 1112      AAB                            CS   B 86         A
 1113      AAC                            ECE  A 78         C
 1114      AAD                            EE   A 89         A
 1115      AAE                            ME   A 74         C

SQL>

SET TRANSACTION : It is used to establish the current transaction as read-only or read/write. 

Syntax : SET TRANSACTION [ READ WRITE | READ ONLY ];

[oracle@learndba ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 12 22:56:06 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SET TRANSACTION READ ONLY;

Transaction set.

SQL>
SQL> 
SQL> INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE');
Enter value for si_no: 1116
Enter value for full_name: aa  AAF
Enter value for branch: EE
Enter value for section: B
Enter value for percentage: 67
Enter value for grade: C
old 1: INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE')
new 1: INSERT INTO STUDENT VALUES(1116,'AAF','EE','B',67,'C')
INSERT INTO STUDENT VALUES(1116,'AAF','EE','B',67,'C')
 *
ERROR at line 1:
ORA-01456: may not perform insert/delete/update operation inside a READ ONLY
transaction

SQL>EXIT
Disconnected from Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options
[oracle@learndba ~]$
[oracle@learndba ~]$
[oracle@learndba ~]$ sqlplus scott/tiger

SQL*Plus: Release 11.2.0.1.0 Production on Mon Dec 12 22:58:47 2016

Copyright (c) 1982, 2009, Oracle. All rights reserved.


Connected to:
Oracle Database 11g Enterprise Edition Release 11.2.0.1.0 - 64bit Production
With the Partitioning, OLAP, Data Mining and Real Application Testing options

SQL> SET TRANSACTION READ WRITE;

Transaction set.

SQL> INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE');
Enter value for si_no: 1116
Enter value for full_name: BBB
Enter value for branch:
Enter value for section: A
Enter value for percentage: 89
Enter value for grade: A
old 1: INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE')
new 1: INSERT INTO STUDENT VALUES(1116,'BBB','','A',89,'A')

1 row created.

SQL>

We can’t change the transaction read write mode in current transaction. We need to exit and set transaction characteristics. 

SQL> SET TRANSACTION READ ONLY;
SET TRANSACTION READ ONLY
*
ERROR at line 1:
ORA-01453: SET TRANSACTION must be first statement of transaction


SQL>

Data Manipulation Language (DML) Statements

      DML statements are used for managing data within schema objects like retrieving, inserting, update and delete etc.

INSERT : Insert statement is used to insert the data into a table.

Syntax :

  1. insert into table_name(column1,..,columnn) values(value1,..,value2);
  2.  insert into table_name values(value1,value2,…,value4);

We can use #1 syntax to insert the data into particular fields or columns and #2 syntax used insert data for all fields or columns. 

For #1, we need to provide the values for mentioned  fields or columns in “table ()”.

For #2, we need to provide values for all fields or columns.  

SQL> CREATE TABLE STUDENT(SI_NO NUMBER(6),FULL_NAME VARCHAR2(30),BRANCH CHAR(4),
SECTION CHAR(1),PERCENTAGE NUMBER(4,2),GRADE CHAR(1));

Table created.

SQL>
SQL>
SQL>
SQL> INSERT INTO STUDENT(SI_NO,FULL_NAME,BRANCH,SECTION) VALUES(101,'AAA','CSE',
'A');

1 row created.

SQL>
SQL> SELECT SI_NO,FULL_NAME,BRANCH,SECTION FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S
---------- ------------------------------ ---- -
 101       AAA                            CSE  A

SQL>
SQL> INSERT INTO STUDENT VALUES(102,'AAB','CSE','A',74.90,'B');

1 row created.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A
 102       AAB                            CSE  A 74.9       B

SQL>
SQL>
SQL>
SQL> INSERT INTO STUDENT VALUES(&SI_NO,&FULL_NAME,&BRANCH,&SECTION,&PERCENTAGE,
&GRADE);
Enter value for si_no: 103
Enter value for full_name: 'AAC'
Enter value for branch: 'CSE'
Enter value for section: 'A'
Enter value for percentage: 75.20
Enter value for grade: 'A'
old 1: INSERT INTO STUDENT VALUES(&SI_NO,&FULL_NAME,&BRANCH,&SECTION,&PERCENTAGE,&GRADE)
new 1: INSERT INTO STUDENT VALUES(103,'AAC','CSE','A',75.20,'A')

1 row created.

SQL> /
Enter value for si_no: 104
Enter value for full_name: 'AAD'
Enter value for branch: 'CSE'
Enter value for section: 'A'
Enter value for percentage: 65.90
Enter value for grade: 'C'
old 1: INSERT INTO STUDENT VALUES(&SI_NO,&FULL_NAME,&BRANCH,&SECTION,&PERCENTAGE,
&GRADE)
new 1: INSERT INTO STUDENT VALUES(104,'AAD','CSE','A',65.90,'C')

1 row created.

SQL> INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',
&PERCENTAGE,'&GRADE');
Enter value for si_no: 105
Enter value for full_name: AAE
Enter value for branch: CSE
Enter value for section: A
Enter value for percentage: 76.00
Enter value for grade: A
old 1: INSERT INTO STUDENT VALUES(&SI_NO,'&FULL_NAME','&BRANCH','&SECTION',&PERCENTAGE,'&GRADE')
new 1: INSERT INTO STUDENT VALUES(105,'AAE','CSE','A',76.00,'A')

1 row created.

SQL>

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 105       AAE                            CSE  A 76         A

SQL>

We can use “&” char in values statement to provide values manually out side of the query. If we want into insert multiple records this will very help full.

We can repeat the same query by ” / ” char and then enter. It will execute previous query.

Note : For string and date values, we must enclose the values by single quote or double quote. 

Note : In above query using “&”, we can enclose fields in values statement(‘&FULL_NAME) or while entering the values (‘AAA’).

 

UPDATE : It is used to update existing records in a table.

Syntax : update table_name set field=value  where conditions;

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 105       AAE                            CSE  A 76         A

SQL>
SQL>
SQL>
SQL> UPDATE STUDENT SET PERCENTAGE=85.09 WHERE SI_NO=101;

1 row updated.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A 85.09
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 105       AAE                            CSE  A 76         A

SQL>
SQL>
SQL> UPDATE STUDENT SET GRADE='A' WHERE PERCENTAGE>75;

3 rows updated.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A 85.09      A
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 105       AAE                            CSE  A 76         A

SQL>

Update multiple fields.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A 85.09      A
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 105       AAE                            CSE  A 76         A

SQL>


SQL> SQL> SQL>
SQL>
SQL> UPDATE STUDENT SET SI_NO=165,BRANCH='IT',SECTION='B' WHERE FULL_NAME='AAE';

1 row updated.

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A 85.09      A
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 165       AAE                            IT   B 76         A

SQL>

Note : We can use update command with out where condition. But it is very risky, it updates all records in the table. If auto commit enabled, we can’t rollback it.

DELETE: It is used to delete records in a table. It never delete allocated space.

Syntax : delete from table_name [where conditions],

SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A 85.09      A
 102       AAB                            CSE  A 74.9       B
 103       AAC                            CSE  A 75.2       A
 104       AAD                            CSE  A 65.9       C
 165       AAE                            IT   B 76         A

SQL>
SQL> DELETE FROM STUDENT WHERE SI_NO=103;

1 row deleted.

SQL>
SQL> SELECT * FROM STUDENT;

SI_NO      FULL_NAME                      BRAN S PERCENTAGE G
---------- ------------------------------ ---- - ---------- -
 101       AAA                            CSE  A 85.09      A
 102       AAB                            CSE  A 74.9       B
 104       AAD                            CSE  A 65.9       C
 165       AAE                            IT   B 76         A

SQL>

Delete all records from table.

SQL> DELETE FROM STUDENT;

4 rows deleted.

SQL> SELECT *FROM STUDENT;

no rows selected

SQL>

 

MERGE : Use this statement to select rows from one or more source tables for update or insertion into a another table (target table).

 

SQL> CREATE TABLE EMP_BONUS (EMP_ID NUMBER,BONUS NUMBER DEFAULT 100);

Table created.

SQL>
SQL> INSERT INTO EMP_BONUS(EMP_ID) (SELECT EMPNO FROM EMPLOYEES);

14 rows created.

SQL>
SQL> SELECT * FROM EMP_BONUS;

EMP_ID     BONUS
---------- ----------
 7369      100
 7499      100
 7521      100
 7566      100
 7654      100
 7698      100
 7782      100
 7788      100
 7839      100
 7844      100
 7876      100

EMP_ID     BONUS
---------- ----------
 7900      100
 7902      100
 7934      100

14 rows selected.

SQL>
SQL> MERGE INTO EMP_BONUS B
 USING (SELECT * FROM EMPLOYEES) S
 ON (B.EMP_ID = S.EMPNO)
 WHEN MATCHED THEN UPDATE SET B.BONUS = B.BONUS + S.SAL*.01
 DELETE WHERE (S.SAL > 2000)
 WHEN NOT MATCHED THEN INSERT (B.EMP_ID, B.BONUS)
 VALUES (S.EMPNO, S.EMPNO*0.1)
 WHERE (S.SAL <= 2000); 2 3 4 5 6 7 8

14 rows merged.

SQL> select * from EMP_BONUS;

EMP_ID     BONUS
---------- ----------
 7369      108
 7499      116
 7521      112.5
 7654      112.5
 7844      115
 7876      111
 7900      109.5
 7934      113

8 rows selected.

SQL>
SQL> select d.EMP_ID,e.sal,d.BONUS from EMP_BONUS d,EMPLOYEES e where 
d.EMP_ID=e.EMPNO;

 EMP_ID    SAL        BONUS
---------- ---------- ----------
 7369      800        108
 7499      1600       116
 7521      1250       112.5
 7654      1250       112.5
 7844      1500       115
 7876      1100       111
 7900      950        109.5
 7934      1300       113

8 rows selected.

SQL>

Here, We have increased the bonus  to 1% for employees whose salary less than 2000 and deleted entries of whose salary greater than 2000.

LOCK TABLE : Lock one or more tables in a specified mode. Table access denied to a other users until your table operation completed.

Syntax : LOCK TABLE table_name IN [ SHARE | EXCLUSIVE ] MODE

SQL> LOCK TABLE EMPLOYEES IN SHARE MODE NOWAIT;

Table(s) Locked.

SQL>

Click here for more on LOCK TABLE

CALL :It is used to execute a stored procedure or a function within SQL.

SQL> CALL my_procedure()

We will learn more about Stored procedures in next chapters.

EXPLAIN PLAN : The EXPLAIN PLAN statement displays execution plans chosen by the Oracle optimizer for SELECT, UPDATE, INSERT, and DELETE statements. 

SQL> explain plan for select * from EMPLOYEES;

Explained.

SQL> 
SQL> 
SQL> SELECT PLAN_TABLE_OUTPUT FROM TABLE(DBMS_XPLAN.DISPLAY());

PLAN_TABLE_OUTPUT
--------------------------------------------------------------------------------
Plan hash value: 1445457117

-------------------------------------------------------------------------------
| Id  | Operation         | Name      | Rows | Bytes | Cost (%CPU)| Time      |
-------------------------------------------------------------------------------
|  0  | SELECT STATEMENT  |           |   14 |   532 |       3 (0)|  00:00:01 |
|  1  |  TABLE ACCESS FULL| EMPLOYEES |   14 |   532 |       3 (0)|  00:00:01 |
-------------------------------------------------------------------------------

8 rows selected.

SQL>

 

 

Data Definition Language (DDL) Statements

CREATE Statement : Create statement used to create data base objects like tables, views, indexes, triggers and synonyms etc..

CREATE TABLE : Syntax is create table table_name(column1 data type,column2 data type,…….,cloumnn data type);

SQL> CREATE TABLE PROFILE (ID INT,FNAME VARCHAR2(50),LNAME VARCHAR2(50),FULL_NAME VARCHAR2(100),
EMAIL VARCHAR2(100),MOBILE_NO NUMBER(10),CREATED_DATE DATE,CREATEDBY VARCHAR2(100));

Table created.

SQL>
SQL> DESC PROFILE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 FNAME                                              VARCHAR2(50)
 LNAME                                              VARCHAR2(50)
 FULL_NAME                                          VARCHAR2(100)
 EMAIL                                              VARCHAR2(100)
 MOBILE_NO                                          NUMBER(10)
 CREATED_DATE                                       DATE
 CREATEDBY                                          VARCHAR2(100)

SQL>

CREATE VIEW : Syntax : create view view_name as select column1,column2,…,columnn from table_name [ where conditions];

SQL> CREATE VIEW ID_EMAIL AS SELECT ID,EMAIL FROM PROFILE;

View created.

SQL> desc ID_EMAIL;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 EMAIL                                              VARCHAR2(100)

SQL>

CREATE INDEX : Syntax is create index index_name on table_name(column);

SQL> CREATE INDEX ID_IDX ON PROFILE(ID);

Index created.

SQL>

CREATE TRIGGER : Syntax is  create trigger trigger_name [before or after]  [delete or insert  or update] on table pl/sql block

SQL> create trigger update_date before insert on PROFILE
 2 for each row
 3 declare
 4 username varchar2(20)
 5 ;
 6 begin
 7 select user into username from dual;
 8 :new.CREATED_DATE:=sysdate;
 9 :new.CREATEDBY:=username;
 10
 11 end;
 12 /

Trigger created.

SQL>

CREATE SYNONYM : Syntax is create [or replace] [public] synonym synonym_name for object_name;

SQL>  CREATE OR REPLACE PUBLIC SYNONYM LIST_TAB FOR sree.DETAILS_LIST;

Synonym created.

SQL>

You will learn more about index, view, triggers and synonyms in next chapters.

 

ALTER Statement : ALTER statement used to modify the definition of objects like rename, adding or removing columns or constraints etc..  

ALTER TABLE: Syntax is alter table table_name [add or modify or drop column] column datatype;

SQL> ALTER TABLE PROFILE ADD STATUS CHAR(1);

Table altered.

SQL> DESC PROFILE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 FNAME                                              VARCHAR2(50)
 LNAME                                              VARCHAR2(50)
 FULL_NAME                                          VARCHAR2(100)
 EMAIL                                              VARCHAR2(100)
 MOBILE_NO                                          NUMBER(10)
 CREATED_DATE                                       DATE
 CREATEDBY                                          VARCHAR2(100)
 STATUS                                             CHAR(1)

SQL>
SQL>
SQL> ALTER TABLE PROFILE MODIFY LNAME VARCHAR2(40);

Table altered.

SQL>
SQL>
SQL> DESC PROFILE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 FNAME                                              VARCHAR2(50)
 LNAME                                              VARCHAR2(40)
 FULL_NAME                                          VARCHAR2(100)
 EMAIL                                              VARCHAR2(100)
 MOBILE_NO                                          NUMBER(10)
 CREATED_DATE                                       DATE
 CREATEDBY                                          VARCHAR2(100)
 STATUS                                             CHAR(1)

SQL>
SQL>
SQL> ALTER TABLE PROFILE DROP COLUMN CREATEDBY;

Table altered.

SQL> DESC PROFILE;
 Name                                      Null?    Type
 ----------------------------------------- -------- ----------------------------
 ID                                                 NUMBER(38)
 FNAME                                              VARCHAR2(50)
 LNAME                                              VARCHAR2(40)
 FULL_NAME                                          VARCHAR2(100)
 EMAIL                                              VARCHAR2(100)
 MOBILE_NO                                          NUMBER(10)
 CREATED_DATE                                       DATE
 STATUS                                             CHAR(1)

SQL>

 

 

ALTER INDEX : Syntax is alter index index_name [RENAME | REBUILD] [ TO NEW_NAME | COMPUTE STATISTICS ]

SQL> ALTER INDEX ID_IDX RENAME TO NEW_ID_IDEX;

Index altered.

SQL>
SQL> ALTER INDEX NEW_ID_IDEX REBUILD COMPUTE STATISTICS;

Index altered.

SQL>

ALTER TRIGGER : Use the ALTER TRIGGER statement to enable, disable, or compile a database trigger.

SQL> ALTER TRIGGER update_date DISABLE;

Trigger altered.

SQL>
SQL> ALTER TRIGGER update_date ENABLE;

Trigger altered.

SQL> ALTER TRIGGER update_date RENAME TO update_date_profile;

Trigger altered.

SQL>

ALTER SYNONYM : Used to modify an existing synonym.

SQL>
SQL> ALTER SYNONYM LIST_TAB COMPILE;

Synonym altered.

SQL> ALTER SYNONYM LIST_TAB NONEDITIONABLE;

Synonym altered.

SQL>

TRUNCATE Statement : TRUNCATE statement used delete all records in the table and deallocates all space used by the removed records.

Syntax : truncate table table_name;

SQL> TRUNCATE TABLE PROFILE;

Table truncated.

SQL>

Caution : You cannot roll back a TRUNCATE TABLE statement.

DROP Statement : DROP statement used remove a table or object from the database entirely.

SQL> DROP TABLE PROFILE;

Table dropped.

SQL>


SQL> DROP VIEW ID_EMAIL;

View dropped.

SQL>
SQL> DROP TRIGGER update_date_profile;

Trigger dropped.

SQL>
SQL>
SQL> DROP INDEX NEW_ID_IDEX;

Index dropped.

SQL>

 

RENAME Statement :

RENAME TABLE allows you to rename an existing table.

SQL> RENAME EMP TO EMPLOYEES;

Table renamed.

SQL>