Copyright © 2011 Micrologica Systems Limited.

IT Training
Micrologica Systems Limited

0800 064 2765

This Topic List is indicative of course coverage but does not indicate delivery sequencing or relative importance of the topics.

Database Fundamentals

What is a Database Management System (DBMS) ?

The Relational Database Management System (RDBMS)  

Tables

Data Types

Data Relationships

Primary and Foreign Keys

Normalization

 

Accessing Databases

The client/server model

Understand how databases are used with applications

Understand how databases are used with the web

 

Structured Query Language

What is Structured Query Language - SQL ?

Data Definition Language (DDL)

Data Manipulation Language (DML)

Data Control Language (DCL)

 

Getting Started with Microsoft SQL Server Management Studio

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 Templates to Write and Execute SQL Statements

 

Other Database Workbench-type Editors

Multi-vendor capabilities

Reverse Engineering

Data Transfer

 

Create, Modify and Delete Tables and Databases

Create a new table using the CREATE statement

Modify the structure of an existing table

The ALTER statement

Delete a column or table: the DROP statement

 Manipulating data in a Table

INSERT INTO statement

Using the UPDATE statement

Delete rows from a table: the DELETE statement

(Delete all rows from a table with TRUNCATE TABLE)

Retrieving data

The SELECT statement

Sorting Results with ORDER BY

Filter rows with the WHERE clause

Use operators to create conditions

Understand the NULL keyword

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
 

Grouping Data

GROUP BY

HAVING

 

Working with Multi-table Queries

Joins

Inner Joins

Outer Joins

Creating Joins with the FROM Clause

Using the WHERE clause to create a join

 

Using SQL Functions in Queries

Aggregate Functions:  SUM, AVG, MIN, MAX and COUNT

Mathematical Functions:  ROUND, CEILING, FLOOR, SQRT, RAND, POWER, (SQUARE, SIGN)

Date Functions:  CURRENT_TIMESTAMP (GETDATE, GETUTCDATE, DATEPART,) DAY, MONTH and YEAR

String Functions:  CHARACTER_LENGTH (LEN, DATALENGTH), SUBSTRING, TRIM, (REPLACE, LTRIM and RTRIM)

Conversion Functions:  CAST, (CONVERT, STR )

 

Working with Views and Stored Procedures

Understanding Views

The CREATE VIEW statement

ALTER VIEW

DROP VIEW  

Create an Indexed View

What is a Stored Procedure?

Parameters in Stored Procedures

CREATE PROCEDURE  

The EXECUTE statement

ALTER PROCEDURE

DROP PROCEDURE