TABLE CREATION:
CREATE TABLE employeerecord(employee_no number(5),employee_name varchar(15),department_no number(5),salary_no number(5),dateofjoin date,job varchar(20));
Table created.
SQL> desc employeerecord
Name Null? Type
----------------------------------------- -------- ----------------
EMPLOYEE_NO NUMBER(5)
EMPLOYEE_NAME VARCHAR2(15)
DEPARTMENT VARCHAR2(5)
SALARY_NO NUMBER(5)
DATEOFJOIN DATE
JOB VARCHAR2(20)
ALTER THE TABLE:
ADD:
SQL> ALTER TABLE employeerecord
ADD dateofbirth date;
Table altered.
Name Null? Type
----------------------------------------- -------- ----------------
EMPLOYEE_NO NUMBER(5)
EMPLOYEE_NAME VARCHAR2(15)
DEPARTMENT VARCHAR2(5)
SALARY_NO NUMBER(5)
DATEOFJOIN DATE
JOB VARCHAR2(20)
DATEOFBIRTH DATE
DROP THE COLUMN:
SQL> ALTER TABLE employeerecord
DROP COLUMN place;
Table altered.
SQL> desc employeerecord
Name Null? Type
----------------------------------------- -------- ----------------
EMPLOYEE_NO NUMBER(5)
EMPLOYEE_NAME VARCHAR2(30)
DEPARTMENT VARCHAR2(5)
SALARY_NO NUMBER(5)
DATEOFJOIN DATE
JOB VARCHAR2(50)
MODIFY:
SQL> ALTER TABLE employeerecord
modify employee_no int;
Table altered.
SQL> desc employeerecord
Name Null? Type
----------------------------------------- -------- -----------------
EMPLOYEE_NO NUMBER(38)
EMPLOYEE_NAME VARCHAR2(30)
DEPARTMENT VARCHAR2(5)
SALARY_NO NUMBER(5)
DATEOFJOIN DATE
DATEOFBIRTH DATE
JOB VARCHAR2(50)
TRUNCATE:
SQL> TRUNCATE TABLE employeerecord ;
Table truncated.
INTEGRITY CONSTRAINTS:
SQL>CREATE TABLE employee_record(employee_no number(5),employee_name varchar(15),department_no int NOT NULL PRIMARY KEY,job varchar(20));
Table created.
SQL> desc employee_record;
Name Null? Type
----------------------------------------- -------- ----------------------------
EMPLOYEE_NO NUMBER(5)
EMPLOYEE_NAME VARCHAR2(15)
DEPARTMENT_NO NOT NULL NUMBER(38)
JOB VARCHAR2(20)
SQL>CREATE TABLE department (department_no int NOT NULL PRIMARY KEY,department_name varchar(20),employee_no int NOT NULL,FOREIGN KEY (department_no) REFERENCES employee_record022(department_no) );
SQL> desc department
Name Null? Type
----------------------------------------- -------- --------------
DEPARTMENT_NO NOT NULL NUMBER(38)
DEPARTMENT_NAME VARCHAR2(20)
EMPLOYEE_NO NOT NULL NUMBER(38)
SQL> alter table employeerecord
add unique (employee_no);
Table altered.
SQL> alter table employeerecord
drop unique (employee_no);
Table altered.
SQL> ALTER TABLE employeerecord
ADD CONSTRAINT uc_employee UNIQUE (employee_no,e_name);
Table altered.
SQL> ALTER TABLE employeerecord
DROP CONSTRAINT uc_employee;
Table altered.
SQL> desc employeerecord;
Name Null? Type
----------------------------------------- -------- -----------------
EMPLOYEE_NO NUMBER(38)
EMPLOYEE_NAME VARCHAR2(30)
DEPARTMENT NOTNULL VARCHAR2(5)
JOB VARCHAR2(50)
SQL> alter table employeerecord
add constraint pk_employee PRIMARY KEY (employee_no);
SQL> desc employeerecord
Name Null? Type
----------------------------------------- -------- -----------------
EMPLOYEE_NO NOTNULL NUMBER(38)
EMPLOYEE_NAME VARCHAR2(30)
DEPARTMENT NOTNULL VARCHAR2(5)
JOB VARCHAR2(50)
SQL> alter table employeerecord
drop constraint pk_employee;
Table altered.
SQL> desc employeerecord;
Name Null? Type
----------------------------------------- -------- -----------------
EMPLOYEE_NO NUMBER(38)
EMPLOYEE_NAME VARCHAR2(30)
DEPARTMENT NOTNULL VARCHAR2(5)
JOB VARCHAR2(50)
SQL> alter table employeerecord
add constraint chk_employee CHECK (employee_no>0);
Table altered.
SQL> alter table employeerecord
drop constraint chk_employee;
Table altered.
SQL> drop table employeerecord;
Table dropped.
SQL> desc employeerecord
ERROR:
ORA-04043: object employeerecord does not exist
EmoticonEmoticon