Monday, July 15, 2013

DBMS-Nested Queries

EX.NO:3 NESTED QUERIES AND JOINS

AIM:

To execute nested queries and join commands in SQL.

NESTED QUERIES :

A subquery is a query within a query. In Oracle, you can create subqueries within your SQL statements. These subqueries can reside in the WHERE clause, the FROM clause, or the SELECT clause.

JOINS:

Join is a query in which data is returned from two or more tables.

How the join will be performed:

Step 1: Make the Cartesian product of the given tables.

Step 2: Check for the equality on common attributes for the given tables.

Natural join:

It returns the matching rows from the table that are being joined.

Syntax:

>select <attribute> from TN where TN1.attribute=TN2.attribute.

Inner join:

It returns the matching rows from the table that are being joined.

Syntax:

>select <attribute> from TN1 innerjoin TN2 on TN1.attribute=TN2.attribute.

Left outer join:

It returns all the rows from the table1 even when they are unmatched.

Syntax:

1. select <attribute> from TN1 left outer join TN2 on TN1.attribute=TN2.attribute.

2. select <attribute> from TN where TN1.attribute(+)=TN2.attribute.

Right outer join:

It returns all the rows from the table2 even when they are unmatched.

Syntax:

1. select <attribute> from TN1 right outer join TN2 on TN1.attribute=TN2.attribute.

2. select <attribute> from TN where TN1.attribute=(+)TN2.attribute.

Full join:

It is the combination of both left outer and right outer join.

Syntax:

>select <attribute> from TN1 full join TN2 on TN1.attribute=TN2.attribute.

Sample Output

NESTED QUERIES - Table Creation

SQL> create table emp_det(eno number(3) not null, ename varchar2(25),

address varchar2(30),

basic_sal number(12,2),job_status varchar2(15),dno number(3));

Table created.

SQL> create table pro_det(pno number(3) not null,pname varchar2(30),

no_of_staff number(3));

Table created.

SQL> create table work_in(pno number(3),eno number(3),pjob char(12));

Table created.

SQL> desc emp_det;

Name Null? Type

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

ENO NOT NULL NUMBER(3)

ENAME VARCHAR2(25)

ADDRESS VARCHAR2(30)

BASIC_SAL NUMBER(12,2)

JOB_STATUS VARCHAR2(15)

DNO NUMBER(3)

SQL> desc pro_det;

Name Null? Type

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

PNO NOT NULL NUMBER(3)

PNAME VARCHAR2(30)

NO_OF_STAFF NUMBER(3)

SQL> desc work_in;

Name Null? Type

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

PNO NUMBER(3)

ENO NUMBER(3)

PJOB CHAR(12)

SQL> insert into emp_det values(&eno,'&ename','&address',&basic_sal,

'&job_status',&dno);

Enter value for eno: 1

Enter value for ename: SaravanaKumar

Enter value for address: GandhiNagar

Enter value for basic_sal: 8000

Enter value for job_status: Manager

Enter value for dno: 10

old 1: insert into emp_det values(&eno,'&ename','&address',&basic_sal,

'&job_status',&dno)

new 1: insert into emp_det values(1,'SaravanaKumar','GandhiNagar',

8000,'Manager',10)

1 row created.

SQL> /

Enter value for eno: 2

Enter value for ename: Mahendran

Enter value for address: RainbowColony

Enter value for basic_sal: 5000

Enter value for job_status: Supervisor

Enter value for dno: 10

old 1: insert into emp_det values(&eno,'&ename','&address',&basic_sal,

'&job_status',&dno)

new 1: insert into emp_det values(2,'Mahendran','RainbowColony',5000,

'Supervisor',10)

1 row created.

SQL> /

Enter value for eno: 3

Enter value for ename:Rajkumar

Enter value for address: EastCoastRoad

Enter value for basic_sal: 10000

Enter value for job_status: Professor

Enter value for dno: 2

old 1: insert into emp_det values(&eno,'&ename','&address',&basic_sal,

'&job_status',&dno)

new 1: insert into emp_det values(3,'RajKumar','EastCoastRoad',10000,

'Professor',2)

1 row created.

SQL> /

Enter value for eno: 4

Enter value for ename: Shirley

Enter value for address: KKnagar

Enter value for basic_sal: 8000

Enter value for job_status: AsstManager

Enter value for dno: 3

