12/11/2023 1

Contents
 Database at a Glance
 SQL Introduction
 Commands of SQL
 DML With Examples
 DDL With Examples
 DCL With Examples
 TCL With Examples
 Conclusion

2
12/11/2023
 What Is a Database?
A database is a collection of related data.
A database management system (DBMS) is a collection of
programs that enables users to create and maintain a database.

 Why use a database management system?

To control redundancy.
To restrict unauthorized access.
To provide persistent storage
To provide multiple user interfaces.
To representing complex relationships among data.
To provide backup and recovery.

12/11/2023 3
 SQL is a Standard
SQL is an ANSI (American National Standards Institute) standard
computer language for accessing and manipulating database
systems.

SQL statements are used to retrieve and update data in a

database.

SQL works with database programs like Crystal,MS Access,

DB2, Informix, MS SQL Server, Oracle, Sybase, etc

12/11/2023 4
 Categorization of Commands

 DML – Data Manipulation Language

 DDL – Data Definition Language
 DCL – Data Control Language
 TCL – Transaction Control Language

12/11/2023 5
 DML – Data Manipulation Language

 Select
 Insert
 Update
 Delete

DDL – Data Definition Language

• Create
• Alter
• Drop
• Truncate
12/11/2023 6
 DCL – Data Control Language

 Grant
 Revoke

TCL – Transaction Control Language

• Commit
• Rollback

12/11/2023 7
 DML Commands with Syntax & Ex
Select
Ei Ename Sal
d
Syntax(diff Formats) : 1 Paul 12000
• Select column_name(s) 2 Anshuman 10000
FROM table_name
3 Ambrish 12000
• SELECT *
4 Neethika 8000
FROM table_name Emp
Ex(diff Formats):
• Select eid, ename, sal from emp
• Select * from emp

12/11/2023 8
 DML Commands with Syntax & Ex
Insert
Ei Ename Sal
d
Syntax : 1 Paul 12000
 Insert into table_name 2 Anshuman 10000
values(val1,val2,val3…,valn) 3 Ambrish 12000
 Insert into table_name(col1,col2,col3…coln) 4 Neethika 8000
values(val1,val2,val3…,valn) 5 Swamy 7000
Emp
Ex:
 Insert into emp values(5,’Swamy’,7000)
 Insert into emp(eid,ename,sal) values(5,’Swamy’,7000)

12/11/2023 9
 DML Commands with Syntax & Ex
Ei Ename Sal
Update d
Syntax :
1 Paul 12000
 update table_name
2 Anshuman 10000
set col1=val1, col2=val2,…coln=valn
where col_name=value 3 Ambrish 12000
Ex: 4 Neethika 8000
Updating single column(only sal) 5 Swaminat 7500
h Emp
 Update emp set sal=7500 where eid=5

Updating multiple columns(both ename & sal)

 Update emp
set sal=7500,ename=‘Swaminath’ where eid=5

12/11/2023 10
 DML Commands with Syntax & Ex
Ei Ename Sal
Delete d
Syntax :
1 Paul 12000
 Delete table_name where col_name=value
 Delete from table_name where col_name=value 2 Anshuman 10000
3 Ambrish 12000
4 Neethika 8000
Ex:
5 Swami 7500
Deleting a selected row(by its eid)
The below query allows you to delete the row with eid=5.
 Delete from emp where eid=5
 OR Emp
Delete emp where eid=5

Deleting all records/rows/tuples

The below query allows you to delete all rows from emp table.
 Delete emp

12/11/2023 11
 Operators, Conditional Clauses, Keywords,
Predefined Functions….

Where, Between , AND, OR , IN , UNION , UNION ALL ,

INTERSECT , ANY, LIKE, TOP, Group by, Order by,
< , > , = , <> , <= , >=
<> Not Equal, in
some versions it is
!=

12/11/2023 12
 Syntax & Ex : Operators, Conditional
Clauses….
Syntax Examples

 SELECT <column_name(s)> • SELECT * FROM emp

FROM <table_name> WHERE sal>9000
WHERE <condition1> AND sal<14000
AND <condition2>
 SELECT <column_name(s)>
• SELECT * FROM emp
FROM <table_name>
WHERE ename=‘Paul’
WHERE <condition1>
OR ename=‘Ambrish’
OR <condition2>
 SELECT <column_name(s)>
FROM <table_name> • SELECT * FROM emp
WHERE <column_name> WHERE sal
BETWEEN <value1> AND <value2> BETWEEN 5000 AND 8000

12/11/2023 13
 Syntax & Ex : Predefined Functions, Keywords,
Clauses…..

Syntax Examples

Retrieving min value for a column from Retrieving min sal from emp
table • SELECT min(sal)
 SELECT min(column_name) FROM emp
FROM <table_name>
Retrieving max sal from emp
Retrieving max value for a column from
• SELECT max(sal)
table
FROM emp
 SELECT max(column_name)
FROM <table_name>
Retrieving count of rows/records from table

Retrieving count of rows/records from table • SELECT COUNT(*)

FROM emp
 SELECT COUNT(*)
FROM <table_name>

12/11/2023 14
 DDL Commands with Syntax & Ex

Create Ei Ename Sal

d
Syntax : Emp
 CREATE TABLE table_name
(
col1 data_type [Primary Key] [Identity(start)], Eid is of type
col2 data_type, int with PK and
col3 data_type, Identity set
...
)

The below format helps us to create structure of table emp with 3 columns
eid,ename,sal with eid set to primary key and identity(1,1)

Create table emp

(eid int Primary Key Identity(1,1), ename varchar(50), sal int)

12/11/2023 15
 DDL Commands with Syntax & Ex

Drop
Syntax :
 DROP table table_name

Ex:
The below query drop the table emp( records and structure of
table).

Drop table emp

12/11/2023 16
 DDL Commands with Syntax & Ex

Truncate
Syntax :
 Truncate table table_name

Ex(2 Formats):
 The below query helps us to delete the table emp(the records
inside the table-if any )

 Truncate do not have where clause where as Delete contains

where clause.

Truncate table emp

12/11/2023 17
 DDL Commands with Syntax & Ex

Alter Ei Enam Sal Desg

d e
Syntax to Add a new Column
Emp
 ALTER TABLE table_name
ADD column_name datatype
Syntax to Drop existing Column
 ALTER TABLE table_name
DROP COLUMN column_name

Ex(Add Column):

 ALTER TABLE Emp

ADD desg varchar(50)
Ex(Drop Column):

 ALTER TABLE Emp

DROP COLUMN desg

12/11/2023 18
 DCL Commands with Syntax & Ex

Grant Ei Enam Sal Desg

d e
Syntax
Emp
 GRANT privilege_name
ON object_name
TO {user_name};

Ex:
GRANT SELECT ON Emp TO user1

12/11/2023 19
 DCL Commands with Syntax & Ex

Revoke Ei Enam Sal Desg

d e
Syntax to remove GRANT
Emp
 REVOKE privilege_name
ON object_name
FROM {user_name}

Ex:
REVOKE SELECT ON Emp FROM user1

12/11/2023 20
 TCL Commands with Syntax & Ex

Commit
Syntax
 COMMIT

 To end your current transaction

 To make permanent all changes performed in the transaction.
 This command also releases the transaction's locks.

12/11/2023 21
 TCL Commands with Syntax & Ex

ROOLBACK
Syntax
 ROLLBACK

Example

BEGIN TRAN
DELETE FROM EMP
ROLLBACK
SELECT * FROM EMP
END TRAN

12/11/2023 22