Fundamentals of Relational Databases using MySQL

$2,000.00


  • Virtual Classroom

  • Onsite

← Continue Searching

Duration: 5 Days

In this 5-day course, you'll learn the principles of database design and get an education in Structured Query Language (SQL), the standard language used to access database structure and data. Today's web applications use a back-end database - usually an SQL-based tool. Using MySQL, an open-source product popular in the industry, you'll learn the fundamentals of SQL, allowing you to work with any database when building your web applications.

What You Will Learn

 

  • Read and understand database model diagrams
  • Database vocabulary
  • 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 your queries for performance
  • Apply views to shield users from underlying data complexity
  • Execute triggers and use stored procedures

Audience

 

Those who need to understand relational databases and how they are designed and implemented.

Prerequistes

 

Course Outline

 

1. Introduction: Databases and the Web

  • Overview
  • The File System
  • Databases and the ACID Test

2. An Overview of SQL

  • The Role of SQL
  • SQL History and Major DBMS Vendors

3. Relational Databases\

  • Queries and Recordsets

4. More about the Parts of Relational Databases

  • Tables
  • Primary Keys, Foreign Keys, and Relationships

5. Retrieving Data with SELECT

  • Comparison Operators for the WHERE Clause
  • Apostrophes in Database Text
  • Calculated Columns: Doing Math in Select Statements
  • Select Distinct
  • Selecting All Columns

6. Data Manipulation: INSERT, UPDATE, DELETE

  • INSERT Statements

7. UPDATE Statements

 

8. DELETE Statements

 

9. Data Retrieval: More About

  • SELECT ORDER BY
  • Aggregate Functions
  • Extended WHERE Conditions

10. Fine-Tuning Aggregate Functions

  • GROUP BY
  • The HAVING Clause
  • Exercise 9: Using GROUP BY and HAVING to Analyze Sales Data

11. Querying Data Across Tables

  • Basic Joins

12. Reusing Queries as Views

 

13. Query Within A Query

  • Subqueries

14. Database Definition SQL

  • CREATE TABLE

15. Database Design

  • Data Modeling
  • Handling More than One Phone Number

16. Establishing a Primary Key

  • Auto-numbered Columns

17. Relationships Between Tables

  • Normalization
  • Online Transaction Processing vs. Decision Support
  • Restructuring Tables
  • Renaming Tables or Columns
  • Adding or Deleting Columns

18. Data Integrity: Controlling What Goes Into a Database

  • Default Values
  • Check Constraints
  • Creating Foreign Keys

19. Many-to-Many Relationships

  • Tying It All Together: Creating The Movies Database

20. Introduction to Transactions

  • Diagram of the Relationships Among the Tables
  • Table Data Types

21. Major SQL Statements

 

22. Advanced SQL and Database Design

 

23. Entity-Relationship Diagrams

  • Text Functions
  • Matching Patterns with LIKE
  • Text Manipulation Functions
  • Dates in SQL Server
  • Conversion Functions
  • Using Union to Combine Queries
  • Transact-SQL's CASE Statement

24. A Review of Aggregate Functions

  • GROUP BY
  • HAVING
  • WITH CUBE and WITH ROLLUP
  • COMPUTE BY

25. Querying Data Across Tables

  • Joins the ANSI-92 Way
  • Outer Joins
  • Self Joins

26. Subqueries

  • Correlated Subqueries
  • Performance Tuning
  • Connecting to Data in Other Databases

27. Beginning Transact-SQL Programming\

  • Introduction to Stored Procedures
  • Transactions in SQL Server
  • Triggers

28. Entity-Relationship Diagrams

 

29. Create Table Statements

Course Labs