SQL statements
 
Statement Type Statement Keyword Purpose
Data definition CREATE

INDEX

VIEW

 Define a table, index, or view
Data manipulation SELECT

UPDATE

DELECT

INSERT

 Retrieve contents of tables

Modify rows

Remove rows

Add rows
Data control COMMIT

ROLLBACK

GRANT
REVOKE
CHECK

 Complete transaction

Undo transaction

Add access rights

Remove access rights

Define integrity constraint

SELECT statement

SELECT <list of columns or expressions involving columns>

FROM <list of tables and join operations>

WHERE <list of row conditions>

GROUP BY <list of columns>

HAVING <list of group conditions>

ORDER BY <list of columns>

Relational algebra
 
Operator Purpose SQL example
Restrict Extract rows that satisfy a specified condition SELECT * FROM Supplier WHERE City="Madison"
Project Extract specified columns SELECT P#, Color FROM Part
Natural Join Returns a table consisting of all possible rows that are a combination of two rows, one from each of two input tables, such that the two rows have a common value for the common column(s) of the two input tables. Only one of the matching columns is retained in the result table.  SELECT * FROM Supplier INNER JOIN SP ON Supplier.S# = SP.S#
Full Outer Join Produce the matching rows as well as the non-matching rows from both tables SELECT * FROM Supplier FULL JOIN SP ON Supplier.S# = SP.S#
One-sided Outer Join Produce the matching rows as well as the non-matching rows from the designated input table SELECT * FROM Supplier LEFT JOIN SP ON Supplier.S# = SP.S#

SELECT * FROM SP RIGHT JOIN Supplier ON Supplier.S# = SP.S#

 

Examples on query formulation using SELECT statement:

Supplier
 
S# Sname City
S1 Auto Parts Madison
S2 Reliable Parts Lexington

Part
 
P# Pname Color Unit Price
P1 Front Door Panel Red $500
P2 Front Door Panel Blue $400
P3 Back Door Panel Red $700

SP
 
S# P#
S1 P1
S1 P3

1.  List all suppliers from Madison

     
    SELECT * FROM Supplier WHERE City="Madison"
     
    S# Sname City
    S1 Auto Parts Madison
2.  List the part number and color of all parts
     
    SELECT P#, Color FROM Part
     
    P# Color
    P1 Red
    P2 Blue
    P3 Red


3.  List the part number of parts supplied by all suppliers. For each part, list the supplier's number, name, and city.

     
    SELECT * FROM Supplier INNER JOIN SP ON Supplier.S# = SP.S#
     
    S# Sname City P#
    S1 Auto Parts Madison P1
    S1 Auto Parts Madison P3
4.  List the part number of parts supplied by all suppliers. For each part, list the supplier's number, name, and city. Include parts that have no supplier.
     
    SELECT Supplier.S#, Supplier.Sname, Supplier.City, SP.P# FROM (Supplier INNER JOIN SP ON Supplier.S# = SP.S# ) RIGHT JOIN Part ON SP.P# = Part.P#
     
    S# Sname City P#
    S1 Auto Parts Madison P1
    S1 Auto Parts Madison P3
          P2
5.  List the part number of parts supplied by all suppliers. For each part, list the supplier's number, name, and city. Include suppliers who supply no part.
     
    SELECT * FROM Supplier LEFT JOIN SP ON Supplier.S# = SP.S#

    Or

    SELECT * FROM SP RIGHT JOIN Supplier ON Supplier.S# = SP.S#
     
    S# Sname City P#
    S1 Auto Parts Madison P1
    S1 Auto Parts Madison P3
    S2 Reliable Parts Lexington  

     

6.  List all parts in ascending order of color
     
    SELECT * FROM Parts ORDER BY color
     
    P# Pname Color Unit Price
    P2 Front Door Panel Blue $400
    P1 Front Door Panel Red $500
    P3 Back Door Panel Red $700
7.  Summarize the average unit price of parts by color.
     
    SELECT color, AVG(unit price) AS AvgPrice FROM Parts GROUP BY color
     
    Color AvgPrice
    Red $600
    Blue $400
8.  Summarize the average unit price of parts by color. Only include parts with an average unit price above $500.

SELECT color, AVG(unit price) AS AvgPrice FROM Parts GROUP BY color HAVING AVG(unit price) > 500
 
Color AvgPrice
Red $600