UNIQUE KEY AND CHECK KEY CONSTRAINT IN SQL

SQL> create table demo_unique(a number unique,b number);

Table created.

SQL> desc demo_unique;

 Name                                      Null?    Type

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

 A                                                  NUMBER

 B                                                  NUMBER

SQL> insert into demo_unique values(4,2);

1 row created.

SQL> insert into demo_unique values(4,2);

insert into demo_unique values(4,2)

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.SYS_C003016) violated

SQL> insert into demo_unique values(6,2);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from demo_unique;

         A          B

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

         4          2

         6          2

SQL> alter table demo_unique

  2  add (c number check (c > 50));

Table altered.

SQL> insert into demo_unique values(9,5,6);

insert into demo_unique values(9,5,6)

*

ERROR at line 1:

ORA-02290: check constraint (SCOTT.SYS_C003017) violated

SQL> insert into demo_unique values(9,5,60);

1 row created.

SQL> commit;

Commit complete.

SQL> select * from demo_unique;

         A          B          C

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

         4          2

         6          2

         9          5         60

SQL> update demo_unique set c=65 where a=4;

1 row updated.

SQL> update demo_unique set c=75 where a=6;

1 row updated.

SQL> select * from demo_unique;

         A          B          C

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

         4          2         65

         6          2         75

         9          5         60

SQL> commit;

Commit complete.

SQL> create table demo (a number, constraint a_uni unique(a));

Table created.

SQL> alter table demo drop constraint a_uni;

Table altered.

SQL> drop table demo;

Table dropped.

SQL> create table demo (a number, constraint a_uni unique(a));

Table created.

SQL> insert into demo values(6);

1 row created.

SQL> insert into demo values(64);

1 row created.

SQL> commit;

Commit complete.

SQL> desc demo;

 Name                                      Null?    Type

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

 A                                                  NUMBER

SQL> select * from demo;

         A

----------

         6

        64

SQL> insert into demo values(64);

insert into demo values(64)

*

ERROR at line 1:

ORA-00001: unique constraint (SCOTT.A_UNI) violated

SQL> alter table demo disable constraint A_UNI;

Table altered.

SQL> insert into demo values(64);

1 row created.

SQL> alter table demo enable constraint a_uni;

alter table demo enable constraint a_uni

*

ERROR at line 1:

ORA-02299: cannot validate (SCOTT.A_UNI) - duplicate keys found

SQL> ed

Wrote file afiedt.buf

  1* alter table demo enable constraint A_UNI

  2  ;

alter table demo enable constraint A_UNI

*

ERROR at line 1:

ORA-02299: cannot validate (SCOTT.A_UNI) - duplicate keys found

SQL> select * from demo;

         A

----------

         6

        64

        64

SQL> delete from demo where a=64;

2 rows deleted.

SQL> alter table demo enable constraint A_UNI;

Table altered.

SQL> commit;

Commit complete.

INTEGRITY CONSTRAINT IN SQL

SQL> CREATE TABLE PERSONS18(PID INTEGER PRIMARY KEY, NAME CHAR(30), MOBILE NUMBER(10));

Table created.

SQL> DESC PERSONS18

 Name                                      Null?    Type

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

 PID                                       NOT NULL NUMBER(38)

 NAME                                               CHAR(30)

 MOBILE                                             NUMBER(10)

  1  CREATE TABLE ORDERS18(OID INTEGER PRIMARY KEY , ORDERNO NUMBER(7) NOT NULL,  PID INTEGER,

  2  CONSTRAINT FKPOR FOREIGN KEY (PID)

  3*  REFERENCES PERSONS18(PID))

  4  ;

Table created.

SQL> DESC PERSONS18

 Name                                      Null?    Type

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

 PID                                       NOT NULL NUMBER(38)

 NAME                                               CHAR(30)

 MOBILE                                             NUMBER(10)

SQL> DESC ORDERS18

 Name                                      Null?    Type

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

 OID                                       NOT NULL NUMBER(38)

 ORDERNO                                   NOT NULL NUMBER(7)

 PID                                                NUMBER(38)

SQL> INSERT INTO PERSONS18 VALUES(1,'SANKET',8745698547);

1 row created.

SQL> INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB);

Enter value for id: 2

Enter value for name: SANKET1

Enter value for mob: 8745965874

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(2,'SANKET1',8745965874)

1 row created.

SQL> /

Enter value for id: 3

Enter value for name: SANKET2

Enter value for mob: 8742154478

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(3,'SANKET2',8742154478)

1 row created.

SQL> /

Enter value for id: 4

Enter value for name: SANKET3

Enter value for mob: 9854784587

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(4,'SANKET3',9854784587)

1 row created.

SQL> /

Enter value for id: 5

Enter value for name: SANKET4

Enter value for mob: 7488745877

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(5,'SANKET4',7488745877)

1 row created.

SQL> ;

  1* INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM PERSONS18;

       PID NAME                               MOBILE

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

         1 SANKET                         8745698547

         2 SANKET1                         8745965874

         3 SANKET2                         8742154478

         4 SANKET3                       9854784587

         5 SANKET4                          7488745877

SQL> INSERT INTO ORDERS18 VALUES(1,101,1);

1 row created.

SQL> INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID);

Enter value for oid: 2

Enter value for ono: 102

Enter value for pid: 2

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(2,102,2)

1 row created.

SQL> /

Enter value for oid: 3

Enter value for ono: 103

Enter value for pid: 3

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(3,103,3)

1 row created.

SQL> /

Enter value for oid: 4

Enter value for ono: 104

Enter value for pid: 4

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(4,104,4)

1 row created.

SQL> /

Enter value for oid: 5

Enter value for ono: 105

Enter value for pid: 5

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(5,105,5)

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM PERSONS18;

       PID NAME                               MOBILE

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

         1 SANKET                         8745698547

         2 SANKET1                         8745965874

         3 SANKET2                         8742154478

         4 SANKET3                       9854784587

         5 SANKET4                          7488745877

SQL> SELECT * FROM ORDERS18;

       OID    ORDERNO        PID

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

         1        101          1

         2        102          2

         3        103          3

         4        104          4

         5        105          5

  1  SELECT P.NAME,

  2  P.MOBILE,

  3  O.ORDERNO

  4   FROM PERSONS18 P, ORDERS18 O

  5* WHERE P.PID=O.PID

  6  ;

NAME                               MOBILE    ORDERNO

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

SANKET                         8745698547        101

SANKET1                        8745965874        102

SANKET2                        8742154478        103

SANKET3                        9854784587        104

SANKET4                        7488745877        105

SQL> DELETE FROM ORDERS18 WHERE PID=1;

1 row deleted.

SQL> SELECT * FROM ORDERS18;

       OID    ORDERNO        PID

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

         2        102          2

         3        103          3

         4        104          4

         5        105          5

SQL> SELECT * FROM PERSONS18;

       PID NAME                               MOBILE

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

         1 SANKET                         8745698547

         2 SANKET1                        8745965874

         3 SANKET2                        8742154478

         4 SANKET3                        9854784587

         5 SANKET4                        7488745877

SQL> DELETE FROM PERSONS18 WHERE PID=2;

DELETE FROM PERSONS18 WHERE PID=2

*

ERROR at line 1:

ORA-02292: integrity constraint (SCOTT.FKPOR) violated - child record found

SQL> ED

Wrote file afiedt.buf

  1* DELETE FROM PERSONS18 WHERE PID=1

  2  ;

1 row deleted.

SQL> COMMIT;

Commit complete.

SQL>                                                                                               

;

  1* COMMIT

SQL> COMMIT;

Commit complete.

IMPLEMENT THE FUNCTIONS MAX(),MIN(),AVG(),COUNT() IN SQL

SQL>  create table product(Pid int primary key,pname varchar (30),Price int);

Table created.

SQL> insert into product values(1,'Monitor',6000);

1 row created.

SQL> insert into product values(3,'Keyboard',500);

1 row created.

SQL> insert into product values(4,'Printer',6500);

1 row created.

SQL> select * from product;

       PID PNAME                               PRICE

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

         1 Monitor                              6000

         3 Keyboard                            500

         4 Printer                                 6500

SQL> select max(price) from product;

MAX(PRICE)

----------

      6500

SQL> select min(price) from product;

MIN(PRICE)

----------

       500

SQL> select avg(price) from product;

AVG(PRICE)

----------

4333.33333

SQL> select count(price) from product;

COUNT(PRICE)

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

           3

SQL> commit;

Commit complete.

ALTER, UPDATE AND DELETE COMMANDS IN SQL

SQL>  create table product(Pid int primary key,pname varchar (30),Price int);

Table created.

SQL> insert into product values(1,'Monitor',6000);

1 row created.

SQL> insert into product values(2,'Mouse',200);

1 row created.

SQL> insert into product values(3,'Keyboard',500);

1 row created.

SQL> insert into product values(4,'Printer',6500);

1 row created.

