Toggle navigation
TUTORIAL HOME
SQL Functions
❮ Previous Next ❯
SQL has many built-in functions for performing calculations on data.

SQL Aggregate Functions
SQL aggregate functions return a single value, calculated from values in a column.

Function Description
AVG() Returns the average value
COUNT() Returns the number of rows
FIRST() Returns the first value
LAST() Returns the last value
MAX() Returns the largest value
MIN() Returns the smallest value
ROUND() Rounds a numeric field to the number of decimals specified
SUM() Returns the sum
SQL String Functions
Function Description
CHARINDEX Searches an expression in a string expression and returns its starting position if found
CONCAT()
LEFT()
LEN() / LENGTH() Returns the length of the value in a text field
LOWER() / LCASE() Converts character data to lower case
LTRIM()
SUBSTRING() / MID() Extract characters from a text field
PATINDEX()
REPLACE()
RIGHT()
RTRIM()
UPPER() / UCASE() Converts character data to upper case

❮ Previous Next ❯

Toggle navigation
TUTORIAL HOME
SQL Views
❮ Previous Next ❯
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 functions, WHERE, and JOIN statements to a view and present the data as if the data were coming from one single table.

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 data, using the view’s SQL statement, every time a user queries a view.

SQL CREATE VIEW Examples
If you have the Northwind database you can see that it has several views installed by default.

The view “Current Product List” lists all active products (products that are not discontinued) from the “Products” table. The view is created with the following SQL:

CREATE VIEW [Current Product List] AS
SELECT ProductID, ProductName
FROM Products
WHERE Discontinued = No;
Then, we can query the view as follows:

SELECT * FROM [Current Product List];
Another view in the Northwind sample database selects every product in the “Products” table with a unit price higher than the average unit price:

CREATE VIEW [Products Above Average Price] AS
SELECT ProductName, UnitPrice
FROM Products
WHERE UnitPrice > (SELECT AVG(UnitPrice) FROM Products);
We can query the view above as follows:

SELECT * FROM [Products Above Average Price];
Another view in the Northwind database calculates the total sale for each category in 1997. Note that this view selects its data from another view called “Product Sales for 1997”:

CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName, Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName;
We can query the view above as follows:

SELECT * FROM [Category Sales For 1997];
We can also add a condition to the query. Let’s see the total sale only for the category “Beverages”:

SELECT * FROM [Category Sales For 1997]
WHERE CategoryName = ‘Beverages’;
SQL Updating a View
You can update a view by using the following syntax:

SQL CREATE OR REPLACE VIEW Syntax
CREATE OR REPLACE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;
Now we want to add the “Category” column to the “Current Product List” view. We will update the view with the following SQL:

CREATE OR REPLACE VIEW [Current Product List] AS
SELECT ProductID, ProductName, Category
FROM Products
WHERE Discontinued = No;
SQL Dropping a View
You can delete a view with the DROP VIEW command.

SQL DROP VIEW Syntax
DROP VIEW view_name;

❮ Previous Next ❯

Toggle navigation
TUTORIAL HOME
SQL DROP TABLE  Statement
❮ Previous Next ❯
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;

❮ Previous Next ❯

Toggle navigation
TUTORIAL HOME
SQL AUTO INCREMENT  Field
❮ Previous Next ❯
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 “ID” column to be an auto-increment primary key field in the “Persons” table:

CREATE TABLE Persons (
    ID int NOT NULL AUTO_INCREMENT,
    LastName varchar(255) NOT NULL,
    FirstName varchar(255),
    Age int,
    PRIMARY KEY (ID)
);
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 “ID” 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 “ID” 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 “ID” column to be an auto-increment primary key field in the “Persons” table:

CREATE TABLE Persons (
    ID 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 “ID” 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 “ID” 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 “ID” 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 “ID” column to be an auto-increment primary key field in the “Persons” table:

CREATE TABLE Persons (
    ID Integer PRIMARY KEY AUTOINCREMENT,
    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 “ID” 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 “ID” 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 “P_Id” 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 (ID,FirstName,LastName)
VALUES (seq_person.nextval,’Lars’,’Monsen’);
The SQL statement above would insert a new record into the “Persons” table. The “ID” 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”.

❮ Previous Next ❯

Toggle navigation
TUTORIAL HOME
SQL GROUP BY  Statement
❮ Previous Next ❯
The SQL GROUP BY Statement
The GROUP BY statement is often used with aggregate functions (COUNT, MAX, MIN, SUM, AVG) to group the result-set by one or more columns.

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:

CustomerID CustomerName ContactName Address City PostalCode Country
1

Alfreds Futterkiste Maria Anders Obere Str. 57 Berlin 12209 Germany
2 Ana Trujillo Emparedados y helados Ana Trujillo Avda. de la Constitución 2222 México D.F. 05021 Mexico
3 Antonio Moreno Taquería Antonio Moreno Mataderos 2312 México D.F. 05023 Mexico
4

Around the Horn Thomas Hardy 120 Hanover Sq. London WA1 1DP UK
5 Berglunds snabbköp Christina Berglund Berguvsvägen 8 Luleå S-958 22 Sweden
SQL GROUP BY Examples
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;

»

❮ Previous Next ❯

Toggle navigation
TUTORIAL HOME
SQL DEFAULT  Constraint
❮ Previous Next ❯
SQL DEFAULT Constraint
The DEFAULT constraint is used to provide 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 GETDATE():

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 / 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;

❮ Previous Next ❯

Toggle navigation
TUTORIAL HOME
SQL CREATE INDEX  Statement
❮ Previous Next ❯
SQL CREATE INDEX Statement
The CREATE INDEX statement is used to create indexes in tables.

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.

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;

❮ Previous Next ❯

TUTORIAL HOME
SQL FOREIGN KEY Constraint
❮ Previous Next ❯
SQL FOREIGN KEY Constraint
A FOREIGN KEY is a key used to link two tables together.

A FOREIGN KEY in a table points to a PRIMARY KEY in another 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 is used to prevent actions that would destroy links between tables.

The FOREIGN KEY constraint also prevents invalid data from being inserted into the foreign key column, because it has to be one of the values contained in the table it points to.

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:

MySQL / SQL Server / Oracle / MS Access:

ALTER TABLE Orders
ADD CONSTRAINT FK_PersonOrder
FOREIGN KEY (PersonID) REFERENCES Persons(PersonID);
DROP a FOREIGN KEY Constraint
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;

❮ Previous Next ❯

Toggle navigation
TUTORIAL HOME
SQL CHECK  Constraint
❮ Previous Next ❯
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 single column it allows 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 you can not have any person below 18 years:

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;

❮ Previous Next ❯

Toggle navigation
TUTORIAL HOME
SQL NOT NULL  Constraint
❮ Previous Next ❯
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.

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
);
»
Tip: If the table has already been created, you can add a NOT NULL constraint to a column with the ALTER TABLE statement.

❮ Previous Next ❯