Web Database Programming Week 9

1
Web Database Programming Week 9

• DB Administration
• Multiple Tables

2
Table Relationships

• One to many
• Use foreign key at the Many side of the
  relationship
• E.g. pets species
• Many to many
• Use an extra relational table
• This table contains foreign keys for Both sides
  of relationship
• E.g. pets ownership table captures relationship
  between pets owners

3
Display Table w/ Foreign Keys (FKs)

• Need to join the table(s) containing FKs with the
  table(s) providing FKs
• Must specify key relationships in join
• SELECT ownership.id,
• owners.lastname,
• pets.name
• FROM ownership, owners, pets
• WHERE ownership.ownerId owners.id
• AND ownership.petId pets.id

4
Rows Aggregation

• GROUP BY clause
• Rows with the same value will be grouped together
• Enable the use of aggregation functions
• SELECT city from customer
• GROUP BY city

5
Aggregation Functions

• Count()
• Sum()
• Min()
• Max()
• Avg()
• SELECT city, COUNT() from customer
• GROUP BY city

6
Example

• Pet Ownership Administration
• Create an ownership
• Update an ownership
• Delete an ownership
• Summary report
• E.g. a table listing the number of pets owned by
  each owner

7
Additional SQL Criteria for WHERE Clause

• Direct comparison , ltgt, lt, lt, gt, gt
• Existence IS NULL, IS NOT NULL
• Between WHERE aNumber BETWEEN 234 AND 999
• Combining AND, ORWHERE (LastName Smith AND
  FirstName Jack) OR (LastName Jones AND
  FirstName Kim)
• Negation WHERE NOT( LastName gt Jones)
• Set membership WHERE LastName In (Jones,
  Smith,formValue)
• Like WHERE LastName LIKE JohnsoenWHERE
  LastName LIKE Ben