SQL Basics

These examples use the following tables.

InStock Table: keyfield = ID

Type	NumberLeft	Variety	Colour	ID	UnitCost
Apple	4	Granny Smith	Green	1	£0.10
Apple	56	Ida Red	Red	2	£0.23
Apple	34	Macintosh	Red	3	£0.05
Orange	23	Naval	Orange	4	£0.20
Banana	234	Ripe	Yellow	5	£0.03
Pear	23	Bosh	Yellow	6	£0.29
Mango	23	Yummy	Green	7	£0.60

Customer Table: keyfield = ID

First	Last	ID	Address	Prov	City	PCode	CreditLimit
Andrew	Wiles	1	234 Queen	ON	Brampton	B3R 4R5	£100.00
Alan	Turing	2	235 Queen	ON	Brampton	B3R 4R5	£123.00
Pierre	de Fermat	3	56 Main	ON	Kincardine	N0G 2G0	£2.00

Purchase Table: foreign key=fruitID and customerID

FruitID	CustomerID	NumberBought
1	1	2
5	2	3
1	3	4
6	2	4
3	3	45
2	3	56

1. Print everything in a table: the Select * Clause

Select * from Purchase

Select * from InStock

2. Print selected fields from a table: the Select Clause

Select First, Last 
from Customer

first	last
Andrew	Wiles
Alan	Turing
Pierre	de Fermat

Select First as "First Name", Last as "Last Name" 
from Customer


"First Name"	"Last Name"
Andrew	Wiles
Alan	Turing
Pierre	de Fermat

3. Restrictions: the where clause

Select * 
from Purchase 
where CustomerID = 3


FruitID	CustomerID	NumberBought
1	3	4
2	3	56
3	3	45

Select * from Customer 
where City = "Brampton" (Strings in Quotes)

Select * from Customer 
where CreditLimit >10 (Can also do greater thans or less thans)

Select * from Customer 
where City = "Brampton" and CreditLimit >10 (Can have more than one condition)

4. Make a "join" between tables

(a) A Cross Product

This is all combinations of records from both tables. For example, we have 3 customers in the customer table and 6 records in our purchase table. That means that each customer can be paired with 6 purchases, so a cross product of the two tables will have 18 records.

SELECT * from Customer, Purchase

First	Last	ID	Address	Prov	City	PCode	CreditLimit	FruitID	CustomerID	NumberBought
Andrew	Wiles	1	234 Queen	ON	Brampton	B3R 4R5	£100.00	1	1	2
Alan	Turing	2	235 Queen	ON	Brampton	B3R 4R5	£123.00	1	1	2
Pierre	de Fermat	3	56 Main	ON	Kincardine	N0G 2G0	£2.00	1	1	2
Andrew	Wiles	1	234 Queen	ON	Brampton	B3R 4R5	£100.00	1	3	4
Alan	Turing	2	235 Queen	ON	Brampton	B3R 4R5	£123.00	1	3	4
Pierre	de Fermat	3	56 Main	ON	Kincardine	N0G 2G0	£2.00	1	3	4
Andrew	Wiles	1	234 Queen	ON	Brampton	B3R 4R5	£100.00	2	3	56
Alan	Turing	2	235 Queen	ON	Brampton	B3R 4R5	£123.00	2	3	56
Pierre	de Fermat	3	56 Main	ON	Kincardine	N0G 2G0	£2.00	2	3	56
Andrew	Wiles	1	234 Queen	ON	Brampton	B3R 4R5	£100.00	3	3	45
Alan	Turing	2	235 Queen	ON	Brampton	B3R 4R5	£123.00	3	3	45
Pierre	de Fermat	3	56 Main	ON	Kincardine	N0G 2G0	£2.00	3	3	45
Andrew	Wiles	1	234 Queen	ON	Brampton	B3R 4R5	£100.00	5	2	3
Alan	Turing	2	235 Queen	ON	Brampton	B3R 4R5	£123.00	5	2	3
Pierre	de Fermat	3	56 Main	ON	Kincardine	N0G 2G0	£2.00	5	2	3
Andrew	Wiles	1	234 Queen	ON	Brampton	B3R 4R5	£100.00	6	2	4
Alan	Turing	2	235 Queen	ON	Brampton	B3R 4R5	£123.00	6	2	4
Pierre	de Fermat	3	56 Main	ON	Kincardine	N0G 2G0	£2.00	6	2	4

(b) A Join

A join is a cross product with some of the records thrown out. You always make the keyfield of one table equal to the foreign key in the other table.

Suppose that you want to link all of the purchases with the names of the customers.

Select * from Customer, Purchase 
where CustomerID = ID


First	Last	ID	Address	Prov	City	PCode	CreditLimit	FruitID	CustomerID	NumberBought
Andrew	Wiles	1	234 Queen	ON	Brampton	B3R 4R5	£100.00	1	1	2
Alan	Turing	2	235 Queen	ON	Brampton	B3R 4R5	£123.00	5	2	3
Alan	Turing	2	235 Queen	ON	Brampton	B3R 4R5	£123.00	6	2	4
Pierre	de Fermat	3	56 Main	ON	Kincardine	N0G 2G0	£2.00	1	3	4
Pierre	de Fermat	3	56 Main	ON	Kincardine	N0G 2G0	£2.00	2	3	56
Pierre	de Fermat	3	56 Main	ON	Kincardine	N0G 2G0	£2.00	3	3	45

Suppose you want the names of the customers and of the fruit that they bought

Select Last, Variety, Type, NumberBought     (Fields we want)
from InStock, Customer, Purchase             (Tables containing the fields)
where CustomerID=Customer.ID                 (First foreign-key field link for first table pair)
and FruitID=InStock.ID                       (Second foreign-key field link for second table pair)


Last	Variety	Type	NumberBought
Wiles	Granny Smith	Apple	2
de Fermat	Granny Smith	Apple	4
de Fermat	Ida Red	Apple	56
de Fermat	Macintosh	Apple	45
Turing	Ripe	Banana	3
Turing	Bosh	Pear	4

5. Aggregates (sum and count)

This sums all of the entries in the number bought field (the total number of fruit sold that day)

Select sum(numberbought) 
from Purchase

This counts all of the entries in the table (the number of purchases that where made)

Select count(*) 
from Purchase

This sums the number of pieces of fruit bought by customer #3:

Select sum(numberbought) 
from Purchase 
where CustomerID = 3

6. SubTotals: The Group By Clause

Decide how you want to group your responses. That field needs to be selected and put in the group by clause.

This counts the number of pieces of fruit bought by each customer.

Select customerID, sum(numberbought) 
from Purchase
group by customerID


customerID	Expr1001
1	2
2	7
3	105

If you want to print the name of the customer with the totals, you could write:

Select Last, sum(numberbought) as "Total Fruit"
from Purchase, Customer
where CustomerID=ID
group by Last


Last	"Total Fruit"
de Fermat	105
Turing	7
Wiles	2