Datails - StepUp Analytics

SQL for Data Science



Workshop Start Date: 04-01-2020

Workshop End Date: 26-01-2020

Venue: Online

Registration Ends: 03-01-2020

Fee: Rs.650

Duration: 16 Hours (4 Weekends)


Welcome to Databases and SQL for Data Science!

 

This course is designed to give you a primer in the fundamentals of SQL and working with data so that you can begin analyzing it for data science purposes. You will begin to ask the right questions and come up with good answers to deliver valuable insights for your organization. This course starts with the basics and assumes you do not have any knowledge or skills in SQL.

 

It will build on that foundation and gradually have you write both simple and complex queries to help you select data from tables.  You'll start to work with different types of data like strings and numbers and discuss methods to filter and pare down your results. 

 

Our team will assist to install MYSQL in your system so that you can practice and complete the projects and assignments as part of this course. 

 

So what are you waiting for?

 

Getting Started and Selecting & Retrieving Data with SQL:

  • Data Models, Part 1: Thinking About Your Data

  • Data Models, Part 2: The Evolution of Data Models

  • Data Models, Part 3: Relational vs. Transactional Models

    • Relational vs Transactional model

    • Building blocks of data models

    • ER diagrams

    • Primary and foreign keys

    • ER diagram notations

      • Chen notation

      • Crow's foot notation

      • UML class diagram notation

  • Retrieving Data with a SELECT Statement

    • Limiting results

  • Creating Tables

    • Inserting data

      • Insert implicitly

      • Insert explicitly

  • Creating Temporary Tables

  • Adding Comments to SQL

Filtering, Sorting, and Calculating Data with SQL

  • Module Introduction

  • Basics of Filtering with SQL

    • Why filter

    • WHERE clause

  • Advanced Filtering: IN, OR, and NOT

    • IN operator

    • OR operator

    • IN vs OR

    • NOT operator

  • Using Wildcards in SQL

    • Using wildcards

    • Underscore wildcard

    • Implications of using wildcards

  • Sorting with ORDER BY

    • Sort direction

    • Sorting by columns position

  • Math operations

  • Aggregate Functions

    • AVG function

    • COUNT function

    • MAX and MIN functions

    • SUM function

    • DISTINCT

  • Grouping Data with SQL

    • Using GROUP BY

    • WHERE, HAVING, and GROUP BY

    • GROUP BY and ORDER BY

  • Putting it all together

Subqueries and Joins in SQL

  • Module introduction

    • Joins

  • Using subqueries

    • What are subqueries?

    • Problem: Get regions for customers who have ordered freight >= 100

    • Working with subquery statements

  • Subquery Best Practices and Considerations

    • Subquery in a Subquery

    • Subqueries for calculations

    • The power of subqueries

  • Joining Tables: An Introduction

    • Benefits of breaking data into tables

    • Joins

  • Cartesian (Cross) Joins

    • Cartesian / Cross Join Example

    • Different ways to create cartesian joins

    • Takeaways

  • Inner Joins

    • Qualifying and aliasing

    • Best practices

  • Aliases and Self Joins

    • Aliases

    • Self joins

  • Advanced Joins: Left, Right, and Full Outer Joins

    • Left join

    • Right join

    • Full outer join

  • Unions

    • Union syntax

  • Summary

    • Best practices using joins

    • Slowly do

    • Joins and database performance

    • Join syntax

Modifying and Analyzing Data with SQL

  • Module Introduction

    • Methods for modifying data

    • Case statements

  • Working with Text Strings

    • Working with string variables

    • Concatenations

    • Trimming strings

    • Substring

    • Upper and Lower

  • Working with Date and Time Strings

    • Date formats

    • SQLite date time functions

    • Timestrings

    • Modifiers

  • Date and Time Strings Examples

    • STRFTIME

    • Compute current date

    • Compute dates and times for the current date

  • Case Statements

    • Search case statement

  • Views

    • Creating a view

  • Using SQL for Data Science, Part 1

  • Using SQL for Data Science, Part 2