Extra slides, week 9

CSE 190 M (Web Programming) Spring 2008

University of Washington

References: SQL syntax reference, w3schools tutorial

Except where otherwise noted, the contents of this presentation are © Copyright 2008 Marty Stepp, Jessica Miller, and Amit Levy, and are licensed under the Creative Commons Attribution 2.5 License.

Entities and relationships

• an entity is a record in a table in the database
• a relationship is a connection between two or more entities
• database designers often draw ER diagrams like the above to represent the entities and relationships in their databases

The SQL INSERT statement

INSERT INTO table
VALUES (value, value, ..., value);

INSERT INTO Student 
VALUES (789, "Nelson", "muntz@fox.com");

---

• adds a new row to the given table
• How would we record that Nelson took CSE 190M and got a D+ in it?

The SQL UPDATE statement

UPDATE table
SET column = value,
    ...,
    column = value
WHERE column = value;

UPDATE Student
SET    email = "lisasimpson@gmail.com"
WHERE  SID = 888;

---

• modifies an existing row(s) in a table

The SQL CREATE TABLE statement

CREATE TABLE name (
    columnName type constraints,
    ...
    columnName type constraints
);

CREATE TABLE Student (
    SID INTEGER UNSIGNED NOT NULL PRIMARY KEY,
    name VARCHAR(20),
    email VARCHAR(32)
);

---

• adds/deletes an entire new table from this database
• you can add constraints such as NOT NULL for a field that cannot be blank or PRIMARY KEY for a column that must be unique for every row
• related commands: CREATE DATABASE, DROP TABLE

SQL data types

• BOOLEAN
• INTEGER
• DOUBLE
• VARCHAR : a string
• DATE, TIME, DATETIME
• BLOB : binary data
• quick reference