old 1: insert into emp_det values(&eno,'&ename','&address',&basic_sal,

'&job_status',&dno)

new 1: insert into emp_det values(4,'Shirley','KKnagar',8000,

'AsstManager',3)

1 row created.

SQL> alter table emp_det modify(ename varchar2(15), address varchar2(15));

Table altered.

SQL> select * from emp_det;

ENO ENAME ADDRESS BASIC_SAL JOB_STATUS DNO

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

1 SaravanaKumar GandhiNagar 8000 Manager 10

2 Mahendran RainbowColony 5000 Supervisor 10

3 RajKumar EastCoastRoad 10000 Professor 2

4 Shirley KKnagar 8000 AsstManager 3

SQL> insert into pro_det values(&pno,'pname',&no_of_staff);

Enter value for pno: 1

Enter value for no_of_staff: 2

old 1: insert into pro_det values(&pno,'pname',&no_of_staff)

new 1: insert into pro_det values(1,'pname',2)

1 row created.

SQL> /

Enter value for pno: 2

Enter value for no_of_staff: 3

old 1: insert into pro_det values(&pno,'pname',&no_of_staff)

new 1: insert into pro_det values(2,'pname',3)

1 row created.

SQL> /

Enter value for pno: 3

Enter value for no_of_staff: 1

old 1: insert into pro_det values(&pno,'pname',&no_of_staff)

new 1: insert into pro_det values(3,'pname',1)

1 row created.

SQL> select * from pro_det;

PNO PNAME NO_OF_STAFF

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

1 pname 2

2 pname 3

3 pname 1

SQL>update pro_det set pname='DBMS' where pno=1;

1 row updated.

SQL> update pro_det set pname='COMPILER' where pno=2;

1 row updated.

SQL>update pro_det set pname='C' where pno=3;

1 row updated.

SQL> select * from Pro_det;

PNO PNAME NO_OF_STAFF

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

1 DBMS 2

2 COMPILER 3

3 C 1

SQL> insert into work_in values(&pno,&eno,'&pjob');

Enter value for pno: 1

Enter value for eno: 1

Enter value for pjob: Programmer

old 1: insert into work_in values(&pno,&eno,'&pjob')

new 1: insert into work_in values(1,1,'Programmer')

1 row created.

SQL> /

Enter value for pno: 2

Enter value for eno: 1

Enter value for pjob: Analyst

old 1: insert into work_in values(&pno,&eno,'&pjob')

new 1: insert into work_in values(2,1,'Analyst')

1 row created.

SQL> /

Enter value for pno: 1

Enter value for eno: 2

Enter value for pjob: Analyst

old 1: insert into work_in values(&pno,&eno,'&pjob')

new 1: insert into work_in values(1,2,'Analyst')

1 row created.

SQL> /

Enter value for pno: 2

Enter value for eno: 2

Enter value for pjob: Programmer

old 1: insert into work_in values(&pno,&eno,'&pjob')

new 1: insert into work_in values(2,2,'Programmer')

1 row created.

SQL> select * from work_in;

PNO ENO PJOB

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

1 1 Programmer

2 1 Analyst

1 2 Analyst

2 2 Programmer

NESTED QUERIES

(i) SQL> select ename from emp_det where dno not in(select dno from

emp_det where ename = 'SaravanaKumar');

ENAME

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

RajKumar

Shirley

(ii)SQL> select ename, dno from emp_det where dno = (select dno from

emp_det where ename = 'RajKumar');

ENAME DNO

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

RajKumar 2

(iii)SQL> select ename from emp_det where eno in(select eno from work_in

where pno = (select pno from

pro_det where pname = 'DBMS')) order by ename;

ENAME

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

Mahendran

SaravanaKumar

(iv)SQL> select ename, basic_sal from emp_det where dno = 2 and

basic_sal>(select max(basic_sal) from

emp_det where dno = 10) order by ename;

ENAME BASIC_SAL

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

RajKumar 10000

(v)SQL> select pno,pname from pro_det where exists(select pno from

work_in where work_in.pno = pro_det.

pno);

PNO PNAME

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

1 DBMS

2 COMPILER

(vi)SQL>select ename, job_status,basic_sal from emp_det where (dno,basic_sal)

in (select dno,basic_sal

from emp_det where ename ='RajKumar');

ENAME JOB_STATUS BASIC_SAL

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

