Have you ever seen a Web page and wondered "Hey! How did they do that?"
View HTML Source Code:
Right-click in an HTML page and select "View Page Source" (in
Chrome) or "View Source" (in Edge), or similar in other browsers. This will open a window
containing the HTML source code of the page.
Inspect an HTML Element:
Right-click on an element (or a blank area), and choose "Inspect" or
"Inspect Element" to see what elements are made up of (you will see both
the HTML and the CSS). You can also edit the HTML or CSS on-the-fly in the
Elements or Styles panel that opens.
SQL Tutorial
SQL is a standard language for storing, manipulating and retrieving data
in databases.
Our SQL tutorial will teach you how to use SQL in:
MySQL, SQL Server, MS Access, Oracle, Sybase, Informix, Postgres, and other database systems.
Examples in Each Chapter
With our online SQL editor, you can edit the SQL statements, and click on a button to view the result.
Example
SELECT * FROM Customers;
Click on the "Try it Yourself" button to see how it works.
Introduction to SQL
SQL is a standard language for accessing and manipulating databases.
What is SQL?
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?
SQL can execute queries against a database
SQL can retrieve data from a database
SQL can insert records in a database
SQL can update records in a database
SQL can delete records from a database
SQL can create new databases
SQL can create new tables in a database
SQL can create stored procedures in a database
SQL can create views in a database
SQL can set permissions on tables, procedures, and views
SQL is a Standard - BUT....
Although SQL is an ANSI/ISO standard, there are different versions of the SQL language.
However, to be compliant with the ANSI standard, they all support at least the major commands (such as
SELECT, UPDATE,
DELETE, INSERT,
WHERE) in a similar manner.
Note: Most of the SQL database programs also have their own proprietary extensions in addition to the SQL standard!
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
RDBMS stands for Relational Database Management System.
RDBMS is the basis for SQL, and for all modern database systems such as MS SQL Server, IBM DB2, Oracle, MySQL, and Microsoft Access.
The data in RDBMS is stored in database objects called tables. A table is a collection of related data entries and it consists of columns and rows.
Look at the "Customers" table:
Example
SELECT * FROM Customers;
Every table is broken up into smaller entities called fields. The fields in
the Customers table consist of CustomerID, CustomerName, ContactName, Address,
City, PostalCode and Country. A field is a column in a table that is designed to maintain
specific information about every record in the table.
A record, also called a row, is each individual entry that exists in a table.
For example, there are 91 records in the above Customers table. A record is a
horizontal entity in a table.
A column is a vertical entity in a table that contains all information
associated with a specific field in a table.
SQL Syntax
Database Tables
A database most often contains one or more tables. Each table is identified
by a name (e.g. "Customers" or "Orders"). Tables contain records (rows) with
data.
In this tutorial we will use the well-known Northwind sample database
(included in MS Access and MS SQL Server).
The table above contains five records (one for each customer) and seven columns
(CustomerID, CustomerName, ContactName, Address, City, PostalCode, and Country).
SQL Statements
Most of the actions you need to perform on a database are done with SQL
statements.
The following SQL statement selects all the records in the "Customers" table:
Example
SELECT * FROM Customers;
In this tutorial we will teach you all about the different SQL statements.
Keep in Mind That...
SQL keywords are NOT case sensitive: select is the same as
SELECT
In this tutorial we will write all SQL keywords in upper-case.
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 Most Important SQL Commands
SELECT - extracts data from a database
UPDATE - updates data in a database
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 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 Syntax
SELECT column1, column2, ...
FROM table_name;
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;
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
The following SQL statement selects all (including the duplicates) values from the "Country" column in the "Customers" table:
Example
SELECT Country FROM Customers;
Now, let us use the SELECT DISTINCT statement and see the result.
SELECT DISTINCT Examples
The following SQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table:
Example
SELECT DISTINCT Country FROM Customers;
The following SQL statement lists the number of different (distinct) customer countries:
Example
SELECT COUNT(DISTINCT Country) FROM Customers;
Note: The example above will not work in Firefox! Because COUNT(DISTINCT column_name) is not supported in Microsoft Access databases. Firefox
is using Microsoft Access in our examples.
Here is the workaround for MS Access:
Example
SELECT Count(*) AS DistinctCountries FROM (SELECT DISTINCT Country FROM Customers);
The SQL WHERE Clause
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified
condition.
WHERE Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note: The WHERE clause is not only used in
SELECT statements, it is also used in UPDATE,
DELETE, etc.!
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
The following SQL statement selects all customers from the "Customers" table,
sorted by the "Country" column:
Example
SELECT * FROM Customers
ORDER BY Country;
ORDER BY DESC Example
The following SQL statement selects all customers from the "Customers" table,
sorted DESCENDING by the "Country" column:
Example
SELECT * FROM Customers
ORDER BY Country DESC;
ORDER BY Several Columns Example
The following SQL statement selects all customers from the "Customers" table,
sorted by the "Country" and the "CustomerName" column. This means that it orders
by Country, but if some rows have the same Country, it orders them by
CustomerName:
Example
SELECT * FROM Customers
ORDER BY Country, CustomerName;
ORDER BY Several Columns Example 2
The following SQL statement selects all customers from the "Customers" table,
sorted ascending by the "Country" and descending by the "CustomerName" column:
Example
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
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, like if you want to return all customers from Spain that starts with
the letter 'G':
Example
Select all customers from Spain that starts with the letter 'G':
SELECT *
FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%';
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 AND condition2 AND condition3 ...;
AND vs OR
The AND operator displays a record if all the conditions are TRUE.
The OR operator displays a record if any of the conditions are TRUE.
Demo Database
The table below shows the complete "Customers" table from the Northwind sample database:
The following SQL statement selects all fields from Customers where
Country is "Germany" AND
City is "Berlin" AND PostalCode is higher than 12000:
Example
SELECT * FROM Customers
WHERE Country = 'Germany'
AND City = 'Berlin'
AND PostalCode > 1200;
Combining AND and OR
You can combine the AND and OR operators.
The following SQL statement selects all customers from Spain that starts with a "G" or an "R".
Make sure you use parenthesis to get the correct result.
Example
Select all Spanish customers that starts with either "G" or "R":
SELECT * FROM Customers
WHERE Country = 'Spain'
AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%');
Without parenthesis, the select statement will return all customers from Spain that starts with a "G", plus
all customers that starts with an "R", regardless of the country value:
Example
Select all customers that either:
are from Spain and starts with either "G", or
starts with the letter "R":
SELECT * FROM Customers
WHERE Country = 'Spain'
AND CustomerName LIKE 'G%' OR CustomerName LIKE 'R%';
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, like if you want to
return all customers from Germany but also those from Spain:
Example
Select all customers from Germany or Spain:
SELECT *
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE condition1 OR condition2 OR condition3 ...;
OR vs AND
The OR operator displays a record if any of the conditions are TRUE.
The AND operator displays a record if all the conditions are TRUE.
Demo Database
The table below shows the complete "Customers" table from the Northwind sample database:
The following SQL statement selects all fields from Customers where either
City is "Berlin",
CustomerName starts with the letter "G" or
Country is "Norway":
Example
SELECT * FROM Customers
WHERE City = 'Berlin'
OR CustomerName LIKE 'G%'
OR Country = 'Norway';
Combining AND and OR
You can combine the AND and OR operators.
The following SQL statement selects all customers from Spain that starts with a "G" or an "R".
Make sure you use parenthesis to get the correct result.
Example
Select all Spanish customers that starts with either "G" or "R":
SELECT * FROM Customers
WHERE Country = 'Spain'
AND (CustomerName LIKE 'G%' OR CustomerName LIKE 'R%');
Without parenthesis, the select statement will return all customers from Spain that starts with a "G", plus
all customers that starts with an "R", regardless of the country value:
Example
Select all customers that either:
are from Spain and starts with either "G", or
starts with the letter "R":
SELECT * FROM Customers
WHERE Country = 'Spain'
AND CustomerName LIKE 'G%' OR CustomerName LIKE 'R%';
The NOT Operator
The NOT operator is used in combination with
other operators to give the opposite result, also called the negative result.
In the select statement below we want to return all customers that are NOT
from Spain:
Example
Select only the customers that are NOT from Spain:
SELECT * FROM Customers
WHERE NOT Country = 'Spain';
In the example above, the NOT operator is used in combination with the
= operator, but it can be used in combination with other comparison and/or logical operators.
See examples below.
Syntax
SELECT column1, column2, ...
FROM table_name
WHERE NOT condition;
Demo Database
The table below shows the complete "Customers" table from the Northwind sample database:
2. If you are adding values for all the columns of the table, you do not 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. Here, the
INSERT INTO syntax
would be as follows:
INSERT INTO table_name
VALUES (value1, value2, value3, ...);
Demo Database
Below is a selection from the "Customers" table in the Northwind
sample database:
CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country
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
INSERT INTO Example
The following SQL statement inserts a new record in the "Customers" table:
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
The selection from the "Customers" table will now look like this:
CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country
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
92
Cardinal
Tom B. Erichsen
Skagen 21
Stavanger
4006
Norway
Did you notice that we did not insert any number into the CustomerID
field? The CustomerID column is
an field and will be
generated automatically when a new record is inserted into the table.
Insert Data Only in Specified Columns
It is also possible to only insert data in specific columns.
The following SQL statement will insert a new record, but only insert data in the "CustomerName",
"City", and "Country" columns (CustomerID will
be updated automatically):
Example
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
The selection from the "Customers" table will now look like this:
CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country
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
92
Cardinal
null
nullÂ
Stavanger
null
Norway
Insert Multiple Rows
It is also possible to insert multiple rows in one statement.
To insert multiple rows of data, we use the same INSERT INTO statement, but with multiple values:
Make sure you separate each set of values with a comma ,.
The selection from the "Customers" table will now look like this:
CustomerID
CustomerName
ContactName
Address
City
PostalCode
Country
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
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
What is a NULL Value?
A field with a NULL value is a field with no value.
If a field in a table is optional, it is possible to insert a new record or
update a record without adding a value to this field. Then, the field will be
saved with a NULL value.
Note: A NULL value is different from a zero value or a field that
contains spaces. A field with a NULL value is one that has been left blank
during 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;
Demo Database
Below is a selection from the "Customers" table in the Northwind sample
database:
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:
Example
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).
The following SQL lists all customers with a value in the "Address" field:
Example
SELECT CustomerName, ContactName, Address FROM Customers WHERE Address
IS NOT NULL;
The SQL UPDATE Statement
The UPDATE statement is used to modify the existing 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!
Demo Database
Below is a selection from the "Customers" table in the Northwind sample
database:
The DELETE statement is used to delete existing records in a table.
DELETE Syntax
DELETE FROM table_name WHERE condition;
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!
Demo Database
Below is a selection from the "Customers" table in the Northwind sample
database:
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;
The following SQL statement deletes all rows in the "Customers" table,
without deleting the table:
Example
DELETE FROM Customers;
Delete a Table
To delete the table completely, use the DROP TABLE statement:
Example
Remove the Customers table:
DROP TABLE Customers;
The SQL SELECT TOP Clause
The SELECT TOP clause is used to specify the number of records to return.
The SELECT TOP clause is useful on large tables with thousands of
records. Returning a large number of records can impact performance.
Note: Not all database systems support the
SELECT TOP clause. MySQL
supports the LIMIT clause to select a limited number of records, while Oracle uses
FETCH FIRST n ROWS ONLY and ROWNUM.
SQL Server / MS Access Syntax:
SELECT TOP number|percentcolumn_name(s)
FROM table_name WHERE condition;
MySQL Syntax:
SELECT column_name(s)
FROM table_name WHERE condition
LIMIT number;
Oracle 12 Syntax:
SELECT column_name(s)
FROM table_name ORDER BY
column_name(s)
FETCH FIRST number ROWS ONLY;
Older Oracle Syntax:
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
Older Oracle Syntax (with ORDER BY):
SELECT * FROM (SELECT column_name(s) FROM table_name
ORDER BY column_name(s))
WHERE ROWNUM <= number;
Demo Database
Below is a selection from the "Customers" table in the Northwind sample database:
The following SQL statement selects all customers with a City starting with
"ber":
Example
SELECT * FROM Customers
WHERE City LIKE 'ber%';
The following SQL statement selects all customers with a City containing the
pattern "es":Â
Example
SELECT * FROM Customers
WHERE City LIKE '%es%';
Using the _ Wildcard
The following SQL statement selects all customers with a City starting with
any character, followed by "ondon":
Example
SELECT * FROM Customers
WHERE City LIKE '_ondon';
The following SQL statement selects all customers with a City starting with
"L", followed by any character, followed by "n", followed by any character,
followed by "on":
Example
SELECT * FROM Customers
WHERE City LIKE 'L_n_on';
Using the [charlist] Wildcard
The following SQL statement selects all customers with a City starting with
"b", "s", or "p":
Example
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';
The following SQL statement selects all customers with a City starting with
"a", "b", or "c":
Example
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';
Using the [!charlist] Wildcard
The two following SQL statements select all customers with a City NOT starting
with "b", "s", or "p":
Example
SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';
Or:
Example
SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';
The SQL 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.
IN Syntax
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1, value2, ...);
or:
SELECT column_name(s)
FROM table_name
WHERE column_name IN (SELECT STATEMENT);
Demo Database
The table below shows the complete "Customers" table from the Northwind sample database:
The following SQL statement creates two aliases, one for the CustomerID
column and one for the CustomerName column:
Example
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;
The following SQL statement creates two aliases, one for the CustomerName
column and one for the ContactName column. Note: It requires
double quotation marks or square brackets if the alias name contains spaces:
Example
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode,
City and Country):
Example
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country
AS Address
FROM Customers;
Note: To get the SQL statement above to work in MySQL use the following:
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;
Note: To get the SQL statement above to work in Oracle use the following:
SELECT CustomerName, (Address || ', ' || PostalCode || ' ' || City || ', ' ||
Country) AS Address FROM Customers;
Alias for Tables Example
The following SQL statement selects all the orders from the customer with
CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and
give them the table aliases of "c" and "o" respectively (Here we use
aliases to make the SQL shorter):
Example
SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName='Around the Horn' AND c.CustomerID=o.CustomerID;
The following SQL statement is the same as above, but without aliases:
Example
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName FROM Customers, Orders WHERE Customers.CustomerName='Around the Horn' AND Customers.CustomerID=Orders.CustomerID;
Aliases can be useful when:
There are more than one table involved in a query
Functions are used in the query
Column names are big or not very readable
Two or more columns are combined together
SQL Joins
SQL JOIN
A JOIN clause is used to combine rows from two or more tables, based on
a related column between them.
Let's look at a selection from the "Orders" table:
OrderID
CustomerID
OrderDate
10308
2
1996-09-18
10309
37
1996-09-19
10310
77
1996-09-20
Then, look at a selection from the "Customers" table:
CustomerID
CustomerName
ContactName
Country
1
Alfreds Futterkiste
Maria Anders
Germany
2
Ana Trujillo Emparedados y helados
Ana Trujillo
Mexico
3
Antonio Moreno TaquerÃa
Antonio Moreno
Mexico
Notice that the "CustomerID" column in the "Orders" table refers to the
"CustomerID" in the "Customers" table. The relationship between the two tables above
is the "CustomerID" column.
Then, we can create the following SQL statement (that contains an
INNER JOIN),
that selects records that have matching values in both tables:
Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate FROM Orders INNER JOIN Customers ON Orders.CustomerID=Customers.CustomerID;
and it will produce something like this:
OrderID
CustomerName
OrderDate
10308
Ana Trujillo Emparedados y helados
9/18/1996
10365
Antonio Moreno TaquerÃa
11/27/1996
10383
Around the Horn
12/16/1996
10355
Around the Horn
11/15/1996
10278
Berglunds snabbköp
8/12/1996
Learn to Filter Data in SQL Like a Data Analyst
Try a hands-on training sessions with step-by-step guidance from an expert. Try the guided project made in collaboration with Coursera now!
Different Types of SQL JOINs
Here 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 the matched
records from the left table
FULL (OUTER) JOIN: Returns all records when there is a match in either left
or right table
Â
Â
Â
SQL INNER JOIN Keyword
The INNER JOIN keyword selects records that have matching values in
both tables.
INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2 ON table1.column_name = table2.column_name;
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Try a hands-on training sessions with step-by-step guidance from an expert. Try the guided project made in collaboration with Coursera now!
SQL INNER JOIN Example
The following SQL statement selects all orders with customer information:
Example
SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID;
Note: The INNER JOIN keyword selects all rows from both
tables as long as there is a match between the columns. If there are records in the
"Orders" table that do not have matches in "Customers", these orders will not
be shown!
 JOIN Three Tables
