SELECT DISTINCT Statement
SELECT DISTINCT column_name,column_name
FROM table_name;
SELECT DISTINCT City FROM Customers;
WHERE Clause
extract only those records that fulfill a specified criterion.
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
SELECT * FROM Customers
WHERE Country='Mexico';
SELECT * FROM Customers
WHERE CustomerID=1;
Operators 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 an inclusive range |
| LIKE |
Search for a pattern |
| IN |
To specify multiple possible values for a column |
AND & OR Operators (Logical Operators)
The AND operator displays a record if both the first condition AND the second condition
are true.
The OR operator displays a record if either the first condition OR the second condition is true.
SELECT * FROM Customers
WHERE Country='Germany'
AND City='Berlin';
SELECT * FROM Customers
WHERE City='Berlin'
OR City='M?nchen';
SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='M?nchen');
ORDER BY Keyword
The ORDER BY keyword is used to sort the result-set by one or more columns.
The ORDER BY keyword sorts the records in ascending order by default.
To sort the records in a descending order, you can use the DESC
keyword.
SELECT column_name,column_name
FROM table_name
ORDER BY column_name,column_name ASC|DESC;
SELECT * FROM Customers
ORDER BY Country;
SELECT * FROM Customers
ORDER BY Country DESC;
SELECT * FROM Customers
ORDER BY Country,CustomerName;
LIKE Operator
search for a specified pattern in a column.
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
SELECT * FROM Customers WHERE City LIKE 's%';
selects all customers with a City starting with
the letter
"s":
SELECT * FROM Customers WHERE City LIKE '%s';
selects all customers with a City ending with the
letter
"s":
SQL Wildcard Characters
used to search for data within a table.
| Wildcard |
Description |
|---|
| % |
A substitute for zero or more characters |
| _ |
A substitute for a single character |
| [charlist] |
Sets and ranges of characters to match |
[^charlist]
or
[!charlist] |
Matches only a character NOT specified within the brackets |
SELECT * FROM Customers WHERE City LIKE '_erlin';
selects all customers with a City starting with
any character, followed by "erlin":
SELECT * FROM Customers WHERE City LIKE 'L_n_on';
SELECT * FROM Customers WHERE City LIKE '[bsp]%';
selects all customers with a City starting with "b",
"s", or "p"
SELECT * FROM Customers WHERE City LIKE '[a-c]%';
selects all customers with a City starting with "a",
"b", or "c"
SELECT * FROM Customers WHERE City LIKE '[!bsp]%';
selects all customers with a City NOT starting with "b",
"s", or "p"
IN Operator
specify multiple values in a WHERE clause.
SELECT column_name(s)
FROM table_name
WHERE column_name IN (value1,value2,...);
SELECT * FROM Customers
WHERE City IN ('Paris','London');
BETWEEN Operator
select values within a range.
SELECT column_name(s)
FROM table_name
WHERE column_name BETWEEN value1 AND value2;
The values can be numbers, text, or dates.
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'C' AND 'M';
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND
#07/09/1996#;
INSERT INTO Statement
insert new records in a table.
INSERT INTO table_name
VALUES (value1,value2,value3,...);
INSERT INTO table_name (column1,column2,column3,...)
VALUES (value1,value2,value3,...);
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
UPDATE Statement
update existing records in a table.
UPDATE table_name
SET column1=value1,column2=value2,...
WHERE some_column=some_value;
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Hamburg'
WHERE CustomerName='Alfreds Futterkiste';
DELETE Statement
delete rows/ records in a table.
DELETE FROM table_name
WHERE some_column=some_value;
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';
Delete All Data
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;
or
DELETE * FROM table_name;
Be very careful when deleting records. You cannot undo this statement!
