SQL LEARNING CLASS3

CONSTRAINTS:

Constraints are categorized as follows.

Domain integrity constraints

• Not null
• Check

Entity integrity constraints

• Unique
• Primary key

Referential integrity constraints

• Foreign key

Constraints are always attached to a column not a table.

We can add constraints in three ways.

• Column level -- along with the column definition
• Table level -- after the table definition
• Alter level -- using alter command

While adding constraints you need not specify the name but the type only, oracle will internally name the constraint.

If you want to give a name to the constraint, you have to use the constraint clause.

NOT NULL

This is used to avoid null values.

We can add this constraint in column level only.

Ex:

SQL> create table student(no number(2) not null, name varchar(10), marks number(3));

SQL> create table student(no number(2) constraint nn not null, name varchar(10), marks

number(3));

CHECK

This is used to insert the values based on specified condition.

We can add this constraint in all three levels.

Ex:

COLUMN LEVEL

SQL> create table student(no number(2) , name varchar(10), marks number(3) check

(marks > 300));

SQL> create table student(no number(2) , name varchar(10), marks number(3) constraint ch

check(marks > 300));

TABLE LEVEL

SQL> create table student(no number(2) , name varchar(10), marks number(3), check

(marks > 300));

SQL> create table student(no number(2) , name varchar(10), marks number(3), constraint

ch check(marks > 300));

ALTER LEVEL

SQL> alter table student add check(marks>300);

SQL> alter table student add constraint ch check(marks>300);

UNIQUE

This is used to avoid duplicates but it allow nulls.

We can add this constraint in all three levels.

Ex:

COLUMN LEVEL

SQL> create table student(no number(2) unique, name varchar(10), marks number(3));

SQL> create table student(no number(2) constraint un unique, name varchar(10), marks

number(3));

TABLE LEVEL

SQL> create table student(no number(2) , name varchar(10), marks number(3),

unique(no));

SQL> create table student(no number(2) , name varchar(10), marks number(3), constraint

un unique(no));

ALTER LEVEL

SQL> alter table student add unique(no);

SQL> alter table student add constraint un unique(no);

PRIMARY KEY

This is used to avoid duplicates and nulls. This will work as combination of unique and not null.

Primary key always attached to the parent table.

We can add this constraint in all three levels.

Ex:

COLUMN LEVEL

SQL> create table student(no number(2) primary key, name varchar(10), marks number(3));

SQL> create table student(no number(2) constraint pk primary key, name varchar(10),

marks number(3));

TABLE LEVEL

SQL> create table student(no number(2) , name varchar(10), marks number(3),

primary key(no));

SQL> create table student(no number(2) , name varchar(10), marks number(3), constraint

pk primary key(no));

ALTER LEVEL

SQL> alter table student add primary key(no);

SQL> alter table student add constraint pk primary key(no);

FOREIGN KEY

This is used to reference the parent table primary key column which allows duplicates.

Foreign key always attached to the child table.

We can add this constraint in table and alter levels only.

Ex:

TABLE LEVEL

SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

primary key(empno), foreign key(deptno) references dept(deptno));

SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

constraint pk primary key(empno), constraint fk foreign key(deptno) references

dept(deptno));

ALTER LEVEL

SQL> alter table emp add foreign key(deptno) references dept(deptno);

SQL> alter table emp add constraint fk foreign key(deptno) references dept(deptno);

Once the primary key and foreign key relationship has been created then you can not remove any parent record if the dependent childs exists.

USING ON DELTE CASCADE

By using this clause you can remove the parent record even it childs exists.

Because when ever you remove parent record oracle automatically removes all its dependent records from child table, if this clause is present while creating foreign key constraint.

Ex:

TABLE LEVEL

SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

primary key(empno), foreign key(deptno) references dept(deptno) on delete cascade);

SQL> create table emp(empno number(2), ename varchar(10), deptno number(2),

constraint pk primary key(empno), constraint fk foreign key(deptno) references

dept(deptno) on delete cascade);

