SQL for Business Users

Table of Contents

Getting Started With Databases And SQL

Relational Databases And Key Terms Explained

About The Author

What Are The SQL Commands

Select Syntax And Examples

Where Can I Use SQL?

Installing Sample Databases And Working Files

Using SQL To Select And Filter Data

Using Select To View Data

Sorting The Data

Adding A Where Clause To Filter Data

Filtering Date Columns

Working With Null Or Empty Values

Using Comments Within SQL

Use Of Wildcards In Filtering

Multiple Criteria Using And Or In

Functions, Grouping And Summarizing

Add A Calculating Column To A SQL Statement

Text Manipulation Functions

Differences In Date Functions Within SQL

Calculating Age

Concatenate Data Columns

Joining Tables

Which Tables Can I Join And How?

Joining More Than Two Tables

Using Different Join Types

The Special Cross Or Cartesian Join

Group And Summaries Within Joined Table SQL

Data Modification

Amend Data In Tables

Adding Data To Tables With Insert

Removing Data From Tables

Creating And Altering Database Objects

Create A New Table

Changing A Tables Structure

What Is A View And How To Create One

Alter Or Delete A View

SQL Summary

The Main Variances In SQL Between Databases

The Powerful Subquery

Conclusions

Wrap Up

The Warm Up

About This SQL Advanced Title

About The Author

Relational Databases And Key Terms Explained

Recap Of SQL SELECT Command

Overview Of The Other DML Statements

Installing The Sample Data And Using The Working Files

Useful Skills

Converting Datatypes

Creating A Temporary Table

The DISTINCT And TOP Keywords In Action

Indexing To Speed Up Your Life

Declaring And Using Variables

The Subquery

What Is A Subquery?

Subquery Vs Joins

Using Correlated Subqueries

Using The EXISTS Keyword

Finding Duplicate Records

The CASE Statement

Getting Started With The CASE Statement

Using The CASE Statement With Criteria On Multiple Columns

Multiple Criteria Within The CASE Statement

CASE In WHERE, ORDER BY, And GROUP BY Clauses

CASE In UPDATE And INSERT Statements

CASE Within Subqueries

Numbering And Ranking

Numbering Or Ranking Records In A SELECT Statement

Filtering Results Based On Numbering Ranking

Using Numbering Within Groups

Filtering A Ranked Group To Determine Top N

Functions

Using Built In Functions

Creating And Using Your Own Functions (UDF)

Views

Creating A View

Example Views And Views In Views

Using The UNION Clause

Stored Procedures

What Is A Stored Procedure?

Creating A Stored Procedure

Running A Stored Procedure

Stored Procedures Within Stored Procedures

The Global Variables @@Identity And @@Rowcount

Looping With WHILE And IF

Dynamically Build SQL Statements

TRY And CATCH For Error Trapping

Cursors In Use - Part 1

Cursors In Use - Part 2

Use Of Transactions

Conclusions

Wrap Up

Getting Started

About The Course

About The Author

What Is A Database?

What Is A Data Model?

Data Model Development Process

Data Model Inputs And Outputs

Data Model Notations

UML Versus IE - Conceptual, Logical And Physical

Basic Data Modeling

Class And Attribute

Operation

Domain

Association

IE Entity Type And Relationship Type

Association Name

Association End

Multiplicity - UML

Multiplicity - IE

Generalization - UML

Generalization - IE

Abstract Versus Concrete Superclass

Practical Tips

Self Assessment Test - Basic Modeling Data

Advanced Data Modeling

Identity

Derived Data

Current Versus Historical Data

Association Class

Ordered Association

Qualified Association - UM

Qualified Association - IE

Large Taxonomies

Package

Abridged UML Metamodel

Abridged IE Metamodel

Modeling Pitfalls

Practical Tips

Self Assessment Test - Advanced Data Modeling

Create A UML Data Model

Problem Statement

Finding Classes

Finding Associations - Part 1

Finding Associations - Part 2

Finding Generalizations

Iterating And Refining The Model - Part 1

Iterating And Refining The Model - Part 2

Adding Attributes

Cleaning Up Layout

Simplifying The Model

Evolving A Model - Part 1

Evolving A Model - Part 2

Enterprise Architect Techniques - Part 1

Enterprise Architect Techniques - Part 2

Enterprise Architect Techniques - Part 3

Translate A UML Data Model Into An IE Data Model

Creating Subject Areas

Creating Entity Types

Creating Domains

Adding Attributes - Part 1

Adding Attributes - Part 2

Creating Relationship Types - Part 1

Creating Relationship Types - Part 2

Creating Relationship Types - Part 3

Subtyping

