Introduction to SQL
Overview
Through hands-on labs, students will explore the SQL standard, giving users an understanding of the SQL Language. The course introduces the concepts of database design and data modeling. Students will be able to distinguish between DDL statements and DML statements and their uses in building queries. Standard SQL operators and functions will be used to learn how to filter queries. Students will learn how to use joins, set operations, and sub-queries to gather information distributed among several tables.
This course provides a foundation for anyone looking to delve deeper into the world of databases.
Duration
4 days
Who Should Take This Course
Audience
This course is suitable for application designers and developers, database administrators and operators, and end users.
Prerequisites
A strong desire, passion and commitment to learn SQL and database fundamentals. Students should have basic computer literacy including use of a browser and a basic text editor such as Atom.
Course Outline
Introduction to SQL
Database Design
• Introduction to Databases
• The Relational Data Model
• Introductory Relational Database Terminology
• Codd’s 12 Rules
• Normalization
• First Normal Form
• Second Normal Form
• Third Normal Forms
Data Modeling
• The Entity Relationship Model
• Entity Relationship Diagrams
• Entity Relationship Diagrams (1:1)
• Entity Relationship Diagrams (1:M)
• Entity Relationship Diagrams (M:N)
• A Sample Database
Using MySQL
• Introduction to MySQL
• SQL
• The MySQL Command Line Interface
• Getting Help
• MySQL Workbench
• Navigating MySQL Workbench
• Creating a Database and Sample Tables
• Displaying Information About Tables
Data Definition Language
• Categories of SQL Statements
• SQL Data Types
• The CREATE Statement
• The DROP Statement
• The ENUM Data Type
• The ALTER Statement
• Integrity Constraints
• Domain Integrity Constraints
• Creating a Table with Domain Constraints
• Entity Integrity Constraints
• Referential Integrity Constraints
• Altering a Table’s Constraints
Data Manipulation Language
• DML Statements
• The SELECT Statement
• The ORDER BY Clause
• The INSERT Statement
• The DELETE Statement
• The UPDATE Statement
Transaction Control
• Transactions
• The ROLLBACK Statement
• The COMMIT Statement
• The SAVEPOINT Statement
SQL Operators
• Comparison Operators
• IN and NOT IN Operators
• BETWEEN Operator
• The LIKE Operator
• Logical Operators
• IS NULL and IS NOT NULL
• CASE Statements
SQL Functions
• Introduction
• The DISTINCT Keyword
• Aliases
• Miscellaneous Functions
• Mathematical Functions
• String Functions
• Date Functions
Joining Tables
• Joins
• Cross Join
• Inner joins
• Equi-Join
• Non-Equi Join
• Non-Key Join
• Self Join
• Natural Join
• The USING Clause
• Outer Joins
• Right Outer Join
• Left Outer Join
Set Operators
• Introduction
• Selection Criteria
• Creating Sample Data
• Union
• Union All
SQL Subqueries
• Introduction
• Using a Sub-query with a DML Statement
• Typical Sub-queries
• Sub-query Operators
• Standard vs. Correlated Sub-queries
• Correlated Sub-query Example
• Predicate Operators
Groups
• SQL Statements
• GROUP BY Clause
• HAVING Clause
• Order of Clauses in a SELECT Statement
Stored Procedures
• Objectives
• Creating Stored Procedures
• Executing Stored Procedures
• Stored Procedures with Parameters
• Dropping Stored Procedures
More Database Objects
• More Database Objects
• Relational Views
• Updating a View
• Indexes