SQL COMMANDS
DDL : Data Definition Language
DML : Data Manipulation Language
DDL
Data Definition Language (DDL) statements are used to define the database structure
or schema. Some examples:
CREATE - to create objects in the database
ALTER - alters the structure of the database
DROP - delete objects from the database
TRUNCATE - remove all records from a table, including all spaces allocated for the
records are removed.
RENAME - rename an object
CREATE
Sample
Create table student (id number,name varchar2(10), age number)
ALTER
ADDING NEW COLUMN
ALTER TABLE emp ADD age NUMBER;
ALTER TABLE emp ADD (age NUMBER,gender varchar2(10));
MODIFYING COLUMN
ALTER TABLE emp MODIFY hiredate varchar2(15);
ALTER TABLE emp MODIFY (empno varchar2(4),sal number(6));
DROP COLUMN
ALTER TABLE emp DROP hiredate ;
RENAME COLUMN
ALTER TABLE emp RENAME COLUMN hiredate TO startdate;
RENAME
ALTER TABLE EMP RENAME TO EMPLOYEES;
This will rename the EMP table to EMPLOYEES.
TRUNCATE
Truncate deletes all rows in a table.
ALTER TABLE EMP RENAME TO EMPLOYEES;
This will rename the EMP table to EMPLOYEES.
Difference between DELETE and TRUNCATE
SQL> DELETE FROM EMP; ALL ROWS HAVE BEEN DELETED
SQL> ROLLBACK ; DELETED ROWS HAVE BEEN RESTORED
SQL> TRUNCATE TABLE EMP; ALL ROWS HAVE BEEN DELETED
SQL> ROLLBACK ; DELETED ROWS CAN NOT BE RESTORED.
TRUNCATE DELETES ROWS, YOU CAN NOT RESTORE THE DELETED ROWS
DML
Data Manipulation Language (DML) statements are used for managing data within
schema objects. Some examples:
SELECT - retrieve data from the a database
INSERT - insert data into a table
UPDATE - updates existing data within a table
DELETE - deletes all records from a table, the space for the records remain
TASKS ( You will create your tables )
Practice Exercise #1:
Based on the departments table below, rename the departments table to depts.
CREATE TABLE departments
( department_id Number(10), department_name varchar2(50) );
Practice Exercise #2:
Based on the employees table below, add a column called salary that is a number(6) datatype.
CREATE TABLE employees ( employee_number Number(10), employee_name Varchar2(50),
department_id
number(10) );
Practice Exercise #3:
Based on the customers table below, add two columns - one column called contact_name that is a
varchar2(50) datatype and one column called last_contacted that is a date datatype.
CREATE TABLE customers ( customer_id Number(10), customer_name Varchar2(50), address
varchar2(50), city varchar2(50), state varchar2(25),
zip_code varchar2(10) );
Practice Exercise #4:
Based on the employees table below, change the employee_name column to a varchar2(75)
datatype.
CREATE TABLE employees ( employee_number Number(10), employee_name Varchar2(50),
department_id number(10) );
Practice Exercise #5:
Based on the customers table below, change the state column to a varchar2(2) datatype.
CREATE TABLE customers ( customer_id Number(10), customer_name varchar2(50), address
varchar2(50), city varchar2(50), state varchar2(25),
zip_code varchar2(10) );
Practice Exercise #6:
Based on the employees table below, drop the salary column.
CREATE TABLE employees ( employee_number Number(10), employee_name Varchar2(50),
department_id number(10), salary number(6));
Practice Exercise #7:
Based on the departments table below, rename the department_name column to dept_name.
CREATE TABLE departments ( department_id Number(10), department_name Varchar2(50));