Adding Alternate Keys

Cleaning Up The Layout

ERwin Techniques - Part 1

ERwin Techniques - Part 2

Model Quality

Model Quality

Normal Forms

Constraints

Hillard Graph Complexity

Hoberman Data Model Scorecard

Kinds Of Data Models

Operational Data Models

Enterprise Data Models

Data Warehouses - Part 1

Data Warehouses - Part 2

Data Warehouses - Part 3

Master Data Models

Database Design

Schema Adjustments

Attribute Details - Part 1

Attribute Details - Part 2

Attribute Details - Part 3

Primary And Alternate Keys

Indexes

Referential Integrity - Part 1

Referential Integrity - Part 2

Check Constraints - Part 1

Check Constraints - Part 2

Views

Other Aspects Of Design

Self Assessment Test - Database Design

Create A SQL Server Database

Creating A New Database

Executing Schema

Inspecting Metadata

Loading Sample Data

Querying Sample Data

Create An MS-Access Database

Generating An ERwin Schema

Creating Tables

Creating Indexes

Creating Constraints And Default Values

Defining Foreign Keys

Creating Views

Loading Sample Data

Querying Sample Data

Software Engineering

Development Frameworks

Agile Data Modelling

Documenting A Model - Part 1

Documenting A Model - Part 2

Presenting A Model

Data Modeling Patterns

Overview

Tree - Hardcoded

Tree - Simple

Tree - Structured

Tree - Overlapping

Tree - Changing Over Time

Tree - Degenerate Node and Edge

Database Reverse Engineering

Motives

Comparison With Forward Engineering

Outputs

Inputs

Process

Principles

Example - Part 1

Example - Part 2

Conclusion

Wrap-Up

Introducing The Relational Database

Course Introduction

What Is A Relational Database

The Database Hierarchy

Table Relations

The Structured Query Language

Conclusion

Installing And Configuring MySQL

Introduction To MySQL

MySQL Features

Installing MySQL - Introduction

Downloading MySQL

Installing MySQL - Part 1

Installing MySQL - Part 2

Configuring MySQL

Setting The System Path - Part 1

Setting The System Path - Part 2

Installing MySQL On The Mac

The Sakila Database

Installing The Sakila Database

Connecting To MySQL And Creating Your First Database

Introduction To MySQL Connections

Role Of The Root User

Logging In For The First Time

Change The Root Password

Creating Your First Database

Creating Your First User Account

Creating Your First Table

Introducing MySQL’s Data Types

Intro To Data Types

String Data Types - Part 1

String Data Types - Part 2

Data Attributes

Customers Table Revised

Numerical Data Types - Part 1

Numerical Data Types - Part 2

Numerical Data Attributes

Date And Time Types

Multiple Timestamp Columns In Table

Primary And Foreign Keys

Primary Keys - Part 1

Primary Keys - Part 2

Creating A Primary Key

Foreign Key Introduction

Create Foreign Key

Introducing phpMyAdmin, MySQL Workbench, SQLyog

Graphical MySQL Clients

phpMyAdmin Intro

MySQL Workbench Intro

MySQL Workbench SQL Development Intro

MySQL Workbench Data Modelling Intro

MySQL Workbench Server Administration Intro

SQLyog Introduction

SQLyog Creating A Database

SQLyog Navigating And Modifying Data

SQLyog Scheduling Backups

Creating, Retrieving, Updating And Deleting Data

Performing CRUD Operations

Creating Data

Retrieving Data

Ordering Results

Filtering Results

Grouping Results

Removing Duplicate Rows

The LIKE Clause

The LIMIT Clause

Updating Data

Deleting Data

Using String, Numeric, Temporal And Stored Functions

Introduction To Functions

String Functions

Numeric Functions

Temporal Functions - Introduction

Temporal Functions - Example

Stored Functions - Part 1

Stored Functions - Part 2

Stored Functions - Part 3

Introducing Subqueries

Subqueries Intro

Subqueries

Introducing SQL Joins

SQL Joins

The Inner Join

The Left Join

Optimizing MySQL

Optimizing MySQL

Column Indexing

Column Indexing Explained

Optimizing The MySQL Configuration

Query Caching

Query Caching Explained

Optimizing MySQL Conclusions

Importing, Exporting And Backing Up MySQL Databases

Introduction To MySQL Data Tasks

Exporting Data With MySQL Workbench

Importing Data With phpMyAdmin

Automatically Backing Up Your MySQL Database

Automating Your Backups With mysqldump And cron

Managing MySQL User Privileges

Managing MySQL Privileges

MySQL Privilege Management Walkthrough

Using PHP And MySQL

