Tuesday, June 27, 2017

SQL:Fundamentals of Querying Course New York Manhattan

SQL:Fundamentals of Querying Course New York Manhattan NYC


Introduction to Practical SQL for Data Analytics (3 hours) Duration: 1 day(s)
Prerequisite: Basic Excel

What will  you achieve from this course:
Create a DB design for your work data you see in excel at your office
Understand Data Wrangling using Group, Joins and PIVOT to get data in the format you need for any data that comes to you adding new insight
Get Excel equivalent functions for SQL commands
Understand the terms in Data Analytics that remain university to all languages (Python / R / SQL)
Project - Convert a simple Excel data in SQL and learn how scripting would automate manual task


Introduction to need of SQL and Design Aspects

Why do we need SQL? What is wrong with excel?For example: if we create the information of people attending the classes - what is wrong with that?
How will SQL give us some better way of handling it?
Top 5 problems in Excel 
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



Getting Hands on with SQL and Outline

Executing a Simple Query
Connect to the SQL Database - Can you control access to users for specific sheets in excel? what about SQL?
Query a Adventure Work 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
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 class)
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 a Single Table Using Joins

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.

1 comment: