SQL Commands : DDL , DML & TCL Commands
https://www.computersprofessor.com/2016/04/sql-commands-ddl-dml-tcl-commands.html
SQL COMMANDS
DDL
Commands.
DDL
stands for DATA DEFINITION LANGUAGE. The
DDL commands are used to create the objects /tables of the database. We can
also modify the table columns of the data types, size of the data & alter
the structure of the tables etc
DDL
COMMANDS are used to create and remove database objects.
They are,
- Create Table
- Alter Table - a) Add b) Modify
- TRUNCATE Table
- DROP Drop Table
CREATE COMMAND:
By
using create command you can create the database tables with the required no of
columns/attributes.
1 INSERTION {Appending the records}
2. DELETE
3. SELECT(QUERY)
4. UP DATE
Insert into<table- name> column-name values (‘&column-name’);
Update <table- name > set column- name= value where (condition);
The
roll back command gets all the rows (deleted records/ tables) back to the
table, in case commit is not applied. When you issues the command commit at the
SQL prompt the changes will become permanent in the table. It means, after the commit statement, you never recall back
the rows in the table.
SYNTAX:
Create table
(column-name datatype(size), column-name data type (size), …);
EXAMPLE:
Create table student(sno
number(4), stuname varchar(20), address varchar (30), phone number(10));
It creates a logical table in the memory.
Similarly you can create
an employee table as follows
Create table employee
(EmpNo number (6), Ename varchar(20), address varchar(30), salary number (8));
ALTER
COMMAND:
The alter command is used to alter the
existing fields of the table either to increase or decrease the width of a
specific column ,providing primary key constraints & to a specific column or even including
a new column
The alter command provides two more
commands such as add & modify. In
alter command add is used to add new columns or new fields to the existing
database
SYNTAX: ALTER TABLE <TABLE –NAME > ADD (column) ;
Example:
Alter
table student ADD(Fname varchar2(25));
You
can even add more than one attribute to the existing table as,
Alter
table student add(maths number (5), physics number (5), chemistry number (5));
MODIFY
COMMAND:
In alter command modify is also used as follows,
·
To
exchange the data type in the structure of the table
·
Changing
the existing data type size
For
eg., in the original database the data types & their sizes at the time of
creating the table is given as follows,
Create
table student (SNO number(5) , Sname
varchar2(20), Addr varchar(30));
Now
to modify the attributes of the existing table , we use modify command as,
Alter
table student modify (sno number(6), sname varchar2(25);
TRUNCATE:
The
truncate command removes all the records from the table simply leaving
structure of the table.
Syntax:
Truncate table <Table name> ;
Eg: Truncate table student;
DROP
COMMAND:
This is used to remove
/drop the table, views synonyms ,clusters & triggers. This command remove
the table along with its structure and as well as rows permanently from the
disk.
SYNTAX:
DROP TABLE <TABLE NAME>;;
Eg: DROP TABLE EMPLOYEE;
DML
COMMANDS: (DATA MANIPULATION LANGUAGE)
The commands are used to
manage the data base, the DML operations are
2. DELETE
3. SELECT(QUERY)
4. UP DATE
INSERT:
The
insert command can be used to enter the rows/ tuples into the Table.
There are four methods to insert
data into the tables.
1. Single
record inserting for all the columns
2. Single record inserting for required
columns
3. Multiple record inserting for required
columns
4. Multiple recorded inserting for required columns
SINGLE
RECORD INSERTIoN FOR ALL THE COLUMNS
SYNTAX: Insert into <table-name>VALUES (VALUES );
Eg: Insert into student values (915, ‘phanindra’);
SINGLE
RECORD INSERTION FOR REQUIRED COLUMNS:
It is used to insert the
records for the required columns.
SYNTAX:
into <table –name> column names values <values>;
Eg:
Insert into student s name, Address values(‘Murthy’, ‘5-3-31’;’TNK’);
MULTIPLE
RECORD INSERTION FOR ALL THE COLUMNS
SYNTAX:
Insert into< table-
name> values (‘& column name’; ‘& column name’;…..);
Note that, for numeric
data type, you can use only fcolumn name where as, for character type data its
‘& column name’, (with a single quote)
Eg: Insert into student
values( &S- NO; ‘|&-name’;
‘&Addr’; &phone)
Then SQL display a data
entry screen as
Enter S-No: 951
Enter S NO: MURTHY
Enter ADDRES:-12/1, Moti
Nagar
Enter phone number:
965034363
D) Multiple
record inserting for required columns:
Syntax:
Eg: Insert into student sname (‘&sname’);
Enter s-name: PHANINDRA
SQL> \
Enter s-name: vinay
SQL>
DELETE:
The delete command is used to delet a particular record based on
the given condition
Syntax: Delete from <
table- name > where (condition)
Eg: Delete from student where S_name = ‘Murthy’;
SELECT (QUERY)
Select
command is used to select the required records from the table. So the select
command is known as query, to select records form a particular table upon a
condition.
(i)
To select all records / rows of a table,
SQL> select * from student;
SYNTAX:
(I)To
select all records / rows of a table,
Select
* from <table- name);
that display all columns of all records
ii)
To select the specific rows /records from the table, you can give a where
condition.
SYNTAX:
Select * from < table-
name> where (condition);
Eg:
Select * from student where city=’RJY’;
Update:
The
update command is used the values for the existing record in the table based on
a given condition
Syntax:
Eg:
Update statement set name=’MURTHY’ where s-no=9151;
TCL:
Transaction
control language commands
The
TCL commands of oracle are,
*Grant *Revoke
*Save/Save
point *Roll back and *Commit
The
grant command allows/ privileges the users to access the data base. It means,
it gives the permission to the specified users to make changes in the table.
The
revoke command cancel the privileges (or) granting options for the users so that
they are not allowed to enter or manipulate data base table.
The
save point option allows the users to save the rows up to certain point.