SQL Basic Syntax

SELECT Statement

SELECT column_name,column_name
FROM table_name;

SELECT * FROM table_name;

SELECT CustomerName,City FROM Customers;

SELECT * FROM Customers;

(all the columns from the "Customers" table)

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!