We are on a mission to address the digital

skills gap for 10 Million+ young professionals,

train and empower them to forge a career
path into future tech
 Introduction to
Structured Query
Language(SQL)
Please download pictures in
Date: 12.07.2025 suitable size here and insert them
by clicking the symbol above.

2 SQL | © SmartCliff | Internal | Version 1.0

 MySQL

Introduction to SQL
• Structured Query Language – SQL is the most common language used to communicate with the
database.

• English like language

• Closely associated with relational model.

Users Database

Create
Insert / Delete
Update

Select
3 SQL | © SmartCliff | Internal | Version 1.0
 MySQL

Introduction to SQL

• Different languages in SQL

▪ Data Definition Language (DDL)

▪ Data manipulation Language (DML)

▪ Data Control Language (DCL)

▪ Transaction Control Language (TCL)

4 SQL | © SmartCliff | Internal | Version 1.0

 MySQL

DDL (Data Definition Language)

• Used to define the objects in database.

• Deals with description of database objects like create and modify the structure of the object.

• All DDL commands are auto-committed, which means they store all database changes permanently.

• Keywords :

▪ CREATE: Create an object in the database.

▪ ALTER: Change the structure of the object.

▪ TRUNCATE: Remove all the records from the table.

▪ RENAME: Change the name of the table.

▪ DROP : Remove the objects from the database.

5 SQL | © SmartCliff | Internal | Version 1.0

 MySQL

DML (Data Manipulation Language)

• DML statements are used to manipulate the values in the database.

• DML Command are not auto - committed , it means changes by DML command are not permanent it can be roll

back.

• Keywords :

• SELECT - Retrieve data from the a database

• INSERT - Insert data into a table

• UPDATE - Updates existing data within a table

• DELETE - Deletes all records from a table

6 SQL | © SmartCliff | Internal | Version 1.0

 MySQL

DCL (Data Control Language)

• Any database user can be granted or revoked authority using DCL commands.

• Keywords:

▪ GRANT - Gives users access privileges to the database

▪ REVOKE - Withdraws the user’s access privileges given by using the GRANT command.

7 SQL | © SmartCliff | Internal | Version 1.0

 MySQL

TCL (Transaction Control Language)

• Transaction :

• A transaction is the logical work unit that performs a single activity or multiple activities in a database.

• Transactions may consist of a single read, write, delete, or update operations or a combination of these.

• TCL

• Commands are used to manage transactions in a database.

• Allows statements to be grouped together

8 SQL | © SmartCliff | Internal | Version 1.0

 MySQL

TCL (Transaction Control Language)

• Keywords :

• Commit : Save the changes permanently

• Rollback : Restores the database to the original state since last commit.

• Savepoint : Used to temporarily save transaction so that you can rollback to that point whenever necessary.

9 SQL | © SmartCliff | Internal | Version 1.0

 MySQL

DataTypes

• Each column in a database table is required to have a name and a data type.

• In MySQL there are three main data types:

• Numeric Datatype

• Date and Time Datatype

• String Datatype

10 SQL | © SmartCliff | Internal | Version 1.0

 MySQL

Datatypes - Numeric
Storage Minimum Value Minimum Value Maximum Value Maximum Value
Type
(Bytes) Signed Unsigned Signed Unsigned

TINYINT 1 -128 0 127 255

SMALLINT 2 -32768 0 32767 65535

MEDIUMINT 3 -8388608 0 8388607 16777215

INT 4 -2147483648 0 2147483647 4294967295

BIGINT 8 -263 0 263-1 264-1

Total number of digits is specified in size.
The number of digits after the decimal point is specified in the d parameter. The maximum
DECIMAL(size,d),
Varies number for size is 65.
NUMERIC(size,d)
The maximum number for d is 30.
The default value for size is 10. The default value for d is 0.
Uses the p value to determine whether to use FLOAT or DOUBLE for the resulting data type.
FLOAT(p) 4/8 If p is from 0 to 24, the data type becomes FLOAT().
If p is from 25 to 53, the data type becomes DOUBLE()

11 SQL | © SmartCliff | Internal | Version 1.0

 MySQL

Datatypes – Data and Time

Display
Types Description Range
Format

Use when you need only date

DATE YYYY-MM-DD 1000-01-01’ to '9999-12-31
information.
Use when you need only time
TIME HH:MM:SS -838:59:59' to '838:59:59
information.
Use when you need values containing
DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00' to '9999-12-31
both date and time information.
Values are converted from the current
timezone to UTC while storing, and 1970-01-01 00:00:01 UTC to 2038-
TIMESTAMP YYYY-MMDD HH:MM:SS
converted back from UTC to the 01-19 03:14:07 UTC
current time zone when retrieved.

12 SQL | © SmartCliff | Internal | Version 1.0

 MySQL

Datatypes – String
Types Description Categories Range
Contains non-binary strings. Length is
fixed as you declare while creating a
Trailing spaces are The length can be any value from 0
CHAR table. When stored, they are
removed. to 255.
rightpadded with spaces to the
specified length.
A value from 0 to 255 before MySQL
Contains non-binary strings. Columns
VARCHAR As stored. 5.0.3, and 0 to 65,535 in 5.0.3 and
are variable-length strings.
later versions.
BINARY Contains binary strings. - 0 to 255
A value from 0 to 255 before MySQL
VARBINARY Contains binary strings. - 5.0.3, and 0 to 65,535 in 5.0.3 and
later versions.

13 SQL | © SmartCliff | Internal | Version 1.0

 MySQL

Datatypes – String
Types Description Categories Range

TINYBLOB Maximum length of 255 characters.

Large binary object that containing a
variable amount of data. Values are Maximum length of 16777215
BLOB treated as binary strings. MEDIUMBLOB
characters.
You don't need to specify length while
creating a column. Maximum length of 4294967295
LONGBLOB
characters

TINYBLOB Maximum length of 255 characters.

TEXT Values are treated as character
strings having a character set.
Maximum length of 16777215
MEDIUMBLOB
characters.

14 SQL | © SmartCliff | Internal | Version 1.0

 DDL -
Data Definition
Language Please download pictures in
suitable size here and insert them
by clicking the symbol above.

15 SQL | © SmartCliff | Internal | Version 1.0

 DDL

Create Command

• The Create command is used for creating a database .

Syntax :
CREATE DATABASE databasename;

Example:
CREATE DATABASE menagerie;

16 SQL | © SmartCliff | Internal | Version 1.0

 DDL

DROP Command

