# COIS-3400H: Database Management
## Lecture 2

### Recap of Last Lecture
- Table names are unique within a DB
- Column names are unique within a table
- Columns are typed
- The result from a query will **always** be a table (even if it is only 1 row
  long)
- Your code does not interact with a database:
  - It will either interact over a server (TCP/IP);
  - Or it will run locally, through a DB Library
  - These "adapters" are what interact with the database itself
  - *Be efficient!* You don't want to use tons of memory on a local system, or
    use tons of data over a network to get a result back.

### XAMPP
- Open source x-platform web server solution
- Framework from *Apache Friends* that helps developers test their databases,
  etc
- XAMPP allows us to avoid using Loki for development purposes
- XAMPP lets us use
  - Apache
  - MySQL
  - FileZilla
  - Mercury
  - TomCat
- We only care about the *Apache* and *MySQL* services
- There may be an error with Apache and Skype both listening on port 80. Kill
  Skype.

### PHPMyAdmin
- Developed in 1998
- MySQL administration tool, one of the most popular ones.
- Allows you to use SQL Queries, mess with settings, and gives us a GUI to
  interact with
- Once XAMPP is running Apache and MySQL, the service runs locally at
  [localhost/phpmyadmin](localhost/phpmyadmin).
- Different databases can be encoded with different standards
  - Default is *latin1_swedish_ci*
  - Another good pick is *utf8_general_ci* (Python likes this encoding)
- We won't be using the GUI to create tables in our databases because:
  - We like to have more control over our columns
  - Using an SQL command offers more advanced error reporting
  - It's no fun

To interact with a DB, go to the SQL tab. Make sure you have the right DB
selected. If you tick the "Retain query box": this will stop PHPMyAdmin from
emptying the text-area after each query.

#### Naming Conventions
- Columns have Simple, short names. Try to stick to one word
- Columns have `lowerCamelCase`
  - Acronyms can be capitalized
  - ex. name, date, carVIN, empID
- Tables have `UpperCamelCase`

### RDBMS - Relational Database Management System
- A database which is stored with tables, which have columns and rows
- Tables are related to one another as well
- There are different types of RDBMS
- Key value databases
- Navigational databases

### SQL - Structured Query Language
- Lets you access and manipulate databases
- ANSI (1986) and ISO (1987) standard
  - It is regulated and standardized
- However, there are still different "flavours" of SQL with slight differences
  in notation and syntax
- All flavours support things like SELECT, UPDATE, DELETE, INSERT, WHERE, etc.
  in order to comply with ANSI/ISO.

### SQL Statements
- What perform the actual operations
- SQL keywords are **not** case sensitive (`selet` = `SELECT`), although it is
  convention to put keywords in all uppercase
- Some flavours need semi-colons, some do not (PHPMyAdmin does)
- Important commands
  - `SELECT` - Extracts information from a DB (perhaps the most important)
  - `UPDATE`
  - `DELETE`
  - `INSERT INTO` - Insert into the database
  - `CREATE DATABASE`
  - `ALTER DATABASE`
  - `CREATE TABLE`
  - `ALTER TABLE`
  - `DROP TABLE` - Deletes a table
  - `CREATE INDEX` - Creates an index (search_key)
  - `DROP INDEX` - Deletes an index
- Every SQL query returns as a Result-Set, which is a table in and of itself
- In SQL, `*` is called a "Kleene Star"

#### `SELECT` syntax:
- `SELECT DISTINCT` can be used to select only unique values
```sql
SELECT column1, column2, ... FROM table_name;
SELECT DISTINCT column1, column2, ... FROM table_name;
```
- We use the Primary-Key (column) to ensure uniqueness.
  - i.e., for people, we could use their SIN, since we know it will always be
    unique

#### `WHERE` clause
- Basically an `if` statement
- Filters on a specified condition, rather than uniqueness
- ex.
```sql
SELECT column1, column2 FROM table_name WHERE condition;
```
- Operators:
  - `=` - Equal
  - `<>` - Not equal (could be `!=` in other flavours)
  - `<`
  - `>`
  - `<=`
  - `>=`
- Example:
  ```sql
  SELECT * FROM employees where name = Alaadin;
  -- Corrected, this is:
  SELECT * FROM employees WHERE name = 'Alaadin';
  ```
  - `where` -> `WHERE`, follow conventions
  - Strings should be in quotes
