SQL

Structured Query Language

What is SQL
SQL is a standard language for accessing and manipulating
databases.

SQL stands for Structured Query Language

SQL lets you access and manipulate databases

SQL became a standard of the American National Standards

Institute (ANSI) in 1986, and of the International Organization for
Standardization (ISO) in 1987
What Can SQL do?
Execute SQL can execute queries against a database

Retrieve SQL can retrieve data from a database

Insert SQL can insert records in a database

Update SQL can update records in a database

Delete SQL can delete records from a database

Create SQL can create new databases

Create SQL can create new tables in a database

Create SQL can create stored procedures in a database

Create SQL can create views in a database

Set SQL can set permissions on tables, procedures, and views

SQL IS A › Although SQL is an ANSI/ISO
STANDARD - standard, there are different
BUT.... versions of the SQL language.
Note: Most of the SQL › However, to be compliant with the
database programs also
have their own proprietary ANSI standard, they all support at
extensions in addition to least the major commands (such
the SQL standard! as SELECT, UPDATE, DELETE, IN
SERT, WHERE) in a similar manner.
Using SQL in Your Web Site
To build a web site that shows data from a database, you
will need:

An RDBMS database program (i.e. MS Access, SQL

Server, MySQL)

To use a server-side scripting language, like PHP or

ASP

To use SQL to get the data you want

To use HTML / CSS to style the page

RDBMS
Relational Database Management
System.
Add a Slide Title - 2
CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico

4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK

5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden

6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany

7 Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France

8 Bólido Comidas preparadas Martín Sommer C/ Araquil, 67 Madrid 28023 Spain

9 Bon app Laurence Lebihans 12, rue des Bouchers Marseille 13008 France

10 Bottom-Dollar Marketse Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen T2F 8M4 Canada

11 Bs Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK

12 Cactus Comidas para llevar Patricio Simpson Cerrito 333 Buenos Aires 1010 Argentina

13 Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 México D.F. 05022 Mexico

14 Chop-suey Chinese Yang Wang Hauptstr. 29 Bern 3012 Switzerland

15 Comércio Mineiro Pedro Afonso Av. dos Lusíadas, 23 São Paulo 05432-043 Brazil

16 Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery London WX1 6LT UK

17 Drachenblut Delikatessend Sven Ottlieb Walserweg 21 Aachen 52066 Germany

18 Du monde entier Janine Labrune 67, rue des Cinquante Otages Nantes 44000 France

19 Eastern Connection Ann Devon 35 King George London WX3 6FW UK

› RDBMS is the basis for SQL, and for

20 Ernst Handel Roland Mendel Kirchgasse 6 Graz 8010 Austria

21 Familia Arquibaldo Aria Cruz Rua Orós, 92 São Paulo 05442-030 Brazil

22 FISSA Fabrica Inter. Salchichas S.A. Diego Roel C/ Moralzarzal, 86 Madrid 28034 Spain

23 Folies gourmandes Martine Rancé 184, chaussée de Tournai Lille 59000 France

all modern database systems such as

24 Folk och fä HB Maria Larsson Åkergatan 24 Bräcke S-844 67 Sweden

25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany

26 France restauration Carine Schmitt 54, rue Royale Nantes 44000 France

27 Franchi S.p.A. Paolo Accorti Via Monte Bianco 34 Torino 10100 Italy

28 Furia Bacalhau e Frutos do Mar Lino Rodriguez Jardim das rosas n. 32 Lisboa 1675 Portugal

MS SQL Server, IBM DB2, Oracle, 29

30

31
Galería del gastrónomo

Godos Cocina Típica

Gourmet Lanchonetes
Eduardo Saavedra

José Pedro Freyre

André Fonseca
Rambla de Cataluña, 23

C/ Romero, 33

Av. Brasil, 442

Barcelona

Sevilla

Campinas
08022

41101

04876-786
Spain

Spain

Brazil

MySQL, and Microsoft Access.

32 Great Lakes Food Market Howard Snyder 2732 Baker Blvd. Eugene 97403 USA

33 GROSELLA-Restaurante Manuel Pereira 5ª Ave. Los Palos Grandes Caracas 1081 Venezuela

34 Hanari Carnes Mario Pontes Rua do Paço, 67 Rio de Janeiro 05454-876 Brazil

35 HILARIÓN-Abastos Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35 San Cristóbal 5022 Venezuela

36 Hungry Coyote Import Store Yoshi Latimer City Center Plaza 516 Main St. Elgin 97827 USA

37 Hungry Owl All-Night Grocers Patricia McKenna 8 Johnstown Road Cork Ireland

› The data in RDBMS is stored in

38 Island Trading Helen Bennett Garden House Crowther Way Cowes PO31 7PJ UK

39 Königlich Essen Philip Cramer Maubelstr. 90 Brandenburg 14776 Germany

40 La corne dabondance Daniel Tonini 67, avenue de lEurope Versailles 78000 France

41 La maison dAsie Annette Roulet 1 rue Alsace-Lorraine Toulouse 31000 France

database objects called tables. A table

42 Laughing Bacchus Wine Cellars Yoshi Tannamuri 1900 Oak St. Vancouver V3F 2K1 Canada

43 Lazy K Kountry Store John Steel 12 Orchestra Terrace Walla Walla 99362 USA

44 Lehmanns Marktstand Renate Messner Magazinweg 7 Frankfurt a.M. 60528 Germany

45 Lets Stop N Shop Jaime Yorres 87 Polk St. Suite 5 San Francisco 94117 USA

is a collection of related data entries

46 LILA-Supermercado Carlos González Carrera 52 con Ave. Bolívar #65-98 Llano Largo Barquisimeto 3508 Venezuela

47 LINO-Delicateses Felipe Izquierdo Ave. 5 de Mayo Porlamar I. de Margarita 4980 Venezuela

48 Lonesome Pine Restaurant Fran Wilson 89 Chiaroscuro Rd. Portland 97219 USA

and it consists of columns and rows.

