Introduction to SQL Programming

---

Overview

This two-day workshop is designed to provide the participant with a solid understanding of the database concepts and the basics of the SQL language. The course is taught by a highly experienced consultant with substantial real-world experience in using SQL programming to facilitate Crystal Reports design projects.

---

Audience

Participants with little or no SQL experience.

---

Duration

2 days

---

Proposed Syllabus

SQL Overview

• Outlining the importance of SQL as the cornerstone of all database activity
• Describing the fundamental building blocks: tables, columns

Building the Database Schema

• Building tables with CREATE TABLE
• Modifying table structure with ALTER TABLE
• Adding columns to an existing table
• Increasing column size
• Removing tables with DROP TABLE

Manipulating Data

• Adding table rows with INSERT
• Changing row content with UPDATE
• Removing rows with DELETE

Writing Single Table Queries

• Retrieving data with SELECT
• Including columns and expressions in query results
• Restricting rows with the WHERE filter
• Sorting the result with ORDER BY
• Handling NULL values in expressions
• Avoiding NULL value pitfalls in filter conditions

Using Aggregate Functions

• Summarizing data using SUM, AVG and COUNT
• Finding the highest and lowest values with MAX and MIN
• Defining the summary level with GROUP BY
• Applying filter conditions with HAVING

Querying Multiple Tables

• Matching related rows with INNER JOIN
• Including non-matched rows with OUTER JOIN

Constructing Nested Queries

• Correlated vs. non-correlated subqueries
• Testing the existence of rows

Understanding and Writing Views

• Creating Views
• Advantages of Views

Understanding and Writing Stored Procedures

• Advantages of stored Procedures
• Manipulating text strings
• Converting date/time presentation
• Conditional formatting with the CASE expression
• Utilizing the CASE expression to simulate IF tests
• Using temporary tables
• Solving mathematical problems with functions