SQL Fundamentals

Course number: CGISQLF40

Students will learn SQL from the ground up, gaining the skills to write queries, filter and sort results, combine data from multiple tables, and manipulate output for analysis or reporting. Students will then build on foundational skills to write advanced queries, structure and index tables, and control how data is stored and manipulated. Real-world exercises provide guidance through every step so that students can confidently retrieve information from a relational database, use subqueries, manage views, and execute transactions.

Course objectives:

After completing this course, students will be equipped to use SQL to extract and manipulate data from relational databases. Students will gain practical experience writing queries, formatting results, and applying functions using SQL Server. Students will also be able to perform advanced SQL operations on relational databases to retrieve, manipulate, and secure data more effectively.

Run basic SQL statements to retrieve data
Apply conditions and logical filters using WHERE clauses
Use string, numeric, and date functions for calculated results
Organize and group query output
Join tables and identify missing or unmatched records
Write subqueries and multi-level subqueries
Insert, update, and delete data in tables
Create and modify table structures and constraints
Create views and perform data operations through views
Create and manage indices for optimized performance
Use transactions to manage changes with commit and rollback

Prerequisites

Students should have basic computer skills, SQL skills, and be familiar with concepts related to database structure and terminology.

Course Outline

Module 1: Executing Basic SQL Statements

Connect to a SQL Server database
Create, modify, and execute queries
Save and reopen query files

Module 2: Filtering with Conditional Logic

Limit results using comparison operators
Combine multiple conditions
Search for ranges, NULL values, and patterns

Module 3: Using Built-In SQL Functions

Perform date and time calculations
Manipulate strings and text fields
Calculate summaries with aggregate functions

Module 4: Structuring and Summarizing Output

Sort results and apply custom rankings
Group data and filter grouped rows
Pivot and unpivot results for advanced formatting

Module 5: Working Across Tables

Merge data with inner and outer joins
Identify records without matches
Compare and combine multiple result sets

Module 6: Exporting Results and Creating Reports

Format and save query results
Generate basic XML output
Review common reporting use cases

Module 7: Using Subqueries to Perform Advanced Querying

Search Based on Unknown Values
Compare a Value with Unknown Values
Search Based on the Existence of Records
Generate Output Using Correlated Subqueries
Filter Grouped Data Within Subqueries
Perform Multiple-Level Subqueries

Module 8: Manipulating Table Data

Insert Data
Modify and Delete Data

Module 9: Manipulating the Table Structure

Create a Simple Table
Create a Table with Constraints
Add or Drop Table Columns
Add or Drop Constraints
Modify the Column Definition
Back Up Tables
Delete Tables

Module 10: Working with Views

Create a View
Manipulate Data in Views
Create Aliases
Modify and Drop Views

Module 11: Indexing Data

Create Indices
Drop Indices

Module 12: Managing Transactions

Create Transactions
Commit Transactions

Available Formats

Live Online

To use reCAPTCHA you must get an API key from https://www.google.com/recaptcha/admin