Tutorial Gateway

SQL Server Tutorial

The SQL Server (Structured Query Language) is the set of instructions used to interact with a relational database. SQL statements are aimed to Insert, Update or Manipulate, and Extract data from the Relational Database Management Systems.

In this SQL Tutorial, we will show explain everything about SQL Server which includes: How to create, alter, delete databases and tables. DML, DDL statements, Built-in functions etc.

SQL Tutorial

Database

  1. Install Software
  2. Install Adventure Works Database
  3. Attach Database
  4. Detach Database
  5. Restore Database
  6. Create, Rename, and Delete a Database
  7. Rename Database along with Files
  8. Get Database Names

Tables

  1. Create Table
  2. ALTER Table
  3. Rename Table
  4. Get Table Names
  5. Add Column
  6. Rename Column
  7. Get Column Names
  8. Local and Global Temporary Tables
  9. Table Variable
  10. Derived Tables

Data Type

  1. Data Types
  2. DataLength

SQL Statements

  1. DML, DDL, DCL, and TCL Commands
  2. SELECT Statement
  3. ALIAS Column
  4. Select Distinct Statement
  5. Select Into Statement
  6. Insert Into Select Statement
  7. Insert Statement
  8. Update Statement
  9. Delete Statement
  10. Truncate Table
  11. Bulk Insert Statement
  12. Query Builder
  13. Pivot
  14. UnPivot
  15. Merge
  16. SubQuery
  17. CASE Statement
  18. Common Table Expressions (CTE)
  19. Coalesce
  20. IS NULL
  21. IS NOT NULL

Clauses

  1. List of Clauses with examples
  2. Group By Clause
  3. Having Clause
  4. Order By Clause
  5. Top Clause
  6. Where Clause

Constraints and Indexes

  1. Primary Key
  2. Foreign Key
  3. Referential Integrity
  4. Check Constraint
  5. Default Constraint
  6. Unique Constraint
  7. Clustered Index
  8. Non-Clustered Index
  9. Filtered Index

Joins

  1. Joins
  2. Cross Join
  3. Full Join
  4. Inner Join
  5. Left Join
  6. Right Join
  7. Self Join

Operators

  1. And & Or Operators
  2. Arithmetic Operators
  3. Between Operator
  4. Comparison Operators
  5. Exists
  6. Except
  7. Intersect
  8. In Operator
  9. Like Operator
  10. Not Exists
  11. Not In Operator
  12. Union
  13. Union All

SQL Tutorial – Decision Making Statements

  1. If Else Statement
  2. Else If Statement
  3. While Loop
  4. Nested While Loop
  5. Break Statement
  6. Continue Statement
  7. Goto Statement
  8. IIF Function
  9. Choose Function

SQL Advanced

  1. Change Data Capture
  2. Table partition
  3. Table Partitioning using Management Studio
  4. Try Catch
  5. Views
  6. User Defined Functions
    1. Alter User Defined Functions
  7. Stored Procedures
    1. Introduction to Stored Procedures
    2. Select Statement Inside the Stored procedure Example
    3. Insert Statement Inside the Stored Procedure Example
    4. Update Statement within the Stored Procedure Example
    5. Input Parameters in a Stored Procedures
    6. Output Parameters in a Stored Procedure
    7. Return Values in a Stored Procedure
    8. Insert Stored Procedure result into Temporary Table
    9. Useful System Stored Procedures
  8. Triggers
    1. Introduction to Triggers
    2. After Insert
    3. Instead Of Insert
    4. After Update
    5. Instead Of Update
    6. After Delete
    7. Instead Of Delete
  9. Cursors
    1. Static Cursor
    2. Dynamic Cursor
    3. Forward_Only Cursor
    4. Fast_Forward Cursor
    5. Keyset
  10. Transactions
    1. Transactions
    2. Nested Transactions
    3. ACID Properties

SQL Server Management Studio

  1. Management Studio (SSMS) Introduction
  2. Install Management Studio (SSMS)
  3. Uninstall Management Studio (SSMS)
  4. Connect to Server using sqlcmd

SSMS Tasks

  1. Create Login – SQL Server Authentication
  2. Create a Windows Login
  3. Creating Server Roles
  4. Login Errors
  5. Back Up Database
  6. Maintenance Plan
  7. Restore database from .bak file

SQL Server Built-in Functions

Following are the built-in functions available in T-SQL Server

SQL tutorial – Ranking Functions

The List of available Ranking functions are:

  1. Introduction to Ranking Functions
  2. Rank
  3. Dense_Rank
  4. Ntile
  5. Row_NumberR

SQL tutorial – Mathematical Functions

The following are the list of mathematical function in SQL Server

  1. Introduction
  2. ABS
  3. ACOS
  4. ASIN
  5. ATAN
  6. ATN2
  1. CEILING
  2. COS
  3. COT
  4. DEGREES
  5. EXP
  6. FLOOR
  1. LOG
  2. LOG10
  3. PI
  4. POWER
  5. RADIANS
  6. RAND
  1. ROUND
  2. SIGN
  3. SIN
  4. SQRT
  5. SQUARE
  6. TAN

String Functions

