SQL NOTES

porn sites

To create a table
==========
SQL> create table thu1(id number(3));

Table created.

To view the structure of table
==================

SQL> desc thu1;

Name Null? Type
------------------------------- -------- ----
ID NUMBER(3)

To add unique constraint after the table creation
==============================
SQL> alter table thu1 add constraint idun unique(id);

Table altered.

SQL> insert into thu1 values(&id);
Enter value for id: 1
old 1: insert into thu1 values(&id)
new 1: insert into thu1 values(1)

1 row created.

SQL> /
Enter value for id: 2
old 1: insert into thu1 values(&id)
new 1: insert into thu1 values(2)

1 row created.


SQL> /
Enter value for id: 3
old 1: insert into thu1 values(&id)
new 1: insert into thu1 values(3)

1 row created.

SQL> select * from thu1;

ID
---------
1
2
3

To drop the constraint
==============

SQL> alter table thu1 drop constraint idun;

Table altered.

SQL> insert into thu1 values(&id);
Enter value for id: 2
old 1: insert into thu1 values(&id)
new 1: insert into thu1 values(2)

1 row created.

SQL> /
Enter value for id: 3
old 1: insert into thu1 values(&id)
new 1: insert into thu1 values(3)

1 row created.

SQL> /
Enter value for id: 4
old 1: insert into thu1 values(&id)
new 1: insert into thu1 values(4)

1 row created.

SQL> /
Enter value for id: 1
old 1: insert into thu1 values(&id)
new 1: insert into thu1 values(1)

1 row created.

SQL> select * from thu1;

ID
---------
1
2
3
4
2
3
4
1

8 rows selected.

To give check constraint
===============

SQL> create table rno(id number(3), check (id > 100 and id <> insert into rno values(&id);
Enter value for id: 101
old 1: insert into rno values(&id)
new 1: insert into rno values(101)

1 row created.

SQL> /
Enter value for id: 102
old 1: insert into rno values(&id)
new 1: insert into rno values(102)

1 row created.

SQL> /
Enter value for id: 103
old 1: insert into rno values(&id)
new 1: insert into rno values(103)
1 row created.

SQL> /
Enter value for id: 104
old 1: insert into rno values(&id)
new 1: insert into rno values(104)

1 row created.

SQL> alter table rno add(sex char(1), check (sex in( 'm' , 'f')));

Table altered.

SQL> truncate table rno;

Table truncated.

SQL> insert into rno values(&id,'&sex');
Enter value for id: 101
Enter value for sex: m
old 1: insert into rno values(&id,'&sex')
new 1: insert into rno values(101,'m')

1 row created.

SQL> /
Enter value for id: 102
Enter value for sex: f
old 1: insert into rno values(&id,'&sex')
new 1: insert into rno values(102,'f')

1 row created.

SQL> /
Enter value for id: 104
Enter value for sex: f
old 1: insert into rno values(&id,'&sex')
new 1: insert into rno values(104,'f')

1 row created.

SQL> /
Enter value for id: 105
Enter value for sex: m
old 1: insert into rno values(&id,'&sex')
new 1: insert into rno values(105,'m')

1 row created.

SQL> /
Enter value for id: 121
Enter value for sex: m
old 1: insert into rno values(&id,'&sex')
new 1: insert into rno values(121,'m')
insert into rno values(121,'m')
*
ERROR at line 1:
ORA-02290: check constraint (KARTHICK.SYS_C00616) violated


SQL> /
Enter value for id: 115
Enter value for sex: j
old 1: insert into rno values(&id,'&sex')
new 1: insert into rno values(115,'j')
insert into rno values(115,'j')
*
ERROR at line 1:
ORA-02290: check constraint (KARTHICK.SYS_C00617) violated

SQL> spool off;

xxx sites