Subscribe and Access : 5200+ FREE Videos and 21+ Subjects Like CRT, SoftSkills, JAVA, Hadoop, Microsoft .NET, Testing Tools etc..

Batch Date: July 21st @7:00PM

Faculty: Mr. V. Venkata Prasad (17+ Yrs of Exp,..)

Duration: 40 Days

Venue :
DURGA SOFTWARE SOLUTIONS,
Flat No : 202, 2nd Floor,
HUDA Maitrivanam,
Ameerpet, Hyderabad - 500038

Ph.No: +91- 8885252627, 9246212143, 80 96 96 96 96

Syllabus:

SQL SERVER

Introduction to SQL Server

• Understanding Data vs. Information
• What is a Database?
• Introduction to DBMS and RDBMS
• Basics of Database Design
• Types of Databases
• Client-Server Architecture
• SQL Server Versions Overview
• Installing & Navigating SQL Server Management Studio (SSMS)

Getting Started with SQL Server

• Creating and Managing Databases
• T-SQL Overview and Categories:
  • DDL (Data Definition Language)
  • DML (Data Manipulation Language)
  • TCL (Transaction Control Language)
  • DCL (Data Control Language)
  • DQL (Data Query Language)

Creating and Managing Tables

• Creating Tables and Table Structures
• Insert, Update, and Delete Operations
• Normalization Concepts (1NF to 3NF)
• Applying Constraints:
  • PRIMARY KEY
  • FOREIGN KEY
  • UNIQUE
  • NOT NULL
  • DEFAULT
  • CHECK

Querying Data (SELECT Statements)

• SELECT Statement Syntax and Execution Flow
• Filtering Data with WHERE
• Sorting Data using ORDER BY
• Removing Duplicates with DISTINCT
• Using TOP Clause
• Operators in SQL Server:
  • Arithmetic, Comparison, Logical, BETWEEN, IN, LIKE, etc.

DML and DDL Commands

• DML:
  • INSERT, UPDATE, DELETE
  • Copying Data Between Tables
• DDL:
  • CREATE, ALTER, DROP, TRUNCATE
  • DELETE vs TRUNCATE (Differences and Use Cases)

SQL Server Built-in Functions

Scalar Functions:

• String Functions (LEN, SUBSTRING, REPLACE, etc.)
• Date Functions (GETDATE, EOMONTH, DATEFROMPARTS, etc.)
• Mathematical Functions (ROUND, FLOOR, CEILING)
• Conversion Functions (CAST, CONVERT)
• Logical/Conditional Functions (COALESCE, ISNULL, IIF)
• Ranking & Row Functions (ROW_NUMBER, RANK, DENSE_RANK)
• Running Total (Using Window Functions)

Group (Aggregate) Functions:

• COUNT(), SUM(), AVG(), MAX(), MIN()
• Deterministic vs Non-Deterministic Functions

Advanced Queries

• SET Operators: UNION, INTERSECT, EXCEPT
• Subqueries:
  • Single-row and Multi-row Subqueries
  • Correlated vs Non-correlated
  • Nested Queries and Derived Tables

JOINS in SQL Server

• Importance of Joins
• Types of Joins:
• INNER JOIN
• LEFT, RIGHT OUTER JOIN
• FULL OUTER JOIN
• SELF JOIN
• CROSS JOIN
• Advanced Join Use Cases

Transactions and Locking

• Understanding Transactions
• COMMIT and ROLLBACK Statements
• ACID Properties
• Locks in SQL Server:
• Shared, Exclusive, Update Locks
• Viewing and Resolving Deadlocks

Database Security

• Database Security Concepts
• Privileges and Permissions
• GRANT, REVOKE Statements
• User Management and Roles

Temporary Tables

• Creating Local and Global Temporary Tables
• Scope and Lifetime of Temp Tables

Views

• Introduction to Views
• Simple and Complex Views
• Updating Data via Views
• Advantages and Limitations of Views

Indexes

• Introduction to Indexing
• Clustered vs Non-Clustered Index
• Creating and Managing Indexes
• Index Performance Considerations

Stored Procedures

• Creating and Executing Stored Procedures
• Parameters and Return Values
• Benefits and Best Practices

Triggers

• Introduction to Triggers
• AFTER and INSTEAD OF Triggers
• Use Cases and Limitations

Cursors and Schemas

• Introduction to Cursors
• Cursor Lifecycle: DECLARE, OPEN, FETCH, CLOSE, DEALLOCATE
• Using Schemas for Data Organization

SQL Server Agent

• Automating Tasks with SQL Server Agent
• Creating Jobs and Schedules
• Alerts and Notifications

Advanced SQL Server Concepts

• Common Table Expressions (CTEs)
• Data Import/Export

Backup and Restore

• Importance of Backup Strategies
• Types of Backups: Full, Differential, Transaction Log
• Restoring Databases using SSMS and T-SQL