Queries

You query or retrieve data from a database with the SQL SELECT statement. The basic query operations in a relational system are selection, projection, and join. The SELECT statement implements all of them.

A projection is a subset of the columns in a table. A restriction (also called selection) is a subset of the rows in a table, based on some conditions.

For example, the following SELECT statement retrieves the names and prices of all products that cost more than fifteen dollars:

SELECT Name, UniPrice
  FROM Products
WHERE UnitPrice > 15 

This query uses both a restriction (WHERE unit_price > 15) and a projection (SELECT name, unit_price)

A join links the rows in two or more tables by comparing the values in key columns and returning rows that have matching values. For example, this query joins the SalesOrderItems, Products, Employees, and SalesOrders tables to calculate sales totals and revenue:

SELECT Products.ID AS 'Product Code', 
  Products.Name AS Item, 
  Products.Description AS Style, 
  SUM(SalesOrderItems.Quantity) AS Sales,  
    Products.UnitPrice, 
  SUM(SalesOrderItems.Quantity * Products.UnitPrice) 
    AS Revenue FROM Employees
  JOIN SalesOrders ON SalesOrders.SalesRepresentative =
    Employees.EmployeeID 
  JOIN SalesOrderItems ON SalesOrderItems.ID =
    SalesOrders.ID
  JOIN Products ON Products.ID  = 
    SalesOrderItems.ProductID 
GROUP BY Products.ID, Products.Description,
  Products.Name, Products.UnitPrice
ORDER BY Sales