SQL Scripting Fundamentals Training

Defining New Tables with SQL

Building a new database starts with creating a table to organize your data. In this section, you'll learn how to use the CREATE TABLE command to build a table with exactly the column names and column data types you plan to store in your database. You will also choose a "primary key" or a unique data element to identify each record (or row of data). After completing this section of our SQL training, you'll know how to:

• Specify appropriate column names for your categories of data
• Select data types for the different types of data you want to store, such as text or numbers
• Set effective "primary keys" to make sure your database entries are unique

Populate Tables with Data

Once you've created a database structure with tables, you'll want to get your data into the database by properly using the INSERT INTO command. We show you how to create records by specifying which data element should fall into which field or column within a record. You will learn how to:

• Write a correct INSERT INTO statement
• Specify the fields into which to insert specific data

Change Data Using UPDATE and WHERE Commands

Part of keeping data effective is keeping it current. You can update and make changes to the data in your database using the UPDATE and WHERE commands. We will teach you the tricks of how to update specific fields, either in all records in a table, or only in specific records. You will also learn the different ways to compose statements (the SQL syntax) for different data types such as text and numbers. This section of the course covers how to:

• Change data in specific fields using the UPDATE and WHERE commands
• Update all records or specific records in a table
• Learn the appropriate syntax for updating different data types
• Use the primary key with the WHERE statement to effect targeted record updates

Removing Records from Database Tables

Keeping data current also means removing records that are no longer relevant. Here is where you learn to use the DELETE command to either delete all records in a table or delete specific records. We'll also show you how to use the WHERE statement in conjunction with DELETE to remove only records that match certain criteria. This section of the course builds your expertise in how to:

• Use the DELETE command to remove all or selected records from a table
• Handle data conditions that prevent the removal of records

Retrieving Data Using the SELECT Command

By far, the most common SQL command is SELECT, which queries the database for specific pieces of information. In this section, we teach you how to combine SELECT with WHERE and ORDER statements to narrow your queries to specific fields and values, and sort your results meaningfully. You will learn how to:

• Construct a SELECT command to query specific data from your tables
• Use the WHERE statement to limit the queried fields and values
• Use the ORDER BY statement to sort the data displayed by a SELECT query

Writing SQL Functions

If you think SQL ends with SELECT, think again. You can create your own SQL functions, such as Math, String, Date and Data Set-related functions that return calculated values based on your data. For example, you can invoke SQR() to find the square root of a number, SUM() to compute the sum of two or more numbers, and DATE() to return a date. We'll show you how to work with functions in different types of databases, which typically use different syntaxes. After this section, you'll know when and how to:

• Use Math, String, Date and Data Set-related functions
• Write functions to calculate values based on your data set
• Write functions that return values without being applied to a data set

Creating Stored Procedures

In this module, you continue building your proficiency as a SQL programmer by mastering stored procedures. Stored procedures are like macros that execute a series of SQL statements. You can even set up flexible procedures to evaluate different input parameters and calculate the output depending on the input conditions. Stored procedures save time, because they are compiled once and can then be run repeatedly as pre-compiled executables. After completing this section, you'll access the full power of SQL to:

• Create a procedure that does your work for you
• Execute a stored procedure

Creating Triggers

One of the tricks of the SQL trade, triggers are like pre-programmed stored procedures you can invoke as an automatic response to INSERT, UPDATE, and DELETE SQL statements. Triggers are often used for audit purposes, for example to create an audit record every time a new employee is added to the database. We'll show you exactly how to:

• Track database additions with a FOR INSERT trigger
• Track database changes with a FOR UPDATE trigger
• Track database removals with a FOR DELETE trigger

Prerequisites:

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

SQL training is offered at four locations::

• MySQL training San Francisco
  
• MySQL training Los Angeles
  
• MySQL training San Jose
• MySQL training Sacramento