• The Drop command is used to drop an existing SQL database.

Syntax :
DROP DATABASE databasename;

Example:
DROP DATABASE menagerie;

17 SQL | © SmartCliff | Internal | Version 1.0

 DDL

Create Command

• The Create command is used for creating a table. Table is the base object in a database

Syntax:
Example:
CREATE TABLE table_name (
CREATE TABLE pet (
column1 datatype,
name VARCHAR(20),
column2 datatype,…
owner VARCHAR(20),
column3 datatype,
species VARCHAR(20),
………
);
);

18 SQL | © SmartCliff | Internal | Version 1.0

 DDL

Lexical Issues

• Must start with an alphabet.

• Can contain numbers and special characters ( _, #, $).

• Must not contain space.

• Must be unique.

19 SQL | © SmartCliff | Internal | Version 1.0

 DDL

Dropping the table

• DROP TABLE is used to remove the table from the database.

• Table, along with its data, remains no longer in the database.

Syntax:
DROP TABLE table_name;

Example:
DROP TABLE pet;

20 SQL | © SmartCliff | Internal | Version 1.0

 DDL

Truncating the table

• TRUNCATE is used to delete all the rows in the table but maintains the structure of the table.

• It also releases the space allocated.

• It cannot be rolled back.

Syntax:
TRUNCATE TABLE table_name;

21 SQL | © SmartCliff | Internal | Version 1.0

 DDL

Altering the table

• The alter table command is used to modify the structure of the table.

• Modifying the structure includes adding columns, constraints, changing data types and sizes,

dropping and renaming columns.

22 SQL | © SmartCliff | Internal | Version 1.0

 DDL

Altering the table (Add Columns)

• Use the ADD option in the ALTER TABLE command.

Syntax:
ALTER TABLE table_name ADD column_name datatype;

ALTER TABLE table_name ADD

(column1_name data type, column2_name data type…);

Example:
ALTER TABLE pet ADD sex CHAR(1);

ALTER TABLE pet ADD (birth DATE, death DATE);

23 SQL | © SmartCliff | Internal | Version 1.0

 DDL

Altering the table (Drop Columns)

• Use the DROP option in the ALTER TABLE command.

Syntax:
ALTER TABLE table_name DROP COLUMN column_name;

Example:
ALTER TABLE pet DROP COLUMN death ;

24 SQL | © SmartCliff | Internal | Version 1.0

 DDL

Altering the table (Modify Column)

• Used the MODIFY option in the ALTER TABLE command to change the definition of the column. The

column data type, length , default value, constraints can be modified.

Syntax:
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;

Example:
ALTER TABLE pet
MODIFY COLUMN sex varchar(10);

25 SQL | © SmartCliff | Internal | Version 1.0

 DDL

Altering the table (Renaming Column)

• The RENAME COLUMN statement is used to change the name of the column

Syntax:

ALTER TABLE table_name RENAME COLUMN old_name to new_name;

Example:

ALTER TABLE pet RENAME COLUMN sex to gender;

26 SQL | © SmartCliff | Internal | Version 1.0

 DDL

Renaming the table

• RENAME command is used to change the name of the table.

Syntax:
RENAME table old_name to new_name;

Example:
RENAME table pet to pet_animal;

27 SQL | © SmartCliff | Internal | Version 1.0

 Constraints
Please download pictures in
suitable size here and insert them
by clicking the symbol above.

28 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

MySQL Constraints

• Provide data integrity to data by applying business rules on a column.

• Can be declared while or after creating the table.

• Can be enabled or disabled.

29 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Types of Constraints

Not Null Constraint:

• Ensures a field to always contain a value.

• Can also be used to uniquely identify the records.

30 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Types of Constraints
Example - NOT NULL Agent_Details

Data
Field Name Size NULL
Type

agent_code char 6 No

agent_name varchar 25 No

working_area varchar 25 No

Example:
CREATE TABLE Agent_Details (

agent_code char(6) NOT NULL,

agent_name varchar(25) NOT NULL,

working_area varchar(25) NOT NULL);

31 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Types of Constraints

Unique Constraint:

• Ensures that all the values in the column are unique.

• Can also be used to uniquely identify the records.

• Accepts null values.

32 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Types of Constraints
Example - Unique Order_Details

Field Name Data Type Size Decimal Places NULL Constraint

ord_num decimal 6 No UNIQUE

ord_amount decimal 12 2 Yes

ord_date date No

cust_code char 6 No

agent_code char 6 No

Example:
CREATE TABLE Order_details ( order_num DECIMAL(6) NOT NULL UNIQUE,
order_amount DECIMAL(12,2) ,
order_date DATE NOT NULL,
customer_code CHAR(6) NOT NULL,
agent_code CHAR(6) NOT NULL);
33 SQL | © SmartCliff | Internal | Version 1.0
 Constraints

Types of Constraints

Primary Key Constraint:

• Uniquely identifies the records in a table.

• Ensures that the column has a value.

• Table is called as Master table or parent.

• Table can have only one primary key

34 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Types of Constraints
Person_Details
Example - PrimaryKey
Field Name Data Type Size NULL Constraint

person_id int No primary

person_name varchar 32 No

person_gender char 1 Yes

person_dob date Yes

person_email varchar 32 No unique

Example:
CREATE TABLE person_details ( person_id INT PRIMARY KEY,
person_name VARCHAR(32) NOT NULL,
person_gender CHAR(1) , person_dob DATE ,
person_email VARCHAR(32) NOT NULL UNIQUE );

35 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Types of Constraints
Default Constraint:

• Used to set a default value for a column

Person_Details
Example:
Field Name Data Type Size NULL Constraint CREATE TABLE person_details (
person_id int No primary person_id INT PRIMARY KEY,
person_name varchar 32 No person_name VARCHAR(32) NOT NULL,
person_gender char 1 Yes person_gender CHAR(1) ,
person_dob date Yes person_dob DATE ,
person_email varchar 32 No unique person_email VARCHAR(32) NOT NULL
person_city varchar 32 No default UNIQUE,
person_city VARCHAR(32) DEFAULT
‘TAMILNADU’ );

36 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Types of Constraints

Check Constraint:

• Allows the values only if the condition is true.

• Can also be used to limit the value of the range that can be placed in a column.

37 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Types of Constraints
Check Constraint:

Person_Details
Example:
Field Name Data Type Size NULL Constraint
CREATE TABLE person_details (
person_id int No primary
person_id INT PRIMARY KEY,
person_name varchar 32 No
person_name varchar(32) NOT NULL,
person_gender char 1 Yes
person_gender CHAR(1) ,
person_age int Yes check
(person_age person_age INT check (person_age >=18) ,
>=18) person_email VARCHAR(32) NOT NULL
person_email varchar 32 No unique UNIQUE,
person_city varchar 32 No default person_city VARCHAR(32) DEFAULT
‘TAMILNADU’ );

38 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Types of Constraints

Foreign Key Constraint:

• Refers to the PRIMARY KEY of another table

• Establishes a link between tables.

• Table is called as Child table.

• A single Primary key can be referred by many foreign keys.

39 SQL | © SmartCliff | Internal | Version 1.0

 Constraints
Types of Constraints
Foreign Key Constraint:
customers Example:
Data
Field Name Size NULL Constraint CREATE TABLE customers (
Type
customer_id INT PRIMARY KEY,
customer_id int No Primary
customer_name VARCHAR(32) NOT NULL,
customer_name varchar 32 No
customer_age INT,
customer_age int Yes
customer_city VARCHAR(32) NOT NULL );
customer_city varchar 32 No

Customer_orders Example:
Data CREATE TABLE customer_orders (
Field Name Size NULL Constraint
Type
order_id INT PRIMARY KEY,
order_id int No Primary
order_date DATETIME NOT NULL,
order_date datetime No
order_amount INT NOT NULL,customer_id INT
order_amount int No
FOREIGN KEY(customer_id ) REFERENCES
customer_id int No Foreign
customers (customer_id) );
40 SQL | © SmartCliff | Internal | Version 1.0
 Constraints

Constraint Levels
Column Level:

• Declared while creating the table.

• Refer to single column

• Declared immediately after defining the column

Example:
CREATE TABLE customer_orders (
order_id INT PRIMARY KEY,
order_date DATETIME NOT NULL,
order_amount INT NOT NULL,
customer_id INT );

41 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Constraint Levels

Table Level:

• Declared while or after creating the table.

• Refer one or more columns

• Declared after defining all the columns.

Note : NOT NULL constraint always column level.

42 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Declaring table level constraint

Example:
CREATE TABLE customer_orders (
order_id INT ,
order_date DATETIME NOT NULL,
order_amount INT NOT NULL,
customer_id INT,
CONSTRAINT oid_pk PRIMARY KEY(order_id),
FOREIGN KEY(customer_id ) REFERENCES customers (customer_id) );

43 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Constraint Levels
• In MySQL, when you have a table with a foreign key reference to another table, and you want to

delete a row or column from the referenced table, you need to carefully manage how this

deletion affects the referencing table. Here’s how you can handle such scenarios:

1. Deleting Rows in the Referenced Table

When you delete a row in the table that is being referenced by a foreign key in another table, MySQL

needs to know what to do with the referencing rows. You can specify the behavior using foreign key

constraints:

44 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Constraint Levels
Options for Handling Deletions

CASCADE:

o What it does: Automatically deletes rows in the referencing table that match the foreign key
value being deleted.

o Use case: Use this if you want to automatically remove all related records when the parent
record is deleted.

Example:
ALTER TABLE referencing_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (column_in_referencing_table)
REFERENCES
referenced_table(column_in_referenced_table
)
ON DELETE CASCADE;
45 SQL | © SmartCliff | Internal | Version 1.0
 Constraints

Constraint Levels
SET NULL:

o What it does: Sets the foreign key column in the referencing table to NULL when the
referenced row is deleted.

o Use case: Use this if you want to retain the referencing row but indicate that the foreign key
reference no longer exists.

Example:
ALTER TABLE referencing_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (column_in_referencing_table)
REFERENCES
referenced_table(column_in_referenced_table
)
ON DELETE SET NULL;

46 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Constraint Levels
RESTRICT:

o What it does: Prevents the deletion of a row in the referenced table if there are any rows in the
referencing table that reference it.

o Use case: Use this if you want to enforce referential integrity strictly and disallow deletion if
dependent rows exist.
Example:
ALTER TABLE referencing_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (column_in_referencing_table)
REFERENCES
referenced_table(column_in_referenced_table
)
ON DELETE RESTRICT;

47 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Constraint Levels
NO ACTION:

o What it does: Similar to RESTRICT, but differs in its behavior in terms of when the restriction
check is performed. Essentially, it behaves like RESTRICT if not explicitly set.

o Use case: Use this for strict enforcement of referential integrity at the end of the transaction.

Example:
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (column_in_referencing_table)
REFERENCES
referenced_table(column_in_referenced_table
)
ON DELETE NO ACTION;

48 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Constraint Levels
2. Deleting Columns in the Referenced Table : If you want to delete a column that is part of a foreign

key constraint, you must first drop the foreign key constraint that uses that column. After dropping the

constraint, you can safely delete the column.

Steps to Drop a Column:

1. Find and Drop the Foreign Key Constraint:

-- Find the foreign key constraint name

SELECT CONSTRAINT_NAME
FROM information_schema.KEY_COLUMN_USAGE
WHERE TABLE_NAME = 'referencing_table'
AND COLUMN_NAME = 'column_in_referencing_table';
-- Drop the foreign key constraint
ALTER TABLE referencing_table
DROP FOREIGN KEY fk_constraint_name;

49 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Constraint Levels
2. Drop the Column:
ALTER TABLE referenced_table
DROP COLUMN column_to_be_deleted;

3. Recreate Foreign Key Constraints (if necessary):

ALTER TABLE referencing_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (column_in_referencing_table)
REFERENCES referenced_table(column_in_referenced_table)
ON DELETE [CASCADE/SET NULL/RESTRICT/NO ACTION];

50 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Constraint Levels
• For insertions, MySQL ensures referential integrity by enforcing foreign key constraints, which

means you cannot insert a value into the referencing table unless it matches a value in the

referenced table.

• Managing insertions involves ensuring that the foreign key values exist in the referenced table,

using transactions to maintain atomic operations, and handling complex scenarios through

application logic. While there is no direct ON INSERT action, the foreign key constraints themselves

help maintain data integrity.

51 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Constraint Levels
On Update Cascade:

Example:
ALTER TABLE referencing_table
ADD CONSTRAINT fk_constraint_name
FOREIGN KEY (column_in_referencing_table)
REFERENCES
referenced_table(column_in_referenced_table)
ON UPDATE CASCADE;

52 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

AUTO_INCREMENT Field
• Auto-increment allows a unique number to be generated automatically when a new record is inserted into a table.

• Often this is the primary key field .

• By default, the starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new record.

Example:
CREATE TABLE Persons (
Personid int NOT NULL AUTO_INCREMENT, LastName varchar(255) NOT NULL,
FirstName varchar(255), Age int,PRIMARY KEY (Personid));

• To let the AUTO_INCREMENT sequence start with another value

Example:
ALTER TABLE Persons AUTO_INCREMENT = 100;

53 SQL | © SmartCliff | Internal | Version 1.0

 Constraints

Altering the table to add constraint

Syntax:
ALTER TABLE table_name ADD [CONSTRAINT constraint_name]

constraint_type(column_name);

Example
CREATE TABLE CustomerOrders(
order_ID INT ,
Example:
orderDate DATETIME,
ALTER TABLE CustomerOrders add CONSTRAINT amt_chk
Amount BIGINT,
CHECK (Amount >0);
CustomerID INT,
PRIMARY KEY(order_ID),
FOREIGN KEY (CustomerID) REFERENCES
Customers(Customer_ID));

54 SQL | © SmartCliff | Internal | Version 1.0

 DML

Data Manipulation Please download pictures in

suitable size here and insert them

Language by clicking the symbol above.

55 SQL | © SmartCliff | Internal | Version 1.0

 DML

DML (Data Manipulation Language)

• DML statements are used to manipulate the values in the database.

• DML consists of the following set of Key words

• INSERT - Insert data into a table

• UPDATE - Updates existing data within a table

• DELETE - Deletes all records from a table

56 SQL | © SmartCliff | Internal | Version 1.0

 DML

Inserting records into a table

• Used to insert records into the table.

Syntax:

INSERT INTO table_name VALUES (value1,value2,value3……);

INSERT INTO table_name( column1_name,column2_name…..)

VALUES(value1,value2,value3……);

Example:

INSERT INTO customers VALUES (‘cus_001’, ‘Smith’, 34,’Boston’);

Note:
Column name list is optional of values.
Character and date values must be enclosed within single quotation.
Date must be in ‘YYYY-MON-DD’ format.

57 SQL | © SmartCliff | Internal | Version 1.0

 DML

UPDATE records into a table

• Used to change or modify the existing values.

• Either all the records or a subset may be chosen using WHERE Clause.

Syntax:
UPDATE table SET column1=value1, column2=value2 WHERE condition;

Example:
UPDATE customer SET customer_city = ‘Gluton’ WHERE customer_id = ‘cus_001’;

58 SQL | © SmartCliff | Internal | Version 1.0

 DML

Deleting Records from a table

• The DELETE statement removes one or more records from a table. A subset may be defined for

deletion using a condition, otherwise all records are removed.

Syntax:
DELETE FROM table_name [WHERE column=condition];

Example:
DELETE FROM customers WHERE customer_id = ‘cus_001’;

59 SQL | © SmartCliff | Internal | Version 1.0

 DML

TRUNCATE Query
• Removes all rows from a table, leaving it empty but with its structure intact.

Syntax:
TRUNCATE TABLE table_name ;

Example:
TRUCATE TABLE customers ;

60 SQL | © SmartCliff | Internal | Version 1.0

 Data Control
Language(DCL)
Please download pictures in
suitable size here and insert them
by clicking the symbol above.

61 SQL | © SmartCliff | Internal | Version 1.0

 Data Control Language

Introduction

• DCL commands are used for access control and permission management for users in our
database.

• With these commands, we can easily allow or deny some actions for users on the tables or
records (row-level security).

• The below table describes the DML Commands:

Commands Description
GRANT This command gives users access privileges to the database.
REVOKE This command withdraws the user’s access privileges given by using the
GRANT command.
DENY bans certain permissions from users.

62 SQL | © SmartCliff | Internal | Version 1.0

 Data Control Language

Introduction

PRIVILEGES

The privilege is to assign access. It can be any of the following values:

PRIVILEGE DESCRIPTION
SELECT Ability to perform SELECT statements on the table.
INSERT Ability to perform INSERT statements on the table.
UPDATE Ability to perform UPDATE statements on the table.
DELETE Ability to perform DELETE statements on the table.
REFERENCES Ability to create a constraint that refers to the table.
ALTER Ability to perform ALTER TABLE statements to change the table definition.

ALL does not grant all permissions for the table. Rather, it grants the ANSI-92 permissions
ALL
which are SELECT, INSERT, UPDATE, DELETE, and REFERENCES.

63 SQL | © SmartCliff | Internal | Version 1.0

 Data Control Language

GRANT Command

• The GRANT command adds new permissions to a database user.

• Syntax:
GRANT [privilege]
ON [object]
TO [user]
[WITH GRANT OPTION]

• Here's the rundown on each of the parameters that we have used in the above command:

Privilege - can be either the keyword ALL (to grant a wide variety of permissions) or a specific
database permission or set of permissions.

Privilege include CREATE DATABASE, SELECT, INSERT, UPDATE, DELETE, EXECUTE,

and CREATE VIEW.

64 SQL | © SmartCliff | Internal | Version 1.0

 Data Control Language

GRANT Command

Object - can be any database object. The valid privilege options vary based on the type of

database object you include in this clause. Typically, the object will be either a database,

function, stored procedure, table or view.

User - can be any database user. You can also substitute a role for the user in this clause if

you wish to make use of role-based database security.

If you include the optional WITH GRANT OPTION clause at the end of the GRANT command,

you not only grant the specified user the permissions defined in the SQL statement but also

give the user permission to further grant those same permissions to other database users.

65 SQL | © SmartCliff | Internal | Version 1.0

 Data Control Language

GRANT Command : Example #1

// Grant permission to the user to retrieve the data from the employee
GRANT SELECT
ON HR.employees
TO Joe

• In Example #1, Joe can retrieve information from the employees' table.

• He will not be able to grant permission to other users to retrieve information from that table because

the DCL script did not include the WITH GRANT OPTION clause.

66 SQL | © SmartCliff | Internal | Version 1.0

 Data Control Language

GRANT Command : Example #2

// Grant permission to the user to retrieve the data from the employee
GRANT UPDATE
ON HR.employees
TO Joe WITH GRANT OPTION

• In example #2, Joe can update the data in HR.employees.

• The WITH GRANT OPTION clause indicates that JOE can grant to other users any of the

privileges on the HR.employees table.

67 SQL | © SmartCliff | Internal | Version 1.0

 Data Control Language

GRANT Command : Example #3

// Grant permission to insert the data in the employee with public keyword
GRANT INSERT
ON HR.employees
TO PUBLIC WITH GRANT OPTION

• In example #3, grant the INSERT privilege on the HR.employees table to all users in the local

database, and allow each of those users to grant the INSERT privilege to other users.

68 SQL | © SmartCliff | Internal | Version 1.0

 Data Control Language

REVOKE Command

• The REVOKE command removes database access from a user previously granted such access.
• Syntax:
REVOKE [GRANT OPTION FOR] [permission]
ON [object]
FROM [user]
[CASCADE]

• Here's the rundown on each of the parameters that we have used in the above command:

Permission — specifies the database permissions to remove from the identified user.

The command revokes both grants and DENY assertions previously made for the identified
permission.

69 SQL | © SmartCliff | Internal | Version 1.0

 Data Control Language

REVOKE Command

Object — can be any database object. The valid privilege options vary based on the type of
database object you include in this clause. Typically, the object will be either a database, function,
stored procedure, table, or view.

User — can be any database user. You can also substitute a role for the user in this clause if you
wish to make use of role-based database security.

The GRANT OPTION FOR clause removes the specified user's ability to grant the specified
permission to other users. If you include the GRANT OPTION FOR clause in a REVOKE
statement, the primary permission is not revoked. This clause revokes only the granting ability.

The CASCADE option also revokes the specified permission from any users to whom the specified
user granted the permission.

70 SQL | © SmartCliff | Internal | Version 1.0

 Data Control Language

REVOKE Command: Examples

// Example 1: Revoking permission from a user

REVOKE SELECT ON HR.employees FROM Joe

• In example #1, Revoke the permission granted to Joe.

// Example 2: Revoking ALL permission

REVOKE ALL ON HR.employees FROM Joe

• In example #2, We used ALL to revoke all the permissions like SELECT, INSERT, UPDATE, DELETE,
and REFERENCES on the table for a user named Joe.

// Example 3: Revoking permission from ALL users

REVOKE INSERT ON HR.employees FROM PUBLIC

• In example #3, We are revoking the INSERT privileges given to all the users (public role).
71 SQL | © SmartCliff | Internal | Version 1.0
 Data Control Language

DENY Command

• The DENY command explicitly prevents a user from receiving a particular permission.

• This feature is helpful when a user is a member of a role or group that is granted permission,

and you want to prevent that individual user from inheriting the permission.

• The parameters for the DENY command are identical to those used for the GRANT command.

Syntax:
DENY [Permission] on [Object] TO [User]

// Example: Denying the user permission

DENY DELETE ON HR.employees TO Matthew

72 SQL | © SmartCliff | Internal | Version 1.0

 Data Query
Language(DQL)
Please download pictures in
suitable size here and insert them
by clicking the symbol above.

73 SQL | © SmartCliff | Internal | Version 1.0

 Data Query Language

Introduction

• Data Query Language(DQL) is used for fetching data from a relational database.

• They perform read-only queries of data.

• Select is one of the most important SQL commands used in the DQL.

• It is used to retrieve data from a database.

• One can fetch either the entire table or some data according to specified rules.

• The data returned by this command is called Resultset.

74 SQL | © SmartCliff | Internal | Version 1.0

 Data Control Language

SELECT Command

SYNTAX:
SELECT column1,column2,….. From table_name;

• The above syntax implies that the resultset holds the data from column 1, column 2, and so on from
the original table.

Example #1: Retrieve the specified column from the table

SELECT FirstName, Age From Employee;

• The above Example, We are retrieving the data of the column FirstName and Age from the Employee
table and after executing the above query, the resultset will be as below

75 SQL | © SmartCliff | Internal | Version 1.0

 Data Control Language

SELECT Command

• We can also retrieve all data from the table with the below syntax:

SYNTAX:
SELECT * From table_name;

• The below Example, We are retrieving the all data from the Employee table using * instead of
specifying the column name.
Example #2: Retrieve all column data from the table
SELECT * From Employee;

• After executing the above example ,the result set will be as follows

76 SQL | © SmartCliff | Internal | Version 1.0

 Data Control Language

SELECT Command

• We can also retrieve the data from the table with alias name as follows:

SYNTAX:
SELECT Column1 As C1, Column2 As C2 from table_name;

• Here C1,C2 are the alias name that will be displayed in the result set instead of the column name.

• The below Example, We are retrieving the all data from the Employee table using * instead of
specifying the column name.

Example #2: Retrieve all column data from the table

SELECT Dept As Dept from Employee;

77 SQL | © SmartCliff | Internal | Version 1.0

 SQL Statements

Quiz
2) Which SQL statement is used to extract data
from a database?

a) GET

b) EXTRACT

c) OPEN

d) SELECT

Answer : Option d)
78 SQL | © SmartCliff | Internal | Version 1.0
 SQL Statements

Quiz
3) Which of the following is used to add a new
row in the table of SQLdatabase ?

a) ADD

b) CREATE

c) MAKE

d) INSERT

Answer : Option d)
79 SQL | © SmartCliff | Internal | Version 1.0
 SQL Statements

Quiz
4) In the given query which of the keyword
must be inserted?

INSERT INTO Employee_______(11,Mano,200);

a) Table

b) Values

c) Relation

d) Field

Answer : Option b)
80 SQL | © SmartCliff | Internal | Version 1.0
 SQL Statements

Quiz
5) Which of the following is not a DDL
command?

a) DROP

b) UPDATE

c) ALTER

d) CREATE

Answer : Option b)
81 SQL | © SmartCliff | Internal | Version 1.0
 SQL Function
Please download pictures in
suitable size here and insert them
by clicking the symbol above.

82 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

SQL Functions
• Used to compute values.

• Accept arguments and return values.

• Argument can be column names or user defined values.

• Features:

– Perform calculations on data.

– Modify individual data items.

– Format dates and numbers for display.

– Convert column data types.

83 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

SQL Functions

Types

• Single row functions

• Multiple row functions or Group functions or Aggregate functions.

84 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Single Row Functions

• Act on each row that is returned by the query and gives one result per row.

• Can manipulate data items

• Can be nested to n level

• Can be used in the SELECT list, WHERE clause etc.

Types of Single row functions

– Character Functions

– Number Functions

– Date Functions

– Conversion Functions

– General Functions.

85 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Character Functions

• Accept character values as input can manipulate data items

• Return either character or number values as output.

Types

– Case Manipulation Functions

– Character Manipulation Functions

86 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Character Manipulation Functions

• Used to extract, change or format the given character string.

Types

• CONCAT: Joins two values. Accept only two arguments

Syntax:
CONCAT(‘string1’,’string2’)

Example:
SELECT concat(Name,concat ('(',concat (countrycode,')'))) NAME_CCODE FROM

city;

87 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Character Manipulation Functions

SUBSTR:

• Returns ‘n’ no. of characters from the given string and starting from the ‘m’ th position

• Accepts a max. of three arguments.

• If m is negative starts from the end of the string

Syntax: Example:
SUBSTR(‘string’,m,n) SELECT SUBSTR(COUNTRYCODE,1,2) FROM CITY;

88 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Character Manipulation Functions

LENGTH:

• Returns no. of characters (length) in the given string

