Learning Outcomes

Installing SQL Server 2019 Express

Installing SQL Server Management Studio

Downloading the AdventureWorks Sample Database

Course Environment Walkthrough

Lesson Summary

In this module, we review some of the essential details regarding programming in SQL. A guide on how to install SQL Server 2019 and SQL server management studio shall be provided. We will also go through the process of downloading the AdventureWorks sample database. A walkthrough of the course environment shall also be provided during the module.

Introducing Window Functions With OVER

PARTITION BY

ROW_NUMBER Function

RANK and DENSE_RANK

LEAD and LAG

In this module, we will get an introduction to the window functions with OVER across an entire data set. We will also be going through a detailed walkthrough of functions such as PARTITION BY, ROW NUMBER, and RANK along with Lead and lag. A brief guide will also be provided on subqueries.

Introducing Subqueries

Scalar Subqueries

Correlated Subqueries

EXISTS function

FOR XML PATH with STUFF

PIVOT for Data

This module will be dealing with everything related to subqueries. We will be dissecting the concepts of scalar and correlated subqueries. We will also be taking a look at the application of the EXIST function. A walkthrough on how to flatten multiple rows into one using the FOR XML PATH will be provided along with the use of PIVOT.

Data Analysis Using CTE's

CTEs Walkthrough

Recursive CTE's

This module breaks down the process of performing complex analyses on your data in a step-by-step manner using common table expressions or CTEs. We will be taking a detailed look at how to easily break down queries in a simple and linear manner. We will also examine the use of recursion in order to generate a series of values in SQL.

Temp Tables 1 and 2

CREATE and INSERT

TRUNCATE Application

Modifying Tables Using UPDATE

Removing Rows Using DELETE

In this module, we will be taking a detailed look at a more flexible alternative to common table expressions known as the temp tables. We will also be examining the CREATE and INSERT functions to manage complex data structures. The TRUNCATE and UPDATE functions for clearing, using and modifying tables will be discussed.

Optimizing With UPDATE

An Improved EXISTS with UPDATE

Introduction to Indexes

Lookup Tables

This module will be taking a look at all the things associated with optimizing queries using the UPDATE statements. An improved version of EXISTS with UPDATE will also be examined during the module. We will be taking a detailed look at database objects known as indexes and the utility of lookup tables to make something permanent.

The Use of Variables

Introduction to User Defined Functions

Stored Procedures

Control Flow with IF Statements

Dynamic SQL Programming

This module will be starting off by taking a detailed look at variables which can help streamline code. We will be going through the use of user-defined functions for situations where built-in functions are not enough. The use and application of dynamic SQL will be discussed which helps prevent repetition when constructing long strings of code.

Diploma in Advanced SQL Server - Course assessment

Develop an in-depth understanding of the concepts and functions of an advanced SQL server with this free online course.

Diploma in Advanced SQL Server

In this course, we will be taking your basic database skills up a notch and providing you with a refined understanding of coding in SQL 2019. We will be going through essential coding concepts such as carrying out advanced analyses, using subqueries, common table expressions and data optimisation. All these techniques can help you become a more efficient programmer and take your skills and career to the next level. So enrol now to keep learning!

Core Database Concepts - Introduction

SQL Server Management Studio

Learn more about the fundamental concepts of database. You will be introduced to databases, SQL, DBMS and RDMS. You will also learn about SSQL Server Management Studio and how GUI can be used to manage an SQL Server, its databases, and the content contained within the databases.

Normalization

Referential Integrity

Constraints

In this module, you will study the relational concepts of database. You will begin by studying Normalization and you will see how the various normal forms impact upon the organization of data in a database. You will also study referential integrity and constraints.

Datatypes

Database Objects

DDL Statements

Learn about what a datatype is and then learn about some of the categories of datatypes. You will also learn about unicode, implicit and explicit datatypes before moving onto database objects and DDL statements.

SELECT Statement

SELECT Statement Demonstration

INSERT, UPDATE and DELETE Statements

In this module, you will be learning about Data Manipulation Language and you will be introduced to the SELECT statement. You will learn the significance of this statement and its applications in database. The INSERT, UPDATE and DELETE statements will also be discussed.

Securing Databases and Objects

Database Backups and Restores

