DDL COMMANDS


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.

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)
 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


Previous
Next Post »

If you still didn't find what you're looking for, you can search this website below: