Duration: 5 days – 35 hrs.
Overview
This comprehensive training program combines two essential IT skills – SQL (Structured Query Language) and Python programming. Participants will gain a strong foundation in working with databases, data management, and programming using Python. Whether you’re a beginner or looking to enhance your existing skills, this course is designed to provide you with the knowledge and hands-on experience needed to excel in the IT industry.
Objectives
- Write simple scripts in Python
- Write Complex SQL Statements
- Clean and process data
- Analyze Data in both SQL and Dataframes
- Properly Visualize data using Python
Audience
- IT Professionals: IT personnel, including developers, system administrators, and network engineers, who want to enhance their database management and programming capabilities.
- Data Analysts: Professionals who work with data and want to gain proficiency in using SQL for data retrieval and Python for data analysis and visualization.
- Software Developers: Developers looking to expand their programming skills to include Python and improve their database interaction using SQL.
- Database Administrators: DBAs who wish to strengthen their SQL skills for database maintenance, optimization, and management.
- Web Developers: Web developers interested in using Python for web development and integrating databases with web applications.
- Business Analysts: Individuals involved in data-driven decision-making who want to use SQL and Python for data manipulation and reporting.
- Students: Students pursuing degrees in computer science, data science, or related fields who want to gain practical skills in SQL and Python.
- Entrepreneurs and Startups: Business owners or startup founders who need to manage data, build software, or develop web applications and want to do it themselves.
- Professionals in Transition: People changing careers or job roles and see SQL and Python as valuable skills for their new path.
- Anyone Interested in IT: Individuals with a general interest in IT and a desire to learn SQL and Python, regardless of their current profession.
Pre- requisites
- No prior knowledge of SQL or Python is required. However, basic computer skills and a willingness to learn are recommended.
Course Content
| Topic | Coverage | Est. Time |
| Day 1: SQL Basics | ||
| Basic Concepts of SQL | T-SQL Statements:
Data Types |
1 HR |
| Basic SELECT Statements | SELECT, Limiting Rows, Unique Values Filtering Aliasing, Sorting | 1.5 HRS |
| Aggregating Data | Count, Sum, Min. Max. Average. Grouping | 1.5 HRS |
| Combining Tables | Database Normalization, Wide Data vs Long Data, Self Joins, Cross Joins, LEFT / RIGHT/ INNER / OUTER JOINs, UNION | 1.5 HR |
| Activity 1 |
|
1.5 HRS |
| Day 2: Python for Beginners | ||
| Introduction to Python | Brief background on Python, Introduction to tools and workspaces, Python syntax, and real-life application | 1 HR |
| My First Python Script | Input, Output, Comments, Variables, Comments, Type Casting, String Operator, and Mathematical Operators | 1.5 HRS |
| Conditional Statements and Loops | Logical Operators, IF statements, nested IF statements, Switch Statements, While Loop, For Loops, Nested Loops and Breaks | 1 HR |
| Other Useful Applications | Libraries, Functions, Sets, Local & Global Variables, Lists, Dictionaries, Exception Handling and Reading Files | 1.5 HRS |
| Activity 2 | Build a simple game using python | 2 HRS |
| Day 3: Advance SQL | ||
| Querying from Memory | Subqueries, Common Table Expressions, Temporary Tables, Pivot and Views | 1.5 HRS |
| Activity 3 | Re-do Activity 1 with CTE. Subqueries, etc. | 1 HR |
| Window Functions and Native Functions | Case Statements, Date Functions, Cast, Conversion, String Operators, Mathematical Operators, Coalesce, NULLIF, | 1.5 HRS |
| Activity 4 | Data Cleansing with SQL | 1 HR |
| Loading, Appending, Removing and Updating Data | Importing Data Sets, Inserting Records, Delete Records, Updating Records, and Exporting Records | 1 HR |
| Activity 5 | Load Data Set and Analyze Data Set | 1 HR |
| Day 4: PANDAS | ||
| Loading Data | Introduction to Numpy, Pandas; Loading a data frame dictionaries, lists,excel and CSV;; limiting rows; data frame data types; Sorting and Filtering Dataframes, | 2 HRS |
| Activity 6 | Create a data frame using dictionaries and reading files then review them by sorting and filtering | 1 HR |
| Simple Aggregation and Visualization | Aggregating and Merging Dataframes; Visualizing Data Frames with Python | 2 HRS |
| Activity 7 | Analyzing Data Using Data Frames using CSV records and visualizing them | 2 HRS |
| Day 5: Combination of SQL and Pandas | ||
| Dataframes and Databases | Connecting Databases and Dataframes; Pivot using Python; Optimizing SQL Scripts (Explain) and Query Structure | 1.5 HRS |
| Activity 8 | Re-do Activity 7 but the data source is in a database | 1.5 HRS |
| Review | Review all concepts discussed | 1 HR |
| Project | Test the skills and concepts of students by analyzing four data sets | 3 HRS |
** Notes:
- In addition to Python and SQL, students will also be using github to upload their individual projects.
- After every topic, students will be given a quiz to test their understanding of the topic. Sample questions are as follows:
| A. What are the total sales of Brand X for February 2023 in Pesos?
Data Set:
… |
||||||||||||
| B. You are about to sort the Brand in Alphabetical Order. Complete the query below:
SELECT Brand, Sales, Year from schema.table ___________ |
||||||||||||
| C. If you run the query below, will it result in an error?
SELECT [Brand], “Sales”, Year from table |
- For learning purposes, Jupyter notebook will be used for Python and Azure Notebook for SQL. In the event Azure is unavailable, we will use third party IDEs.
- Instructor will provision a database for activity 8 and the final project.