SQL> alter table product

  2  add(Qty int);

Table altered.

SQL> select * from product;

       PID PNAME                               PRICE        QTY

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

         1 Monitor                              6000

         2 Mouse                                 200

         3 Keyboard                            500

         4 Printer                                 6500

SQL> update product set qty=2 where Pname='Mouse';

1 row updated.

SQL> select * from product;

       PID PNAME                               PRICE        QTY

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

         1 Monitor                              6000

         2 Mouse                                 200          2

         3 Keyboard                            500

         4 Printer                                 6500

SQL> delete from product where pid=2 and Qty=2;

1 row deleted.

SQL> select * from product;

       PID PNAME                               PRICE        QTY

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

         1 Monitor                              6000

         3 Keyboard                            500

         4 Printer                                6500

SQL> commit;

Commit complete.

CREATE TABLE USING FOREIGN KEY CONSTRAINT IN SQL

SQL> CREATE TABLE PERSONS18(PID INTEGER PRIMARY KEY, NAME CHAR(30), MOBILE NUMBER(10));

Table created.

SQL> DESC PERSONS18

 Name                                      Null?    Type

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

 PID                                       NOT NULL NUMBER(38)

 NAME                                               CHAR(30)

 MOBILE                                             NUMBER(10)

  1  CREATE TABLE ORDERS18(OID INTEGER PRIMARY KEY , ORDERNO NUMBER(7) NOT NULL,  PID INTEGER,

  2  CONSTRAINT FKPOR FOREIGN KEY (PID)

  3*  REFERENCES PERSONS18(PID))

  4  ;

Table created.

SQL> DESC PERSONS18

 Name                                      Null?    Type

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

 PID                                       NOT NULL NUMBER(38)

 NAME                                               CHAR(30)

 MOBILE                                             NUMBER(10)

SQL> DESC ORDERS18

 Name                                      Null?    Type

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

 OID                                       NOT NULL NUMBER(38)

 ORDERNO                                   NOT NULL NUMBER(7)

 PID                                                NUMBER(38)

SQL> INSERT INTO PERSONS18 VALUES(1,'SANKET',8745698547);

1 row created.

SQL> INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB);

Enter value for id: 2

Enter value for name: SANKET1

Enter value for mob: 8745965874

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(2,'SANKET1',8745965874)

1 row created.

SQL> /

Enter value for id: 3

Enter value for name: SANKET2

Enter value for mob: 8742154478

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(3,'SANKET2',8742154478)

1 row created.

SQL> /

Enter value for id: 4

Enter value for name: SANKET3

Enter value for mob: 9854784587

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(4,'SANKET3',9854784587)

1 row created.

SQL> /

Enter value for id: 5

Enter value for name: SANKET4

Enter value for mob: 7488745877

old   1: INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

new   1: INSERT INTO PERSONS18 VALUES(5,'SANKET4',7488745877)

1 row created.

SQL> ;

  1* INSERT INTO PERSONS18 VALUES(&ID,'&NAME',&MOB)

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM PERSONS18;

       PID NAME                               MOBILE

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

         1 SANKET                         8745698547

         2 SANKET1                        8745965874

         3 SANKET2                        8742154478

         4 SANKET3                        9854784587

         5 SANKET4                        7488745877

SQL> INSERT INTO ORDERS18 VALUES(1,101,1);

1 row created.

SQL> INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID);

Enter value for oid: 2

Enter value for ono: 102

Enter value for pid: 2

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(2,102,2)

1 row created.

SQL> /

Enter value for oid: 3

Enter value for ono: 103

Enter value for pid: 3

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(3,103,3)

1 row created.

SQL> /

Enter value for oid: 4

Enter value for ono: 104

Enter value for pid: 4

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(4,104,4)

1 row created.

SQL> /

Enter value for oid: 5

Enter value for ono: 105

Enter value for pid: 5

old   1: INSERT INTO ORDERS18 VALUES(&OID,&ONO,&PID)

new   1: INSERT INTO ORDERS18 VALUES(5,105,5)

1 row created.

SQL> COMMIT;

Commit complete.

SQL> SELECT * FROM PERSONS18;

       PID NAME                               MOBILE

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

         1 SANKET                         8745698547

         2 SANKET1                        8745965874

         3 SANKET2                        8742154478

         4 SANKET3                        9854784587

         5 SANKET4                        7488745877

SQL> SELECT * FROM ORDERS18;

       OID    ORDERNO        PID

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

         1        101          1

         2        102          2

         3        103          3

         4        104          4

         5        105          5