LOGO For Immediate Assistance Call 855-211-9361

1-3 day (9 HOUR TRAINING):

Fundamentals of Relational Databases

Price:

Call for availability 855-211-9361

This Capstone series will teach the fundamentals of database design, and provide an education in Structured Query Language (SQL), the standard language used to access database structure and data. Any modern Web application built today uses a back-end database, usually a SQL-based tool. Learn the fundamentals of SQL, along with an introduction to database design, which will allow you to work with any database when building your Web applications. We will be using MySQL as the basis for this class, a popular open-source product that supports ANSI SQL.

Course Overview

By the end of the class students will be able to:

  • Read and understand database model diagrams & database vocabulary
  • Use Select, Insert, Update, and Delete to view and modify data
  • Query data simultaneously from multiple, related tables
  • Create, edit, and drop tables - Design efficient database structures
  • Ensure data integrity and quality -Enforce business logic and rules
  • Design sub-queries and correlated sub-queries
  • Use unions to combine queries into a single result set & Tune queries for performance
  • Apply views to shield users from underlying data complexity -Execute triggers and use stored procedures

Course Outline

  • Introduction: Databases and the Web - Overview of SQL & Relational Databases

    • Databases Overview - The File System
    • Databases and the ACID Test - The Role of SQL
    • SQL History and Major DBMS vendors
    • Queries and Recordsets -Tables
    • Primary Keys, Foreign Keys, and Relationships
  • Retrieving Data with SELECT - Data Manipulation: INSERT, UPDATE, DELETE – UPDATE & DELETE Statements -

    • Comparison Operators for the WHERE Clause
    • Apostrophes in Database Text
    • Calculated Columns: Doing Math in Select Statements
    • Select Distinct -Selecting All Columns
    • INSERT Statements
  • Data Retrieval: More About SELECT - Fine-Tuning Aggregate Functions - Querying Data Across Tables -

    • ORDER BY -Aggregate Functions -Extended WHERE Conditions
    • GROUP BY -The HAVING Clause
    • Basic Joins
    • Reusing Queries as Views - Query Within A Query
    • Subqueries
  • Database Definition SQL - Database Design- Establishing a Primary Key -

    • CREATE TABLE
    • Exercise: Creating a Table for a Guest Book
    • Data Modeling -Handling more than one phone number
    • Exercise: Adding Columns with ALTER TABLE
    • Auto-numbered Columns
  • Relationships Between Tables - Restructuring Tables - Data Integrity: Controlling What Goes Into a Database

    • SNormalization
    • Online Transaction Processing vs. Decision Support
    • Renaming Tables or Columns -Adding or Deleting Columns
    • Default values -Check Constraints
    • Creating Foreign Keys
  • Many-to-Many Relationships - Introduction to Transactions - Appendix A: The Camp sales Database

    • Tying It All Together: Creating The Movies Database
    • Diagram of the relationships among the tables
    • Table Data Types - Sample Data (Pre-Exercises)
    • Major SQL Statements
    • Advanced SQL and Database Design
  • Entity-Relationship Diagrams -Text Functions - A Review of Aggregate Functions

    • S Matching Patterns with LIKE
    • Text manipulation functions -Dates in SQL Server
    • Conversion Functions - Using Union to Combine Queries
    • Transact-SQL's CASE statement
    • GROUP BY - HAVING - ITH CUBE and WITH ROLLUP - COMPUTE BY
  • Querying Data Across Tables- Subqueries - Beginning Transact-SQL Programming - The Books Database

    • S Joins the ANSI-92 Way -Outer Joins -Self Joins - Correlated Subqueries
    • Performance Tuning -Connecting to Data in Other Databases
    • Introduction to Stored Procedures
    • Transactions in SQL Server -Triggers
    • Entity-Relationship Diagrams -Create Table Statements