The following SQL statement selects all orders with customer and shipper
information:
Example
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM
((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
SQL LEFT JOIN Keyword
The LEFT JOIN keyword returns all records from the left table (table1), and the
matching records from the right table (table2). The result is 0 records from the right side,
if there is no match.
LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2 ON table1.column_name = table2.column_name;
Note: In some databases LEFT JOIN is called LEFT OUTER JOIN.
Demo Database
In this tutorial we will use the well-known Northwind sample database.
The following SQL statement will select all customers, and any orders they
might have:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The LEFT JOIN keyword returns all records from the
left table (Customers), even if there are no matches in the right table
(Orders).
SQL RIGHT JOIN Keyword
The RIGHT JOIN keyword returns all records from the right table (table2), and the
matching records from the left table (table1). The result is 0 records from the left side,
if there is no match.
RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2 ON table1.column_name = table2.column_name;
Note: In some databases RIGHT JOIN is called
RIGHT OUTER JOIN.
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Below is a selection from the "Orders" table:
OrderID
CustomerID
EmployeeID
OrderDate
ShipperID
10308
2
7
1996-09-18
3
10309
37
3
1996-09-19
1
10310
77
8
1996-09-20
2
And a selection from the "Employees" table:
EmployeeID
LastName
FirstName
BirthDate
Photo
1
Davolio
Nancy
12/8/1968
EmpID1.pic
2
Fuller
Andrew
2/19/1952
EmpID2.pic
3
Leverling
Janet
8/30/1963
EmpID3.pic
SQL RIGHT JOIN Example
The following SQL statement will return all employees, and any orders they
might have placed:
Example
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
Note: The RIGHT JOIN keyword returns all records from the
right table (Employees), even if there are no matches in the left table
(Orders).
Learn to Filter Data in SQL Like a Data Analyst
Try a hands-on training sessions with step-by-step guidance from an expert. Try the guided project made in collaboration with Coursera now!
SQL FULL OUTER JOIN Keyword
The FULL OUTER JOIN keyword returns all records when there is a match in
left (table1) or right (table2) table records.
Tip:FULL OUTER JOIN and
FULL JOIN are the same.
FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2 ON table1.column_name = table2.column_name
WHERE condition;
Note:FULL OUTER JOIN can potentially return very large
result-sets!
Demo Database
In this tutorial we will use the well-known Northwind sample database.
Try a hands-on training sessions with step-by-step guidance from an expert. Try the guided project made in collaboration with Coursera now!
SQL FULL OUTER JOIN Example
The following SQL statement selects all customers, and all orders:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
A selection from the result set may look like this:
CustomerName
OrderID
Null
10309
Null
10310
Alfreds Futterkiste
Null
Ana Trujillo Emparedados y helados
10308
Antonio Moreno TaquerÃa
Null
Note: The FULL OUTER JOIN keyword returns all matching
records from both tables whether the other table matches or not. So, if
there are rows in "Customers" that do not have matches in "Orders", or if there
are rows in "Orders" that do not have matches in "Customers", those rows will be
listed as well.
SQL Self Join
SQL Self Join
A self join is a regular join, but the table is joined with itself.
Self Join Syntax
SELECT column_name(s)
FROM table1 T1, table1 T2
WHERE condition;
T1 and T2 are different table aliases for the same table.
Demo Database
In this tutorial we will use the well-known Northwind sample database.
The following SQL statement matches customers that are from the same city:
Example
SELECT A.CustomerName AS CustomerName1, B.CustomerName AS CustomerName2,
A.City FROM Customers A, Customers B WHERE A.CustomerID <> B.CustomerID
AND A.City = B.City ORDER BY A.City;
The SQL UNION Operator
The UNION operator is used to combine the result-set of two or more
SELECT
statements.
Every SELECT statement within
UNION must have the same number
of columns
The columns must also have similar data types
The columns in
every SELECT statement must also be in the same order
UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
UNION ALL Syntax
The UNION operator selects only distinct values by default. To allow
duplicate values, use UNION ALL:
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
Note: The column names in the result-set are usually equal to
the column names in the first SELECT statement.
Demo Database
In this tutorial we will use the well-known Northwind sample database.
The following SQL statement returns the cities
(only distinct values) from both the "Customers" and the "Suppliers" table:
Example
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
Note: If some customers or suppliers have the same city, each city will only be
listed once, because UNION selects only distinct values. Use
UNION ALL to also select
duplicate values!
SQL UNION ALL Example
The following SQL statement returns the cities
(duplicate values also) from both the "Customers" and the "Suppliers" table:
Example
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers
ORDER BY City;
SQL UNION With WHERE
The following SQL statement returns the German cities
(only distinct values) from both the "Customers" and the "Suppliers" table:
Example
SELECT City, Country FROM Customers WHERE Country='Germany' UNION
SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
SQL UNION ALL With WHERE
The following SQL statement returns the German cities (duplicate values also) from
both the "Customers" and the "Suppliers" table:
Example
SELECT City, Country FROM Customers WHERE Country='Germany' UNION ALL
SELECT City, Country FROM Suppliers WHERE Country='Germany' ORDER BY City;
Another UNION Example
The following SQL statement lists all customers and suppliers:
Example
SELECT 'Customer' AS Type, ContactName, City, Country FROM Customers
UNION SELECT 'Supplier', ContactName, City, Country FROM Suppliers;
Notice the "AS Type" above - it is an alias. are used to give a table or a column a temporary name.
An alias only exists for the duration of the query. So, here we have created a
temporary column named "Type", that list whether the contact person is a
"Customer" or a "Supplier".
The SQL GROUP BY Statement
The GROUP BY statement groups rows that have the same values into summary
rows, like "find the number of customers in each country".
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.
GROUP BY Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s) ORDER BY column_name(s);
Demo Database
Below is a selection from the "Customers" table in the Northwind sample
database:
The following SQL statement lists the number of customers in each country:
Example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
The following SQL statement lists the number of customers in each country,
sorted high to low:
Example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
Demo Database
Below is a selection from the "Orders" table in the Northwind sample database:
OrderID
CustomerID
EmployeeID
OrderDate
ShipperID
10248
90
5
1996-07-04
3
10249
81
6
1996-07-05
1
10250
34
4
1996-07-08
2
And a selection from the "Shippers" table:
ShipperID
ShipperName
1
Speedy Express
2
United Package
3
Federal Shipping
GROUP BY With JOIN Example
The following SQL statement lists the number of orders sent by each shipper:
Example
SELECT Shippers.ShipperName, COUNT(Orders.OrderID) AS NumberOfOrders FROM
Orders LEFT JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID
GROUP BY ShipperName;
The SQL HAVING Clause
The HAVING clause was added to SQL because the
WHERE keyword cannot be
used with aggregate functions.
HAVING Syntax
SELECT column_name(s)
FROM table_name
WHERE condition
GROUP BY column_name(s) HAVING condition ORDER BY
column_name(s);
Demo Database
Below is a selection from the "Customers" table in the Northwind sample
database:
The following SQL statement lists the number of customers in each country.
Only include countries with more than 5 customers:
Example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country
HAVING COUNT(CustomerID) > 5;
The following SQL statement lists the number of customers in each country,
sorted high to low (Only include countries with more than 5 customers):
Example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID)
> 5
ORDER BY COUNT(CustomerID) DESC;
Demo Database
Below is a selection from the "Orders" table in the Northwind sample database:
OrderID
CustomerID
EmployeeID
OrderDate
ShipperID
10248
90
5
1996-07-04
3
10249
81
6
1996-07-05
1
10250
34
4
1996-07-08
2
And a selection from the "Employees" table:
EmployeeID
LastName
FirstName
BirthDate
Photo
Notes
1
Davolio
Nancy
1968-12-08
EmpID1.pic
Education includes a BA....
2
Fuller
Andrew
1952-02-19
EmpID2.pic
Andrew received his BTS....
3
Leverling
Janet
1963-08-30
EmpID3.pic
Janet has a BS degree....
More HAVING Examples
The following SQL statement lists the employees that have registered more
than 10 orders:
Example
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM
(Orders INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID)
GROUP BY LastName HAVING COUNT(Orders.OrderID) > 10;
The following SQL statement lists if the employees "Davolio" or "Fuller" have registered
more than 25 orders:
Example
SELECT Employees.LastName, COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
INNER JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
WHERE LastName = 'Davolio' OR LastName = 'Fuller' GROUP BY LastName HAVING
COUNT(Orders.OrderID) > 25;
SQL EXISTS Operator
The SQL EXISTS Operator
The EXISTS operator is used to test for the existence of any record in a subquery.
The EXISTS operator returns TRUE if the subquery returns one or more records.
EXISTS Syntax
SELECT column_name(s)
FROM table_name
WHERE
EXISTS (SELECT column_name FROM table_name WHERE
condition);
Demo Database
Below is a selection from the "Products" table in the Northwind sample database:
ProductID
ProductName
SupplierID
CategoryID
Unit
Price
1
Chais
1
1
10 boxes x 20 bags
18
2
Chang
1
1
24 - 12 oz bottles
19
3
Aniseed Syrup
1
2
12 - 550 ml bottles
10
4
Chef Anton's Cajun Seasoning
2
2
48 - 6 oz jars
22
5
Chef Anton's Gumbo Mix
2
2
36 boxes
21.35
And a selection from the "Suppliers" table:
SupplierID
SupplierName
ContactName
Address
City
PostalCode
Country
1
Exotic Liquid
Charlotte Cooper
49 Gilbert St.
London
EC1 4SD
UK
2
New Orleans Cajun Delights
Shelley Burke
P.O. Box 78934
New Orleans
70117
USA
3
Grandma Kelly's Homestead
Regina Murphy
707 Oxford Rd.
Ann Arbor
48104
USA
4
Tokyo Traders
Yoshi Nagase
9-8 Sekimai Musashino-shi
Tokyo
100
Japan
SQL EXISTS Examples
The following SQL statement returns TRUE and lists the suppliers with a product price less than 20:
Example
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM
Products WHERE Products.SupplierID = Suppliers.supplierID AND Price < 20);
The following SQL statement returns TRUE and lists the suppliers with a product price
equal to 22:
Example
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM
Products WHERE Products.SupplierID = Suppliers.supplierID AND Price = 22);
SQL ANY and ALL Operators
The SQL 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.
The 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.
ANY 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 <=).
The 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.Â
ALL Syntax With SELECT
SELECT ALL column_name(s)
FROM table_name
WHERE
condition;
ALL Syntax With WHERE or HAVING
SELECT column_name(s)
FROM table_name
WHERE
column_name operator ALL  (SELECT column_name  FROM table_name  WHERE condition);
Note: The operator must be a standard comparison
operator (=, <>, !=, >, >=, <, or <=).
Demo Database
Below is a selection from the "Products" table in the Northwind sample database:
ProductID
ProductName
SupplierID
CategoryID
Unit
Price
1
Chais
1
1
10 boxes x 20 bags
18
2
Chang
1
1
24 - 12 oz bottles
19
3
Aniseed Syrup
1
2
12 - 550 ml bottles
10
4
Chef Anton's Cajun Seasoning
2
2
48 - 6 oz jars
22
5
Chef Anton's Gumbo Mix
2
2
36 boxes
21.35
6
Grandma's Boysenberry Spread
3
2
12 - 8 oz jars
25
7
Uncle Bob's Organic Dried Pears
3
7
12 - 1 lb pkgs.
30
8
Northwoods Cranberry Sauce
3
2
12 - 12 oz jars
40
9
Mishi Kobe Niku
4
6
18 - 500 g pkgs.
97
And a selection from the "OrderDetails" table:
OrderDetailID
OrderID
ProductID
Quantity
1
10248
11
12
2
10248
42
10
3
10248
72
5
4
10249
14
9
5
10249
51
40
6
10250
41
10
7
10250
51
35
8
10250
65
15
9
10251
22
6
10
10251
57
15
SQL ANY Examples
The following SQL statement lists the ProductName if it
finds ANY records in the OrderDetails table has Quantity equal to 10 (this will
return TRUE because the Quantity column has some values of 10):
Example
SELECT ProductName FROM Products WHERE ProductID = ANY  (SELECT ProductID  FROM OrderDetails  WHERE Quantity = 10);
The following SQL statement lists the ProductName if it
finds ANY records in the OrderDetails table has Quantity larger than 99 (this
will return TRUE because the Quantity column has some values larger than 99):
Example
SELECT ProductName FROM Products WHERE ProductID = ANY Â (SELECT ProductID Â
FROM OrderDetails  WHERE Quantity > 99);
The following SQL statement lists the ProductName if it
finds ANY records in the OrderDetails table has Quantity larger than 1000 (this
will return FALSE because the Quantity column has no values larger than 1000):
Example
SELECT ProductName FROM Products WHERE ProductID = ANY Â (SELECT ProductID Â
FROM OrderDetails  WHERE Quantity > 1000);
SQL ALL Examples
The following SQL statement lists ALL the product names:
Example
SELECT ALL ProductName FROM Products WHERE TRUE;
The following SQL statement lists the ProductName if ALL the records in the
OrderDetails table has Quantity equal to 10. This will of course return FALSE
because the Quantity column has many different values (not only the value of 10):
Example
SELECT ProductName FROM Products WHERE ProductID = ALL  (SELECT ProductID  FROM OrderDetails  WHERE Quantity = 10);
The SQL SELECT INTO Statement
The SELECT INTO statement copies data from one table into a new table.
SELECT INTO Syntax
Copy all columns into a new table:
SELECT *
INTO newtable [IN externaldb]
FROM oldtable WHERE condition;
Copy only some columns into a new table:
SELECT column1, column2, column3, ...
INTO newtable [IN externaldb]
FROM oldtable WHERE condition;
The new table will be created with the column-names and types as defined in the old table.
You can create new column names using the AS clause.
SQL SELECT INTO Examples
The following SQL statement creates a backup copy of Customers:
SELECT * INTO CustomersBackup2017
FROM Customers;
The following SQL statement uses the IN clause to copy the table into a new
table in another database:
SELECT *
INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;
The following SQL statement copies only a few columns into a new table:
SELECT CustomerName, ContactName INTO CustomersBackup2017
FROM Customers;
The following SQL statement copies only the German customers into a new table:
SELECT *
INTO CustomersGermany
FROM Customers
WHERE Country = 'Germany';
The following SQL statement copies data from more than one table into a new table:
SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
Tip:SELECT INTO can also be used to create a
new, empty table using the schema of another. Just add a
WHERE clause that
causes the query to return no data:
SELECT * INTO newtable FROM oldtable WHERE 1 = 0;
The SQL INSERT INTO SELECT Statement
The INSERT INTO SELECT statement copies data from one table and
inserts it into another table.
The INSERT INTO SELECT statement requires that
the data types in source and target tables match.
Note: The existing records in the target table are unaffected.
INSERT INTO SELECT Syntax
Copy all columns from one table to another table:
INSERT INTO table2
SELECT * FROM table1 WHERE condition;
Copy only some columns from one table into another table:
INSERT INTO table2 (column1, column2, column3, ...)
SELECT column1, column2, column3, ... FROM table1
WHERE condition;
Demo Database
In this tutorial we will use the well-known Northwind sample database.
The following SQL statement copies "Suppliers" into "Customers" (the columns
that are not filled with data, will contain NULL):
Example
INSERT INTO Customers (CustomerName,
City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
The following SQL statement copies "Suppliers" into "Customers" (fill all
columns):
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode,
Country) SELECT SupplierName, ContactName, Address, City, PostalCode,
Country FROM Suppliers;
The following SQL statement copies only the German suppliers into "Customers":
Example
INSERT INTO Customers (CustomerName,
City, Country)
SELECT SupplierName, City, Country FROM Suppliers WHERE Country='Germany';
The SQL CASE Expression
The CASE expression goes through conditions and returns a value when the first condition is
met (like an if-then-else statement). So, once a condition is true, it will stop
reading and return the result. If no conditions are true, it returns
the value in the ELSE clause.
If there is no ELSE part and no conditions are true, it returns NULL.
CASE Syntax
CASE Â Â Â WHEN condition1 THEN result1 Â Â Â
WHEN condition2 THEN result2 Â Â Â WHEN
conditionN THEN resultN Â Â Â ELSE result END;
Demo Database
Below is a selection from the "OrderDetails" table in the Northwind sample database:
OrderDetailID
OrderID
ProductID
Quantity
1
10248
11
12
2
10248
42
10
3
10248
72
5
4
10249
14
9
5
10249
51
40
SQL CASE Examples
The following SQL goes through conditions and returns a value when the first condition is met:
Example
SELECT OrderID, Quantity, CASE Â Â Â WHEN Quantity > 30
THEN 'The quantity is greater than 30' Â Â Â WHEN Quantity = 30 THEN 'The
quantity is 30' Â Â Â ELSE 'The quantity is under 30' END AS QuantityText FROM OrderDetails;
The following SQL will order the customers by City. However, if City is NULL, then order by Country:
Example
SELECT CustomerName, City, Country FROM Customers ORDER BY (CASE Â Â Â
WHEN City IS NULL THEN Country    ELSE City END);
SQL IFNULL(), ISNULL(), COALESCE(), and NVL() Functions
Look at the following "Products" table:
P_Id
ProductName
UnitPrice
UnitsInStock
UnitsOnOrder
1
Jarlsberg
10.45
16
15
2
Mascarpone
32.56
23
Â
3
Gorgonzola
15.67
9
20
Suppose that the "UnitsOnOrder" column is optional, and may contain NULL values.
Look at the following SELECT statement:
SELECT ProductName, UnitPrice * (UnitsInStock + UnitsOnOrder)
FROM Products;
In the example above, if any of the "UnitsOnOrder" values are NULL, the result
will be NULL.
Solutions
MySQL
The MySQL function lets you
return an alternative value if an expression is NULL:
SELECT ProductName, UnitPrice * (UnitsInStock + IFNULL(UnitsOnOrder, 0))
FROM Products;
or we can use the function, like this:
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;
SQL Server
The SQL Server function lets
you return an alternative value when an expression is NULL:
SELECT ProductName, UnitPrice * (UnitsInStock + ISNULL(UnitsOnOrder, 0))
FROM Products;
or we can use the
function, like this:
SELECT ProductName, UnitPrice * (UnitsInStock + COALESCE(UnitsOnOrder, 0))
FROM Products;
MS Access
The MS Access function returns
TRUE (-1) if the expression is a null value, otherwise FALSE (0):
The following SQL statement creates a stored procedure named "SelectAllCustomers"
that selects all records from the "Customers" table:
Example
CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO;
Execute the stored procedure above as follows:
Example
EXEC SelectAllCustomers;
Stored Procedure With One Parameter
The following SQL statement creates a stored procedure
that selects Customers from a particular City from the "Customers" table:
Example
CREATE PROCEDURE SelectAllCustomers
@City nvarchar(30) AS SELECT * FROM Customers WHERE
City = @City GO;
Execute the stored procedure above as follows:
Example
EXEC SelectAllCustomers @City = 'London';
Stored Procedure With Multiple Parameters
Setting up multiple parameters is very easy. Just list each parameter and the
data type separated by a comma as shown below.
The following SQL statement creates a stored procedure
that selects Customers from a particular City with a particular PostalCode from the "Customers" table:
Example
CREATE PROCEDURE SelectAllCustomers
@City nvarchar(30), @PostalCode nvarchar(10) AS SELECT * FROM Customers WHERE
City = @City AND PostalCode = @PostalCode GO;
Comments are used to explain sections of SQL statements, or to prevent execution
of SQL statements.
Note: The examples in this chapter will not work in Firefox and
Microsoft Edge!
Comments are not supported in Microsoft Access databases. Firefox and Microsoft Edge are using Microsoft Access database in our examples.
Single Line Comments
Single line comments start with --.
Any text between -- and the end of the line will be ignored (will not be executed).
The following example uses a single-line comment as an explanation:
Example
--Select all:
SELECT * FROM Customers;
The following example uses a single-line comment to ignore the end of a line:
Example
SELECT * FROM Customers -- WHERE City='Berlin';
The following example uses a single-line comment to ignore a statement:
Example
--SELECT * FROM Customers;
SELECT * FROM Products;
Multi-line Comments
Multi-line comments start with /* and end with
*/.
Any text between /* and */ will be ignored.
The following example uses a multi-line comment as an explanation:
Example
/*Select all the columns
of all the records
in the Customers table:*/
SELECT * FROM Customers;
The following example uses a multi-line comment to ignore many statements:
Example
/*SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;
SELECT * FROM Categories;*/
SELECT * FROM Suppliers;
To ignore just a part of a statement, also use the /* */ comment.
The following example uses a comment to ignore part of a line:
Example
SELECT CustomerName, /*City,*/ Country FROM Customers;
The following example uses a comment to ignore part of a statement:
Example
SELECT * FROM Customers WHERE (CustomerName LIKE 'L%'
OR CustomerName LIKE 'R%' /*OR CustomerName LIKE 'S%'
OR CustomerName LIKE 'T%'*/ OR CustomerName LIKE 'W%')
AND Country='USA'
ORDER BY CustomerName;
SQL Operators
SQL Arithmetic Operators
Operator
Description
Example
+
Add
-
Subtract
*
Multiply
/
Divide
%
Modulo
SQL Bitwise Operators
Operator
Description
&
Bitwise AND
|
Bitwise OR
^
Bitwise exclusive OR
SQL Comparison Operators
Operator
Description
Example
=
Equal to
>
Greater than
<
Less than
>=
Greater than or equal to
<=
Less than or equal to
<>
Not equal to
SQL Compound Operators
Operator
Description
+=
Add equals
-=
Subtract equals
*=
Multiply equals
/=
Divide equals
%=
Modulo equals
&=
Bitwise AND equals
^-=
Bitwise exclusive equals
|*=
Bitwise OR equals
SQL Logical Operators
Operator
Description
Example
ALL
TRUE if all of the subquery values meet the condition
AND
TRUE if all the conditions separated by AND is TRUE
ANY
TRUE if any of the subquery values meet the condition
BETWEEN
TRUE if the operand is within the range of comparisons
EXISTS
TRUE if the subquery returns one or more records
IN
TRUE if the operand is equal to one of a list of expressions
LIKE
TRUE if the operand matches a pattern
NOT
Displays a record if the condition(s) is NOT TRUE
OR
TRUE if any of the conditions separated by OR is TRUE
SOME
TRUE if any of the subquery values meet the condition
The SQL CREATE DATABASE Statement
The CREATE DATABASE statement is used to create a new SQL database.
Syntax
CREATE DATABASE databasename;
CREATE DATABASE Example
The following SQL statement creates a database called "testDB":
Example
CREATE DATABASE testDB;
Tip: Make sure you have admin privilege before creating any database. Once a
database is created, you can check it in the list of databases with the
following SQL command: SHOW DATABASES;
The SQL DROP DATABASE Statement
The DROP DATABASE statement is used to drop an existing SQL database.
Syntax
DROP DATABASE databasename;
Note: Be careful before dropping a database. Deleting
a database will result in loss of complete information stored in the database!
DROP DATABASE Example
The following SQL statement drops the existing database "testDB":
Example
DROP DATABASE testDB;
Tip: Make sure you have admin privilege before dropping any database. Once a
database is dropped, you can check it in the list of databases with the
following SQL command: SHOW DATABASES;
The SQL BACKUP DATABASE Statement
The BACKUP DATABASE statement is used in SQL Server to create a full back up of an existing SQL database.
Syntax
BACKUP DATABASE databasename TO DISK = 'filepath';
The SQL BACKUP WITH DIFFERENTIAL Statement
A differential back up only backs up the parts of the database that have
changed since the last full database backup.
Syntax
BACKUP DATABASE databasename TO DISK = 'filepath' WITH
DIFFERENTIAL;
BACKUP DATABASE Example
The following SQL statement creates a full back up of the existing database "testDB"
to the D disk:
Example
BACKUP DATABASE testDB TO DISK = 'D:backupstestDB.bak';
Tip: Always back up the database to a different drive than the
actual database. Then, if you get a disk crash, you will not lose your backup
file along with the database.
BACKUP WITH DIFFERENTIAL Example
The following SQL statement creates a differential back up of the database "testDB":
Example
BACKUP DATABASE testDB TO DISK = 'D:backupstestDB.bak' WITH
DIFFERENTIAL;
Tip: A differential back up reduces the back up time (since only the changes are backed up).
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, Â Â Â column2 datatype, Â Â Â column3 datatype,
 Â
....
);
The column parameters specify the names of the columns of the table.
The datatype parameter specifies the type of data the column can hold (e.g. varchar, integer, date, etc.).
Tip: For an overview of the available data types,
go to our complete .
SQL CREATE TABLE Example
The following example creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName,
Address, and City:
Example
CREATE TABLE Persons
(
  Â
PersonID int,
  Â
LastName varchar(255),
  Â
FirstName varchar(255),
  Â
Address varchar(255),
  Â
City varchar(255)
);
The PersonID column is of type int and will hold an integer.
The LastName, FirstName, Address, and City columns are of
type varchar and will hold characters, and the maximum length for these fields
is 255 characters.
The empty "Persons" table will now look like this:
PersonID
LastName
FirstName
Address
City
Â
Â
Â
Â
Â
Tip: The empty "Persons" table can now be filled with data with the
SQL statement.
Create Table Using Another Table
A copy of an existing table can also be created using
CREATE TABLE.
The new table gets the same column definitions. All columns or specific
columns can be selected.
If you create a new table using an existing table, the new table will be
filled with the existing values from the old table.
Syntax
CREATE TABLE new_table_name AS Â Â Â
SELECT column1, column2,... Â Â Â FROM
existing_table_name    WHERE ....;
The following SQL creates a new table called "TestTables" (which is
a copy of the "Customers" table):Â
Example
CREATE TABLE TestTable AS SELECT customername, contactname FROM
customers;
The SQL DROP TABLE Statement
The DROP TABLE statement is used to drop an existing table in a database.
Syntax
DROP TABLE table_name;
Note: Be careful before dropping a table. Deleting a
table will result in loss of complete information stored in the table!
SQL DROP TABLE Example
The following SQL statement drops the existing table "Shippers":
Example
DROP TABLE Shippers;
SQL TRUNCATE TABLE
The TRUNCATE TABLE statement is used to delete the data inside a table, but
not the table itself.
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.
ALTER TABLE - ADD Column
To add a column in a table, use the following syntax:
ALTER TABLE table_name
ADD column_name datatype;
The following SQL adds an "Email" column to the "Customers" table:
Example
ALTER TABLE Customers ADD Email varchar(255);
ALTER TABLE - DROP COLUMN
To delete a column in a table, use the following syntax (notice that some
database systems don't allow deleting a column):
ALTER TABLE table_name
DROP COLUMN column_name;
The following SQL deletes the "Email" column from the "Customers" table:
Example
ALTER TABLE Customers DROP COLUMN Email;
ALTER TABLE - RENAME COLUMN
To rename a column in a table, use the following syntax:
ALTER TABLE table_name
RENAME COLUMN old_name to new_name;
ALTER TABLE - ALTER/MODIFY DATATYPE
To change the data type of a column in a table, use the following syntax:
SQL Server / MS Access:
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
My SQL / Oracle (prior version 10G):
ALTER TABLE table_name
MODIFY COLUMN column_name datatype;
Oracle 10G and later:
ALTER TABLE table_name
MODIFY column_name datatype;
SQL ALTER TABLE Example
Look at the "Persons" table:
ID
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
Now we want to add a column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
ALTER TABLE Persons
ADD DateOfBirth date;
Notice that the new column, "DateOfBirth", is of type date and is going to hold a
date. The data type specifies what type of data the column can hold. For a complete
reference of all the data types available in MS Access, MySQL, and SQL Server,
go to our complete .
The "Persons" table will now look like this:
ID
LastName
FirstName
Address
City
DateOfBirth
1
Hansen
Ola
Timoteivn 10
Sandnes
Â
2
Svendson
Tove
Borgvn 23
Sandnes
Â
3
Pettersen
Kari
Storgt 20
Stavanger
Â
Change Data Type Example
Now we want to change the data type of the column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year;
Notice that the "DateOfBirth" column is now of type year and is going to hold a year in a two- or four-digit format.
DROP COLUMN Example
Next, we want to delete the column named "DateOfBirth" in the "Persons" table.
We use the following SQL statement:
ALTER TABLE Persons
DROP COLUMN DateOfBirth;
The "Persons" table will now look like this:
ID
LastName
FirstName
Address
City
1
Hansen
Ola
Timoteivn 10
Sandnes
2
Svendson
Tove
Borgvn 23
Sandnes
3
Pettersen
Kari
Storgt 20
Stavanger
SQL Constraints
SQL constraints are used to specify rules for data in a table.
SQL Create Constraints
Constraints can be specified when the table is created with the
CREATE TABLE statement, or after the table is created
with the ALTER TABLE statement.
Syntax
CREATE TABLE table_name ( Â Â Â column1 datatypeconstraint, Â Â Â column2 datatypeconstraint, Â Â Â column3 datatypeconstraint,
  Â
....
);
SQL Constraints
SQL constraints are used to specify rules for the data in a table.
Constraints are used to limit the type of data that can go into a table. This
ensures the accuracy and reliability of the data in the table. If there is any violation between the constraint and the data action,
the action is aborted.
Constraints can be column level or table level. Column level constraints
apply to a column, and table level constraints apply to the whole table.
The following constraints are commonly used in SQL:
- Ensures that a column cannot have a NULL value
- Ensures that all values in a column are
different
- A combination of a
NOT NULL and UNIQUE.
Uniquely identifies each row in a table
- Prevents actions that would destroy links between tables
- Ensures that
the values in a column satisfies a specific condition
- Sets a default value for a column
if no value
is specified
- Used to create and retrieve data from the database
very quickly
SQL NOT NULL Constraint
By default, a column can hold NULL values.
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.
SQL NOT NULL on CREATE TABLE
The following SQL ensures that the "ID", "LastName", and
"FirstName" columns
will NOT accept NULL values when the "Persons" table is created:
Example
CREATE TABLE Persons ( Â Â Â ID int NOT NULL, Â Â Â
LastName varchar(255) NOT NULL, Â Â Â FirstName varchar(255)
NOT NULL, Â Â Â Age int );
SQL NOT NULL on ALTER TABLE
To create a NOT NULL constraint on the "Age" column when the "Persons" table is already created, use the following SQL:
SQL Server / MS Access:
ALTER TABLE Persons
ALTER COLUMN Age int NOT NULL;
My SQL / Oracle (prior version 10G):
ALTER TABLE Persons
MODIFY COLUMN Age int NOT NULL;
Oracle 10G and later:
ALTER TABLE Persons
MODIFY Age int NOT NULL;
SQL UNIQUE Constraint
The UNIQUE constraint ensures that all values in a column are different.
Both the UNIQUE and
PRIMARY KEY constraints provide a guarantee for uniqueness for a column or set of columns.
A PRIMARY KEY constraint automatically has a
UNIQUE constraint.
However, you can have many UNIQUE constraints per table, but only one
PRIMARY KEY constraint per table.
SQL UNIQUE Constraint on CREATE TABLE
The following SQL creates a UNIQUE constraint on the "ID" column when the "Persons" table is created:
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)
);
To name a UNIQUE constraint, and to define a
UNIQUE constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
   ID int NOT NULL,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
  Â
Age int,
  Â
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);
SQL UNIQUE Constraint on ALTER TABLE
To create a UNIQUE constraint on the "ID" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD UNIQUE (ID);
To name a UNIQUE constraint, and to define a
UNIQUE constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
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 table.
Primary keys must contain UNIQUE values, and cannot contain NULL values.
A table can have only ONE primary key; and in the table, this primary key can
consist of single or multiple columns (fields).
SQL PRIMARY KEY on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:
MySQL:
CREATE TABLE Persons
(
   ID int NOT NULL,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
  Â
Age int,
  Â
PRIMARY KEY (ID)
);
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
   ID int NOT NULL PRIMARY KEY,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
  Â
Age int
);
To allow naming of a PRIMARY KEY constraint, and for defining a
PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
   ID int NOT NULL,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
  Â
Age int,
  Â
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
Note: In the example above there is only ONE
PRIMARY KEY (PK_Person).
However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).
SQL PRIMARY KEY on ALTER TABLE
To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
To allow naming of a PRIMARY KEY constraint, and for defining a
PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
Note: If you use ALTER TABLE to add a primary key, the primary key column(s) must
have been declared to not contain NULL values (when the table was first created).
DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
DROP PRIMARY KEY;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
SQL FOREIGN KEY Constraint
The FOREIGN KEY constraint is used to prevent actions that would destroy links between tables.
A FOREIGN KEY is a field (or collection of fields) in one table, that refers to
the in another table.
The table with the foreign key is called the child table, and the table
with the primary key is called the referenced or parent table.
Look at the following two tables:
Persons Table
PersonID
LastName
FirstName
Age
1
Hansen
Ola
30
2
Svendson
Tove
23
3
Pettersen
Kari
20
Orders Table
OrderID
OrderNumber
PersonID
1
77895
3
2
44678
3
3
22456
2
4
24562
1
Notice that the "PersonID" column in the "Orders" table points to the "PersonID" column in the "Persons" table.
The "PersonID" column in the "Persons" table is the PRIMARY KEY in the "Persons" table.
The "PersonID" column in the "Orders" table is a
FOREIGN KEY in the "Orders" table.
The FOREIGN KEY constraint prevents invalid data from being inserted into the foreign key column,
because it has to be one of the values contained in the parent table.
SQL FOREIGN KEY on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:
MySQL:
CREATE TABLE Orders
(
  Â
OrderID int NOT NULL,
  Â
OrderNumber int NOT NULL,
  Â
PersonID int,
  Â
PRIMARY KEY (OrderID),
  Â
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
  Â
OrderID int NOT NULL PRIMARY KEY,
  Â
OrderNumber int NOT NULL,
  Â
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
To allow naming of a FOREIGN KEY constraint, and for defining a
FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
  Â
OrderID int NOT NULL,
  Â
OrderNumber int NOT NULL,
  Â
PersonID int,
  Â
PRIMARY KEY (OrderID),
  Â
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
  Â
REFERENCES Persons(PersonID)
);
SQL FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID);
To allow naming of a FOREIGN KEY constraint, and for defining a
FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
SQL CHECK Constraint
The CHECK constraint is used to limit the value range that can be placed in a column.
If you define a CHECK constraint on a column it
will allow only certain values for this column.
If you define a CHECK constraint on a table it can limit the values in certain columns based on values in other columns in the row.
SQL CHECK on CREATE TABLE
The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created.
The CHECK constraint ensures that the age of a person must be 18, or older:
MySQL:
CREATE TABLE Persons
(
   ID int NOT NULL,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
  Â
Age int,
  Â
CHECK (Age>=18)
);
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
   ID int NOT NULL,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
  Â
Age int CHECK (Age>=18)
);
To allow naming of a CHECK constraint, and for defining a
CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
   ID int NOT NULL,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
  Â
Age int,
  Â
