Constraints

CONSTRAINTS

==>It Enforce the rule on table.
==>We can create Constrains while creating the table and After the table has been created.
1.For Column level(Cont Name  ...SYS_C000001)
2.For Table Level(We assign the OWN names)

Type Of Constraints::

There are five types of constraints are there.

1.Primary Key
2.Unique Key
3.Foreign Key
4.CHECK Constraint
5.NOT NULL
------------------------------------------------------------------------------------------
TYPES                      || DUPLICATE   ||              NULL
-------------------------------------------------------------------------------------------
1.Primary Key(PK) NOT ALLOW NOT ALLOW
--------------------------------------------------------------------------------------------
2.Unique Key(U)         NOT ALLOW              ALLOW
--------------------------------------------------------------------------------------------
3.Forign Key(RK)          ALLOW                   ALLOW
--------------------------------------------------------------------------------------------
4.CHECK(CK) It's Our Own Condition
---------------------------------------------------------------------------------------------
5.NOT NULL(NN) NOT ALLOW
---------------------------------------------------------------------------------------------

Naming Rules::

Examples::

Employee_Id ------------------------> emp_id_pk
email_Id ------------------------> emp_mail_uk
First_Name ------------------------> emp_fname_nn
Salary ------------------------> emp_Salary_ck
Department_Id ------------------------> emp_did_rk

Examples::
-

CREATE TABLE STUDENTS_DB
(
STU_ID NUMBER(5),
STU_NAME VARCHAR2(50) NOT NULL,
STU_GENDER CHAR,
STU_EMAIL VARCHAR2(50),
STU_DID NUMBER(10),

CONSTRAINT STU_ID_PK PRIMARY KEY(STU_ID),
CONSTRAINT STU_GENDER_CK CHECK(STU_GENDER IN ('M','F','m','f')),
CONSTRAINT STU_EMAIL_UK UNIQUE(STU_EMAIL),
CONSTRAINT STU_DID_RK FORIGN KEY(STU_DID) REFERENCES (DEPARTMENT_ID)
);

ALTER SYNTAX::

ALTER TABLE <TABLE_NAME...>
ADD CONSTRAINTS.........

USING ALTER::
-
ALTER TABLE STUDENT_DB
MODIFY STU_NAME VARCHAR2(50) NOT NULL;

CONDITIONS TO USE CONSTRAINTS::

*We can use more than one constraints for a single column.
*We cant alter or Modify constraint except (NOT NULL) Column
*If u want to change Drop and re create again
*We can Alter or Modify the NOT NULL column contraints
*While Create Primary & Unique Constraint automatically the "UNIX INDEX" will created.

NOTE::
-
*Using "DICT" table we can see the data dictionary tables.

Example::
-
SELECT * FROM DICT WHERE TABLE_NAME like '%CONST%';

To Find the Constraint Tables::
-
SELECT * FROM USER_CONSTRAINTS WHERE TABLE_NAME = UPPER('STUDENTS_DB');
SELECT * FROM USER_CONS_COLUMNS WHERE TABLE_NAME = UPPER('STUDENTS_DB');

ON DELETE CASCADE  & ON DELETE SET NULL::
-
If We use the "ON DELETE CASCADE" the related data's also deleted from the Child table.
If we us the "ON DELETE SET NULL" the related data's has changed into "NULL" in the CHILD table.

Examples::
-

CONSTRAINTS STU_DID_RK FORIGN KEY (STU_DID) REFERENCES                              Departments(Department_Id)
ON DELETE CASCADE;



CONSTRAINTS STU_DID_RK FORIGN KEY (STU_DID) REFERENCES Departments(Department_Id)
ON DELETE SET NULL;

ON DELETE CASCADE::

1.When we use ON DELETE CASCADE While Create the FOREIGN Key,If the Parent 
table Primary key Value is deleted 
then the Child table Value is also deleted
2.When we delete the FOREIGN key Column in a table it will be deleted. but 
its not affect the Parent table(Primary KEY
table)
3.In this same case if we try to Drop the Primary key Shows error
4.In this case if we Drop FOREIGN key it will be drop.

CREATE TABLE supplier
    (      supplier_id     numeric(10)     not null,
           supplier_name   varchar2(50)    not null,
           contact_name    varchar2(50),
           CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
   );