• Accepts only one argument.

Syntax: Example:
LENGTH(‘string’) SELECT LENGTH(countrycode) FROM city;

89 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Character Manipulation Functions

INSTR:

• Searches for a character in the given string and returns its position

• By default, returns the position of first occurrence.

Syntax:
INSTR(‘string’,’substring’,s)

Example:
SELECT INSTR(COUNTRYCODE,'F') FROM CITY;

90 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Character Manipulation Functions

LPAD / RPAD:

• Returns the string value left/right padded with the given pad value

• The length of the whole string will be ‘n’ no.of characters.

Syntax:
LPAD(‘string’,n,’pad_value’)

Example:
SELECT LPAD(POPULATION,7,0) FROM CITY;

91 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Case Manipulation Functions

• Convert the case of the given string

• Accept only one argument

Types:

– UPPER : Convert the given string to upper case.

– LOWER: Convert the given string to lower case.

Syntax:
UPPER(‘String’ | column_name)

92 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Number Functions

• Accept number values as input and return number values as output.

• Types

• ROUND: round off the given number (n) to the nearest integer(d) places and it is optional.

Syntax:
ROUND(n,[d])

93 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Number Functions
Types

• TRUNC: Truncate the value ‘x’ up to ‘y’ decimal places .

Syntax:
TRUNC(x,y)

