The DELETE statement is used to delete existing records in a table.
DELETE FROM table_name WHERE condition;
Note: Be careful when deleting records in a table! Notice the
WHEREDELETE 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!
The UPDATE statement is used to modify the existing records in a table.
UPDATE table_name SET column1 = value1, column2 = value2, ... WHERE condition;
Note: Be careful when updating records in a table! Notice the
WHEREUPDATE 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!
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!
The INSERT INTO statement is used to insert new records in a table.
It is possible to write the INSERT INTO statement in two ways:
1. Specify both the column names and the values to be inserted:
INSERT INTO table_name (column1, column2, column3, ...) VALUES (value1, value2, value3, ...);
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
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:
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.
SELECT column1, column2, ... FROM table_name WHERE NOT condition;
Below is a selection from the Customers table used in the examples:
| 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 |
Select customers that does not start with the letter ‘A’:
SELECT * FROM Customers
WHERE CustomerName NOT LIKE 'A%';
Select customers with a customerID not between 10 and 60:
SELECT * FROM Customers
WHERE CustomerID NOT BETWEEN 10 AND 60;
Select customers that are not from Paris or London:
SELECT * FROM Customers
WHERE City NOT IN ('Paris', 'London');
Select customers with a CustomerId not greater than 50:
SELECT * FROM Customers
WHERE NOT CustomerID > 50;
Note: There is a not-greater-than operator: !> that would give you the same result.
Select customers with a CustomerID not less than 50:
SELECT * FROM Customers
WHERE NOT CustomerId < 50;
Note: There is a not-less-than operator: !< that would give you the same result.
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:
Select all customers from Germany or Spain:
SELECT *
FROM Customers
WHERE Country = 'Germany' OR Country = 'Spain';
SELECT column1, column2, ... FROM table_name WHERE condition1 OR condition2 OR condition3 ...;
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.
Below is a selection from the Customers table used in the examples:
| 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 |
The following SQL statement selects all fields from Customers where either City is “Berlin”,
CustomerName
Country
SELECT * FROM Customers
WHERE City = 'Berlin' OR CustomerName LIKE 'G%' OR Country = 'Norway';
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.
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:
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 WHERE clause can contain one or many
AND
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’:
Select all customers from Spain that starts with the letter ‘G’:
SELECT *
FROM Customers
WHERE Country = 'Spain' AND CustomerName LIKE 'G%';
SELECT column1, column2, ... FROM table_name WHERE condition1 AND condition2 AND condition3 ...;
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.
Below is a selection from the Customers table used in the examples:
| 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 |
The following SQL statement selects all fields from Customers where Country is “Germany” AND City is “Berlin” AND PostalCode is higher than 12000:
SELECT * FROM Customers
WHERE Country = 'Germany'
AND City = 'Berlin'
AND PostalCode > 12000;
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.
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:
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 ORDER BY keyword is used to sort the result-set in ascending or descending order.
Sort the products by price:
SELECT * FROM Products
ORDER BY Price;
SELECT column1, column2, ... FROM table_name ORDER BY column1, column2, ... ASC|DESC;
Below is a selection from the Products table used in the examples:
| 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 |
The ORDER BY keyword sorts the records in ascending order by default. To sort the records in descending order, use the
DESC
Sort the products from highest to lowest price:
SELECT * FROM Products
ORDER BY Price DESC;
For string values the ORDER BY keyword will order alphabetically:
Sort the products alphabetically by ProductName:
SELECT * FROM Products
ORDER BY ProductName;
To sort the table reverse alphabetically, use the DESC keyword:
Sort the products by ProductName in reverse order:
SELECT * FROM Products
ORDER BY ProductName DESC;
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:
SELECT * FROM Customers
ORDER BY Country, CustomerName;
The following SQL statement selects all customers from the “Customers” table, sorted ascending by the “Country” and descending by the “CustomerName” column:
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
The WHERE clause is used to filter records.
It is used to extract only those records that fulfill a specified condition.
Select all customers from Mexico:
SELECT * FROM Customers
WHERE Country='Mexico';
The SELECT DISTINCT statement is used to return only distinct (different) values.
Select all the different countries from the “Customers” table :
SELECT DISTINCT Country FROM Customers;
Inside a table, a column often contains many duplicate values; and sometimes you only want to list the different (distinct) values.