STRUCTURED QUERY

LANGUAGE (SQL)
▪ Query is a question or requesting information.
Query language is a language which is used to
retrieve information from a database.
➢Procedural Query Language: Here the user
instruct the system to perform a sequence of
operation in order to produce desired result. User
tells what to be retrieved and how to be retrieved
➢Non-Procedural Query Language: Here user
described the desired information without giving
the specific procedure for obtaining that
information.
➢Relational Algebra is a Procedural Query
Language.
➢SQL is non-procedural Language.
➢Relational Calculus is a declarative(non-
procedural) language
➢Tuple Relational Calculus is a non-
procedural query language.
➢Domain Relational Calculus is a non-
procedural query language.
 SQL
▪ A standard specifies how
➢A relational schema is created
➢Data is inserted/Updated in relations
➢Data is queried
➢Transactions are started and stopped
➢Program access data in relations
▪ Every relational database system (RDMS) is
required to support/implement the SQL standard
 SQL
❑ SQL is a short-form of the structured query
language, and it is pronounced as S-Q-L or
sometimes as See-Quell.
❑ SQL is a computer language for storing,
manipulating and retrieving data stored in a
relational database.

❑SQL can do much more than just a query

database, can define structure of database,
modify data in database , specify security
constraints and number of other tasks.
❑SQL is standard language for Relational
database system.

❑ All relational database management system

like MySQL, MS Access, Oracle, and SQL
Server use SQL as standard database
language.

❑Declarative language based on Relational

Algebra i.e. you write exactly what you want
and the queries, no need to describe how to get
the data out of database.
➢1970 – Dr. Edger F. “Ted” Codd of IBM is known as the
father of relational databases a relational model. He
described a relational model for databases.
➢1974 – Structured Query Language appeared
➢1978 – IBM worked to develop codd ideas and release
a product named system/R.
➢1986 – IBM developed the first prototype of relational
database and standardized by ANSI. The first relational
database was released by relational software which
later came to be known as Oracle
SQL STANDARDS
2003 SQL:2003

2006 SQL:2006

2008 SQL:2008

2011 SQL:2011

2016 SQL:2016

2019 SQL:2019
SQL Process
• RDBMS stands for Relational Database Management
System. RDBMS is the basis for SQL, and all modern
database systems like MS SQL Server, IBM DB2, Oracle,
MySQL, and Microsoft Access

• A Relational Database Management System (RDBMS) is

a Database management system (DBMS) that is based
on the relational model as introduced by E.F. Codd.

• The data in RDBMS is stored in database objects which

are called as tables.
➢Char(n): A fixed-length character string with user-
specified maximum length ‘n’. The full form,
CHARACTER, can be used instead.
➢VARCHAR(n): A variable-length character string with
user specified maximum length n. The full form,
CHARACTER VARING, can also be used.
➢INT: An integer(a finite subset of the integers that is
machine dependent). The full form, INTEGER, is
equivalent.
➢SMALLINT: also denotes integer, but the number of
integers permitted may be less, depending on the
implementation.
➢ Boolean: It denotes an attribute whose value is
logical. The possible value of such an attribute are
TRUE or FALSE
➢ REAL or DOUBLE PRECISION: Floating-point and
double precision floating point number with
precision of at least n digits.
➢ FLOAT (n): A floating-point number with precision
of at least n digits.
➢ NUMERIC (p, d): A fixed-point number with user
specified precision. The number consist of p digit
(plus a sign), and d out of p digits are to the right
of the decimal point. Thus, NUMERIC(4,2) allows
63.21 to be stored exactly but neither, 632.1 or
6.321 can stored in a field of this type.
➢ DATE: A calendar date containing a four digit
year, two digit month and two digit day.
➢ TIME: The time of day, in hours, minutes, and
seconds. A variant, TIME(p), can be used to
specify the number of fractional digits for
seconds (the default being 0).
➢ TIMESTAMP: A combination of date and time; A
variant, TIMESTAMP(P) can be used to specify
the number of fractional digits for seconds.
 OPERATORS IN SQL
➢An operator is a reserved word or a character used
primarily in an SQL statement's WHERE clause to
perform operation(s), such as comparisons and
arithmetic operations, such as comparisons and
arithmetic operations.
➢Operators are used to specify conditions in an SQL
statement and to serve as conjunctions for multiple
conditions in a statement.
➢Arithmetic operators(+,-,*,/,%)
➢Comparison Operators(>,<,>=,<=,=,!=,<>,!<,!>)
 Operator & Description
