DML Statements
Add a new row to the table:
INSERT INTOdepartments
(location, dname, department_id)
VALUES
(‘Beijing’, ‘Public Relations’, 50);
INSERT INTO employees
VALUES
(9013, USER, 'MANAGER', 7788, ADD_MONTHS (SYSDATE, -36), DEFAULT, '', NULL);
INSERT INTO employees_copy
(SELECT empno, ename, job, mgr, hiredate --?
FROM employees);
Modifies existing rows in the table:
UPDATE departments
SET dname = 'Engineering',
location = DEFAULT
WHERE department_id = 50;
UPDATE employees
SET ename = (SELECT ename FROM emp WHERE empno = 7844),
mgr = DEFAULT
WHERE empno = 7944;
Removes existing rows from the table
DELETE FROMdepartments/ DELETE FROM departments
WHERE department_id = 50;
Conditionally inserts / updates data in a table
MERGE INTOdepartments
WHERE department_id = 50;
DDL Statements
Creates a table
CREATE TABLEdepartments_copy
(department_id NUMBER(2),
dname VARCHAR2(14) DEFAULT‘Administration’);
Modifies table structures
ALTER TABLEdepartments_copy
ADD / MODIFY/ DROP COLUMN / SETUNUSED ...
...
Adding a Column
ALTER TABLEdepartments_copy
ADD (dcategory VARCHAR2(9) DEFAULT ‘General’,
mgr_head NUMBER(5) DEFAULT12345);
Modifying a Column
ALTER TABLEdepartments_copy
MODIFY (dcategory VARCHAR2(30) DEFAULT ‘Private’,
mgr_head VARCHAR2(10) DEFAULT‘None’);
Renaming a Column
ALTER TABLEdepartments_copy
RENAME COLUMN (dcategory TO dept_cat);
Dropping a Column[s]
ALTER TABLEdepartments_copy
DROP COLUMN dcategory;/ DROPCOLUMN (mgr_head [, dcategory, ...]);
SET UNUSED Option
ALTER TABLEdepartments_copy
SET UNUSED (dcategory, mgr_head);
ALTER TABLEdepartments_copy
DROP UNUSED COLUMNS;
Removes the rows and table structure
DROP TABLEdepartments_copy;
Changes the name of a table
RENAME [TABLE] departments_copy TO detail_dept;
OR
ALTER TABLEdepartments_copy
RENAME TOdetail_dept;
Removes all rows from a table
TRUNCATE TABLE detail_dept; --Releasesthe storage space, cannot rollback.
OR
DELETE detail_dept; --Do not release the storage space, can-be rollback.
Explicit Locking for all SQL statements except SELECT
SELECT * FROM detail_dept
FOR UPDATENOWAIT;
FOR UPDATENOWAIT;
Add Comments to a column/ table/ view (Delete Comments by setting it to empty string i.e. ‘’.)
COMMENT ON TABLE departments_copy / COMMENT ON COLUMNdepartments_copy.dname
IS‘Copy of Departments table’; IS‘This column has Department Names’;
IS‘Copy of Departments table’; IS‘This column has Department Names’;
USER_TAB_COMMENTS USER_COL_COMMENTS
CONSTRAINTS (using CREATE TABLE)
Column-Level only
NOT NULL Constraints
CREATE TABLE employees
(employee_id NUMBER(2),
last_name VARCHAR2(25) NOT NULL, --Oracle Server Named Cons.
salary NUMBER(8,2),
commission_pct NUMBER(2,2),
hire_date DATE CONSTRAINT emp_hire_date_nn
NOT NULL --User Named Cons.
);
Column-Level
Table-Level
Table-Level
UNIQUE Constraints
CREATE TABLE employees
(employee_id NUMBER(2) UNIQUE, --Oracle Server Named Cons.
last_name VARCHAR2(25) NOT NULL,
nic_no NUMBER(14),
passport_no NUMBER(7),
cell_no NUMBER(11) CONSTRAINTemp_cell_no_uk
UNIQUE, --User Named Cons.
UNIQUE(nic_no),
CONSTRAINTemp_passport_no_uk UNIQUE (passport_no)
);
PRIMARY KEY Constraints
CREATE TABLE employees
(CONSTRAINT emp_passport_no_pk PRIMARY KEY (passport_no),
employee_id NUMBER(2) PRIMARY KEY, --Oracle Server Named Cons.
last_name VARCHAR2(25) NOT NULL DISABLE,
nic_no NUMBER(14),
passport_no NUMBER(7),
sal NUMBER(5) CHECK(sal>5000) DISABLE,
cell_no NUMBER(11) CONSTRAINT emp_cell_no_pk
PRIMARY KEY, --User Named Cons.
PRIMARY KEY (nic_no)
);
FOREIGN KEY Constraints
CREATE TABLE employees
(manager_id NUMBER(2) REFERENCESemployees(employee_id),
--Oracle Server Named Cons.
employee_id NUMBER(2) PRIMARY KEY,
department_id,
location_id CONSTRAINT emp_location_id_fk
REFERENCESlocation,
--User Named Cons.
last_name VARCHAR2(25) NOT NULL,
test_id NUMBER(2),
CONSTRAINTemp_department_id_fk
FOREIGN KEY (department_id) REFERENCESdepartment
ON DELETE CASCADE / ON DELETE SET NULL
ON DELETE CASCADE / ON DELETE SET NULL
FOREIGN KEY (test_id) REFERENCESemployees(employee_id),
);
CHECK Constraints
CREATE TABLE employees
(employee_id NUMBER(2), CHECK (employee_id > 5000),
--Oracle Server Named Cons.
salary NUMBER(8,2) CONSTRAINTemp_salary_min_chk
CHECK(salary > 0)
DISABLE --User Named Cons.
CHECK(employee_id > 5000),
CONSTRAINTemp_salary_min_chk CHECK(salary > 0)
);
CONSTRAINTS (using ALTER TABLE)
Adding/ Dropping/ Disabling/ Enabling Constraints (Using Constraint Name)
ALTER TABLE employees
ADD/ DROP/ DISABLE/ ENABLE CONSTRAINTSYS_C003553;
Adding Constraints (Disabled Explicitly) – Table-Level only except NOT NULL
ALTER TABLE employees
MODIFY (manager_id NOT NULL
DISABLE); --Oracle Server Named Cons.
DISABLE); --Oracle Server Named Cons.
ALTER TABLE employees
MODIFY (manager_id CONSTRAINTemp_manager_id_nn
NOT NULL
NOT NULL
DISABLE); --User Named Cons.
ALTER TABLE employees
ADD UNIQUE(manager_id)
DISABLE; --Oracle Server Named Cons.
ALTER TABLE employees
ADD CONSTRAINT emp_manager_id_uk
UNIQUE(manager_id); --User Named Cons.
ALTER TABLE employees
ADD PRIMARY KEY(manager_id)
DISABLE; --Oracle Server Named Cons.
ALTER TABLE employees
ADD CONSTRAINT emp_manager_id_pk
PRIMARY KEY(manager_id)
DISABLE; --User Named Cons.
ALTER TABLE employees
ADD FOREIGN KEY(manager_id) REFERENCES employees
DISABLE; --Oracle Server Named Cons.
ALTER TABLE employees
ADD CONSTRAINT emp_manager_id_fk
FOREIGN KEY(manager_id) REFERENCESemployees
DISABLE; --User Named Cons.
ALTER TABLE employees
ADD ( CHECK (duty_pct < 10)); --Oracle Server Named Cons.
ALTER TABLE employees
ADD ( CONSTRAINT emp_duty_chk
CHECK (duty_pct < 10));
--User Named Cons.
ALTER TABLE employees --Add a new column with Cons.
ADD (duty_pct NUMBER(2,2) CHECK (duty_pct < 10)
NOT NULL, --Oracle Server Named Cons.
department_id, CONSTRAINT emp_department_id_fk
FOREIGN KEY(department_id) REFERENCESdepartments
DISABLE); --User Named Cons.
Dropping/ Disabling Constraints
ALTER TABLE employees
DROP PRIMARY KEY/ UNIQUE (email) [CASCADE];/
DISABLE PRIMARY KEY/ UNIQUE (email) [CASCADE];
Dropping Table/ Column having Constraints defined
DROP TABLE employees/ DROP COLUMN employee_id
CASCADE CONSTRAINTS; --To Drop an object other than Cons., tell Oracle to effect on its Cons. also,
if there any exist, using CONSTRAINTS keyword after CASCADE.
Enabling Constraints
ALTER TABLE employees
ENABLE PRIMARY KEY/ UNIQUE (email);
Renaming Constraints
ALTER TABLE employees
RENAME CONSTRAINTcust_fname_nn TOcust_firstname_nn;
CONSTRAINTS (using CREATE TABLE)
PRIMARY KEY Constraints
CREATE TABLE employee
(CONSTRAINT emp_employee_id_pk PRIMARY KEY (employee_id),
NOT NULL(salary)
CHECK (salary>=5000) DISABLE,
UNIQUE (email) DISABLE,
UNIQUE (email) DISABLE,
employee_id NUMBER(4),
manager_id REFERENCES employee
ON DELETE CASCADE,
last_name VARCHAR2(25) DEFAULT ‘Public User’
NOT NULL
DISABLE,
email VARCHAR2(25) DEFAULT ‘user@kingtest.com.uk’, --???
department_id, FOREIGN KEY (department_id)
REFERENCESdepartments
ON DELETE SET NULL,
salary NUMBER(5)
);
Other Database Objects
INDEXES
Creating an Index
CREATE INDEX emp_sal_01 --Non-Unique Index
ON emp (sal);
CREATE UNIQUE INDEXemp_empno_01 --Unique Index
ON emp (empno);
CREATE UNIQUE INDEXemp_empno_ename_indx_01 --Composite Index
ON emp (empno, ename);
CREATE INDEX emp_ename_reverse_indx --Reverse Key Index
ON emp (ename) REVERSE;
CREATE BITMAP INDEXemp_deptno_indx_01 --Bitmap Index
ON emp (deptno);
CREATE INDEX ixd_emp_01 --Function-Based Index
ON emp (sal * 1.08);
Renaming an Index
ALTER INDEX upper_ix
RENAME TO upper_name_ix;
Dropping an Index
DROP INDEX upper_ix;
SYNONYMS
Creating a Synonym
CREATE SYNONYM my_private_synonym --Private Synonym by Default
FOR emp;
CREATE PUBLIC SYNONYM like_this --Public Synonym
FOR scott.emp;
Dropping a Synonym
DROP SYNONYM emp_sal;