• MOD: returns the reminder of division

Syntax:
MOD(number,divisor)

94 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Date Functions

• The default format for display is YYYY-MON-DD

• The default format for input is ‘YYYY-MON-DD’

95 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Aggregate Functions
• Types of group functions(Aggregate Functions)

– count

– min

– max

– avg

– sum

96 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Aggregate Functions
Example: Employee Table

employee_id name department salary

1 John HR 50000
2 Alice IT 70000
3 Bob IT 60000
4 Sarah Finance 80000
5 Michael HR 45000

1. Count : Count the number of employees.

Example:
SELECT COUNT(*) AS total_employees FROM employees;

97 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Aggregate Functions
Example: Employee Table

employee_id name department salary

1 John HR 50000
2 Alice IT 70000
3 Bob IT 60000
4 Sarah Finance 80000
5 Michael HR 45000

2. MIN : Find the Mininum Salary.

Example:
SELECT MIN(salary) AS minimum_salary FROM employees;

98 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Aggregate Functions
Example: Employee Table

employee_id name department salary

1 John HR 50000
2 Alice IT 70000
3 Bob IT 60000
4 Sarah Finance 80000
5 Michael HR 45000

3. MAX : Find the Minimum Salary.

Example:
SELECT MAX(salary) AS maximum_salary FROM employees;

99 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Aggregate Functions
Example: Employee Table

employee_id name department salary

1 John HR 50000
2 Alice IT 70000
3 Bob IT 60000
4 Sarah Finance 80000
5 Michael HR 45000

4. AVG : Calculate the Average Salary

Example:
SELECT AVG(salary) AS average_salary FROM employees;

100 SQL | © SmartCliff | Internal | Version 1.0

 SQL Functions

Aggregate Functions
Example: Employee Table

employee_id name department salary

1 John HR 50000
2 Alice IT 70000
3 Bob IT 60000
4 Sarah Finance 80000
5 Michael HR 45000

5. SUM : Calculate the total sum of all salaries.

Example:
SELECT SUM(salary) AS total_salary FROM employees;

101 SQL | © SmartCliff | Internal | Version 1.0

 MySQL- Clauses

Please download pictures in

suitable size here and insert them
by clicking the symbol above.

102 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Need of Clauses

• MySQL clauses are used to provide some additional functionalities such as filtering the records,

sorting the records, fetching the records, and grouping the records .

• The following table describe the clauses used in MySQL

Clauses Description
Where Filtering the records in a table
Order By sorting the records in ascending or descending order
Limit Restrict the number of rows
Group By Grouping a set of rows
Having Filtering the data like where clause

103 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Order of the clauses

• The below table shows the order in which the clauses are used.

Order Clause Function

Tables are joined to get the base
1 FROM
data.

2 WHERE The base data is filtered.

The filtered base data is

3 GROUP BY
grouped.
The grouped base data is
4 HAVING
filtered.

5 SELECT The final data is returned.

6 ORDER BY The final data is sorted.

104 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Where Clause

• The WHERE clause filters certain records that meet the conditions mentioned in the query.

• It is evaluated second after the FROM clause.

• We can use the following operators with the WHERE clause.

Operator Description
= Equal
>= Greater than equal to
<= less than equal to
> Greater than
< Less than
<> Not equal
BETWEEN Between a range
Like Search for a pattern
105 SQL | © SmartCliff | Internal | Version 1.0
 SQL Clauses

Where Clause

• Let us understand the where clause with following tables

106 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Where Clause : Example

• The MySQL WHERE clause is not a mandatory clause of SQL DML statements, but if you want to

limit the number of rows to be affected by your DML query or the number of rows to return from

your select statement, then you need to use the Where Clause in MySQL.

• In Example#1, return all the row from the employee table under a condition that the employee must

belong to ‘Mumbai’.
Example#1: Where with Select Query
SELECT * FROM Employee WHERE CITY = ‘MUMBAI’;

107 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Where Clause : Example

• In Example #2, the SELECT statement uses the AND condition to return all the employees from the

Employee table whose GenderID is 1 and the Salary is greater than or equal to 27000.

• Since we have used AND so both conditions must be satisfied.

Example#2: Where with Multiple conditions

SELECT * FROM Employee WHERE GenderID = 1 AND Salary >= 27000;

108 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Where Clause : Example

• In Example #3, the SELECT statement uses the OR condition to return the data that satisfy either of
any one condition.

• In this case, the SELECT statement will return all ID, Name, EmailID, and CITY column values from
the Employee table where the GenderID is 1 or the Salary is greater than 29000.