ALL
The ALL operator is used to compare a value to all values in another value
set.

AND
The AND operator allows the existence of multiple conditions in an SQL
statement's WHERE clause.

ANY
The ANY operator is used to compare a value to any applicable value in
the list as per the condition.

BETWEEN
The BETWEEN operator is used to search for values that are within a set
of values, given the minimum value and the maximum value.

EXISTS
The EXISTS operator is used to search for the presence of a row in a
specified table that meets a certain criterion.
IN
The IN operator is used to compare a value to a list of literal values that have been
specified.

LIKE
The LIKE operator is used to compare a value to similar values using wildcard
operators.

NOT
The NOT operator reverses the meaning of the logical operator with which it is
used. E.g.: NOT EXISTS, NOT BETWEEN, NOT IN, etc. This is a negate operator.

OR
The OR operator is used to combine multiple conditions in an SQL statement's
WHERE clause.

IS NULL
The NULL operator is used to compare a value with a NULL value.

UNIQUE
The UNIQUE operator searches every row of a specified table for uniqueness (no
➢Any SQL command is a combination of Keyword,
identifier and clauses.
➢Every SQL command begins with a keyword
(CREATE, SELECT, DELETE and so on) which has a
specific meaning to the language.

SELECT Name FROM Students WHERE RegNO = 10

➢SELECT, FROM, and WHERE are keywords
➢The clause are “FROM students” and “WHERE
RegNo= 10”.
➢ Here SELECT and FROM are mandatory, but
WHERE is optional.

➢ Name, Student, RegNO, are identifiers that refers

to objects in the database.

➢ Name and RegNo are column names, while

Student is a table name.

➢ The equal sign is an operator and 10 is a numeric

constant.
The syntax of the structured query language is a
unique set of rules and guidelines, which is not case-
sensitive. Its Syntax is defined and maintained by the
ISO and ANSI standards.
Important Points:
➢ You can write the keywords of SQL in both uppercase
and lowercase, but writing the SQL keywords in
uppercase improves the readability of the SQL query.
➢ SQL statements or syntax are dependent on text lines.
We can place a single SQL statement on one or multiple
text lines.
➢ You can perform most of the action in a database with
SQL statements.
➢ SQL syntax depends on relational algebra and tuple
relational calculus.
➢NOT NULL Constraint- Ensures that a column cannot have null
values

➢Default Constraints- Provides a default for a column when

none is specified.

➢UNIQUE Constraints- Ensures that all the values in the column

are different.

➢PRIMARY Key- Uniquely identifies each row or record in a

database table.
➢ FOREIGN Key- Uniquely identifies a
row/record in any other database table.

➢ CHECK Constraints- Ensures that all

values in the column satisfy certain
condition.

➢ INDEX- Used to create and retrieve data

from the database very quickly
 Data integrity
➢Entity Integrity: There are no duplicate rows in a table.
➢Domain Integrity: Enforces valid entries for a given
column by restricting the type, the format, or the range
of values.
➢Referential Integrity: Rows cannot be deleted, which are
used by other records.
➢User-Defined Integrity: Enforces some specific business
rules that do not fall into entity, domain or referential
integrity.
 SQL Commands
➢Data Definition Language(DDL)

➢Data Manipulation Language(DML)

➢Data Control Language(DCL)

➢Transaction Control Language(TCL)

❑ The standard SQL commands to interact
with relational databases are CTEATE,
SELECT, INSERT, UPDATE, DELETE, DROP etc.

❑ These commands can be classified into

groups based on their nature.
➢ SQL commands that can be used to define the
database schema(Structure).

➢DDL is a set of SQL commands used to create,

modify, and delete database structures but not data.

➢These commands are normally not used by a general

user, who should be accessing the database via an
application.
 Data Manipulation
Language(DML)
➢The DML commands in Structured Query
Language change the data present in the SQL
database.

➢We can easily access, store, modify, update and

delete the existing records from the database
using DML commands.
 Data Control Language(DCL)
• The SQL commands that implement security on database
objects like table, view, stored procedure etc. GRANT and
REVOKE commands belongs to this category.
Transaction Control Language (TCL)
➢These SQL commands manage the transactions in SQL
databases.

➢ It also helps to save the change into database

permanently.

➢COMMIT, ROLLBACK, SET TRANSACTION and SAVEPOINT

commands belongs to this category.
 Database Commands
➢CREATE DATABASE DatabaseName;
➢SHOW DATABASE;
➢DROP DATABASE testDB;
➢USE DatabaseName;
Table definition example
CREATE TABLE Student
RollNo CHAR(8) NOT NULL,
Name VARCHAR(15) NOT NULL,
Sex CHAR(1) Default=‘M’
DeptNo SMALLINT,
PRIMARY KEY (RollNo),
FORIGN KEY (DeptNo) REFERENCES Department
(DeptId) ON DELETE SET NULL. ON UPDATE CASCADE;
 CREATE Table
• Defining the schema of a relation:
• Create table r (attribute Definition 1, attribute
Definition 2…..attribute Definition n, [integrity
constraint 1], [integrity constraint 2]……[integrity
constraint m] );
• Attribute Definition-
• Attribute name domain type [integrity constraints]
Integrity Constraints in SQL:
Integrity constraints are used to ensure accuracy and
consistency of data in a relational database. There are
many type of integrity constraints.
➢NOT NULL Constraints: It specifies that value of a
column cannot be null.
➢Default Constraint: It is used to define a default value for
an attribute.
➢UNIQUE Constraints: It specifies that value of two rows
for a specific column Must be unique or different.
➢CHECK Constraints: It can be utilized to check the
validity of data entered into particular table columns.
➢ PRIMARY KEY Constraint: It specifies one or more
attributes that make up the primary key of the
table.

➢ FOREIGN KEY Constraint: It is used to enforce

referential integrity between tables in a relational
database. A column defined as a foreign key is
used to reference a column defines as a primary
key in another table.
 Specifying key constraints:
PRIMARY KEY (A1, A2,….Ak)
Specifies that [A1, A2,….Ak] is the primary key of the
table.
There can be only one primary key constraint.

FOREIGN KEY (A1) REFERENCES r2 (B1)

Specifies that attribute A1 of table being define, say r1 is
a foreign key referring to attribute B1 of table r2.

This Means- Each value of a column A1 is either null or

is one of the values appearing in column B1 of r2.
Specifying what to do if referential integrity
constraint violation occurs:
RIC violation
1) Can occur if a reference tuple is deleted or modified
2) Action can be specified for each case using qualifiers ON DELETE or
UPDATE
Actions
1) Three possibilities can be specified
SET NULL, SET DEFAULT, CASCADE
2) These are action to be taken on referencing tuple
SET NULL- foreign key attribute value to be set null.
SET DEFAULT- foreign key attribute value to be set to its default value.
CASCADE- delete the referencing tuple if the referenced tuple is deleted
or updated the FK attribute if the referenced tuple is updated.
 INSERT Query
