Menu Close

SQL SERVER

SQL stands for Structured Query Language. SQL Server is a database software given by the Microsoft or more specifically it is a Relational Database Management System (RDBMS). Many enterprise software programs and websites use this database software for storing and retrieving digital data.

This course is meant for everyone who would like to visualize how information gets stored and delivered to end users and a solid understanding of the Oracle products used in professional job roles.

  • Application Developer
  • Database Developer
  • Support Analyst

Candidates intending to learn a strong database using Microsoft technologies.

Concepts of DBMS
• Introduction to DBMS
• Data Models
• HDBMS, NDBMS, RDBMS, ORDBMS
• Entity–Relationship (E-R)
• Normalization
Introduction to SQL Server
• Advantages & limitations
• Login & Password
• Server Type & Server Name
• Authentication Modes
– SQL Server, Windows
• SQL Server Management Studio & Tools
• Object Explorer
• Query Editor
Introduction To SQL
• Types of SQL Commands
• Data Types
• DDL, DML, DQL, DCL, TCL
• Databases
– Create / Alter / Drop
• Tables
– Create / Alter / Drop
Table Constraints
• Not Null
• Unique
• Default
• Check
• Primary Key
• R eferential Integrity or foreign key
Data Manipulation Language (DML)
• Insert / Update / Delete
• Truncate & Drop
• Create a Table From Another Table
• Insert Rows From One Table To Another
Data Query Language (DQL)
• Simple Select
• Select with row filters
• Where clause with relational operators
• Logical operators
• Special operators
• Between … and
• In, Like
• Is Null, Isnull()
• Order By Clause
• Distinct Keyword
• Column aliases
Transaction Management
• User & Server schema
• Implicit Transactions
• Begin / Save Transaction
• Commit / Rollback
• Role of Log File in Transaction
Management
Built In Functions
• Numeric/Character
• Date/Conversion/Identity
• Aggregate Functions
– sum(), avg(), etc.
• Group By & Having Clauses
• Ranking Functions
• Top Clause
Set Operators
• Union, Union all ,Intersect
Joins
• Simple
• Equi / Non-Equi
• Natural, Self
• Inner, Outer
• Cross or Cartesian Join
Sub Queries
• Single / Multi Row
• Any, Some, ALL
• Exists & Not Exists
• Nested
• Correlated
Indexes
• Create, Alter & Drop
• Performance variation
• Composite
• Clustered / Non-Clustered
DBA Activities
• Authenticted Login
– SQL Server/Windows
• Create / Alter / Drop users
• Granting & Revoking Permissions
Views
• Create / Alter / Drop Views
• Simple, Complex Views
• Indexed & Partition views
• Encryption & Schema Binding Options
TSQL Programming
• SQL versus TSQL Programming
• Introduction
• Control statements – If,Case
• While & other looping statements
Cursors
• Types of Cursors
• Forward_Only, Scroll
• Static, Dynamic
• Keyset
• Local & Global
Stored Procedures
• Creating/Calling Stored Procedures
• Altering & Dropping
• Optional, Input, Output Parameters
• Permissions on Stored Procedures
Exception Handling
• User Defined Error Messages
– Add & Remove
• Raising Exceptions Manual
User Defined Functions
• Scalar Functions
• Table Valued Functions
• Permissions on User Defined Functions
Triggers
• Stored Procedures VS
User Defined Functions VS Triggers
• Creating Triggers
• Altering Triggers
• Dropping Triggers
• Magic Tables
• Instead of Triggers
CLR Integration
• What is CLR Integration?
• Implementing CLR
• A Simple Example
Working with XML Data Type
Database – Backup, Restore
– Import, Export
– Attach, Detach