SQL Server Course Syllabus:
Microsoft SQL Server is one the most popular Relational Database Management System (RDBMS) used in Microsoft universe. It can be used for data storage as well as for data reading for applications which can be either on desktop or API/Browse.
Course Objectives: Learn about SQL – Structured Query Language
- Build database using Data Definition Language Statements Perform basic CRUD operations using Data Manipulation Language statements like Insert, Update and Delete Write and call Stored Procedures, Functions, Triggers, Cursors and Optimizations techniques in a database.
Pre-requisite / Target Audience:
This sql server course can be taken by any beginner who wants to build career in Information Technology. The subscriber needs to have working knowledge of Windows Operating System.
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, Files, File Groups and database?
- Limitations of File Management System.
- Basic Concepts and Advantages of DBMS.
- Exploring Relational DBMS (RDBMS)
- Understanding Client and Server Architecture
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
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, Drop and Truncate table statements,
- Insert, Update and Delete Statements
- Grant, revoke statements
- Commit, roll back, Save point statements
Module 7: Working with Constraints
In this module, we will learn about how to create a constraint, types of constraints, and difference between constraints.
- Primary Key
- Unique Key
- NOT NULL
- Composite Key
- Candidate Key
- Foreign key
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, ISNULL,IDENTITY etc…keywords
- Using String and Arithmetic Expressions
- Clauses (WHERE, GROUP BY,HAVING) with Operators
- Using Advanced Operators
- Sorting data using Order By clause and Offset and Fetch
- CASE Statement
Module 8: Aggregate Functions / String Functions / Date & Time 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 String functions in Queries
- Count, Sum, Min, Max, Avg Group by and Having Clauses
- Date time Functions
- Windows Functions / Analytical Functions
- with Rollup and Cube
- RANK Functions
Module 9: Set Operators
In this module we learn about how to write combination of Query’s result into single result set
- Introduction Set Operators
- UNION ALL
Module 10 : Sub Query’s
In this module we will learn about how we can write a query’s with in the same query and what are the different methods available
- Introduction of Subquery’s
- Co-Related Sub Query’s
- Nested Subquery’s
Module 11 : Joins
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 and types
- Inner Join / Left Outer Join / Right Outer Join
- Full Outer Join
- Self-Join, Cross Join and Cross apply
- Co-related Sub Queries
- Set Operations using Unions, Intersect and Except on top of the joins
Module 12 : Table Expressions / Temporary tables
In this module we learn about table expressions, how can we pass the query result into another table.
- Derived Tables
- Table variables
- CTE (Common Table Expressions)
- Temporary Tables (Local, Global)
- PIVOT / UNPIVOT
Module 13:Working with Indexes
In this module, how to create an index, advantages and disadvantages of index, and types of index and dropping index, and also about index structure.
- Heap structure vs B-Tree Structure Format
- Index Types
- Clustered Index
- Non-Clustered Index
- Filter Index
- Covering Index
- Column Store index
- Index Scan
- Index Seek
Module 14:Implementing Views
In this module, how to create a view,advantages of views, altering and dropping a View And advanced options while creating aview.
- Introduction & Advantages of Views
- Types of Views (Simple / Complex / Materialized)
- Creating, Altering, Dropping Views
- Advance Options while Creating a View
- SQL Server Catalogue Views (System Defined Views)
Module 15: Transactions
In this module we learn about the transaction types and level and locks about the tables and row levels.
- ACID Properties
- ISOLATION Levels