49 Magazzini Alimentari Riuniti Giovanni Rovelli Via Ludovico il Moro 22 Bergamo 24100 Italy

50 Maison Dewey Catherine Dewey Rue Joseph-Bens 532 Bruxelles B-1180 Belgium

51 Mère Paillarde Jean Fresnière 43 rue St. Laurent Montréal H1J 1C3 Canada

52 Morgenstern Gesundkost Alexander Feuer Heerstr. 22 Leipzig 04179 Germany

53 North/South Simon Crowther South House 300 Queensbridge London SW7 1RZ UK

54 Océano Atlántico Ltda. Yvonne Moncada Ing. Gustavo Moncada 8585 Piso 20-A Buenos Aires 1010 Argentina

55 Old World Delicatessen Rene Phillips 2743 Bering St. Anchorage 99508 USA

› Look at the "Customers" table:

56 Ottilies Käseladen Henriette Pfalzheim Mehrheimerstr. 369 Köln 50739 Germany

57 Paris spécialités Marie Bertrand 265, boulevard Charonne Paris 75012 France

58 Pericles Comidas clásicas Guillermo Fernández Calle Dr. Jorge Cash 321 México D.F. 05033 Mexico

59 Piccolo und mehr Georg Pipps Geislweg 14 Salzburg 5020 Austria

60 Princesa Isabel Vinhoss Isabel de Castro Estrada da saúde n. 58 Lisboa 1756 Portugal

61 Que Delícia Bernardo Batista Rua da Panificadora, 12 Rio de Janeiro 02389-673 Brazil

62 Queen Cozinha Lúcia Carvalho Alameda dos Canàrios, 891 São Paulo 05487-020 Brazil

63 QUICK-Stop Horst Kloss Taucherstraße 10 Cunewalde 01307 Germany

64 Rancho grande Sergio Gutiérrez Av. del Libertador 900 Buenos Aires 1010 Argentina

65 Rattlesnake Canyon Grocery Paula Wilson 2817 Milton Dr. Albuquerque 87110 USA

66 Reggiani Caseifici Maurizio Moroni Strada Provinciale 124 Reggio Emilia 42100 Italy

67 Ricardo Adocicados Janete Limeira Av. Copacabana, 267 Rio de Janeiro 02389-890 Brazil

68 Richter Supermarkt Michael Holz Grenzacherweg 237 Genève 1203 Switzerland

69 Romero y tomillo Alejandra Camino Gran Vía, 1 Madrid 28001 Spain

70 Santé Gourmet Jonas Bergulfsen Erling Skakkes gate 78 Stavern 4110 Norway

› SELECT * FROM Customers;

71 Save-a-lot Markets Jose Pavarotti 187 Suffolk Ln. Boise 83720 USA

72 Seven Seas Imports Hari Kumar 90 Wadhurst Rd. London OX15 4NB UK

73 Simons bistro Jytte Petersen Vinbæltet 34 København 1734 Denmark

74 Spécialités du monde Dominique Perrier 25, rue Lauriston Paris 75016 France

75 Split Rail Beer & Ale Art Braunschweiger P.O. Box 555 Lander 82520 USA

76 Suprêmes délices Pascale Cartrain Boulevard Tirou, 255 Charleroi B-6000 Belgium

77 The Big Cheese Liz Nixon 89 Jefferson Way Suite 2 Portland 97201 USA

78 The Cracker Box Liu Wong 55 Grizzly Peak Rd. Butte 59801 USA

79 Toms Spezialitäten Karin Josephs Luisenstr. 48 Münster 44087 Germany

80 Tortuga Restaurante Miguel Angel Paolino Avda. Azteca 123 México D.F. 05033 Mexico

81 Tradição Hipermercados Anabela Domingues Av. Inês de Castro, 414 São Paulo 05634-030 Brazil

82 Trails Head Gourmet Provisioners Helvetius Nagy 722 DaVinci Blvd. Kirkland 98034 USA

83 Vaffeljernet Palle Ibsen Smagsløget 45 Århus 8200 Denmark

84 Victuailles en stock Mary Saveley 2, rue du Commerce Lyon 69004 France

85 Vins et alcools Chevalier Paul Henriot 59 rue de lAbbaye Reims 51100 France

86 Die Wandernde Kuh Rita Müller Adenauerallee 900 Stuttgart 70563 Germany

87 Wartian Herkku Pirkko Koskitalo Torikatu 38 Oulu 90110 Finland

88 Wellington Importadora Paula Parente Rua do Mercado, 12 Resende 08737-363 Brazil

89 White Clover Markets Karl Jablonski 305 - 14th Ave. S. Suite 3B Seattle 98128 USA

90 Wilman Kala Matti Karttunen Keskuskatu 45 Helsinki 21240 Finland

91 Wolski Zbyszek ul. Filtrowa 68 Walla 01-012 Poland

 SQL Statements
SQL SYNTAX
Select all records from the
Customers table: Most of the actions you need to perform
on a database are done with SQL
SELECT * FROM Customers; statements.

SQL statements consist of keywords

that are easy to understand.

The following SQL statement selects all

records from the table named
"Customers":
 Database Tables
A database most often contains one or more tables. Each table is identified by a name
(e.g. "Customers" or "Orders"), and contain records (rows) with data.
we will use the well-known Northwind sample database (included in MS Access and
MS SQL Server).
Below is a selection from the Customers table used in the examples:
Custom Custome Contact Address City PostalCo Country
erID rName Name de
1 Alfreds Maria Obere Berlin 12209 German
Futterkis Anders Str. 57 y
te
2 Ana Ana Avda. de México 05021 Mexico
This table above contains five records (one for each Trujillo Trujillo la D.F.
customer) and seven columns (CustomerID, Empared Constitu
CustomerName, ContactName, Address, City, ados y ción
PostalCode, and Country). helados 2222
3 Antonio Antonio Matader México 05023 Mexico
Moreno Moreno os 2312 D.F.
Taquería
4 Around Thomas 120 London WA1 UK
the Horn Hardy Hanover 1DP
Sq.
5 Berglund Christina Berguvs Luleå S-958 Sweden
s Berglund vägen 8 22
snabbkö
Keep in Mind That...

