Empower Day 12: Agenda

Time	Activity
08:30 AM	SQL Relational Design Review
	EXERCISE: Group design another database
09:00 AM	SQL Review
	SQL Basics SELECT...FROM...WHERE INNER JOIN, any number of tables LEFT JOIN, any number of tables GROUP BY, with or without aggregate function usage CASE Useful predefined functions For Strings: SUBSTRING, CHARINDEX, LEN, LEFT, RIGHT For Dates: DATEADD, DATEDIFF, FORMAT, GETDATE, GETUTCDATE, DATEPART/YEAR/MONTH/DAY, DATENAME COALESCE, ISNULL, CAST, CONVERT Subqueries Derived Tables (looks like a subquery you select from) CLUSTERED vs. NONCLUSTERED Index Views, Functions and Triggers Stored Procedures EXERCISE: Write a stored procedure
10:30 AM	PostgreSQL
	PgAdmin Usage ; are required IF...THEN...ELSE...END IF; instead of IF...BEGIN...END Coding style: all lower case, id as surrogate key name, use _ in compound names Name delimiter is "", not [] CONCAT or \|\|, not + for string concatenation Variables have no special prefix but must be declared before the BEGIN in a "function" definition No SET keyword but you must use := instead of = to assign a variable value Cursors are different - FOR...IN syntax with a RECORD data type Data Types: "character varying" instead of varchar, true or false instead of 1 or 0 Stored Procedures are called "Functions", temp tables are declared differently (see real world examples in the subdirectory) Views work the same The concept of a functions and stored procedure are one in the same here Triggers exist and work the same Array and CSV processing is very powerful and developer friendly here, a multitude of pre-defined functions exist to make developers' lives easier CONCAT, COALESCE, LIMIT SERIAL or SEQUENCE (Oracle does it this way) or IDENTITY as of v10, not IDENTITY BACKUP and RESTORE EXERCISE: Query Sakila on Postgres
11:30 AM	MySQL
	MySQL Workbench Usage ; are required Name delimiter is ``, not [] Data Types: true or false instead of 1 or 0 Stored Procedures, parameters, variables and temp tables Views Functions Triggers AUTO_INCREMENT, not IDENTITY BACKUP and RESTORE EXERCISE: Query Sakila on MySQL
Lunch Break (12-1PM)
1:00 PM	EXERCISE: Independent Database Creation Practice
	Design Implementation Deploy to AWS, Azure or Google Cloud
Quiz: Relational Databases & SQL

Time

Activity

08:30 AM

SQL Relational Design Review

EXERCISE: Group design another database

09:00 AM

SQL Review

SQL Basics
- SELECT...FROM...WHERE
- INNER JOIN, any number of tables
- LEFT JOIN, any number of tables
- GROUP BY, with or without aggregate function usage
- CASE
Useful predefined functions
- For Strings: SUBSTRING, CHARINDEX, LEN, LEFT, RIGHT
- For Dates: DATEADD, DATEDIFF, FORMAT, GETDATE, GETUTCDATE, DATEPART/YEAR/MONTH/DAY, DATENAME
- COALESCE, ISNULL, CAST, CONVERT
Subqueries
Derived Tables (looks like a subquery you select from)
CLUSTERED vs. NONCLUSTERED Index
Views, Functions and Triggers
Stored Procedures
EXERCISE: Write a stored procedure

10:30 AM

PostgreSQL

PgAdmin Usage
; are required
IF...THEN...ELSE...END IF; instead of IF...BEGIN...END
Coding style: all lower case, id as surrogate key name, use _ in compound names
Name delimiter is "", not []
CONCAT or ||, not + for string concatenation
Variables have no special prefix but must be declared before the BEGIN in a "function" definition
No SET keyword but you must use := instead of = to assign a variable value
Cursors are different - FOR...IN syntax with a RECORD data type
Data Types: "character varying" instead of varchar, true or false instead of 1 or 0
Stored Procedures are called "Functions", temp tables are declared differently (see real world examples in the subdirectory)
Views work the same
The concept of a functions and stored procedure are one in the same here
Triggers exist and work the same
Array and CSV processing is very powerful and developer friendly here, a multitude of pre-defined functions exist to make developers' lives easier
CONCAT, COALESCE, LIMIT
SERIAL or SEQUENCE (Oracle does it this way) or IDENTITY as of v10, not IDENTITY
BACKUP and RESTORE
EXERCISE: Query Sakila on Postgres

11:30 AM

MySQL

MySQL Workbench Usage
; are required
Name delimiter is ``, not []
Data Types: true or false instead of 1 or 0
Stored Procedures, parameters, variables and temp tables
Views
Functions
Triggers
AUTO_INCREMENT, not IDENTITY
BACKUP and RESTORE
EXERCISE: Query Sakila on MySQL

Lunch Break (12-1PM)

1:00 PM

EXERCISE: Independent Database Creation Practice

Design
Implementation
Deploy to AWS, Azure or Google Cloud

Quiz: Relational Databases & SQL