PARUL INSTITUTE OF ENGINEERING & TECHNOLOGY

FACULTY OF ENGINEERING & TECHNOLOGY

PARUL UNIVERSITY

Database Management System

SQL (Structured Query Language )
Computer Science & Engineering

Prepared by : Prof.Nilesh Khodifad

Unit-2
Outline
• SQL
• DDL and DML Command
• DCL and TCL Command
• Predicates & Clauses
• Functions in SQL
Structured Query Language (SQL)

● Structured Query Language is a standard Database

language that is used to create, maintain, and retrieve
the relational database.
● SQL is case insensitive.
● SQL is the programming language for relational
databases (explained below) like MySQL, Oracle, Sybase,
SQL Server, Postgre, etc.
● Other non-relational databases (also called NoSQL)
databases like MongoDB, DynamoDB, etc. do not use
SQL.
Rules in SQL

1. Structure query language is not case sensitive.

Generally, keywords of SQL are written in uppercase.
2. Statements of SQL are dependent on text lines.
3. SQL depends on tuple relational calculus and
relational algebra.
SQL process
Characteristics of SQL
A. SQL is used to access data from relational database management
systems.
B. SQL can execute queries against the database.
C. SQL is used to describe the data.
D. SQL is used to define the data in the database and manipulate it
when needed.
E. SQL is used to create and drop the database and table.
F. SQL is used to create a view, stored procedure, function in a
database.
G. SQL allows users to set permissions on tables, procedures, and
views.
Advantages of SQL
1. High speed
2. No coding needed
3. Well defined standards
4. Portability
5. Interactive language
6. Multiple data view
SQL Datatype
SQL Datatype
SQL Datatype
SQL Commands
SQL Commands
● SQL commands are instructions. It is used to
communicate with the database. It is also used to
perform specific tasks, functions, and queries of
data.
● SQL can perform various tasks like create a table,
add data to tables, drop the table, modify the table,
set permission for users.
● There are five types of SQL commands: DDL, DML,
DCL, TCL.
SQL Commands
 Data Definition Language (DDL)
● DDL changes the structure of the table.
● All the command of DDL are auto-committed that means it
permanently save all the changes in the database.
some commands that come under DDL:
● CREATE
● ALTER
● DROP
● TRUNCATE
Data Definition Language (DDL)

CREATE
It is used to create a new table in the database.
Syntax: CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
Example: CREATE TABLE EMPLOYEE(Name VARCHAR2(20), Email
VARCHAR2(100), DOB DATE);
Data Definition Language (DDL)

DROP:
It is used to delete both the structure and record
stored in the table.
Syntax: DROP TABLE table_name;
Example: DROP TABLE EMPLOYEE;
Data Definition Language (DDL)
ALTER:
It is used to alter the structure of the database. This change could
be either to modify the characteristics of an existing attribute or
probably to add a new attribute.
Syntax:
To add a new column in the table
ALTER TABLE table_name ADD column_name COLUMN-definition;

ALTER TABLE table_name MODIFY(column_definitions....);

Data Definition Language (DDL)
Data Manipulation Language(DML)
DML commands are used to modify the database.
commands that come under DML:
● INSERT
● UPDATE
● DELETE
INSERT: The INSERT statement is a SQL query. It is used to insert
data into the row of a table.
Syntax:
INSERT INTO TABLE_NAME
(col1, col2, col3,.... col N)
VALUES (value1, value2, value3, .... valueN);
Data Manipulation Language(DML)
INSERT Example
INSERT INTO Engineering (Author, Subject) VALUES ("Sonoo",
"DBMS");
UPDATE: This command is used to update or modify the value of a
column in the table.
Syntax:
1. UPDATE table_name SET [column_name1=
value1,...column_nameN = valueN] [WHERE CONDITION]
Data Manipulation Language(DML)
UPDATE Example
UPDATE students
SET User_Name = 'Sonoo'
WHERE Student_Id = '3'
DELETE: It is used to remove one or more row from a table.
Syntax: DELETE FROM table_name [WHERE condition];
example:
DELETE FROM javatpoint
WHERE Author="Sonoo";
Data Control Language(DCL)
DCL commands are used to grant and take back authority from
any database user.
commands that come under DCL:
● Grant
● Revoke
Grant: It is used to give user access privileges to a database.
Example
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER,
ANOTHER_USER;
Data Control Language(DCL)
Revoke: It is used to take back permissions from the user.
Example
REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
Transaction Control Language(TCL)
➢ TCL commands can only use with DML commands like INSERT,
DELETE and UPDATE only.
➢ These operations are automatically committed in the database
that's why they cannot be used while creating tables or
dropping them.
commands that come under TCL:
● COMMIT
● ROLLBACK
● SAVEPOINT
Transaction Control Language(TCL)
Commit: Commit command is used to save all the transactions to
the database.
Syntax:
COMMIT;
Example:
DELETE FROM CUSTOMERS
WHERE AGE = 25;
COMMIT;
Transaction Control Language(TCL)
Rollback: Rollback command is used to undo transactions that
have not already been saved to the database.
Syntax: ROLLBACK;
Example:
DELETE FROM CUSTOMERS
WHERE AGE = 25;
ROLLBACK;
Transaction Control Language(TCL)
Predicates & Clauses
A Predicate in DBMS is a condition expression which evaluates and
results in boolean value either true or false which enables decision
making in retrieving and manipulating a record.
A predicate is a condition that is specified for:
● Filtering the data using the WHERE clause,
● Pattern matching in LIKE operator,
● Specifying a set of list for using IN operator,
● Manipulating a range of values using BETWEEN operator, etc
Predicates & Clauses
Consider a sample table ’emp’ EMPNO ENAME JOB SALARY

