Description

This course introduces SQL Server and describes logical table design, indexing and query plans. It also focusses on the creation of database objects including views, stored procedures along with parameters, and functions. This course covers the other common aspects of procedure coding, such as indexes, concurrency, error handling and triggers. After completing this course you will be able introduce the entire SQL Server platform and its major tools, design and implement views, stored procedures, functions etc.

Syllabus

• Introduction to Basic Database Concepts
  • 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
• E-R modeling and Diagram
  • Analyzing the Requirement
  • Identify Entities and their Relationships
  • Drawing E-R Diagram
  • Conversion of ERD into Tables
• Normalization
  • First Normal Form
  • Second Normal Form
  • Third Normal Form Practically Normalizing Tables
  • BCNF

• Introduction to SQL Server
  • 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
• Introduction to SQL
  • 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
• DDL and DML Statements
  • Why write statements in frontends
  • Create, Alter and Drop Table Insert,
  • Update and Delete Statement Truncate Statement

• Working with Queries (DQL)
  • 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
• Aggregate Functions
  • Using functions in Queries
  • Using predefined function
  • Count, Sum, Min, Max, Avg Group By and Having Clause
  • Using Group By with Rollup and Cube
• Joins and Set Operations
  • Introduction to join
  • Inner Join
  • Outer Join
  • Self Join
  • co-related Sub Queries
  • Set Operations using Unions, Intersect and Except
• Implementation of Data integrity
  • Entity integrity
  • Domain integrity
  • Referential integrity
  • Types of constraints
• Working with Constraints
  • Unique
  • Not NULL
  • Primary Key
  • default Check Foreign Key
• Implementing Views
  • Introduction & Advantages of Views
  • Creating, Altering, Dropping Views
  • Advance Options while Creating a View
  • SQL Server Catalogue Views

• Data Control language (DCL)
  • Creating Users & Roles
  • Granting & Revoking of Roles & privileges
  • Managing using Management Studio
• Working with Indexes
  • Introduction Clustered and Non Clustered Index
  • Creating and Dropping Indexes
• Writing Transact-SQL(T-SQL)
  • What is T-SQL?
  • Scripts and Batches Declaring Variables
  • Using Statements
  • Working with Temp tables
  • Error Handling
  • Using System Functions / Global Variables Using Dynamic SQL
• Working with Stored Procedures and Functions
  • Introduction to stored procedures
  • Benefits of Stored Procedures
  • Creating, Executing Modifying, Dropping
  • Input-Output and Optional Parameters
  • System defined SP’s and Functions.
  • User defined Functions
• Implementing Triggers
  • Introduction to triggers
  • Constraints vs Triggers
  • Creating, Altering, Dropping triggers
  • for/after/instead of triggers
  • Using Rollback Tran
• Working with Cursors
  • Creating Cursors
  • Cursors vs. Select
  • Types of cursors
  • Locks on cursors
  • Advantages of cursors
• Transaction Control Language (TCL)
  • Introduction Transactions process
  • Types of transactions (Implicit, explicit)
  • Working with Locks, Types of locks