SQL> connect hr
Enter password:
Connected.
DDL Statement are:=>Create,alter,drop,truncate,rename,comments
1.Create table emp:=>
SQL> create table emp
2 (
3 empno number,
4 ename varchar2(25),
5 sal number(8,2),
6 email varchar2(25),
7 birthdate date,
8 hiredate date
9 );
Table created.
SQL> alter table emp add (city varchar2(25));
Table altered.
SQL> alter table emp modify (email varchar2(35));
Table altered.
SQL> alter table emp rename column email to emailid;
Table altered.
SQL> create table dept(
2 deptid number(4),
3 dname varchar2(25)
4 );
Table created.
SQL> alter table emp add constraint emp_pk primary key(empno);
Table altered.
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER
ENAME VARCHAR2(25)
SAL NUMBER(8,2)
EMAILID VARCHAR2(35)
BIRTHDATE DATE
HIREDATE DATE
CITY VARCHAR2(25)
SQL> alter table dept add constraint deptname_chk check(dname in('IT','HR','Sale
s'));
Table altered.
1* alter table emp add (deptid number(4))
SQL> /
Table altered.
SQL> alter table dept add constraint dept_pk primary key(deptid);
Table altered.
SQL> alter table emp add constraint emp_fk foreign key(deptid) references dept(d
eptid);
Table altered.
SQL> insert into dept values(10,'IT');
1 row created.
SQL> insert into dept values(10,'HR');
insert into dept values(10,'HR')
*
ERROR at line 1:
ORA-00001: unique constraint (HR.DEPT_PK) violated
SQL> insert into dept values(20,'HR');
1 row created.
SQL> insert into dept values(30,'Finance');
insert into dept values(30,'Finance')
*
ERROR at line 1:
ORA-02290: check constraint (HR.DEPTNAME_CHK) violated
SQL> insert into dept values(30,'Sales');
1 row created.
SQL> commit;
Commit complete.
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTID NOT NULL NUMBER(4)
DNAME VARCHAR2(25)
SQL> select * from dept;
DEPTID DNAME
---------- -------------------------
10 IT
20 HR
30 Sales
SQL> --Add check constraints to check for special condition to check employee's
age is not less than 18yrs of age;
SQL> alter table emp add constraint age_chk check(hiredate>=birthdate+365*18);
Table altered.
SQL> desc emp
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NOT NULL NUMBER
ENAME VARCHAR2(25)
SAL NUMBER(8,2)
EMAILID VARCHAR2(35)
BIRTHDATE DATE
HIREDATE DATE
CITY VARCHAR2(25)
DEPTID NUMBER(4)
SQL> insert into emp values(101,'Arijit',20000,'arijit@gmail.com',to_date('10-08-1982','dd-mm-yyyy'),sysdate,'Mumbai',10);
1 row created.
SQL> insert into emp values(102,'Aravv',20000,'aravv@gmail.com',to_date('10-08-2
011','dd-mm-yyyy'),sysdate,'Mumbai',20);
insert into emp values(102,'Aravv',20000,'aravv@gmail.com',to_date('10-08-2011',
'dd-mm-yyyy'),sysdate,'Mumbai',20)
*
ERROR at line 1:
ORA-02290: check constraint (HR.AGE_CHK) violated
SQL> insert into emp values(102,'Aravv',20000,'aravv@gmail.com',to_date('10-08-1
982','dd-mm-yyyy'),sysdate,'Mumbai',20);
1 row created.
SQL> commit;
Commit complete.
To be Continued.....Abhi Picture baki hai mera dost...
No comments:
Post a Comment