Sql course curriculum

The Course starts with learning basics sql using MySql, and then we shall move the course to Spark to build data jobs.

This course is exclusively designed for starters as well as for experienced professionals addressing SQL Basics, LINUX, Installation, Database Design, Queries, Joins, Stored Procedures, Triggers, Transactions and Tuning options. As part of the learning curve we will be starting to work on MySql first to get hands-on on the general Database syntaxes. Once we have acquired the required knowledge on Sql basics, then we shall move on to get some basics on Python before we move onto Spark. Once we are done with the prerequisite, then we will be going through the Installation of spark, initializing Spark and spark ecoSystem.

 

The course starts with,

  • MySql Installation
    1. Installation Pre-Requisites and Precautions
    2. MySql Server/Client installation
    3. Creating a new User
    4. Grant permission
  • INTRODUCTION TO RDBMS
      1. Features and advantages of RDBMS
      2. RDBMS from other vendors.
      3. Databases & Schemas
      4. Database Entities and Attributes
      5. Database Types: OLTP and OLAP Databases
      6. Real-world Advantage of OLTP & OLAP
  • SQL LANGUAGE COMMANDS
      1. SQL Basics
        1. Data Types
        2. Creating Users and Tables
        3. Table Data Inserts and Validations
        4. DDL Commands with Examples
      2. Data Definition Language (DDL)
      3. Data Retrieval Language (DRL)
      4. Data Manipulation Language (DML)
      5. Transaction Control Language (TCL)
      6. CREATE, ALTER, TRUNCATE, DROP, RENAME
      7. INSERT, UPDATE, DELETE Statements
      8. SELECT Statements with Multiplier
      9. WHERE Keyword for Query Conditions
      10. Difference between Truncate and Delete commands
      11. SQL Comments
  • Operators
      1. AND, OR, NOT, IN
      2. Special Operators – IN (NOT IN), BETWEEN (NOT BETWEEN)
      3. Arithmetic and Logical Operators
      4. Understand Operator Precedence
      5. UNION, UNION ALL, INTERSECT, MINUS
  • CONSTRAINTS & KEYS
      1. NOT NULL Constraint, UNIQUE Constraint
      2. PRIMARY KEY Constraint and Usage
      3. FOREIGN KEY Constraints and Relations
      4. Multiple Constraints on Columns
      5. Adding Constraints to Tables
  • QUERIES & JOINS
      1. Need for Joins and Table Comparisons
      2. Join Types
      3. Self Joins, Merge Joins and Sub Queries
      4. Using Aliases in Joins and Conditions
      5. Using Inner Self Joins with HAVING
      6. JOINS and GROUP BY Queries
    • SUB QUERIES, NESTED QUERIES
      1. Single Row Sub query
      2. Multiple Row Sub query
      3. Dynamic Conditions with Sub Queries
      4. Sub Queries and Nested Sub Queries
    • Views & Procedures
      1. Views 
      2. Procedures
  • Performance Tuning
    1. Indexes
    2. Materialized Views
    3. Partitioning Tables
    4. Parallel Query Process with Partitions
  • Python 
    • Introduction to Python
    • Variables in Python
    • Python Operators and Operands
    • Python Conditional
    • Python LOOPS 
    • Python Exception Handling
  • Spark
    • Introduction to Apache Spark
    • What is Spark?
    • Spark Ecosystem, &modes of Spark
    • overview of Spark on a cluster
    • Spark Standalone cluster
    • Spark Web UI &
    • Spark Common Operations
  • Spark Core
    1. performing basic Operations on files
    2. building a Spark project
    3. Playing with RDDs:
    4. RDDs, transformations in RDD, actions in RDD
    5. loading data in RDD
  • Spark SQL
    1. SparkSQL and Performance Tuning in Spark:
    2. Analyze Hive and Spark SQL architecture, SQLContext in Spark SQL
    3. working with Data Frames
    4. implementing an example for Spark SQL
    5. integrating hive and Spark SQL
    6. support for JSON and Parquet File Formats
    7. implement data visualization in Spark
    8. loading of data
    9. performance tuning tips in Spark