SQL Querying Data Science

SQL Data Science Course / SQL Private Tutor/ SQL Interview Prep Course New York Manhattan

Fundamentals of Querying by Online/Onsite Tutors in New York

http://BootcampNYC.QcFinance.in

sql boot camp

Introduction to Practical SQL for Data Analytics (3 hours / days) Duration: 2 day(s)

Prerequisite: Basic Excel

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

Class is meant to be for non programmers

 

What will  you achieve from this course:

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

Executing a Simple Query

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

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

 

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

 

Appendix A:

The OGCBooks Database

Adventure work Database provided by MS.

After completing this course, students 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.

—————————————————————————-

 

Duration: 9 hours / 2-3 classes / also 2-3 day(s)

This course is made so that you can do smooth transition from SQL to Data Science.

Outline
Executing a Simple Query
Connect to the SQL Database
Query a Database
Save a Query
Modify a Query
Execute a Saved Query

Performing a Conditional Search
Search Using a Simple Condition
Compare Column Values
Search Using Multiple Conditions
Search for a Range of Values and Null Values
Retrieve Data Based on Patterns

Working with Functions
Perform Date Calculations
Calculate Data Using Aggregate Functions
Manipulate String Values Organizing Data

Sort Data
Rank Data
Group Data
Filter Grouped Data
Summarize Grouped Data
Use PIVOT and UNPIVOT Operators

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 a Single Table Using Joins

Presenting Query Results
Save the Query Result
Generate an XML Report

Appendix A: The OGCBooks Database

After completing this course, students will know how to:
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.

 

Advanced Topics in SQL – SQL tutor / class in NYC New York Manhattan
SQL tutor / class in NYC New York Manhattan
Basic Topics:
Executing a Simple Query
Working with Functions
Organizing Data
Retrieving Data from Multiple Tables using Joins
Presenting Query Results
Querying Using Subqueries
Manipulating Table Data
Views
Indexing
Transactions
Decision
Format
Stored Procedures
Automation

 

Other topics if needed:

OLAP Cubes in SQL Server for Adventure Works

Advanced T-SQL Querying, Programming and Tuning for SQL Server 2012, 2014 and 2016

SQL for Data Analytics / SQL for data science makes you ready for delving more into data science.

Learn SQL Server 2016 Querying & Analytics

Query, Arrange, Group, Join, merge data using SQL server.
If you have SQL server installed in your system and Adventure-work Database we can start with the Code from the official books.
The prep is similar to SQL MS certification so that you can also take certification in the future.
A very good foundation to move to SQL Business Intelligence Applications.

 

Comments are closed.