SQL / JavaScript Web App

Description

This SQL / JavaScript / Web Course is for anyone who wants to get into the world of Database Developers or Business Intelligence . Create web-enabled apps with a database back-end using Node.JS and MySql

Prerequisites

Intro To Programming with JavaScript recommended, however if you did not do it, you might find a strong need to do it after this course in order to build more web-apps.

Intended Audience

• This SQL / Javascript web training course is for anyone who wants to get into the world of Database Developers / Administrators or Business Intelligence and wants the ability to develop small / medium web-enabled database systems with reporting .
• Some familiarity with spreadsheets, relational databases and basic SQL might be helpful. Some coding experience might be helpful.
• Anyone interested in learning SQL to help with their current job or to find a new role. SQL is one of today’s most in-demand skills
• Anyone looking to answer complex business problems and generate reports using data
• Business owners, people in sales, or people in marketing roles who are interested in understanding company data better.
• Developers who are looking to build applications (e.g. social network, ecommerce site) using MySQL

After this course you should

• Know SQL syntax
• Know SQL database design concepts
• Know advanced querying
• Know how to do hard core database manipulation with Subqueries, Joins and Transactions
• Understand effective and efficient database design and creation
• Be able to query, normalize and join data
• Be ready to prepare for the MTA DBA Fundamentals Exam 98-364
• Create your own database or interact with existing databases
• Write complex SQL queries across multiple tables
• Build a web app using NodeJS and MySQL
• Model real-world data and generate reports using SQL
• Answer company performance or sales questions using data

Alignment

• Microsoft MTA DBA Fundamentals Exam 98-364 (MTA Exam not included in course price) – please ask for study material if you want to pursue this exam
• w3schools.com/sql

Further Training

• Consider doing our Advanced SQL Querying or DB Design

Course Material

• Course material included (electronic)

Course Contents

DAY 1

• Introduction to SQL
• Getting Started: Overview and Installation
  • What Is a Database?
  • SQL Vs. MySQL
  • Installation Overview
  • Cloud9 Registration
  • Setting Up Cloud
  • Exploring The Cloud9 Interface
  • Coud9 Interface Challenge
  • Installing MySQL on Cloud
  • Your First MySQL Activity
  • Mac Installation
  • Windows Installation
• Creating Databases and Tables
  • Creating Databases
  • Dropping Databases
  • Using Databases
  • Introduction to Tables
  • The Basic Datatypes
  • Note about INT max size
  • Basic Datatypes Challenge
  • Creating Your Own Tables
  • How Do We Know It Worked?
  • Dropping Tables
  • Creating Your Own Tables Challenge
• Inserting Data (and a couple other things)
  • Inserting Data
  • Super Quick Intro To SELECT
  • Multiple INSERT
  • INSERT Challenges
  • MySQL Warnings
  • NULL and NOT_NULL
  • Setting Default Values
  • Setting Default Values
  • A Primer On Primary Keys
  • Table Constraints

DAY 2

• CRUD commands
  • Introduction
  • Preparing Our Data
  • Introduction to SELECT
  • Introduction to WHERE
  • Aliases
  • The UPDATE Command
  • Introduction to DELETE
• CRUD Challenge Section
• The World Of String Functions
  • Running SQL Files
  • Loading Our Book Data
  • Working with CONCAT
  • Introducing SUBSTRING
  • Introducing REPLACE
  • Using REVERSE
  • Working with CHAR LENGTH
  • Changing Case with UPPER and LOWER
  • Note about string functions
  • String Function Challenges
• Refining Selections
  • Seed Data: Adding A Couple New Books
  • Using DISTINCT
  • Sorting Data with ORDER BY
  • Using LIMIT
  • Better Searches with LIKE
  • LIKE Part 2: More Wildcards

DAY 3

• Aggregate Functions
  • The Count Function
  • The Power of Group By
  • Min and Max Basics
  • SUBQUERIES – Shortcomings of Min and Max
  • Using Min and Max with Group By
  • The Sum Function
  • The Avg Function
  • Aggregate Functions Challenges
• Revisiting Data Types
  • CHAR and VARCHAR
  • DECIMAL
  • FLOAT and DOUBLE
  • Setting the timezone in Cloud9
  • DATE, TIME, and DATETIME
  • Creating Our DATE data
  • CURDATE, CURTIME, and NOW
  • Note about formatting dates
  • Formatting Dates
  • Date Math
  • Working with TIMESTAMPS
• Logical Operators
  • Not Equal
  • Not Like
  • Greater Than
  • Less Than
  • Logical AND
  • Logical OR
  • Between
  • In And Not In
  • Case Statements
• One To Many
  • Types of Data Relationships
  •  One To Many: The Basics
  • Working With Foreign Keys
  • Cross Join
  • Inner Joins
  • Left Join
  • Right Joins
  • Right and Left Joins: A Common Question

DAY 4

• Many To Many
  • Basics
  • Creating Our Tables
  • 7 Joins Exercises
• Social Media Database Example
  • Users Schema
  • Photos Schema
  • Comments Schema
  • Likes Schema
  • Followers Schema
  • Hashtags Schema
• Working With Lots Of Social Media Data
  • Loading lots of data
  • 7 Exercises

DAY 5

• Node.js
  • MySQL and Other Languages
  • BUT WHAT ABOUT PHP
  • Introduction to JOIN US app
  • Setting Up Cloud9 for Node
  • 5 Minute NodeJS Crash Course
  • Note about Faker install
  • Introduction to NPM and Faker
  • Introducing the MySQL Package
  • Connecting Node to MySQL
  • Creating The Users Table
  • Selecting Using Node
  • Inserting Using Node
  • Some MySQL/Node Magic
  • Bulk Inserting 500 Users
• Building Our Web App with Express JavaScript Framework
  • Introducing Express
  • NPM Init and package.json files
  • Our First Simple Web App
  • Adding Multiple Routes
  • Connecting Express and MySQL
  • Adding EJS Templates
  • Connecting The Form
  • Styling with HTML/CSS
• Database Triggers for the Web App
  • Introduction to Database Triggers
  • Writing Our First Trigger
  • Preventing Social Self-Follows With Triggers
  • Creating Logger Triggers
  • Managing Triggers And A Warning

Duration and pricing

• In Price Group A

Certificate

Read about our certificates

Questions

Please email us

Schedule for Johannesburg

On the calendar on this page below.
If your browser doesn’t display the calendar below, please click on this link or try using Google Chrome, alternatively please enquire via our ‘Contact Us’ page.

Download as pdfPrint this page