SQL, the Structured
Query Language
Overview
Introduction
DDL Commands
DML Commands
SQL Statements, Operators, Clauses
Aggregate Functions
Structured Query Language
SQL is a non procedural language that is used to create,
manipulate and process the databases(relations).
Characteristics of SQL
It is very easy to learn and use.
Large volume of databases can be handled quite easily.
It is non procedural language. It means that we do not need
to specify the procedures to accomplish a task but just to give
a command to perform the activity.
SQL can be linked to most of other high level languages
that makes it first choice for the database programmers.
Structured Query Language
Processing Capabilities of SQL
The following are the processing capabilities of SQL.
1.Data Definition Language (DDL)
DDL contains commands that are used to create the tables,
databases, indexes, views, sequences and synonyms etc.
e.g: Create table, create view, create index, alter table
Structured Query Language
Processing Capabilities of SQL
2. Data Manipulation Language (DML)
DML contains command that can be used to manipulate the
data base objects and to query the databases for information
retrieval.
e.g Select, Insert, Delete, Update.
Structured Query Language
Processing Capabilities of SQL
3. View Definition:
DDL contains set of command to create a view of a relation.
e.g : create view
4. Data Control Language:
This language is used for controlling the access to the data.
The commonly used commands DCL are,
GRANT, REVOKE
Structured Query Language
Processing Capabilities of SQL
5. Transaction Control Language (TCL)
TCL include commands to control the transactions in a data
base system. The commonly used commands in TCL are
COMMIT, ROLLBACK
SQL Commands
Operators in SQL:
The following are the commonly used operators in SQL
Arithmetic Operators +, -, *, /
Relational Operators =, <, >, <=, >=, <>
Logical Operators OR, AND, NOT
Arithmetic operators are used to perform simple arithmetic
operations.
Relational Operators are used when two values are to be
compared and
Logical operators are used to connect search conditions in
the WHERE Clause in SQL.
Example:
CREATE TABLE FoodCart
(
date varchar(10), FoodCart
food varchar(20), date food profit
profit float
);
FoodCart
ALTER TABLE FoodCart ( date food profit sold
ADD sold int
);
FoodCart
ALTER TABLE FoodCart( date food sold
DROP COLUMN profit
);
DROP TABLE FoodCart;
SQL: DML Commands
INSERT: adds new rows to a table.
UPDATE: modifies one or more attributes.
DELETE: deletes one or more rows from a table.
SQL: INSERT Statement
To insert a row into a table, it is necessary to
have a value for each attribute, and order matters.
INSERT statement syntax:
INSERT into <table name>
VALUES ('value1', 'value2', NULL);
Example: INSERT into FoodCart
VALUES (’02/26/08', ‘pizza', 70 );
FoodCart
date food sold
date food sold 02/25/08 pizza 350
02/25/08 pizza 350 02/26/08 hotdog 500
02/26/08 hotdog 500 02/26/08 pizza 70
SQL: UPDATE Statement
To update the content of the table:
UPDATE statement syntax:
UPDATE <table name> SET <attr> = <value>
WHERE <selection condition>;
Example: UPDATE FoodCart SET sold = 349
WHERE date = ’02/25/08’ AND food = ‘pizza’;
FoodCart
date food sold date food sold
02/25/08 pizza 350 02/25/08 pizza 349
02/26/08 hotdog 500 02/26/08 hotdog 500
02/26/08 pizza 70 02/26/08 pizza 70
SQL: DELETE Statement
To delete rows from the table:
DELETE statement syntax:
DELETE FROM <table name>
WHERE <condition>;
Example: DELETE FROM FoodCart
WHERE food = ‘hotdog’;
FoodCart
date food sold date food sold
02/25/08 pizza 349 02/25/08 pizza 349
02/26/08 hotdog 500 02/26/08 pizza 70
02/26/08 pizza 70
Note: If the WHERE clause is omitted all rows of data are deleted from the table.
StudentTable
Roll_n Name Class Marks City
o
101 Rohan XI 400 Chennai
102 Aneeta XII 390 Bengaluru
103 Pawan Kumar IX 298 Mysore
104 Rohan IX 376 Mangalore
105 Sanjay VII 240 Mumbai
113 Anju VIII 432 Delhi
SQL Statements, Operations, Clauses
SQL Statements:
Select
SQL Operations:
Join
Left Join
Right Join
Like
SQL Clauses:
Order By
Group By
Having
SQL: SELECT Statement
A basic SELECT statement includes 3 clauses
SELECT <attribute name> FROM <tables> WHERE <condition>
SELECT FROM WHERE
Specifies the Specifies the Specifies the
attributes that are tables that serve selection condition,
part of the as the input to the including the join
resulting relation statement condition.
Note: that you don't need to use WHERE
SQL: SELECT Statement (cont.)
Using a “*” in a select statement indicates that
every attribute of the input table is to be
selected.
Example: SELECT * FROM … WHERE …;
To get unique rows, type the keyword
DISTINCT after SELECT.
Example: SELECT DISTINCT * FROM …
WHERE …;
Example: 1) SELECT *
Person FROM person
Name Age Weight WHERE age > 30;
Harry 34 80 Name Age Weight
Sally 28 64 Harry 34 80
George 29 70 Helena 54 54
Helena 54 54 Peter 34 80
Peter 34 80
2) SELECT weight 3) SELECT distinct weight
FROM person FROM person
WHERE age > 30; WHERE age > 30;
Weight Weight
80 80
54 54
80
SQL Commands
Conditions based on a range
SQL provides a BETWEEN operator that defines a range of
values that the column value must fall for the condition to
become true.
e.g. SELECT Roll_no, name FROM student WHERE
Roll_no BETWENN 100 AND 103;
The above command displays Roll_no and name of those
students whose Roll_no lies in the range 100 to 103 (both
100 and 103 are included in the range).
SQL Commands
Conditions based on a list
To specify a list of values, IN operator is used. This operator
select values that match any value in the given list.
e.g. SELECT * FROM student WHERE city IN
(‘Bengaluru’,’Delhi’,’Chennai’);
The above command displays all those records whose city is
either Bengaluru or Delhi or Chennai
SQL Commands
Conditions based on a list
To specify a list of values, IN operator is used. This operator
select values that match any value in the given list.
e.g. SELECT * FROM student WHERE city IN
(‘Bengaluru’,’Delhi’,’Chennai’);
The above command displays all those records whose city is
either Bengaluru or Delhi or Chennai
SQL Commands
Conditions based on Pattern
SQL provides two wild card characters that are used while
comparing the strings with LIKE operator.
a. percent ( % ) Matches any string
b.Underscore ( _ ) Matches any one character
e.g SELECT Roll_no, name, city FROM student WHERE
Roll_no LIKE “%3”;
displays those records where last digit of Roll_no is 3 and
may have any number of characters in front.
SQL Commands
Conditions based on Pattern
e.g SELECT Roll_no, name, city FROM student WHERE
Roll_no LIKE “1_3”;
displays those records whose Roll_no starts with 1 and
second letter may be any letter but ends with digit 3.
SQL Commands
ORDER BY Clause
ORDER BY clause is used to display the result of a query in
a specific order(sorted order).
The sorting can be done in ascending or in descending order.
It should be kept in mind that the actual data in the database
is not sorted but only the results of the query are displayed in
sorted order.
e.g. SELECT name, city FROM student ORDER BY
name;
The above query returns name and city columns of table
student sorted by name in increasing/ascending order.
SQL Commands
ORDER BY Clause
e.g. SELECT * FROM student ORDER BY city DESC;
It displays all the records of table student ordered by city in
descending order.
Note:- If order is not specifies that by default the
sorting will be performed in ascending order.
SQL Commands
GROUP BY Clause
The GROUP BY clause can be used in a SELECT statement
to collect data across multiple records and group the results
by one or more columns.
The syntax for the GROUP BY clause is:
SELECT column1, column2, ... column_n,
aggregate_function (expression)
FROM tables
WHERE conditions
GROUP BY column1, column2, ... column_n;
SQL Commands
GROUP BY Clause
aggregate_function can be a function such as SUM, COUNT,
MAX, MIN, AVG etc.
e.g SELECT name, COUNT(*) as "Number of
employees“ FROM student WHERE marks>350
GROUP BY city;
SQL: Join operation
A join can be specified in the FROM clause
which list the two input relations and the
WHERE clause which lists the join condition.
Example:
Emp Dept
ID State ID Division
1000 CA 1001 IT
1001 MA 1002 Sales
1002 TN 1003 Biotech
SQL: Join operation (cont.)
inner join = join
SELECT *
FROM emp join dept (or FROM emp, dept)
on emp.id = dept.id;
Emp.ID Emp.State Dept.ID Dept.Division
1001 MA 1001 IT
1002 TN 1002 Sales
SQL: Join operation (cont.)
left outer join = left join
SELECT *
FROM emp left join dept
on emp.id = dept.id;
Emp.ID Emp.State Dept.ID Dept.Division
1000 CA null null
1001 MA 1001 IT
1002 TN 1002 Sales
SQL: Join operation (cont.)
right outer join = right join
SELECT *
FROM emp right join dept
on emp.id = dept.id;
Emp.ID Emp.State Dept.ID Dept.Division
1001 MA 1001 IT
1002 TN 1002 Sales
null null 1003 Biotech
SQL: Like operation
Pattern matching selection
% (arbitrary string)
SELECT *
FROM emp
WHERE ID like ‘%01’;
finds ID that ends with 01, e.g. 1001, 2001, etc
_ (a single character)
SELECT *
FROM emp
WHERE ID like ‘_01_’;
finds ID that has the second and third character as
01, e.g. 1010, 1011, 1012, 1013, etc
SQL: The ORDER BY Clause
Ordered result selection
desc (descending order)
SELECT *
FROM emp
order by state desc
puts state in descending order, e.g. TN, MA, CA
asc (ascending order)
SELECT *
FROM emp
order by id asc
puts ID in ascending order, e.g. 1001, 1002, 1003
SQL: The GROUP BY Clause
The function to divide the tuples into groups and
returns an aggregate for each group.
Usually, it is an aggregate function’s companion
SELECT food, sum(sold) as totalSold
FROM FoodCart
group by food;
FoodCart
date food sold food totalSold
02/25/08 pizza 349 hotdog 500
02/26/08 hotdog 500 pizza 419
02/26/08 pizza 70
SQL Commands
HAVING Clause
The HAVING clause is used in combination with the GROUP
BY clause. It can be used in a SELECT statement to filter the
records that a GROUP BY returns.
The syntax for the HAVING clause is:
SELECT column1, column2, ... column_n,
aggregate_function (expression)
FROM tables
WHERE predicates
GROUP BY column1, column2, ... column_n
HAVING condition1 ... condition_n;
SQL Commands
HAVING Clause
e.g SELECT SUM(marks) as "Total marks"
FROM student
GROUP BY department
HAVING SUM(sales) > 1000;
Note: select statement can contain only those attribute
which are already present in the group by clause.
SQL: The HAVING Clause
The substitute of WHERE for aggregate functions
Usually, it is an aggregate function’s companion
SELECT food, sum(sold) as totalSold
FROM FoodCart
group by food
having sum(sold) > 450;
FoodCart
date food sold food totalSold
02/25/08 pizza 349 hotdog 500
02/26/08 hotdog 500
02/26/08 pizza 70
SQL: Aggregate Functions
Are used to provide summarization information for
SQL statements, which return a single value.
COUNT(attr)
SUM(attr)
MAX(attr)
MIN(attr)
AVG(attr)
Note: when using aggregate functions, NULL values
are not considered, except in COUNT(*) .
SQL: Aggregate Functions (cont.)
FoodCart
date food sold
02/25/08 pizza 349
02/26/08 hotdog 500
02/26/08 pizza 70
COUNT(attr) -> return # of rows that are not null
Ex: COUNT(distinct food) from FoodCart; -> 2
SUM(attr) -> return the sum of values in the attr
Ex: SUM(sold) from FoodCart; -> 919
MAX(attr) -> return the highest value from the attr
Ex: MAX(sold) from FoodCart; -> 500
SQL: Aggregate Functions (cont.)
FoodCart
date food sold
02/25/08 pizza 349
02/26/08 hotdog 500
02/26/08 pizza 70
MIN(attr) -> return the lowest value from the attr
Ex: MIN(sold) from FoodCart; -> 70
AVG(attr) -> return the average value from the attr
Ex: AVG(sold) from FoodCart; -> 306.33
Note: value is rounded to the precision of the datatype
References
Riccardi, Greg. Principles of Database Systems with Internet and Java
Applications. Addison Wesley, 2001.
Ronald R. Plew, Ryan K. Stephens. Teach Yourself SQL in 24 Hours 3rd Edition.
Sams Publishing, 2003.
SQL http://en.wikipedia.org/wiki/SQL
W3C http://www.w3schools.com/sql/sql_tryit.asp
Wikipedia - SQL http://en.wikipedia.org/wiki/SQL
Wikipedia - join http://en.wikipedia.org/wiki/Join_(SQL)
