Become An Sql Data Engineer/Data Analyst

Mastering Data: Unleashing the Power of SQL for Future Data Analysts and Engineers

What you'll learn

Understand the roles and responsibilities of data analysts and data engineers.

Identify and use different types of SQL databases including MS SQL, MySQL, PostgreSQL, and Oracle SQL.

Write basic to complex SQL queries using various SQL syntax, operators, and functions.

Understand and implement data cleaning, , backup, and restoration in SQL.

Perform data analysis tasks using SQL, such as computing descriptive statistics and utilizing various functions and techniques for manipulating data.

Understand the principles of data engineering using SQL, including designing databases, handling ETL processes, and managing large datasets.

Implement SQL-like queries in NoSQL databases.

Understand the basics of Big Data technologies and how SQL interfaces with these tools.

Use SQL in conjunction with popular data visualization tools such as Tableau and PowerBI.

Apply SQL best practices and performance optimization strategies in real-world situations.

Gain a strong foundation in SQL, setting the stage for further learning and specialization in the fields of data analysis and data engineering.

Requirements

Basic Computer Literacy: Students should be comfortable using a computer, including file management and installing software.

Fundamental Math Skills: Familiarity with basic high school level mathematics is helpful, especially in areas such as statistics, since this course involves understanding and performing data analyses.

Basic Programming Knowledge: While not a strict prerequisite, having a general understanding of programming concepts like variables, loops, and functions can make learning SQL more accessible.

Understanding of Database Concepts: A basic understanding of databases, tables, and relationships is helpful but not required. The course will cover these topics in the early modules.

Software Requirements: Students will need to have a computer with internet access. Certain modules may require the installation of free database software (guidance will be provided during the course).

Motivation and Willingness to Learn: Above all, students should have an eagerness to learn new concepts and the commitment to practice and apply those skills through exercises and a capstone project.

Please note that this course is designed to accommodate students with varying levels of previous experience, and it covers all necessary foundational topics. Hence, anyone with a keen interest in data engineering and data analysis can benefit from the course, regardless of their background.

Description

The SQL Data Engineer/Data Analyst course is a comprehensive learning experience that equips students with the skills to leverage SQL's powerful features in real-world data engineering and data analysis scenarios. This course offers an in-depth exploration of SQL, extending from the basics to advanced concepts, and including essential topics like NoSQL, Big Data technologies, and data visualization.This course begins by introducing students to the roles and responsibilities of data analysts and data engineers, emphasizing the significance of SQL in these professions. It familiarizes students with a variety of SQL databases such as MS SQL, MySQL, PostgreSQL, and Oracle SQL. Gradually, we delve into the fundamentals of SQL, including its syntax, data types, operators, and expressions, and the common SQL statements used to manipulate data in databases.We then advance to more complex SQL concepts like functions, joins, subqueries, views, indexes, and constraints. Students will have an opportunity to master the art of writing sophisticated SQL queries, and manage databases effectively. This includes learning essential data cleaning techniques and understanding the import and export of data, as well as backup and restoration of databases.The course also places a special focus on using SQL for data analysis. It covers topics like descriptive statistics, group by, having and order by clauses, window functions, and other advanced SQL techniques used in data analysis. Simultaneously, it sheds light on using SQL for data engineering tasks, such as designing databases, handling ETL processes, and managing large datasets.Moreover, the curriculum explores SQL-like queries in NoSQL databases, helping students gain a broader understanding of the data ecosystem. It provides an introduction to Big Data technologies like Hadoop and Spark and shows how SQL interfaces with these tools. As visualization is crucial in data analysis, the course outlines how to use SQL with popular data visualization tools like Tableau and PowerBI.Finally, students learn SQL best practices and performance optimization strategies, ensuring that they not only write functional SQL queries but also write efficient and secure ones.The culmination of the course is a capstone project, which allows students to apply their acquired knowledge and skills to a real-world data problem, demonstrating their proficiency in using SQL for data engineering and data analysis.This course is designed for anyone looking to upskill in the field of data analysis and data engineering. With a blend of theoretical lessons, practical exercises, and quizzes, students will gain hands-on experience and in-depth knowledge of SQL, enabling them to succeed in their professional careers.

Overview

Section 1: Introduction to Data Analysis and Data Engineering

Lecture 1 Introduction

Lecture 2 Definition and Roles of a Data Analyst and Data Engineer

Lecture 3 Importance of SQL in Data Analysis and Data Engineering

Lecture 4 Introduction to Databases and SQL

Lecture 5 SQL Databases: MS SQL, MySQL, PostgreSQL, Oracle SQL

Lecture 6 Basic Database Concepts

Section 2: Relational Databases Setup

Lecture 7 Note on database downloads

Lecture 8 SQL Server Editions

Lecture 9 Download MS SQL Server

Lecture 10 Install MS SQL Server

Lecture 11 Install SQL Server Management Studio - SSMS

Lecture 12 Connect SSMS to MS SQL Server

Lecture 13 Restore sample database to MS SQL Server

Lecture 14 MySQL Database Server Installation on Windows

Lecture 15 MySQL Database Server Installation on Mac

Lecture 16 Introduction to MySQL Workbench

Lecture 17 Installing MySQL Workbench on Mac

Lecture 18 Installing PostgreSQL on Windows

Lecture 19 Installing PostgreSQL on Mac

Lecture 20 Installing PgAdmin for PostgreSQL on Mac

Lecture 21 Connect PgAdmin to PostgreSQL Database Server

Lecture 22 Restore sample database to PostgreSQL Database Server

Lecture 23 Download Oracle Database Server

Lecture 24 Install Oracle Database Server

Lecture 25 What is SQLPlus

Lecture 26 Connect SQLPLus to Oracle

Lecture 27 Create a new database user in Oracle with SQLPlus

Lecture 28 Create a new table in Oracle with SQLPlus

Lecture 29 What is Oracle SQL Developer

Lecture 30 Download Oracle SQL Developer

Lecture 31 Connect SQL Developer to Oracle

Lecture 32 What are Schemas

Lecture 33 Download Sample Oracle Schemas

Lecture 34 Unlock sample hr schema account

Lecture 35 Connect sample schema account to Oracle

Lecture 36 Unlock sample schema tables

Section 3: SQL Fundamentals

Lecture 37 SQL Syntax

Lecture 38 SQL Data Types

Lecture 39 SQL Data Types Operations

Lecture 40 SQL Operators

Lecture 41 SQL Expressions

Section 4: SQL Syntax - Performing CRUD Operations

Lecture 42 What is CRUD

Lecture 43 Create a database in multiple systems

Lecture 44 Examples of CRUD in multiple systems

Lecture 45 What is T-SQL

Lecture 46 Creating a database object

Lecture 47 Creating a table object

Lecture 48 Perform a Create Operation ( Inserting Data)

Lecture 49 Perform a Read Operation

Lecture 50 Perform Update Operation

Lecture 51 Perform a Delete Operation

Section 5: Manipulating Data with SQL Functions

Lecture 52 Introduction

Lecture 53 STRING Functions

Lecture 54 CONCAT() Function

Lecture 55 CHARACTER LENGTH Function

Lecture 56 Examples of using String Functions

Lecture 57 Conversion Functions

Lecture 58 Examples of conversion functions

Lecture 59 Date Functions

Lecture 60 Examples of using Date Functions

Lecture 61 T-SQL CASE Expression

Lecture 62 T-SQL SUBSTRING Function

Lecture 63 T-SQL CONVERT Function

Lecture 64 T-SQL CAST Function

Section 6: SQL Joins and Subqueries

Lecture 65 SQL Joins

Lecture 66 LEFT JOIN

Lecture 67 INNER JOIN

Lecture 68 RIGHT JOIN

Lecture 69 SELF JOIN

Lecture 70 What is a Subquery

Lecture 71 Nested subqueries

Lecture 72 SQL Views

Lecture 73 Query Views

Lecture 74 SQL Indexes

Lecture 75 SQL Constraints

Section 7: Working with Data in SQL

Lecture 76 Data Cleaning in SQL

Lecture 77 SQL Data Cleaning Examples

Lecture 78 Importing and Exporting Data

Lecture 79 Backing Up and Restoring Databases

Lecture 80 Backup MySQL Databases

Lecture 81 Restore MySQL Databases

Lecture 82 Transaction Control: COMMIT, ROLLBACK, SAVEPOINT

Section 8: Data Analysis and Descriptive Statistics in SQL

Lecture 83 Introduction to Descriptive Statistics in SQL

Lecture 84 Aggregate Functions

Lecture 85 COUNT() Aggregate Function

Lecture 86 SUM() Aggregate Function

Lecture 87 AVG () Aggregate Function

Lecture 88 MIN() Aggregate Function

