Course curriculum

  • 1
    Course Overview
    • Course Overview
    • Course Handout
  • 2
    Introduction to Databases
    • Why do we need databases
    • What is a database
    • Some properties of a good Database
    • Types of Databases
    • How data is Stored in Relational Databases
    • ACID Properties of Relational Databases
    • Companies using MySQL
    • Quiz - Introduction to Databases
  • 3
    Installing MySQL
    • Architecture - Client and Server
    • MySQL Distributions
    • Local Installation on Mac
    • Local Installation on Linux
    • Local Installation on Windows
    • Quiz - Installing MySQL
  • 4
    Getting started with SQL
    • Getting Started with SQL
    • What is SQL
    • Connecting to MySQL on Windows
    • Connecting to MySQL on Linux
    • Connecting to MySQL on Mac
    • Exploring Databases
    • Creating a Table
    • Datatypes in MySQL
    • Describe Command
    • Describing Tables
    • Insert Command
    • Inserting Records
    • Retrieving Records
    • NULL vs NOT NULL
    • Dealing with NULL values
    • Quiz - Getting Started with SQL
    • Update Command
    • Updating Records
    • Delete Command
    • Deleting Records
    • Alter Command
    • Altering Table Structure
    • Drop Table
    • Dropping Table
    • Quiz - Modifying Table Structure
  • 5
    Basic Querying
    • Importing data from CSV to MySQL
    • Counting Records
    • Aggregation Functions
    • Extreme Values Identification
    • Quiz - Basic Querying 1
    • Slicing Data
    • Limiting Data
    • Sorting Data
    • Quiz - Basic Querying 2
    • Pattern Matching
    • Quiz - Basic Querying 3
    • Grouping Records
    • Filtering in Groups
    • Quiz - Basic Querying 4
    • Exporting data from MySQL to CSV
    • Backing up Databases
    • Restoring Databases
    • Importing and Exporting Datasets Troubleshooting Guide
    • Quiz - Importing and Exporting data in MySQL
  • 6
    Data Analysis with SQL - FIFA Project
    • Description Analytics of FIFA 19 Players
    • Data Eyeballing
    • Data Dictionary
    • Questions we need answers of
    • Analyzing data and creating table structure
    • Loading data to our MySQL table
    • Data Analysis – Simple Queries
    • Data Analysis – Advanced Queries
    • FIFA19 Players Dataset
  • 7
    MySQL built-in functions
    • String Concatenation
    • String Case Conversion
    • Trimming Strings
    • Extracting Substrings
    • Understanding RegEx
    • Matching String patterns with RegEx
    • Current Date and Time
    • Extracting Date and Time
    • Formatting Date and Time as Strings
    • Numeric functions
    • Conditional Flow
    • Writing Conditional Statements
    • SQL CheatSheet
    • Quiz - MySQL built-in functions
  • 8
    Subqueries
    • Why we need Subqueries
    • What are Subqueries
    • Types of Subqueries
    • Implementing Subquery
    • Quiz - Subqueries
  • 9
    Constraints in SQL
    • What are Constraints
    • Domain Constraint
    • NOT NULL Constraint
    • DEFAULT Constraint
    • Adding NOT NULL and DEFAULT Constraints
    • UNIQUE Constraint
    • CHECK Constraint
    • Adding UNIQUE and CHECK Constraints
    • Key Constraint
    • Implementing KEYS in SQL
    • Quiz - Constraints in SQL
  • 10
    Working with Multiple Tables - Movie Ticket Project
    • Working with multiple tables
    • What is Foreign Key
    • Types of Relationships
    • Quiz - Working with Multiple Tables
    • Movie TIcket Project Dataset
  • 11
    Joins
    • Joins Overview
    • Inner Join
    • Implementing Inner Join
    • Left Join
    • Implementing Left Join
    • Right Join
    • Implementing Right Join
    • UNION Clause
    • Full Outer Join
    • Implementing Full Outer Join
    • Cross Join
    • Implementing Cross Join
    • Self Join
    • Implementing Self Join
    • Multiple Tables with Sub-queries
    • Quiz - Joins
  • 12
    MySQL Python Connector
    • Installing Python Connector
    • Making Connection with MySQL Server
    • Implementing basic SQL commands with connector
    • SQL to Pandas DataFrame
  • 13
    ETL using Python and SQL - E-Commerce Project
    • ETL Project Files
    • What is ETL
    • Understanding the Context
    • Understanding the Requirements
    • Setting up the Environment
    • Designing the Pipeline
    • ETL Pipeline 1
    • ETL Pipeline 2
    • ETL Pipeline 3
    • Schedule ETL Pipeline

