CSC325 2010F, Class 17: An Introduction to SQL

Overview:
* Review: Key Relational Operations.
* Leftover Questions.
* Lab.

Admin:
* I tried to get grading done this weekend and failed.  Apologies.
* Lab today!
* Are there questions on the assignment?
* Reading for Wednesday: Figure out how to access a MySQL database from PHP.

Questions on the Assignment
* What is it?
  * Finish the exercise from Friday.  That is, design a social-network
    CD database that lets people store information they might care
    about for their CD collection and learn stuff about other people's
    collections.
* What additional stuff do you expect us to do for social networking?
  * Clearly: Relate users to their collections
  * Also, possibly, things like
    * Accounts (pw, contact info, ...)
    * Friend lists?

Review: What are the key operations in a relational DBMS?
* Select rows from tables
* Insert rows into tables
* Project columns from tables
* Join tables together to form new tables (virtual or real)
* Create new tables
* Create views
* Create new databases
* Delete information from a table w/o destroying the structure
  * Rows
  * Columns
* Manage permissions
  * Ideally, this would be covered in the others, since permissiosn
    are supposed to be stored in a table.

Syntax in SQL
* Project columns from tables
  SELECT columns FROM table
* Select rows from tables
  SELECT * FROM table WHERE condition
* Hybrid
  SELECT columns FROM table WHERE condition
* Insert rows into tables
  INSERT INTO table VALUES (x,y,z);
  INSERT INTO table (columns) VALUES (x,y,z);
  ...
* Join tables together to form new tables (virtual or real)
  SELECT columns FROM table1,table2 WHERE condition
* Create new tables
  CREATE TABLE name
  (
    field_name field_type modifiers
  , field_name field_type modifiers
  , field_name field_type modifiers
  )
* Delete information from a table w/o destroying the structure
  * Rows
  DELETE FROM table WHERE condition;
* Manage permissions
  * Ideally, this would be covered in the others, since permissiosn
    are supposed to be stored in a table.
  * GRANT permisssions ON table TO ...

Questions
*  Please explain clustered indices.
* I have one question about the SELECT * example on page 93:

         SELECT titles.title_id, publishers.*
         FROM titles, publishers
         WHERE titles.pub_id = publishers.pub_id

Easier than

         SELECT title_id, publishers.name, publishers.address, publishers.primary_editor, ...
         FROM titles, publishers
         WHERE titles.pub_id = publishers.pub_id
      I don't understand why publishers.* is where it is on the first line. I thought you could only have column names between SELECT and FROM, and if there are table names they have to be attached to the column name like table_name.column_name. I think I'm not understanding something about the asterisk * character. 

Lab!