Lecture 89 MAX() Aggregate Function

Lecture 90 Group By, Having, and Order By Clauses

Lecture 91 Advanced SQL techniques for data analysis: PIVOT, UNPIVOT, CUBE, ROLLUP, etc.

Section 9: Data Analysis using: SQL window and Analytic functions

Lecture 92 Introduction to Windows Functions

Lecture 93 Introduction to Analytic Functions

Lecture 94 Introduction to Ranking Functions

Lecture 95 Basic Syntax for Analytic Functions

Lecture 96 Note

Lecture 97 RANK() Function

Lecture 98 DENSE_RANK() Function

Lecture 99 ROW_NUMBER() Function

Lecture 100 LAG() Function

Lecture 101 LEAD() Function

Lecture 102 FIRST_VALUE() Function

Lecture 103 LAST_VALUE() Function

Lecture 104 NTH_VALUE() Function

Lecture 105 Using Multiple Ranking Functions

Section 10: SQL for Data Engineering

Lecture 106 Designing and Building Databases

Lecture 107 Data modeling

Lecture 108 Database Design Principles

Lecture 109 SQL Data Definition Language (DDL)

Lecture 110 Designing Tables and Relationships:

Lecture 111 Indexing for Performance

Lecture 112 Building a Database

Lecture 113 SQL for ETL (Extract, Transform, Load) Processes

Lecture 114 Normalization and denormalization

Lecture 115 Handling Large Datasets: Partitioning, Sharding, Indexing Strategies

Lecture 116 Partitioning, Sharding, Indexing Strategies

Section 11: Working with NoSQL

Lecture 117 Introduction to NoSQL databases

Lecture 118 Difference between SQL and NoSQL

Lecture 119 SQL-like Queries in NoSQL (e.g., MongoDB Query Language)

Section 12: Introduction to Big Data Technologies

Lecture 120 Introduction to Hadoop and Spark

Lecture 121 SQL with Big Data: Hive and SparkSQL

Lecture 122 Real-time processing with Stream SQL

Section 13: Introduction to Data Visualization

Lecture 123 Basics of Data Visualization

Lecture 124 Tableau Public Desktop

Lecture 125 Tableau Public Desktop Overview : Part 1

Lecture 126 Tableau Public Desktop Overview : Part 2

Lecture 127 Tableau Online

Lecture 128 Tableau Data Sources

Lecture 129 What is Power BI Desktop

Lecture 130 Install Power BI Desktop

Lecture 131 Explore Power BI Desktop Interface

Lecture 132 SQL with Data Visualization Tools: Tableau, PowerBI

Section 14: SQL Best Practices | Performance Optimization | Triggers | Stored Procedures

Lecture 133 SQL Best Practices and Performance Optimization

Lecture 134 SQL best practices and performance optimization examples with Oracle Database

Lecture 135 SQL Security Best Practices

Lecture 136 Introduction to triggers using PostgreSQL Database

Lecture 137 Creating your first trigger - part 1

Lecture 138 Creating your first trigger - part 2

Lecture 139 Creating your first trigger - part 3

Lecture 140 Managing triggers

Lecture 141 Stored Procedures

Section 15: Capstone Project

Lecture 142 Real-world data engineering and data analysis project using SQL

Lecture 143 Project Approach (High-level overview.)

Lecture 144 Project Steps

Aspiring Data Analysts and Data Engineers: Individuals aiming to start their career in the field of data analysis or data engineering will find this course particularly beneficial. It provides foundational and advanced knowledge of SQL, which is a crucial skill in these professions.,Current IT Professionals: IT professionals who are currently in different roles (like software development, system analysis, or project management) and wish to transition into data-oriented roles would find this course a strong stepping stone.,Data Science Enthusiasts: People interested in data science can use this course as a pathway to further learning. Understanding SQL and how databases work is often a key skillset for data scientists.,Business Analysts and Managers: Professionals who deal with data regularly and need to extract insights from databases will gain significantly from this course. Being able to write SQL queries and perform data analysis independently can be a great asset in decision-making roles.,Students Studying Computer Science or Related Fields: Students in undergraduate or graduate programs related to computer science, information systems, or data analysis could use this course to complement their academic knowledge and gain practical, industry-relevant skills.,Anyone Interested in Data: In today's world, data skills are highly valuable across various fields. Anyone with a curiosity about data and how it drives decision-making would benefit from understanding SQL and its applications.