SQL Scripting Essentials

Note: For additional questions and clarification, you may reach Bill Ramirez at bill@dynamicsedge.com or on his following contact details: Office- (510) 804-3600 & Cell- (415) 200-6969

COURSE OVERVIEW:

SQL (short for “Structured Query Language”) is the standard language for accessing, inputting and manipulating data in relational databases. With an easy-to-follow English-based syntax, SQL scripting gives you the power to access data in a wide variety of databases, including MS Access, MySQL, Postgres, etc.  You will learn the skill of writing correct and effective SQL statements that manipulate and retrieve data from your database

PREREQUISITES:

• Experience with a desktop database such as Filemaker or MS Access

AGENDA TOPICS

Unit 1- Introduction to the Structured Query Language (SQL)?

• Relational Databases
• Basic SQL Keywords
• Tips for Planning Queries
• What People Do with Queries
• SQL Platforms

Unit 2- Data Selection

• Using SELECT Statements to Examine Database Tables
• Using Results to Figure Out Other Tables to Examine
• Narrowing Your SQL Statements Down to Fields You Need
• Sorting Records
• Using Field Aliasing
• Saving a SQL Statement

Unit 3- Retrieving Data From Multiple Tables

• Inner and Outer JOINS
• Table Aliasing
• Self JOINS

Unit 4- Use Criteria in SQL Statements

• Avoiding Ambigious Situations
• Using A Single Criterion to Limit Records
• AND/OR Situations in Criteria
• Criteria Keywords
• Using Comparison Criteria
• Using Wildcards

Unit 5- Generate Totals Using SQL

• Aggregate Keywords
• The GROUP BY Clause
• Using Criteria in Aggregate-based Queries

Unit 6- Generate Calculated Fields Using SQL

• Math Calculations
• Using SQL Keywords to Enhance the Display of Data
• DATE Calculations
• TEXT Calculations

Unit 7- Using UNIONS, SUBQUERIES, and More JOINS

• Using a WHERE Clause as an INNER JOIN
• Using UNIONS
• Using SUBQUERIES

Unit 8- Action Queries within SQL

• Using the INSERT Keyword
• Using the UPDATE Statements
• Using the DELETE Keyword

Unit 9- VIEWS

• Creating a VIEW to Simplify Data Retrieval
• Altering a VIEW
• Creating a VIEW to Restrict Accessbile Data
• Inserting Data Through a VIEW
• Delete VIEWS

Unit 10- Structure Databases using SQL

• Understanding DATA Types
• Creating Tables
• Altering Tables
• Deleting Tables

Unit 11- Working with Index

• Creating a Single-Column Index
• Creating a Multiple-Column Index
• Dropping an Index

Unit 12- Working with Conditional Queries

• Using an IF Statement
• Using a CASE Statement

Unit 13- Developing Common Tables Expressions

• Creating and Using Common Table Expressions (CTES)