SQL KEYWORDS ARE NOT CASE IN THIS TUTORIAL WE WILL WRITE ALL
SENSITIVE: SELECT IS THE SAME SQL KEYWORDS IN UPPER-CASE.
AS SELECT
Semicolon after SQL Statements?
› Some database systems require a semicolon at the end of
each SQL statement.
› Semicolon is the standard way to separate each SQL
statement in database systems that allow more than one
SQL statement to be executed in the same call to the
server.
› In this tutorial, we will use semicolon at the end of each
SQL statement.
SOME OF THE
› SELECT - extracts data from a database
MOST
IMPORTANT SQL › UPDATE - updates data in a database
COMMANDS › DELETE - deletes data from a database
› INSERT INTO - inserts new data into a database
› CREATE DATABASE - creates a new database
› ALTER DATABASE - modifies a database
› CREATE TABLE - creates a new table
› ALTER TABLE - modifies a table
› DROP TABLE - deletes a table
› CREATE INDEX - creates an index (search key)
› DROP INDEX - deletes an index
The SQL CREATE DATABASE Statement

You need administrative privileges to create a new database.

Syntax

CREATE DATABASE database_name; CREATE DATABASE testDB;

Show Databases
Once a database is created, you can check it in the list of databases with the following SQL
command:
Syntax for SQL Server

SELECT name FROM [Link];

Syntax for MySQL

SHOW DATABASES;
The SQL DROP DATABASE Statement
The DROP DATABASE statement is used to permanently delete an existing SQL database.
Note: Be careful before dropping a database! Dropping a database deletes the database and all its content (tables, views, stored procedures,
and data)!

Syntax

DROP DATABASE databasename; //You need administrative privileges to drop a database.

DROP DATABASE Example

DROP DATABASE testDB;

Tip: Once a database is dropped, you can check that it is removed from the list of databases with: SHOW
DATABASES; (MySQL) or SELECT name FROM [Link]; (SQL Server).
The BACKUP DATABASE Statement
The BACKUP DATABASE statement is used in SQL Server to create a full backup of an existing SQL
database.
Syntax

BACKUP DATABASE databasename

TO DISK = 'filepath';

BACKUP DATABASE testDB

TO DISK = 'D:\backups\[Link]';

Always place the backup database in a different drive than the original database! If you get a disk crash,
you will not lose your backup file along with the database.

The BACKUP WITH DIFFERENTIAL Statement

A differential backup only captures the data that has changed since the last full backup.
A differential backup requires at least one prior full backup!
BACKUP DATABASE databasename BACKUP DATABASE testDB
Syntax TO DISK = 'filepath' TO DISK = 'D:\backups\[Link]'
WITH DIFFERENTIAL; WITH DIFFERENTIAL;
The SQL CREATE TABLE Statement
The CREATE TABLE statement is used to create a new table in a database.
Syntax

CREATE TABLE table_name (

column1 datatype constraint,
column2 datatype constraint,
column3 datatype constraint,
....
);
Tip: For an overview of the available data types, go to our complete

CREATE TABLE Example

CREATE TABLE Persons (

PersonID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
The SQL DROP TABLE Statement
The DROP TABLE statement is used to permanently delete an existing table in a database.

Note: Be careful before dropping a table! Dropping a table deletes the entire table and all its
content!

Syntax DROP TABLE table_name;

To prevent an error from occur (if the table does not exists), it is a good practice to add the IF
EXISTS clause:
DROP TABLE IF EXISTS table_name;
Note: In most databases you cannot drop a table that is referenced by a in another table. To solve
this, you must remove the foreign key constraint or drop the dependent table.

DROP TABLE Example DROP TABLE IF EXISTS Shippers;

SQL TRUNCATE TABLE

The TRUNCATE TABLE statement is used to delete all the records in a table, but it keeps the table
structure, columns and constraints.
Syntax TRUNCATE TABLE table_name;
SQL ALTER TABLE Statement
The ALTER TABLE statement is used to add, delete, or modify columns in an existing table.
The ALTER TABLE statement is also used to add and drop various constraints on an existing table.
Common ALTER TABLE operations are:
▪ Add column - Adds a new column to a table
▪ Drop column - Deletes a column in a table
▪ Rename column - Renames a column
▪ Modify column - Changes the data type, size, or constraints of a column
▪ Add constraint - Adds a new constraint
▪ Rename table - Renames a table
ALTER TABLE - ADD Column
To add a column in a table, use the following syntax:
Syntax

ALTER TABLE table_name

ADD column_name datatype;

ALTER TABLE Customers

ADD Email varchar(255);
ALTER TABLE - DROP COLUMN
(notice that some database systems don't allow deleting a
column):
Syntax

ALTER TABLE table_name

DROP COLUMN column_name;

ALTER TABLE Customers

DROP COLUMN Email;

ALTER TABLE - RENAME COLUMN

Syntax

ALTER TABLE table_name

RENAME COLUMN old_name to new_name;
Syntax for SQL Server:
EXEC sp_rename 'table_name.old_name', 'new_name', 'COLUM
ALTER TABLE - MODIFY Datatype
Syntax for SQL Server / MS Access:

ALTER TABLE table_name

ALTER COLUMN column_name new_datatype constraint;

Syntax for MySQL / Oracle:

ALTER TABLE table_name

MODIFY column_name new_datatype constraint;
The SQL modifies the size of the "Email" column to varchar(100), and we also add a NOT
NULL constraint:
ALTER TABLE Customers
MODIFY Email varchar(100) NOT NULL;
ALTER TABLE - ADD CONSTRAINT
Syntax

ALTER TABLE table_name

ADD CONSTRAINT constraint_name constraint_definition;

ALTER TABLE Members

ADD CONSTRAINT CHK_Age CHECK (Age >= 18);

ALTER TABLE - Rename table

Syntax

ALTER TABLE table_name

RENAME TO new_table_name;

The following SQL renames the "Customers" table to "Clients":

ALTER TABLE Customers

RENAME TO Clients;
SQL ALTER TABLE

ALTER TABLE Persons

ADD DateOfBirth date;

Change Data Type

ALTER TABLE Persons

ALTER COLUMN DateOfBirth year;

DROP COLUMN

ALTER TABLE Persons

DROP COLUMN DateOfBirth;
SQL Constraints
SQL constraints are rules for data in a table.

SQL Constraint Types

The following constraints are commonly used in SQL:
•NOT NULL- Ensures that a column cannot have a NULL value
•UNIQUE - Ensures that all values in a column are unique
•PRIMARY KEY - Uniquely identifies each row in a table (a combination of a NOT
NULL and UNIQUE)
•FOREIGN KEY- Establishes a link between data in two tables, and prevents
action that will destroy the link between them
•CHECK - Ensures that the values in a column satisfies a specific condition
•DEFAULT- Sets a default value for a column if no value is specified
•CREATE INDEX - Creates indexes on columns to retrieve data from the
database faster
SQL NOT NULL Constraint
The NOT NULL constraint enforces a column to NOT accept NULL values. This enforces a field to always contain
a value, which means that you cannot insert a new record, or update a record without adding a value to this
field.
By default, a column can hold NULL values.

NOT NULL on CREATE TABLE

To define a NOT NULL constraint when creating a table, add NOT NULL after the data type of the column name.
The following SQL creates a "Persons" table, and ensures that the "ID", "LastName", and "FirstName" columns
cannot accept NULL values:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255) NOT NULL,
Age int);

