SQL Query Writing & Fundamentals Course

Databases - An Overview

• Describe a Database
• Define a Database Management System (DBMS)
• List the Advantages of a DBMS
• Define a Relational Database Management System (RDBMS)
• Describe a Table
• Explain Data Types
• Describe Data Relationships
• Explain Primary and Foreign Keys
• Define Normalization
• back to top

SQL - An Overview

• Define what is SQL
• Explain the Role of SQL
• List the Functions of SQL
• Understand SQL Components
  • Data Manipulation Language (DML)
  • Data Definition Language (DDL)
  • Data Control Language (DCL)
• back to top

Getting Started with Microsoft SQL Server

• Understand the Features of Microsoft SQL Server
• Work with the command-line SQL CMD utility
• Building and Executing Queries
• Executing SQL Statements from a saved file
• Saving Output to a File
• Understand what is the SQL Server Management Studio
• Use SQL Server Management Studio for scripting
• Use Auto-generating SQL Statements
• Use Templates to Write and Execute SQL Statements
• back to top

Retrieving data

• Introduce the SELECT statement
• Use the SELECT statement
• Execute a query in Microsoft SQL Server
• Retrieve specific columns in a table
• Sorting Results with ORDER BY
• Filter rows with the WHERE clause
• Use operators to create conditions
• Retrieve unknown values from the database
• Understand the NULL keyword
• Sort the Result Set with the ORDER BY clause
• Use the DISTINCT keyword to suppress duplicate rows
• View top rows in a large result set
• Use the LIKE keyword with wildcards to retrieve data
• Work with calculated columns
• back to top

Working with Multi-table Queries

• Understand what are Joins
• Use different types of Joins
  • Inner Joins
  • Outer Joins
• Creating Joins with the FROM Clause
• Using the WHERE clause to create a join
• back to top

Using SQL Functions in Queries

• Understanding Functions and Function Arguments
• Use Aggregate Functions:
  • SUM, AVG, MIN, MAX and COUNT
• Use Mathematical Functions:
  • ROUND, CEILING, FLOOR, SQUARE, SQRT, and SIGN
• Use Date Functions:
  • GETDATE, DAY, MONTH and YEAR
• Use String Functions:
  • LEN, SUBSTRING, REPLACE, LTRIM and RTRIM
• Use Conversion Functions:
  • CAST, CONVERT, STR
• back to top

Grouping Data

• Create a new table
• Using the CREATE statement
• Modify the structure of an existing table
• Using the ALTER statement
• Delete a table
• Using the DROP statement
• Create a database
• Delete an existing database
• back to top

Create, Modify and Delete Tables and Databases

• Create a new table
• Using the CREATE statement
• Modify the structure of an existing table
• Using the ALTER statement
• Delete a table
• Using the DROP statement
• Create a database
• Delete an existing database
• back to top

Manipulating data in a Table

• Insert rows into a table
• Using the INSERT INTO statement
• Update existing rows in a table
• Using the UPDATE INTO statement
• Delete rows from a table
• Using the DELETE statement
• Delete all rows from a table with the TRUNCATE statement
• back to top

Working with Views and Stored Procedures

• Understand and describe what is a View
• Using the CREATE VIEW statement
• Modifying a View with the ALTER VIEW statement
• Deleting a View with the DROP VIEW statement
• Understand and create an Indexed View
• Understand and describe what is a Stored Procedure
• Understand what are parameters in a Stored Procedure
• Create a Stored Procedure with the CREATE PROCEDURE statement
• Execute a Stored Procedure with the EXECUTE statement
• Modify a Stored Procedure with the ALTER PROCEDURE statement
• Delete a Stored Procedure with the DROP PROCEDURE statement
• back to top