70 likes | 194 Views
Web Database Programming Week 9. DB Administration – Multiple Tables. 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 N D
Web Database Programming Week 9 DB Administration – Multiple Tables
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
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
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;
Aggregation Functions • Count() • Sum() • Min() • Max() • Avg() SELECT city, COUNT(*) from customer GROUP BY city;
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
Additional SQL Criteria for WHERE Clause • Direct comparison: =, <>, <=, <, >, >= • 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 >= ‘Jones’) • Set membership: WHERE LastName In (‘Jones’, ‘Smith’,‘$formValue’) • Like: WHERE LastName LIKE ‘Johns[oe]n’WHERE LastName LIKE ‘Ben%’