- `WHERE` can use `AND` and `OR` and `NOT`
- You can use brackets when compounding statements, but we try not to use
  complicated statements

#### `ORDER BY` Keyword
- Sorts the result-set in ascending or descending order
- Use `ASC` or `DESC`
```sql
SELECT c1, c2 FROM table1 ORDER BY c1, c2, ASC;
```

#### SQL `NULL` Values
- A field with a `NULL` value is a field with no value
- Does **not** mean "no value" -- it could mean something in some cases
  - This is important when we get to the `JOIN` operator
- White-space is actually data in an SQL table
- We can test for `NULL` values
- `IS NULL` and `IS NOT NULL`
```sql
SELECT c1 FROM table1 WHERE c1 IS NULL;
SELECT c1, c2 FROM table1 WHERE c2 IS NOT NULL;
```
- This is useful to check integrity of data

#### `UPDATE` Statement
- Used to modify existing data records in a table
```sql
UPDATE table1 SET c1 = data WHERE condition;
```
- Not using a `WHERE` condition will be catastrophic!

#### SQL `TOP`, `LIMIT`, or `ROWNUM` Clause
- `SELECT TOP` is used to specify the number of records to return
  - Useful with large tables of thousands of records
  - Lets you only see the top ten
- `LIMIT` and `ROWNUM` work similarly
```sql
SELECT col1 FROM table WHERE condition LIMIT 12;
```

#### `MIN()` and `MAX()` Functions
- Returns the smallest and largest value of the specific column
```sql
SELECT MIN(column_name) FROM table WHERE condition;
SELECT MAX(column_name) FROM table WHERE condition;
```

#### `COUNT()`, `AVG()` and `SUM()`
- `COUNT()` returns the number of rows that match the condition
- `AVG()` gives the average value of a numeric column
- `SUM()` sums numeric columns

#### `LIKE` Operator
- Used in a `WHERE` clause to search for a specified pattern
- Like a RegEx
- `%` - Zero, one, or more character wildcard
- `_` - Placeholder for a single character
- Examples
  ```sql
  WHERE custName LIKE 'a%'; --Start with 'a'
  WHERE custName LIKE '%a'; --End with 'a'
  WHERE custName LIKE '%or%'; --Has 'or' anywhere in it
  WHERE custName LIKE '_r%'; --Has 'r' in the second position
  WHERE custName LIKE 'a_%_%'; --Starts with 'a' and is at least 3 characters
  WHERE custName LIKE 'a%o'; --Starts with 'a' and ends with 'o'
  ```

#### `IN` Operator
- Allows you to specify multiple values in a `WHERE` clause
- `IN` is shorthand for multiple `OR`s.
- Example:
```sql
SELECT * FROM countries WHERE name = 'Germany' OR name = 'Algeria';
-- Becomes:
SELECT * FROM countries WHERE name IN ('Germany', 'Algeria');
```

#### `BETWEEN` Operator
- `BETWEEN` is inclusive
- "45 to 100"
  - English: 46, 47, ..., 99
  - SQL: 45, 46, ..., 100

#### SQL Aliases
- Used to give a table or column a temporary name for the duration of the query
- Useful for long queries
- Example:
```sql
SELECT col1 AS alias FROM table;
--could be handy for
SELECT name AS n FROM table WHERE n in ('Germany', 'Algeria', 'Sweden');
```

#### `HAVING` Operator
- Useful for replacing `AND` and `OR` in some cases
```sql
SELECT c1 FROM table1 WHERE cond1 GROUP BY c1 HAVING cond2 ORDER BY c1
```

#### `EXISTS` Operators
- Tests the existence of any record in a subquery
- Returns true if the subquery returns one or more records
- Syntax:
```sql
SELECT colName
FROM TableName
WHERE EXISTS
(SELECT colName FROM TableName WHERE condition);
```
```sql
SELECT supplierName
FROM Suppliers
WHERE EXISTS
(SELECT productName
  FROM Products
  WHERE supplierID = Suppliers.supplierID AND price > 20);
```

#### `INSERT INTO` Statement
- Copies data from one table into another
- Cannot copy in somewhere if the records already exists
  - Use `UPDATE` if the record already exists

#### SQL Comments
- `-- words` for single-line commends
- `/* words */` for multi-line comments