RajKumar Professor 10000

(vii)SQL>select * from emp_det where basic_sal=(select max(basic_sal) from emp_det);

ENO ENAME ADDRESS BASIC_SAL JOB_STATUS DNO

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

3 RajKumar EastCoastRoad 10000 Professor 2

(viii)SQL>select max(basic_sal) from emp_det where basic_sal< (select max(basic_sal)

from emp_det);

MAX(BASIC_SAL)

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

8000

(ix)SQL> select * from emp_det where basic_sal < (select avg(basic_sal) from emp_det);

ENO ENAME ADDRESS BASIC_SAL JOB_STATUS DNO

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

2 Mahendran RainbowColony 5000 Supervisor 10

DBMS-DML

EX.NO:2 DATA MANIPULATION AND DATA CONTROL LANGUAGE

(Insert, select, update and delete commands)

AIM:

To execute the Data Manipulation Language (DML) and Data Control Language (DCL) commands in RDBMS.

Data Manipulation Language-Syntax :

INSERT:

INSERT INTO <table name> values

(

fieldvalue-1,fieldvalue-2,…,fieldvalue-n

);

INSERT INTO <table name> VALUES

(

&fieldname-1,&fieldname-2,…&fieldname-n

);

INSERT INTO <table name>

(

SELECT(att_list) FROM <existing table name>

);

SELECT

SELECT(att_list) FROM <table name> [WHERE <condition/expression>];

UPDATE

UPDATE <table name> SET

(

fieldname-1 = value, fieldname-2 = value,…,fieldname-n = value)

[WHERE <condition/expression>]; );

DELETE

DELETE FROM <table name>

[WHERE <condition/expression>];

Data Control Language:

Data control language provides users with privilege commands.

GRANT

Used to give privilege to user on object

Grant privilege on <object_name> to <user name>;

REVOKE

Used to withdraw the privilege that has been granted to the user.

Revoke privilege on <object_name> from <user name>;

Sample Output

INSERT, SELECT, UPDATE AND DELETE COMMANDS

SQL> create table person(pid int, lastname varchar2(10),firstname varchar(10),

address varchar2(20),age number);

Table created.

INSERTING A SINGLE ROW INTO A TABLE

SQL> insert into person values(1,'Prettina','Anne','Bangalore',14);

1 row created.

SQL> insert into person values(2,'Benitto','Anish','Trichy',24);

1 row created.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

INSERTING MORE THAN ONE ROW USING A SINGLE INSERT COMMAND

SQL> insert into person values(&pid,'&lastname','&firstname','&address',&age);

Enter value for pid: 3

Enter value for lastname: Raj

Enter value for firstname: Anita

Enter value for address: Chennai

Enter value for age: 27

old 1: insert into person values(&pid,'&lastname','&firstname','&address',&age)

new 1: insert into person values(3,'Raj','Anita','Chennai',27)

1 row created.

SQL> /

Enter value for pid: 4

Enter value for lastname: kumar

Enter value for firstname: Ashok

Enter value for address: Coimbatore

Enter value for age: 30

old 1: insert into person values(&pid,'&lastname','&firstname','&address',&age)

new 1: insert into person values(4,'kumar','Ashok','Coimbatore',30)

1 row created.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 kumar Ashok Coimbatore 30

SKIPPING THE FIELDS WHILE INSERTING

SQL> insert into person(pid,lastname,firstname) values(5,Hinn,Benny);

insert into person(pid,lastname,firstname) values(5,'Hinn','Benny')

1 row created.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 kumar Ashok Coimbatore 30

5 Hinn Benny

INSERT VALUES USING MEANINGFUL FIELD NAMES

SQL> insert into person values(&personid,'&lastname','&firstname','&personaddress',&age);

Enter value for personid: 6

Enter value for lastname: Prakash

Enter value for firstname: Bhaskar

Enter value for personaddress: Andhra

Enter value for age: 40

old 1: insert into person values(&personid,'&lastname','&firstname','&personaddress',&age)

new 1: insert into person values(6,'Prakash','Bhaskar','Andhra',40)

1 row created.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 kumar Ashok Coimbatore 30

5 Hinn Benny

6 Prakash Bhaskar Andhra 40

6 rows selected.

UPDATE VALUES USING CONDITION

SQL> update person set address='United States'where pid=5;

1 row updated.

UPDATE VALUES USING &

