Integrity constraints Constraints are used to impose business

Integrity constraints Constraints are used to impose business rules. It allows to enter only valid data. consistent data: satisfies the integrity constraints condition on data that type of data called in consistent data: not obeying integrity constraints conditions on data is called in consistent data.

• various types of integrity constraints: • • Domain Integrity: (Domain means a set of permitted values) • • Three types: 1)NOT NULL Constraint 2)CHECK 3)DEFAULT • Entity Integrity: • It defines a entity or column as a unique for particular table. • Two types: • • Unique Constraints Primary Key constraints • Referential Integrity constraints: • Foreign Key • It enforces the Relationship between Two tables.

• As per Oracle six types of Constraints: • • • NOT NULL UNIQUE KEY PRIMARY KEY DEFAULT CHECK FOREIGN KEY

• • • LEVEL OF CONSTRAINTS: Column Level: used to define constraints next in column level Define with each column composite key can not be defined. • Table Level: • Defining constraints after defining all columns • Not null can not be defined. • • constraint clause can apper only Create table Alter table Create view

• • Not Null: Not accept the null values Duplication values allowed defined only at column level Syntax: Create table <tablename> <column name data type> <constraint><constraint name> not null

• Unique Key: • • • Not allowed duplicates values into columns Accept null values. unique key can defined on more than one column (i. e composite key) ckey defined only table level column level • create table stu_det ( • (stuname varchar 2(10) constraint stna_unq unique ); • Table level: • create table ss ( sno number(10), sname varchar 2(10), addr varchar 2(10), constraint snona_cunq unique(sno, sname) );

• • Check constraint: used to impose a conditional Rule on a table column. single columns can have multiple check constraints. Check constraints can be defined at the col level or table level. • create table dept • (deptno number(2) constraint dno_p primary key constraint deptno_chk check(depno between 10 and 99) );

• • Default: if values is not provided for table column default will be considered. default constraints may not stored in the database. • Cascade: • Cascade option used to remove the child Table record automatically, when parent record is removed.

• Foreign key: • foreign key Represent Relationship b/n tables. • fkey consraint can be defined on a single key column either columnlevel or tabel level. • the table containing the foreign key is called the child table • the table containing the reference key is callled the parent key. • parent: Dept • Child: Emp • Foreign key: which uniquely identify each Record which refers pkey of the other table. • Referencekey: which refers pkey of the same table.

• • Creating Reference constraint: Create table Dept ( Deptno number(2) constraint deptno_pk primary key, Dname carchar 2(20) constraint dname_nn Not null, Loc varchar 2(20) constraint loc_nn not null );

• create table emp (empno number(4) constraint empno_p primary key, ename varchar 2(20) constraint ename_nn not null, job varchar 2(15) constraint job_nn not null, mgr number(4) constraint mgr_fk self references emp(empno), hiredate default sysdate, sal number(8, 2) constraint sal_nn not null, comm number(8, 2), deptno number constraint deptno_fk references dept(deptno) On delete cascade );

• Adding constraint to a table: • Alter table <table name> add constraint <constraint name>(columname); • Dropping constraint from a table: • alter table <table name> drop constraint name(column); • Disable constraint: • alter table <table name> Disable constraint<constraint name>[cascade]; • Enable constraint: • alter table <table name> Enable constraint<consname>;

• • JOINS: A join is a Query that combines rows from Two or more Tables, views. when data from more than one table in the database is Required, a join condition. Rows in one table can be joined to rows in another table according to common values existing in columns, i. e Pkey, Fkey. The column in the join condition need not be part of the select list.

• note: if a join involves over two tables then oracle joins the first two based on the • condition and then compares the result with the next table. • Table 1 ----> join 01<--------Table 2 • • Result 01 ---->join 02<-----Table 3 • Result 02 • Note: to join n tables together, you need a minimum of n-1 join condition

• Syntax: where table 1. column=table 2. column • Eg: select empno, ename, dname, loc, grade from emp, dept, salgrade; • select empno, ename, emp. deptno, dname, loc from emp, dept;

• Equi join/inner join/simple join • only matching Records only Display. • note: joining Tables must have at least one common column with same datatype and same values. • Syntax: select col 1, col 2, col 3 ----from <table 1>, <table 2>-----where<table 1>. <common colname>=<table 2>. <common colname>; • select empno, emp. ename, emp. deptno, dept. dname, dept. loc from emp, dept where emp. deptno=deptno; • select e. empno, e. ename, e. sal, e. deptno, d. deptno from emp e inner join dept d on(e. deptno=d. deptno)

• self joins: • A join is performing on the same table • The same table Appears Twice in the From clause. • Select e. ename "empname", m. ename "mgrname" from emp e, emp m where e. mgr=m. empno; Display emp and mgr the emp who is the senior than mgr. • select e. name, m. ename from emp e, emp m where e. mgr=m. empno and e. hiredate<m. hiredate;

• Cartesion join: • this result display in all combination of Rows displayed • here avoide where clause • join condition in valide. >select ename, job, dname. dloc from emp, dept; Non Equi Join: • A non equi join specifies the relationship b/n columns belonging To different tables by making use of between operator. • It is also called between join >select e. empno, e. sal, s. grade, s. losal, s. hisal from emp e, Salgrade s where e. sal between s. losal and s. hisal;

• Joining data from more than Two tables: • The join is first executed upon the two most relevent tables and then • the result is applied upon the third table. • select e. ename, m. ename, sal, grade, d. deptno, dname from emp e inner join dept d on e. deptno=d. deptno inner join emp m on e. mgr=m. empno inner join salgrade s on e. sal between losal and hisal;

Cross join: • Cross join Returns a cartesian product from the two tables. with using where clause. select ename, deptno, dname, loc from emp cross join dept where emp. deptno=deptno • creating joins with the using Clause: • Use the USING clause to match oly one column when more than one column matches. select e. ename, d. dname from emp e join dept d using(deptno) where deptno=10;

special examples: select e. ename "employee name", m. ename "manager" from emp e left outer join emp m on(e. mgr=m. empno) order by 2; • Display the empname and mgr and the mgr job must be a manager. >Select e. enmae, m. ename “manager”, m. job from emp e, emp m where e. mgr=m. empno And m. job=‘manager’

• • • VIEWS: view is one of the object it is a logical table based on one or more tables or views. view cannot hold any information , always information stored in base table. using views we can access the specific information view can be created based on a table called BASE TABLE. DML (Insert, Update, Delete), Select, Desc allowed on views.

Advantages of views: • Provides high security while sharing data b/n users • view can be used to make a simple quries to retrive the result of complicated quries • user_views hold the details of views. Syntax: • create or replace [force/noforce] viewname • as • subquery[with {checkoption/readonly] • constraing constraint name];

force: • base table does not exist not create view (default) no force: • create view base table exist (default) with check: • specifies that only rows accessible to the view • can be inserted or updated or deleted. with Readonly: • ensures that no DML operations can be perfrmed on this view.

Types of views: Simple Views: The view which is created w/o following clauses • join condition • group by • having clause • set operators • distinct eg: create or replace view employee as select *from emp;

Eg: • select empno, ename from employee; • insert into employee values( ); • update employee set job=' 'where deptno= • delete from employee where deptno=30; • desc employee; • data modified after applying the above conditions in view and main table.

note: • Drop the view, the main table data is there. • The main table is dropped the view also dropped • suppose u drop the main table ( ) But u cannot create view on table ( ) • suppose u truncate the table ( ) u can possible to create view on table

• Complex view: • the view which is created with any Restriction caluse is called complex view • complex views can contain subqueries eg of cview: Join view: The view which is created on more than one table Called as a join view • create view empinfo as select e. empno, e. ename, d. deptno, d. dname from emp e, dept d where d. deptno=e. deptno order by d. deptno

• Note: in complex view cannot perform DML operations • create view payinfo as select empno ecode, sal basic sal*0. 25 da, sal*0. 35 hra, sal*0. 12 pf, sal+sal*0. 25+sal*0. 35 -sal*0. 12 Gross from emp;

creating views with columns decleration • create or replace view empv (id_number, name, sal, department_id) select empno, ename, sal deptno from emp where deptno=30; select *from empv id_number name sal department_id

view on view: • we can create view on there view • create view v 1 as select *from emp; • create view v 2 as select empno, ename, sal*0. 22 from v 1 • create view v 3 as select *from v 2; • Note: when we create an view w/o a base table • view created with compilation errors

Inline view: • an inline view is a subquery with an alias • an inline view is placing a subquery in the form clause. • select e. ename, e. sal, e. deptno, I. maxsal from emp e, (select deptno, max(sal) maxsal from emp group by deptno)I where e. deptno=i. deptno and e. sal<i. maxsal;

view in data dictionary: • select text from user_views where view_name='payinfo'; modify a view: • using OR REPLACE option is used to modify an existing view. Dropping a view: • a view can remove w/o losing data • Dropping a view has no effect on the tables upon which the view is created • we need DROP ANY VIEW priviliges to remove the views • Syntax: Drop viewname

Materialized view: • introduced on oracle 8 i • it holds data • no DML operations • only select stmt • REFRESH ON COMMIT procedure is there. • M. VIEWS are used in Data ware housing they are used in increase the speed of • queries on very large databases • if do any changes on table then commit the changes reflect on table.

syntax: • create materialized view <view name> [refresh on commit] as <select stmt>; • create materialized view store refresh on commit as select *from pra; SET AUTO TRACE ON EXPLAIN: • find out how much time taken for execution of query.

• snapshot: • it is database object • it holds always data • if do any changes n table the changes not refresh on snapshot. • no dml possible on snapsht • select only possible • performance is fast. Syntax: • Create snapshot<snapsot name> As Select *from <object name>

SUB QUERIES: • A Sub query is a select stmt that is embedded in a clause of another select stmt • we can bulid power ful stmts out of simple ones. • they can be very useful when you need to select rows from a table with a condition that • depends on the data in the table itself.

TYPES OF QUERY: Root Query: (Independent) • The query which is not depend on any other query for its conditions value eg: select ename, sal from emp where deptno=10; parent query: (main) • the query which depend on any other query for it's conditional value. • create table temp as (select *from emp where deptno=10);

• Sub query (Child query) • The query which provides, conditional values to its parent query. • select ename, deptno, sal, from emp where sal=(select max(sal) from emp); syntax: select list of columns from tablename where condition/expr operator (select list of columns from table);

• A subquery in the where clause of a select stmt is called as Nested Subquery • select empno, enmae, sal, deptno from emp where sal<(select sal from emp where empno=7566); • A sub query in the from cluse of select stmt is called as INLINE VIEW. NOTE: • Sub query can contain another subquery • nolimit in the from clause • 255 subqueries in where clause.

operators used in sub queries: single row operators: • >, =, >=, <, <=, <> • these quires returns only one row from the inner select stmt. Multiple row operators: • In, Any/some, ALL • these quires returns more than one column from the inner select stmt. note: only one order by clause can be used for a select stmt.

• simple sub query with single row: display the emp's who are having a salary more than 7902 emp salary >select ename, empno, sal from emp where sal>(select sal from emp where empno=7902); display the emp's whose job is matched with 7369 emp and they deptno should be 30 >select ename, job, deptno from emp where job= (select job from emp where empno=7369) and deptno=30;

display the emp's who are working in sales dept >select ename, sal, deptno from emp where deptno=(select deptno from dept where dname='sales'); display the emp who are working in newyork location display the o/p in descending order >seleect ename, eno, job, sal from emp where deptno= (select deptno from dept where loc='newyork') and order by sal desc;

Applying Group functions: • the data from the main query can be displayed by using a group function • this function returns a single row. • display the employee details whose getting the highest salary. • >select ename, sal, deptno from emp where sal=(select max(sal) from emp); • display the employee who are having a salary more than the lowest pay of the 20 th dept. • select ename, sal, deptno from emp where sal>(select min(sal)from emp where deptno=20);

Applying having clause: • the oracle server executes the sub query and the result are returned into the having clause of the main query. display the deptno and minsal the min sal more than the 20 th deptno >select deptno min(sal) from emp group by deptno having min(sal)>(select min(sal) from emp where deptno=20); display the lowest avg sal job >select job, sal, avg(sal) from emp group by job having avg(sal)=(select min(avg(sal)) from emp group by job;

multiple row sub query: • In->equel to any member in the list • Any/some->compare value to each value returned by the end query • All->compare value to every value returned by the sub query • Note: >any/some: means more than the minimum value( with including value) • <any/some: more than the maximum value: • >all: • <all:

>select ename, sal, deptno from emp where sal in(select max(sal)from emp group by deptno); >select ename, sal, deptno from emp where sal<some (1250, 1500, 1600) >select ename, sal, deptno from emp where sal>some(select sal from emp where job='salesman');

• >select empno, ename, sal, deptno from emp where sal>any(select sal from emp where deptno=10); >select empno, ename, sal, deptno from emp where sal>all(select sal from emp where deptno=10);

simple sub query: • sub query processed first and processed completely. Co_related sub query: • in the co related sub query a parent query will be executed first and • based on the o/p of outer query the inner query execute null values in a sub query: • one of the values returned by the inner query is null value, and hence the entire query returns no rows. • display the emp's those who are not as a mgr select ename, deptno from emp where empno not in(select unique mgr from emp where mgr is not null);

applying sub queries in from clause: • a subquery in the from clause is equalent to a inline view. select e. empcount, d. deptcount from(select count(*) emp count from emp)e, (select count(*)dept count from emp)d; subselect stmt: • select stmt declared as part of the select stmt select ename, sal (select max(sal) from emp (select min(sal) from emp;

exists: • Returns true if inner query is success other wise fals >select empno, ename, sal, deptno from emp where deptno=10 and exists (select count(*) from emp where deptno=10 and job='analyst' having count(*)>5;