SELECT Syntax
Example
SELECT column_name,column_name
FROM table_name;
and
SELECT * FROM table_name;
SELECT Column Example
Example
SELECT CustomerName,City FROM Customers;
SELECT * Example
Example
SELECT * FROM Customers;
SELECT DISTINCT Syntax
Example
SELECT DISTINCT column_name,column_name
FROM table_name;
SELECT DISTINCT Example
Example
SELECT DISTINCT City FROM Customers;
SQL WHERE Syntax
SELECT column_name,column_name
FROM table_name
WHERE column_name operator value;
WHERE Clause Example
Example
SELECT * FROM Customers
WHERE Country='Mexico';
Text Fields vs. Numeric Fields
Example
SELECT * FROM Customers
WHERE CustomerID=1;
SELECT DISTINCT Example
Example
SELECT DISTINCT City FROM Customers;
AND Operator Exampl
Example
SELECT * FROM Customers
WHERE Country='Germany'
AND City='Berlin';
OR Operator Example
Example
SELECT * FROM Customers
WHERE City='Berlin'
OR City='München';
Combining AND & OR
Example
SELECT * FROM Customers
WHERE Country='Germany'
AND (City='Berlin' OR City='München');
ORDER BY Example
Example
SELECT * FROM Customers
ORDER BY Country;
ORDER BY DESC Example
Example
SELECT * FROM Customers
ORDER BY Country DESC;
ORDER BY Several Columns Example
Example
SELECT * FROM Customers
ORDER BY Country, CustomerName;
ORDER BY Several Columns Example 2
Example
SELECT * FROM Customers
ORDER BY Country ASC, CustomerName DESC;
INSERT INTO Example
Example
INSERT INTO Customers (CustomerName, ContactName, Address, City, PostalCode, Country)
VALUES ('Cardinal','Tom B. Erichsen','Skagen 21','Stavanger','4006','Norway');
Insert Data Only in Specified Columns
Example
INSERT INTO Customers (CustomerName, City, Country)
VALUES ('Cardinal', 'Stavanger', 'Norway');
SQL UPDATE Statement
Example
Change the value of the "City" column of a record in the "Customers" table:
UPDATE Customers
SET City='Hamburg'
WHERE CustomerID=1;
UPDATE Multiple Columns
Example
UPDATE Customers
SET ContactName='Alfred Schmidt', City='Frankfurt'
WHERE CustomerID=1;
UPDATE Multiple Records
Example
UPDATE Customers
SET ContactName='Juan'
WHERE Country='Mexico';
Update Warning!
Example
UPDATE Customers
SET ContactName='Juan';
SQL DELETE Example
Example
DELETE FROM Customers
WHERE CustomerName='Alfreds Futterkiste' AND ContactName='Maria Anders';
Delete All Data
Example
DELETE FROM table_name;
or
DELETE * FROM table_name;
SQL Injection Based on 1=1 is Always True
Server Result
Example
SELECT * FROM Users WHERE UserId = 105 or 1=1;
Example
The SQL statement above is much the same as this:
SELECT UserId, Name, Password FROM Users WHERE UserId = 105 or 1=1;
SQL Injection Based on ""="" is Always True
Here is a common construction, used to verify user login to a web site:
User Name:John Doe
Password:myPass
server Code
uName = getRequestString("UserName");
uPass = getRequestString("UserPass");
Example
'SELECT * FROM Users WHERE Name ="' + uName + '" AND Pass ="' + uPass + '"'
Result
SELECT * FROM Users WHERE Name ="John Doe" AND Pass ="myPass"
User Name: " or ""="
Password: " or ""="
The code at the server will create a valid SQL statement like this:
Result
SELECT * FROM Users WHERE Name ="" or ""="" AND Pass ="" or ""="";
SQL Injection Based on Batched SQL Statements
Most databases support batched SQL statement, separated by semicolon.
Example
SELECT * FROM Users; DROP TABLE Suppliers
And the following input:
User id:
105; DROP TABLE Suppliers
The code at the server would create a valid SQL statement like this:
Result
SELECT * FROM Users WHERE UserId = 105; DROP TABLE Suppliers;
Parameters for Protection
ASP.NET Razor Example
txtUserId = getRequestString("UserId");
txtSQL = "SELECT * FROM Users WHERE UserId = @0";
db.Execute(txtSQL,txtUserId);
Note that parameters are represented in the SQL statement by a @ marker.
The SQL engine checks each parameter to ensure that it is correct for its column and are treated literally, and not as part of the SQL to be executed.
Another Example
txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
db.Execute(txtSQL,txtNam,txtAdd,txtCit);
Examples
The following examples shows how to build parameterized queries in some common web languages.
SELECT STATEMENT IN ASP.NET:
txtUserId = getRequestString("UserId");
sql = "SELECT * FROM Customers WHERE CustomerId = @0";
command = new SqlCommand(sql);
command.Parameters.AddWithValue("@0",txtUserID);
command.ExecuteReader();
or
INSERT INTO STATEMENT IN ASP.NET:
txtNam = getRequestString("CustomerName");
txtAdd = getRequestString("Address");
txtCit = getRequestString("City");
txtSQL = "INSERT INTO Customers (CustomerName,Address,City) Values(@0,@1,@2)";
command = new SqlCommand(txtSQL);
command.Parameters.AddWithValue("@0",txtNam);
command.Parameters.AddWithValue("@1",txtAdd);
command.Parameters.AddWithValue("@2",txtCit);
command.ExecuteNonQuery();
or
INSERT INTO STATEMENT IN PHP:
$stmt = $dbh->prepare("INSERT INTO Customers (CustomerName,Address,City)
VALUES (:nam, :add, :cit)");
$stmt->bindParam(':nam', $txtNam);
$stmt->bindParam(':add', $txtAdd);
$stmt->bindParam(':cit', $txtCit);
$stmt->execute();
The SQL SELECT TOP Clause
SQL Server / MS Access Syntax
Example
SELECT TOP number|percent column_name(s)
FROM table_name;
SQL SELECT TOP Equivalent in MySQL and Oracle
MySQL Syntax
Example
SELECT column_name(s)
FROM table_name
LIMIT number;
Example
SELECT *
FROM Persons
LIMIT 5;
Oracle Syntax
SELECT column_name(s)
FROM table_name
WHERE ROWNUM <= number;
Example
SELECT *
FROM Persons
WHERE ROWNUM <=5;
SQL SELECT TOP Example
Example
SELECT TOP 2 * FROM Customers;
SQL SELECT TOP PERCENT Example
Example
SELECT TOP 50 PERCENT * FROM Customers;
SQL LIKE Syntax
Example
SELECT column_name(s)
FROM table_name
WHERE column_name LIKE pattern;
SQL LIKE Operator Examples
The following SQL statement selects all customers with a City starting with the letter "s":
Example
SELECT * FROM Customers
WHERE City LIKE 's%';
Example
SELECT * FROM Customers
WHERE City LIKE '%s';
Example
SELECT * FROM Customers
WHERE Country LIKE '%land%';
Example
SELECT * FROM Customers
WHERE Country NOT LIKE '%land%';
Using the SQL % Wildcard
Example
SELECT * FROM Customers
WHERE City LIKE 'ber%';
Example
SELECT * FROM Customers
WHERE City LIKE '%es%';
Using the SQL _ Wildcard
Example
SELECT * FROM Customers
WHERE City LIKE '_erlin';
Example
SELECT * FROM Customers
WHERE City LIKE 'L_n_on';
Using the SQL [charlist] Wildcard
The following SQL statement selects all customers with a City starting with "b", "s", or "p":
Example
SELECT * FROM Customers
WHERE City LIKE '[bsp]%';
Example
SELECT * FROM Customers
WHERE City LIKE '[a-c]%';
Example
SELECT * FROM Customers
WHERE City LIKE '[!bsp]%';
Example
SELECT * FROM Customers
WHERE City NOT LIKE '[bsp]%';
IN Operator Example
Example
SELECT * FROM Customers
WHERE City IN ('Paris','London');
BETWEEN Operator Example
The following SQL statement selects all products with a price BETWEEN 10 and 20:
Example
SELECT * FROM Products
WHERE Price BETWEEN 10 AND 20;
NOT BETWEEN Operator Example
To display the products outside the range of the previous example, use NOT BETWEEN:
Example
SELECT * FROM Products
WHERE Price NOT BETWEEN 10 AND 20;
BETWEEN Operator with IN Example
Example
SELECT * FROM Products
WHERE (Price BETWEEN 10 AND 20)
AND NOT CategoryID IN (1,2,3);
BETWEEN Operator with Text Value Example
Example
SELECT * FROM Products
WHERE ProductName BETWEEN 'C' AND 'M';
NOT BETWEEN Operator with Text Value Example
Example
SELECT * FROM Products
WHERE ProductName NOT BETWEEN 'C' AND 'M';
BETWEEN Operator with Date Value Example
Example
SELECT * FROM Orders
WHERE OrderDate BETWEEN #07/04/1996# AND #07/09/1996#;
Alias Example for Table Columns
Example
SELECT CustomerName AS Customer, ContactName AS [Contact Person]
FROM Customers;
Example
SELECT CustomerName, Address+', '+City+', '+PostalCode+', '+Country AS Address
FROM Customers;
Alias Example for Tables
Example
SELECT o.OrderID, o.OrderDate, c.CustomerName
FROM Customers AS c, Orders AS o
WHERE c.CustomerName="Around the Horn" AND c.CustomerID=o.CustomerID;
Example
SELECT Orders.OrderID, Orders.OrderDate, Customers.CustomerName
FROM Customers, Orders
WHERE Customers.CustomerName="Around the Horn" AND Customers.CustomerID=Orders.CustomerID;
SQL JOIN
Example
SELECT Orders.OrderID, Customers.CustomerName, Orders.OrderDate
FROM Orders
INNER JOIN Customers
ON Orders.CustomerID=Customers.CustomerID;
SQL INNER JOIN Syntax
SELECT column_name(s)
FROM table1
INNER JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
JOIN table2
ON table1.column_name=table2.column_name;
SQL INNER JOIN Example
The following SQL statement will return all customers with orders:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
INNER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
SQL LEFT JOIN Syntax
SELECT column_name(s)
FROM table1
LEFT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
LEFT OUTER JOIN table2
ON table1.column_name=table2.column_name;
SQL LEFT JOIN Example
The following SQL statement will return all customers, and any orders they might have:
Example
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
SQL RIGHT JOIN Keyword
SQL RIGHT JOIN Syntax
SELECT column_name(s)
FROM table1
RIGHT JOIN table2
ON table1.column_name=table2.column_name;
or:
SELECT column_name(s)
FROM table1
RIGHT OUTER JOIN table2
ON table1.column_name=table2.column_name;
SQL RIGHT JOIN Example
The following SQL statement will return all employees, and any orders they have placed:
Example
SELECT Orders.OrderID, Employees.FirstName
FROM Orders
RIGHT JOIN Employees
ON Orders.EmployeeID=Employees.EmployeeID
ORDER BY Orders.OrderID;
SQL FULL OUTER JOIN Keyword
SQL FULL OUTER JOIN Syntax
SELECT column_name(s)
FROM table1
FULL OUTER JOIN table2
ON table1.column_name=table2.column_name;
SQL FULL OUTER JOIN Example
The following SQL statement selects all customers, and all orders:
SELECT Customers.CustomerName, Orders.OrderID
FROM Customers
FULL OUTER JOIN Orders
ON Customers.CustomerID=Orders.CustomerID
ORDER BY Customers.CustomerName;
The SQL UNION Operator
SQL UNION Syntax
SELECT column_name(s) FROM table1
UNION
SELECT column_name(s) FROM table2;
SQL UNION ALL Syntax
SELECT column_name(s) FROM table1
UNION ALL
SELECT column_name(s) FROM table2;
SQL UNION Example
Example
SELECT City FROM Customers
UNION
SELECT City FROM Suppliers
ORDER BY City;
SQL UNION ALL Example
Example
SELECT City FROM Customers
UNION ALL
SELECT City FROM Suppliers
ORDER BY City;
SQL UNION ALL With WHERE
Example
SELECT City, Country FROM Customers
WHERE Country='Germany'
UNION ALL
SELECT City, Country FROM Suppliers
WHERE Country='Germany'
ORDER BY City;
SQL SELECT INTO Syntax
Example
SELECT *
INTO newtable [IN externaldb]
FROM table1;
Example
SELECT column_name(s)
INTO newtable [IN externaldb]
FROM table1;
SQL SELECT INTO Examples
Example
SELECT *
INTO CustomersBackup2013
FROM Customers;
Example
SELECT *
INTO CustomersBackup2013 IN 'Backup.mdb'
FROM Customers;
Example
SELECT CustomerName, ContactName
INTO CustomersBackup2013
FROM Customers;
Example
SELECT *
INTO CustomersBackup2013
FROM Customers
WHERE Country='Germany';
Example
SELECT Customers.CustomerName, Orders.OrderID
INTO CustomersOrderBackup2013
FROM Customers
LEFT JOIN Orders
ON Customers.CustomerID=Orders.CustomerID;
Example
SELECT *
INTO newtable
FROM table1
WHERE 1=0;
SQL INSERT INTO SELECT Syntax
Example
INSERT INTO table2
SELECT * FROM table1;
Example
INSERT INTO table2
(column_name(s))
SELECT column_name(s)
FROM table1;
SQL INSERT INTO SELECT Examples
Example
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers;
Example
INSERT INTO Customers (CustomerName, Country)
SELECT SupplierName, Country FROM Suppliers
WHERE Country='Germany';
SQL CREATE DATABASE Syntax
CREATE DATABASE dbname;
SQL CREATE DATABASE Example
The following SQL statement creates a database called "my_db":
CREATE DATABASE my_db;
SQL CREATE TABLE Syntax
CREATE TABLE table_name
(
column_name1 data_type(size),
column_name2 data_type(size),
column_name3 data_type(size),
....
);
SQL CREATE TABLE Example
Example
CREATE TABLE Persons
(
PersonID int,
LastName varchar(255),
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
SQL CREATE TABLE + CONSTRAINT Syntax
Example
CREATE TABLE table_name
(
column_name1 data_type(size) constraint_name,
column_name2 data_type(size) constraint_name,
column_name3 data_type(size) constraint_name,
....
);
SQL NOT NULL Constraint
Example
CREATE TABLE PersonsNotNull
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
SQL UNIQUE Constraint on CREATE TABLE
SQL Server / Oracle / MS Access:
Example
CREATE TABLE Persons
(
P_Id int NOT NULL UNIQUE,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
MySQL:Example
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
UNIQUE (P_Id)
);
MySQL / SQL Server / Oracle / MS Access:
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName)
);
SQL UNIQUE Constraint on ALTER TABLE
MySQL / SQL Server / Oracle / MS Access:
Example
ALTER TABLE Persons
ADD UNIQUE (P_Id);
MySQL / SQL Server / Oracle / MS Access:
Example
ALTER TABLE Persons
ADD CONSTRAINT uc_PersonID UNIQUE (P_Id,LastName);
To DROP a UNIQUE Constraint
MySQL:
ALTER TABLE Persons
DROP INDEX uc_PersonID
SQL Server / Oracle / MS Access:
ALTER TABLE Persons
DROP CONSTRAINT uc_PersonID;
SQL PRIMARY KEY Constraint on CREATE TABLE
MySQL:Example
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (P_Id)
);
SQL Server / Oracle / MS Access:Example
CREATE TABLE Persons
(
P_Id int NOT NULL PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
MySQL / SQL Server / Oracle / MS Access:Example
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName)
);
SQL PRIMARY KEY Constraint on ALTER TABLE
MySQL / SQL Server / Oracle / MS Access:Example
ALTER TABLE Persons
ADD PRIMARY KEY (P_Id)
MySQL / SQL Server / Oracle / MS Access:Example
ALTER TABLE Persons
ADD CONSTRAINT pk_PersonID PRIMARY KEY (P_Id,LastName);
To DROP a PRIMARY KEY Constraint
MySQL:Example
ALTER TABLE Persons
DROP PRIMARY KEY
SQL Server / Oracle / MS Access:Example
ALTER TABLE Persons
DROP CONSTRAINT pk_PersonID
SQL FOREIGN KEY Constraint on CREATE TABLE
MySQL:Example
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
FOREIGN KEY (P_Id) REFERENCES Persons(P_Id)
);
SQL Server / Oracle / MS Access:Example
CREATE TABLE Orders
(
O_Id int NOT NULL PRIMARY KEY,
OrderNo int NOT NULL,
P_Id int FOREIGN KEY REFERENCES Persons(P_Id)
)
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:Example
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
PRIMARY KEY (O_Id),
CONSTRAINT fk_PerOrders FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
);
SQL FOREIGN KEY Constraint on ALTER TABLE
To create a FOREIGN KEY constraint on the "P_Id" column when the "Orders" table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:Example
ALTER TABLE Orders
ADD FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
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:Example
ALTER TABLE Orders
ADD CONSTRAINT fk_PerOrders
FOREIGN KEY (P_Id)
REFERENCES Persons(P_Id)
To DROP a FOREIGN KEY Constraint
To drop a FOREIGN KEY constraint, use the following SQL:
MySQL:Example
ALTER TABLE Orders
DROP FOREIGN KEY fk_PerOrders
SQL Server / Oracle / MS Access:Example
ALTER TABLE Orders
DROP CONSTRAINT fk_PerOrders;
SQL CHECK Constraint on CREATE TABLE
The following SQL creates a CHECK constraint on the "P_Id" column when the "Persons" table is created. The CHECK constraint specifies that the column "P_Id" must only include integers greater than 0.
MySQL:Example
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CHECK (P_Id>0)
)
SQL Server / Oracle / MS Access:Example
CREATE TABLE Persons
(
P_Id int NOT NULL CHECK (P_Id>0),
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
)
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:Example
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
)
SQL CHECK Constraint on ALTER TABLE
To create a CHECK constraint on the "P_Id" column when the table is already created, use the following SQL:
MySQL / SQL Server / Oracle / MS Access:Example
ALTER TABLE Persons
ADD CHECK (P_Id>0)
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:Example
ALTER TABLE Persons
ADD CONSTRAINT chk_Person CHECK (P_Id>0 AND City='Sandnes')
To DROP a CHECK Constraint
To drop a CHECK constraint, use the following SQL:
SQL Server / Oracle / MS Access:Example
ALTER TABLE Persons
DROP CONSTRAINT chk_Person
MySQL:
ALTER TABLE Persons
DROP CHECK chk_Person;
SQL DEFAULT Constraint on CREATE TABLE
The following SQL creates a DEFAULT constraint on the "City" column when the "Persons" table is created:
My SQL / SQL Server / Oracle / MS Access:Example
CREATE TABLE Persons
(
P_Id int NOT NULL,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255) DEFAULT 'Sandnes'
)
The DEFAULT constraint can also be used to insert system values, by using functions like GETDATE():
CREATE TABLE Orders
(
O_Id int NOT NULL,
OrderNo int NOT NULL,
P_Id int,
OrderDate date DEFAULT GETDATE()
);
SQL DEFAULT Constraint on ALTER TABLE
To create a DEFAULT constraint on the "City" column when the table is already created, use the following SQL:
MySQL:Example
ALTER TABLE Persons
ALTER City SET DEFAULT 'SANDNES'
SQL Server / MS Access:Example
ALTER TABLE Persons
ALTER COLUMN City SET DEFAULT 'SANDNES'
Oracle:Example
ALTER TABLE Persons
MODIFY City DEFAULT 'SANDNES'
To DROP a DEFAULT Constraint
To drop a DEFAULT constraint, use the following SQL:
MySQL:Example
ALTER TABLE Persons
ALTER City DROP DEFAULT;
SQL Server / Oracle / MS Access:Example
ALTER TABLE Persons
ALTER COLUMN City DROP DEFAULT;
SQL CREATE INDEX Syntax
Creates an index on a table. Duplicate values are allowed:
CREATE INDEX index_name
ON table_name (column_name);
SQL CREATE UNIQUE INDEX Syntax
Creates a unique index on a table. Duplicate values are not allowed:
CREATE UNIQUE INDEX index_name
ON table_name (column_name)
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 "PIndex" on the "LastName" column in the "Persons" table:
CREATE INDEX PIndex
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 PIndex
ON Persons (LastName, FirstName);
DROP INDEX Syntax for MS Access:Example
DROP INDEX index_name ON table_name;
DROP INDEX Syntax for MS SQL Server:Example
DROP INDEX table_name.index_name;
DROP INDEX Syntax for DB2/Oracle:Example
DROP INDEX index_name;
DROP INDEX Syntax for MySQL:
ALTER TABLE table_name DROP INDEX index_name
The DROP TABLE Statement
Example
DROP TABLE table_name
The DROP DATABASE Statement
Example
DROP DATABASE database_name
The TRUNCATE TABLE Statement
Example
TRUNCATE TABLE table_name;
SQL ALTER TABLE Syntax
Example
ALTER TABLE table_name
ADD column_name datatype
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
To change the data type of a column in a table, use the following syntax:
SQL Server / MS Access:
Example
ALTER TABLE table_name
ALTER COLUMN column_name datatype;
My SQL / Oracle (prior version 10G):
Example
ALTER TABLE table_name
MODIFY COLUMN column_name datatype
Oracle 10G and later:
Example
ALTER TABLE table_name
MODIFY column_name datatype
Change Data Type Example
Example
ALTER TABLE Persons
ALTER COLUMN DateOfBirth year
Example
DROP COLUMN Example
Example
ALTER TABLE Persons
DROP COLUMN DateOfBirth;
Syntax for MySQL
Example
CREATE TABLE Persons
(
ID int NOT NULL AUTO_INCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255),
PRIMARY KEY (ID)
)
Example
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
Example
CREATE TABLE Persons
(
ID int IDENTITY(1,1) PRIMARY KEY,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Syntax for Access
Example
CREATE TABLE Persons
(
ID Integer PRIMARY KEY AUTOINCREMENT,
LastName varchar(255) NOT NULL,
FirstName varchar(255),
Address varchar(255),
City varchar(255)
);
Syntax for Oracle
Example
CREATE SEQUENCE seq_person
MINVALUE 1
START WITH 1
INCREMENT BY 1
CACHE 10
SQL CREATE VIEW Syntax
CREATE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition;
SQL CREATE VIEW Examples
Example
CREATE VIEW [Current Product List] AS
SELECT ProductID,ProductName
FROM Products
WHERE Discontinued=No
Example
SELECT * FROM [Current Product List]
Example
CREATE VIEW [Products Above Average Price] AS
SELECT ProductName,UnitPrice
FROM Products
WHERE UnitPrice>(SELECT AVG(UnitPrice) FROM Products)
Example
SELECT * FROM [Products Above Average Price]
Example
CREATE VIEW [Category Sales For 1997] AS
SELECT DISTINCT CategoryName,Sum(ProductSales) AS CategorySales
FROM [Product Sales for 1997]
GROUP BY CategoryName
Example
SELECT * FROM [Category Sales For 1997]
Example
SELECT * FROM [Category Sales For 1997]
WHERE CategoryName='Beverages'
SQL CREATE OR REPLACE VIEW Syntax
Example
CREATE OR REPLACE VIEW view_name AS
SELECT column_name(s)
FROM table_name
WHERE condition
Example
CREATE OR REPLACE VIEW [Current Product List] AS
SELECT ProductID,ProductName,Category
FROM Products
WHERE Discontinued=No
SQL DROP VIEW Syntax
DROP VIEW view_name;
SQL Working with Dates
SELECT * FROM Orders WHERE OrderDate='2008-11-11';
Example
SELECT * FROM Orders WHERE OrderDate='2008-11-11';
SQL IS NULL
Example
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NULL;
SQL IS NOT NULL
Example
SELECT LastName,FirstName,Address FROM Persons
WHERE Address IS NOT NULL;
SQL ISNULL(), NVL(), IFNULL() and COALESCE() Functions
Example
SELECT ProductName,UnitPrice*(UnitsInStock+UnitsOnOrder)
FROM Products;
MS Access
SELECT ProductName,UnitPrice*(UnitsInStock+IIF(ISNULL(UnitsOnOrder),0,UnitsOnOrder))
FROM Products;
SQL Server
SELECT ProductName,UnitPrice*(UnitsInStock+ISNULL(UnitsOnOrder,0))
FROM Products;
Oracle
Example
SELECT ProductName,UnitPrice*(UnitsInStock+NVL(UnitsOnOrder,0))
FROM Products;
MySQL
Example
SELECT ProductName,UnitPrice*(UnitsInStock+IFNULL(UnitsOnOrder,0))
FROM Products
or we can use the COALESCE() function, like this:
SELECT ProductName,UnitPrice*(UnitsInStock+COALESCE(UnitsOnOrder,0))
FROM Products
Single Line Comments
Example
A single-line comment as an explanation:
--Select all:
SELECT * FROM Customers;
Example
A single-line comment to ignore the end of a line:
SELECT * FROM Customers -- WHERE City='Berlin';
Example
A single-line comment to ignore a statement:
--SELECT * FROM Customers;
SELECT * FROM Products;
Multi-line Comments
Example
A multi-line comment as an explanation:
/*Select all the columns
of all the records
in the Customers table:*/
SELECT * FROM Customers;
Example
A multi-line comment to ignore many statements:
/*SELECT * FROM Customers;
SELECT * FROM Products;
SELECT * FROM Orders;
SELECT * FROM Categories;*/
SELECT * FROM Suppliers;
Comments in Statements
Example
Ignore part of a line:
SELECT CustomerName, /*City,*/ Country FROM Customers;
Example
Ignore parts of a statment:
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;
The AVG() Function
Example
SELECT AVG(column_name) FROM table_name;
SQL AVG() Example
Example
SELECT AVG(Price) AS PriceAverage FROM Products;
Example
SELECT ProductName, Price FROM Products
WHERE Price>(SELECT AVG(Price) FROM Products);
SQL COUNT(column_name) Syntax
Example
SELECT COUNT(column_name) FROM table_name;
SQL COUNT(*) Syntax
Example
SELECT COUNT(*) FROM table_name;
SQL COUNT(DISTINCT column_name) Syntax
Example
SELECT COUNT(DISTINCT column_name) FROM table_name;
SQL COUNT(column_name) Example
Example
SELECT COUNT(CustomerID) AS OrdersFromCustomerID7 FROM Orders
WHERE CustomerID=7;
SQL COUNT(*) Example
Example
SELECT COUNT(*) AS NumberOfOrders FROM Orders;
SQL COUNT(DISTINCT column_name) Example
Example
SELECT COUNT(DISTINCT CustomerID) AS NumberOfCustomers FROM Orders;
SQL FIRST() Syntax
SELECT FIRST(column_name) FROM table_name;
Note: The FIRST() function is only supported in MS Access.
SQL Server Syntax
Example
SELECT TOP 1 column_name FROM table_name
ORDER BY column_name ASC;
Example
SELECT TOP 1 CustomerName FROM Customers
ORDER BY CustomerID ASC;
MySQL Syntax
Example
SELECT column_name FROM table_name
ORDER BY column_name ASC
LIMIT 1;
Example
SELECT CustomerName FROM Customers
ORDER BY CustomerID ASC
LIMIT 1;
Oracle Syntax
Example
SELECT column_name FROM table_name
WHERE ROWNUM <=1
ORDER BY column_name ASC;
Example
SELECT CustomerName FROM Customers
WHERE ROWNUM <=1
ORDER BY CustomerID ASC;
SQL FIRST() Example
Example
SELECT FIRST(CustomerName) AS FirstCustomer FROM Customers;
SQL LAST() Syntax
Example
SELECT LAST(column_name) FROM table_name;
SQL Server Syntax
Example
SELECT TOP 1 column_name FROM table_name
ORDER BY column_name DESC;
Example
SELECT TOP 1 CustomerName FROM Customers
ORDER BY CustomerID DESC;
MySQL Syntax
Example
SELECT column_name FROM table_name
ORDER BY column_name DESC
LIMIT 1;
Example
SELECT CustomerName FROM Customers
ORDER BY CustomerID DESC
LIMIT 1;
Oracle Syntax
Example
SELECT column_name FROM table_name
ORDER BY column_name DESC
WHERE ROWNUM <=1;
Example
SELECT CustomerName FROM Customers
ORDER BY CustomerID DESC
WHERE ROWNUM <=1;
SQL LAST() Example
Example
SELECT LAST(CustomerName) AS LastCustomer FROM Customers;
SQL MAX() Syntax
Example
SELECT MAX(column_name) FROM table_name;
SQL MIN() Syntax
Example
SELECT MIN(column_name) FROM table_name;
SQL MIN() Example
Example
SELECT MIN(Price) AS SmallestOrderPrice FROM Products;
SQL SUM() Syntax
Example
SELECT SUM(column_name) FROM table_name;
Example
SELECT SUM(Quantity) AS TotalItemsOrdered FROM OrderDetails;
SQL GROUP BY Syntax
Example
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name;
Example
SELECT Shippers.ShipperName,COUNT(Orders.OrderID) AS NumberOfOrders FROM Orders
LEFT JOIN Shippers
ON Orders.ShipperID=Shippers.ShipperID
GROUP BY ShipperName;
SQL HAVING Syntax
Example
SELECT column_name, aggregate_function(column_name)
FROM table_name
WHERE column_name operator value
GROUP BY column_name
HAVING aggregate_function(column_name) operator value;
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;
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;
SQL UCASE() Syntax
Example
SELECT UCASE(column_name) FROM table_name;
Syntax for SQL Server
Example
SELECT UPPER(column_name) FROM table_name;
Example
SELECT UCASE(CustomerName) AS Customer, City
FROM Customers;
SQL LCASE() Syntax
Example
SELECT LCASE(column_name) FROM table_name;
Syntax for SQL Server
Example
SELECT LOWER(column_name) FROM table_name;
Example
SELECT LCASE(CustomerName) AS Customer, City
FROM Customers;
SQL MID() Syntax
Example
SELECT MID(column_name,start,length) AS some_name FROM table_name;
Example
SELECT SUBSTRING(column_name,start,length) AS some_name FROM table_name;
Example
SELECT SUBSTR(column_name,start,length) AS some_name FROM table_name;
SQL MID() Example
Example
SELECT MID(City,1,4) AS ShortCity
FROM Customers;
SQL LEN() Syntax
Example
SELECT LEN(column_name) FROM table_name;
Syntax for Oracle
Example
SELECT LENGTH(column_name) FROM table_name;
Example
SELECT CustomerName,LEN(Address) as LengthOfAddress
FROM Customers;
SQL ROUND() Syntax
Example
SELECT ROUND(column_name,decimals) FROM table_name;
Example
SELECT ProductName, ROUND(Price,0) AS RoundedPrice
FROM Products;
Example
SELECT ProductName, Price, Now() AS PerDate
FROM Products;
SQL FORMAT() Syntax
Example
SELECT FORMAT(column_name,format) FROM table_name;
Example
SELECT ProductName, Price, FORMAT(Now(),'YYYY-MM-DD') AS PerDate
FROM Products;