SQL> update person set address ='&address',age=&age where pid=&pid;

Enter value for address: Assam

Enter value for age: 40

Enter value for pid: 6

old 1: update person set address ='&address',age=&age where pid=&pid

new 1: update person set address ='Assam',age=40 where pid=6

1 row updated.

SQL> /

Enter value for address: Britain

Enter value for age: 55

Enter value for pid: 5

old 1: update person set address ='&address',age=&age where pid=&pid

new 1: update person set address ='Britain',age=55 where pid=5

1 row updated.

SELECT COMMAND TO RETRIEVE THE ENTIRE INFORMATION FROM THE TABLE

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 kumar Ashok Coimbatore 30

5 Hinn Benny Britain 55

6 Prakash Bhaskar Assam 40

6 rows selected.

SELECT COMMAND USING 'WHERE' CLAUSE

SQL>select * from person where lastname= 'Kumar' and address='Coimbatore';

PID LASTNAME FIRSTNAME ADDRESS AGE

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

4 Kumar Ashok Coimbatore 30

7 Kumar Chander Coimbatore 45

SELECT COMMAND TO RETRIEVE THE TOP VALUES

SQL> select * from person where rownum<=3;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

SELECT COMMAND WITH LIKE OPERATOR

SQL> select * from person where address like 'C%';

PID LASTNAME FIRSTNAME ADDRESS AGE

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

3 Raj Anita Chennai 27

4 kumar Ashok Coimbatore 30

SQL> select * from person where address like '%i%';

PID LASTNAME FIRSTNAME ADDRESS AGE

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

2 Benitto Anish richy 24

3 Raj Anita Chennai 27

4 Kumar Ashok Coimbatore 30

5 Hinn Benny Britain 55

SELECT COMMAND USING IN OPERATOR

SQL> select * from person where lastname in('Prettina');

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

SELECT COMMAND USING BETWEEN OPERATOR

SQL> insert into person values(7,'Kumar','Chander','Coimbatore',45);

1 row created.

SQL> select * from person where lastname between 'Kumar' and 'Kumar';

PID LASTNAME FIRSTNAME ADDRESS AGE

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

6 Kumar Ashok Coimbatore 30

7 Kumar Chander Coimbatore 45

SELECT COMMAND TO ELIMINATE DUPLICATES

SQL> select DISTINCT lastname from person;

LASTNAME

----------

Benitto

Hinn

Kumar

Prakash

Prettina

Raj

6 rows selected.

SELECT COMMAND WITH ORDER BY CLAUSE

SQL> select pid, firstname,age from person order by age;

PID FIRSTNAME AGE

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

1 Anne 14

2 Anish 24

3 Anita 27

4 Ashok 30

6 Bhaskar 40

7 Chander 45

5 Benny 55

7 rows selected.

SELECT COMMAND TO CREATE A TABLE

SQL> create table individual as select * from person;

Table created.

SQL> select * from individual;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne Bangalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 Kumar Ashok Coimbatore 30

5 Hinn Benny Britain 55

6 Prakash Bhaskar Assam 40

7 Kumar Chander Coimbatore 45

7 rows selected.

SELECT COMMAND TO INSERT RECORDS

SQL> insert into individual(select * from person);

7 rows created.

SELECT COMMAND WITH FUNCTIONS

SQL> select count(*) as pid from person;

PID

----------

SQL> select count(distinct lastname) as pid from person;

PID

----------

SQL> select max(age) from person;

MAX(AGE)

----------

SQL> select min(age) from person;

MIN(AGE)

----------

SQL> select sum(age) from person;

SUM(AGE)

----------

235

DATA CONTROL LANGUAGE (DCL) COMMANDS

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne BAngalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 Kumar Ashok Coimbatore 30

5 Hinn Benny Britain 55

6 Prakash Bhaskar Assam 40

7 Kumar Chander Coimbatore 45

7 rows selected.

SQL> commit;

Commit complete.

DELETE COMMAND

SQL> delete from person where lastname='Kumar';

2 rows deleted.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne BAngalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

5 Hinn Benny Britain 55

6 Prakash Bhaskar Assam 40

SQL> rollback;

Rollback complete.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne BAngalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 Kumar Ashok Coimbatore 30

5 Hinn Benny Britain 55

6 Prakash Bhaskar Assam 40

