Toggle navigation
TUTORIAL HOME
SQL PRIMARY KEY Constraint
❮ Previous Next ❯
SQL PRIMARY KEY Constraint
The PRIMARY KEY constraint uniquely identifies each record in a database table.

Primary keys must contain UNIQUE values, and cannot contain NULL values.

A table can have only one primary key, which may consist of single or 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;

❮ Previous Next ❯

Toggle navigation
TUTORIAL HOME
SQL UNIQUE  Constraint
❮ Previous Next ❯
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;

❮ Previous Next ❯

Toggle navigation
TUTORIAL HOME
SQL ALTER TABLE  Statement
❮ Previous Next ❯
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;
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;
ALTER TABLE – ALTER/MODIFY COLUMN
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 Data Types reference.

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

❮ Previous Next ❯

UTORIAL HOME
SQL Constraints
❮ Previous Next ❯
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 datatype constraint,
    column2 datatype constraint,
    column3 datatype constraint,
    ….
);
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:

NOT NULL – Ensures that a column cannot have a NULL value
UNIQUE – Ensures that all values in a column are different
PRIMARY KEY – A combination of a NOT NULL and UNIQUE. Uniquely identifies each row in a table
FOREIGN KEY – Uniquely identifies a row/record in another table
CHECK – Ensures that all values in a column satisfies a specific condition
DEFAULT – Sets a default value for a column when no value is specified
INDEX – Use to create and retrieve data from the database very quickly

❮ Previous Next ❯

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

❮ Previous Next ❯

Toggle navigation
TUTORIAL HOME
SQL CREATE TABLE  Statement
❮ Previous Next ❯
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 Data Types Reference.

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 INSERT INTO statement.

Create Table Using Another Table
A copy of an existing table can be created using a combination of the CREATE TABLE statement and the SELECT statement.

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 ….;

❮ Previous Next ❯

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

❮ Previous Next ❯

Toggle navigation
TUTORIAL HOME
SQL Comments
❮ Previous Next ❯
SQL Comments
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;
»

❮ Previous Next ❯

Toggle navigation
TUTORIAL HOME
SQL HAVING  Clause
❮ Previous Next ❯
The SQL HAVING Clause
The HAVING clause was added to SQL because the WHERE keyword could not 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:

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 HAVING Examples
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;

»

❮ Previous Next ❯