SQL

Day 1: INTRODUCTION, INSTALLATION

  • Data
  • Databases and RDBMS Software
  • Microsoft SQL Server Advantages
  • Use
  • Database Engine Component and OLTP
  • BI Components & Data Science Components
  • SQL
  • Purpose
  • Real-time Usage Options
  • SQL Versus Microsoft T-SQL [MSSQL]
  • Microsoft SQL Server - Career Options
  • Real-time Projects & Job Responsibilities
  • Versions and Editions of SQL Server
  • SQL Server and SSMS Installation Plan
  • SQL Server Pre-requisites
  • S/W
  • H/W
  • SQL Server 2019 / 2017 Installation
  • Instance Name and Server Name
  • Features
  • Collation
  • Admin Users

Day 2: SSMS Tool, SQL BASICS

  • SQL Server Management Studio
  • Local and Remote Connections
  • System Databases
  • Master and Model
  • MSDB, TempDB
  • Resource Databases
  • Creating Databases
  • Files [MDF, LDF]
  • Creating Tables in User Interface
  • Data Insertion & Storage
  • Limitations
  • SQL
  • Purpose and Real-time Usage
  • DDL
  • DML
  • SELECT
  • DCL and TCL Statements
  • Creating Tables using SQL Scripts
  • Data
  • Storage
  • Inserts - Basic Level
  • SELECT
  • Table Data Retrieval
  • Table Scan

Day 3: SQL BASICS, SELECT STATEMENT

  • Creating Databases in SQL Server
  • Creating Tables in SQL Databases
  • Using Basic Data Types
  • Int
  • Char
  • Single Row Inserts
  • Multi Row Inserts
  • Rules for Data Insertion Statements
  • SELECT Statement For Data Retrieval
  • SELECT with WHERE Conditions
  • AND and OR Operators Usage
  • IN Operator and NOT IN Operator
  • BETWEEN
  • NOT BETWEEN Operators; LIKE and NOT LIKE Operators
  • Wild Card Characters
  • IS and IS NOT Operator
  • NULLs
  • DISTINCT
  • TOP Keywords

Day 4: SCHEMAS & EXCEL IMPORTS

  • UPDATE Statement & Conditions
  • DELETE Statement & Conditions
  • TRUNCATE & DELETE Differences
  • Table Data / Content Modification
  • Table Structure Modifications (DDL)
  • ALTER
  • ADD and DROP Statements
  • Removing Tables and Databases
  • Schemas
  • Real-time Usage
  • Creation
  • Table Transfer and 2P
  • 3P Naming
  • Table Migrations across Schemas
  • Import / Export Wizard From SSMS
  • GO Statement
  • SQL BATCH Concept
  • CHAR Versus VARCHAR Data Types
  • VARCHAR & NVARCHAR Data Types

Day 5: CONSTRAINTS BASICS

  • Constraints and Keys - Data Integrity
  • NULL
  • NOT NULL Property on Tables
  • UNIQUE KEY Constraints
  • Importance
  • PRIMARY KEY Constraint
  • Importance
  • FOREIGN KEY Constraint
  • Importance
  • REFERENCES For Foreign Keys
  • CHECK Constraints and Conditions
  • Database Diagrams (E R) Diagrams
  • Table Key Relations with ER Diagrams
  • Relationships Verification and Links
  • Identity Property
  • Sequence Generation
  • Identity Property
  • Seed & Increment
  • DEFAULT Constraints
  • Insert Rules
  • Candidate Keys
  • Real-time Advantages

DAY 6: VIEWS, FUNCTIONS, PROCEDURES : BASIC OVERVIEW

  • Views
  • Types
  • Usage in Real-time
  • Creating
  • Important System Views For Metadata
  • Functions : Types
  • Usage in Real-time
  • Using Parameters in Functions (UDF)
  • Create, Execute Functions in Database
  • Parameters in SQL Server Database
  • Procedures : Types
  • Usage in Real-time
  • Using Parameters in Stored Procedures
  • User & System Predefined Procedures
  • Sp_help, Sp_helpdb and Sp_recompile
  • sp_rename, sp_depends System SPs
  • Compare Views
  • SPs and Functions
  • SProcs : Performance Advantages

Contents -5 (SQL)

Joining Tables

  • Joining Basics
  • Cartesian Product
  • Inner Joins and Outer Join
  • Equi-Join Non-Equi
  • Join Right & Left Outer Join
  • Full Outer Join Set Operator
  • Union & Union All
  • Intersect SQL Subqueries

Contents -6 SQL

Groups

  • SQL Statements
  • GROUP BY Clause

  • HAVING Clause
  • Order of a SELECT Statement

Reports

  • Reporting Concept
  • The SET Command The COLUMN Command
  • The BREAK Command
  • The COMPUTE Command

Contents -7 SQL

Looping

  • Loop Concept
  • For LOOP Dual
  • For Loop While Loop

Triggers

  • Triggers and database events
  • Defining a trigger
  • Timing a trigger Enabling and disabling a trigger.

Contents -8 (SQL)

  • Procedures
  • functions and Views
  • Basics of procedures
  • Basics of functions & Packages Package forward declaration
  • Package dependency Package overloading Listing package information
  • Error Handling
  • Errors in Programs Run Time Exceptions