SQL QUERIES

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 that would
like to be created automatically every time a
new record is inserted.
 The following SQL statement defines the "ID"
column to be an auto-increment primary key
field in the "Persons" table
AUTO INCREMENT Field

CREATE TABLE Persons (

ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
PRIMARY KEY (ID)
);
AUTO INCREMENT Field

 MySQL uses the AUTO_INCREMENT keyword

to perform an auto-increment feature.
 By default, the starting value for
AUTO_INCREMENT is 1, and it will increment
by 1 for each new record.
 To let the AUTO_INCREMENT sequence start
with another value, use the following SQL
statement.
 ALTER TABLE Persons AUTO_INCREMENT=100;
AUTO INCREMENT Field

 To insert a new record into the "Persons"

table, no need to specify a value for the "ID"
column (a unique value will be added
automatically).
INSERT INTO Persons (FirstName,LastName)
VALUES (‘Shamakant',‘Navathe');
INSERT INTO Statement

 The INSERT INTO statement is used to insert new

records in a table.
 It is possible to write the INSERT INTO statement
in two ways.
1. Specifies both the column names and the
values to be inserted
INSERT INTO table_name (column1, column2,
column3, ...) VALUES (value1, value2, value3,
...);
INSERT INTO Statement

2.For adding values to all the columns of the

table, no need to specify the column names in
the SQL query.
 However, make sure the order of the values is
in the same order as the columns in the table.

INSERT INTO table_name

VALUES (value1, value2, value3, ...);
INSERT INTO

 The SQL statement inserts a new record in the

“Student" table is as follow

