SQL Database Design and Querying Training Course | Code College


Postgres SQL Training Course

Learn the fundamentals of SQL Databases and SQL Querying, and how to apply it practically



Duration

  • 10 Days Morning lectures

What do I need?

  • Webinar : A laptop, and a stable internet connection. The recommended minimum speed is around 10 Mbps.
  • Classroom Training : A laptop, please notify us if you are not bringing your own laptop. Please see the calendar below for the schedule

Certification

  • Attendance : If you have attended 80% of the sessions and completed all the class work, you qualify for the Attendance Certificate. (Course Price : R14 995 based on minimum of 4 students)
  • Competency : Only offered as part of a Coding Bootcamp
  • You may apply for credits in NQF level 5 but these are ordered seperataly as it incurs additional processing

Pre-requisites

  • None - this is one of our entry-level courses

Delivery

  • In-Person (Woodmead Classroom)
  • Remote (Discord Webinar)

Who will benefit

  • School leavers intending to do one of our Coding Bootcamps.
  • You have been using spreadsheets, but are getting ready for something bigger and more sustainable, a database!
  • Junior Developers should know SQL and databases - you cannot develop software without a solid data base foundation
  • Management / Statisticians that want to get empowered by being able to learn how to use data productively

What you will learn

  • Understand Tables and how to design them, maintain them and query them
  • How to query single and multiple tables with the SELECT statement
  • The DELETE and UPDATE statements and how to use them safely
  • ALTER - how to modify existing tables and databases
  • Advanced querying functions like GROUP By etc. and when are they useful
  • Statistic Basics
  • Data Analysis Fundamentals
  • Subqueries - when to use and not
  • Outer Joins - when they are useful
  • Constraints - why do we need them
  • UML - how to design a normalised database with UML
  • Forward Engineering - how to forward engineer a schema from a UML diagram
  • Reverse Engineering - how to reverse engineer a UML Diagram from an existing schema

Content



Day 1 and 2

Setting Up

  • Databases
  • Tables
  • Populating Databases and Tables
  • Reading Data with SELECT statements
  • The WHERE clause
  • Data types in SQL
  • Importing data into your database
  • Exporting data from your database
  • Math Operators
  • Aggregate Functions

    • Day 3 and 4

  • Median
  • Mode
  • Relational Database Design
  • Table Relationships
  • Join Types to join related tables
  • Aliases
  • Table Naming Conventions
  • Constraints
  • Query performance
  • Using indexes to speed up performance
  • Capstone: Using a public census database, produce summarised reports using Max, min, count and GROUP BY

    • Day 5 and 6

  • Inspecting Datasets to find missing, malformed or inconsistent data
  • Modifying Data, Tables and Columns
  • Using Transactions to save and revert back
  • Statistical funtions: Correlation with corr(X,Y)
  • Statistical funtions: Predicting values with Regression Analysis
  • Creating Rankings with rank() and dense_rank
  • Manipulating dates and times
  • Working with time zones

    • Day 7 and 8

  • Subqueries
  • CTE's
  • CASE
  • Text mining for meaningful data
  • Text Patterns and Regex
  • Full Text Search
  • Spatial Data with PostGIS
  • Two-Dimensional Geometries

    • Day 9 and 10

  • Creating Spatial Objects with PostGIS Functions
  • GiST indexes
  • Performing Spatial Joins
  • Views, Functions and Triggers
  • SQL from the command line
  • Database maintenance
  • Database management

    • Calendar