7 Kumar Chander Coimbatore 45

7 rows selected.

SQL> savepoint s1;

Savepoint created.

SQL> delete from person;

7 rows deleted.

SQL> select * from person;

no rows selected

SQL> rollback to savepoint s1;

Rollback complete.

SQL> select * from person;

PID LASTNAME FIRSTNAME ADDRESS AGE

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

1 Prettina Anne BAngalore 14

2 Benitto Anish Trichy 24

3 Raj Anita Chennai 27

4 Kumar Ashok Coimbatore 30

5 Hinn Benny Britain 55

6 Prakash Bhaskar Assam 40

7 Kumar Chander Coimbatore 45

7 rows selected.

RESULT:

Thus the Data Manipulation Language (DML) and Data Control Language (DCL) commands in RDBMS were executed and verified.

DBMS-DDL

EX.NO:1 DATA DEFINITION LANGUAGE, TABLE CREATION, CONSTRAINTS

AIM:

To execute the Data Definition Language (DDL) commands and creating the table with constraints using RDBMS.

Data Definition Language-Syntax:

CREATE TABLE

CREATE TABLE <table name>

{

fieldname-1 datatype,

fieldname-2 datatype,

…….

Fieldname-n datatype

};

CREATE TABLE <table name> AS

(

SELECT(att-list) FROM <existing table name>

);

ALTER TABLE

ALTER TABLE <table name> ADD/MODIFY

(

fieldname-1 datatype,

fieldname-2 datatype,

…..

fieldname-n datatype

);

ALTER TABLE DROP COLUMN column name;

DROP TABLE

All data and structure in the table is deleted
Any pending transactions are committed
All indexes are dropped
We can not rollback the drop table statement.

DROP TABLE <table name>;

DESCRIBING TABLE

Desc table name

CHANGING NAME OF AN OBJECT

To change the name of a table, view, sequence, or synonym, execute the rename statement.

Rename old name to new name

TRUNCATE

The truncate table statement :

· Removes all rows from a table

· Release the storage space used by that table

· We cannot rollback row removal when using truncate.

TRUNCATE TABLE <table name>;

SET UNUSED OPTION:

Used to mark one or more columns as unused.

ALTER TABLE table name SET UNUSED(column name )

(or)

ALTER TABLE table name SET UNUNSED COLUMN column name

REFERENCING ANOTHER USER TABLE

Table belonging to other users are not in the user’s schema, we should use the owner’s name as prefix to those tables.

Select * from user name .table name

QUERYING THE DATA DICTIONARY

· To see the names of tables owned by the user

Select table_name from user_tables;

· To view distinct object types owned by the user

Select Distinct object_type from user_objects;

· To view tables, views, synonyms and sequences owned by the user;

Select * from user_catalog;

CONSTRAINTS:

To implement Domain Integrity Constraints, Entity Integrity Constraints, Referential Integrity Constraints

Integrity Constraints are used to enforce rules that the columns in a table have to conform with. It is a mechanism used by Oracle to prevent invalid data entry into the table.

Domain Integrity Constraints

Not Null Constraint – The enforcement of Not Null Constraints in a table ensures that the table contain values
Check Constraint – Allow only a particular range of values

Entity Integrity Constraints

Unique Constraints – The unique constraint designates a Column or a group of columns as unique key. This allows only unique value to be stored in the column. Rejects duplication.
Primary Key Constraints – Primary key similar to unique key. avoids duplication , relation between two tables , does not allow not null values

Referential Integrity Constraints

Enforces relationship between tables. It designates a column or group of columns as a foreign key

Ø Foreign Key – establish a relationship with the specified primary or unique key in another table

Parent Table – Referenced key

Child table – Primary or unique table

Sample Output

CONSTRAINTS

NOT NULL, UNIQUE AND CHECK CONSTRINTS

SQL> create table vendor_master(vencode varchar(5) unique,

venname varchar(7) not null);

Table created.

SQL> desc vendor_master;

Name Null? Type

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

VENCODE VARCHAR2(5)

VENNAME NOT NULL VARCHAR2(7)

SQL> alter table vendor_master add(productprice number(10)

check(productprice<10000));

Table altered.

SQL> insert into vendor_master values('v001','Sony TV',9100);

1 row created.

UNIQUE CONSTRAINT VIOLATION

SQL> insert into vendor_master values('&vencode','&venname',

&productprice);

