In this SQL Tutorial, we will show the best way to learn SQL Server and explain everything about it. Which includes: How to create, alter, delete databases, and tables. DML, DDL statements, Built-in functions etc.
SQL Server stands for Structured Query Language is a nonprocedural language, which makes RDBMS (Relational Database Management Systems) possible. The SQL Server Query 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.
Best Way to Learn SQL Tutorial
This section of SQL Tutorial explains about the creating Databases, Tables. Next, inserting, updating, selecting data from tables using filters, sorting, etc.
Learn SQL Database
A SQL Database helps us to organize the data logically using tables. The main advantage of storing data in a database is its efficient and fast retrieval of data.
- Install Software
- Install the Adventure Works Database
- Attach Database
- Detach Database
- Restore Database
- Create, Rename, and Delete a Database
- Rename Database along with Files
- Get Database Names
SQL Server Tutorial on Tables
For a database designer, designing tables and their structure is an essential aspect. Here itself, one has to decide the tables, relationships, and data normalization. It includes many elements, this section of SQL Server tutorial explains the creating, renaming, and altering regular Tables and their columns, derived tables, and temporary tables.
- Create Table
- ALTER Table
- Rename Table
- Get Table Names
- Add Column
- Rename Column
- Get Column Names
- Local and Global Temporary Tables
- Table Variable
- Derived Tables
Data Type
Learn SQL Statements
SQL Server provides various statements for creating a new database, table structures, Select Statement. The SQL SELECT statement, which retrieves and returns data from the database, is the most commonly used. This SQL tutorial section explains all those DDL and DML commands available to work with the database.
- DML, DDL, DCL, and TCL Commands
- SELECT Statement
- ALIAS Column
- Select Distinct Statement
- Select Into Statement
- Insert Into Select Statement
- Insert Statement
- Update Statement
- Delete Statement
- Truncate Table
- Bulk Insert Statement
- Query Builder
- Pivot
- UnPivot
- Merge
- SubQuery
- CASE Statement
- Common Table Expressions (CTE)
Sql Server Clauses Tutorial
SQL Constraints and Indexes
The SQL Server constraints are the rules that will apply to table data. While we are inserting or deleting a record in a table, SQL checks these constraints and rejects the records that won’t meet the rules. Flor example, unique constraints won’t allow duplicates. This SQL Server tutorial section covers all the Indexes and the constraints or keys
- Primary Key
- Foreign Key
- Referential Integrity
- Check Constraint
- Default Constraint
- Unique Constraint
- Clustered Index
- Non-Clustered Index
- Filtered Index
Working with Nulls
While viewing the SQL data or working with data, Nulls are a bit problematic. Sometimes, we need to replace those Nulls or skip those entries. To resolve these problems, SQL has powerful functions that can work with Null values. So use this section to learn SQL Server Null functions.
SQL Joins Tutorial
The most important, rather powerful feature of SQL Server is Joins, which helps you to join two or more tables to perform various operations. Without Joins, there is no concept of data normalization because we have to store the entire data in one table.
SQL Tutorial on Operators
The following are the list of available SQL operators and statements.
- And & Or Operators
- Arithmetic Operators
- Between Operator
- Comparison Operators
- Exists
- Except
- Intersect
- In Operator
- Like Operator
- Not Exists
- Not In Operator
- Union
- Union All
SQL Tutorial on Decision Making Statements
- If-Else Statement
- Else-If Statement
- While Loop
- Nested While Loop
- Break Statement
- Continue
- Goto Statement
- IIF Function
- Choose Function
Sql Server Advanced Tutorial
It is an advanced SQL tutorial section. Here, you learn about View, Stored procedures, triggers, functions, Cursors, Etc.
- Change Data Capture
- Table partition
- Table Partitioning using Management Studio
- Try Catch
- Views
- User Defined Functions
- Stored Procedures
- Introduction to Stored Procedures
- Select Statement Inside the Stored procedure Example
- Insert Statement Inside a Stored Procedure Example
- Update Statement within the Stored Procedure
- Input Parameters in a Stored Procedures
- Output Parameters in a Stored Procedure
- Return Values in a Stored Procedure
- Insert Stored Procedure output into Temporary Table
- Useful System Stored Procedures
- Triggers
- Cursors
- Transactions
SQL Server Management Studio
This SQL Server tutorial section covers the list of operations we can do using the Management studio
- Management Studio (SSMS) Introduction
- Install Management Studio (SSMS)
- Uninstall Management Studio (SSMS)
- Connect to Server using sqlcmd
SQL SSMS Tasks
- Create Login – SQL Server Authentication
- Create a Windows Login
- Creating Server Roles
- Login Errors
- Back Up Database
- Maintenance Plan
- Restore a database from .bak file
SQL Server Built-in Functions
The following are the list of available built-in functions in SQL Server. Within this SQL Server tutorial, we covered almost all of them with examples.
Sql Server Tutorial on Ranking Functions
The List of available Ranking functions are:
SQL tutorial on Mathematical Functions
Apart from basic math operators, which we discussed in the Arithmetic Operators section, there are standard and common Math functions in SQL Server. These Mathematical functions allow you to manipulate the numerical data, which is crucial in data processing. The following are the list of mathematical function in SQL Server
SQL Tutorial on String Functions
A string in Sql is a group of charters stored in Varchar or Nvarchar data type, which is useful to store names, product descriptions, etc. SQL Server String functions enable you to manipulate the string data by searching for the given string and replacing it or adding so on. The following are the list of available string function in SQL Server:
SQL Tutorial on Aggregate Functions
SQL Server Aggregate functions are used to extract the aggregated or high-level data. For instance, total sales in a region, product sale by country, etc. To use these aggregate functions, you have to use the Group By Clause for the non aggregated columns. Otherwise, it will throw an error.
If you want to use any expressions to restrict the records, then use Having Clause, not the Where Clause. The below section will show you the list of aggregate function available in SQL Server
Learn SQL Date Functions
In general, the SQL Date will store in dd-mm-yyyy hh:mm:ss format or any other system-specific format. There are some cases where we have to extract individual parts such as Month, Month Name, Year, Day, etc. In other times, we may have to format the Date, add or subtract a few days from the original date. In all these scenarios, we can use the built-in standard SQL Server date functions to manipulate the Date.
Use this section to learn Sql Server data functions, which includes getting data, formatting date and time etc.
Logical Functions
Learn SQL Analytic Function
Conversion Function
Use this Sql Server Tutorial section to learn about the conversion functions.
Learn SQL Configuration Functions
This section of Sql Server tutorial covers the available list of Configuration functions.
SQL System Functions
This section of Sql Server tutorial covers the list of available system functions.
- @@Identity
- @@pack_Received
- IsNull
- IsNumeric
- Error_Line
- Error_Number
- Error_Message
- Error_Severity
- Error_State
- Host_Id
- Host_Name
Sql Server Tutorial on System Statistical
SET Statements
XML Functions
SQL Interview Questions and FAQ’s
This Sql Server tutorial section covers some of the faqs. The following are the list of both Frequently Asked Questions and the SQL Interview Questions.
Basic Connections and Configurations
- Create ODBC Connection
- Create ODBC Connection- Using Native Client
- Connecting to R programming
- Configure Database Mail
- Create Native Client 11.0 Connection
SQL Tables and Columns
- Get Column Names from Table
- How to Swap Column Values
- How to check if a Table Exists
- Identity Value Jumps after restarting
- Find all Dependencies on Table
- Find all Tables that Contain Specific Column Name
- Rename Table Name, and Column Name
SQL Date and Time
Differences
- Difference between DATEPART and DATENAME
- Difference between UNION and UNION ALL
- Differentiate between CTE, Temp Tables, Derived tables, Table Variables
Joins
Learn SQL Groups
- Count Records in a Group
- Retrieve the Last Record in each Group
- Select Top N Records for each Category
- How to Select First Row in each group
SQL Select, Insert, Update, and Delete
- Delete Duplicate Rows
- Insert values into Identity Column using IDENTITY INSERT
- Insert Stored Procedure result into Temporary Table
- Insert Images into Table
- Select Rows with Maximum Value on a Column
- Select All If Parameter is Empty or NULL
- UPDATE Table Columns from SELECT Statement
Learn Sql Server Strings
- Concatenate Rows into String
- Replace String in a Table Column
- Reverse String Words
- Select Domain From Email