7839 KING PRESIDENT 5000

The predicate in where clause
7698 BLAKE MANAGER 2850
select * from emp
7782 CLARK MANAGER 2450
where [job='MANAGER']; 7566 JONES MANAGER 2975

O/P EMPNO ENAME JOB SALARY

7698 BLAKE MANAGE 2850

R

7782 CLARK MANAGE 2450

R

7566 JONES MANAGE 2975

R
Predicates Logical Operators Clauses
Predicates Logical Operators Clauses
Consider a sample table ’emp’ EMPNO ENAME JOB SALARY

SELECT * 7839 KING PRESIDENT 5000

FROM emp 7698 BLAKE MANAGER 2850
WHERE EMPNO = 7698
7782 CLARK MANAGER 2450
AND (SALARY>= 2850
OR SALARY >= 2975); 7566 JONES MANAGER 2975

EMPNO ENAME JOB SALA

RY

7839 KING KING 5000

O/P
7566 JONES MANAG 2975
ER
Predicates Relational Operators
Predicates in ‘IN’ Clauses
Consider a sample table ’emp’ EMPNO ENAME JOB SALARY

select empno,job,sal 7839 KING PRESIDENT 5000

from emp 7698 BLAKE MANAGER 2850
where [ename
7782 CLARK MANAGER 2450
in('JONES','CLARK','KING')];
7566 JONES MANAGER 2975

EMPNO JOB SALARY

O/P 7839 KING 5000

7782 MANAGER 2450

7566 MANAGER 2975

Predicates in ‘BETWEEN’ Clauses
Consider a sample table ’emp’ EMPNO ENAME JOB SALARY

select empno,job,sal 7839 KING PRESIDENT 5000

from emp 7698 BLAKE MANAGER 2850

where [sal between 800 and 2900]; 7782 CLARK MANAGER 2450

7566 JONES MANAGER 2975

EMPNO JOB SALARY

O/P
7698 BLAKE 2850

7782 MANAGER 2450

7566 MANAGER 2975

Predicates in ‘LIKE’ Clauses
Consider a sample table ’emp’ EMPNO ENAME JOB SALARY

select empno,job,sal 7839 KING PRESIDENT 5000

from emp 7788 SCOTT ANALYST 2850

where [ename like 'S%']; 7782 CLARK MANAGER 2450

7369 SMITH CLERK 800

EMPNO JOB SALARY

O/P
7788 ANALYST 2850

7369 CLERK 800

Predicates in ‘IS NULL’ Clauses
Consider a sample table ’emp’ EMPNO ENAME JOB COMM SALARY

select * from emp 7839 KING PRESIDENT - 5000

where [comm is null] 7788 SCOTT ANALYST 500 2850

7782 CLARK MANAGER 500 2450

O/P 7369 SMITH CLERK - 800

EMPNO ENAME JOB COMM SALARY

7839 KING PRESIDENT - 5000

7369 SMITH CLERK - 800

Predicates in ‘NOT’ Clauses
Consider a sample table ’emp’ EMPNO ENAME JOB SALARY

select empno,job,sal 7839 KING PRESIDENT 5000

from emp 7698 BLAKE MANAGER 2850

where [sal NOT between 800 and 7782 CLARK MANAGER 2450

2900]; 7566 JONES MANAGER 2975

EMPNO JOB SALARY

7839 PRESIDENT 5000