Enter value for vencode: v001

Enter value for venname: Philips

Enter value for productprice: 6000

old 1: insert into vendor_master values('&vencode','&venname',

&productprice)

new 1: insert into vendor_master values('v001','Philips',6000)

insert into vendor_master values('v001','Philips',6000)

ERROR at line 1:

ORA-00001: unique constraint (USER06.SYS_C002807) violated

SQL> ed

Wrote file afiedt.buf

1* insert into vendor_master values('&vencode','&venname',

&productprice)

SQL> /

Enter value for vencode: v002

Enter value for venname: Sony TV

Enter value for productprice: 6000

old 1: insert into vendor_master values('&vencode','&venname',

&productprice)

new 1: insert into vendor_master values('v002','Sony TV',6000)

1 row created.

NULL CONSTRAINT VIOLATION

SQL> /

Enter value for vencode: v003

Enter value for venname:

Enter value for productprice: 9000

old 1: insert into vendor_master values('&vencode','&venname',

&productprice)

new 1: insert into vendor_master values('v003','',9000)

insert into vendor_master values('v003','',9000)

ERROR at line 1:

ORA-01400: cannot insert NULL into ("USER06"."VENDOR_MASTER".

"VENNAME")

SQL> /

Enter value for vencode: v003

Enter value for venname: Philips

Enter value for productprice: 8000

old 1: insert into vendor_master values('&vencode','&venname',

&productprice)

new 1: insert into vendor_master values('v003','Philips',8000)

1 row created.

CHECK CONSTRAINT VIOLATION

SQL> /

Enter value for vencode: v004

Enter value for venname: Akai

Enter value for productprice: 12000

old 1: insert into vendor_master values('&vencode','&venname',

&productprice)

new 1: insert into vendor_master values('v004','Akai',12000)

insert into vendor_master values('v004','Akai',12000)

ERROR at line 1:

ORA-02290: check constraint (USER06.SYS_C002809) violated

SQL> /

Enter value for vencode: v005

Enter value for venname: Aiwa

Enter value for productprice: 8500

old 1: insert into vendor_master values('&vencode','&venname',

&productprice)

new 1: insert into vendor_master values('v005','Aiwa',8500)

1 row created.

PRIMARY AND FOREIGN KEY CONSTRAINTS

SQL> create table order_master(orderno varchar(5) constraint order_prim

primary key,odate date,

vencode varchar(5),o_status char(1) not null,deldate date);

Table created.

SQL> desc order_master;

Name Null? Type

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

ORDERNO NOT NULL VARCHAR2(5)

ODATE DATE

VENCODE VARCHAR2(5)

O_STATUS NOT NULL CHAR(1)

DELDATE DATE

SQL> create table order_detail(orderno varchar2(5) ,itemcode varchar(3),

qtyorder number(3),

foreign key(orderno) references order_master on delete cascade on

update cascade);

Table created.

SQL> desc order_detail;

Name Null? Type

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

ORDERNO VARCHAR2(5)

ITEMCODE VARCHAR2(3)

QTYORDER NUMBER(3)

SQL> insert into order_master values('&oredrno','&odate','&vencode',

'&o_status','&deldate');

Enter value for oredrno: o001

Enter value for odate: 12-aug-2009

Enter value for vencode: v001

Enter value for o_status: p

Enter value for deldate: 12-sep-2009

old 1: insert into order_master values('&oredrno','&odate','&vencode',

'&o_status','&deldate')

new 1: insert into order_master values('o001','12-aug-2009','v001','p',

'12-sep-2009')

1 row created.

PRIMARY KEY CONSTRAINT VIOLATION

SQL> /

Enter value for oredrno: o001

Enter value for odate: 11-nov-2008

Enter value for vencode: v002

Enter value for o_status: p

Enter value for deldate: 14-feb-2010

old 1: insert into order_master values('&oredrno','&odate','&vencode',

'&o_status','&deldate')

new 1: insert into order_master values('o001','11-nov-2008','v002','p',

'14-feb-2010')

insert into order_master values('o001','11-nov-2008','v002','p',

'14-feb-2010')

ERROR at line 1:

ORA-00001: unique constraint (USER06.ORDER_PRIM) violated

SQL> /

Enter value for oredrno: o002

Enter value for odate: 13-jan-2007

Enter value for vencode: v002