Syntax
➢ INSERT INTO TABLE_NAME (column1, column2,
column3,……column N) VALUES(value1, value2,
value3,…..valueN);

➢ INSERT INTO TABLE_NAME VALUES

(value1, value2 value3,……valueN);
➢ INSERT INTO CUSTOMERS (ID, NAME, AGE, ADDRESS, SALARY)

VALUES(1, ‘Shiksha’, 32, ‘Ahmedabad’, 2000.00);

➢ INSERT INTO CUSTOMERS
(ID, NAME, AGE, ADDRESS, SALARY) VALUES (2, ‘Ruchi’,25,
‘Delhi’,1500.00);

➢ INSERT INTO CUSTOMER VALUES (7, ‘Abhishek’, 24,

‘Indore’,10000.00);
➢ INSERT INTO first_table_name [(column1, column2,…..columnN)]
Schema change Statements
➢The Drop Command- The drop command can be used to
drop named schema, elements such as tables, domains or
constraints

➢Two drop behavior

➢ Cascade- if all related elements are to be deleted with
this drop
➢ Restrict- Can be dropped only if no dependent
elements are
➢ Drop schema company cascade
➢ Drop table company cascade
 Alter Command
➢Alter table emp add column job varchar(20);
➢Alter table emp drop column job cascade;
➢Alter table emp alter column job drop default;
➢Alter table emp alter column job set default “Manager”;
 UPADTE Query
➢UPDATE table_name SET column1= value1,column2=
value2….,columnN= valueN WHERE [condition];
➢UPDATE CUSTOMERS SET ADDRESS = ‘Pune’ WHERE ID = 6;

DELETE Query
➢DELETE FROM table_name WHERE [condition];
➢DELETE FROM CUSTOMERS;
 SELECT Query