Table created.

CREATE TABLE products
     (      product_id      numeric(10)     not null,
            supplier_id     numeric(10)     not null,
            supplier_name   varchar2(50)    not null,
            CONSTRAINT fk_supplier_comp
              FOREIGN KEY (supplier_id, supplier_name)
             REFERENCES supplier(supplier_id, supplier_name)
             ON DELETE CASCADE
     );
SQL> INSERT INTO SUPPLIER VALUES('100','Arul','XXX');

1 row created.

SQL> INSERT INTO SUPPLIER VALUES('101','XAVIER','YYY');

1 row created.

SQL> INSERT INTO PRODUCTS VALUES('1001','100','Arul');

1 row created.

SQL> INSERT INTO PRODUCTS VALUES('1002','101','XAVIER');

1 row created.

SQL> SELECT * FROM SUPPLIER;

SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- -----------------------------------------------------
100                Arul         XXX
101                XAVIER YYY


SQL> SELECT * FROM PRODUCTS;

PRODUCT_ID SUPPLIER_ID SUPPLIER_NAME
---------- ----------- --------------------------------
      1001         100            Arul
      1002         101            XAVIER

SQL> DELETE FROM SUPPLIER WHERE SUPPLIER_ID=100;

1 row deleted.

SQL> SELECT * FROM SUPPLIER;

SUPPLIER_ID SUPPLIER_NAME CONTACT_NAME
----------- --------------- -------------------------------------
 101 XAVIER                       YYY



SQL> SELECT * FROM PRODUCTS;

PRODUCT_ID SUPPLIER_ID SUPPLIER_NAME
---------- ----------- --------------------------------
 1002                        101 XAVIER

 iii)In Primary Key Case::
 SQL> ALTER TABLE SUPPLIER
  2  DROP CONSTRAINT supplier_pk;
DROP CONSTRAINT supplier_pk
                *
ERROR at line 2:
ORA-02273: this unique/primary key is referenced by some foreign keys

iv)In Foreign Key Case::

SQL> ALTER TABLE PRODUCTS
  2  DROP CONSTRAINT fk_supplier_comp;

Table altered.
=======================================================================

ON DELETE SET NULL::

1.When we Set the ON DELETE SET NULL in Child table ,
While delete the Parent table Primary column the refered Child
column values changed into NULL.
2.If we delete the Foreign kay table values it will be deleted.
3.if we try to Drop primary key Shows ERROR .
4.If we dro the foreign key it will dropped.


CREATE TABLE supplier
    (      supplier_id     numeric(10)     not null,
           supplier_name   varchar2(50)    not null,
           contact_name    varchar2(50),
           CONSTRAINT supplier_pk PRIMARY KEY (supplier_id, supplier_name)
   );

Table created.


CREATE TABLE products
     (      product_id      numeric(10)     not null,
            supplier_id     numeric(10)     ,
            supplier_name   varchar2(50)    ,
            CONSTRAINT fk_supplier_comp
              FOREIGN KEY (supplier_id, supplier_name)
             REFERENCES supplier(supplier_id, supplier_name)
             ON DELETE SET NULL
     );

SQL> DELETE FROM SUPPLIER WHERE SUPPLIER_ID=100;

1 row deleted.


SQL> SELECT * from SUPPLIER;

SUPPLIER_ID SUPPLIER_NAME              CONTACT_NAME
----------- ---------------- -----------------------------------------
        101                XAVIER                           YYY

SQL> SELECT * from PRODUCTS;

PRODUCT_ID SUPPLIER_ID SUPPLIER_NAME
---------- ----------- ------------------------------
      1001
      1002                     101                XAVIER

CASE 3::
--------
SQL> ALTER TABLE SUPPLIER
  2  DROP CONSTRAINT supplier_pk;
DROP CONSTRAINT supplier_pk
                *
ERROR at line 2:
ORA-02273: this unique/primary key is referenced by some foreign keys

CASE 4::

SQL> ALTER TABLE PRODUCTS
  2  DROP CONSTRAINT fk_supplier_comp;

Table altered.
================================================================
UNIQUE KEY + NOT NULL ====>PRIMARY KEY:


