Glory Technologies

Contact Number: +91-8008627755

Subject: SQL SERVER

Duration: 45 Days

Timings: 8am-9am

Chapter 1: About Database & Data Warehouse

  • What is Database?
  • What is Data Warehouse?
  • Difference between Database & Data Warehouse

Chapter 2: SQL Server Database Design

  • Database Creation using GUI
  • Database Creation using T-SQL Scripts
  • DB Design using Files
  • File locations and Size Parameters
  • DB Log Files, Growth and Placement
  • DB Structure Modifications & Ownership

Chapter 3: SQL Server Tables and T-SQL Queries

  • Table creation using T-SQL Scripts
  • DML Operations: INSERT, UPDATE, DELETE
  • Single Row and Multi-Row Insert Statements
  • Table Aliases, Column Aliases & Usage
  • Table creation with Schemas
  • DELETE versus TRUNCATE Statements
  • SELECT Queries and Sub Queries
  • Schemas and Nested Queries with Aliases

Chapter4: Constraints, Keys and Data Validation

 

  • Data Integrity and Normal Forms
  • Tables with Keys & Constraints
  • NULL and IDENTITY Properties – Usage
  • UNIQUE KEY Constraint and NOT NULL
  • PRIMARY KEY Constraint & Importance
  • FOREIGN KEY and REFERENCE Attributes
  • CHECK and DEFAULT Constraints, Usage

Chapter 5: Joins, Sub Queries and Nested Queries

  • SELECT Statements – IIF, SWITCH, CHOOSE
  • ORDER BY, GROUP BY, GROUPING, HAVING
  • Sub Queries, Nested Queries and EXISTS
  • DELETE from SELECT, UPDATE from SELECT
  • INSERT INTO … SELECT & Data Copy
  • Inner Joins – Purpose and Performance
  • Outer Joins – Types, Advantages and Usage
  • Cross Joins – Advantages and Limitations
  • Self Joins and Sub Queries

Chapter 6: Joins, Views and Nested Queries

  • Benefits of Views – End User Access
  • Defining Views on Tables and Views
  • SCHEMABINDING and Encryption Options
  • ALTER TABLE Issues and Solutions with Views
  • System Views & Management Views: Audits
  • Joins and Nested Sub Queries in Views

Chapter 7: User Defined Functions and Joins

  • Functions – Syntax, Usage, and Importance
  • Scalar-Valued Functions and Examples
  • Table Valued Functions and Examples
  • System Functions and Joins – CROSS APPLY
  • Date, Time and Conversion Functions
  • String Functions and Meta-data Functions
  • ROWCOUNT, GROUPING, ROLLUP Functions
  • Joining Functions and Tables / Views

Chapter 8: Indexes and Query Tuning

  • Need for Indexes, Index Types & Usage
  • Indexing Table & View Columns
  • Index SCAN AND SEEK
  • PRIMARY KEYS and Non-Clustered Indexes

Chapter 9: Stored Procedures and Benefits

  • Need for Stored Procedures & Syntax
  • Use of Variables and Query Parameters
  • Stored Procedures with Loops &IF… ELSE
  • SCHEMABINDING and ENCRYPTION Options
  • INPUT, OUTPUT & RESULTSET Parameters
  • System Stored Procedures and Nesting
  • Dynamic SQL Queries and Parameters
  • Stored Procedures, Data Validation Options
  • OUTPUT Parameters and Result set Options

Chapter 10: Triggers and Memory Limitations

  • Triggers – Architecture, Types & Usage
  • DML Triggers and Performance Impact
  • INSERTED and DELETED Memory Tables
  • Triggers for Audit Operations (Memory)

Chapter 11: Cursors, Stored Procedures, CTE

  • Cursor Declaration and Fetch Options
  • STATIC and DYNAMIC Cursor Types
  • SCROLL and FORWARD_ONLY Cursors
  • LOCAL and GLOBAL Cursors – Scope
  • KEYSET Cursors and Temp Objects
  • Embedding Cursors in SPs and Functions
  • Temp Tables and Procedures with Cursors

Chapter 12: Transactions & Procedures

  • Transaction Options and Stored Procedures
  • ACID Properties in T-SQL and Scope
  • EXPLICIT Transaction Types and Issues
  • IMPLICIT Transactions Types and Issues
  • AUTOCOMMIT Transaction and Advantages
  • Nested Points and Save Points usage
  • Query Blocking Issues and Query Hints
  • SPs with Transactions & Temp Objects

*******************************************************************************************

What is SQL Server?

SQL Server is a relational database management system (RDBMS) developed by Microsoft, designed to store, manage, and retrieve data efficiently. It uses Structured Query Language (SQL) to perform operations like querying, updating, and managing databases. SQL Server is widely used in enterprise environments for handling large-scale data operations, analytics, and reporting.

🧩 Key Features of SQL Server:

  • Relational Database Management: Organizes data into tables with rows and columns.
  • T-SQL (Transact-SQL): An extended version of SQL with additional programming constructs like variables, loops, and error handling.
  • Stored Procedures & Functions: Predefined queries for automating tasks.
  • Triggers: Automated responses to database events.
  • Views: Virtual tables created from query results.
  • Indexes: Speed up data retrieval operations.
  • Backup and Recovery: Ensures data protection and disaster recovery.
  • Security: Offers encryption, authentication, and authorization features.

🛠️ SQL Server Editions:

  • 🏢 Enterprise Edition: Full features for large organizations with high workloads.
  • 🏬 Standard Edition: Core features for medium-sized businesses.
  • 🛎️ Express Edition: Free, lightweight version for small-scale applications.
  • 💻 Developer Edition: Full features for development and testing (free).
  • 🌐 Azure SQL Database: Cloud-based version of SQL Server.