Example#3: Where with OR Condition

SELECT ID, Name, EmailID, CITY FROM Employee WHERE GenderID = 1 OR Salary >= 29000;

109 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Where Clause : Example

• In Example #4, the SELECT statement uses the AND & OR condition.

• The below example uses the WHERE clause to define multiple conditions, but it combines the AND
condition and the OR condition.

Example #4: Where with both And & OR Condition

SELECT * FROM Employee WHERE (CITY = ‘MUMBAI’ AND GenderID = 1) OR (DepartmentID = 3);

110 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Where Clause : Example

• In Example #5, We used Where Clause with Update Statement (DML Statement).

• In The below example we use the Where clause to update all the Employee Salary to 37000 where
the DepartmentID is 3.

Example #5: Update Statement with Where Condition

UPDATE Employee SET Salary = 37000 WHERE DepartmentID = 3

• Once you execute the below query you will get the following output and notice the salary is updated to
37000 as expected.
SELECT * FROM Employee WHERE DepartmentId = 3;

111 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Order By Clause

• The Order By Clause in MySQL is used for sorting the data either in ascending or descending

order of a query based on a specified column or list of columns.

• That means if you want to sort the output or result of a query either in ascending or descending order

then you need to use MySQL Order by Clause.

• Following is the syntax to use Order By Clause:

Syntax:
SELECT expressions FROM tables [WHERE conditions] ORDER BY expression [ASC | DESC];

112 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Order By Clause

• Parameters used in the above syntax are as Follows

1.Expression: The columns or calculations that we want to retrieve.

2.Tables: The tables from which we want to retrieve the records. There should be at least one table
specified in the FROM clause.

3.WHERE Conditions: It is optional. The conditions must be met for the records to be selected by the
query.

4.ASC: It is optional. If you want to sort the result set in ascending order of the expression, then you
need to use ASC.

5.DESC: It is optional. If you want to sort the result set in descending order by expression, then you
need to the DESC keyword.

113 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Order By Clause

Note:

• By default, the Order By Clause in MySQL will sort the data in ascending order.

• If you want to arrange the data in descending order, then you must have to use the DESC

keyword.

• The Order By Clause can be applied to any data type column in the table.

• This clause will arrange the data temporarily but not in the permanent store.

• The Order By Clause can only be used in Select Statements.

114 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Order By Clause : Example

• We are going to use the following Employee table to understand the Order By clause in MySQL

115 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Order By Clause : Example

• In Example #1, We used order by Clause without specifying ASC or DEC and by default it will set ad
ASC.

• In The below example, all records are sorted by the Name field in ascending order

Example #1: Order By Clause

SELECT * FROM Employee ORDER BY Name;
(Or)
SELECT * FROM Employee ORDER BY Name ASC;

116 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Order By Clause : Example

• In Example #2, We used ORDER BY Clause to sort the result set in descending order.

• The below MySQL ORDER BY example will return all records sorted by the Name field in the
descending order whose Gender is Male.

Example #2: ORDER BY Clause with Where and sort out in DEC
SELECT * FROM Employee WHERE Gender = ‘Male’ ORDER BY Name DESC;

117 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Order By Clause : Example

• Notice, the result set has the Male employees and then sort the employees by name in

descending order.

NOTE:
If we are using where clause and order by clause in a single query, then first where clause gets executed and then
order by clause gets executed.

118 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Order By Clause : Example

• In Example #3, We used the ORDER BY Clause to sort with the relative position in the result set.

• Here, we specify the relative position as 1 in the order by clause.

Example #3: ORDER BY Clause with Relative position

SELECT Name, EmailID, Salary FROM Employee WHERE Salary > 26000 ORDER BY 1 DESC;

• The above MySQL ORDER BY statement would return all the records sorted by the Name field in
the descending order whose Salary is greater than 26000.

• Since the Name field is in position 1 in the select clause and would be equivalent to the following
ORDERBYclause example.

Example #3: ORDER BY Clause with Relative position

SELECT Name, EmailID, Salary FROM Employee WHERE Salary > 26000 ORDER BY Name DESC;

119 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Order By Clause : Example

• The following resultset will be returned when we execute the above query

120 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Order By Clause : Example

• In Example #4, We used the ORDER BY Clause to sort with both ASC and DEC attributes in a
single SELECT statement.

• In the following query, the Order By Clause will return all records sorted by the Gender field in
descending order, with a secondary sort by Name field in ascending order whose salary is
greater than 25000.

Example #4: ORDER BY Clause with both ASC and DEC

SELECT Name, Gender, EmailID, Salary FROM Employee WHERE Salary > 25000 ORDER BY Gender DESC,
Name ASC;

NOTE: When we have multiple columns in order by clause, the data first gets arranged based on the first column
and if any duplicate values are there in the first column then it will take the support of the second column for the
arrangement or else the second column will not be used.
121 SQL | © SmartCliff | Internal | Version 1.0
 SQL Clauses

Order By Clause : Example

• The following resultset will be returned when we execute the above query

122 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Limit Clause

• The LIMIT clause in MySQL is used to restrict the number of rows returned by a query. It is often
combined with ORDER BY to fetch a specific subset of rows.

Syntax:
SELECT column1, column2, ...
FROM table_name
[WHERE condition]
[ORDER BY column_name ASC|DESC]
LIMIT [offset,] row_count;

• row_count: The number of rows to return.

• offset (optional): The starting position to retrieve rows (0-based index). If omitted, it defaults to 0.

123 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Limit Clause

Example:
1. Fetch the first 3 employees from the Employee table

SELECT * FROM Employee LIMIT 3;

2. Fetch the top 2 highest-paid employees:

SELECT * FROM Employee ORDER BY Salary DESC LIMIT 2;

3. Fetch employees starting from the 4th row (Pagination: skip 3 rows, fetch the next 3):

SELECT * FROM Employee LIMIT 3, 3;

124 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Limit Clause

4. Fetch the youngest 5 employees

SELECT * FROM Employee ORDER BY Age ASC LIMIT 5;

5. Fetch the first 2 employees from the Payroll department, ordered by their name

SELECT * FROM Employee WHERE DepartmentID = 3 ORDER BY Name ASC LIMIT 2;

125 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Group By Clause

• The Group by Clause in MySQL is used to divide similar types of records or data as a group and

then return.

• If we use group by clause in the query then we should use groupings/aggregate functions such as

count(), sum(), max(), min(), and avg() functions.

