Structure

d Query
Language
(SQL)
1. SQL stands for Structured Query Language. It is used for storing and
managing data in relational database management system (RDMS).
2. It is a standard language for Relational Database System. It enables a
user to create, read, update and delete relational databases and tables.
3. All the RDBMS like MySQL, Informix, Oracle, MS Access and SQL Server
use SQL as their standard database language.
4. 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.
5. SQL can perform various tasks like create a table, add data to tables,
drop the table, modify the table, set permission for users.
What Can SQL do?

 SQL can execute queries against a database

 SQL can retrieve data from a database
 SQL can insert records in a database
 SQL can update records in a database
 SQL can delete records from a database
 SQL can create new databases
 SQL can create new tables in a database
 SQL can create stored procedures in a database
 SQL can create views in a database
 SQL can set permissions on tables, procedures, and views
SQL is divided into the following sub languages

1)DDL (Data Definition language)

2)DML(Data manipulation language)
3)DCL(Data control language)
4)TCL(Transaction control language)
5)DQL(Data Query language)
SQL COMMANDS
Data definition language (DDL)
• DDL changes the structure of the table like creating a table, deleting a
table, altering a table, etc.
Here are some commands that come under DDL:
• CREATE
• ALTER
• DROP
• TRUNCATE
Creating MySQL Database

• Once MySQL is installed, connect to it using MySQL root user and

create the database as shown below.
• After connecting to MySQL root user, execute the following command
from the “mysql> ” prompt to create a database.
• The following command will create the database called “ITA”.
mysql> create database ITA;
• Use “show databases” command to verify that the database was
created successfully.
Creating MySQL Table

• Connect to the newly created database using “use” command before

you can perform any operation in the database.
• To connect to the database, do the following.
mysql> use ITA;
CREATE It is used to create a new table in the database.
Syntax:
CREATE TABLE TABLE_NAME (COLUMN_NAME DATATYPES[,....]);
• The following example creates a employee table.
create table employee (
id int,
name varchar(20),
dept varchar(10),
salary int(10)
);
The end of a SQL command is identified by a semi-colon.
• Do the following to view all the tables available in the database.
mysql> show tables;
• To view the table description, do the following.
mysql> desc employee;
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 or to
drop single attribute form the table.

1)Alter with add

2)Alter wit modify
3)Alter with drop
1) Alter with add:

Syntax:
To add a new column in the table

ALTER TABLE table_name ADD column_name COLUMN-definition;

Example:

ALTER TABLE STU_DETAILS ADD(ADDRESS VARCHAR2(20));

2)Alter with MODIFY:

To modify existing column definition in the table:

ALTER TABLE table_name

MODIFY COLUMN column_name datatype;

EXAMPLE:

alter table employee modify column salary varchar(20);

3) Alter with drop:

Syntax:
To drop the column form the table

ALTER TABLE table_name

DROP COLUMN column_name;

Example:

ALTER TABLE STU_DETAILS DROP ADDRESS;

DROP: It is used to delete both the structure and record stored in the
table.
Syntax
DROP TABLE ;

Example
DROP TABLE EMPLOYEE;
TRUNCATE:
It is used to delete all the rows from the table and free the space containing the
table.
(OR)
The SQL TRUNCATE TABLE statement is used to remove all records from a table.
It performs the same function as a DELETE statement without a WHERE clause.

Syntax:
TRUNCATE TABLE table_name;
Example:
TRUNCATE TABLE EMPLOYEE;
Data Manipulation Language:
DML commands are used to modify the database. It is responsible for all form of
changes in the database.
The command of DML is not auto-committed that means it can't permanently
save all the changes in the database. They can be rollback.
Here are some 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);
Or
INSERT INTO TABLE_NAME VALUES (value1, value2, value3, .... valueN);
Inserting Records into a Table

• Use the following sample insert commands to insert some records to