NOT NULL on ALTER TABLE

To define a NOT NULL constraint on an existing table, use ALTER TABLE and add NOT NULL after the data type
of the column name.
The following SQL adds a NOT NULL constraint on the "Age" column, after the "Persons" table is already created:
Syntax for SQL Server / MS Access:

ALTER TABLE Persons

ALTER COLUMN Age int NOT NULL;
Syntax for My SQL:

ALTER TABLE Persons

MODIFY COLUMN Age int NOT NULL;

Syntax for Oracle 10G+:

ALTER TABLE Persons

MODIFY Age int NOT NULL;

Remove a NOT NULL Constraint

To remove a NOT NULL constraint from a column (to let the column accept NULL values again), use the following syntax:
Syntax for SQL Server / MS Access:

ALTER TABLE Persons

ALTER COLUMN Age int NULL;

Syntax for My SQL:

ALTER TABLE Persons

MODIFY COLUMN Age int NULL;

Synatx for Oracle 10G+:

ALTER TABLE Persons

MODIFY Age int NULL;
UNIQUE Constraint on CREATE TABLE
The following SQL defines a UNIQUE constraint for the "ID"
column upon creation of the "Persons" table:
SQL Server / Oracle / MS Access:

CREATE TABLE Persons (

ID int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);

MySQL:

CREATE TABLE Persons (

ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
UNIQUE (ID)
);
Naming a Unique Constraint
To name a UNIQUE constraint, and to define
a UNIQUE constraint on multiple columns, use the following
SQL syntax:
CREATE TABLE Persons (
ID int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);

UNIQUE Constraint on ALTER TABLE

To create a UNIQUE constraint on the "ID" column when the
table is already created, use the following SQL syntax:
ALTER TABLE Persons
ADD UNIQUE (ID);
Naming a Unique Constraint
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the
following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT UC_Person UNIQUE (ID,LastName);

Drop a UNIQUE Constraint

To drop a UNIQUE constraint, use the following SQL:
MySQL:

ALTER TABLE Persons

DROP INDEX UC_Person;

SQL Server / Oracle / MS Access:

ALTER TABLE Persons

DROP CONSTRAINT UC_Person;
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table.
A PRIMARY KEY constraint ensures unique values, and cannot contain NULL values (it is a
combination of both a UNIQUE constraint and a NOT NULL constraint).
A table can have only ONE PRIMARY KEY constraint. The primary key can either be a single
column, or a combination of columns.
Tip: The primary key is the target for FOREIGN KEY constraints in other tables (which enforces
referential integrity between data in two tables).

PRIMARY KEY on CREATE TABLE

The following SQL creates a PRIMARY KEY on the "ID" column upon creation of the "Persons"
table:
CREATE TABLE Persons (
ID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
PRIMARY KEY on ALTER TABLE
To create a PRIMARY KEY constraint on the "ID" column when the table already has been created,
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
PRIMARY KEY on Multiple Columns
To define a named PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID, LastName);
Note: When using ALTER TABLE to add a primary key, the primary key column(s) must have been declared with
NOT NULL upon creation of the table.

Drop a PRIMARY KEY Constraint

To drop a PRIMARY KEY constraint, use the following SQL:
SQL Server / Oracle / MS Access:

ALTER TABLE Persons

DROP CONSTRAINT PK_Person;

MySQL:

ALTER TABLE Persons

DROP PRIMARY KEY;
SQL FOREIGN KEY Constraint

The FOREIGN KEY constraint establishes a link between two tables, and
prevents action that will destroy the link between them.
A FOREIGN KEY is a column in a table that refers to the PRIMARY KEY in
another table.
The table with the foreign key column is called the child table, and the
table with the primary key column is called the referenced or parent
table.
The FOREIGN KEY constraint prevents invalid data from being inserted
into the foreign key column (in the child table), because the value has to
exist in the parent table.
The FOREIGN KEY constraint also prevents you from deleting a record
in the parent table, if related rows still exist in the child table.
Assume we have two tables:
FOREIGN KEY on CREATE TABLE
The following SQL creates a FOREIGN KEY constraint on the "PersonID" column upon creation of the
"Orders" table:
CREATE TABLE Orders (
OrderID int PRIMARY KEY,
OrderNumber int NOT NULL,
PersonID int,
CONSTRAINT fk_Person
FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID)
);
FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the "PersonID" column after the "Orders" table is created,
use the following SQL:
ALTER TABLE Orders
ADD CONSTRAINT fk_Person
FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID);