Example::

CREATE TABLE College_Master(Clg_Id NUMBER NOT NULL,
UNIQUE (Clg_Id),
College_Name VARCHAR2(30)
);

SQL> INSERT INTO College_Master VALUES(1,'XYZ');

1 row created.

SQL> INSERT INTO College_Master VALUES(1,'ABC');
INSERT INTO College_Master VALUES(1,'ABC')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.SYS_C004129) violated


SQL> INSERT INTO College_Master VALUES(NULL,'ABC');
INSERT INTO College_Master VALUES(NULL,'ABC')
                                  *
ERROR at line 1:
ORA-01400: cannot insert NULL into ("HR"."COLLEGE_MASTER"."CLG_ID")

TO ENABLE & DISABLE & DROP CONSTRAINTS::


ENABLE::

ALTER TABLE TABLE_NAME
ENABLE CONSTRAINT CONSTRAINT_NAME....

DISABLE::

ALTER TABLE TABLE_NAME
DISABLE CONSTRAINT CONSTRAINT_NAME....

DROP::

ALTER TABLE TABLE_NAME
DROP CONSTRAINT CONSTRAINT_NAME....

-------END CONSTRAINTS------

Read More

DDL & DML

 DDL & DML

DDL-(DATA DEFINITION LANGUAGE)

1.CREATE

2.ALTER
     1.ADD
     2.MODIFY
     3.RENAME
     4.DROP
These four for Only the column level Operations

3.DROP
4.TRUNCATE

DML-(DATA MANIPULATION LANGUAGE)::

1.INSERT
2.UPDATE
3.DELETE
4.MERGE

DCL-(DATA CONTROL LANGUAGE)::

1.GRANT
2.REVOKE

TCL-(TRANSACTIONAL CONTROL LANGUAGE)::

1.COMMIT
2.ROLLBACK
3.SAVE POINT

CREATE::

Its mainly used for Create a New table.

SYNTAX::

CREATE TABLE TABLE_NAME (COL1 DATATYPE,COL2 DATATYPE.....);
EXAMPLE::

CREATE TABLE Students
(
STU_ID NUMBER(4),
STU_NAME VARCHAR(20),
STU_GENGER CHAR,
STU_DPT_ID NUMBER(10)
);

ALTER::

The ALTER is mainly used of done the column level changes like
1.ADD NEW COLUMNS
2.MODIFY THE EXISTING DATATYPES
3.RENAME THE EXISTING COLUMNS
4.DROP THE UNWANTED COLUMNS

1.ADD NEW COLUMNS::

Using ADD keyword we can add new columns in existing table.

SYNTAX::

ALTER TABLE <TABLE_NAME>
ADD <COL1_NAME DATATYPE..........>;

Example::


ALTER TABLE students
ADD Feedback VARCHAR2(50);

2.MODIFY THE EXISTING DATATYPES::

Using MODIFY keyword we can Modify existing column datatypes.

SYNTAX::

ALTER TABLE <TABLE_NAME>
MODIFY <COL1_NAME DATATYPE..........>;

Example::


ALTER TABLE students
MODIFY Feedback VARCHAR2(100);

3.RENAME THE EXISTING COLUMNS::

RENAME is mainly used for RENAME the existing column in a table.

SYNTAX::

ALTER TABLE <TABLE_NAME>
RENAME COLUMN <COL_NAME(OLD) TO COL_NAME(NEW)......>;

Example::


ALTER TABLE students
RENAME Column Feedback TO FEEDBACKS;

4.DROP THE UNWANTED COLUMNS::

DROP IN ALTER CASE is mainly used for DROP the particular unwanted columns in a table.

SYNTAX::

ALTER TABLE <TABLE_NAME>
DROP COLUMN <COL1_NAME.>;

Example::


ALTER TABLE students
DROP COLUMN FEEDBACKS;

INSERT::

The INSERT keyword is maily used for INSERT a new record into a table.

SYNTAX::

INSERT INTO TABLE_NAME VALUES('Col1_Date','Col2_Date','...','...')

Examples::

INSERT INTO Students Values('1','Arul','M','10');
INSERT INTO Students Values('2','Xavier','M','20');
INSERT INTO Students Values('4','Anu','F','30');
INSERT INTO Students Values('4','Vino','M','40');
INSERT INTO Students Values('5','Mathi','M','50');