• When we implement group by clause first the data of the table will be divided into the separate

group as per the column and later aggregate function will execute on each group’s data to get

the result.

126 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Group By Clause

• That means first Group By clause is used to divide similar types of data as a group and then an
aggregate function is applied to each group to get the required results.

Syntax:
SELECT expression1, expression2, expression_n, aggregate_function (expression)
FROM tables [WHERE conditions GROUP BY expression1, expression2, expression_n;

Arguments used in Group By Clause in MySQL:

1.expression1, expression2, expression_n: The expressions that are not encapsulated within an
aggregate function must be included in the GROUP BY clause.

2.aggregate_function: The aggregate function is nothing but such as SUM, COUNT, MIN, MAX,
or AVG functions that we should use while we are using the Group by Clause in MySQL.

127 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Group By Clause

3. Tables: Tables are nothing but the name of the table or tables from which we want to retrieve the

data.

4. WHERE conditions: It is optional. If you want to retrieve the data based on some conditions, then

you need to specify such conditions using the Where Clause in MySQL.

128 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Group By Clause : Example

• We are going to use the following Employee table to understand the Group By clause in MySQL

129 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Group By Clause: Example

• In The following example we grouped the employees by department, and then we applied the count
function to each group.
Example #1: Group By with Count
SELECT Department, COUNT(*) AS TotalEmployee FROM Employee GROUP BY Department

• When we execute the above query, we will have the following output

130 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Group By Clause: Example

• In The following example we find the highest salary in each department in the organization.

Example #2: Group By with MAX function

SELECT Department, MaxSalary = MAX(SALARY) FROM Employee GROUP BY Department

• When we execute the above query, we will have the following output

131 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Group By Clause: Example

• In The following example we get the number of employees working in each Gender per
department.

Example #3: Group By with COUNT function

SELECT Department, Gender, EmployeeCount = COUNT(*) FROM Employee GROUP BY Department, Gender
ORDER BY Department

• When we execute the above query, we will have the following output

132 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Group By Clause: Example

NOTE:

• When we use multiple columns in a group by clause first data in the table is divided based on the first column of

the group by clause and then each group is subdivided based on the second column of the group by clause and

then the group function is applied on each inner group to get the result.

• When the aggregate function is applied to a group it returns only a single value but each group can return a

value.

• Use the Group By clause only on a column that contains duplicate values, never apply it on unique columns.

133 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Group By Clause: Example

• In The following example we get the number of employees working in each Gender per
department.

Example #4: Group By with SUM and COUNT function

SELECT City, Gender, SUM(Salary) as TotalSalary, COUNT(ID) as TotalEmployees FROM Employee
GROUP BY CITY, Gender

• When we execute the above query, we will have the following output

134 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Having Clause

• The Having Clause in MySQL is used for restricting or you can say filtering the data just like the

where clause in MySQL.

• So, the Having Clause in MySQL is an additional filter that is applied to the result set.

• Logically, the having clause filters the rows from the intermediate result set that is built by using

the FROM, WHERE, or GROUP BY clauses in the SELECT statement.

• The Having clause is typically used with a GROUP BY clause.

• That means it is used in combination with a GROUP BY clause to restrict the number of groups to be

returned by satisfying the condition which is specified using the having clause.

135 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Having Clause

Syntax:
SELECT expression1, expression2, expression_n,
aggregate_function (expression)
FROM tables
[WHERE conditions]
GROUP BY expression1, expression2, expression_n
HAVING having_condition;

Arguments used in Group By Clause in MySQL:

1. aggregate_function: It can be any of the aggregate functions such as SUM, COUNT, MIN, MAX, or
AVG.

136 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Having Clause

2. expression1, expression2, expression_n: The expressions which are not encapsulated within an

aggregate function must be included in the GROUP BY clause.

3. Where Conditions: It is optional. If you want to retrieve selected records based on some conditions

then you need to specify the conditions using the Where clause in MySQL.

4. HAVING having_condition: The Having Clause Condition is used to add a further condition that can

be applied only to the aggregated results to restrict the number of groups to be returned.

137 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Having Clause: Example

• We are going to use the following Employee table to understand the Having clause in MySQL

138 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Having Clause: Example

• In case, we perform filtering of rows using where as below example

Example #1: Filter rows using WHERE

SELECT City, SUM(Salary) as TotalSalary FROM Employee WHERE City = 'MUMBAI’ GROUP BY City

• The Where clause in MySQL is used to filter the rows before aggregation, whereas the Having
clause in MySQL is used to filter the groups that mean after aggregations.

Example #2: Filter rows using HAVING

SELECT City, SUM(Salary) as TotalSalary FROM Employee GROUP BY City HAVING City = 'MUMBAI'

139 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Having Clause: Example

• The below example uses the SUM function to return the name of the department and the total Salary

(associated with the department).

• The Having Clause will filter the results so that only departments with a total Salary greater than

140000 will be returned.

Example #3: Having Clause with SUM

SELECT Department, SUM(Salary) AS Total_Salary FROM Employee GROUP BY Department HAVING
SUM(Salary) > 140000;

140 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Having Clause: Example

• The below example uses the COUNT function to return the city and the number of employees

(residing in that city).

• The Having Clause will filter the results so that only cities with more than 4 employees will be

returned.

Example #3: Having Clause with SUM

SELECT City, COUNT(*) AS 'Number of employees’ FROM Employee
GROUP BY City HAVING COUNT(*) > 4;

141 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Quiz

1) ____ clause is used to filters out the

unwanted data.

a) SELECT

b) ORDER BY

c) WHERE

Answer : Option c)

142 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Quiz

2) Which clause is used to group rows together

by common column values?

a) SELECT

b) GROUP BY

c) WHERE

Answer : Option b)

143 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Quiz

3) Which clause is used to sort the rows of the

final result set by one or more columns?

a) SELECT

b) ORDER BY

c) WHERE

Answer : Option b)

144 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Quiz

4) If you don’t specify ASC or DESC after a SQL

ORDER BY clause, the following is used by
default ______________?

a) ASC

b) DESC

c) There is no default value

c) None of the above

Answer : Option a)

145 SQL | © SmartCliff | Internal | Version 1.0

 SQL Clauses

Quiz

5) What does the following query do?

SELECT TOP(5) * FROM students;

a) Select the top 5 entries for all the column in the

table

b) Select all the entries from the table other than

top 5 entries

Answer : Option a)

146 SQL | © SmartCliff | Internal | Version 1.0

THANK YOU