Introduction to PHP and MySQL

Querying Your MySQL Database With PHP

About The Author

About The Author

Install And Setup MSSQL

Is This Course For Me

About The Author

MSSQL Versions And Editions

Installing MSSQL - Part 1

Installing MSSQL - Part 2

The Adventureworks Sample Database

SQL Server Management Studio (SSMS)

Restoring The Adventureworks DB

Exploring Adventureworks With SSMS

Exploring A Foreign Database

Browsing The Database With Object Explorer

SSMS Query Editor

Selecting Data About Pay Rates

Limiting Number Of Rows Returned

Is My Query Valid

Query Result Formats

Documentation In Extended Properties

Creating A Project To Organize Scripts

The Query Designer

Answering Questions By Querying A Database

Answering The Question Of Pay Inequality

Inspecting Dependencies

Inspecting Foreign Keys

Executing Multiple Queries In Query Editor

Counting Results From A Query

Select Distinct

Group By And Having

Joining Tables

Aliasing Tables In A Query

Validating Joins And Join Types

Computing The Average Pay Rate

Order By

Where

Variables

Querying Current Rate Change Date

Join To A Sub Query

Inequality In Current Wages

Recap

Advanced Querying To Answer Subtleties

Organizational Level And Pay Equity

Common Table Expressions

REFactoring For Readability

Case When And Calculated Columns

Over Rank Functions

Reporting From A Database

Getting Information To Those That Need It

Connecting Excel To MSSQL

Creating Views

Extracting CSVS With SQL CMD

SSRS Report Builder

SSRS Report Web Access

Creating Databases

National Stock Number Extract

Create Database

Drop Database

SSMS Table Designer

Create Table

Drop Table

Data Types

Create Table National Stock Numbers

Stored Procedures - SP_Rename

Alter Table

Importing Data - National Stock Number Extract

Troubleshooting Importing Data

Import Alternatives

Backing Up The Database

Backup With Select Into

Developing Applications With MSSQL

Visual Studio Community Edition

New Console Application

Connecting To MSSQL

Querying Data Old School

Create Table In Code

Create A Foreign Key

Inserting Data Old School

Installing Entity Famework

Querying With Entity Framework

SQL Server Profiler - What Is EF Doing

EF Querying Data

EF Inserting Data

EF Updating Data

EF Deleting Data

Other Programming Languages

Course Wrap Up

Introduction

Welcome To The Course

About The Author

Course Overview

Current Context

Introduction To SQL

Introduction To R

Software Installation

Rstudio Overview

R Packages

The Relationship Between R And SQL

Demo Application And Database Schema Overview

SQL With Single Table Results Sets

Relational Theory Review

Results Sets

Processing Results Sets With R

Filtering And Ordering With SQL

Grouping And Summarizing SQL

Modify Results Using SQL Functions

SQL With Multiple Tables

Common Database Joins

Less-Common Joins

Subqueries

Set Operations

DBA Considerations

Table-Like Objects

Indexes

R Packages And SQL-Like Results Set Processing

SQL Results Set And Tidy Data

Processing Results Sets With R Vs SQL

Filtering And Ordering With Dplyr Vs. SQL

Grouping And Summarizing With Dplyr Vs. SQL

Modify Results Using Dplyr And R Functions

Joins Using Dplyr

Set Operations Using Dplyr

Reshape Package

RTidy Package

Data Artifacts Using SQL And R

Plotting Database Results With R

Plots Using Base R Plots

Plots Using Lattice

Plots Using ggplot2

Plotting Time Series Data

Creating Maps With R

Creating Reports With R

Web Applications With R

Data Sources And Connections

Sample Data Sets

Database File Exports/Imports

Local Relational Databases (RSQLite)

Non-Relational Data Sources

Remote Connections

Troubleshooting Remote Connections

JDBC Client Software

Additional Topics

Derived Tables

Vendor Specific SQL

Non-SQL Inspired/SQL-Like Languages

R Inside The Database

Schema Design Considerations

Conclusion

Wrap Up And Thank You

Introduction

What Is Apache Phoenix And Who Uses It

What You Should Expect From This Course

About The Author

Introduction To NoSQL

What Is NoSQL

What Is SQL

Performance Characteristics Of SQL

Performance Characteristics Of NoSQL

Optimizing NoSQL

Introduction To Phoenix

Hbase Fundamentals

Phoenix SQL Engine For Hbase

Obtaining And Configuring Apache Phoenix

Using Phoenix

Creating Tables

Schema Operations

Indexes And Secondary Indexes

Executing Queries

Advanced Phoenix

Statistics

Salted Tables

Views

Conclusion

Wrap Up