About Fundamentals of Structured Query Language

SQL is a must have skill for every data professional. This course will start from basics of databases and Structured Query Language (SQL). It will include writing queries in SQL, manipulating data, working with multiple tables, and even building ETL pipelines.

Key Takeaways

  • Working with MySQL - the most popular Database Management System

  • Learn to Insert, Update, Delete records in SQL

  • Manipulating data using SQL clauses

  • Perform data analysis using SQL’s aggregate functions

  • Understand and implement subqueries in SQL

  • Learn to work with multiple tables using different types of JOINS

  • Understand and build ETL pipelines

Projects

Descriptive Analytics of FIFA 19 Players

In this project we take a real life dataset of FIFA19 players and do descriptive analytics on it. We will go step-by-step as is done in real life by a Data Scientist - and we will walk you through the thought process of how data is understood, transformed into a useful format and how we get answers and insights from our data.
Projects

Movie Ticket Booking

In this project we work with multiple tables to store and process the data. We will be building multiple tables and defining the relationships between them. We will then make use of SQL Joins to query on these multiple tables.

E-Commerce ETL Project

In this project about an e-commerce website, we will be building an ETL pipeline to Extract data from multiple sources, Transform that data to fulfill the requirements, and finally Load the transformed data into target source. We will also look at how to automate this ETL pipeline.

Instructor(s)

  • Anand Mishra

    Anand Mishra

    Anand Mishra is Head of Engineering at Analytics Vidhya. He is an entrepreneur, an engineer and a data science professional all rolled into one. He is one of the co-founders of MudraCircle. Before MudraCircle, Anand has worked across several companies like Lendingkart, HTMedia as Head of Data Science, Tickled Media, Infoedge India and Opera Solutions. He brings experience across several domains including E-Commerce, Fashion and Retail. Anand earned his B.Tech and M.Tech in Electrical Engineering at IIT Kanpur. Anand specializes in analytical problem solving, especially machine learning, classification, regression, and decision optimization.
  • Pranav_ Dar

    Pranav_ Dar

    Pranav is a data scientist and Senior Editor for Analytics Vidhya. He has experience in data visualization and data science. Pranav has previously worked for a number of years in the learning and development field for a globally-known MNC. He brings a wealth of instructor experience to this course as he has taken multiple trainings on data science, statistics and presentation skills over the years. He is passionate about writing and has penned over 200 articles on data science for Analytics Vidhya.
  • Siddharth Sonkar

    Senior Big Data engineer

    Siddharth Sonkar

    Siddharth is a Senior Big Data Engineer at Analytics Vidhya. He has a rich experience in Big Data technology stack like Hadoop, Spark, Kafka and AWS, amongst others. He has worked for top clients across the globe and across a diverse set of domains like e-commerce, telecom, banking and automotive to name a few. Siddharth is an avid reader and enjoys watching movies and playing video games whenever he gets the chance.
  • Lakshay Arora

    Lakshay Arora

    Lakshay is a Computer Science Graduate from NorthCap University. He is currently working as a Data Engineer at Analytics Vidhya. He loves to explore the endless world of data engineering and also writes blogs related to model deployment, machine learning pipelines and big data tools like Spark. In the free time, Lakshay loves to do competitive programming
  • Aniruddha Bhandari

    Data Scientist

    Aniruddha Bhandari

    Aniruddha is a Computer Science Graduate and a Data Scientist at Analytics Vidhya. He regularly contributes to the AV Blog with articles related to Data Science and Data Engineering fields. He has also contributed to courses on NLP and Apache Spark.