ALTER LEVEL

SQL> alter table emp add foreign key(deptno) references dept(deptno) on delete cascade;

SQL> alter table emp add constraint fk foreign key(deptno) references dept(deptno) on

delete cascade;

COMPOSITE KEYS

A composite key can be defined on a combination of columns.

We can define composite keys on entity integrity and referential integrity constraints.

Composite key can be defined in table and alter levels only.

Ex:

UNIQUE (TABLE LEVEL)

SQL> create table student(no number(2) , name varchar(10), marks number(3),

unique(no,name));

SQL> create table student(no number(2) , name varchar(10), marks number(3), constraint

un unique(no,name));

UNIQUE (ALTER LEVEL)

SQL> alter table student add unique(no,name);

SQL> alter table student add constraint un unique(no,name);

PRIMARY KEY (TABLE LEVEL)

SQL> create table student(no number(2) , name varchar(10), marks number(3),

primary key(no,name));

SQL> create table student(no number(2) , name varchar(10), marks number(3), constraint

pk primary key(no,name));

PRIMARY KEY (ALTER LEVEL)

SQL> alter table student add primary key(no,anme);

SQL> alter table student add constraint pk primary key(no,name);

FOREIGN KEY (TABLE LEVEL)

SQL> create table emp(empno number(2), ename varchar(10), deptno number(2), dname

varchar(10), primary key(empno), foreign key(deptno,dname) references

dept(deptno,dname));

SQL> create table emp(empno number(2), ename varchar(10), deptno number(2), dname

varchar(10), constraint pk primary key(empno), constraint fk foreign

key(deptno,dname) references dept(deptno,dname));

FOREIGN KEY (ALTER LEVEL)

SQL> alter table emp add foreign key(deptno,dname) references dept(deptno,dname);

SQL> alter table emp add constraint fk foreign key(deptno,dname) references

dept(deptno,dname);

DEFERRABLE CONSTRAINTS

Each constraint has two additional attributes to support deferred checking of constraints.

• Deferred initially immediate
• Deferred initially deferred

Deferred initially immediate checks for constraint violation at the time of insert.

Deferred initially deferred checks for constraint violation at the time of commit.

Ex:

SQL> create table student(no number(2), name varchar(10), marks number(3), constraint

un unique(no) deferred initially immediate);

SQL> create table student(no number(2), name varchar(10), marks number(3), constraint

un unique(no) deferred initially deferred);

SQL> alter table student add constraint un unique(no) deferrable initially deferred;

SQL> set constraints all immediate;

This will enable all the constraints violations at the time of inserting.

SQL> set constraints all deferred;

This will enable all the constraints violations at the time of commit.

OPERATIONS WITH CONSTRAINTS

Possible operations with constraints as follows.

• Enable
• Disable
• Enforce
• Drop

ENABLE

This will enable the constraint. Before enable, the constraint will check the existing data.

Ex:

SQL> alter table student enable constraint un;

DISABLE

This will disable the constraint.

Ex:

SQL> alter table student enable constraint un;

ENFORCE

This will enforce the constraint rather than enable for future inserts or updates.

This will not check for existing data while enforcing data.

Ex:

SQL> alter table student enforce constraint un;

DROP

This will remove the constraint.

Ex:

SQL> alter table student drop constraint un;

Once the table is dropped, constraints automatically will drop.

GROUP BY AND HAVING:

GROUP BY

Using group by, we can create groups of related information.

Columns used in select must be used with group by, otherwise it was not a group by expression.

Ex:

SQL> select deptno, sum(sal) from emp group by deptno;

DEPTNO SUM(SAL)

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

10 8750

20 10875

30 9400

SQL> select deptno,job,sum(sal) from emp group by deptno,job;

DEPTNO JOB SUM(SAL)

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

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

HAVING

This will work as where clause which can be used only with group by because of absence of where clause in group by.

