Learn SQL Server Tutorial For Free

SQL Server Tutorial

In this tutorial, we will show the best way to learn SQL Server and explain everything about the language. Which includes: SQL tutorial for beginners on how to create, alter, delete databases, and tables. DML, DDL statements, Built-in functions etc.

What is Microsoft SQL?

SQL is a RDBMS introduced by the Microsoft with different versions, including Express, Standard, Enterprise, and Developer. Apart from these, it has the Reporting and Integration services to perform ETL and reporting.

The Users who want to learn SQL can download the Developer version and it is completely free for non commercial purposes. Remember, it is different from MySQL.

What is RDBMS or Relational Database Management Systems?

An RDBMS is an acronym of Relational Database Management Systems that allows you to store, retrieve, and modify the data. The structured query language is the standard one to create databases, select records, update, delete, or managing data them.

What is Structured Query Language?

SQL stands for Structured Query Language is a nonprocedural database language helps to create, access, and manipulate databases. It makes RDBMS (Relational Database Management Systems) possible.

It is the standard language used by the commercial and free database products. Some of the products that support this SQL language : Oracle, MySQL, Access, Sybase, PostgreSQL, IBM DB2, and so on.

What is Transact SQL Query?

It is an extension to the standard structural language produced by Microsoft that is used to manage the Sql databases. The transact queries are aimed to Insert, Update or Manipulate, and Extract data from the Relational Database Management Systems.

Who uses SQL?

It is used by both developers and database administrators. And C#, Java developers create an application that inserts data into database tables, retrieve to show in a web or windows, and modify.

The database administrator controls how data has to store, who can access, type of data it accepts, security, maintenance, backups, etc.

What is the Difference between SQL and a Database?

  • Database: It allows you to organize large amount of data into logical groups, and for this, it uses the tables for storing data. A single table can have multiple columns and each may hold different data type.
  • SQL language used to query data present in the database or enter records.

What are the 5 basic SQL Commands?

  1. Create Table – To create tables.
  2. SELECT – To query data inside a database. It selects one or more columns or all the columns.
  3. Insert New records or rows to a table.
  4. Update the existing data.
  5. Delete or drop data from a table.

What are the two basic components of a Sql Statement?

The two main components are SELECT and FROM clause. The Select is used to retrieve data from one or more tables. The FROM clause is to specify from where to retrieve the data (table(s), view, and stored procedures).

Best Way to Learn SQL Course

SQL Server is a Relational database and this section of tutorial explains about the creating Database objects, instance, Tables. Next, inserting, updating, selecting data from database systems using filters, sorting, etc.

SQL Tutorial on Database

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

SQL Tutorial on Tables

Designing tables and their structure is an essential aspect. Here itself, one has to decide the tables, relationships, and data normalization. So this section of SQL basics tutorial explains the create tables, renaming, and altering existing Table, columns, and temporary ones.

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

Data Types

  1. Data Types
  2. DataLength

Learn Basic SQL Commands

This SQL tutorial section provides various commands for creating a new database, table structures, Select Statement, and manipulate data.

  1. DML, DDL, DCL, and TCL Commands
  2. ALIAS Column
  3. Select Distinct
  4. Select Into
  5. Insert Into Select
  6. Truncate
  7. Bulk Insert
  8. Query Builder
  9. Pivot
  10. UnPivot
  11. Merge
  12. SubQuery
  13. CASE Statement
  14. Common Table Expressions (CTE)

Sql Clauses Tutorial

  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 Example

The constraints are the rules that will apply to column data. While we are inserting or deleting a record in a tbl, queries checks these constraints and rejects the records that won’t meet the rules.

The unique constraints won’t allow duplicates. This SQL tutorial section covers all the Indexes and the constraints or keys syntaxes and examples to learn and test.

  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

Learn working with Nulls SQL Tutorial

While working with data, Nulls are a bit problematic. Sometimes, we need to replace Nulls or skip those entries. To resolve, it has powerful functions that can work with Null values. So use this Sql tutorial section to learn the Null functions.

  1. Coalesce
  2. IS NULL
  3. IS NOT NULL
  4. ISNULL

Join Clause

Joins are a way of combining data from multiple tables. The two tables can be from the same or different databases. For joining multiple tables, there should be a common column with same data type.

  1. Introduction to Joins
  2. Cross Join
  3. Full Join
  4. Inner Join
  5. Left Join
  6. Right Join
  7. Self Join
  8. Cross Join Vs. Inner Join
  9. List of Outer Joins

Sql tutorial on Operators

The list of available basic operators and statements.

  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 SQL Statements tutorial

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

Advanced Sql tutorial Topics

The following are the advanced concepts used in software applications. Here, you learn about create View, Stored procedures, triggers, functions, Cursors, Etc.

  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
  7. Stored Procedures and result set
    1. Introduction to Stored Procedures
    2. Select Statement Inside the Stored procedure Example
    3. Insert Statement Inside a Stored Procedure Example
    4. Update Statement within the Stored Procedure
    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 output into Temporary Tbl
    9. Useful System Stored Procedures
  8. SQL Triggers tutorial
    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

Learn SQL SSMS

This section covers the list of operations we can do using the Management studio

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

SSMS Tasks

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

Advanced SQL Functions tutorial

The list of available built-in date, math, and string functions. Within this, we covered almost all of them with syntax and examples.

  1. Aggregate Functions – Use sum, avg, max, etc on grouping data.
  2. Configuration Functions
  3. Mathematical functions
  4. Date methods Functions
  5. String methods
  6. System methods
  7. System Statistical

Sql Tutorial on Ranking Functions

The List of available Ranking methods and their syntax are:

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

Logical Example

  1. IIF
  2. Choose

Analytics

  1. Cume_Dist
  2. First_Value
  3. Last_Value
  4. Lag
  5. Lead
  6. Percentile_Cont
  7. Percentile_Disc
  8. Percent_Rank

Conversion

Use this Sql Server Tutorial section to learn basics about the conversion functions.

  1. Cast
  2. Try_Cast
  3. Convert
  4. Try_Convert
  5. Parse
  6. Try_Parse

SET Permissions

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

XML

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

Learn SQL Interview Questions and FAQ’s

This Sql Server tutorial section covers some of the faqs and advanced topics. The following are the list of both Frequently Asked Questions in this programming language Interview Questions.

  1. Get Column Names from Table
  2. Swap Column Values
  3. 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

Basic Connections and Configurations

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

Date and Time

  1. Format 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, Derived, Tbl Variables

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. Insert values into Identity Column using IDENTITY INSERT
  3. Insert Stored Procedure result into Temporary tbl
  4. How to Insert Images
  5. Select Rows with Maximum Value on a Column
  6. Select All If Parameter is Empty or NULL
  7. UPDATE Columns from SELECT

Learn tutorial about Sql Server Strings

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

Misc queries of the Sql tutorial

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

SQL Advantages

Some of the advantages of use it but not limited are

  • Data Access: Allows the users to access data in a simple way. For the third party applications, use the connection strings such as OLE DB to connect with this server.
  • Efficiency: Process the data very quickly. By using stored procedure, you can enhance the performance.
  • Flexibility: It is very flexible and you can restrict the records count or any other specific needs.
  • Scalability: It can grow with your business needs.
  • Security: It is extremely secure, and has lot of security feature to control right from log in to access data.
  • Reliability: Very reliable, and it can handle large amount of data without any issue.
  • Reporting: It has the famous SSRS reporting tool or report builder to design reports or for data analysis from it.

Comments are closed.