City varchar(255),
  Â
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
SQL CHECK on ALTER TABLE
To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CHECK (Age>=18);
To allow naming of a CHECK constraint, and for defining a
CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
DROP a CHECK Constraint
To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
MySQL:
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
SQL DEFAULT Constraint
The DEFAULT constraint is used to set a default value for a column.
The default value will be added to all new records, if no other value is specified.
SQL DEFAULT on CREATE TABLE
The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created:
My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
   ID int NOT NULL,
  Â
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 :
CREATE TABLE Orders
(
   ID int NOT NULL,
  Â
OrderNumber int NOT NULL,
  Â
OrderDate date DEFAULT GETDATE()
);
SQL DEFAULT on ALTER TABLE
To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:
MySQL:
ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';
SQL Server:
ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City;
MS Access:
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';
Oracle:
ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';
DROP a DEFAULT Constraint
To drop a DEFAULT constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
ALTER City DROP DEFAULT;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
SQL Server:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
SQL CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes in tables.
Indexes are used to retrieve data from the database more quickly than
otherwise. The users cannot see the indexes, they are just used to speed up searches/queries.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update).
So, only create indexes on columns that will be frequently searched against.
CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column1, column2, ...);
CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column1, column2, ...);
Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.
CREATE INDEX Example
The SQL statement below creates an 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_pname
ON Persons (LastName, FirstName);
DROP INDEX Statement
The DROP INDEX statement is used to delete an index in a table.
MS Access:
DROP INDEX index_name ON table_name;
SQL Server:
DROP INDEX table_name.index_name;
DB2/Oracle:
DROP INDEX index_name;
MySQL:
ALTER TABLE table_name DROP INDEX index_name;
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 we would like to be created automatically every time a new record is inserted.
Syntax for MySQL
The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:
CREATE TABLE Persons
(
  Â
Personid int NOT NULL AUTO_INCREMENT,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
   Age int,
  Â
PRIMARY KEY (Personid)
);
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;
To 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 SQL statement above would insert a new record into the "Persons" table. The
"Personid" column would be assigned a unique value. The "FirstName" column would be set to
"Lars" and the "LastName" column would be set to "Monsen".
Syntax for SQL Server
The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:
CREATE TABLE Persons
(
  Â
Personid int IDENTITY(1,1) PRIMARY KEY,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
  Â
Age int
);
The MS SQL Server uses the IDENTITY keyword to perform an auto-increment feature.
In the example above, the starting value for
IDENTITY is 1, and it will increment by 1 for each new record.
Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change
it to IDENTITY(10,5).
To 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 SQL statement above would insert a new record into the "Persons" table. The
"Personid" column would be assigned a unique value. The "FirstName" column would be set to
"Lars" and the "LastName" column would be set to "Monsen".
Syntax for Access
The following SQL statement defines the "Personid" column to be an auto-increment primary key field in the "Persons" table:
CREATE TABLE Persons
(
  Â
Personid AUTOINCREMENT PRIMARY KEY,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
  Â
Age int
);
The MS Access uses the AUTOINCREMENT keyword to perform an auto-increment feature.
By default, the starting value for AUTOINCREMENT is 1, and it will increment by 1 for each new record.
Tip: To specify that the "Personid" column should start at value 10 and increment by 5, change the autoincrement to
AUTOINCREMENT(10,5).
To 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 SQL statement above would insert a new record into the "Persons" table. The
"Personid" column would be assigned a unique value. The "FirstName" column would be set to
"Lars" and the "LastName" column would be set to "Monsen".
Syntax for Oracle
In Oracle the code is a little bit more tricky.
You will have to create an auto-increment field with the sequence object (this object generates a number sequence).
Use the following CREATE SEQUENCE syntax:
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10;
The code above creates a sequence object called seq_person, that starts with 1 and will increment by 1.
It will also cache up to 10 values for performance. The cache option specifies how many sequence values will be stored in memory for faster access.
To insert a new record into the "Persons" table, we will have to use the nextval function (this function retrieves the next value from seq_person
sequence):
INSERT INTO Persons (Personid,FirstName,LastName)
VALUES (seq_person.nextval,'Lars','Monsen');
The SQL statement above would insert a new record into the "Persons" table. The "Personid" column would be assigned the next number from the seq_person
sequence. The "FirstName" column would be set to "Lars" and the "LastName" column would be set to "Monsen".
SQL Working With Dates
SQL Dates
The most difficult part when working with dates is to be sure that the format of the date you are trying to insert,
matches the format of the date column in the database.
As long as your data contains only the date portion, your queries will work as expected. However, if a time portion is involved, it gets
more complicated.
SQL Date Data Types
MySQL comes with the following data types for storing a date or a date/time value in the database:
DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: YYYY-MM-DD HH:MI:SS
YEAR - format YYYY or YY
SQL Server comes with the following data types for storing a date or a date/time value in the database:
DATE - format YYYY-MM-DD
DATETIME - format: YYYY-MM-DD HH:MI:SS
SMALLDATETIME - format: YYYY-MM-DD HH:MI:SS
TIMESTAMP - format: a unique number
Note: The date types are chosen for a column when you create a new table in your database!
SQL Working with Dates
Look at the following table:
Orders Table
OrderId
ProductName
OrderDate
1
Geitost
2008-11-11
2
Camembert Pierrot
2008-11-09
3
Mozzarella di Giovanni
2008-11-11
4
Mascarpone Fabioli
2008-10-29
Now we want to select the records with an OrderDate of "2008-11-11" from the table above.
We use the following SELECT statement:
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
The result-set will look like this:
OrderId
ProductName
OrderDate
1
Geitost
2008-11-11
3
Mozzarella di Giovanni
2008-11-11
Note: Two dates can easily be compared if there is no time component involved!
Now, assume that the "Orders" table looks like this (notice the
added time-component in the "OrderDate" column):
OrderId
ProductName
OrderDate
1
Geitost
2008-11-11 13:23:44
2
Camembert Pierrot
2008-11-09 15:45:21
3
Mozzarella di Giovanni
2008-11-11 11:12:01
4
Mascarpone Fabioli
2008-10-29 14:56:59
If we use the same SELECT statement as above:
SELECT * FROM Orders WHERE OrderDate='2008-11-11'
we will get no result! This is because the query is looking only for dates with no time portion.
Tip: To keep your queries simple and easy to maintain, do not use time-components in your dates, unless you have to!
SQL Views
SQL CREATE VIEW Statement
In SQL, a view is a virtual table based on the result-set of an SQL statement.
A view contains rows and columns, just like a real table. The fields in a view are fields from one or more real tables in the database.
You can add SQL statements and functions to a view and present the data as if the data were coming from one single table.
A view is created with the CREATE VIEW
statement.Â
CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
Note: A view always shows up-to-date data! The database engine recreates the
view, every time a user queries it.
SQL CREATE VIEW Examples
The following SQL creates a view that shows all customers from Brazil:
Example
CREATE VIEW [Brazil
Customers] AS
SELECT
CustomerName, ContactName
FROM Customers
WHERE
Country = 'Brazil';
We can query the view above as follows:
Example
SELECT * FROM [Brazil
Customers];
The following SQL creates a view that selects every product in the "Products" table with a price higher than the average price:
Example
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, Price
FROM Products
WHERE Price > (SELECT AVG(Price) FROM Products);
We can query the view above as follows:
Example
SELECT * FROM [Products Above Average Price];
SQL Updating a View
A view can be updated with the CREATE OR REPLACE
VIEW statement.
SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, ...
FROM table_name
WHERE condition;
The following SQL adds the "City" column to the "Brazil Customers" view:
Example
CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = 'Brazil';
SQL Dropping a View
A view is deleted with the DROP VIEW
statement.
SQL DROP VIEW Syntax
DROP VIEW view_name;
The following SQL drops the "Brazil Customers" view:
Example
DROP VIEW [Brazil Customers];
SQL Injection
SQL Injection
SQL injection is a code injection technique that might destroy your database.
SQL injection is one of the most common web hacking techniques.
SQL injection is the placement of malicious code in SQL statements, via web page input.
SQL in Web Pages
SQL injection usually occurs when you ask a user for input, like their
username/userid, and instead of a name/id, the user gives you an SQL statement
that you will unknowingly run on your database.
Look at the following example which creates a
SELECT statement by adding a variable
(txtUserId) to a select string. The variable is fetched from user input
(getRequestString):
Example
txtUserId = getRequestString("UserId"); txtSQL = "SELECT *
FROM Users WHERE UserId = " + txtUserId;
The rest of this chapter describes the potential dangers of using user input in SQL statements.
SQL Injection Based on 1=1 is Always True
Look at the example above again. The original purpose of the code was to create an SQL statement to select a
user, with a given user id.
If there is nothing to prevent a user from entering "wrong" input, the user
can enter some "smart" input like this:
UserId:
Then, the SQL statement will look like this:
SELECT * FROM Users WHERE UserId = 105 OR 1=1;
The SQL above is valid and will return ALL rows from the "Users" table, since
OR 1=1 is always TRUE.
Does the example above look dangerous? What if the "Users" table contains names and passwords?
The SQL statement above is much the same as this:
SELECT UserId, Name, Password
FROM Users WHERE UserId = 105 or 1=1;
A hacker might get access to all the user names and passwords in a database, by
simply inserting
105 OR 1=1 into the input field.
If you want your web site to be able to store and retrieve data from a
database, your web server should have access to a database-system that uses the
SQL language.
If your web server is hosted by an Internet Service Provider (ISP), you will have to look for SQL hosting plans.
The most common SQL hosting databases are MS SQL Server, Oracle, MySQL, and MS Access.
MS SQL Server
Microsoft's SQL Server is a popular database software for database-driven web sites
with high traffic.
SQL Server is a very powerful, robust and full featured SQL database system.
Oracle
Oracle is also a popular database software for database-driven web sites
with high traffic.
Oracle is a very powerful, robust and full featured SQL database system.
MySQL
MySQL is also a popular database software for web sites.
MySQL is a very powerful, robust and full featured SQL database system.
MySQL is an inexpensive
alternative to the expensive Microsoft and Oracle solutions.
MS Access
When a web site requires only a simple database, Microsoft Access can be a
solution.
MS Access is not well suited for very high-traffic, and not as powerful as
MySQL, SQL Server, or Oracle.
SQL Data Types for MySQL, SQL Server, and MS Access
The data type of a column defines what value the column can hold:
integer, character, money, date and time, binary,
and so on.
SQL Data Types
Each column in a database table is required to have a name and a data type.
An SQL developer must decide what type of data that will be stored inside each
column when creating a table. The data type is a
guideline for SQL to understand what type of data is expected inside of each
column, and it also identifies how SQL will interact with the stored data.
Note: Data types might have different names in different database.
And even if the name is the same, the size and other details may be different!
Always check the
documentation!
MySQL Data Types (Version 8.0)
In MySQL there are three main data types: string, numeric, and date and time.
String Data Types
Data type
Description
CHAR(size)
A FIXED length string (can contain letters, numbers, and special characters). The
size parameter specifies the column length in characters - can be
from 0 to 255. Default is 1
VARCHAR(size)
A VARIABLE length string (can contain letters, numbers, and special
characters). The size parameter specifies the maximum string
length in characters - can be from 0 to 65535
BINARY(size)
Equal to CHAR(), but stores binary byte strings. The size
parameter specifies the column length in bytes. Default is 1
VARBINARY(size)
Equal to VARCHAR(), but stores binary byte strings. The size
parameter specifies the maximum column length in bytes.
TINYBLOB
For BLOBs (Binary Large Objects). Max length: 255 bytes
TINYTEXT
Holds a string with a maximum length of 255 characters
TEXT(size)
Holds a string with a maximum length of 65,535 bytes
BLOB(size)
For BLOBs (Binary Large Objects). Holds up to 65,535 bytes of data
MEDIUMTEXT
Holds a string with a maximum length of 16,777,215 characters
MEDIUMBLOB
For BLOBs (Binary Large Objects). Holds up to 16,777,215 bytes of data
LONGTEXT
Holds a string with a maximum length of 4,294,967,295 characters
LONGBLOB
For BLOBs (Binary Large Objects). Holds up to 4,294,967,295 bytes of data
ENUM(val1, val2, val3, ...)
A string object that can have only one value, chosen from a list of possible values. You can list up to 65535 values in an ENUM list. If a value is inserted that is not in the list, a blank value will be inserted.
The values are sorted in the order you enter them
SET(val1, val2, val3, ...)
A string object that can have 0 or more values, chosen from a list of
possible values. You can list up to 64 values in a SET list
Numeric Data Types
Data type
Description
BIT(size)
A bit-value type. The number of bits per value is specified in size.
The size parameter can hold a value from 1 to 64. The default
value for size is 1.
TINYINT(size)
A very small integer. Signed range is from -128 to 127. Unsigned range
is from 0 to 255. The size parameter specifies the maximum
display width (which is 255)
BOOL
Zero is considered as false, nonzero values are considered as true.
BOOLEAN
Equal to BOOL
SMALLINT(size)
A small integer. Signed range is from -32768 to 32767. Unsigned range
is from 0 to 65535. The size parameter specifies the maximum
display width (which is 255)
MEDIUMINT(size)
A medium integer. Signed range is from -8388608 to 8388607. Unsigned
range is from 0 to 16777215. The size parameter specifies the
maximum display width (which is 255)
INT(size)
A medium integer. Signed range is from -2147483648 to 2147483647.
Unsigned range is from 0 to 4294967295. The size parameter
specifies the maximum display width (which is 255)
INTEGER(size)
Equal to INT(size)
BIGINT(size)
A large integer. Signed range is from -9223372036854775808 to
9223372036854775807. Unsigned range is from 0 to 18446744073709551615. The
size parameter specifies the maximum display width (which is 255)
FLOAT(size, d)
A floating point number. The total number of digits is specified in
size. The number of digits after the decimal point is specified
in the d parameter. This syntax is deprecated in MySQL 8.0.17,
and it will be removed in future MySQL versions
FLOAT(p)
A floating point number. MySQL uses the p value to determine
whether to use FLOAT or DOUBLE for the resulting data type. If p
is from 0 to 24, the data type becomes FLOAT(). If p is from 25 to
53, the data type becomes DOUBLE()
DOUBLE(size, d)
A normal-size floating point number. The total number of digits is specified in
size. The number of digits after the decimal point is specified
in the d parameter
DOUBLE PRECISION(size, d)
Â
DECIMAL(size, d)
An exact fixed-point number. The total number of digits is specified in
size. The number of digits after the decimal point is specified
in the d parameter. The maximum number for size is 65.
The maximum number for d is 30. The default value for size
is 10. The default value for d is 0.
DEC(size, d)
Equal to DECIMAL(size,d)
Note: All the numeric data types may have an extra option: UNSIGNED
or ZEROFILL. If you add the UNSIGNED option, MySQL disallows negative values for
the column. If you add the ZEROFILL option, MySQL automatically also adds the
UNSIGNED attribute to the column.
Date and Time Data Types
Data type
Description
DATE
A date. Format: YYYY-MM-DD. The supported range is from '1000-01-01' to '9999-12-31'
DATETIME(fsp)
A date and time combination. Format: YYYY-MM-DD hh:mm:ss. The supported range is from '1000-01-01 00:00:00' to '9999-12-31 23:59:59'.
Adding DEFAULT and ON UPDATE in the column definition to get automatic
initialization and updating to the current date and time
TIMESTAMP(fsp)
A timestamp. TIMESTAMP values are stored as the number of seconds since the Unix epoch ('1970-01-01 00:00:00' UTC). Format: YYYY-MM-DD
hh:mm:ss. The supported range is from '1970-01-01 00:00:01' UTC to '2038-01-09 03:14:07' UTC.
Automatic initialization and updating to the current date and time can be
specified using DEFAULT CURRENT_TIMESTAMP and ON UPDATE CURRENT_TIMESTAMP
in the column definition
TIME(fsp)
A time. Format: hh:mm:ss. The supported range is from '-838:59:59' to '838:59:59'
YEAR
A year in four-digit format. Values allowed in four-digit format: 1901 to 2155, and 0000.
MySQL 8.0 does not support year in two-digit format.
SQL Server Data Types
String Data Types
Data type
Description
Max size
Storage
char(n)
Fixed width character string
8,000 characters
Defined width
varchar(n)
Variable width character string
8,000 characters
2 bytes + number of chars
varchar(max)
Variable width character string
1,073,741,824 characters
2 bytes + number of chars
text
Variable width character string
2GB of text data
4 bytes + number of chars
nchar
Fixed width Unicode string
4,000 characters
Defined width x 2
nvarchar
Variable width Unicode string
4,000 characters
Â
nvarchar(max)
Variable width Unicode string
536,870,912 characters
Â
ntext
Variable width Unicode string
2GB of text data
Â
binary(n)
Fixed width binary string
8,000 bytes
Â
varbinary
Variable width binary string
8,000 bytes
Â
varbinary(max)
Variable width binary string
2GB
Â
image
Variable width binary string
2GB
Â
Numeric Data Types
Data type
Description
Storage
bit
Integer that can be 0, 1, or NULL
Â
tinyint
Allows whole numbers from 0 to 255
1 byte
smallint
Allows whole numbers between -32,768 and 32,767
2 bytes
int
Allows whole numbers between -2,147,483,648 and 2,147,483,647
4 bytes
bigint
Allows whole numbers between -9,223,372,036,854,775,808 and 9,223,372,036,854,775,807
8 bytes
decimal(p,s)
Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1.
The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.
The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
5-17 bytes
numeric(p,s)
Fixed precision and scale numbers.
Allows numbers from -10^38 +1 to 10^38 –1.
The p parameter indicates the maximum total number of digits that can be stored (both to the left and to the right of the decimal point). p must be a value from 1 to 38. Default is 18.
The s parameter indicates the maximum number of digits stored to the right of the decimal point. s must be a value from 0 to p. Default value is 0
5-17 bytes
smallmoney
Monetary data from -214,748.3648 to 214,748.3647
4 bytes
money
Monetary data from -922,337,203,685,477.5808 to 922,337,203,685,477.5807
8 bytes
float(n)
Floating precision number data from -1.79E + 308 to 1.79E + 308.
The n parameter indicates whether the field should hold 4 or 8 bytes. float(24) holds a 4-byte field and float(53) holds an 8-byte field. Default value of n is 53.
4 or 8 bytes
real
Floating precision number data from -3.40E + 38 to 3.40E + 38
4 bytes
Date and Time Data Types
Data type
Description
Storage
datetime
From January 1, 1753 to December 31, 9999 with an accuracy of 3.33 milliseconds
8 bytes
datetime2
From January 1, 0001 to December 31, 9999 with an accuracy of 100 nanoseconds
6-8 bytes
smalldatetime
From January 1, 1900 to June 6, 2079 with an accuracy of 1 minute
4 bytes
date
Store a date only. From January 1, 0001 to December 31, 9999
3 bytes
time
Store a time only to an accuracy of 100 nanoseconds
3-5 bytes
datetimeoffset
The same as datetime2 with the addition of a time zone offset
8-10 bytes
timestamp
Stores a unique number that gets updated every time a row gets created or modified. The timestamp value is based upon an internal clock and does not correspond to real time. Each table may have only one timestamp variable
Â
Other Data Types
Data type
Description
sql_variant
Stores up to 8,000 bytes of data of various data types, except text, ntext, and timestamp
uniqueidentifier
Stores a globally unique identifier (GUID)
xml
Stores XML formatted data. Maximum 2GB
cursor
Stores a reference to a cursor used for database operations
table
Stores a result-set for later processing
MS Access Data Types
Data type
Description
Storage
Text
Use for text or combinations of text and numbers. 255 characters maximum
Â
Memo
Memo is used for larger amounts of text. Stores up to 65,536 characters. Note: You cannot sort a memo field. However, they are searchable
Â
Byte
Allows whole numbers from 0 to 255
1 byte
Integer
Allows whole numbers between -32,768 and 32,767
2 bytes
Long
Allows whole numbers between -2,147,483,648 and 2,147,483,647
4 bytes
Single
Single precision floating-point. Will handle most decimals
4 bytes
Double
Double precision floating-point. Will handle most decimals
8 bytes
Currency
Use for currency. Holds up to 15 digits of whole dollars, plus 4 decimal places. Tip: You can choose which country's currency to use
8 bytes
AutoNumber
AutoNumber fields automatically give each record its own number, usually starting at 1
4 bytes
Date/Time
Use for dates and times
8 bytes
Yes/No
A logical field can be displayed as Yes/No, True/False, or On/Off. In code, use the constants True and False (equivalent to -1 and 0). Note: Null values are not allowed in Yes/No fields
1 bit
Ole Object
Can store pictures, audio, video, or other BLOBs (Binary Large Objects)
up to 1GB
Hyperlink
Contain links to other files, including web pages
Â
Lookup Wizard
Let you type a list of options, which can then be chosen from a drop-down list
4 bytes
This SQL keywords reference contains the reserved words in SQL.
SQL Keywords
Keyword
Description
Adds a column in an existing table
Adds a constraint after a table is already created
Returns true if all of the subquery values meet the
condition
Adds, deletes, or modifies columns in a table, or changes the data
type of a column in a table
Changes the data type of a column in a table
Adds, deletes, or modifies columns in a table
Only includes rows where both conditions is true
Returns true if any of the subquery values meet the
condition
Renames a column or table with an alias
Sorts the result set in ascending order
Creates a back up of an existing database
Selects values within a given range
Creates different outputs based on conditions
A constraint that limits the value that can be placed in a
column
Changes the data type of a column or deletes a column in a table
Adds or deletes a constraint
Creates a database, index, view, table, or procedure
Creates a new SQL database
Creates an index on a table (allows duplicate values)
Updates a view
Creates a new table in the database
Creates a stored procedure
Creates a unique index on a table (no duplicate values)
Creates a view based on the result set of a SELECT statement
Creates or deletes an SQL database
A constraint that provides a default value for a column
Deletes rows from a table
Sorts the result set in descending order
Selects only distinct (different) values
Deletes a column, constraint, database, index, table, or view
Deletes a column in a table
Deletes a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint
Deletes an existing SQL database
Deletes a DEFAULT constraint
Deletes an index in a table
Deletes an existing table in the database
Deletes a view
Executes a stored procedure
Tests for the existence of any record in a subquery
A constraint that is a key used to link two tables together
Specifies which table to select or delete data from
Returns all rows when there is a match in either left table or right table
Groups the result set (used with aggregate functions: COUNT, MAX, MIN, SUM,
AVG)
Used instead of WHERE with aggregate functions
Allows you to specify multiple values in a WHERE clause
Creates or deletes an index in a table
Returns rows that have matching values in both tables
Inserts new rows in a table
Copies data from one table into another table
Tests for empty values
Tests for non-empty values
Joins tables
Returns all rows from the left table, and the matching rows from the right
table
Searches for a specified pattern in a column
Specifies the number of records to return in the result set
Only includes rows where a condition is not true
A constraint that enforces a column to not accept NULL values
Includes rows where either condition is true
Sorts the result set in ascending or descending order
Returns all rows when there is a match in either left table or right table
A constraint that uniquely identifies each record in a database table
A stored procedure
Returns all rows from the right table, and the matching rows from the
left table
Specifies the number of records to return in the result set
Selects data from a database
Selects only distinct (different) values
Copies data from one table into a new table
Specifies the number of records to return in the result set
Specifies which columns and values that should be updated in a table
Creates a table, or adds, deletes, or modifies columns in a table, or
deletes a table or data inside a table
Specifies the number of records to return in the result set
Deletes the data inside a table, but not the table itself
Combines the result set of two or more SELECT statements (only
distinct values)
Combines the result set of two or more SELECT statements (allows
duplicate values)
A constraint that ensures that all values in a column are unique
Updates existing rows in a table
Specifies the values of an INSERT INTO statement
Creates, updates, or deletes a view
Filters a result set to include only records that fulfill a specified
condition
SQL ADD Keyword
ADD
The ADD command is used to add a column in an
existing table.
The following SQL adds an "Email" column to the "Customers" table:
Example
ALTER TABLE Customers ADD Email varchar(255);
SQL ADD CONSTRAINT Keyword
ADD CONSTRAINT
The ADD CONSTRAINT command is used to create
a constraint after a table is already created.
The following SQL adds a constraint named "PK_Person" that is a
PRIMARY KEY constraint on multiple columns (ID and LastName):
Example
ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
SQL ALL Keyword
ALL
The ALL command returns true if all of the subquery values meet
the
condition.
The following SQL statement returns TRUE and lists the productnames if ALL
the records in the OrderDetails table has quantity = 10:
Example
SELECT ProductName FROM Products WHERE ProductID = ALL (SELECT ProductID FROM OrderDetails WHERE Quantity = 10);
SQL ALTER Keyword
ALTER TABLE
The ALTER TABLE command adds, deletes, or
modifies columns in a table.
The ALTER TABLE command also adds and
deletes various constraints in a table.
The following SQL adds an "Email" column to the "Customers" table:
Example
ALTER TABLE Customers ADD Email varchar(255);
The following SQL deletes the "Email" column from the "Customers" table:
Example
ALTER TABLE Customers DROP COLUMN Email;
ALTER COLUMN
The ALTER COLUMN command is used to change the data type of a column in a table.
The following SQL changes the data type of the column named "BirthDate" in
the "Employees" table to type year:
Example
ALTER TABLE Employees ALTER COLUMN BirthDate
year;
SQL ALTER COLUMN Keyword
ALTER COLUMN
The ALTER COLUMN command is used to change the data type of a column in a table.
The following SQL changes the data type of the column named "BirthDate" in
the "Employees" table to type year:
Example
ALTER TABLE Employees ALTER COLUMN BirthDate
year;
SQL ALTER TABLE Keyword
ALTER TABLE
The ALTER TABLE command adds, deletes, or
modifies columns in a table.
The ALTER TABLE command also adds and
deletes various constraints in a table.
The following SQL adds an "Email" column to the "Customers" table:
Example
ALTER TABLE Customers ADD Email varchar(255);
The following SQL deletes the "Email" column from the "Customers" table:
Example
ALTER TABLE Customers DROP COLUMN Email;
SQL AND Keyword
AND
The AND command is used with WHERE to only include rows where both conditions is true.
The following SQL statement selects all fields from "Customers" where country is "Germany" AND city is "Berlin":
Example
SELECT * FROM Customers
WHERE Country='Germany'
AND City='Berlin';
SQL ANY Keyword
ANY
The ANY command returns true if any of the subquery values meet
the
condition.
The following SQL statement returns TRUE and lists the productnames if it
finds ANY records in the OrderDetails table where quantity = 10:
Example
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT
ProductID FROM OrderDetails WHERE Quantity = 10);
The following SQL statement returns TRUE and lists the productnames if it
finds ANY records in the OrderDetails table where quantity > 99:
Example
SELECT ProductName FROM Products WHERE ProductID = ANY (SELECT ProductID
FROM OrderDetails WHERE Quantity > 99);
SQL AS Keyword
AS
The AS command is used to rename a column or table with an alias.
An alias only exists for the duration of the query.
Alias for Columns
The following SQL statement creates two aliases, one for the CustomerID
column and one for the CustomerName column:
Example
SELECT CustomerID AS ID, CustomerName AS Customer
FROM Customers;
The following SQL statement creates two aliases. Notice that it requires
double quotation marks or square brackets if the alias name contains spaces:
Example
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
The following SQL statement creates an alias named "Address" that combine four columns (Address, PostalCode,
City and Country):
Example
SELECT CustomerName, Address + ', ' + PostalCode + ' ' + City + ', ' + Country
AS Address
FROM Customers;
Note: To get the SQL statement above to work in MySQL use the following:
SELECT CustomerName, CONCAT(Address,', ',PostalCode,', ',City,', ',Country) AS Address
FROM Customers;
Alias for Tables
The following SQL statement selects all the orders from the customer with
CustomerID=4 (Around the Horn). We use the "Customers" and "Orders" tables, and
give them the table aliases of "c" and "o" respectively (Here we use
aliases to make the SQL shorter):
Example
SELECT o.OrderID, o.OrderDate, c.CustomerName FROM Customers AS c, Orders AS o WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;
SQL ASC Keyword
ASC
The ASC command is used to sort the data
returned in ascending order.
The following SQL statement selects all the columns from the "Customers"
table, sorted by the "CustomerName" column:
Example
SELECT * FROM Customers ORDER BY CustomerName ASC;
SQL BACKUP DATABASE Keyword
BACKUP DATABASE
The BACKUP DATABASE command is used in SQL
Server to create a full back up of an existing SQL database.
The following SQL statement creates a full back up of the existing database "testDB"
to the D disk:
Example
BACKUP DATABASE testDB TO DISK = 'D:backupstestDB.bak';
Tip: Always back up the database to a different drive than the
actual database. If you get a disk crash, you will not lose your backup
file along with the database.
A differential back up only backs up the parts of the database that have
changed since the last full database backup.
The following SQL statement creates a differential back up of the database "testDB":
Example
BACKUP DATABASE testDB TO DISK = 'D:backupstestDB.bak' WITH
DIFFERENTIAL;
Tip: A differential back up reduces the back up time (since only the changes are backed up).
SQL BETWEEN Keyword
BETWEEN
The BETWEEN command is used to select values
within a given range. The values can be numbers, text, or dates.
The BETWEEN command is inclusive: begin and end values are included.Â
The following SQL statement selects all products with a price BETWEEN 10 and 20:
Example
SELECT * FROM Products WHERE Price BETWEEN 10 AND 20;
To display the products outside the range of the previous example, use NOT BETWEEN:
Example
SELECT * FROM Products WHERE Price NOT BETWEEN 10 AND 20;
The following SQL statement selects all products with a ProductName BETWEEN 'Carnarvon
Tigers' and 'Mozzarella di Giovanni':
Example
SELECT * FROM Products WHERE ProductName BETWEEN 'Carnarvon Tigers' AND 'Mozzarella
di Giovanni' ORDER BY ProductName;
SQL CASE Keyword
CASE
The CASE command is used is to
create different output based on conditions.
The following SQL goes through several conditions and returns a value when the
specified condition is met:
Example
SELECT OrderID, Quantity, CASE Â Â Â WHEN Quantity > 30
THEN 'The quantity is greater than 30' Â Â Â WHEN Quantity =
30 THEN 'The quantity is 30' Â Â Â ELSE 'The quantity is under 30' END FROM OrderDetails;
The following SQL will order the customers by City. However, if City is NULL,
then order by Country:
Example
SELECT CustomerName, City, Country FROM Customers ORDER BY (CASE Â Â Â
WHEN City IS NULL THEN Country    ELSE City END);
SQL CHECK Keyword
CHECK
The CHECK constraint limits the value that can be placed in a
column.
SQL CHECK on CREATE TABLE
The following SQL creates a CHECK constraint on the "Age" column when the "Persons" table is created.
The CHECK constraint ensures that you can not have any person below 18 years:
MySQL:
CREATE TABLE Persons
(
  Â
Age int,
  Â
CHECK (Age>=18)
);
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
  Â