INSERT INTO Student (StudentName, RollNo,

Address, City, PostalCode, Country)
VALUES (‘ShamakantNavathe', 1, ‘Mumbai',
4000050, ‘India');
INSERT INTO

 It is also possible to only insert data in specific

columns.
 The SQL statement to insert a new record, but
only insert data in the “StudentName", "City",
and "Country" columns.
INSERT INTO Student (StudentName, City,
Country) VALUES (‘Reshma', ‘Navi
Mumbai', ‘India');

UPDATE Statement

 The UPDATE statement is used to modify the

existing records in a table.

 UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;
UPDATE Statement

 Note: Be careful when updating records in a

table. The WHERE clause specifies which
record(s) that should be updated. If you omit
the WHERE clause, all records in the
table will be updated.
UPDATE Statement

 UPDATE Table student for rollno 1 and set

city=Pune

 UPDATE Student
SET City= ‘Pune',
WHERE RollNo = 1;
UPDATE Multiple Records

 WHERE clause that determines how many

records that will be updated.
 The SQL statement to update the city as
“Pune" for all records where country is “India"
 UPDATE Student
SET City=‘Pune'
WHERE Country=‘India';
Update Warning

 Be careful when updating records. If you omit

the WHERE clause, ALL records will be
updated!

 UPDATE Student
SET City=‘Pune';
DELETE Statement

 The DELETE statement is used to delete existing

records in a table.

 DELETE FROM table_name

WHERE condition;

 Be careful when deleting records in a table. The

WHERE clause specifies which record(s) should be
deleted. If you omit the WHERE clause, all
records in the table will be deleted!
DELETE Statement

• The SQL statement to delete the Student

“Shamakant Navathe" from the “Student"
table.

• DELETE FROM Student

WHERE StudentName=‘Shamakant Navathe';
DELETE Statement

 Delete All Records

 It is possible to delete all rows in a table
without deleting the table. This means that
the table structure, attributes, and indexes
will be intact:
 DELETE FROM table_name; or
 DELETE * FROM table_name;
SELECT Statement
 The SELECT statement is used to select data
from a database.
 The data returned is stored in a result table,
called the result-set.
 SELECT column1, column2, ...
FROM table_name;
SELECT Statement

 Here, column1, column2, ... are the field

names of the table you want to select data
from. If you want to select all the fields
available in the table, use the following syntax

 SELECT * FROM table_name;

SELECT Statement

SELECT Column Example

 The SQL statement to select the
“StudentrName" and "City" columns from the
“Student" table:

 SELECT StudentName, City FROM Student;

SELECT Statement

 SELECT * Example
 The SQL statement to select all the columns
from the “Student" table:

 SELECT * FROM Student;

SELECT DISTINCT Statement
• The SELECT DISTINCT statement is used to return only
distinct (different) values.

• Inside a table, a column often contains many duplicate

values; and sometimes you only want to list the different
(distinct) values.

• The SELECT DISTINCT statement is used to return only

distinct (different) values.

• SELECT DISTINCT column1, column2, ...

FROM table_name;
SELECT DISTINCT Statement

 Examples
 The SQL statement selects only the DISTINCT
values from the "Country" column in the
“Student" table:
 SELECT DISTINCT Country FROM Student;
WHERE Clause

 The WHERE clause is used to filter records.

 The WHERE clause is used to extract only those
records that fulfill a specified condition.

 SELECT column1, column2, ...

FROM table_name
WHERE condition;

 The WHERE clause is used in SELECT statement,

UPDATE, DELETE statement, etc.
WHERE Clause
• The SQL statement to select all the students
from the country “India", from the “Student"
table:

• SELECT * FROM Student

WHERE Country=‘India';
Operators

Operator Description
= Equal
<> Not equal. Note: In some versions of SQL this operator may be written as
!=
> Greater than
< Less than
>= Greater than or equal
<= Less than or equal
BETWEEN Between an inclusive range
LIKE Search for a pattern
IN To specify multiple possible values for a column
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 is 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.
SQL AND, OR and NOT Operators
 AND Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
 OR Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
 NOT Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
AND Example

 Selects all fields from “Student" where

country is “India" AND city is “Mumbai":

 SELECT * FROM Student

WHERE Country=‘India' AND City=‘Mumbai';
OR Example

 The SQL statement to select all fields from

“Student" where country is “India" OR
“Germany"

 SELECT * FROM Student

WHERE Country=‘India' OR Country=‘Germany
';
NOT Example
 Selects all fields from “Student" where
country is NOT "Germany":

 SELECT * FROM Student

 WHERE NOT Country='Germany';
Combining AND, OR and NOT

 User can also combine the AND, OR and NOT

operators.
 selects all fields from “Student" where country is
“India" AND city must be “Mumbai" OR “Pune"
(use parenthesis to form complex expressions):

 SELECT * FROM Student

WHERE Country=‘India' AND (City=‘Mumbai' OR
City=‘Pune');
Combining AND, OR and NOT

 Select all fields from “Student" where country

is NOT "Germany" and NOT "USA":
 SELECT * FROM Student
WHERE NOT Country='Germany' AND
NOT Country='USA';
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.

 SELECT column1, column2, ...

FROM table_name
ORDER BY column1, column2, ... ASC|DESC;
ORDER BY Keyword
 Selects all student from the “Student" table,
sorted by the "Country" column:

 SELECT * FROM Student

ORDER BY Country;
ORDER BY

 ORDER BY DESC Example

 Selects all students from the “Student" table,
sorted DESCENDING by the "Country" column:

 SELECT * FROM Student

ORDER BY Country DESC;
ORDER BY

 ORDER BY Several Columns Example

 selects all students from the “Student" table,
sorted by the "Country" and the
“StudentName" column:
 SELECT * FROM Student
ORDER BY Country, StudentName;
SQL Wildcard Characters

 A wildcard character is used to substitute any

other character(s) in a string.
 Wildcard characters are used with the SQL
LIKE operator.
 The LIKE operator is used in a WHERE clause
to search for a specified pattern in a column.
LIKE Operator

 There are two wildcards used in conjunction

with the LIKE operator.
 % - The percent sign represents zero, one, or
multiple characters.
 _ - The underscore represents a single
character.
LIKE Operator

 The percent sign and the underscore can also

be used in combinations!

 SELECT column1, column2, ...

FROM table_name
WHERE columnN LIKE pattern;

 User can also combine any number of

conditions using AND or OR operators.
LIKE Operator

 Selects all Student with a StudentName

starting with "a":

SELECT * FROM Student

WHERE StudentName LIKE 'a%'
LIKE Operator

 Selects all studednt with a StudentName

ending with "a":

 SELECT * FROM Student

WHERE StudentName LIKE '%a';
LIKE Operator

 Selects all student with a StudentName that

have “aa" in any position:

 SELECT * FROM Student

WHERE StudentName LIKE '%aa%';
LIKE Operator

 Selects all student with a StudentName that

have “n" in the second position:

 SELECT * FROM Student

WHERE StudentName LIKE '_n%';
LIKE Operator

 Selects all students with a StudentName that

starts with “i" and are at least 3 characters in
length.

 SELECT * FROM Student

WHERE StudentName LIKE 'a_%_%';
LIKE Operator
• Selects all student with a StudentName that
starts with “i" and ends with “a":

• SELECT * FROM Student

WHERE StudentName LIKE ‘i%a';
LIKE Operator

 Selects all Student with a StudentName that

does NOT start with “z“

 SELECT * FROM Student

WHERE StudentName NOT LIKE ‘z%';
IN Operator

 The IN operator allows user 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, ...);