Drop a FOREIGN KEY Constraint

To drop a FOREIGN KEY constraint, use the following SQL:
SQL Server / Oracle / MS Access:

ALTER TABLE Orders

DROP CONSTRAINT fk_Person;

MySQL:

ALTER TABLE Orders

DROP FOREIGN KEY fk_Person;
SQL CHECK Constraint
The CHECK constraint is used to ensure that the values in a
column satisfies a specific condition.
The CHECK constraint evaluates the data to TRUE or FALSE.
If the data evaluates to TRUE, the operation is ok. If the data
evaluates to FALSE, the entire INSERT or UPDATE operation
is aborted, and an error is raised.

CHECK Constraint on CREATE TABLE

The following SQL creates a CHECK constraint on the "Age"
column upon creation of the "Persons" table.
Here, the CHECK constraint ensures that the "Age" column
must have a value of 18, or above:
CREATE TABLE Persons (
ID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int CHECK (Age >= 18)
);
Naming a CHECK Constraint
To name a CHECK constraint, and to define a CHECK constraint on multiple
columns, use the following SQL syntax:
CREATE TABLE Persons (
ID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255),
CONSTRAINT chk_PersonAge CHECK (Age >= 18 AND City
= 'Sandnes')
);
SQL DEFAULT Constraint
The DEFAULT constraint is used to automatically insert a
default value for a column, if no value is specified.
The default value will be added to all new records (if no
other value is specified).

DEFAULT Constraint on CREATE TABLE

The following SQL sets a DEFAULT value for the "City"
column upon creation of the "Persons" table:
CREATE TABLE Persons (
ID int PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int,
City varchar(255) DEFAULT 'Sandnes'
);
The DEFAULT constraint can also be used to insert system values, by using functions
like CURRENT_DATE() to insert the current date:
MySQL:

CREATE TABLE Orders (

ID int PRIMARY KEY,
OrderNumber int NOT NULL,
OrderDate date DEFAULT CURRENT_DATE()
);

SQL Server:
To achieve the same result in SQL Server use the following SQL (to insert the current date):
CREATE TABLE Orders (
ID int PRIMARY KEY,
OrderNumber int NOT NULL,
OrderDate date DEFAULT CAST(GETDATE() AS date)
);
SQL CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes on tables in databases, to speed
up data retrieval.
The users cannot see the indexes, they are just used to speed up searches/queries.

Note: Updating tables with indexes are more time-consuming than tables without
indexes (because the indexes must also be updated). So, only create indexes on
columns that are frequently searched against.

Types of Indexes: Non-unique and Unique

There are two types of indexes:
•CREATE INDEX - Creates a non-unique index (duplicate values are allowed)
•CREATE UNIQUE INDEX - Creates a unique index (duplicate values are not allowed)
CREATE INDEX Syntax

CREATE INDEX index_name

ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX Syntax

CREATE UNIQUE INDEX index_name

ON table_name (column1, column2, ...);
Note: The syntax for creating indexes varies among different databases. Check the syntax for
creating indexes in your database!

CREATE INDEX Example

The following SQL creates a non-unique index named "idx_lastname" on the "LastName"
column in the "Persons" table:
CREATE INDEX idx_lastname
ON Persons (LastName);
If you want to create an index on a combination of columns, you can list the column names
within the parentheses, separated by commas:
CREATE INDEX idx_lname_fname
ON Persons (LastName, FirstName);
SQL AUTO INCREMENT Field
An auto-increment field is a numeric column that automatically generates a unique number,
when a new record is inserted into a table.
The auto-increment field is typically the PRIMARY KEY field that we want to automatically be
assigned a unique number, every time a new record is inserted.

Syntax for MySQL

MySQL uses the AUTO_INCREMENT keyword to perform an auto-increment feature.
The following SQL defines the "Personid" column to be an auto-increment primary key field in
the "Persons" table:
CREATE TABLE Persons (
Personid int AUTO_INCREMENT PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Age int
);
The default starting value for AUTO_INCREMENT is 1, and it will increment by 1 for each new
record.
To let AUTO_INCREMENT start with another value, use the following SQL statement:
ALTER TABLE Persons AUTO_INCREMENT = 100;
When we insert a new record into the "Persons" table, we will NOT have to specify a value
for the "Personid" column (a unique value will be added automatically):
INSERT INTO Persons (FirstName, LastName)
VALUES ('Lars', 'Monsen');
 The SELECT statement is used to
THE SQL SELECT select data from a database.
STATEMENT
SELECT Syntax
SELECT column1, column2, ... FROM table_name;

❖ Here, column1, column2, ... are the column

names in the table you want to select data from.
❖ The table_name represents the name of the
table you want to select data from.

SELECT CustomerName, City FROM Customers;

Select ALL Columns
To select ALL columns, without specifying every column name, use the SELECT
* syntax:

Select ALL columns from the "Customers" table:

SELECT * FROM Customers;

 › SELECT DISTINCT Syntax
› SELECT DISTINCT column1, column
SQL SELECT 2, ...
DISTINCT STATEMENT FROM table_name;
The SELECT
DISTINCT statement is
used to return only distinct › Select all the distinct (unique)
(unique) values. countries from the "Customers" table:
In a table, a column may › SELECT DISTINCT Country FROM
contain several duplicate Customers;
values - and sometimes
you want to list only the
unique values.
SELECT EXAMPLE SELECT Country FROM Customers;
WITHOUT DISTINCT
If you omit
the DISTINCT keyword, the
SQL statement returns the
"Country" value from all the
records of the "Customers"
table: By using the COUNT() function with the DISTINCT keyword, we
can count the number of unique countries.

COUNT DISTINCT VALUES SELECT COUNT(DISTINCT Country) FROM Customers;

Note: The COUNT(DISTINCT column_name) is not supported in Microsoft Access
databases.

workaround for MS Access:

SELECT Count(*) AS DistinctCountries
FROM (SELECT DISTINCT Country FROM Customers);
The SQL WHERE Clause
The WHERE clause is used to filter records.
The WHERE clause is used to extract only those records that fulfill a specific condition.

WHERE Syntax

SELECT column1, column2, ...

FROM table_name
WHERE condition;

Here we select all customers from Mexico:

SELECT * FROM Customers
WHERE Country = 'Mexico';

Note: The WHERE clause is not only used in SELECT statements, it is also used
in UPDATE, DELETE, etc.
Operators in The WHERE Clause
Operat Description Ex
or am
You can use other operators than ple
the = operator to filter the search.
= Equal
> Greater than

Select all customers with a CustomerID < Less than

greater than 80: >= Greater than or equal
SELECT * FROM Customers <= Less than or equal
WHERE CustomerID > 80;
<> Not equal. Note: In some
versions of SQL this operator
may be written as !=
BETW Between a certain range
EEN
LIKE Search for a pattern
IN To specify multiple possible
values for a column
The SQL ORDER BY
The ORDER BY keyword is used to sort the result-set in ascending or descending order.
The ORDER BY keyword sorts the result-set in ascending order (ASC) by default.

ORDER BY Syntax
Sort the products from lowest to highest price:
SELECT column1, column2, ... SELECT * FROM Products
FROM table_name ORDER BY Price;
ORDER BY column1, column2,
... ASC|DESC;
ORDER BY Order Alphabetically ORDER BY Combine ASC
DESC Alphabetically DESC Several Columns and DESC
To sort the records in For string values, the ORDER To sort the text values in a 1. SQL statement selects all SQL statement selects all
descending order, use BY keyword will sort the values column in a descending order, customers from the "Customers" customers from the "Customers"
the DESC keyword. in the column alphabetically: use the DESC keyword: table - and sorts it by the table, and sorts it ASCENDING
"Country" and the by the "Country" and
"CustomerName" column. DESCENDING by the
2. it sorts it first by Country, and "CustomerName" column:
if some records have the same
Country, it sorts them by
CustomerName:
Sort the products from highest For string values, the ORDER To sort the text values in a 1. SQL statement selects all SQL statement selects all
to lowest price: BY keyword will sort the values column in a descending order, customers from the "Customers" customers from the "Customers"
SELECT * FROM Products in the column alphabetically: use the DESC keyword: table - and sorts it by the table, and sorts it ASCENDING
ORDER BY Price DESC; "Country" and the by the "Country" and
"CustomerName" column. DESCENDING by the
2. it sorts it first by Country, and "CustomerName" column:
if some records have the same
Country, it sorts them by
CustomerName:
The SQL AND Operator
The WHERE clause can contain one or many AND operators.
The AND operator is used to filter records based on more than one condition.
Note: The AND operator displays a record if all the conditions are TRUE.

AND Syntax SQL selects all customers from Spain

that starts with the letter 'G':
SELECT column1, column2, ...
FROM table_name Select all customers where Country is
WHERE condition1 AND condition "Spain" AND CustomerName starts with
2 AND condition3 ...; the letter 'G':
SELECT *
FROM Customers
WHERE Country
= 'Spain' AND CustomerName LIKE 'G%'
;
The SQL OR Operator
The WHERE clause can contain one or more OR operators.
The OR operator is used to filter records based on more than one condition.
Note: The OR operator displays a record if any of the conditions are TRUE.

OR Syntax Select all customers where Country is

"Germany" OR "Spain":
SELECT column1, column2, ... SELECT *
FROM table_name FROM Customers
WHERE condition1 OR condition2 WHERE Country
OR condition3 ...; = 'Germany' OR Country = 'Spain';
CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany

8 Bólido Comidas preparadas Martín Sommer C/ Araquil, 67 Madrid 28023 Spain

17 Drachenblut Delikatessend Sven Ottlieb Walserweg 21 Aachen 52066 Germany

22 FISSA Fabrica Inter. Salchichas Diego Roel C/ Moralzarzal, 86 Madrid 28034 Spain
S.A.

25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany

29 Galería del gastrónomo Eduardo Saavedra Rambla de Cataluña, 23 Barcelona 08022 Spain

30 Godos Cocina Típica José Pedro Freyre C/ Romero, 33 Sevilla 41101 Spain

39 Königlich Essen Philip Cramer Maubelstr. 90 Brandenburg 14776 Germany

44 Lehmanns Marktstand Renate Messner Magazinweg 7 Frankfurt a.M. 60528 Germany

52 Morgenstern Gesundkost Alexander Feuer Heerstr. 22 Leipzig 04179 Germany

56 Ottilies Käseladen Henriette Pfalzheim Mehrheimerstr. 369 Köln 50739 Germany

63 QUICK-Stop Horst Kloss Taucherstraße 10 Cunewalde 01307 Germany

69 Romero y tomillo Alejandra Camino Gran Vía, 1 Madrid 28001 Spain

79 Toms Spezialitäten Karin Josephs Luisenstr. 48 Münster 44087 Germany

86 Die Wandernde Kuh Rita Müller Adenauerallee 900 Stuttgart 70563 Germany
The SQL NOT Operator
The NOT operator is used in the WHERE clause to return all records that DO NOT match the specified
criteria. It reverses the result of a condition from true to false and vice-versa.

NOT Syntax the NOT operator is used in combination

with the = operator.
SELECT column1, column2, ...
FROM table_name
The NOT operator is also used in
WHERE NOT condition; combination with other operators to
exclude data, such as:
•NOT LIKE
Select only the customers that are NOT from
Spain:
•NOT BETWEEN
•NOT IN
•IS NOT NULL
SELECT * FROM Customers
WHERE NOT Country = 'Spain';
•NOT EXISTS
CustomerID CustomerName ContactName Address City PostalCode Country

1 Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany

2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico

3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4 Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
6 Blauer See Delikatessen Hanna Moos Forsterstr. 57 Mannheim 68306 Germany
7 Blondel père et fils Frédérique Citeaux 24, place Kléber Strasbourg 67000 France
9 Bon app Laurence Lebihans 12, rue des Bouchers Marseille 13008 France
10 Bottom-Dollar Marketse Elizabeth Lincoln 23 Tsawassen Blvd. Tsawassen T2F 8M4 Canada
11 Bs Beverages Victoria Ashworth Fauntleroy Circus London EC2 5NT UK
12 Cactus Comidas para llevar Patricio Simpson Cerrito 333 Buenos Aires 1010 Argentina
13 Centro comercial Moctezuma Francisco Chang Sierras de Granada 9993 México D.F. 05022 Mexico

14 Chop-suey Chinese Yang Wang Hauptstr. 29 Bern 3012 Switzerland

15 Comércio Mineiro Pedro Afonso Av. dos Lusíadas, 23 São Paulo 05432-043 Brazil
16 Consolidated Holdings Elizabeth Brown Berkeley Gardens 12 Brewery London WX1 6LT UK

17 Drachenblut Delikatessend Sven Ottlieb Walserweg 21 Aachen 52066 Germany

18 Du monde entier Janine Labrune 67, rue des Cinquante Otages Nantes 44000 France

19 Eastern Connection Ann Devon 35 King George London WX3 6FW UK

20 Ernst Handel Roland Mendel Kirchgasse 6 Graz 8010 Austria
21 Familia Arquibaldo Aria Cruz Rua Orós, 92 São Paulo 05442-030 Brazil
23 Folies gourmandes Martine Rancé 184, chaussée de Tournai Lille 59000 France

24 Folk och fä HB Maria Larsson Åkergatan 24 Bräcke S-844 67 Sweden

25 Frankenversand Peter Franken Berliner Platz 43 München 80805 Germany
26 France restauration Carine Schmitt 54, rue Royale Nantes 44000 France
27 Franchi S.p.A. Paolo Accorti Via Monte Bianco 34 Torino 10100 Italy
28 Furia Bacalhau e Frutos do Mar Lino Rodriguez Jardim das rosas n. 32 Lisboa 1675 Portugal

31 Gourmet Lanchonetes André Fonseca Av. Brasil, 442 Campinas 04876-786 Brazil
32 Great Lakes Food Market Howard Snyder 2732 Baker Blvd. Eugene 97403 USA
33 GROSELLA-Restaurante Manuel Pereira 5ª Ave. Los Palos Grandes Caracas 1081 Venezuela

34 Hanari Carnes Mario Pontes Rua do Paço, 67 Rio de Janeiro 05454-876 Brazil
35 HILARIÓN-Abastos Carlos Hernández Carrera 22 con Ave. Carlos Soublette #8-35 San Cristóbal 5022 Venezuela

36 Hungry Coyote Import Store Yoshi Latimer City Center Plaza 516 Main St. Elgin 97827 USA

37 Hungry Owl All-Night Grocers Patricia McKenna 8 Johnstown Road Cork Ireland

38 Island Trading Helen Bennett Garden House Crowther Way Cowes PO31 7PJ UK

39 Königlich Essen Philip Cramer Maubelstr. 90 Brandenburg 14776 Germany

40 La corne dabondance Daniel Tonini 67, avenue de lEurope Versailles 78000 France

41 La maison dAsie Annette Roulet 1 rue Alsace-Lorraine Toulouse 31000 France

42 Laughing Bacchus Wine Cellars Yoshi Tannamuri 1900 Oak St. Vancouver V3F 2K1 Canada

43 Lazy K Kountry Store John Steel 12 Orchestra Terrace Walla Walla 99362 USA
44 Lehmanns Marktstand Renate Messner Magazinweg 7 Frankfurt a.M. 60528 Germany
The SQL 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:
Syntax 1
Specify both the column names and the values to be inserted:

INSERT INTO table_name (column1, column2, column3, ...)

