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.

SQL Server

Database & Tables

  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. Create Table
  9. ALTER Table
  10. Rename Table
  11. Add Column
  12. Rename Column
  13. Local and Global Temporary Tables
  14. Table Variable
  15. Derived Tables

Data Type

  1. Data Types
  2. DataLength

SQL Statements

  1. SELECT Statement
  2. WHERE Clause
  3. ALIAS Column
  4. Order By Clause
  5. TOP Clause
  6. Select Distinct Statement
  7. Select Into Statement
  8. Insert Into Select Statement
  9. Insert Statement
  10. Update Statement
  11. Delete Statement
  12. Truncate Table
  13. Group By Clause
  14. Having Clause
  15. Bulk Insert Statement
  16. Query Builder
  17. Pivot
  18. UnPivot
  19. Merge
  20. SubQuery
  21. CASE Statement
  22. Common Table Expressions (CTE)
  23. Coalesce
  24. IS NULL
  25. IS NOT NULL

Constraints and Indexes

  1. Primary Key
  2. Foreign Key
  3. Check Constraint
  4. Default Constraint
  5. Unique Constraint
  6. Clustered Index
  7. Non Clustered Index
  8. 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

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. Try Catch
  3. Views
  4. User Defined Functions
    1. Alter User Defined Functions
  5. 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. Useful System Stored Procedures
  6. 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
  7. Cursors
    1. Static Cursor
    2. Dynamic Cursor
    3. Forward_Only Cursor
    4. Fast_Forward Cursor
    5. Keyset
  8. Tranasctions
    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)

SSMS Tasks

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

SQL Server Built-in Functions

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

Ranking Functions

The List of available Ranking functions are:

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

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. Datetimeoffsetfromparts
  7. DateAdd
  8. DatePart
  1. DateName
  2. DateDiff
  3. DAY
  4. EoMonth
  5. GetDate
  6. GetUtcDate
  7. Month
  8. Smalldatetimefromparts
  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. IsNull
  3. IsNumeric
  4. Error_Line
  5. Error_Number
  6. Error_Message
  7. Error_Severity
  8. Error_State
  9. Host_Id
  10. Host_Name

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 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 among 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 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. 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

Comments

  1. What is the database used in this tutorial

    Reply

    • Adventure Works DW 2014, and Adventure Works 2014

      Reply

      • Could you please let me know where can i get the schema for SQLTutorials database used in your example(s)?

        Reply

  2. Fantastic source to learn SQL.

    Great Job !!

    Reply

  3. Super contribution..

    Reply

  4. Fantastic.. it's really useful to learn SQL.. thank you

    Reply

  5. Great Information, Please add topics like execution plan and statistics , Performance tuning steps and sql agent jobs

    Reply

    • Thanks Abhinav
      We will do as soon as possible

      Reply

  6. Thank you so mush for your efforts on this. Very helpful and easy to understand! Appreciate it

    Reply

  7. Thank you very much for the information ..
    can you please add topics like Transaction Isolation Levels and Performance Tuning
    🙂

    Reply

  8. Thanks to everyone who is part of this great work. Learned some good basics here.
    I would suggest this as one of the best SQL courses available in online for free especially for the beginners.

    Reply

  9. Full package !!! awesome tutorial.. easy to learn
    Thanks you so much,…

    Reply

  11. Thank You for the Valuable tutorial. its really very nice..

    please add the performance tuning tips and how to understand the execution plans.

    Reply

  12. how to use partition in table sqlserver?

    Reply

  13. Cool tutorials Suresh…..You made this website with html5 or wordpress?

    Reply

  14. Where Can i find exercises files for Adventure work databases for practice….If anyone have please share the link

    Reply

  15. I have downloaded the adventure work2014 but in that i haven't found costomer tables which is used in the exercise

    Reply

  16. Wonderful way of presenting. Short and to the point. One can really be a good SQL developer after spending just few hours. Great work. Appreciate it as well as your vast knowledge on SQL.

    Reply

  17. Hi yours tutorial was awesome, Can u please post regarding execution plan as well

    Thanks

    Reply

  18. Really really superB

    Reply

  19. A good source to learn SQL Thank u …..

    Reply

Leave a Reply

Your email address will not be published. Required fields are marked *