Create table department and employee
CREATE TABLE TABLENAME(COLUMN1
DATATYPE(SIZE),
CLUMN2 DATATYPE(SIZE)….);
employee (empno integer,ename charcter,job chracter,hiredate
date,salary number,deptnumber integer)
department (deptnumber integer, deptname character, location
character)
create table dept(
deptno integer,
dname varchar2(14),
loc varchar2(13));
create table emp(
empno number(4),
ename varchar2(10),
job varchar2(9),
hiredate date,
sal number(7,2),
deptno integer);
Describe the structure of the tables;
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(38)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)22-2-1981',
SQL> desc emp;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPNO NUMBER(4)
ENAME VARCHAR2(10)
JOB VARCHAR2(9)
HIREDATE DATE
SAL NUMBER(7,2)
DEPTNO NUMBER(38)
select values from tables;
SQL> select * from dept;
no rows selected
SQL> select * from emp;
no rows selected
Display all the tables in your tablespace
SQL>SELECT * FROM TAB;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPT TABLE
EMP TABLE
Add column state to department table
SQL> alter table dept add state varchar2(10);
Table altered.
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(38)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
STATE VARCHAR2(10)
Modify the data type of the column deptno
SQL> alter table dept modify deptno number(4,2);
Table altered.
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER(4,2)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
STATE VARCHAR2(10)
Modify the size of column loc from 13 to 20
SQL> alter table dept modify loc varchar2 (20);
Table altered.
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER (4,2)
DNAME VARCHAR2 (14)
LOC VARCHAR2 (20)
STATE VARCHAR2 (10)
Rename the column loc to location in dept table
SQL> alter table dept rename column loc to location;
Table altered.
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NUMBER (4, 2)
DNAME VARCHAR2 (14)
LOCATION VARCHAR2 (20)
STATE VARCHAR2 (10)
Rename the table dept to department
SQL> rename dept to department;
Table renamed.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
DEPARTMENT TABLE
EMP TABLE
Remove the column state from department table
SQL> alter table department drop column state;
Table altered.
SQL> desc department;
Name Null? Type
---------------------------- -------- ----------------------------
DEPTNO NUMBER(4,2)
DNAME VARCHAR2(14)
LOCATION VARCHAR2(20)
Drop the tables emp and department
SQL> drop table emp;
Table dropped.
SQL> drop table department;
Table dropped.
SQL> select * from tab;
TNAME TABTYPE CLUSTERID
------------------------------ ------- ----------
reBIN$jccR2MF9EubgUAB/AQAZXQ==$0 TABLE
BIN$jccR2MGAEubgUAB/AQAZXQ==$0 TABLE
Clear recycle bin
SQL> purge recyclebin;
Recyclebin purged.
SQL> select * from tab;
no rows selected
Create tables employee and department;
create table dept(
deptno integer,
dname varchar2(14),
loc varchar2(13));
create table emp(
empno integer,
ename varchar2(10),
job varchar2(9),
hiredate date,
sal number(7,2),
deptno integer);
Add primary key constraint to column deptno in dept table;
alter table dept add constraint pk_dno primary key(deptno);
SQL> desc dept;
Name Null? Type
----------------------------------------- -------- ----------------------------
DEPTNO NOT NULL NUMBER(38)
DNAME VARCHAR2(14)
LOC VARCHAR2(13)
Add foregin key constraint to column deptno in emp table to deptno column
in dept table;
alter table emp add constraint fk_dno foreign key(deptno) references dept(deptno);
Table altered
Add primary key constraint to column empno in emp table;
SQL> alter table emp add constraint pk_empno primary key(empno);
Table altered.
View the constraint name and type in table
SQL> select constraint_name,constraint_type from user_constraints where table_name='EMP';
CONSTRAINT_NAME C
------------------------------ -
FK_DNO R
PK_EMPNO P
Drop the foreign key constraint in emp table
SQL> alter table emp drop constraint FK_DNO;
Table altered.
Add foregin key constraint to column deptno in emp table to deptno column
in dept table;
alter table emp add constraint fk_dno foreign key(deptno) references dept(deptno);
Insert values to table emp & dept
INSERT INTO TABLE VALUES(VALUE1,VALUE2,VALUE3….);
insert into DEPT values(10, 'ACCOUNTING', 'NEW YORK');
insert into dept values(20, 'RESEARCH', 'DALLAS');
insert into dept values(30, 'SALES', 'CHICAGO');
SQL> select * from dept;
DEPTNO DNAME LOC
---------- -------------- -------------
10 ACCOUNTING NEW YORK
20 RESEARCH DALLAS
30 SALES CHICAGO
Primary key violation
insert into DEPT values(10, 'RESEARCH', 'WDC');
SQL> insert into DEPT values(10, 'RESEARCH', 'WDC');
insert into DEPT values(10, 'RESEARCH', 'WDC')
*
ERROR at line 1:
ORA-00001: unique constraint (IGNOU030.PK_DNO) violated
insert into emp
values(7698, 'BLAKE', 'MANAGER',to_date('1-5-1981','dd-mm-yyyy'), 2850, 30);
insert into emp
values(7782, 'CLARK', 'MANAGER',to_date('9-6-1981','dd-mm-yyyy'),2450,10);
insert into emp
values(7566, 'JONES', 'MANAGER',to_date('2-4-1981','dd-mm-yyyy'), 2975,20);
insert into emp
values(7902, 'FORD', 'ANALYST',to_date('3-12-1981','dd-mm-yyyy'),3000,20);
insert into emp
values(7369, 'SMITH', 'CLERK', to_date('17-12-1980','dd-mm-yyyy'),800,20);
insert into emp
values(7521, 'WARD', 'SALESMAN','22-feb-1981',1250, 30 );
insert into emp
values(7844, 'TURNER', 'SALESMAN',to_date('8-9-1981','dd-mm-yyyy'),1500,30);
insert into emp
values( 7876, 'ADAMS', 'CLERK',to_date('13-JUL-1987', 'dd-mm-yyyy'),1100,20);
insert into emp
values(7900, 'JAMES', 'CLERK',to_date('3-12-1981','dd-mm-yyyy'), 950,30);
SQL> select * from emp;
EMPNO ENAME JOB HIREDATE SAL DEPTNO
---------- ---------- --------- --------- ---------- ----------
7698 BLAKE MANAGER 01-MAY-81 2850 30
7782 CLARK MANAGER 09-JUN-81 2450 10
7566 JONES MANAGER 02-APR-81 2975 20
7902 FORD ANALYST 03-DEC-81 3000 20
7369 SMITH CLERK 17-DEC-80 800 20
7521 WARD SALESMAN 22-FEB-81 1250 30
7844 TURNER SALESMAN 08-SEP-81 1500 30
7900 JAMES CLERK 03-DEC-81 950 30
7876 ADAMS CLERK 13-JUL-87 1100 20
9 rows selected.
Foregin key violation
SQL> insert into emp
values(7900, 'JAM', 'CLERK',to_date('3-12-1981','dd-mm-yyyy'), 950,40);
insert into emp
*
ERROR at line 1:
ORA-00001: unique constraint (IGNOU030.PK_EMPNO) violated
Update the data in the table
UPDATE TABLENAME SET COLUMNNAME=NEW VALUE WHERE
CONDITION;
uSQL> update emp set sal=1959 where empno=7900;
1 row updated.
SQL> select * from emp;
EMPNO ENAME JOB HIREDATE SAL DEPTNO
---------- ---------- --------- --------- ---------- ----------
7698 BLAKE MANAGER 01-MAY-81 2850 30
7782 CLARK MANAGER 09-JUN-81 2450 10
7566 JONES MANAGER 02-APR-81 2975 20
7902 FORD ANALYST 03-DEC-81 3000 20
7369 SMITH CLERK 17-DEC-80 800 20
7521 WARD SALESMAN 22-FEB-81 1250 30
7844 TURNER SALESMAN 08-SEP-81 1500 30
7900 JAMES CLERK 03-DEC-81 1959 30
7876 ADAMS CLERK 13-JUL-87 1100 20
Deleting row from the table
SQL> delete from emp where empno=7876;
1 row deleted.
SQL> select * from emp;
EMPNO ENAME JOB HIREDATE SAL DEPTNO
---------- ---------- --------- --------- ---------- ----------
7698 BLAKE MANAGER 01-MAY-81 2850 30
7782 CLARK MANAGER 09-JUN-81 2450 10
7566 JONES MANAGER 02-APR-81 2975 20
7902 FORD ANALYST 03-DEC-81 3000 20
7369 SMITH CLERK 17-DEC-80 800 20
7521 WARD SALESMAN 22-FEB-81 1250 30
7844 TURNER SALESMAN 08-SEP-81 1500 30
7900 JAMES CLERK 03-DEC-81 1959 30
8 rows selected.
Deleting all the rows from the tables;
SQL> delete from emp;
8 rows deleted.
SQL> select * from emp;
no rows selected
COMMIT command is used to permanently save any transaction into the
database.
SQL> commit;
Commit complete.