VALUES (value1, value2, value3, ...);
INSERT INTO Customers
VALUES ('Cardinal', 'Tom B.
Erichsen', 'Skagen21', 'Stavanger', '4006', 'Norway');

Syntax 2
If you insert values for ALL the columns of the table, you can omit the column names.
However, the order of the values must be in the same order as the columns in the table:

INSERT INTO table_name

VALUES (value1, value2, value3, ...);
Insert Data Only in Specific Columns
SQL inserts a new record - but only inserts data in the "CustomerName", "City", and "Country"
columns (CustomerID will be updated automatically):

INSERT INTO Customers (CustomerName, City, Country)

VALUES ('Cardinal', 'Stavanger', 'Norway');

CustomerID CustomerNa ContactNam Address City PostalCode Country

me e
92 Cardinal null null Stavanger null Norway
Insert Multiple Rows
To insert multiple rows of data, we use the same INSERT
INTO statement, but with multiple values:
Example

INSERT INTO Customers (CustomerName,

ContactName, Address, City, PostalCode,
Country)
VALUES
('Cardinal', 'Tom B. Erichsen', 'Skagen
21', 'Stavanger', '4006', 'Norway'),
('Greasy Burger', 'Per Olsen', 'Gateveien
15', 'Sandnes', '4306', 'Norway'),
('Tasty Tee', 'Finn Egan', 'Streetroad
19B', 'Liverpool', 'L1 0AA', 'UK');
Note: Make sure you separate each set of values with a
comma ,.
CustomerID CustomerName ContactName Address City PostalCode Country

92 Cardinal Tom B. Erichsen Skagen 21 Stavanger 4006 Norway

93 Greasy Burger Per Olsen Gateveien 15 Sandnes 4306 Norway

94 Tasty Tee Finn Egan Streetroad 19B Liverpool L1 0AA UK

SQL NULL Values
If a field in a table is optional, it is possible to insert or update a record without adding any value to this
field. This way, the field will be saved with a NULL value.
A NULL value represents an unknown, missing, or inapplicable data in a database field. It is not a value
itself, but a placeholder to indicate the absence of data.

Note: A NULL value is different from zero (0) or an empty string (''). A field with a NULL value is one that
has been left blank upon record creation.

How to Test for NULL Values?

It is not possible to test for NULL values with comparison operators, such as =, <, or <>.
We will have to use the IS NULL and IS NOT NULL operators instead.
IS NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NULL;

IS NOT NULL Syntax

SELECT column_names
FROM table_name
WHERE column_name IS NOT NULL;
The IS NULL Operator

The IS NULL operator is used to test for empty values (NULL values).
The following SQL lists all customers with a NULL value in the "Address" field:

SELECT CustomerName, ContactName, Address

FROM Customers
WHERE Address IS NULL;
Tip: Always use IS NULL to look for NULL values.

The IS NOT NULL Operator

The IS NOT NULL operator is used to test for non-empty
values (NOT NULL values).
SELECT CustomerName, ContactName, Address
FROM Customers
WHERE Address IS NOT NULL;
The SQL UPDATE Statement
UPDATE statement is used to update or modify one or more
records in a table.
UPDATE Syntax

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

Note: Be careful when updating records in a table! Notice the WHERE clause in the UPDATE statement.
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 Table

UPDATE Customers
SET ContactName = 'Alfred Schmidt',
City= 'Frankfurt'
WHERE CustomerID = 1;
UPDATE Multiple Records

The WHERE clause determines which records that will be updated.

UPDATE Customers
SET ContactName='Juan' Custo CustomerName ContactName Address City PostalCode Country
merID
WHERE Country='Mexico';
1 Alfreds Futterkiste Alfred Schmidt Obere Str. Frankfurt 12209 Germany
57

2 Ana Trujillo Juan Avda. de la México D.F. 05021 Mexico

Emparedados y Constitución
helados 2222

3 Antonio Moreno Juan Mataderos México D.F. 05023 Mexico

Taquería 2312

Update Warning!

If you omit the WHERE clause, ALL records will be updated!

Below SQL will update the ContactName to "Juan" for ALL records:
Example

UPDATE Customers
SET ContactName='Juan';
 The SQL DELETE Statement
The DELETE statement is used to delete existing records in a table.

DELETE Syntax

DELETE FROM table_name WHERE condition;

DELETE FROM Customers WHERE CustomerName='Alfreds Futterkiste';

Delete All Records

It is possible to delete all records in a table, without deleting the table. This means that the table
structure, attributes, and indexes will be intact.
Syntax

DELETE FROM table_name;

SQL deletes ALL records in the "Customers" table, without deleting the table:
Example

DELETE FROM Customers; Note: Be careful when deleting records in a table! Notice the WHERE clause in
the DELETE statement. 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 a Table
To delete the table completely, use the DROP
TABLE statement:
Syntax

DROP TABLE table_name;

The following SQL drops the entire "Customers" table:

Example

Delete entire "Customers" table:

DROP TABLE Customers;

SQL SELECT TOP, The SQL SELECT TOP Clause SELECT TOP 3 * FROM Customers;

LIMIT AND FETCH Syntax for SQL Server / MS

Access
FIRST SELECT TOP
number|percent column_name(s)
FROM table_name
WHERE condition;
SELECT * FROM Customers
Syntax for MySQL LIMIT 3;
SELECT column_name(s)
FROM table_name
WHERE condition
LIMIT number;

Syntax for Oracle 12+ SELECT * FROM Customers

SELECT column_name(s) FETCH FIRST 3 ROWS ONLY;
FROM table_name
ORDER BY column_name(s)
FETCH FIRST number ROWS
ONLY;
SQL TOP PERCENT Example
Here we will use the SELECT TOP clause with the percent syntax.
The following SQL selects the first 50% of the records from the "Customers" table

SQL Server and MS Access Oracle

SELECT TOP 50 PERCENT * FR SELECT * FROM Customers

OM Customers; FETCH FIRST 50 PERCENT RO
WS ONLY;

SELECT TOP with WHERE

The following SQL selects the first three records from the "Customers" table, where Country is "Germany"

SQL Server and MS Access MySQL Oracle

SELECT TOP 3 * FROM Customers SELECT * FROM Customers SELECT * FROM Customers
WHERE Country = 'Germany'; WHERE Country WHERE Country = 'Germany'
= 'Germany' FETCH FIRST 3 ROWS ONLY;
LIMIT 3;
SELECT TOP and ORDER BY
Add the ORDER BY keyword when you want to sort the result and return the first 3 records of the
sorted result.
The following SQL shows the equivalent example for Oracle:

SQL Server and MS Access MySQL Oracle

SELECT TOP 3 * FROM Customers SELECT * FROM SELECT * FROM Custome

ORDER BY CustomerName DESC; Customers rs
ORDER BY Cust ORDER BY CustomerName
omerName DESC DESC
LIMIT 3; FETCH FIRST 3 ROWS
ONLY;
SQL Aggregate Functions
An aggregate function is a function that performs a calculation on a set of values, and returns a
single value.
Aggregate functions are often used with the GROUP BY clause of the SELECT statement.
The GROUP BY clause splits the result-set into groups of values and the aggregate function can
be used to return a single value for each group.
The most commonly used SQL aggregate functions are:
•MIN() - returns the smallest value of a column
•MAX() - returns the largest value of a column
•COUNT() - returns the number of rows in a set
•SUM() - returns the sum of a numerical column
•AVG() - returns the average value of a numerical column
Aggregate functions ignore null values (except for COUNT(*)).
We will go through the aggregate functions above in the next chapters.
The SQL MIN() Function
The MIN() function returns the smallest value of the selected column.
The MIN() function works with numeric, string, and date data types.

Return the lowest price in the Price column, in the "Products" table:

MIN() Syntax

SELECT MIN(column_name) SELECT MIN(Price)

FROM table_name FROM Products;
WHERE condition;
The SQL MAX() Function
The MAX() function returns the largest value of the selected column.
The MAX() function works with numeric, string, and date data types.

MAX() Syntax

SELECT MAX(column_name) SELECT MAX(Price)

FROM table_name FROM Products;
WHERE condition;