SQL Immersive Course NYC

Introduction to SQL for Data Analytics for Beginners

Introduction to Practical SQL for Data Analytics

Duration: 7 days 35 hours

Prerequisite: Basic Excel

 

SQL Developer Immersive Boot-camp

Day 1 SQL Intro -Filtering, inbuilt functions. Creating DB. DB Fiddle
Day 2 Installations on Ubuntu (MYSQL, SQLite). Joins, Group by, Views AWS / Digital Ocean
Day 3 Advanced Joins and Group by (Nested) Union, CASE, Sub queries, CTE, Casting MYSQL / SQL Server
Day 4 Database Design (Foreign Keys, M-2-M tables)

Creating Database Diagrams in Workbench / SQL Server

MYSQL Workbench
Day 5 Functions, Procedures, Indexing, Timestamps, constraints, indexes, and triggers MYSQL / SQL Server
Day 6 SQLite for Python (Flask Django & Pandas) Jupyter Anaconda
Day 7 NoSQL MongoDB, Hadoop HIVE and Hbase installation Ubuntu

Computer with SQL server and Adventurework Database would help but not a mandatory requirement

Suggested Installs: SQLite, SQL Server, MYSQL Workbench, MySQL server

Class is meant to be for non programmers

 

PPT: https://docs.google.com/presentation/d/1iXpK0tiPiPWS2KiRfT7BgShtQwK-ppAzY7nY23NmV9Y/edit?usp=sharing

 

Day 1 SQL 101.1 Session

 

The courses teaches MYSQL using online platforms and explains querying and creating tables. Introduces with querying logic and explains group by and joins in SQL. The class is completely hands on and assumes no prior knowledge.

 

This class is an ideal class for learning group by, joins, in built functions. After taking this SQL 101 class you can take other intermediate classes or classes involving functions, procedures and views. After attending the course, you can expect to run simple group by, joins and filtering on SQL tables.  Also learn about SQL Analytics and SQL Server.

 

Top 10 frequently asked SQL interview questions along with solutions and example is provided.

 

You will be also provided the 15 SQL queries that we run in the class for future reference.

 

The user also gets the code and slides for future references.

 

Intro to different versions of SQL

 

Part A: Create your tables and input values

Part B: Select based on different filtering

Part C: Group by, Union and Joins

Part D: String functions

 

Day 2 SQL 101.2 Session

 

Microsoft SQL Server - Adventure-works and Northwind

 

Part 1

Understand Different Cloud databases

Difference between SQL and NoSQL

Run SQL Workbench and connect to AWS endpoint

 

Part 2

Run nested Group by

Join multiple tables

Join and Group by in the same code

Sub queries

CASE Statement

 

Part 3

Custom functions and string functions

CAST, string, date functions

Functions and Procedures

When to use functions and procedures

Use a Wildcard with a Parameter

Handling date time format

Null values count and Joins

 

Part 4

Correlated queries Views

Index

 

Prerequisite is 101 SQL

You need a computer with SQL workbench

 

Day 3 SQL 101.3 Session

MYSQL and MYSQL-Workbench

What will  you achieve from this class:

Create a DB design for your day to day work data that you use in Excel

Understand what you can do and what you cannot do. Or rather how hard or simple it is to do some analytical steps in SQL.

Understand Data Wrangling using Group, Joins and PIVOT to get data in the format you need

For any data that comes to you new tools get new insights

Get Excel equivalent functions for SQL commands

Understand the data wrangling terms often used in Data Analytics that remain common to all languages (Python / R / SQL)

 

Project and Games-

Convert a simple Excel data in SQL and learn how scripting would automate manual task

Query data for group by, joins, pivot for the data we created

 

It is recommended for the learner to read the below questions and think over them to make the most out of the sessions.

 

Introduction to need of SQL and Design Aspects

Why do we need SQL? What is wrong with excel?

Different distribution packages available from Oracle, microsoft, ibm

For example: if we create the information of people attending the classes in excel- what is wrong with that?

How will SQL give us some better way of handling data?

Top 5 problems in Excel and top 5 most useful features in SQL

Can I and should I move all my data of work from excel to SQL? How would I do that?

Can I link Excel with SQL?

Project: Table of people attending the class. Understand and demonstrate what is possible and what is not. Also comment on how hard it would be.

 

Getting Hands on with SQL and Outline

*You need an SQL Server Developers Edition on a Windows Based System

** If you have a Mac then you will be required to setup an AWS account and EC2 with Windows and SQL Server

 

 

What happens if you want to repeat some steps. VBA vs Sql.

Connect to the SQL Database - Can you control access to users for specific sheets in excel? what about SQL security?

Query the common database available AdventureWork Database / OGCBooks on SQL Server

Save a Query and Modify a Query

Execute a Saved Query

 

Why do we need condition - what is the Excel equivalent?

Performing a Conditional Search

Search Using a Simple Condition (Filtering)

Compare Column Values

Search Using Multiple Conditions

Search for a Range of Values and Null Values

Retrieve Data Based on Patterns

 

Day 4 SQL 101.4: Advanced SQL Procs & SQL Functions -

Pre build and predefined Functions in SQL

Why and which are pre built functions in analytics

Working with Functions

Perform Date Calculations

Calculate Data Using Aggregate Functions

Manipulate String Values Organizing Data

Sort Data

Rank Data

Data Wrangling and Data Analytics

Group Data (the most important concept of the lesson!)

Filter Grouped Data

Summarize Grouped Data

Use PIVOT and UNPIVOT Operators (Broad concept that goes to R, Python, SAS, Tablue everywhere)

Retrieving Data from Tables

Combine Results of Two Queries

Compare the Results of Two Queries

Retrieve Data by Joining Tables

Check for Unmatched Records

Retrieve Information from multiple Tables Using Joins (understand Venn diagram - set theory)

Presenting and Making sense of Query Results

Save the Query Result

Generate an XML Report in SQL Server

 

Appendix A:

The OGCBooks Database

Adventure work Database provided by MS.

After completing this course, you will know how to:

Understand why SQL is needed as a solution to limitation of excel

Connect to the SQL Server database and execute a simple query.

Include a search condition in a simple query.

Use various functions to perform calculations on data.

Organize data obtained from a query before it is displayed on-screen.

Retrieve data from tables.

Format an output, save a result, and generate a report.

 

Day 5 SQL 101.5 Session

 

Project Portfolio for Github for showcasing to prospective employers

 

#Instructor:

Shivgan Joshi

info@bainyc.com

929 356 5046

https://github.com/shivgan3

https://www.linkedin.com/in/shivganjoshi/

https://notebooks.azure.com/shivgan3/libraries

 

** Payment Policy: We only accept payment at door and before the class. We accept payment through event leap, cash, Venmo & Paypal(+5). **