the employee table.
• While inserting values to all the columns in the table, you don’t need to
specify the column name. Just specify the values in the same sequence
as the column names.
insert into employee values(100,'Thomas','Sales',5000);
insert into employee values(200,'Jason','Technology',5500);
insert into employee values(300,'Mayla','Technology',7000);
insert into employee values(400,'Nisha','Marketing',9500);
insert into employee values(500,'Randy','Technology',6000);
• To insert values only to specific columns, you should specify the
column names as shown below.
mysql> insert into employee(name,dept) values('Ritu', 'Accounting’);
UPDATE: This command is used to update or modify the value of a column
in the table.

Syntax:
UPDATE table_name SET [column_name1= value1,...column_nameN = valueN] [WHERE CONDITION]

Example:
UPDATE students SET EMPName = ’SIVA' WHERE DEPTNAME = ’IT’;
DELETE: It is used to remove one or more row from a table.
Syntax:
DELETE FROM table_name [WHERE condition];
Example: DELETE FROM EMPLOYEE WHERE EMP NAME="SIVA";
3) Data Query Language
DQL is used to fetch the data from the database. It uses only one command
SELECT
SELECT:. It is used to select the attribute based on the condition described by
WHERE clause.
Syntax:
SELECT expressions FROM TABLES [WHERE conditions];
EXAMPLES:
SELECT * FORM TABLE EMPLYOEE

SELECT EMPNAME FROM EMPLYOEE

SELECT EMPNAME FROM EMPLYOEE WHERE SALARY=‘35000’;

Querying Records from a Table
• To view all the records from a table, use the following select
statement.
mysql> select * from employee;
• To view only a specific columns from a table, specify the column
names in the select command.
mysql> select name, dept from employee;
The SQL WHERE Clause
• The WHERE clause is used to filter records.
• The WHERE clause is used to extract only those records that fulfil a
specified condition.
• WHERE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;
• The following select statement has a where condition which displays
the employee records who belong to Technology department and
getting a salary >= 7000.
• mysql> select * from employee where dept = 'Technology' and
salary >= 6000;
The SQL SELECT DISTINCT
Statement
• Inside a table, a column often contains many duplicate values; and
sometimes you only want to list the different (distinct) values.
SELECT DISTINCT Syntax
SELECT DISTINCT column1, column2, ...
FROM table_name;
e.g. select distinct dept from employee;
 The SQL AND, OR and NOT Operators

• The WHERE clause can be combined with AND, OR, and NOT operators.
• The AND and OR operators are used to filter records based on more than one
condition:
• The AND operator displays a record if all the conditions separated by AND are
TRUE.
• The OR operator displays a record if any of the conditions separated by OR is
TRUE.
• The NOT operator displays a record if the condition(s) is NOT TRUE.
mysql> select * from employee where id=100 and dept="Technology";
mysql> select * from employee where not dept="Technology";
• Operators in The WHERE Clause

The following operators can be used in the WHERE clause:

Operator Description Example
1. = Equal
2. > Greater than
3. < Less than
4. >= Greater than or equal
5. <= Less than or equal
6. <> Not equal. Note: In some versions of SQL this operator may be written as !=
7. BETWEEN Between a certain range
8. LIKE Search for a pattern
9. IN To specify multiple possible values for a column
Examples
• SELECT * FROM Products WHERE Price = 18;
• SELECT * FROM Products WHERE Price > 30;
• SELECT * FROM Products WHERE Price <> 18;
• SELECT * FROM Products WHERE Price BETWEEN 50 AND 60;
• SELECT * FROM Customers WHERE City IN ('Paris','London');
• The SQL LIKE clause is used to compare a value to similar values using
wildcard operators. There are two wildcards used in conjunction with the
LIKE operator.

 The percent sign (%)

 The underscore (_)

• The percent sign represents zero, one or multiple characters.

• The underscore represents a single number or character.
• These symbols can be used in combinations.
Examples:
• SELECT * FROM Customers WHERE City LIKE ‘s%’;
• Select * from customer WHERE SALARY LIKE ‘200%’
Finds any values that start with 200.
• Select * from customer WHERE SALARY LIKE '%200%’
Finds any values that have 200 in any position.
• Select * from customer WHERE SALARY LIKE '_00%’
Finds any values that have 00 in the second and third positions.
• Select * from customer WHERE SALARY LIKE '%2’
Finds any values that end with 2.
• Select * from customer WHERE SALARY LIKE '_2%3’
Finds any values that have a 2 in the second position and end with a 3.
• Select * from customer WHERE SALARY LIKE ‘2___3’
Finds any values in a five-digit number that start with 2 and end with 3.
SQL ORDER BY Keyword

• The ORDER BY keyword is used to sort the result-set in ascending or

descending order.
• The ORDER BY keyword sorts the records in ascending order by
default. To sort the records in descending order, use the DESC
keyword.
• Syntax
SELECT column1, column2, ...
FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
Example:
• SELECT * FROM Customers ORDER BY Country;
• SELECT * FROM Customers ORDER BY Country DESC;

