Intermediate SQL - July 5

[linamnt]

We will be diving a bit more into SQL and databases using what we learned in our Intro SQL lesson and the kinds of more complex queries we can make.

Where: Rm 408 on the 4th floor of Burton Tower in the McLennan Physics building When: Wednesday, July 5, 6:10-7pm Instructor: Brian Sutherland What to bring: A laptop with browser & internet

[mbonsma]

Link to the cheat sheet from last time: https://bmc.med.utoronto.ca/bmcwiki/lib/exe/fetch.php/technologies:mysql_cheat_sheet.pdf

[QuLogic]

That link returns Forbidden; maybe only accessible on the campus network?

[bksutherland]

SQL Review - SQL stands for Structured Query Language, an ISO standard

Name three free SQL databases SQLite MySQL (Community) PostGreSQL

Review of relations, 1:1 1:many, many to many 1:1 birthdays; one person-one birthday ONE table, two different fields 1:many Toronto beaches ecoli levels; one beach has many measurements; TWO tables: beaches and measurements where measurements references the keys of beaches many:many car rental agency; THREE tables: cars, people and a junction table to link them called ‘rentals’ which references both cars and people, and the rental date, mileage, etc.

Generally the purpose of relations is to only have one copy of each bit of information to update, for efficiency and consistency. (However, disk space and computing power is cheap)

Review database manager we are using, such as PhpMyAdmin

Review Entity Relationship diagrams

Review main types of queries Cheat Sheet SELECT INSERT UPDATE DELETE Select is a kind of ‘search’ based on indexes Wildcards in select: * for fields % for matches Always use LIMIT when doing updates, in case you’ve flubbed the matching criteria, you want to limit the damage to the records.

Creating Joins

List of all car renter-contracts SELECT p.first_name, p.last_name, c.id AS id_contract FROM people AS p INNER JOIN contracts AS c ON c.id_people = p.id

LEFT JOIN (include users who have never rented) List of all car renters in the system SELECT p.first_name, p.last_name, c.id AS id_contract FROM people AS p LEFT JOIN contracts AS c ON c.id_people = p.id

Copy or reorganize part of a recordset to give to someone else (say you must keep the artist (or research participants) anonymous, for a student assistant) INSERT INTO (SELECT … from) INSERT INTO user00.jack_titles_anon (frequency, title) SELECT frequency, title FROM examples.jack_titles WHERE artist LIKE 'Rush%'

Boolean logic example - list apples AND oranges (SELECT artist, frequency, title FROM examples.jack_titles WHERE artist LIKE 'Rush%' ORDER BY title ASC) UNION (SELECT artist, frequency, title FROM examples.jack_titles WHERE artist LIKE 'Supertramp%' ORDER BY title ASC)

Moving databases around (assuming you have terminal access) (source machine, use mysqldump) mysqldump -u root -p examples > /var/www/examples.sql;

(destination machine, create examples database then import data into it) mysql -u root -p examples < examples.sql;

ACID reliability of SQL vs high speed performance A: atomicity (all-or-nothing, preserve sequence). Suppose you define a transaction that contains an UPDATE, an INSERT, and a DELETE. Bank transaction? The whole deal, or else roll back.

C: consistency (prevent deleting customer before invoices, to avoid 'orphan' invoices) (database rejects buggy application logic)

I: isolation (Delete customer before customer's invoices, meanwhile second user trying to update invoices) Blocking occurs -- can not remove until - second user would discover user is deleted before updating). Similar for eBay inventory vs bidding.

D: durability (strategy for backup, involving hardware components and archiving - UPS, replication, etc.)