Age int CHECK (Age>=18)
);
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
  Â
Age int,
  Â
City varchar(255),
  Â
CONSTRAINT CHK_Person CHECK (Age>=18 AND City='Sandnes')
);
SQL CHECK on ALTER TABLE
To create a CHECK constraint on the "Age" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CHECK (Age>=18);
To allow naming of a CHECK constraint, and for defining a CHECK constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT CHK_PersonAge CHECK (Age>=18 AND City='Sandnes');
DROP a CHECK Constraint
To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
MySQL:
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
SQL COLUMN Keyword
ALTER COLUMN
The ALTER COLUMN command is used to change the data type of a column in a table.
The following SQL changes the data type of the column named "BirthDate" in
the "Employees" table to type year:
Example
ALTER TABLE Employees ALTER COLUMN BirthDate
year;
DROP COLUMN
The DROP COLUMN command is used to delete a column in an
existing table.
The following SQL deletes the "ContactName" column from the "Customers" table:
Example
ALTER TABLE Customers DROP COLUMN ContactName;
SQL CONSTRAINT Keyword
ADD CONSTRAINT
The ADD CONSTRAINT command is used to create
a constraint after a table is already created.
The following SQL adds a constraint named "PK_Person" that is a
PRIMARY KEY constraint on multiple columns (ID and LastName):
Example
ALTER TABLE Persons ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
DROP CONSTRAINT
The DROP CONSTRAINT command is used to delete
a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint.
DROP a UNIQUE Constraint
To drop a UNIQUE constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT UC_Person;
MySQL:
ALTER TABLE Persons
DROP INDEX UC_Person;
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;
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_PersonOrder;
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
DROP a CHECK Constraint
To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
MySQL:
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
SQL CREATE Keyword
CREATE DATABASE
The CREATE DATABASE command is used is to
create a new SQL database.
The following SQL creates a database called "testDB":
Example
CREATE DATABASE testDB;
Tip: Make sure you have admin privilege before creating any database. Once a
database is created, you can check it in the list of databases with the
following SQL command: SHOW DATABASES;
CREATE TABLE
The CREATE TABLE command creates a new table
in the database.
The following SQL creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName,
Address, and City:
Example
CREATE TABLE Persons
(
  Â
PersonID int,
  Â
LastName varchar(255),
  Â
FirstName varchar(255),
  Â
Address varchar(255),
  Â
City varchar(255)
);
CREATE TABLE Using Another Table
The following SQL creates a new table called "TestTables" (which is
a copy of two columns of the "Customers" table):Â
Example
CREATE TABLE TestTable AS SELECT customername, contactname FROM
customers;
CREATE INDEX
The CREATE INDEX command is used to
create indexes in tables (allows duplicate values).
Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries.
The following SQL creates an 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_pname
ON Persons (LastName, FirstName);
Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update).
So, only create indexes on columns that will be frequently searched against.
CREATE UNIQUE INDEX
The CREATE UNIQUE INDEX command creates a unique index on a table (no duplicate values
allowed)
The following SQL creates an index named "uidx_pid" on the "PersonID" column in the "Persons" table:
CREATE UNIQUE INDEX uidx_pid
ON Persons (PersonID);
CREATE VIEW
The CREATE VIEW command creates a view.
A view is a virtual table based on the result set of an SQL statement.
The following SQL creates a view that selects all customers from Brazil:
Example
CREATE VIEW [Brazil
Customers] AS
SELECT
CustomerName, ContactName
FROM Customers
WHERE
Country = "Brazil";
CREATE OR REPLACE VIEW
The CREATE OR REPLACE VIEW command updates a view.
The following SQL adds the "City" column to the "Brazil Customers" view:
Example
CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = "Brazil";
Query The View
We can query the view above as follows:
Example
SELECT * FROM [Brazil
Customers];
CREATE PROCEDURE
The CREATE PROCEDURE command is used to
create a stored procedure.
A stored procedure is a prepared SQL code that you can save, so the
code can be reused over and over again.
The following SQL creates a stored procedure named "SelectAllCustomers"
that selects all records from the "Customers" table:
Example
CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO;
Execute the stored procedure above as follows:
Example
EXEC SelectAllCustomers;
SQL CREATE DATABASE Keyword
CREATE DATABASE
The CREATE DATABASE command is used is to
create a new SQL database.
The following SQL creates a database called "testDB":
Example
CREATE DATABASE testDB;
Tip: Make sure you have admin privilege before creating any database. Once a
database is created, you can check it in the list of databases with the
following SQL command: SHOW DATABASES;
SQL CREATE INDEX Keyword
CREATE INDEX
The CREATE INDEX command is used to
create indexes in tables (allows duplicate values).
Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries.
The following SQL creates an 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_pname
ON Persons (LastName, FirstName);
Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update).
So, only create indexes on columns that will be frequently searched against.
SQL CREATE OR REPLACE VIEW Keyword
CREATE OR REPLACE VIEW
The CREATE OR REPLACE VIEW command updates a view.
The following SQL adds the "City" column to the "Brazil Customers" view:
Example
CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = "Brazil";
Query The View
We can query the view above as follows:
Example
SELECT * FROM [Brazil
Customers];
SQL CREATE TABLE Keyword
CREATE TABLE
The CREATE TABLE command creates a new table
in the database.
The following SQL creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName,
Address, and City:
Example
CREATE TABLE Persons
(
  Â
PersonID int,
  Â
LastName varchar(255),
  Â
FirstName varchar(255),
  Â
Address varchar(255),
  Â
City varchar(255)
);
CREATE TABLE Using Another Table
The following SQL creates a new table called "TestTables" (which is
a copy of two columns of the "Customers" table):Â
Example
CREATE TABLE TestTable AS SELECT customername, contactname FROM
customers;
SQL CREATE PROCEDURE Keyword
CREATE PROCEDURE
The CREATE PROCEDURE command is used to
create a stored procedure.
A stored procedure is a prepared SQL code that you can save, so the
code can be reused over and over again.
The following SQL creates a stored procedure named "SelectAllCustomers"
that selects all records from the "Customers" table:
Example
CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO;
Execute the stored procedure above as follows:
Example
EXEC SelectAllCustomers;
SQL CREATE UNIQUE INDEX Keyword
CREATE UNIQUE INDEX
The CREATE UNIQUE INDEX command creates a unique index on a table (no duplicate values
allowed)
Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries.
The following SQL creates an index named "uidx_pid" on the "PersonID" column in the "Persons" table:
CREATE UNIQUE INDEX uidx_pid
ON Persons (PersonID);
Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update).
So, only create indexes on columns that will be frequently searched against.
SQL CREATE VIEW Keyword
CREATE VIEW
The CREATE VIEW command creates a view.
A view is a virtual table based on the result set of an SQL statement.
The following SQL creates a view that selects all customers from Brazil:
Example
CREATE VIEW [Brazil
Customers] AS
SELECT
CustomerName, ContactName
FROM Customers
WHERE
Country = "Brazil";
Query The View
We can query the view above as follows:
Example
SELECT * FROM [Brazil Customers];
SQL DATABASE Keyword
CREATE DATABASE
The CREATE DATABASE command is used is to
create a new SQL database.
The following SQL creates a database called "testDB":
Example
CREATE DATABASE testDB;
Tip: Make sure you have admin privilege before creating any database. Once a
database is created, you can check it in the list of databases with the
following SQL command: SHOW DATABASES;
DROP DATABASE
The DROP DATABASE command is used is to
delete an existing SQL database.
The following SQL drops a database named "testDB":
Example
DROP DATABASE testDB;
Note: Be careful before dropping a database. Deleting
a database will result in loss of complete information stored in the database!
SQL DEFAULT Keyword
DEFAULT
The DEFAULT constraint provides a default
value for a column.
The default value will be added to all new records if no other value is specified.
SQL DEFAULT on CREATE TABLE
The following SQL sets a DEFAULT value for the "City" column when the "Persons" table is created:
My SQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
  Â
