SQL Data Analytics Course: Advanced Level
DURATION: 36 HOURS ONSITE TRAINING
(12 Sessions of 3 Hours each)
Method: 1 on 1
The schedule is tentative; finalized after enrollment according to student/instructor availability.
SQL Intro - Filtering, inbuilt functions, & creating databases
- 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
- Installations on Ubuntu (MYSQL, SQLite). Joins, Group by, Views
- Microsoft SQL Server – Adventure-works and Northwind
DAY 4 - Part 1
- Understand Different Cloud databases
- Difference between SQL and NoSQL
- Run SQL Workbench and connect to AWS endpoint
DAY 5 - Part 2
- Run nested Group by
- Join multiple tables
- Join and Group by in the same code
- Sub queries
- CASE Statement
DAY 6 - 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
DAY 7 - Part 4
- Correlated queries Views
- Prerequisite is 101 SQL
- You need a computer with SQL workbench
Advanced Joins and Group by (Nested) Union, CASE, Sub queries, CTE, Casting
- 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)
- 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.
- 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?
- Top 5 problems in Excel and top 5 most useful features in 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.
- 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
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 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
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 the 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.
- Project Portfolio for Github for showcasing to prospective employers
- SQLite for Python (Flask Django & Pandas)
- NoSQL MongoDB, Hadoop HIVE and Hbase installation