SQL by Example

This page presents simple examples that illustrate major features of SQL. Please report any errors that you encounter.

Using Access to build SQL statements

Writing SQL statements is difficult for the beginner. A fairly easy way to get started is to use MS Access to build the query in design mode and then to switch to SQL mode to see the actual statement. Sometimes it is necessary to simplify the resulting SQL statement by removing unnecessary parentheses before using the statement in an ODBC context. Other minor adjustments are sometimes necessary as well, such as replacing the word "LIKE" with "=". Finally, SQL statements built in Access are usually overqualified in the sense that every field name is preceded by a table name, even when doing so is unnecessary.

Common operators

=   <>   <   <=   >   >=
AND   OR   NOT

The database tables

Products (ProductID, ProductName, UnitPrice, CategoryID, LastPurchaseDate)

ProductID	text (key)
ProductName	text
UnitPrice	currency
CategoryID	integer
LastPurchaseDate	date

Orders (OrderID, ProductID, CustomerID, Quantity, PurchaseDate)

OrderID	auto number (key)
ProductID	text (foreign key)
CustomerID	text (foreign key)
Quantity	integer
PurchaseDate	date

SELECT statements

• Select all fields and rows from the Products table

SELECT * FROM Products;

• Select two fields but all rows from the Products table (place square brackets around field names whenever they contains spaces)

SELECT ProductID, UnitPrice FROM Products;

WHERE clause

• Select all rows for which CategoryID equals 1

SELECT * FROM Products WHERE CategoryID = 1;

• Select all rows for which LastPurchaseDate is after 1/1/2000

SELECT * FROM Products WHERE LastPurchaseDate > #01/01/2000#;

• Select all rows for which the ProductName equals "Potato Chips"

SELECT * FROM Products WHERE ProductName = 'Potato Chips';

• Select all rows for which CategoryID is 1 and UnitPrice is less than $5.00

SELECT * FROM Products WHERE CategoryID = 1 AND UnitPrice < 5.00;

ORDER BY clause

• Select all rows and sort in ascending order by UnitPrice

SELECT * FROM Products ORDER BY UnitPrice ASC;

• Select all rows and sort in ascending order by CategoryID and descending order by UnitPrice

SELECT * FROM Products ORDER BY CategoryID ASC, UnitPrice DESC;

• Select all rows for which the UnitPrice is greater than $5.00 and sort in ascending order by CategoryID and descending order by UnitPrice

SELECT * FROM Products WHERE UnitPrice > 5.00 
                       ORDER BY CategoryID ASC, UnitPrice DESC;

Joining tables

• Create a join that combines all combinations of rows from the tables Products and Orders

SELECT * FROM Products, Orders;

• Create a join that combines row from Products and Orders that match on ProductID. Include only the fields ProductID, ProductName and Quantity

SELECT Products.ProductID, Products.ProductName, Orders.Quantity 
       FROM Products, Orders WHERE Products.ProductID = Orders.ProductID;

INSERT statement

• Insert a new row into the Products table

INSERT INTO 
    Products (ProductID, ProductName, UnitPrice, CategoryID, LastPurchaseDate)
    VALUES ('PC101, 'Potato Chips', 1.79, 1, #02/17/2000#);

• Insert a new row into the Orders table. Remember that the OrderID is auto number

INSERT INTO 
    Orders (ProductID, CustomerID, Quantity, PurchaseDate)
    VALUES ('PC101', 'BS451', 20, #02/17/2000#);

UPDATE statement

• Change the name and price of product PC101

UPDATE Products 
    SET ProductName = 'Big Potato Chips', UnitPrice = 2.69
    WHERE ProductID = 'PC101';

• Raise the price of all products costing more than $5.00 by 10%.

UPDATE Products 
    SET UnitPrice = UnitPrice * 1.1
    WHERE UnitPrice > 5.00;

DELETE statement

• Delete all products which have not been purchased since 12/25/1999

DELETE * FROM Products WHERE LastPurchaseDate <= #12/25/1999#;

Aggregation functions

• Count the number of products costing more than $5.00.

SELECT COUNT(ProductID) AS CountOfProductID
    FROM Products
    WHERE UnitPric > 5.00;

• Compute the total quantity sold for each product

SELECT ProductID, Sum(Quantity) AS SumOfQuantity
    FROM Orders
    GROUP BY ProductID;