Sql Commands_Dr.R.Shalini.ppt

OVRVIEW ON SQL COMMANDS
Dr.R.Shalini
Asistant Professor
Dept. BCA/IT
VISTAS

Definition of DBMS
A database is a collection of related data organized
in a way that data can be accessed, managed and updated.
Any piece of information can be a data, for example in
school database stu_id, name, std etc
DBMS is a system software for creating and
managing databases. It provides users and programmers
with a systematic way to create, retrieve, update and
manage data.

RDBMS
 RDBMS (Relational Database Management System)
 The data in RDBMS are stored in database objects
called tables.
 A table is a collection of related data entries and it
consists of columns and rows.

Structured Query Language(SQL)
SQL is the standard language for managing data in
Relational Database System.
All the Relational Database Management Systems
(RDMS) like MySQL, MS Access, Oracle, Sybase,
Informix, Postgres and SQL Server use SQL as their
standard database language.

Categories Of SQL
The four main categories of sql:
1. DML (Data Manipulation Language)
2. DDL (Data Definition Language)
3. DCL (Data Control Language)
4. TCL (Transaction Control Language)

DDL command
DDL(Data Definition Language) which deals with database
schemas and descriptions, of how the data should reside in the
database.
 CREATE – create a new Table, database, schema.
 ALTER – alter existing table, column description.
 DROP – delete existing objects from database.
 RENAME – to rename a column or table.

Create Command
Create command used to create a table or a database.
Syntax:
create table table-name { column-name1 datatype1, column-
name2 datatype2, column-name3 datatype3, column-name4
datatype4 };
Example:
create table Student(id number(3), name varchar2(20), age
number(3));

Alter command
Alter command is used for alteration of table structures.
Syntax:
alter table table-name add(column-name datatype);
Example
alter table Student add(address char);
 alter table Student add(father-name varchar(60), mother name
varchar(60), dob date);
 alter table Student modify(address varchar(30));

Drop command
Drop query completely removes a table from database.
Syntax
 alter table table-name drop(column-name);
 drop table table-name;
Example
 alter table Student drop(address);
 drop table Student;

Rename command
Rename command is used to rename a table or column.
Syntax:
1.Rename table old-table-name to new-table-name;
2.Alter table tablename rename old-cloumn name to columnname;
Example :
1.Rename table Student to Student-record;
2.Alter table student rename address column to location;

DML command
DML( Data Manipulation Language) which deals with data
manipulation and includes SQL statements such SELECT, INSERT,
UPDATE, DELETE etc, and it is used to store, modify, retrieve, delete
and update data in a database.
 SELECT – select records from a table
 INSERT – insert new records
 UPDATE – update/Modify existing records
 DELETE – delete existing records

Insert command
Insert command is used to insert data into a table
Syntax
INSERT into table-name values(data1,data2,..);
Example
INSERT into Student values(102,'Alex',15);

Update command
Update command is used to update a row of a table.
Syntax
Update table-name set column-name = value where condition;
Example
 Update Student set age=18 where s_id=102;
 Update Student set s_name='Abhi',age=17 where s_id=103;

Delete command
Delete command is used to delete data from a table.
Syntax
DELETE from table-name;
Example
To Delete all Records from a Table
DELETE from Student;
To Delete a particular Record from a Table
DELETE from Student where s_id=103;

SELECT command
The SELECT statement is used to select data from a database.
The data returned is stored in a result table, called the result-set.
The SELECT statement has many optional clauses:
•WHERE specifies which rows to retrieve.
•GROUP BY groups rows sharing a property so that an aggregate function can be
applied to each group.
•HAVING selects among the groups defined by the GROUP BY clause.
•ORDER BY specifies an order in which to return the rows.
•DISTINCT keyword is used in conjunction with the SELECT statement to
eliminate all the duplicate records and fetching only unique records.

SELECT Command contd...
Syntax 1: Select * FROM table_name;
Syntax 2: Select column1, column2, columnN FROM table_name WHERE
[condition];
Ex1:SQL> Select Id, Name, Salary From Customers WHERE Salary >
2000;
Ex 2: SQL> Select Id, Name, Salary From Customers WHERE Name =
'Hardik';
Syntax 3: Select column1, column2, columnN FROM table_name WHERE
[condition1] AND [condition2]...AND [conditionN];

SELECT Command contd..
Ex1:Select Id, Name, Salary From Customers Where Salary > 2000 AND age <
25;
Ex2: Select Id, Name, Salary From Customers Where Salary > 2000 OR Age <
25;
Syntax 4:
SELECT column-list * from table-name order by asc|desc;
Ex 1:SELECT * from Emp order by salary;
Ex 2: SELECT * from Emp order by salary DESC;

SELECT Command contd..
Syntax5:
SELECT column_name, function (column_name) FROM
table_name WHERE condition GROUP BY
column_name;
Ex1 : SELECT name, age from Emp group by salary:
Ex2: Group by Statement with WHERE clause
SELECT name, salary from Emp where age > 25 group by salary;

SELECT command contd..
Like clause:It is used to find similar data from the table.
Two wildcard operators used in like clause.
 Percent sign % : represents zero, one or more than one character.
 Underscore sign _ : represents only one character.
Ex1: :SELECT * from Student where s_name like 'A%';
Ex2: SELECT * from Student where s_name like '_d%';
Ex3: SELECT * from Student where s_name like '%x';

DCL Command
Data Control Language(DCL) is used to control privilege in Database
The rights that allow the use of some or all of oracle's resources on the Server are
called PRIVILEGES
Privileges are of two types,
 System : creating session, table etc are all types of system privilege.
 Object : any command or query to work on tables comes under object privilege.
DCL defines two commands,
 Grant :Gives privileges to user for accessing database data
 Revoke :Take back for given privileges

DCL Command
Grant Syntax:
GRANT <object privileges> ON <object_name> TO <User_Name>[WITH GRANT
OPTION]
eg:1 GRANT ALL ON Student TO Mohd Imran WITH GRANT OPTION
eg:2 Grant Select, Update On Student To Fareen With Grant Option
To view the contents of the student table that belongs to Mohd Imran.
eg: Select * From Mohd Imran.Student;

DCL Command
Revoke Syntax:
Revoke <object_privileges> ON <Object_Name> FROM
<User_Name>
Eg: REVOKE UPDATE ON Student FROM Fareen

TCL Command
 Transaction Control Language(TCL) commands are used to
manage transactions in database.
 These are used to manage the changes made by DML statements
COMMIT :Permanent work save into database.
ROLLBACK :Restore database to original form since the last COMMIT.
SAVEPOINT :Create SAVEPOINT for later use ROLLBACK the new
changes

TCL Command
 Syntax: commit;
 Syntax: rollback to savepoint-name;
 Syntax: savepoint savepoint-name;

TCL Command
Example:
 INSERT into class values(5,'Rahul');
 commit;
 UPDATE class set name='abhijit' where id='5';
 savepoint A;
 INSERT into class values(6,'Chris');
 savepoint B;
 INSERT into class values(7,'Bravo');
 savepoint C;
 SELECT * from class

TCL Command
The resultant table will look like,
Now rollback to savepoint B
rollback to B;

TCL Command
SELECT * from class;
The resultant table will look like

Sql Commands_Dr.R.Shalini.ppt

In this document

More Related Content

What's hot

Similar to Sql Commands_Dr.R.Shalini.ppt

Recently uploaded

Sql Commands_Dr.R.Shalini.ppt