STRUCTURED QUERY LANGUAGE
SQL is a non procedural database language used for storing and retrieving data from the
database.
SQL was invented by IBM in early 1970’s.
SQL supports the following categories of commands to communicate with the database
Languages Commands
DDL (Data Definition Language) CREATE, ALTER, DROP, RENAME, TRUNCATE
DML (Data Manipulation Language) INSERT, DELETE, UPDATE
DCL (Data Control Language) GRANT, REVOKE
TCL (Transaction Control Language) COMMIT, ROLLBACK, SAVEPOINT
Anbarasi M. AP(Sr),SCOPE
Oracle Data types
• Char (length) – Fixed length character
– E.g char(10)
• Varchar2(length) – Variable length character
– E.g varchar2(10)
• Number – Integer of any
– e.g number(3) – only 3 digits
– E.g number(4,1) – float of max 1 decimal place
• Date
– E.g 01-jan-06
Anbarasi M. AP(Sr),SCOPE
• DDL commands
are used for table definition. They are used to
create, remove and alter the structure of database
objects.
• CREATE
• ALTER
• TRUNCATE
• DROP
• RENAME
Anbarasi M. AP(Sr),SCOPE
DDL (DATA DEFINITION LANGUAGE)
• CREATE – Used to create the table
Syntax
CREATE TABLE < tablename> (
<column name1 > < datatype>,
<column name 2> < datatype>,
<column name 3> < datatype>…..
<column name 1000> <datatype>
);
Anbarasi M. AP(Sr),SCOPE
E.g.
CREATE TABLE emp(
emp_id NUMBER(6),
ename VARCHAR2(20),
ph_no VARCHAR2(20),
job_id VARCHAR2(10),
salary NUMBER(8,2)) ;
Anbarasi M. AP(Sr),SCOPE
ALTER – Altering the table
• Add - Adding new columns
– ALTER TABLE <tablename> add ( <column name > <
datatype>);
– E.g alter table emp add( dob date);
• Modify - Modify the data type or increase / decrease the column
width
– ALTER TABLE <tablename> modify (<column name > <
newdatatype>);
– E.g alter table emp modify(job_id varchar2(20));
Anbarasi M. AP(Sr),SCSE
ALTER con..
• To Drop a column
– ALTER TABLE <tablename> drop column < column
name>;
– E.g alter table emp drop column job_id
• To Rename a column
– ALTER TABLE <tablename> rename column <old
column name> to <new column name>
– E.g alter table emp rename column dob to dateofbirth;
Anbarasi M. AP(Sr),SCSE
RENAME & DROP
• Rename – change the table name
– Rename <old tablename> to <new tablename>;
– E.g rename emp to employee;
• Drop – drop the table definition
– Drop Table <table name>;
– E.g drop table employee;
• Truncate – Removing the rows not definition
– Truncate table <table name>;
– E.g Truncate table employee;
Anbarasi M. AP(Sr),SCSE
Data Manipulation Language
• DML commands are used to insert, update,
retrieval and delete information in the database.
– INSERT
– UPDATE
– DELETE
Anbarasi M. AP(Sr),SCSE
Insert Command
• Inserting values
– INSERT INTO <tablename> VALUES( val1,val2 …);
– E.g insert into emp values(10,’anu’,’0416-2265767’,
’sales’,4000);
• Inserting interactively
– INSERT INTO <tablename> VALUES( &<column name1> ,
& <column name2> …);
– E.g insert into emp values(&emp_id,’&ename’,
’&ph_no’,’&job_id’,&salary);
Anbarasi M. AP(Sr),SCSE
• Inserting null values
– INSERT INTO <tablename> VALUES( val1,’ ‘,’ ‘,val4);
• E.g insert into emp values(10,’anu’,’ ’,NULL,4000);
– INSERT INTO <tablename> (column name1,column name2)
values (val1,val2);
• E.g insert into emp(emp_id, ename, salary) values
(10,’banu’,5000);
Anbarasi M. AP(Sr),SCSE
UPDATE
– a. Simple update
• UPDATE < tablename> SET <col> = < new value>;
• E.g update emp set salary=salary*10;
– b.Using where clause
• UPDATE < tablename> SET <col1> = < new value> , <col2> =
< new value> WHERE <conditions>;
• E.g update emp set salary = salary*10 where emp_id =10;
Anbarasi M. AP(Sr),SCSE
DELETE
• Deleting all rows
– DELETE FROM <tablename>;
– E.g delete from emp;
• Deleting specific rows
– DELETE FROM <tablename> where <condition>;
– E.g delete from emp where emp_id=10;
Anbarasi M. AP(Sr),SCSE
SELECT
• Selecting all the rows from a table
– SELECT * FROM < tablename>;
– E.g select * from emp;
• Selecting specific rows
– SELECT * FROM < tablename> where <condition>;
– E.g select * from emp where salary>5000;
• Selecting specific column
– SELECT <col1>, <col2> FROM < tablename>;
– E.g select emp_id, ename from emp;
Anbarasi M. AP(Sr),SCSE
• Alias name
– SELECT <col1> <alias name 1> , <col2> < alias name 2>
FROM <tablename>;
– E.g select emp_id “employee no” from emp;
– E.g select emp_id as employee_id from emp;
• Selecting distinct values for a column
– SELECT DISTINCT <col2> FROM < tab1>;
– E.g Select distinct emp_id from emp;
• Selecting columns satisfying a condition
– SELECT <col1>, <col2> FROM < table name> WHERE
<conditions>;
– E.g select emp_id, ename from emp where salary>5000;
Anbarasi M. AP(Sr),SCSE
Database Security & Authorization
Privileges: The rights that allow the use of some or
all of oracle’s resources on the server are called
privileges
Granting of privileges: the owner of the table will
have to give permissions for such access
Revoking of privileges: privileges once given can
be taken back by the owner
Syntax
Grant <Object Privileges> on <Object Name>
To <User Name> [With Grant Option]
Anbarasi M. AP(Sr),SCSE
Object privileges
Alter- Allows the grantee to change the table
definition
Delete - Allows the grantee to remove the records
from the table
Insert - Allows the grantee to add records to the
table
Select - Allows the grantee to query the table
Update - Allows the grantee to modify the records
in the table
With grant option -
Anbarasi M. AP(Sr),SCSE
1. Give the user Anu all data manipulation
permission on the table Emp
Grant All ON emp TO Anu;
2. Give the user Lakshmi permission to only view
& modify records in the table master;
Grant Select, Update ON master to lakshmi
with grant option;
3. Select * from lakshmi.master;
4. Grant Select on lakshmi.master to sriram;
Anbarasi M. AP(Sr),SCSE
Revoking Privileges
Syntax: Revoke <Object Privileges> ON
<Object Name> from <user name>;
Revoke Delete ON emp from priya;
Revoke All ON emp from priya;
Revoke Select ON anu.master from sara;
Anbarasi M. AP(Sr),SCSE