➢SELECT Column1, column2, column FROM table_name;
➢SELECT * FROM table_name;
➢SELECT column1, column2, column FROM table_name
WHERE [Condition];
➢SELECT column1, column2, column FROM table_name
WHERE [condition] AND [condition2]….AND [conditionN];
➢SELECT column1, column2, columnN FROM table_name
WHERE [condition1] OR [condition2]…OR [conditionN];
AND, OR, and NOT Operators
➢The WHERE clause can be combined with AND, OR and NOT
operators.
➢The AND operator displays a record if all the conditions
separated by AND are TRUE.
➢SELECT column1, column2,….FROM table_name WHERE
condition1 AND condition2 AND condition3…;
➢The OR operator displays a record if any of the conditions
separated by OR is TRUE.
➢SELECT column1, column2,…FROM table_name WHERE
condition1 OR condition2 OR condition3…;
➢The NOT operator displays a record if the condition(s) is not
true.
 Order by clause
➢SELECT column-list FROM table_name [WHERE condition]
[ORDER BY column1, column2…columnN] [ASC | DESC];

➢SELECT * FROM USTOMERS ORDER BY NAME, SALARY;

 LIKE Clause
➢There are two wildcards used in conjuction 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
➢SELECT FROM table_name WHERE column LIKE ’XXXX%’ or

➢SELECT FROM table_name WHERE column LIKE ’%XXXX%’

or

➢SELECT FROM table_name WHERE column LIKE ’XXXX_’ or

➢SELECT FROM table_name WHERE column LIKE ’_XXXX’ or

➢SELECT FROM table_name WHERE column LIKE ’_XXXX_’

Aggregate functions perform Calculation on a set of
values and return a single value. They ignore NULL values
except COUNT and are used with GROUP BY clause of
SELECT statement.
Types of aggregate functions
The different types of aggregate functions are −
•AVG
•MAX
•MIN
•SUM
•COUNT()
.COUNT(*)
➢The MIN() function returns the smallest value of the selected
column.

➢The MAX() function returns the largest value of the selected

column.

➢The COUNT() function returns the number of rows that matches

a specified criterion

➢The AVG() function returns the average value of a numeric

column.

➢The SUM() function returns the total sum of a numeric column.

1) Average function:
The keyword used to calculate the average of given
items is AVG. It returns the average of the data
values.
Syntax
The syntax is as follows −
SELECT <column name> from < table name>
Maximum function:
The keyword used to return maximum value for a column
is MAX.
Syntax
The syntax is as follows –
SELECT MAX <column name> from <table name>

Let’s consider an employee table. We will perform the

calculations on this table by using aggregate functions.

EXAMPLE: select max(salary) from employee

OUTPUT: max(salary) = 30,000

MINIMUM FUNCTION:
The keyword used to return the minimum value for a
column is MIN.
Syntax
The syntax is as follows −
SELECT MIN <column name> from <table name>;

EXAMPLE: SELECT MIN (salary ) from employee;

OUTPUT : min (salary) =4000
SUM function:
It returns the sum(addition) of the data values. The keyword
used to perform addition on data items is SUM.

SYNTAX: SELECT sum(<column name>) from <table name>

COUNT function
It returns the total number of values in a given column.
Syntax
The syntax is given below −

SELECT COUNT <column name> from <table name>

COUNT(*) function
It returns the number of rows in a table.
Syntax
The syntax is as follows −

SELECT COUNT(*) from <table name>;

 Group by Clause
➢The GROUP BY statement groups rows that have the same values
into summary rows, like “find the number of emp in each city”.
➢The GROUP BY statement in SQL is used to arrange identical data
into groups with the help of some functions. i.e. if a particular
column has same values in different rows then it will arrange these
rows in a group.
➢The GROUP BY statement is often used with aggregate functions
(COUNT(), MAX(), MIN(), SUM(), AVG()) to group the result- set by
one or more columns.
➢Note
➢ GROUP BY clause is used with 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.
➢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.
 Having Clause
➢We can use HAVING clause to place conditions to decide
which group will be the part of final result-set. Also we can
not use the aggregate function like SUM(), COUNT() etc.
with WHERE clause . So we have to use HAVING clause if we
want to use any of these functions in the conditions.

➢SELECT column1, function_name(column2) FROM

table_name WHERE condition GROUP BY column1, column2
HAVING condition ORDER BY column1, column2;
 BETWEEN Operator
➢The BETWEEN operator selects values within a given
range. The values can be number, text or dates.

➢The BETWEEN operator is inclusive: begin and end values

are included.

➢SELECT column_name(s) FROM table_name WHERE

column_name BETWEEN value1 and value2;
 Aliases