DELETE::

The Delete is Works in Two Methods

1.We can delete Whole date from the table
2.We can Check the Condition in WHERE and delete the Particular data's.

SYNTAX_1::

DELETE FROM TABLE_NAME;
(Deletes Whole data's from the table)

SYNTAX_2::(USING WHERE CLAUSE)

DELETE FROM TABLE_NAME WHERE Col_Name='';

DELETE FROM TABLE WHERE COL_NAME IN ('','','')

Example_1::

DELETE FROM Students;
This query deletes all the data's from the table.

Example_2::

DELETE FROM Students WHERE STU_ID = '1';

Example_3::

DELETE FROM Students WHERE STU_ID IN ('2','3','4');

4.TRUNCATE::

TRUNCATE is same as the DELETE but here we cant check the WHERE Condition.It delete all the Data's from the table.
We cant roll back the data's again.

SYNTAX::

TRUNCATE TABLE TABLE_NAME;

Example::

TRUNCATE TABLE Students;

5.DROP::

DROP Is mainly used delete the Whole table and data's(Structure and data).

SYNTAX::

DROP TABLE TABLE_NAME;

Example::

DROP TABLE Students;

=====>DDL & DML END<=====

Read More

SUB QUERY

               SUB QUERY

*The Query with in another is query is known as "SUB QUERY".
*In Sub query the inner part is execute first and the Outer query will execute second.
*Can not use the WHERE condition after the GROUP BY Clause.
*Sub query start with "(" and ends with ")" after the conditional operator.

Simple Example 1::


SELECT * FROM employees WHERE Salary>
(SELECT Salary FROM employees where First_Name='Neena');

In this query,The inner part executes first and get the "Salary" of employee "Neena" and then will returns the Output,Who
all are having more than(Greater than) "Neena's" salary.

Simple Example 2::


SELECT * FROM employees WHERE Salary>
(SELECT ROUND(AVG(Salary)) FROM employees);

In this query the same Inner part is executes first and then Get the "Average salary" of employees and then return the Output
Who all are having greater then "AVG" salary.

TYPES OF SUB QUERIES::


There are following two types of subsidiaries are there.

1.Single Row Sub Queries.
2.Multiple Row Sub Queries.

1.Single Row Sub Queries::

Query that return only one row from the inner SELECT statement is Known as "Single Row Sub Queries".

Here below i have mentioned some single-row comparison operators::
 -----------------------------------------
= - Equal To
< - Less than
<= - Less than or Equal to
> - Greater than
>= - Greater than or Equal to
<> - Not Equal to
------------------------------------------


Places Which we are Using SUB Queries::

1.USING Group Functions in Sub Query(MIN,MAX,AVG....)
2.After SELECT Key word
3.After HAVING clause

Examples and their explanations for Single Row Sub quries::


SELECT last_name, job_id
FROM employees
WHERE job_id =
(SELECT job_id
FROM employees
WHERE employee_id = 141);

OUTPUT::
In this the Inner Query returns the Output is "ST_CLERK"

So it gives the Output like Below format::
----------------------------------
Nayer ST_CLERK
Mikkilineni ST_CLERK
Landry ST_CLERK
Markle ST_CLERK
Bissot ST_CLERK
Atkinson ST_CLERK
....Cont...
------------------------

SELECT last_name, job_id
FROM employees
WHERE salary>
(SELECT salary
FROM employees
WHERE employee_id = 143);

OUTPUT::
In this the Inner Query returns the Output is "2600" Then outer query execute then gives the Outputs.

So it gives the Output like Below format::
-------------------------------------
King AD_PRES 24000
Kochhar AD_VP 17000
De Haan AD_VP 17000
Hunold IT_PROG 9000
...Cont....


USING SUBQUERIES AFTER HAVING CALUES::


SELECT job_id, AVG(salary)
FROM employees
GROUP BY job_id
HAVING AVG(salary) = (SELECT MIN(AVG(salary))
FROM employees
GROUP BY job_id);

OUTPUT::
---------------------------
JOB_ID AVG(SALARY)
---------------------------
PU_CLERK 2780
---------------------------

2.Multiple Row Sub Queries::

*If Inner Query returns more than one row then it's known as "Multiple Row Sub queries".
*Using Following Operators after the Conditional Operators to Solve this Simple.

--------------------------------------------------------
IN - Equal to any member in the list

ANY - Compare value to each value returned by
the sub query

ALL - Compare value to every value returned
by the sub query
--------------------------------------------------------

USING IN::

While Using in Don't Specify the Conditional Operator With the Word "IN"

Example::

SELECT Employee_Id,First_Name,Salary
FROM employees
WHERE Salary IN
(SELECT Salary from employees where First_Name='Alexandar');

This returns the Out Put in Below Format because the First_Name='Alexandar' having 2 times and the Same Alexander Salary is
equal to Some employees in employee tables.


--------------------------------------
EMPLOYEE_ID FIRST_NAME SALARY
--------------------------------------
158 Allan 9000
152 Peter 9000
109 Daniel 9000
103 Alexander 9000
196 Alana 3100
181 Jean 3100
142 Curtis 3100
115 Alexander 3100
--------------------------------------

USING "ANY" OPERATOR::

Using ANY Operator Which Compares the Each sub query value with the Outer Query and give the OUTPUT based on below
CONDITION BASES.
=================================================================
< ANY --- Get the Max Value in the Sub Query and Give Output Lessthan than the MAX                                 Value.
------------------------------------------------------------------------------------------------
> ANY --- Get the MIN Value in the Sub Query and give Output Greterthan than the MIN                               Value
------------------------------------------------------------------------------------------------
= ANY --- Which Gives the result same as IN Condition
=================================================================

Example::
SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ANY
(select Salary from employees where Job_Id='IT_PROG');

Here first the Sub query will excute and it check the MAX Salary of IT_PROG(MAX is 9000) and returns the output like Less than 9000
who all are get

------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
132 Olson ST_CLERK 2100
128 Markle ST_CLERK 2200
136 Philtanker ST_CLERK 2200
127 Landry ST_CLERK 2400
135 Gee ST_CLERK 2400
119 Colmenares PU_CLERK 2500
131 Marlow ST_CLERK 2500
..Cont.........
------------------------------------------------------

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > ANY
(select Salary from employees where Job_Id='IT_PROG');
OUTPUT::

                ------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
100 King AD_PRES 24000
101 Kochhar AD_VP 17000
102 De Haan AD_VP 17000
145 Russell SA_MAN 14000
146 Partners SA_MAN 13500
201 Hartstein MK_MAN 13000
108 Greenberg FI_MGR 12000
-------------------------------------------------------

USING "ALL" OPERATOR::

Following are the conditions for using ALL Operator.

=================================================================
< ALL --- Get the MIN Value in the Sub Query and Give Output Less than than the MIN Value.
------------------------------------------------------------------------------------------------
> ALL --- Get the MAX Value in the Sub Query and give Output Grater than than the MAX Value
=================================================================

Examples::


SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary < ALL
(select Salary from employees where Job_Id='IT_PROG');

OUTPUT::

------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
115 Khoo PU_CLERK 3100
116 Baida PU_CLERK 2900
117 Tobias PU_CLERK 2800
118 Himuro PU_CLERK 2600
119 Colmenares PU_CLERK 2500
125 Nayer ST_CLERK 3200
126 Mikkilineni ST_CLERK 2700
127 Landry ST_CLERK 2400
128 Markle ST_CLERK 2200
-------------------------------------------------------

SELECT employee_id, last_name, job_id, salary
FROM employees
WHERE salary > ALL
(select Salary from employees where Job_Id='IT_PROG');

OUTPUT::

------------------------------------------------------
EMPLOYEE_ID LAST_NAME JOB_ID SALARY
------------------------------------------------------
100 King AD_PRES 24000
101 Kochhar AD_VP 17000
102 De Haan AD_VP 17000
108 Greenberg FI_MGR 12000
114 Raphaely PU_MAN 11000
145 Russell SA_MAN 14000
146 Partners SA_MAN 13500
147 Errazuriz SA_MAN 12000
148 Cambrault SA_MAN 11000
------------------------------------------------------

 =====>SUB QUERY END<=====

Read More