• ORDER BY Several Columns Example

• The following SQL statement selects all customers from the
"Customers" table, sorted by the "Country" and the "CustomerName"
column. This means that it orders by Country, but if some rows have
the same Country, it orders them by CustomerName:
SELECT * FROM Customers ORDER BY Country, CustomerName;
• ORDER BY Several Columns Example

• The following SQL statement selects all customers from the

"Customers" table, sorted by the "Country" and the "CustomerName"
column. This means that it orders by Country, but if some rows have
the same Country, it orders them by CustomerName:
4) Transaction Control Language
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.
Here are some commands that come under TCL:
COMMIT
ROLLBACK
SAVEPOINT
1.Commit: The COMMIT command is the transactional command used
to save changes invoked by a transaction to the database.
-The COMMIT command saves all the transactions to the database
since the last COMMIT or ROLLBACK command.
Syntax:
COMMIT;
Example:
DELETE FROM CUSTOMERS
WHERE AGE = 25;

COMMIT;
2)Rollback: Rollback command is used to undo transactions that have not already been saved to the
database.
The ROLLBACK command is the transactional command used to undo transactions that have not already
been saved to the database.
This command can only be used to undo transactions since the last COMMIT or ROLLBACK command was
issued.

Syntax:
ROLLBACK;
Example:
mysql> select * from faculty;
+------+-------+--------+
| fid | fname | salary |
+------+-------+--------+
| 1 | abc | 20000 |
| 2 | lmn | 25000 |
| 3 | xyz | 50000 |
| 4 | fgh | 40000 |
+------+-------+--------+
4 rows in set (0.00 sec)
mysql> delete from faculty where fid=3;
Query OK, 1 row affected (0.00 sec)

mysql> select * from faculty;

+------+-------+--------+
| fid | fname | salary |
+------+-------+--------+
| 1 | abc | 20000 |
| 2 | lmn | 25000 |
| 4 | fgh | 40000 |
+------+-------+--------+
3 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from faculty;

+------+-------+--------+
| fid | fname | salary |
+------+-------+--------+
| 1 | abc | 20000 |
| 2 | lmn | 25000 |
| 3 | xyz | 50000 |
| 4 | fgh | 40000 |
+------+-------+--------+
4 rows in set (0.00 sec)
mysql> insert into faculty values(5,'aaa',45000);
Query OK, 1 row affected (0.00 sec)

mysql> select * from faculty;

+------+-------+--------+
| fid | fname | salary |
+------+-------+--------+
| 1 | abc | 20000 |
| 2 | lmn | 25000 |
| 3 | xyz | 50000 |
| 4 | fgh | 40000 |
| 5 | aaa | 45000 |
+------+-------+--------+
5 rows in set (0.00 sec)
mysql> rollback;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from faculty;

+------+-------+--------+
| fid | fname | salary |
+------+-------+--------+
| 1 | abc | 20000 |
| 2 | lmn | 25000 |
| 3 | xyz | 50000 |
| 4 | fgh | 40000 |
+------+-------+--------+
4 rows in set (0.00 sec)
3. SAVEPOINT: A SAVEPOINT is a point in a transaction when you can roll the
transaction back to a certain point without rolling back the entire
transaction.
Syntax:
SAVEPOINT SAVEPOINT_NAME;

The syntax for rolling back to a SAVEPOINT is as shown below.

ROLLBACK TO SAVEPOINT_NAME;
mysql> select * from faculty;
+------+-------+--------+
| fid | fname | salary |
+------+-------+--------+
| 1 | abc | 20000 |
| 2 | lmn | 25000 |
| 3 | xyz | 50000 |
| 4 | fgh | 40000 |
| 5 | aaa | 45000 |
+------+-------+--------+
5 rows in set (0.00 sec)
• mysql> savepoint f1;
• Query OK, 0 rows affected (0.00 sec)

• mysql> delete from faculty where fid=2;

• Query OK, 1 row affected (0.00 sec)

• mysql> savepoint f2;

• Query OK, 0 rows affected (0.00 sec)

• mysql> delete from faculty where fid=3;

• Query OK, 1 row affected (0.00 sec)

• mysql> savepoint f3;

• Query OK, 0 rows affected (0.00 sec)

• mysql> delete from faculty where fid=5;

• Query OK, 1 row affected (0.00 sec)
mysql> select * from faculty; mysql> rollback to f2;
+------+-------+--------+ Query OK, 0 rows affected (0.00 sec)