City varchar(255) DEFAULT 'Sandnes'
);
The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():
CREATE TABLE Orders
(
  Â
OrderDate date DEFAULT GETDATE()
);
SQL DEFAULT on ALTER TABLE
To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:
MySQL:
ALTER TABLE Persons
ALTER City SET DEFAULT 'Sandnes';
SQL Server:
ALTER TABLE Persons ADD CONSTRAINT df_City DEFAULT 'Sandnes' FOR City;
MS Access:
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'Sandnes';
Oracle:
ALTER TABLE Persons
MODIFY City DEFAULT 'Sandnes';
DROP a DEFAULT Constraint
To drop a DEFAULT constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
ALTER City DROP DEFAULT;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
SQL DELETE Keyword
DELETE
The DELETE command is used to delete
existing records in a table.
The following SQL statement deletes the customer "Alfreds Futterkiste" from
the "Customers" table:
Example
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste';
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!
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:
The following SQL statement deletes all rows in the "Customers" table,
without deleting the table. This means that the table structure, attributes, and
indexes will be intact:
Example
DELETE FROM Customers;
SQL DESC Keyword
DESC
The DESC command is used to sort the data
returned in descending order.
The following SQL statement selects all the columns from the "Customers"
table, sorted descending by the "CustomerName" column:
Example
SELECT * FROM Customers ORDER BY CustomerName DESC;
SQL SELECT DISTINCT Keyword
SELECT DISTINCT
The SELECT DISTINCT command returns only
distinct (different) values in the result set.
The following SQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table:
Example
SELECT DISTINCT Country FROM Customers;
SQL DROP Keyword
DROP COLUMN
The DROP COLUMN command is used to delete a column in an
existing table.
The following SQL deletes the "ContactName" column from the "Customers" table:
Example
ALTER TABLE Customers DROP COLUMN ContactName;
DROP a UNIQUE Constraint
To drop a UNIQUE constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT UC_Person;
MySQL:
ALTER TABLE Persons
DROP INDEX UC_Person;
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;
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_PersonOrder;
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
DROP a CHECK Constraint
To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
MySQL:
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
DROP DEFAULT
The DROP DEFAULT command is used to delete
a DEFAULT constraint.
To drop a DEFAULT constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
MySQL:
ALTER TABLE Persons
ALTER City DROP DEFAULT;
DROP INDEX
The DROP INDEX command is used to delete an
index in a table.
MS Access:
DROP INDEX index_name ON table_name;
SQL Server:
DROP INDEX table_name.index_name;
DB2/Oracle:
DROP INDEX index_name;
MySQL:
ALTER TABLE table_name DROP INDEX index_name;
DROP DATABASE
The DROP DATABASE command is used is to
delete an existing SQL database.
The following SQL drops a database named "testDB":
Example
DROP DATABASE testDB;
Note: Be careful before dropping a database. Deleting
a database will result in loss of complete information stored in the database!
DROP TABLE
The DROP TABLE command deletes a table in
the database.
The following SQL deletes the table "Shippers":
Example
DROP TABLE Shippers;
Note: Be careful before deleting a table. Deleting a
table results in loss of all information stored in the table!
DROP VIEW
The DROP VIEW command deletes a view.
The following SQL drops the "Brazil Customers" view:
Example
DROP VIEW [Brazil Customers];
SQL DROP COLUMN Keyword
DROP COLUMN
The DROP COLUMN command is used to delete a column in an
existing table.
The following SQL deletes the "ContactName" column from the "Customers" table:
Example
ALTER TABLE Customers DROP COLUMN ContactName;
SQL DROP CONSTRAINT Keyword
DROP CONSTRAINT
The DROP CONSTRAINT command is used to delete
a UNIQUE, PRIMARY KEY, FOREIGN KEY, or CHECK constraint.
DROP a UNIQUE Constraint
To drop a UNIQUE constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT UC_Person;
MySQL:
ALTER TABLE Persons
DROP INDEX UC_Person;
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;
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_PersonOrder;
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
DROP a CHECK Constraint
To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT CHK_PersonAge;
MySQL:
ALTER TABLE Persons
DROP CHECK CHK_PersonAge;
SQL DROP DATABASE Keyword
DROP DATABASE
The DROP DATABASE command is used to
delete an existing SQL database.
The following SQL drops a database named "testDB":
Example
DROP DATABASE testDB;
Note: Be careful before dropping a database. Deleting
a database will result in loss of complete information stored in the database!
SQL DROP DEFAULT Keyword
DROP DEFAULT
The DROP DEFAULT command is used to delete
a DEFAULT constraint.
To drop a DEFAULT constraint, use the following SQL:
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
MySQL:
ALTER TABLE Persons
ALTER City DROP DEFAULT;
SQL DROP INDEX Keyword
DROP INDEX
The DROP INDEX command is used to delete an
index in a table.
MS Access:
DROP INDEX index_name ON table_name;
SQL Server:
DROP INDEX table_name.index_name;
DB2/Oracle:
DROP INDEX index_name;
MySQL:
ALTER TABLE table_name DROP INDEX index_name;
SQL DROP TABLE and TRUNCATE TABLE Keywords
DROP TABLE
The DROP TABLE command deletes a table in
the database.
The following SQL deletes the table "Shippers":
Example
DROP TABLE Shippers;
Note: Be careful before deleting a table. Deleting a
table results in loss of all information stored in the table!
TRUNCATE TABLE
The TRUNCATE TABLE command deletes the data inside a table, but
not the table itself.
The following SQL truncates the table "Categories":Â
Example
TRUNCATE TABLE Categories;
SQL DROP VIEW Keyword
DROP VIEW
The DROP VIEW command deletes a view.
The following SQL drops the "Brazil Customers" view:
Example
DROP VIEW [Brazil Customers];
SQL EXEC Keyword
EXEC
The EXEC command is used to execute a stored procedure.
The following SQL executes a stored procedure named "SelectAllCustomers":
Example
EXEC SelectAllCustomers;
SQL EXISTS Keyword
EXISTS
The EXISTS command tests for the existence of any record in a subquery,
and returns true if the subquery returns one or more records.
The following SQL lists the suppliers with a product price less than 20:
Example
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM
Products WHERE SupplierId = Suppliers.supplierId AND Price < 20);
The following SQL lists the suppliers with a product price
equal to 22:
Example
SELECT SupplierName FROM Suppliers WHERE EXISTS (SELECT ProductName FROM
Products WHERE SupplierId = Suppliers.supplierId AND Price = 22);
SQL FOREIGN KEY Keyword
FOREIGN KEY
The FOREIGN KEY constraint is a key used to link two tables together.
A FOREIGN KEY is a field (or collection of fields) in one table that refers to
the PRIMARY KEY in another table.
SQL FOREIGN KEY on CREATE TABLE
The following SQL creates a FOREIGN KEY on the "PersonID" column when the "Orders" table is created:
MySQL:
CREATE TABLE Orders
(
  Â
OrderID int NOT NULL,
  Â
OrderNumber int NOT NULL,
  Â
PersonID int,
  Â
PRIMARY KEY (OrderID),
  Â
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID)
);
SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
  Â
OrderID int NOT NULL PRIMARY KEY,
  Â
OrderNumber int NOT NULL,
  Â
PersonID int FOREIGN KEY REFERENCES Persons(PersonID)
);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Orders
(
  Â
OrderID int NOT NULL,
  Â
OrderNumber int NOT NULL,
  Â
PersonID int,
  Â
PRIMARY KEY (OrderID),
  Â
CONSTRAINT FK_PersonOrder FOREIGN KEY (PersonID)
  Â
REFERENCES Persons(PersonID)
);
SQL FOREIGN KEY on ALTER TABLE
To create a FOREIGN KEY constraint on the "PersonID" column when the "Orders" table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Orders
ADD FOREIGN KEY (PersonID)
REFERENCES Persons(PersonID);
To allow naming of a FOREIGN KEY constraint, and for defining a FOREIGN KEY constraint on multiple columns, use the following SQL syntax:
To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Orders
DROP FOREIGN KEY FK_PersonOrder;
SQL Server / Oracle / MS Access:
ALTER TABLE Orders
DROP CONSTRAINT FK_PersonOrder;
SQL FROM Keyword
FROM
The FROM command is used to specify which
table to select or delete data from.
The following SQL statement selects the "CustomerName" and "City" columns
from the "Customers" table:
Example
SELECT CustomerName, City FROM Customers;
The following SQL statement selects all the columns from the "Customers"
table:
Example
SELECT * FROM Customers;
The following SQL statement deletes the customer "Alfreds Futterkiste" from
the "Customers" table:
Example
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste';
SQL FULL OUTER JOIN Keyword
FULL OUTER JOIN
The FULL OUTER JOIN command returns all rows
when there is a match in either left table or right table.
The following SQL statement selects all customers, and all orders:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The FULL OUTER JOIN keyword returns all the rows from
the left table (Customers), and all the rows from the right table (Orders). If
there are rows in "Customers" that do not have matches in "Orders", or if there
are rows in "Orders" that do not have matches in "Customers", those rows will be
listed as well.
SQL GROUP BY Keyword
GROUP BY
The GROUP BY command is used to group the result set (used with aggregate functions: COUNT, MAX, MIN, SUM,
AVG).
The following SQL lists the number of customers in each country:
Example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country;
The following SQL lists the number of customers in each country,
sorted high to low:
Example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country
ORDER BY COUNT(CustomerID) DESC;
SQL HAVING Keyword
HAVING
The HAVING command is used instead of WHERE with aggregate functions.
The following SQL lists the number of customers in each country.
Only include countries with more than 5 customers:
Example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country
HAVING COUNT(CustomerID) > 5;
The following SQL lists the number of customers in each country,
sorted high to low (Only include countries with more than 5 customers):
Example
SELECT COUNT(CustomerID), Country FROM Customers GROUP BY Country HAVING COUNT(CustomerID)
> 5
ORDER BY COUNT(CustomerID) DESC;
SQL IN Keyword
IN
The IN command allows you to specify
multiple values in a WHERE clause.
The IN operator is a shorthand for multiple OR conditions.
The following SQL selects all customers that are located in "Germany", "France" and "UK":
Example
SELECT * FROM Customers
WHERE Country IN ('Germany', 'France', 'UK');
The following SQL selects all customers that are NOT located in "Germany", "France" or "UK":
Example
SELECT * FROM Customers
WHERE Country NOT IN ('Germany', 'France', 'UK');
The following SQL selects all customers that are from the same
countries as the suppliers:
Example
SELECT * FROM Customers
WHERE Country IN (SELECT Country FROM Suppliers);
SQL INDEX Keyword
CREATE INDEX
The CREATE INDEX command is used to
create indexes in tables (allows duplicate values).
Indexes are used to retrieve data from the database very fast. The users cannot see the indexes, they are just used to speed up searches/queries.
The following SQL creates an 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_pname
ON Persons (LastName, FirstName);
Note: The syntax for creating indexes varies among different databases. Therefore: Check the syntax for creating indexes in your database.
Note: Updating a table with indexes takes more time than updating a table without (because the indexes also need an update).
So, only create indexes on columns that will be frequently searched against.
DROP INDEX
The DROP INDEX command is used to delete an
index in a table.
MS Access:
DROP INDEX index_name ON table_name;
SQL Server:
DROP INDEX table_name.index_name;
DB2/Oracle:
DROP INDEX index_name;
MySQL:
ALTER TABLE table_name DROP INDEX index_name;
SQL INNER JOIN Keyword
INNER JOIN
The INNER JOIN command returns rows that have matching values in both tables.
The following SQL selects all orders with customer information:
Example
SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID;
Note: The INNER JOIN keyword selects all rows from both
tables as long as there is a match between the columns. If there are records in the
"Orders" table that do not have matches in "Customers", these orders will not
be shown!
The following SQL statement selects all orders with customer and shipper
information:
Example
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM
((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
SQL INSERT INTO Keyword
INSERT INTO
The INSERT INTO command is used to insert new rows in a table.
The following SQL inserts a new record in the "Customers" table:
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
The following SQL will insert a new record, but only insert data in the "CustomerName",
"City", and "Country" columns (CustomerID will
be updated automatically):
Example
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
SQL INSERT INTO SELECT Keyword
INSERT INTO SELECT
The INSERT INTO SELECT command copies data
from one table and inserts it into another table.
The following SQL copies "Suppliers" into "Customers" (the columns
that are not filled with data, will contain NULL):
Example
INSERT INTO Customers (CustomerName,
City, Country)
SELECT SupplierName, City, Country FROM Suppliers;
The following SQL copies "Suppliers" into "Customers" (fill all
columns):
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode,
Country) SELECT SupplierName, ContactName, Address, City, PostalCode,
Country FROM Suppliers;
The following SQL copies only the German suppliers into "Customers":
Example
INSERT INTO Customers (CustomerName,
City, Country)
SELECT SupplierName, City, Country FROM Suppliers WHERE Country='Germany';
SQL IS NULL Keyword
IS NULL
The IS NULL command is used to test for
empty values (NULL values).
The following SQL lists all customers with a NULL value in the "Address"
field:
Example
SELECT CustomerName, ContactName, Address FROM Customers WHERE Address
IS NULL;
Note: A NULL value is
different from a zero value or a field that contains spaces. A field with a NULL
value is one that has been left blank during record creation!
Tip: Always use IS NULL to look for NULL values.
SQL IS NOT NULL Keyword
IS NOT NULL
The IS NOT NULL command is used to test for
non-empty values (NOT NULL values).
The following SQL lists all customers with a value in the "Address" field:
Example
SELECT CustomerName, ContactName, Address FROM Customers WHERE Address
IS NOT NULL;
SQL JOIN Keyword
INNER JOIN
The INNER JOIN command returns rows that have matching values in both tables.
The following SQL selects all orders with customer information:
Example
SELECT Orders.OrderID, Customers.CustomerName FROM Orders INNER JOIN
Customers ON Orders.CustomerID = Customers.CustomerID;
Note: The INNER JOIN keyword selects all rows from both
tables as long as there is a match between the columns. If there are records in the
"Orders" table that do not have matches in "Customers", these orders will not
be shown!
The following SQL statement selects all orders with customer and shipper
information:
Example
SELECT Orders.OrderID, Customers.CustomerName, Shippers.ShipperName FROM
((Orders INNER JOIN Customers ON Orders.CustomerID = Customers.CustomerID)
INNER JOIN Shippers ON Orders.ShipperID = Shippers.ShipperID);
LEFT JOIN
The LEFT JOIN command returns all rows from
the left table, and the matching rows from the right table. The result is NULL
from the right side, if there is no match.
The following SQL will select all customers, and any orders they
might have:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The LEFT JOIN keyword returns all records from the
left table (Customers), even if there are no matches in the right table
(Orders).
RIGHT JOIN
The RIGHT JOIN command returns all rows from
the right table, and the matching records from the left table. The result is
NULL from the left side, when there is no match.
The following SQL will return all employees, and any orders they
might have placed:
Example
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
Note: The RIGHT JOIN keyword returns all records from the
right table (Employees), even if there are no matches in the left table
(Orders).
FULL OUTER JOIN
The FULL OUTER JOIN command returns all rows
when there is a match in either left table or right table.
The following SQL statement selects all customers, and all orders:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The FULL OUTER JOIN keyword returns all the rows from
the left table (Customers), and all the rows from the right table (Orders). If
there are rows in "Customers" that do not have matches in "Orders", or if there
are rows in "Orders" that do not have matches in "Customers", those rows will be
listed as well.
SQL LEFT JOIN Keyword
LEFT JOIN
The LEFT JOIN command returns all rows from
the left table, and the matching rows from the right table. The result is NULL
from the right side, if there is no match.
The following SQL will select all customers, and any orders they
might have:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The LEFT JOIN keyword returns all records from the
left table (Customers), even if there are no matches in the right table
(Orders).
SQL LIKE Keyword
LIKE
The LIKE command is used in a WHERE clause
to search for a specified pattern in a column.
You can use two wildcards with LIKE:
% - Represents zero, one, or multiple characters
_ - Represents a single character (MS Access uses a question mark (?) instead)
The following SQL selects all customers with a CustomerName starting with
"a":
Example
SELECT * FROM Customers
WHERE CustomerName LIKE 'a%';
The following SQL selects all customers with a CustomerName ending with "a":
Example
SELECT * FROM Customers
WHERE CustomerName LIKE '%a';
The following SQL selects all customers with a CustomerName that
have "or" in any position:
Example
SELECT * FROM Customers
WHERE CustomerName LIKE '%or%';
The following SQL statement selects all customers with a CustomerName that
starts with "a" and are at least 3 characters in length:
Example
SELECT * FROM Customers
WHERE CustomerName LIKE 'a__%';
SQL SELECT TOP, LIMIT and ROWNUM Keywords
SELECT TOP, LIMIT and ROWNUM
The LIMIT, SELECT TOP
or ROWNUM command is used to specify the number of records to return.
Note: SQL Server uses
SELECT TOP. MySQL
uses LIMIT, and Oracle uses
ROWNUM.
The following SQL statement selects the first three records from the "Customers" table
(SQL SERVER):
Example
SELECT TOP 3 * FROM Customers;
The following SQL statement shows the equivalent example using the LIMIT clause
(MySQL):
Example
SELECT * FROM Customers LIMIT 3;
The following SQL statement shows the equivalent example using ROWNUM
(Oracle):
Example
SELECT * FROM Customers WHERE ROWNUM <= 3;
SQL NOT Keyword
NOT
The NOT command is used with WHERE to only
include rows where a condition is not true.
The following SQL statement selects all fields from "Customers" where country is NOT "Germany":
Example
SELECT * FROM Customers
WHERE
NOT Country='Germany';
SQL NOT NULL Keyword
NOT NULL
The NOT NULL constraint enforces a column to
not accept NULL values, which means that you cannot insert or update a record without adding a value to this field.
The following SQL ensures that the "ID", "LastName", and
"FirstName" columns
will NOT accept NULL values:
Example
CREATE TABLE Persons ( Â Â Â ID int NOT NULL, Â Â Â
LastName varchar(255) NOT NULL, Â Â Â FirstName varchar(255)
NOT NULL, Â Â Â Age int );
The following SQL creates a NOT NULL constraint on the "Age" column when the "Persons" table is already created:
ALTER TABLE Persons
MODIFY Age int NOT NULL;
SQL OR Keyword
OR
The OR command is used with WHERE to include
rows where either condition is true.
The following SQL statement selects all fields from "Customers" where city is
"Berlin" OR city is "München":
Example
SELECT * FROM Customers
WHERE City='Berlin' OR City='München';
SQL ORDER BY Keyword
ORDER BY
The ORDER BY command is used to sort the result set in ascending or descending order.
The ORDER BY command sorts the result set in
ascending order by default. To sort the records in descending order, use the
DESC keyword.
The following SQL statement selects all the columns from the "Customers"
table, sorted by the "CustomerName" column:
Example
SELECT * FROM Customers ORDER BY CustomerName;
ASC
The ASC command is used to sort the data
returned in ascending order.
The following SQL statement selects all the columns from the "Customers"
table, sorted by the "CustomerName" column:
Example
SELECT * FROM Customers ORDER BY CustomerName ASC;
DESC
The DESC command is used to sort the data
returned in descending order.
The following SQL statement selects all the columns from the "Customers"
table, sorted descending by the "CustomerName" column:
Example
SELECT * FROM Customers ORDER BY CustomerName DESC;
SQL FULL OUTER JOIN Keyword
FULL OUTER JOIN
The FULL OUTER JOIN command returns all rows
when there is a match in either left table or right table.
The following SQL statement selects all customers, and all orders:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
Note: The FULL OUTER JOIN keyword returns all the rows from
the left table (Customers), and all the rows from the right table (Orders). If
there are rows in "Customers" that do not have matches in "Orders", or if there
are rows in "Orders" that do not have matches in "Customers", those rows will be
listed as well.
SQL PRIMARY KEY Keyword
PRIMARY KEY
The PRIMARY KEY constraint uniquely identifies each record in a table.
A table can have only one primary key, which may consist of one single or of multiple fields.
SQL PRIMARY KEY on CREATE TABLE
The following SQL creates a PRIMARY KEY on the "ID" column when the "Persons" table is created:
MySQL:
CREATE TABLE Persons
(
   ID int NOT NULL,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
  Â
Age int,
  Â
PRIMARY KEY (ID)
);
SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
   ID int NOT NULL PRIMARY KEY,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
  Â
Age int
);
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
   ID int NOT NULL,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
  Â
