SQL Fundamental in SQL Server

SQL Fundamental in SQL Server Course Outline

Module 1:- Introduction to Basic Database Concepts

In this module we learn about Basic concepts and advantages of DBMS and limitations of file management system, and also about 3 data base models

  • What is Data, Field, Record and database?
  • Limitations of File Management System.
  • Basic Concepts of Advantages of DBMS.
  • Exploring Relational DBMS
  • Understanding Client and Server

Module 2: E-R Modeling and Diagram

In this module we learn about entity, attributes and relationship , identify the entities and attributes How to draw a E-R diagram and translating the E-R diagram in relation schema.

  • Analyzing the Requirement
  • Identify Entities and their Relationships
  • Drawing E-R Diagram
  • Conversion of E.R. Diagrams into Tables

Module 3: Normalization

In this module we learn about what is normalization, types of normalization, data before and after normalization, benefits of normalization.

  • First Normal Form
  • Second Normal Form
  • Third Normal Form Practically Normalizing Tables

Module 4: Introduction to SQL Server

In this module we learn about SQL Server, history of sql server ,types of system databases, communication between frontend and backend and sql server editions.

  • What is SQL Server Version history and different editions
  • Basic Features Components and Tools
  • Starting and Stopping SQL Server Instances / Services
  • Introduction to Management Studio
  • Types of System Databases in SQL

Module 5: Introduction to SQL

In this module we learn about types of sql statements, databases in sql server, how to create a database, datatypes in sql server, and about DDL Statements.

  • Basics of SQL Types of SQL Statements
  • DDL, DML, DQL, DCL and TCL
  • Create Database using Management Studio
  • Datatypes in SQL Server
  • Exploring DDL Statements on Table using Management Studio

Module 6: DDL and DML Statements

In this module we learn about how to create a table,alter and drop a table ,and about DML statements, like insert update and delete statements.

  • Why write statements in Frontends?
  • Create, Alter and Drop Table Insert,
  • Update and Delete Statement Truncate Statement

Module 7: Working with Queries (DQL)

In this module we learn about select statement, top, distinct string and arithmetic expressions, Sorting the data and about sub queries and where clause(codition).

  • Understanding Select Statement
  • Usage of Top, Distinct, Null etc…keywords
  • Using String and Arithmetic Expressions
  • Exploring Where Clause with Operators
  • Using Advanced Operators
  • Sorting data using Order By clause
  • Working with basic of Sub Queries

Module 8: Aggregate Functions

In this module we learn about how to use aggregate functions like sum,mean,max,avg what is difference between having and where clause, group by clause rollup and cube operator.

  • Using functions in Queries
  • Count, Sum, Min, Max, Avg Group By and Having Clause
  • Using Group By with Rollup and Cube

Module 9: Joins and Set Operations

In this module we will know about joins and types of joins how to join the tables and about Sub queries, types of operators like union ,intersect and except and how to add the tables and relationship between them.

  • Introduction to Joins Cross Joins
  • Inner Join
  • Outer Join
  • Self Join
  • Co-related Sub Queries
  • Set Operations using Unions, Intersect and Except

Module 10: Working with Constraints

In this module, we will learn about how to create a constraint,types of constraints,and difference between unique, not null and primary key constraints.

  • Unique
  • Not NULL
  • Primary Key
  • Default Check Foreign Key

Module 11: Implementing Views

In this module, how to create a view, advantages of views, altering and dropping a view

And advanced options while creating a view.

  • Introduction & Advantages of Views
  • Creating, Altering, Dropping Views
  • Advance Options while Creating a View
  • SQL Server Catalogue Views

Module 12: Data Control language (DCL)

In this module, we will learn how to grant permission and revoking of roles.

  • Creating Users & Roles
  • Granting & Revoking of Roles & privileges
  • Managing using Management Studio

Module 13: Working with Indexes

In this module, how to create a index, advantages and disadvantages of index, and types of index and dropping index, and also about index structure.

  • Introduction Clustered and Non Clustered Index
  • Creating and Dropping Indexes

Module 14: Introduction to Transact-SQL (Procedure and Functions)

In this module, what is script and what is variables and if else statements and working with temporary tables, and checking with the existence of database objects. We will also learn how to create a stored procedure and difference between stored procedure and functions, advantages of procedures and about types of parameters.

  • What is T-SQL?
  • Using System Functions
  • Using Dynamic SQL
  • Introduction to stored procedures
  • Benefits of Stored Procedures
  • Creating, Executing Modifying, Dropping
  • Working with Temp tables
  • Error Handling

SQL SERVER

Microsoft SQL SERVER

SQL Server 2008 Architecture and Configuration

SQL server editions
SQL Server Metadata
Components of the SQL Server Engine
The SQLOS
The Scheduler
Memory
SQL Server Resource Governor
SQL Server 2008 Configuration
SQL Server System Configuration
SQL Server Configuration Settings

Change Tracking, Tracing and Extended Events

The Basics: Triggers and Event Notifications
Change Tracking
Tracing and Profiling
Extended Events

Databases and Database Files

System Databases
Sample Databases
Database Files
Creating a Database
Expanding or Shrinking a Database
Using Database Filegroups
Altering a Database
Databases Under the Hood
Setting Database Options
Database Snapshots
The tempdb Database
Database Security
Moving or Copying a Database
Compatibility Levels

Logging and Recovery

Transaction Log Basics
Changes in Log Size
Backing Up and Restoring a Database

Tables

Creating Tables
User-Defined Data Types
IDENTITY Property
Internal Storage
Constraints
Altering a Table
Heap Modification Internals

Indexes: Internals and Management

Overview
Tools for Analyzing Indexes
Understanding Index Structures
Index Creation Options
Physical Index Structures
Special Index Structures
Data Modification Internals
Managing Index Structures

Special Storage

Large Object Storage
Filestream Data
Sparse Columns
Data Compression
Table and Index Partitioning

The Query Optimizer

Overview
What Is Optimization?
How the Query Optimizer Explores Query Plans
Optimizer Architecture
Statistics, Cardinality Estimation, and Costing
Index Selection
Partitioned Tables
Data Warehousing
Updates
Distributed Query
Extended Indexes
Plan Hinting

Plan Caching and Recompilation

The Plan Cache
Caching Mechanisms
Plan Cache Internals
Objects in Plan Cache: The Big Picture
Multiple Plans in Cache
When to Use Stored Procedures and Other Caching Mechanisms
Troubleshooting Plan Cache Issues

Transactions and Concurrency

Concurrency Models
Transaction Processing
Locking
Lock Compatibility
Internal Locking Architecture
Row-Level Locking vs. Page-Level Locking
Row Versioning
Controlling Locking

DBCC Internals

Getting a Consistent View of the Database
Processing the Database Effi ciently
Primitive System Catalog Consistency Checks
Allocation Consistency Checks
Per-Table Logical Consistency Checks
Cross-Table Consistency Checks
DBCC CHECKDB Output
DBCC CHECKDB Options
Database Repair
Consistency-Checking Commands Other Than
DBCC CHECKDB