Study the fundamentals of Sql Server Administration Fundamentals and learn about Securables, Principals and Permissions. You will learn about the three-tiered approach to accessing content and about how Databases and Objects are secured.

Diploma in Databases and T-SQL - First Assessment

You must score 80% or more to pass this assessment.

Introducing Transact-SQL - Course Introduction

Introduction to Transact-SQL

The SELECT Statement

Data Types and Working with NULL

In this module, you will be introduced to Transact-SQL and you will learn about Structured Query Language, Data Manipulation Language, Data Definition Language and Data Control Language.

Removing Duplicates and Sorting

Filtering and Using Predicates

In this module, you will learn how to query tables with select. You will look at removing duplicates and sorting as well as filtering and using predicates.

Join Concepts and Syntax

Inner Joins

Cross Joins

Self Joins

Union Queries

Intersect Queries and Except Queries

Outer Joins

This module will introduce you to Joins and Queries, you will learn about how to use a join and the various types of joins that are used.

Scalar Functions

Logical Functions

Window and Aggregate Functions

Group By and Filtering with Having

This module will introduce you to various functions such as Scalar Functions and Logical Functions. You will also gain a greater understanding of GROUP BY and Filtering with HAVING.

Subqueries

APPLY Operator

Views

Temporary Tables and Table Variables

Table Valued Functions

Derived Tables

Common Table Expressions

Grouping Sets

Pivoting Data

This module will introduce you to Subqueries as well as showing you how certain table expressions work. You will also learn more about Grouping Sets and Pivoting Data.

Inserting Data

Update and Delete

Batches, Comments and Variables

Conditional Branching and Looping

In this module, you will learn more about the practicalities of working with data. You will learn about how to input data and also about how to update and delete data in a table. You will also study Batches, Comments and Variables and take a look at Conditional Branching and Looping.

Error Message and Raising Errors

Catching and Handling Errors

Transactions

In this module, you will learn about Error Messages and Raising Errors. Following on from this, you will study how errors are caught and handled and you will learn about Transactions.

Diploma in Databases and T-SQL - Second Assessment

Diploma in Databases and T-SQL - Assessment

Learn about the core concepts of database including the structure by studying this free Databases and T-SQL course.

Diploma in Databases and T-SQL

This free online Databases and T-SQL course will teach you the core concepts of databases and their structure. It will teach you how to retrieve data from a database using Structured Query Language (SQL) and Microsoft's variant Transact-SQL (T-SQL). You will learn about queries and the different constraints and clauses you can include in them. You will also study administrative tasks you can perform on a database.

Introduction to Database Concepts  - Final Assessment

Learn the core concepts of relational databases for storing data with this free online Database Concepts course.

Introduction to Database Concepts

This free online Introduction to Database Concepts course will teach you the process of storing, managing, and retrieving information in a database. A database is an organized collection of data in rows and columns, typically stored in tables. These tables are then stored on database servers that host the database files. With this course, you will learn how databases can provide high-level performance for a diverse set of users.

Databases - DML Statements and SQL Server Administration - Final Assessment

Learn about DML Statements and database security using SQL Server Administration with this databases course.

Databases - DML Statements and SQL Server Administration

This free online databases course will teach you about DML Statements and SQL Server Administration. You will learn how to use statements to retrieve, modify, and delete data from a database, perform administrative tasks such as adding security and backing up your database using SQL Server Administration, and add users to a database. You will also look into the different levels of security available with SQL Server Administration, and more.

SQL Introduction and Installation

Beginner SQL Queries

Intermediate SQL and Advanced Operators

This module analyses database design and architecture, SQL installation, notation for database schema, and the differences between procedural and declarative data manipulation languages. Learn how to use SQL servers and perform basic MySQL queries. We will also consider SQL data types and how to use basic, intermediate, and advanced SQL operators.

SQL for Beginners - Course Assessment

This free online course will teach new users about the architecture, database schema, and operators used in SQL queries.

SQL for Beginners

SQL is a user-friendly language used to create, implement, modify, and structure a database. Its features include large data retrieval, faster query processing, and multiple data views. This online course will introduce you to basic database design and architecture. You will learn to use primary, intermediate, and advanced SQL operators for backend data manipulation in your applications. Take this course to sharpen your SQL skills. Enroll now!