O/P
7566 MANAGER 2975
Aggregate function
Aggregation function is used to perform the calculations on
multiple rows of a single column of a table. It returns a single
value.
Aggregate function
COUNT FUNCTION
COUNT function is used to Count the number of rows in a
database table. It can work on both numeric and non-numeric
data types.
Syntax: COUNT(*)
or
COUNT( [ALL|DISTINCT] expression )
Aggregate function
COUNT FUNCTION
Example: COUNT()
or
SELECT COUNT(*)
FROM PRODUCT_MAST;
COUNT with WHERE : SELECT COUNT(*)
FROM PRODUCT_MAST;
WHERE RATE>=20;
COUNT() with DISTINCT: SELECT COUNT(DISTINCT COMPANY)
FROM PRODUCT_MAST;
Aggregate function
COUNT FUNCTION
COUNT() with GROUP BY
Example: SELECT COMPANY, COUNT(*)
FROM PRODUCT_MAST
GROUP BY COMPANY;
SUM Function
Syntax: SUM()
or
SUM( [ALL|DISTINCT] expression )
Example: SELECT SUM(COST)
FROM PRODUCT_MAST;
Aggregate function
SUM() with WHERE
Example: SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3;
SUM() with GROUP BY
Example: SELECT SUM(COST)
FROM PRODUCT_MAST
WHERE QTY>3
GROUP BY COMPANY;
Aggregate function
AVG function
Syntax : AVG()
or
AVG( [ALL|DISTINCT] expression )
Example: SELECT AVG(COST)
FROM PRODUCT_MAST;
MAX Function
Syntax: MAX()
or
MAX( [ALL|DISTINCT] expression )
Example: SELECT MAX(RATE)
FROM PRODUCT_MAST;
Aggregate function
MIN Function
Syntax : MIN()
or
MIN( [ALL|DISTINCT] expression )
Example: SELECT MIN(RATE)
FROM PRODUCT_MAST;
Arithmetic function
Function Description Example
ABS(d) Returns the absolute value of the double d ABS(-25.52) = 25.52
CEIL(d) Returns the smallest integer that is greater or equal to d. CEIL(24.8) = 25 CEIL(-24.8)= -24

COS(d) Evaluates the cosine of d radians. COS(1) = .540302306

EXP(d) Returns e Raised to d EXP(5) = 148.413159
FLOOR(d) Returns the largest integer less or equal to d. FLOOR(24.8) = 24, FLOOR(-
24.8)= -25
MOD(i1, i2) Returns i1 modulo i2, or the reminder of i1 divided i2. MOD(15,7) = 1
POWER(d, Returns d to the pth power (dp). POWER(2,5)= 32
p)
ROUND(d, i) Returns d rounded to i digits of precision to the right of the ROUND(2.6586,2) = 2.66
decimal point.
SIN(d) Evaluates the sine of d radians SIN(1) = .841470985
SQRT(d) Returns the square root of d. SQRT(81) = 9
TAN(d) Evaluates the tangent of d radians. TAN(1) = 1.55740772
Character Functions
character or string function is a function which takes one or more characters or
numbers as parameters and returns a character value.

Functions Description

lower() The SQL LOWER() function is used to convert all characters of a string to lower
case.

upper() The SQL UPPER() function is used to convert all characters of a string to
uppercase.

trim() The SQL TRIM() removes leading and trailing characters(or both) from a
character string.

translate() The SQL TRANSLATE() function replaces a sequence of characters in a string

with another sequence of characters. The function replaces a single character
at a time.
Character Functions
LOWER() function

Syntax: LOWER(string)

Example: LOWER(‘TEST’)

UPPER() function

Syntax: UPPER(string)

Example: UPPER(‘test’)
Character Functions
TRIM() function

The SQL TRIM() removes leading and trailing characters(or both) from a character string.

Syntax: TRIM( [ [{LEADING | TRAILING | BOTH}] [removal_char]

FROM ] target_string [COLLATE collation_name]

Character Functions
TRIM() function

Example: TRIM( TRAILING ‘M’ from ‘MADAM’)

output: MADA

Example: TRIM( LEADING ‘M’ from ‘MADAM’)

output: ADAM

Example: TRIM( BOTH ‘M’ from ‘MADAM’)

output: ADA
Date Functions
Date functions in SQL:
1. NOW() : give the current system's date and time.
2. CURDATE() : give the current system's date.
3. CURTIME() : give the current system time.
4. DATE() : extract the date from the DATETIME datatype column.
5. EXTRACT():extract a specific part of date and time according to our requirements: day,
month, year, day, hour, minute, etc.
6. DATE_ADD() :add a specific time interval to the given date.
7. DATE_SUB(): remove a specific time interval from the given date.
8. DATEDIFF(): give us the number of days that fall between the two given dates.
9. DATE_FORMAT(): display the date or time-related information in a well-formatted
manner.
Conversion Functions
the conversion functions are used to convert a value from one datatype to
another.
Function Description
CAST() Is used to transform the numerical data into character or string data.

CONVERT() Is used to transform an expression from one data type to another.

PARSE() Is used to convert a string data to the desired data format and returns
the outcome as an expression.

TRY_CAST() Is used to return the expression in the chosen data type.

TRY_CONVERT() Is used to change the datatype of an expression.

TRY_PARSE() Is used to return a result of an expression that has been converted to

the specified data type, or NULL if the conversion is unsuccessful.
Conversion Functions
Conversion Functions
Conversion Functions
Thank You!!!
www.paruluniversi
ty.ac.in