Age int,
  Â
CONSTRAINT PK_Person PRIMARY KEY (ID,LastName)
);
Note: In the example above there is only ONE PRIMARY KEY (PK_Person).
However, the VALUE of the primary key is made up of TWO COLUMNS (ID + LastName).
SQL PRIMARY KEY on ALTER TABLE
To create a PRIMARY KEY constraint on the "ID" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD PRIMARY KEY (ID);
To allow naming of a PRIMARY KEY constraint, and for defining a PRIMARY KEY constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD CONSTRAINT PK_Person PRIMARY KEY (ID,LastName);
Note: If you use the ALTER TABLE statement to add a primary key, the primary key column(s) must
already have been declared to not contain NULL values (when the table was first created).
DROP a PRIMARY KEY Constraint
To drop a PRIMARY KEY constraint, use the following SQL:
MySQL:
ALTER TABLE Persons
DROP PRIMARY KEY;
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT PK_Person;
SQL CREATE PROCEDURE Keyword
CREATE PROCEDURE
The CREATE PROCEDURE command is used to
create a stored procedure.
A stored procedure is a prepared SQL code that you can save, so the
code can be reused over and over again.
The following SQL creates a stored procedure named "SelectAllCustomers"
that selects all records from the "Customers" table:
Example
CREATE PROCEDURE SelectAllCustomers AS SELECT * FROM Customers GO;
Execute the stored procedure above as follows:
Example
EXEC SelectAllCustomers;
SQL RIGHT JOIN Keyword
RIGHT JOIN
The RIGHT JOIN command returns all rows from
the right table, and the matching records from the left table. The result is
NULL from the left side, when there is no match.
The following SQL will return all employees, and any orders they
might have placed:
Example
SELECT Orders.OrderID, Employees.LastName, Employees.FirstName FROM Orders RIGHT JOIN Employees ON Orders.EmployeeID = Employees.EmployeeID
ORDER BY Orders.OrderID;
Note: The RIGHT JOIN keyword returns all records from the
right table (Employees), even if there are no matches in the left table
(Orders).
SQL SELECT TOP, LIMIT and ROWNUM Keywords
SELECT TOP, LIMIT and ROWNUM
The LIMIT, SELECT TOP
or ROWNUM command is used to specify the number of records to return.
Note: SQL Server uses
SELECT TOP. MySQL
uses LIMIT, and Oracle uses
ROWNUM.
The following SQL statement selects the first three records from the "Customers" table
(SQL SERVER):
Example
SELECT TOP 3 * FROM Customers;
The following SQL statement shows the equivalent example using the LIMIT clause
(MySQL):
Example
SELECT * FROM Customers LIMIT 3;
The following SQL statement shows the equivalent example using ROWNUM
(Oracle):
Example
SELECT * FROM Customers WHERE ROWNUM <= 3;
SQL SELECT Keyword
SELECT
The SELECT command is used to select data
from a database. The data returned is stored in a result table, called the result set.
The following SQL statement selects the "CustomerName" and "City" columns
from the "Customers" table:
Example
SELECT CustomerName, City FROM Customers;
The following SQL statement selects all the columns from the "Customers"
table:
Example
SELECT * FROM Customers;
SQL SELECT DISTINCT Keyword
SELECT DISTINCT
The SELECT DISTINCT command returns only
distinct (different) values in the result set.
The following SQL statement selects only the DISTINCT values from the "Country" column in the "Customers" table:
Example
SELECT DISTINCT Country FROM Customers;
SQL SELECT INTO Keyword
SELECT INTO
The SELECT INTO command copies data
from one table and inserts it into a new table.
The following SQL statement creates a backup copy of Customers:
SELECT * INTO CustomersBackup2017
FROM Customers;
The following SQL statement uses the IN clause to copy the table into a new
table in another database:
SELECT *
INTO CustomersBackup2017 IN 'Backup.mdb'
FROM Customers;
The following SQL statement copies only a few columns into a new table:
SELECT CustomerName, ContactName INTO CustomersBackup2017
FROM Customers;
The following SQL statement copies only the German customers into a new table:
SELECT *
INTO CustomersGermany
FROM Customers
WHERE Country = 'Germany';
The following SQL statement copies data from more than one table into a new table:
SELECT Customers.CustomerName, Orders.OrderID INTO CustomersOrderBackup2017
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID = Orders.CustomerID;
SQL SELECT TOP, LIMIT and ROWNUM Keywords
SELECT TOP, LIMIT and ROWNUM
The LIMIT, SELECT TOP
or ROWNUM command is used to specify the number of records to return.
Note: SQL Server uses
SELECT TOP. MySQL
uses LIMIT, and Oracle uses
ROWNUM.
The following SQL statement selects the first three records from the "Customers" table
(SQL SERVER):
Example
SELECT TOP 3 * FROM Customers;
The following SQL statement shows the equivalent example using the LIMIT clause
(MySQL):
Example
SELECT * FROM Customers LIMIT 3;
The following SQL statement shows the equivalent example using ROWNUM
(Oracle):
Example
SELECT * FROM Customers WHERE ROWNUM <= 3;
SQL SET Keyword
SET
The SET command is used with UPDATE to specify which columns and values that should be updated
in a table.
The following SQL updates the first customer (CustomerID = 1) with a new ContactName and
a new City:
Example
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
The following SQL will
update the "ContactName" field to "Juan" for all records where Country is "Mexico":
Example
UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';
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!
SQL TABLE Keyword
CREATE TABLE
The CREATE TABLE command creates a new table
in the database.
The following SQL creates a table called "Persons" that contains five columns: PersonID, LastName, FirstName,
Address, and City:
Example
CREATE TABLE Persons
(
  Â
PersonID int,
  Â
LastName varchar(255),
  Â
FirstName varchar(255),
  Â
Address varchar(255),
  Â
City varchar(255)
);
CREATE TABLE Using Another Table
A copy of an existing table can also be created using
CREATE TABLE.
The following SQL creates a new table called "TestTables" (which is
a copy of the "Customers" table):Â
Example
CREATE TABLE TestTable AS SELECT customername, contactname FROM
customers;
ALTER TABLE
The ALTER TABLE command adds, deletes, or
modifies columns in a table.
The ALTER TABLE command also adds and
deletes various constraints in a table.
The following SQL adds an "Email" column to the "Customers" table:
Example
ALTER TABLE Customers ADD Email varchar(255);
The following SQL deletes the "Email" column from the "Customers" table:
Example
ALTER TABLE Customers DROP COLUMN Email;
DROP TABLE
The DROP TABLE command deletes a table in
the database.
The following SQL deletes the table "Shippers":
Example
DROP TABLE Shippers;
Note: Be careful before deleting a table. Deleting a
table results in loss of all information stored in the table!
TRUNCATE TABLE
The TRUNCATE TABLE command deletes the data inside a table, but
not the table itself.
The following SQL truncates the table "Categories":Â
Example
TRUNCATE TABLE Categories;
SQL SELECT TOP, LIMIT and ROWNUM Keywords
SELECT TOP, LIMIT and ROWNUM
The LIMIT, SELECT TOP
or ROWNUM command is used to specify the number of records to return.
Note: SQL Server uses
SELECT TOP. MySQL
uses LIMIT, and Oracle uses
ROWNUM.
The following SQL statement selects the first three records from the "Customers" table
(SQL SERVER):
Example
SELECT TOP 3 * FROM Customers;
The following SQL statement shows the equivalent example using the LIMIT clause
(MySQL):
Example
SELECT * FROM Customers LIMIT 3;
The following SQL statement shows the equivalent example using ROWNUM
(Oracle):
Example
SELECT * FROM Customers WHERE ROWNUM <= 3;
SQL DROP TABLE and TRUNCATE TABLE Keywords
DROP TABLE
The DROP TABLE command deletes a table in
the database.
The following SQL deletes the table "Shippers":
Example
DROP TABLE Shippers;
Note: Be careful before deleting a table. Deleting a
table results in loss of all information stored in the table!
TRUNCATE TABLE
The TRUNCATE TABLE command deletes the data inside a table, but
not the table itself.
The following SQL truncates the table "Categories":Â
Example
TRUNCATE TABLE Categories;
SQL UNION Keyword
UNION
The UNION command combines the result set of two or more SELECT statements (only
distinct values)
The following SQL statement returns the cities
(only distinct values) from both the "Customers" and the "Suppliers" table:
Example
SELECT City FROM Customers UNION SELECT City FROM Suppliers ORDER BY City;
SQL UNION ALL Keyword
UNION ALL
The UNION ALL command combines the result set of two or more SELECT statements (allows
duplicate values).
The following SQL statement returns the cities
(duplicate values also) from both the "Customers" and the "Suppliers" table:
Example
SELECT City FROM Customers UNION ALL SELECT City FROM Suppliers
ORDER BY City;
SQL UNIQUE Keyword
UNIQUE
The UNIQUE constraint ensures that all
values in a column are unique.
SQL UNIQUE Constraint on CREATE TABLE
The following SQL creates a UNIQUE constraint on the "ID" column when the "Persons" table is created:
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)
);
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
   ID int NOT NULL,
  Â
LastName varchar(255) NOT NULL,
  Â
FirstName varchar(255),
  Â
Age int,
  Â
