Data Base Design

• SQL
• MYSQL
• POST Gre SQL

Overview of SQL Server

• Mac OS – How to set up SQL Server Database on a Mac Computer using Docker
• Solutions to the SELECT Challenges
• SELECT DISTINCT Statement
• SELECT DISTINCT Challenges
• Solutions to the SELECT DISTINCT Challenges
• Row Ordering – ORDER BY
• Filtering Data with TOP
• ORDER BY Challenges
• Solutions to the ORDER BY Challenges

WHERE Clause – Three-valued Logic

• Replacing Nulls in Queries
• WHERE Clause – Comparison Operators
• WHERE Clause Challenges
• Solutions to the WHERE Clause Challenges
• Introduction to Collations and Pattern Matching

Pattern Matching

• Introduction to Character Data Types
• Pattern Matching Challenges
• Solutions to the Pattern Matching Challenges
• Introduction to Aggregate Functions
• GROUP BY and Aggregate Functions
• HAVING Clause
• Logical Query Processing Order
• GROUP BY Challenges
• Solutions to the GROUP BY Challenges
• AND operator
• OR operator
• Operator Precedence
• IN Operator
• Logical Operator Challenges
• Solutions to the Logical Operator Challenges

Outer Joins

• LEFT OUTER JOIN and RIGHT OUTER JOIN
• FULL OUTER JOIN
• Integrity Constraints
• Many-to-many Relationships
• Join Challenges
• Solutions to the Join Challenges
• Composite Joins – Joining on multiple columns
• Joining more than two tables
• Predicate placement – ON clause vs WHERE clause
• Self-referencing Joins
• Cross Joins
• Advanced Join Challenges
• Solutions to the Advanced Join Challenges
• UNION and UNION ALL
• INTERSECT
• EXCEPT
• Set Operator Precedence
• SET Operator Challenges
• Solutions to the Set Operator Challenges
• Self-contained Subqueries
• EXISTS

Introduction to Window Functions

• Common Table Expressions
• Avoiding the NOT IN trap
• Subquery Challenges
• Solutions to the Subquery Challenges
• Concatenation in SQL Server
• String Manipulation Functions
• Date and Time Functions
• Function Challenges
• Solutions to the Function Challenges
• CASE Expression
• CASE Expression Challenges
• Solutions to the CASE Expression Challenges

Overview of Data Types

• DECIMAL Data Type
• FLOAT Data Type
• CREATE TABLE Statement
• INSERT Statement
• CREATE TABLE Challenges
• Solutions to the CREATE TABLE Challenges
• UNIQUEIDENTIFIER (GUID) Data Type
• UPDATE Statement
• DELETE Statement
• TRUNCATE TABLE statement
• DROP TABLE Statement
• Stored Procedures
• Stored Procedures Demo
• Stored Procedure Challenges
• Solutions to the Stored Procedure Challenges

ALTER TABLE Statement and sp_rename

• ALTER TABLE Challenges
• Solutions to the ALTER TABLE Challenges
• UNIQUE Constraints
• CHECK Constraints

Introduction to Indexes

• Index Types and Indexing Guidelines
• Index Types Part 2 – Composite vs Included Columns
• Indexing and Sargable Query Challenges
• Solutions to the Indexing and Sargable Query Challenges

CREATE VIEW Statement

• Functional Dependencies
• Functional Dependencies Exercise
• Candidate Keys and Super Keys

First Normal Form

• Restructuring Multi-valued and Multi-type fields
• Second Normal Form
• Third Normal Form
• Boyce Codd Normal Form

Section 1 an introduction to MySQL

• Chapter 1     An introduction to relational databases and SQL
• Chapter 2     How to use MySQL Workbench and other development tools

Section 2 The essential SQL skills

• Chapter 3     How to retrieve data from a single table
• Chapter 4     How to retrieve data from two or more tables
• Chapter 5     How to code summary queries
• Chapter 6     How to code subqueries
• Chapter 7     How to insert, update, and delete data
• Chapter 8     How to work with data types
• Chapter 9     How to use functions

Section 3 Database design and implementation

• Chapter 10     How to design a database
• Chapter 11     How to create databases, tables, and indexes
• Chapter 12     How to create views

Section 4 Stored program development

• Chapter 13     Language skills for writing stored programs
• Chapter 14     How to use transactions and locking
• Chapter 15     How to create stored procedures and functions
• Chapter 16     How to create triggers and events

Section 5 Database administration

• Chapter 17     An introduction to database administration
• Chapter 18     How to secure a database
• Chapter 19     How to back up and restore a database

Introduction to PostgreSQL

• A Brief History of PostgreSQL
• Features
• Internals Summary
• Limits and Terminology
• Installation and Configuration
• Pre-requisites

Installation from Packages and Creating Database

• Installation from Source Code
• Client Installation
• Starting and Stopping a Database Server
• Environment Setup

The SQL Language

• SQL Syntax
• Data Definition
• Data Manipulation
• Queries
• Data Types
• JSON
• Functions and Operators
• Type Conversion
• Indexes

Transactions and Concurrency

• Transactions and Isolation
• Multi-Version Concurrency Control
• Client Interfaces
• Command Line Interface – psql
• Graphical Interface – pgadmin4
• Server Programming
• Extending SQL
• Triggers
• The Rule System
• Procedural Languages
• PL/pgSQL – SQL Procedural Language
• Error Handling
• Cursors
• Foreign Data Wrappers
• Extension in PostgreSQL
• Adding FDW in a Database
• postgres_fdw
• file_fdw
• Other FDWs
• SQL Tuning
• Logging in PostgreSQL
• Query Plans
• Optimizing Queries
• Statistics
• Planner Parameters
• Parallel Query Scans
• SQL Best Practices
• Table Partitioning