TVxzwjVR

1# COIS-3400H: Database Management
2## Lecture 2
3
4### Recap of Last Lecture
5- Table names are unique within a DB
6- Column names are unique within a table
7- Columns are typed
8- The result from a query will **always** be a table (even if it is only 1 row
9  long)
10- Your code does not interact with a database:
11  - It will either interact over a server (TCP/IP);
12  - Or it will run locally, through a DB Library
13  - These "adapters" are what interact with the database itself
14  - *Be efficient!* You don't want to use tons of memory on a local system, or
15    use tons of data over a network to get a result back.
16
17### XAMPP
18- Open source x-platform web server solution
19- Framework from *Apache Friends* that helps developers test their databases,
20  etc
21- XAMPP allows us to avoid using Loki for development purposes
22- XAMPP lets us use
23  - Apache
24  - MySQL
25  - FileZilla
26  - Mercury
27  - TomCat
28- We only care about the *Apache* and *MySQL* services
29- There may be an error with Apache and Skype both listening on port 80. Kill
30  Skype.
31
32### PHPMyAdmin
33- Developed in 1998
34- MySQL administration tool, one of the most popular ones.
35- Allows you to use SQL Queries, mess with settings, and gives us a GUI to
36  interact with
37- Once XAMPP is running Apache and MySQL, the service runs locally at
38  [localhost/phpmyadmin](localhost/phpmyadmin).
39- Different databases can be encoded with different standards
40  - Default is *latin1_swedish_ci*
41  - Another good pick is *utf8_general_ci* (Python likes this encoding)
42- We won't be using the GUI to create tables in our databases because:
43  - We like to have more control over our columns
44  - Using an SQL command offers more advanced error reporting
45  - It's no fun
46
47To interact with a DB, go to the SQL tab. Make sure you have the right DB
48selected. If you tick the "Retain query box": this will stop PHPMyAdmin from
49emptying the text-area after each query.
50
51#### Naming Conventions
52- Columns have Simple, short names. Try to stick to one word
53- Columns have `lowerCamelCase`
54  - Acronyms can be capitalized
55  - ex. name, date, carVIN, empID
56- Tables have `UpperCamelCase`
57
58### RDBMS - Relational Database Management System
59- A database which is stored with tables, which have columns and rows
60- Tables are related to one another as well
61- There are different types of RDBMS
62- Key value databases
63- Navigational databases
64
65### SQL - Structured Query Language
66- Lets you access and manipulate databases
67- ANSI (1986) and ISO (1987) standard
68  - It is regulated and standardized
69- However, there are still different "flavours" of SQL with slight differences
70  in notation and syntax
71- All flavours support things like SELECT, UPDATE, DELETE, INSERT, WHERE, etc.
72  in order to comply with ANSI/ISO.
73
74### SQL Statements
75- What perform the actual operations
76- SQL keywords are **not** case sensitive (`selet` = `SELECT`), although it is
77  convention to put keywords in all uppercase
78- Some flavours need semi-colons, some do not (PHPMyAdmin does)
79- Important commands
80  - `SELECT` - Extracts information from a DB (perhaps the most important)
81  - `UPDATE`
82  - `DELETE`
83  - `INSERT INTO` - Insert into the database
84  - `CREATE DATABASE`
85  - `ALTER DATABASE`
86  - `CREATE TABLE`
87  - `ALTER TABLE`
88  - `DROP TABLE` - Deletes a table
89  - `CREATE INDEX` - Creates an index (search_key)
90  - `DROP INDEX` - Deletes an index
91- Every SQL query returns as a Result-Set, which is a table in and of itself
92- In SQL, `*` is called a "Kleene Star"
93
94#### `SELECT` syntax:
95- `SELECT DISTINCT` can be used to select only unique values
96```sql
97SELECT column1, column2, ... FROM table_name;
98SELECT DISTINCT column1, column2, ... FROM table_name;
99```
100- We use the Primary-Key (column) to ensure uniqueness.
101  - i.e., for people, we could use their SIN, since we know it will always be
102    unique
103
104#### `WHERE` clause
105- Basically an `if` statement
106- Filters on a specified condition, rather than uniqueness
107- ex.
108```sql
109SELECT column1, column2 FROM table_name WHERE condition;
110```
111- Operators:
112  - `=` - Equal
113  - `<>` - Not equal (could be `!=` in other flavours)
114  - `<`
115  - `>`
116  - `<=`
117  - `>=`
118- Example:
119  ```sql
120  SELECT * FROM employees where name = Alaadin;
121  -- Corrected, this is:
122  SELECT * FROM employees WHERE name = 'Alaadin';
123  ```
124  - `where` -> `WHERE`, follow conventions
125  - Strings should be in quotes
126- `WHERE` can use `AND` and `OR` and `NOT`
127- You can use brackets when compounding statements, but we try not to use
128  complicated statements
129
130#### `ORDER BY` Keyword
131- Sorts the result-set in ascending or descending order
132- Use `ASC` or `DESC`
133```sql
134SELECT c1, c2 FROM table1 ORDER BY c1, c2, ASC;
135```
136
137#### SQL `NULL` Values
138- A field with a `NULL` value is a field with no value
139- Does **not** mean "no value" -- it could mean something in some cases
140  - This is important when we get to the `JOIN` operator
141- White-space is actually data in an SQL table
142- We can test for `NULL` values
143- `IS NULL` and `IS NOT NULL`
144```sql
145SELECT c1 FROM table1 WHERE c1 IS NULL;
146SELECT c1, c2 FROM table1 WHERE c2 IS NOT NULL;
147```
148- This is useful to check integrity of data
149
150#### `UPDATE` Statement
151- Used to modify existing data records in a table
152```sql
153UPDATE table1 SET c1 = data WHERE condition;
154```
155- Not using a `WHERE` condition will be catastrophic!
156
157#### SQL `TOP`, `LIMIT`, or `ROWNUM` Clause
158- `SELECT TOP` is used to specify the number of records to return
159  - Useful with large tables of thousands of records
160  - Lets you only see the top ten
161- `LIMIT` and `ROWNUM` work similarly
162```sql
163SELECT col1 FROM table WHERE condition LIMIT 12;
164```
165
166#### `MIN()` and `MAX()` Functions
167- Returns the smallest and largest value of the specific column
168```sql
169SELECT MIN(column_name) FROM table WHERE condition;
170SELECT MAX(column_name) FROM table WHERE condition;
171```
172
173#### `COUNT()`, `AVG()` and `SUM()`
174- `COUNT()` returns the number of rows that match the condition
175- `AVG()` gives the average value of a numeric column
176- `SUM()` sums numeric columns
177
178#### `LIKE` Operator
179- Used in a `WHERE` clause to search for a specified pattern
180- Like a RegEx
181- `%` - Zero, one, or more character wildcard
182- `_` - Placeholder for a single character
183- Examples
184  ```sql
185  WHERE custName LIKE 'a%'; --Start with 'a'
186  WHERE custName LIKE '%a'; --End with 'a'
187  WHERE custName LIKE '%or%'; --Has 'or' anywhere in it
188  WHERE custName LIKE '_r%'; --Has 'r' in the second position
189  WHERE custName LIKE 'a_%_%'; --Starts with 'a' and is at least 3 characters
190  WHERE custName LIKE 'a%o'; --Starts with 'a' and ends with 'o'
191  ```
192
193#### `IN` Operator
194- Allows you to specify multiple values in a `WHERE` clause
195- `IN` is shorthand for multiple `OR`s.
196- Example:
197```sql
198SELECT * FROM countries WHERE name = 'Germany' OR name = 'Algeria';
199-- Becomes:
200SELECT * FROM countries WHERE name IN ('Germany', 'Algeria');
201```
202
203#### `BETWEEN` Operator
204- `BETWEEN` is inclusive
205- "45 to 100"
206  - English: 46, 47, ..., 99
207  - SQL: 45, 46, ..., 100
208
209#### SQL Aliases
210- Used to give a table or column a temporary name for the duration of the query
211- Useful for long queries
212- Example:
213```sql
214SELECT col1 AS alias FROM table;
215--could be handy for
216SELECT name AS n FROM table WHERE n in ('Germany', 'Algeria', 'Sweden');
217```
218
219#### `HAVING` Operator
220- Useful for replacing `AND` and `OR` in some cases
221```sql
222SELECT c1 FROM table1 WHERE cond1 GROUP BY c1 HAVING cond2 ORDER BY c1
223```
224
225#### `EXISTS` Operators
226- Tests the existence of any record in a subquery
227- Returns true if the subquery returns one or more records
228- Syntax:
229```sql
230SELECT colName
231FROM TableName
232WHERE EXISTS
233(SELECT colName FROM TableName WHERE condition);
234```
235```sql
236SELECT supplierName
237FROM Suppliers
238WHERE EXISTS
239(SELECT productName
240  FROM Products
241  WHERE supplierID = Suppliers.supplierID AND price > 20);
242```
243
244#### `INSERT INTO` Statement
245- Copies data from one table into another
246- Cannot copy in somewhere if the records already exists
247  - Use `UPDATE` if the record already exists
248
249#### SQL Comments
250- `-- words` for single-line commends
251- `/* words */` for multi-line comments