Ex:

SQL> select deptno,job,sum(sal) tsal from emp group by deptno,job having sum(sal) > 3000;

DEPTNO JOB TSAL

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

10 PRESIDENT 5000

20 ANALYST 6000

30 SALESMAN 5600

SQL> select deptno,job,sum(sal) tsal from emp group by deptno,job having sum(sal) > 3000

order by job;

DEPTNO JOB TSAL

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

20 ANALYST 6000

10 PRESIDENT 5000

30 SALESMAN 5600

ORDER OF EXECUTION

• Group the rows together based on group by clause.
• Calculate the group functions for each group.
• Choose and eliminate the groups based on the having clause.
• Order the groups based on the specified column.

ROLLUP GROUPING CUBE:

These are the enhancements to the group by feature.

USING ROLLUP

This will give the salaries in each department in each job category along wih the total salary

fot individual departments and the total salary of all the departments.

SQL> Select deptno,job,sum(sal) from emp group by rollup(deptno,job);

DEPTNO JOB SUM(SAL)

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

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

30 9400

29025

USING GROUPING

In the above query it will give the total salary of the individual departments but with a

blank in the job column and gives the total salary of all the departments with blanks in

deptno and job columns.

To replace these blanks with your desired string grouping will be used

SQL> select decode(grouping(deptno),1,'All Depts',deptno),decode(grouping(job),1,'All

jobs',job),sum(sal) from emp group by rollup(deptno,job);

DECODE(GROUPING(DEPTNO),1,'ALLDEPTS',DEP DECODE(GR SUM(SAL)

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

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 All jobs 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 All jobs 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

30 All jobs 9400

All Depts All jobs 29025

Grouping will return 1 if the column which is specified in the grouping function has been

used in rollup.

Grouping will be used in association with decode.

USING CUBE

This will give the salaries in each department in each job category, the total salary for individual departments, the total salary of all the departments and the salaries in each job category.

SQL> select decode(grouping(deptno),1,’All Depts’,deptno),decode(grouping(job),1,’All

Jobs’,job),sum(sal) from emp group by cube(deptno,job);

DECODE(GROUPING(DEPTNO),1,'ALLDEPTS',DEP DECODE(GR SUM(SAL)

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

10 CLERK 1300

10 MANAGER 2450

10 PRESIDENT 5000

10 All Jobs 8750

20 ANALYST 6000

20 CLERK 1900

20 MANAGER 2975

20 All Jobs 10875

30 CLERK 950

30 MANAGER 2850

30 SALESMAN 5600

30 All Jobs 9400

All Depts ANALYST 6000

All Depts CLERK 4150

All Depts MANAGER 8275

All Depts PRESIDENT 5000

All Depts SALESMAN 5600

                        All Depts All Jobs 29025

CASE AND DEFAULT:

CASE

Case is similar to decode but easier to understand while going through coding

Ex:

SQL> Select sal,

Case sal

When 500 then ‘low’

When 5000 then ‘high’

Else ‘medium’

End case

From emp;

SAL CASE

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

500 low

2500 medium

2000 medium

3500 medium

3000 medium

5000 high

4000 medium

5000 high

1800 medium

1200 medium

2000 medium

2700 medium

2200 medium

3200 medium

DEFAULT

Default can be considered as a substitute behavior of not null constraint when applied to new rows being entered into the table.

When you define a column with the default keyword followed by a value, you are actually telling the database that, on insert if a row was not assigned a value for this column, use the default value that you have specified.

Default is applied only during insertion of new rows.

Ex:

SQL> create table student(no number(2) default 11,name varchar(2));

SQL> insert into student values(1,'a');

SQL> insert into student(name) values('b');

SQL> select * from student;

NO NAME

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

1 a

11 b

SQL> insert into student values(null, ‘c’);

SQL> select * from student;

NO NAME

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

1 a

11 b

C

-- Default can not override nulls.