The following are the list of available string function in SQL Server:

  1. Introduction
  2. ASCII
  3. CHAR
  4. CHARINDEX
  5. CONCAT
  6. CONCAT_WS
  7. DIFFERENCE
  8. FORMAT
  1. LEN
  2. LEFT
  3. LTRIM
  4. LOWER
  5. NCHAR
  6. PATINDEX
  7. QUOTENAME
  8. REPLACE
  1. REPLICATE
  2. REVERSE
  3. RIGHT
  4. RTRIM
  5. SOUNDEX
  6. SPACE
  7. STR
  8. STRING_AGG
  1. STRING_ESCAPE
  2. STRING_SPLIT
  3. STUFF
  4. SUBSTRING
  5. TRANSLATE
  6. TRIM
  7. UNICODE
  8. UPPER

Aggregate Functions

Below table will show you the list of aggregate function available in SQL Server

  1. Introduction
  2. AVG
  3. CheckSum_Agg
  4. COUNT
  5. COUNT_BIG
  1. GROUPING
  2. GROUPING_ID
  3. MAX
  4. MIN
  5. STDEV
  1. STDEVP
  2. SUM
  3. VAR
  4. VARP

 

Date Functions

  1. Introduction
  2. Current_TimeStamp
  3. DateFromParts
  4. DateTimeFromParts
  5. DateTime2FromParts
  6. Datetimeoffsetfrompart
  7. DateAdd
  8. DatePart
  1. DateName
  2. DateDiff
  3. DAY
  4. EoMonth
  5. GetDate
  6. GetUtcDate
  7. Month
  8. Smalldatetimfrompart
  1. SwitchOffset
  2. SysDateTime
  3. SysDateTimeOffset
  4. SysUtcDateTime
  5. TimeFromParts
  6. ToDateTimeOffset
  7. Year
  8. Date&time formats

Logical Functions

  1. IIF Function
  2. Choose Function

Analytic Function

  1. Cume_Dist
  2. First_Value
  3. Last_Value
  4. Lag
  1. Lead
  2. Percentile_Cont
  3. Percentile_Disc
  4. Percent_Rank

Conversion Function

  1. Cast
  2. Try_Cast
  3. Convert
  1. Try_Convert
  2. Parse
  3. Try_Parse

Configuration Functions

  1. @@Language
  2. @@LangId
  3. @@Max_Connections
  4. @@Max_Precision
  5. @@ServerName
  6. @@ServiceName
  7. @@SpId
  8. @@Version

System Functions

  1. @@Identity
  2. @@pack_Received
  3. IsNull
  4. IsNumeric
  5. Error_Line
  6. Error_Number
  7. Error_Message
  8. Error_Severity
  9. Error_State
  10. Host_Id
  11. Host_Name

System Statistical

  1. @@Connections
  2. @@CPU_Busy
  3. @@Idle
  4. @@IO_Busy
  5. @@Pack_Sent
  1. @@Packet_Errors
  2. @@TimeTicks
  3. @@Total_Read
  4. @@Total_Write
  5. @@Total Errors

SET Statements

  1. DateFirst
  2. DateFormat
  3. Language
  4. NoCount On
  5. RowCount

XML Functions

  1. For XML Raw
  2. For XML Auto
  3. XML Path

SQL Interview Questions and FAQ’s

Following are the list of both Frequently Asked Questions and the Interview Questions.

Basic Connections and Configurations

  1. Create ODBC Connection
  2. Create ODBC Connection- Using Native Client
  3. Connecting to R programming
  4. Configure Database Mail
  5. Create Native Client 11.0 Connection

Tables and Columns

  1. Get Column Names from Table
  2. How to Swap Column Values
  3. How to check if a Table Exists
  4. Identity Value Jumps after restarting
  5. Find all Dependencies on Table
  6. Find all Tables that Contain Specific Column Name
  7. Rename Table Name, and Column Name

Date and Time

  1. Formatting DATE
  2. Return Date Part Only from a DateTime datatype

Differences

  1. Difference between DATEPART and DATENAME
  2. Difference between UNION and UNION ALL
  3. Differentiate between CTE, Temp Tables, Derived tables, Table Variables

Joins

  1. Cross Join Vs Inner Join
  2. List of Outer Joins

Groups

  1. Count Records in a Group
  2. Retrieve the Last Record in each Group
  3. Select Top N Records for each Category
  4. How to Select First Row in each group

Select, Insert, Update, and Delete

  1. Delete Duplicate Rows
  2. Inserting values into Identity Column using IDENTITY INSERT
  3. Insert Stored Procedure result into Temporary Table
  4. Insert Images into Table
  5. Select Rows with Maximum Value on a Column
  6. Select All If Parameter is Empty or NULL
  7. UPDATE Table Columns from SELECT Statement

Strings

  1. Concatenate Rows into String
  2. Replace String in a Table Column
  3. Reverse String Words
  4. Extract Domain From Email

Misc

  1. Calculating Running Total
  2. Converting Rows into Columns without using PIVOT
  3. Converting Column Names into Rows without using UNPIVOT
  4. Export Data to CSV
  5. Nth Highest Salary
  6. Backup Database Automatically

Thank You for Visiting Our Blog