| fid | fname | salary |

mysql> select * from faculty;
+------+-------+--------+ +------+-------+--------+
| 1 | abc | 20000 | | fid | fname | salary |
| 4 | fgh | 40000 | +------+-------+--------+
+------+-------+--------+ | 1 | abc | 20000 |
2 rows in set (0.00 sec) | 3 | xyz | 50000 |
| 4 | fgh | 40000 |
| 5 | aaa | 45000 |
+------+-------+--------+
4 rows in set (0.00 sec)
mysql> rollback to f1;
Query OK, 0 rows affected (0.03 sec)

mysql> select * from faculty;

+------+-------+--------+
| fid | fname | salary |
+------+-------+--------+
| 1 | abc | 20000 |
| 2 | lmn | 25000 |
| 3 | xyz | 50000 |
| 4 | fgh | 40000 |
| 5 | aaa | 45000 |
+------+-------+--------+
5 rows in set (0.00 sec)
The RELEASE SAVEPOINT Command

• The RELEASE SAVEPOINT command is used to remove a SAVEPOINT

that you have created.
• The syntax for a RELEASE SAVEPOINT command is as follows.
RELEASE SAVEPOINT SAVEPOINT_NAME;
• Once a SAVEPOINT has been released, you can no longer use the
ROLLBACK command to undo transactions performed since the last
SAVEPOINT.
5) Data Control Language:
DCL commands are used to grant and take back authority from any database user.
Here are some commands that come under DCL:
Grant
Revoke

1)Grant: It is used to give user access privileges to a database.

Example
GRANT SELECT, UPDATE ON MY_TABLE TO SOME_USER, ANOTHER_USER;
b. Revoke: It is used to take back permissions from the user.

Example
• REVOKE SELECT, UPDATE ON MY_TABLE FROM USER1, USER2;
SQL string Data types

It is used to specify a fixed length string that can

CHAR(Size) contain numbers, letters, and special characters.
Its size can be 0 to 255 characters. Default is 1.

It is used to specify a variable length string that

can contain numbers, letters, and special
VARCHAR(Size)
characters. Its size can be from 0 to 65535
characters.
 MySQL Numeric Data Types

It is used for the integer value. Its signed range

varies from -2147483648 to 2147483647 and
INT(size) unsigned range varies from 0 to 4294967295.
The size parameter specifies the max display
width that is 255.

INTEGER(size) It is equal to INT(size).

It is used to specify a floating point number. Its

size parameter specifies the total number of
FLOAT(size, d) digits. The number of digits after the decimal
point is specified by d parameter.
It is used to specify Boolean values true and
BOOL false. Zero is considered as false, and nonzero
values are considered as true.
MySQL Date and Time Data Types

It is used to specify date format YYYY-MM-DD. Its

DATE
supported range is from '1000-01-01' to '9999-12-31'.

It is used to specify date and time combination. Its format

DATETIME is YYYY-MM-DD hh:mm:ss. Its supported range is from
'1000-01-01 00:00:00' to 9999-12-31 23:59:59'.

It is used to specify the timestamp. Its value is stored as

