Example of DDL, DML, DCL and TCL Commands

Example of DDL, DML, DCL and TCL Commands

1) Data Definition Language (DDL)

- These SQL commands are used for creating, modifying, and dropping the structure of database objects.

create
alter
drop
truncate
rename

a) CREATE - To create objects in the database

ex:
create table mqm (PersonID int, LastName varchar(255), FirstName varchar(255), Address varchar(255), City varchar(255));

Note: You can't create same table with same name, if you trying to create it will throuh an error

ERROR at line 1:
ORA-00955: name is already used by an existing object


b) ALTER - alters the structure of the database

The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.

ex:
ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name DROP COLUMN column_name;

ALTER TABLE table_name MODIFY COLUMN column_name datatype;

c) DROP - delete objects from the database i,e. Indexes tables, and databases can easily be

deleted/removed with the DROP statement.

ex:
drop index index_name ON table_name

DROP TABLE table_name

DROP DATABASE database_name

d) TRUNCATE - remove all records from a table, including all spaces allocated for the records are

removed. What if we only want to delete the data inside the table, and not the table itself? Then, use the

TRUNCATE TABLE statement:

ex:
TRUNCATE TABLE table_name

f) RENAME - rename an object

ex:
ALTER TABLE table_name RENAME TO new_table_name

ALTER TABLE table_name RENAME COLUMN column 1 TO column 2;

2) Data Manipulation Language (DML)

- These SQL commands are used for storing, retrieving, modifying, and deleting data.

select
insert
update
delete

a) SELECT - retrieve data from the a database

ex:
SELECT column_name,column_name FROM table_name;
SELECT * FROM table_name;
SELECT CustomerName,City FROM Customers;

b) INSERT - insert data into a table

ex:
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country) VALUES

('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
insert into mqm (lastname, firstname) values ('&mam','&lns');

c) UPDATE - updates existing data within a table

ex:
UPDATE Customers SET ContactName='Alfred Schmidt', City='Hamburg';

d) DELETE - deletes all records from a table, the space for the records remain

ex:
DELETE FROM table_name;

or

DELETE * FROM table_name;

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';

3) Data Control Language (DCL)

- These SQL commands are used for providing security to database objects.

grant
revoke

a) GRANT - gives user's access privileges to database

ex:
grant dba to abc;
grant connect, resource to abc;

b) REVOKE - withdraw access privileges given with the GRANT command

ex:
revoke dba from abc;
revoke connect, resouce from abc;

4) Transaction Control Language (TCL)

- These SQL commands are used for managing changes affecting the data.

commit
savepoint
rollback

a) COMMIT - save work done

b) SAVEPOINT - identify a point in a transaction to which you can later roll back

c) ROLLBACK - restore database to original since the last COMMIT