➢SQL aliases are used to give a table, or a column in a table,
a temporary name.
➢Aliases are often used to make column names more
readable.
➢An alias only exists for the duration of that query.
➢An alias is created with the AS keyword.
➢SELECT column_name AS alias_name FROM table_name;
➢SELECT column_name(s) FROM table_name AS alias name;
 IN Operator
➢The IN operator allows you to specify multiple values in a
WHERE clause.
➢The IN operator is a shorthand for multiple OR conditions.
➢SELECT column_name(s) FROM table_name WHERE
column_name IN (value1, value2,….);
➢SELECT column_name(s) FROM table_name WHERE
column_name IN (SELECT STATEMENT)
 JOIN
➢The SQL join clause is used to combine records from two or
more tables in a database. A JOIN is a means for combining
fields from two tables by using values common to each.
➢There are the different types of the JOINs in SQL:
➢ (INNER) JOIN: Returns records that have matching values
in both tables
➢ LEFT (OUTER) JOIN: Returns all records from the left
table, and the matched records from the right table
➢ RIGHT (OUTER) JOIN: Returns all records from the right
table, and matched records from the left table
➢ FULL (OUTER) JOIN: Returns all records when there is a
match in either left or right table.
 INNER JOINS
➢SELECT table1.column1, table2.column2….
FROM table1
INNER JOIN table2
ON table1.common_field = table2.common_field;
 LEFT JOINS
➢ SELECT table1.column1, table2.column2….
FROM table1
LEFT JOIN table2
ON table1.common_field = table2.common_field;
 RIGHT JOINS
➢ SELECT table1.column1, table2.column2….
FROM table1
RIGHT JOIN table2
ON table1.common_field = table2.common_field;
 FULL JOINS
➢SELECT table1.column1, table2.column2….
FROM table1
FULL JOIN table2
ON table1.common_field = table2.common_field;
 UNION
• SELECT column_1, column_2,...column_n FROM
table_1
UNION
SELECT column_1, column_2,...column_n FROM
table_2;

➢The Union clause is used to combine two separate

selected statements and produce the result set as a union
of both the selected statements.
➢NOTE:
To use UNION in SQL, we must always remember,

➢Column count in all tables must be the same. For example,

Teachers and Students both tables have three columns.
➢The data types of columns must be the same. For example,
the age column in Teachers is integer, so is the age in
Students table.
➢The columns must be in the same order in each table. For
example, the order of columns is id-name-age in Teachers,
so in the Students table.
 UNION ALL
• SELECT column_1, column_2,...column_n FROM table_1
UNION ALL
SELECT column_1, column_2,...column_n FROM table_2;
 INTERSECT
• SELECT column_1, column_2,...column_n FROM
table_name
INTERSECT
SELECT column_1, column_2,...column_n FROM
table_name;
 ANY and ALL Operators
• The ANY and ALL operators allow you to perform a
comparison between a single column value and a range of
other values.

SQL ANY Operator

The ANY operator:
➢ returns a boolean value as a result
➢ returns TRUE if ANY of the subquery values meet the condition
➢ ANY means that the condition will be true if the operation is true
for any of the values in the range.

➢ Syntax
SELECT column_name(s) FROM table_name WHERE column_name
operator ANY (SELECT column_name FROM table_name WHERE
condition);
Note: The operator must be a standard comparison
operator (=, <>, !=, >, >=, <, or <=).
 SQL All OPERATOR
The ALL operator:
➢returns a boolean value as a result
➢returns TRUE if ALL of the subquery values meet the
condition
➢is used with SELECT, WHERE and HAVING statements
➢ALL means that the condition will be true only if the
operation is true for all values in the range.
➢Syntax:
SELECT column_name(s) FROM table_name WHERE
column_name operator ALL (SELECT column_name
FROM table_name WHERE condition);
 EXCEPT Clause
➢In SQL, EXCEPT returns those tuples that are returned
by the first SELECT operation, and not returned by the
second SELECT operation.

➢This is the same as using a subtract operator in

relational algebra.
➢Syntax:
SELECT column1, column2,…column_n FROM table1
EXCEPT
SELECT column1, column2,…column_n FROM table_2
Example:
Say we have two relations, Students and TA (Teaching
Assistant). We want to return all those students who are not
teaching assistants. The query can be formulated as:
 DESCRIBE Statement
➢As the name suggests, DESCRIBE is used to describe
something. Since in database we have tables, that’s why we
use DESCRIBE or DESC(both are same) command to
describe the structure of a table.
➢Syntax:
DESCRIBE table_name;
OR
DESC table_name;