the number of seconds since the Unix epoch('1970-01-01
TIMESTAMP 00:00:00' UTC). Its format is YYYY-MM-DD hh:mm:ss. Its
supported range is from '1970-01-01 00:00:01' UTC to
'2038-01-09 03:14:07' UTC.

It is used to specify the time format. Its format is

TIME hh:mm:ss. Its supported range is from '-838:59:59' to
'838:59:59'
It is used to specify a year in four-digit format. Values
YEAR
allowed in four digit format from 1901 to 2155, and 0000.
SQL Aggregate Functions:
• SQL aggregation function is used to perform the calculations on
multiple rows of a single column of a table. It returns a single value.
• It is also used to summarize the data.
Types of SQL Aggregation 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.
• COUNT function uses the COUNT(*) that returns the count of all the rows in
a specified table. COUNT(*) considers duplicate and Null.

Syntax
COUNT(*)
or
COUNT( [ALL|DISTINCT] expression )
example table:

PRODUCT_MAST

PRODUCT COMPANY QTY RATE COST

Item1 Com1 2 10 20
Item2 Com2 3 25 75
Item3 Com1 2 30 60
Item4 Com3 5 10 50
Item5 Com2 2 20 40
Item6 Cpm1 3 25 75
Item7 Com1 5 30 150
Item8 Com1 3 10 30
Item9 Com2 2 25 50
Item10 Com3 4 30 120
Example: COUNT()
SELECT COUNT(*) FROM PRODUCT_MAST;

Output:
10
Example: COUNT with WHERE

SELECT COUNT(*) FROM PRODUCT_MAST WHERE RATE>=20;

Output:
7
Example: COUNT() with DISTINCT

SELECT COUNT(DISTINCT COMPANY) FROM PRODUCT_MAST;

Output:
3
Example: COUNT() with GROUP BY

SELECT COMPANY, COUNT(*) FROM PRODUCT_MAST GROUP BY COMPANY;

Output:
Com1 5
Com2 3
Com3 2
SUM Function

Sum function is used to calculate the sum of all selected columns. It works on numeric
fields only.
Syntax

SUM()
or
SUM( [ALL|DISTINCT] expression )

Example: SUM()
SELECT SUM(COST) FROM PRODUCT_MAST;

Output:
670
Example: SUM() with WHERE
SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3;

Output:
320
Example: SUM() with GROUP BY

SELECT SUM(COST) FROM PRODUCT_MAST WHERE QTY>3 GROUP BY COMPANY;

Output:

Com1 150
Com2 170
Example: SUM() with HAVING

SELECT COMPANY, SUM(COST) FROM PRODUCT_MASTGROUP GROUP

BY COMPANY HAVING SUM(COST)>=170;

Output:
Com1 335
Com3 170
AVG function
The AVG function is used to calculate the average value of the numeric type. AVG function
returns the average of all non-Null values.

Syntax

AVG()
or
AVG( [ALL|DISTINCT] expression )

Example:
SELECT AVG(COST) FROM PRODUCT_MAST;

Output:
67.00
MAX Function

MAX function is used to find the maximum value of a certain column. This function
determines the largest value of all selected values of a column.

Syntax
MAX()
or
MAX( [ALL|DISTINCT] expression )
Example:
SELECT MAX(RATE) FROM PRODUCT_MAST;

30
MIN Function

MIN function is used to find the minimum value of a certain column.

This function determines the smallest value of all selected values of a
column.

Syntax

MIN()
or
MIN( [ALL|DISTINCT] expression )

Example:
SELECT MIN(RATE) FROM PRODUCT_MAST;

Output:
10
My SQL Clauses

1.WHERE
2.DISTINCT
3.ORDERD BY
4.GROUP BY
5. HAVING
GROUP BY

• SQL GROUP BY statement is used to arrange identical data into

groups.
• GROUP BY clause is used with the SELECT statement.
• In the query, GROUP BY clause is placed after the WHERE clause.
• In the query, GROUP BY clause is placed before ORDER BY clause if
used any.

• Syntax:
SELECT column FROM table_name WHERE conditions GROUP BY
column
mysql> select name,sal from emp where sal>
3000 group by name;
Syntax when GROUP BY statement is used with aggregation function.

SELECT column1, function_name(column2)

FROM table_name
WHERE condition
GROUP BY column1, column2
ORDER BY column1, column2;

• function_name: Name of the function used for example, SUM() , AVG().

• table_name: Name of the table.
• condition: Condition used.
• Example:
Group By single column: Group By single column means, to place all the rows with
same value of only that particular column in one group. Consider the query as
shown below:
mysql> select name,sum(sal) from emp group by name;
Group By multiple columns: Group by multiple column is say for
example, GROUP BY column1, column2.
• This means to place all the rows with same values of both the
columns column1 and column2 in one group. Consider the below
query:
SELECT SUBJECT, YEAR, Count(*)
FROM Student
GROUP BY SUBJECT, YEAR;
Group by with order by
Example:

select name,sum(sal),age from emp group by name order by age;

select name,sum(sal),age from emp group by name order by age desc;
HAVING Clause

• The HAVING clause was added to SQL because the WHERE keyword could not be used
with aggregate functions.
• Syntax:
SELECT column1, function_name(column2)
FROM table_name
WHERE condition
GROUP BY column1, column2
HAVING condition
ORDER BY column1, column2;

• function_name: Name of the function used for example, SUM() , AVG().

• table_name: Name of the table.
• condition: Condition used.
• Example:
SELECT NAME, SUM(SALARY) FROM Employee
GROUP BY NAME
HAVING SUM(SALARY)>3000;

Output:
select name,sum(sal) from emp group by
name having sum(sal)>1000 order by name;