CONSTRAINT UC_Person UNIQUE (ID,LastName)
);
SQL UNIQUE Constraint on ALTER TABLE
To create a UNIQUE constraint on the "ID" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:
ALTER TABLE Persons
ADD UNIQUE (ID);
To name a UNIQUE constraint, and to define a UNIQUE constraint on multiple columns, use the following SQL syntax:
MySQL / SQL Server / Oracle / MS Access:
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 UPDATE Keyword
UPDATE
The UPDATE command is used to update existing rows in a table.
The following SQL statement updates the first customer (CustomerID = 1) with a new contact person and
a new city.
Example
UPDATE Customers
SET ContactName = 'Alfred Schmidt', City= 'Frankfurt'
WHERE CustomerID = 1;
The following SQL statement will
update the contactname to "Juan" for all records where country is "Mexico":
Example
UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';
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!
SQL VALUES Keyword
VALUES
The VALUES command specifies the values of an INSERT INTO statement.
The following SQL inserts a new record in the "Customers" table:
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal', 'Tom B. Erichsen', 'Skagen 21', 'Stavanger', '4006', 'Norway');
The following SQL will insert a new record, but only insert data in the "CustomerName",
"City", and "Country" columns (CustomerID will
be updated automatically):
Example
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
SQL VIEW Keyword
CREATE VIEW
In SQL, a view is a virtual table based on the result set of an SQL statement.
The CREATE VIEW command creates a view.
The following SQL creates a view that selects all customers from Brazil:
Example
CREATE VIEW [Brazil
Customers] AS
SELECT
CustomerName, ContactName
FROM Customers
WHERE
Country = "Brazil";
Query The View
We can query the view above as follows:
Example
SELECT * FROM [Brazil
Customers];
CREATE OR REPLACE VIEW
The CREATE OR REPLACE VIEW command updates a view.
The following SQL adds the "City" column to the "Brazil Customers" view:
Example
CREATE OR REPLACE VIEW [Brazil Customers] AS
SELECT CustomerName, ContactName, City
FROM Customers
WHERE Country = "Brazil";
DROP VIEW
The DROP VIEW command deletes a view.
The following SQL drops the "Brazil Customers" view:
Example
DROP VIEW [Brazil Customers];
SQL WHERE Keyword
SELECT
The WHERE command filters a result set to include only records that fulfill a specified condition.
The following SQL statement selects all the customers from
"Mexico" in the "Customers" table:
Example
SELECT * FROM Customers
WHERE Country='Mexico';
SQL requires single quotes around text values (most database systems will
also allow double quotes).
However, numeric fields should not be enclosed in quotes:
Example
SELECT * FROM Customers
WHERE CustomerID=1;
Note: The WHERE clause is not only used in SELECT statement, it is also used in UPDATE,
DELETE statement, etc.!
The following operators can be used in the WHERE clause:
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 a certain range
LIKE
Search for a pattern
IN
To specify multiple possible values for a column
MySQL Functions
MySQL has many built-in functions.
This reference contains string, numeric, date, and some advanced functions
in MySQL.
MySQL String Functions
Function
Description
Returns the ASCII value for the specific character
Returns the length of a string (in characters)
Returns the length of a string (in characters)
Adds two or more expressions together
Adds two or more expressions together with a separator
Returns the index position of a value in a list of values
Returns the position of a string within a list of strings
Formats a number to a format like "#,###,###.##", rounded to a
specified number
of decimal places
Inserts a string within a string at the specified position and for a certain
number of characters
Returns the position of the first occurrence of a string in another string
Converts a string to lower-case
Extracts a number of characters from a string (starting from left)
Returns the length of a string (in bytes)
Returns the position of the first occurrence of a substring in a string
Converts a string to lower-case
Left-pads a string with another string, to a certain length
Removes leading spaces from a string
Extracts a substring from a string (starting at any position)
Returns the position of the first occurrence of a substring in a string
Repeats a string as many times as specified
Replaces all occurrences of a substring within a string, with a new
substring
Reverses a string and returns the result
Extracts a number of characters from a string (starting from right)
Right-pads a string with another string, to a certain length
Removes trailing spaces from a string
Returns a string of the specified number of space characters
Compares two strings
Extracts a substring from a string (starting at any position)
Extracts a substring from a string (starting at any position)
Returns a substring of a string before a specified number of
delimiter occurs
Removes leading and trailing spaces from a string
Converts a string to upper-case
Converts a string to upper-case
MySQL Numeric Functions
Function
Description
Returns the absolute value of a number
Returns the arc cosine of a number
Returns the arc sine of a number
Returns the arc tangent of one or two numbers
Returns the arc tangent of two numbers
Returns the average value of an expression
Returns the smallest integer value that is >= to a number
Returns the smallest integer value that is >= to a number
Returns the cosine of a number
Returns the cotangent of a number
Returns the number of records returned by a select query
Converts a value in radians to degrees
Used for integer division
Returns e raised to the power of a specified number
Returns the largest integer value that is <= to a number
Returns the greatest value of the list of arguments
Returns the smallest value of the list of arguments
Returns the natural logarithm of a number
Returns the natural logarithm of a number, or the logarithm of a number to a
specified base
Returns the natural logarithm of a number to base 10
Returns the natural logarithm of a number to base 2
Returns the maximum value in a set of values
Returns the minimum value in a set of values
Returns the remainder of a number divided by another number
Returns the value of PI
Returns the value of a number raised to the power of another number
Returns the value of a number raised to the power of another number
Converts a degree value into radians
Returns a random number
Rounds a number to a specified number of decimal places
Returns the sign of a number
Returns the sine of a number
Returns the square root of a number
Calculates the sum of a set of values
Returns the tangent of a number
Truncates a number to the specified number of decimal places
MySQL Date Functions
Function
Description
Adds a time/date interval to a date and then returns the date
Adds a time interval to a time/datetime and then returns the time/datetime
Returns the current date
Returns the current date
Returns the current time
Returns the current date and time
Returns the current time
Extracts the date part from a datetime expression
Returns the number of days between two date values
Adds a time/date interval to a date and then returns the date
Formats a date
Subtracts a time/date interval from a date and then returns the date
Returns the day of the month for a given date
Returns the weekday name for a given date
Returns the day of the month for a given date
Returns the weekday index for a given date
Returns the day of the year for a given date
Extracts a part from a given date
Returns a date from a numeric datevalue
Returns the hour part for a given date
Extracts the last day of the month for a given date
Returns the current date and time
Returns the current date and time
Creates and returns a date based on a year and a number of days value
Creates and returns a time based on an hour, minute, and second value
Returns the microsecond part of a time/datetime
Returns the minute part of a time/datetime
Returns the month part for a given date
Returns the name of the month for a given date
Returns the current date and time
Adds a specified number of months to a period
Returns the difference between two periods
Returns the quarter of the year for a given date value
Returns the seconds part of a time/datetime
Returns a time value based on the specified seconds
Returns a date based on a string and a format
Subtracts a time/date interval from a date and then returns the date
Subtracts a time interval from a datetime and then returns the time/datetime
Returns the current date and time
Extracts the time part from a given time/datetime
Formats a time by a specified format
Converts a time value into seconds
Returns the difference between two time/datetime expressions
Returns a datetime value based on a date or datetime value
Returns the number of days between a date and date "0000-00-00"
Returns the week number for a given date
Returns the weekday number for a given date
Returns the week number for a given date
Returns the year part for a given date
Returns the year and week number for a given date
MySQL Advanced Functions
Function
Description
Returns a binary representation of a number
Converts a value to a binary string
Goes through conditions and return a value when the first condition is
met
Converts a value (of any type) into a specified datatype
Returns the first non-null value in a list
Returns the unique connection ID for the current connection
Converts a number from one numeric base system to another
Converts a value into the specified datatype or character set
Returns the user name and host name for the MySQL account that the server
used to authenticate the current client
Returns the name of the current database
Returns a value if a condition is TRUE, or another value if a condition is FALSE
Return a specified value if the expression is NULL, otherwise return the
expression
Returns 1 or 0 depending on whether an expression is NULL
Returns the AUTO_INCREMENT id of the last row that has been inserted or
updated in a table
Compares two expressions and returns NULL if they are equal. Otherwise, the
first expression is returned
Returns the current MySQL user name and host name
Returns the current MySQL user name and host name
Returns the current MySQL user name and host name
Returns the current version of the MySQL database
Example
Return the ASCII value of the first character in "CustomerName":
SELECT ASCII(CustomerName) AS NumCodeOfFirstChar FROM Customers;
Definition and Usage
The ASCII() function returns the ASCII value for the specific character.
Syntax
ASCII(character)
Parameter Values
Parameter
Description
character
Required. The character to return the ASCII value for. If more than one
character is entered, it will only return the value for the first character
Technical Details
Works in:
From MySQL 4.0
Example
Return the length of the string:
SELECT CHAR_LENGTH("SQL Tutorial") AS LengthOfString;
Definition and Usage
The CHAR_LENGTH() function return the length of a string (in characters).
Note: This function is equal to the
function.
Syntax
CHAR_LENGTH(string)
Parameter Values
Parameter
Description
string
Required. The string to count the length for
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Return the length of the text in the "CustomerName" column:
SELECT CHAR_LENGTH(CustomerName) AS LengthOfName FROM Customers;
Example
Return the length of the string:
SELECT CHARACTER_LENGTH("SQL Tutorial") AS LengthOfString;
Definition and Usage
The CHARACTER_LENGTH() function return the length of a string (in characters).
Note: This function is equal to the
function.
Syntax
CHARACTER_LENGTH(string)
Parameter Values
Parameter
Description
string
Required. The string to count the length for
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Return the length of the text in the "CustomerName" column:
SELECT CHARACTER_LENGTH(CustomerName) AS LengthOfName FROM Customers;
Example
Add several strings together:
SELECT CONCAT("SQL ", "Tutorial ", "is ", "fun!") AS ConcatenatedString;
Definition and Usage
The CONCAT() function adds two or more expressions together.
Note: Also look at the function.
Syntax
CONCAT(expression1, expression2, expression3,...)
Parameter Values
Parameter
Description
expression1, expression2, expression3, etc.
Required. The expressions to add together.
Note:
If any of the expressions is a NULL value, it returns NULL
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Add three columns into one "Address" column:
SELECT CONCAT(Address, " ", PostalCode, " ", City) AS Address FROM
Customers;
Example
Add several expressions together, and add a "-" separator between them:
SELECT CONCAT_WS("-", "SQL", "Tutorial", "is", "fun!") AS ConcatenatedString;
Definition and Usage
The CONCAT_WS() function adds two or more expressions together with a separator.
Required. The separator to add between each of the
expressions. If separator is NULL, this function returns NULL
expression1, expression2, expression3, etc.
Required. The expressions to add together. An expression with a NULL value
will be skipped
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Add three columns (and add a space between them) into one "Address" column:
SELECT CONCAT_WS(" ", Address, PostalCode, City) AS Address FROM
Customers;
Example
Return the index position of "q" in the string list:
SELECT FIELD("q", "s", "q", "l");
Definition and Usage
The FIELD() function returns the index position of a value in a list of
values.
This function performs a case-insensitive search.
Note: If the specified value is not found in the list of values, this function
will return 0. If value is NULL, this function will return 0.
Syntax
FIELD(value, val1, val2, val3, ...)
Parameter Values
Parameter
Description
value
Required. The value to search for in the list
val1, val2, val3, ....
Required. The list of values to search
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Return the index position of "c" in the string list:
SELECT FIELD("c", "a", "b");
Example
Return the index position of "Q" in the string list:
SELECT FIELD("Q", "s", "q", "l");
Example
Return the index position of 5 in the numeric list:
SELECT FIELD(5, 0, 1, 2, 3, 4, 5);
Example
Search for "q" within the list of strings:
SELECT FIND_IN_SET("q", "s,q,l");
Definition and Usage
The FIND_IN_SET() function returns the position of a string within a list of strings.
Syntax
FIND_IN_SET(string, string_list)
Parameter Values
Parameter
Description
string
Required. The string to search for
string_list
Required. The list of string values to be searched (separated by commas)
Return Values
If string is not found in string_list, this function returns 0
If string or string_list is NULL, this function returns
NULL
If string_list is an empty string (""), this function returns 0
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Search for "a" within the list of strings:
SELECT FIND_IN_SET("a", "s,q,l");
Example
Search for "q" within the list of strings (string list is NULL):
SELECT FIND_IN_SET("q", null);
Example
Format the number as "#,###,###.##" (and round with two decimal places):
SELECT FORMAT(250500.5634, 2);
Definition and Usage
The FORMAT() function formats a number to a format like "#,###,###.##", rounded to a
specified number
of decimal places, then it returns the result as a string.
Syntax
FORMAT(number, decimal_places)
Parameter Values
Parameter
Description
number
Required. The number to be formatted
decimal_places
Required. The number of decimal places for number. If this
parameter is 0, this function returns a string
with no decimal places
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Format the number as a format of "#,###,###.##" (and round with 0 decimal places):
SELECT FORMAT(250500.5634, 0);
Example
Insert the string "Example" into the string "W3Schools.com". Replace the
first nine characters:
SELECT INSERT("W3Schools.com", 1, 9, "Example");
Definition and Usage
The INSERT() function inserts a string within a string at the specified position
and for a certain number of characters.
Syntax
INSERT(string, position, number, string2)
Parameter Values
Parameter
Description
string
Required. The string that will be modified
position
Required. The position where to insert string2
number
Required. The number of characters to replace
string2
Required. The string to insert into string
Return Values
If position is outside the length of string, this
function returns string
If number is higher than the length of the rest of the string,
this function replaces string from position until
the end of string
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Insert the string "no" into the string "W3Schools.com". Replace three
characters, starting from position 11:
SELECT INSERT("W3Schools.com", 11, 3, "no");
Example
Search for "3" in string "W3Schools.com", and return position:
SELECT INSTR("W3Schools.com", "3") AS MatchPosition;
Definition and Usage
The INSTR() function returns the position of the first occurrence of a string
in another string.
This function performs a case-insensitive search.
Syntax
INSTR(string1, string2)
Parameter Values
Parameter
Description
string1
Required. The string that will be searched
string2
Required. The string to search for in string1. If string2 is not found, this function returns 0
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Search for "COM" in string "W3Schools.com", and return position:
SELECT INSTR("W3Schools.com", "COM") AS MatchPosition;
Example
Search for "a" in CustomerName column, and return position:
SELECT INSTR(CustomerName, "a") FROM Customers;
Example
Convert the text to lower-case:
SELECT LCASE("SQL Tutorial is FUN!");
Definition and Usage
The LCASE() function converts a string to lower-case.
Note: The function is a synonym for the LCASE()
function.
Syntax
LCASE(text)
Parameter Values
Parameter
Description
text
Required. The string to convert
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Convert the text in "CustomerName" to lower-case:
SELECT LCASE(CustomerName) AS LowercaseCustomerName FROM Customers;
Example
Extract 3 characters from a string (starting from left):
SELECT LEFT("SQL Tutorial", 3) AS ExtractString;
Definition and Usage
The LEFT() function extracts a number of characters from a string (starting from left).
Tip: Also look at the
function.
Syntax
LEFT(string, number_of_chars)
Parameter Values
Parameter
Description
string
Required. The string to extract from
number_of_chars
Required. The number of characters to extract. If this parameter is
larger than the number of characters in string,
this function will return string
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Extract 5 characters from the text in the "CustomerName" column (starting from left):
SELECT LEFT(CustomerName, 5) AS ExtractString FROM Customers;
Example
Return the length of the string, in bytes:
SELECT LENGTH("SQL Tutorial") AS LengthOfString;
Definition and Usage
The LENGTH() function returns the length of a string (in bytes).
Syntax
LENGTH(string)
Parameter Values
Parameter
Description
string
Required. The string to count the length for
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Return the length of the text in the "CustomerName" column, in bytes:
SELECT LENGTH(CustomerName) AS LengthOfName FROM Customers;
Example
Search for "3" in string "W3Schools.com", and return position:
SELECT LOCATE("3", "W3Schools.com") AS MatchPosition;
Definition and Usage
The LOCATE() function returns the position of the first occurrence of a substring in a string.
If the substring is not found within the original string, this function returns 0.
This function performs a case-insensitive search.
Note: This function is equal to the
function.
Syntax
LOCATE(substring, string, start)
Parameter Values
Parameter
Description
substring
Required. The substring to search for in string
string
Required. The string that will be searched
start
Optional. The starting position for the search. Position 1 is default
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Search for "com" in string "W3Schools.com" (start at position 3), and return position:
SELECT LOCATE("com", "W3Schools.com", 3) AS MatchPosition;
Example
Search for "a" in CustomerName column, and return position:
SELECT LOCATE("a", CustomerName) FROM Customers;
Example
Convert the text to lower-case:
SELECT LOWER("SQL Tutorial is FUN!");
Definition and Usage
The LOWER() function converts a string to lower-case.
Note: The
function is equal to the LOWER() function.
Syntax
LOWER(text)
Parameter Values
Parameter
Description
text
Required. The string to convert
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Convert the text in "CustomerName" to lower-case:
SELECT LOWER(CustomerName) AS LowercaseCustomerName FROM Customers;
Example
Left-pad the string with "ABC", to a total length of 20:
SELECT LPAD("SQL Tutorial",
20, "ABC");
Definition and Usage
The LPAD() function left-pads a string with another string, to a certain
length.
Note: Also look at the
function.
Syntax
LPAD(string,
length, lpad_string)
Parameter Values
Parameter
Description
string
Required. The original string. If the length of the original string is
larger than the length parameter, this function removes the overfloating characters from
string
length
Required. The length of the string after it has been left-padded
lpad_string
Required. The string to left-pad to string
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Left-pad the text in "CustomerName" with "ABC", to a total length of 30:
SELECT LPAD(CustomerName, 30, "ABC") AS LeftPadCustomerName FROM Customers;
Example
Remove leading spaces from a string:
SELECT LTRIM("Â Â Â Â SQL Tutorial") AS LeftTrimmedString;
Definition and Usage
The LTRIM() function removes leading spaces from a string.
Syntax
LTRIM(string)
Parameter Values
Parameter
Description
string
Required. The string to remove leading spaces from
Technical Details
Works in:
From MySQL 4.0
Example
Extract a substring from a string (start at position 5, extract 3
characters):
SELECT MID("SQL Tutorial", 5, 3) AS ExtractString;
Definition and Usage
The MID() function extracts a substring from a string (starting at any
position).
Note: The MID() and functions
equals the function.
Syntax
MID(string, start, length)
Parameter Values
Parameter
Description
string
Required. The string to extract from
start
Required. The start position. Can be both a positive or negative number.
If it is a positive number, this function extracts from the beginning of the
string. If it is a negative number, this function extracts from the
end of the string
length
Required. The number of characters to extract
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Extract a substring from the text in a column (start at position 2, extract 5
characters):
SELECT MID(CustomerName,
2, 5) AS ExtractString FROM Customers;
Example
Extract a substring from a string (start from the end, at position -5, extract
5
characters):
SELECT MID("SQL Tutorial", -5, 5) AS ExtractString;
Example
Search for "3" in string "W3Schools.com", and return position:
SELECT POSITION("3" IN "W3Schools.com") AS MatchPosition;
Definition and Usage
The POSITION() function returns the position of the first occurrence of a substring
in a string.
If the substring is not found within the original string, this function returns 0.
This function performs a case-insensitive search.
Note: The function is
equal to the
POSITION() function.
Syntax
POSITION(substring IN string)
Parameter Values
Parameter
Description
substring
Required. The substring to search for in string
string
Required. The original string that will be searched
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Search for "COM" in string "W3Schools.com", and return position:
SELECT POSITION("COM" IN "W3Schools.com") AS MatchPosition;
Example
Search for "a" in CustomerName column, and return position:
SELECT POSITION("a" IN CustomerName) FROM Customers;
Example
Repeat a string 3 times:
SELECT REPEAT("SQL Tutorial", 3);
Definition and Usage
The REPEAT() function repeats a string as many times as specified.
Syntax
REPEAT(string, number)
Parameter Values
Parameter
Description
string
Required. The string to repeat
number
Required. The number of times to repeat the string
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Repeat the text in CustomerName 2 times:
SELECT REPEAT(CustomerName,
2) FROM Customers;
Example
Repeat the string 0 times:
SELECT REPEAT("SQL Tutorial", 0);
Example
Replace "SQL" with "HTML":
SELECT REPLACE("SQL Tutorial", "SQL", "HTML");
Definition and Usage
The REPLACE() function replaces all occurrences of a substring within a
string, with a new substring.
Note: This function performs a case-sensitive
replacement.
Syntax
REPLACE(string, from_string, new_string)
Parameter Values
Parameter
Description
string
Required. The original string
from_string
Required. The substring to be replaced
new_string
Required. The new replacement substring
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Replace "X" with "M":
SELECT REPLACE("XYZ FGH XYZ", "X", "M");
Example
Replace "X" with "m":
SELECT REPLACE("XYZ FGH XYZ", "X", "m");
Example
Replace "x" with "m":
SELECT REPLACE("XYZ FGH XYZ", "x", "m");
Example
Reverse a string:
SELECT REVERSE("SQL Tutorial");
Definition and Usage
The REVERSE() function reverses a string and returns the result.
Syntax
REVERSE(string)
Parameter Values
Parameter
Description
string
Required. The string to reverse
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Reverse the text in CustomerName:
SELECT REVERSE(CustomerName) FROM Customers;
Example
Extract 4 characters from a string (starting from right):
SELECT RIGHT("SQL Tutorial is cool", 4) AS ExtractString;
Definition and Usage
The RIGHT() function extracts a number of characters from a string (starting
from right).
Tip: Also look at the
function.
Syntax
RIGHT(string, number_of_chars)
Parameter Values
Parameter
Description
string
Required. The string to extract from
number_of_chars
Required. The number of characters to extract. If this parameter is
larger than the number of characters in string,
this function will return string
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Extract 5 characters from the text in the "CustomerName" column (starting from right):
SELECT RIGHT(CustomerName, 5) AS ExtractString FROM Customers;
Example
Right-pad the string with "ABC", to a total length of 20:
SELECT RPAD("SQL Tutorial",
20, "ABC");
Definition and Usage
The RPAD() function right-pads a string with another string, to a certain
length.
Note: Also look at the
function.
Syntax
RPAD(string,
length, rpad_string)
Parameter Values
Parameter
Description
string
Required. The original string. If the length of the original string is
larger than the length parameter, this function removes the overfloating characters from
string
length
Required. The length of the string after it has been right-padded
rpad_string
Required. The string to right-pad to string
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Right-pad the text in "CustomerName" with "ABC", to a total length of 30:
SELECT
RPAD(CustomerName, 30, "ABC") AS RightPadCustomerName FROM Customers;
Example
Remove trailing spaces from a string:
SELECT RTRIM("SQL Tutorial    ") AS RightTrimmedString;
Definition and Usage
The RTRIM() function removes trailing spaces from a string.
Syntax
RTRIM(string)
Parameter Values
Parameter
Description
string
Required. The string to remove trailing spaces from
Technical Details
Works in:
From MySQL 4.0
Example
Return a string with 10 space characters:
SELECT
SPACE(10);
Definition and Usage
The SPACE() function returns a string of the specified number of space
characters.
Syntax
SPACE(number)
Parameter Values
Parameter
Description
number
Required. The number of space characters to return
Technical Details
Works in:
From MySQL 4.0
Example
Compare two strings:
SELECT STRCMP("SQL Tutorial",
"SQL Tutorial");
Definition and Usage
The STRCMP() function compares two strings.
Syntax
STRCMP(string1, string2)
Parameter Values
Parameter
Description
string1, string2
Required. The two strings to be compared
Return Values
If string1 = string2, this function returns 0
If string1 < string2, this function returns -1
If string1 > string2, this function returns 1
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Compare two strings:
SELECT STRCMP("SQL Tutorial",
"HTML Tutorial");
Example
Extract a substring from a string (start at position 5, extract 3
characters):
SELECT SUBSTR("SQL Tutorial", 5, 3) AS ExtractString;
Definition and Usage
The SUBSTR() function extracts a substring from a string (starting at any
position).
Note: The SUBSTR() and functions
equals to the
function.
Syntax
SUBSTR(string, start, length)
OR:
SUBSTR(string FROM start FOR length)
Parameter Values
Parameter
Description
string
Required. The string to extract from
start
Required. The start position. Can be both a positive or negative number.
If it is a positive number, this function extracts from the beginning of the
string. If it is a negative number, this function extracts from the
end of the string
length
Optional. The number of characters to extract. If omitted, the whole
string will be returned (from the start position)
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Extract a substring from the text in a column (start at position 2, extract 5
characters):
SELECT SUBSTR(CustomerName,
2, 5) AS ExtractString FROM Customers;
Example
Extract a substring from a string (start from the end, at position -5, extract
5
characters):
SELECT SUBSTR("SQL Tutorial", -5, 5) AS ExtractString;
Example
Extract a substring from a string (start at position 5, extract 3
characters):
SELECT SUBSTRING("SQL Tutorial", 5, 3) AS ExtractString;
Definition and Usage
The SUBSTRING() function extracts a substring from a string (starting at any
position).
Note: The and functions
equals to the SUBSTRING()
function.
Syntax
SUBSTRING(string, start, length)
OR:
SUBSTRING(string FROM start FOR length)
Parameter Values
Parameter
Description
string
Required. The string to extract from
start
Required. The start position. Can be both a positive or negative number.
If it is a positive number, this function extracts from the beginning of the
string. If it is a negative number, this function extracts from the
end of the string
length
Optional. The number of characters to extract. If omitted, the whole
string will be returned (from the start position)
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Extract a substring from the text in a column (start at position 2, extract 5
characters):
SELECT SUBSTRING(CustomerName,
2, 5) AS ExtractString FROM Customers;
Example
Extract a substring from a string (start from the end, at position -5, extract
5
characters):
SELECT SUBSTRING("SQL Tutorial", -5, 5) AS ExtractString;
Example
Return a substring of a string before a specified number of
delimiter occurs:
The SUBSTRING_INDEX() function returns a substring of a string before a
specified number of
delimiter occurs.
Syntax
SUBSTRING_INDEX(string, delimiter, number)
Parameter Values
Parameter
Description
string
Required. The original string
delimiter
Required. The delimiter to search for
number
Required. The number of times to search for the delimiter. Can
be both a positive or negative number. If it is a positive number, this
function returns all to the left of the delimiter. If it is a
negative number, this function returns all to the right of the delimiter.
Technical Details
Works in:
From MySQL 4.0
More Examples
Example
Return a substring of a string before a specified number of
delimiter occurs:
Â
Â
Â
