MICROSOFT SQL SERVER 2017

Introduction

What is SQL?

SQL Process

SQL Commands

RDBMS Concepts

Data, Database, Field, Record

What is RDBMS?

SQL Constraints

Data Integrity

Advantages of DBMS

Limitations of File Management System

Understanding Client and Server

E-R Modeling and Diagram

Analyzing the Requirement

Identify Entities and their Relationships

E-R Diagram Symbols

Drawing E-R Diagram

E-R Diagram uses

Conversion of E-R Diagrams into Tables

Normalization

Database Normalization

First Normal Form (1NF)

Second Normal Form (2NF)

Third Normal Form (3NF)

RDBMS Databases

MySQL

MS SQL Server

Oracle

MS Access

SQL Syntax

Various Syntax in SQL

Data Types

haracter and String Data Types

Numeric Data Types

Date and Time Data Types

Unicode Character String Data Types

Binary Data Types

Miscellaneous Data Types

Operators
What is an Operator in SQL?

Arithmetic Operators

Comparison Operators

Logical Operators

Expressions

Boolean Expressions

Numeric Expressions

Date Expressions

Constraints

NOT NULL Constraint

DEFAULT Constraint

UNIQUE Constraint

Primary Key

Foreign Key

CHECK Constraint

INDEX Constraint

Dropping Constraints

Integrity Constraints

SQL Queries

CREATE Database

DROP or DELETE Database

SELECT Database, USE Statement

CREATE Table

Creating a Table from an Existing Table

DROP or DELETE Table

INSERT Query

SELECT Query

WHERE Clause

AND & OR Conjunctive Operators

The AND Operator

The OR Operator

UPDATE Query

DELETE Query

LIKE Clause

TOP, LIMIT or ROWNUM Clause

ORDER BY Clause

Group By

Distinct Keyword

SORTING Results

Handling Duplicates

Using Joins

INNER JOIN

LEFT JOIN

RIGHT JOIN

FULL JOIN

SELF JOIN

CARTESIAN or CROSS JOIN

Unions Clause

he UNION ALL Clause

INTERSECT Clause

EXCEPT Clause

NULL Values

Alias Syntax

Indexes

The CREATE INDEX Command

The DROP INDEX Command

INDEX Constraint

ALTER TABLE Command

TRUNCATE TABLE Command

Implementing Views

What are Views?

Advantages of Views

Creating, Altering, Dropping Views

Advance Options while Creating a View

SQL Server Catalogue Views

The WITH CHECK OPTION

Having Clause

Transact-SQL (T-SQL)

Properties of Transactions

Transactional Control Commands

Wildcard Operators

Date Functions

Temporary Tables

What are Temporary Tables?

Dropping Temporary Tables

Clone Tables

Sub Queries

Subqueries with the SELECT Statement

Subqueries with the INSERT Statement

Subqueries with the UPDATE Statement

Subqueries with the DELETE Statement

Working with Stored Procedures and Functions

hat are Stored Procedures?

Benefits of Stored Procedures

Creating, Executing Modifying, Dropping

Input, Output and Optional Parameters

System Defined Stored Procedures and Functions

User Defined Functions

Implementing Triggers

What are Triggers?

Constraints vs Triggers

Creating, Altering, Dropping Triggers

After and Instead of triggers

Using Rollback Transaction

Working with Cursors

Creating Cursors

Cursors vs. Select

Types of Cursors

Locks on Cursors

Advantages of Cursors

Using Sequences

Using AUTO_INCREMENT Column

Obtain AUTO_INCREMENT Values

Renumbering an Existing Sequence

Starting a Sequence at a Particular Value

Backup and Restore

Generating SQL Script

Executing SQL Script

Generating Change Script

Taking database Backup

Restoring database using Backup

Attaching and Detaching of Database