IN Operator

 SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT)
;
IN Operator

 Selects all student that are located in

"Germany", "France" and "UK":

 SELECT * FROM Student

WHERE Country IN ('Germany', 'France', 'UK');
 Selects all Student that are NOT located in
"Germany", "France" or "UK":

 SELECT * FROM Student

WHERE Country NOT IN ('Germany', 'France', '
UK');
BETWEEN Operator
 The SQL BETWEEN operator selects values within a
given range.
 The values can be numbers, 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;
BETWEEN Operator

 Selects all students with age BETWEEN 18 and

22.

 SELECT * FROM Student

WHERE age BETWEEN 18 AND 22;
NOT BETWEEN

 To display the students outside the range of

18 and 22.

 SELECT * FROM Student

WHERE age NOT BETWEEN 18 AND 22;
BETWEEN with IN Example

 Selects all student with a age BETWEEN 18 and

22. also do not show student with a CategoryID
of 1,2, or 3:

 SELECT * FROM Student

WHERE (age BETWEEN 18 AND 22)
AND NOT CategoryID IN (1,2,3);
BETWEEN Text Values Example

 Selects all Student with a BranchName

BETWEEN 'Computer Engineering' and
‘Information Technology‘.

 SELECT * FROM Student

WHERE BranchName BETWEEN 'Computer
Engineering ' AND ‘Information Technology'
ORDER BY BranchName;
NOT BETWEEN

 Selects all students with a BranchName NOT

BETWEEN 'Computer Engineering' and
‘Information Technology‘ and sort according
to Branchname

 SELECT * FROM Student

WHERE BranchName NOT BETWEEN 'Comput
er Engineering' AND ‘Information Technology'
ORDER BY BranchName;
BETWEEN Dates Example

 Selects all orders with an OrderDate BETWEEN

'01-July-2020' and '31-July-2020':

 SELECT * FROM Orders

WHERE OrderDate BETWEEN #01/07/2020# AND
#31/07/2020#;

 SELECT * FROM Orders

WHERE OrderDate BETWEEN ‘2020-07-
01' AND ‘2020-07-31';
SQL MIN() and MAX() Functions
 The MIN() function returns the smallest value of the
selected column.
 The MAX() function returns the largest value of the
selected column.

 SELECT MIN(column_name)
FROM table_name
WHERE condition;

 SELECT MAX(column_name)
FROM table_name
WHERE condition;
SQL MIN() and MAX() Functions

 Finds the price of the cheapest product.

SELECT MIN(Price) AS SmallestPrice
FROM Products;

 Finds the price of the most expensive product.

SELECT MAX(Price) AS LargestPrice
FROM Products;
SQL COUNT(), AVG() and SUM() Functions
 The COUNT() function returns the number of rows that
matches a specified criteria.

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

column.

 The SUM() function returns the total sum of a numeric

column.
SQL COUNT(), AVG() and SUM()
Functions
 SELECT COUNT(column_name)
FROM table_name
WHERE condition;

 SELECT AVG(column_name)
FROM table_name
WHERE condition;

 SELECT SUM(column_name)
FROM table_name
WHERE condition;
COUNT()
• Finds the number of products
SELECT COUNT(ProductID)
FROM Products;

• The SQL statement finds the average price of all

products

SELECT AVG(Price)
FROM Products;
SUM()

 Finds the sum of the "Quantity" fields in the

"OrderDetails" table.

SELECT SUM(Quantity)
FROM OrderDetails;
GROUP BY Statement

 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.

 SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
ORDER BY column_name(s);
SQL GROUP BY Examples

 Lists the number of customers in each

country.

SELECT COUNT(CustomerID), Country

FROM Customers
GROUP BY Country;
 Lists the number of customers in each country,
sorted high to low.

SELECT COUNT(CustomerID), Country

FROM Customers
GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
SQL HAVING Clause

 The HAVING clause was added to SQL because

the WHERE keyword could not be used with
aggregate functions.

 SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s)
HAVING condition
ORDER BY column_name(s);
SQL HAVING Examples

 Lists the number of customers in each

country. Only include countries with more
than 5 customers:

SELECT COUNT(CustomerID), Country

FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5;
SQL HAVING Examples
 Lists the number of customers in each country,
sorted high to low (Only include countries with
more than 5 customers).

SELECT COUNT(CustomerID), Country

FROM Customers
GROUP BY Country
HAVING COUNT(CustomerID) > 5
ORDER BY COUNT(CustomerID) DESC;