Enter value for o_status: p

Enter value for deldate: 16-oct-2009

old 1: insert into order_master values('&oredrno','&odate','&vencode',

'&o_status','&deldate')

new 1: insert into order_master values('o002','13-jan-2007','v002','p',

'16-oct-2009')

1 row created.

SQL> select * from order_master;

ORDER ODATE VENCO O DELDATE

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

o001 11-NOV-08 voo2 p 14-FEB-10

o002 13-JAN-07 v002 p 16-OCT-09

FOREIGN KEY CONSTRAINT VIOLATION

SQL> insert into order_detail values('&orderno','&itemcode',&qtyorder);

Enter value for orderno: o003

Enter value for itemcode: i01

Enter value for qtyorder: 25

old 1: insert into order_detail values('&orderno','&itemcode',&qtyorder)

new 1: insert into order_detail values('o003','i01',25)

insert into order_detail values('o003','i01',25)

ERROR at line 1:

ORA-02291: integrity constraint (USER06.SYS_C002814) violated - parent key

not

found

SQL> /

Enter value for orderno: o001

Enter value for itemcode: i01

Enter value for qtyorder: 25

old 1: insert into order_detail values('&orderno','&itemcode',&qtyorder)

new 1: insert into order_detail values('o001','i01',25)

1 row created.

SQL> select * from order_detail;

ORDER ITE QTYORDER

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

o001 i01 25

SQL>delete from order_master where orderno='o001'

ERROR at line 1:

ORA-02292: integrity constraint (USER06.SYS_C002814) violated - child

record found

USING on delete cascade with foreign key constraint

SQL> drop table order_master;

Table dropped.

SQL> drop table order_detail;

Table dropped.

SQL> create table order_master(orderno varchar(5) constraint order_prim

primary key, odate date,

vencode varchar(5),o_status char(1) not null,deldate date);

SQL>create table order_detail(orderno varchar2(5) ,itemcode varchar(3),

qtyorder number(3),

foreign key(orderno) references order_master on delete cascade);

Table created.

SQL> insert into order_master values('&oredrno','&odate','&vencode',

'&o_status','&deldate');

Enter value for oredrno: o001

Enter value for odate: 12-jan-2007

Enter value for vencode: voo1

Enter value for o_status: p

Enter value for deldate: 13-jun-2008

old 1: insert into order_master values('&oredrno','&odate','&vencode',

'&o_status','&deldate')

new 1: insert into order_master values('o001','12-jan-2007','voo1','p',

'13-jun-2008')

1 row created.

SQL> /

Enter value for oredrno: o002

Enter value for odate: 24-sep-2008

Enter value for vencode: voo2

Enter value for o_status: p

Enter value for deldate: 16-jan-2010

old 1: insert into order_master values('&oredrno','&odate','&vencode',

'&o_status','&deldate')

new 1: insert into order_master values('o002','24-sep-2008','voo2','p',

'16-jan-2010')

1 row created.

SQL> select * from order_master;

ORDER ODATE VENCO O DELDATE

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

o001 12-JAN-07 voo1 p 13-JUN-08

o002 24-SEP-08 voo2 p 16-JAN-10

SQL> insert into order_detail values('&orderno','&itemcode',&qtyorder);

Enter value for orderno: o001

Enter value for itemcode: i01

Enter value for qtyorder: 25

old 1: insert into order_detail values('&orderno','&itemcode',&qtyorder)

new 1: insert into order_detail values('o001','i01',25)

1 row created.

SQL> /

Enter value for orderno: o002

Enter value for itemcode: i02

Enter value for qtyorder: 50

old 1: insert into order_detail values('&orderno','&itemcode',&qtyorder)

new 1: insert into order_detail values('o002','i02',50)

1 row created.

SQL> select * from order_detail;

ORDER ITE QTYORDER

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

o001 i01 25

o002 i02 50

SQL> delete from order_master where orderno='o001';

1 row deleted.

SQL> select * from order_master;

ORDER ODATE VENCO O DELDATE

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

o002 24-SEP-08 voo2 p 16-JAN-10

SQL> select * from order_detail;

ORDER ITE QTYORDER

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

o002 i02 50

[Note: Deletion of a row in parent is reflected in child table also. ]

RESULT:

Thus the Data Definition Language (DDL) and Data Control Language (DCL) commands in RDBMS were executed and verified.