Microsoft SQL Server Certification Course
- 5k Enrolled Learners
- Weekend
- Live Class
In today’s market, where a humongous amount of data gets generated every day, it is very important to understand how to handle data. SQL Server is an integrated environment developed by Microsoft to handle data. In this article on the SQL Server tutorial, you will be learning all the operations and commands that you need to explore your databases.
For your better understanding, I have divided the blog into the following categories:
Commands | Description |
This set of commands are used to define a database. | |
The manipulation commands are used to manipulate the data present in the database. | |
This set of commands deal with the permissions, rights and other controls of the database systems. | |
These commands are used to deal with the transaction of the database. |
Apart from the commands, the following topics are covered in this article:
***NOTE*** In this SQL Server Tutorial, I am going to consider the below database as an example, to show you how to learn and write commands.
StudentID | StudentName | ParentName | PhoneNumber | Address | City | Country |
1 | Vihaan | Akriti Mehra | 9955339966 | Brigade Road Block 9 | Hyderabad | India |
2 | Manasa | Shourya Sharma | 9234568762 | Mayo Road 15 | Kolkata | India |
3 | Anay | Soumya Mishra | 9876914261 | Marathalli House No 101 | Bengaluru | India |
4 | Preeti | Rohan Sinha | 9765432234 | Queens Road 40 | Delhi | India |
5 | Shanaya | Abhinay Agarwal | 9878969068 | Oberoi Street 21 | Mumbai | India |
Before we start understanding the different commands used in SQL Server, let us understand what is SQL Server, its architecture and how to install it. You can even check out the details of relational databases, functions, queries, variables, etc with the SQL Certification Course.
Microsoft SQL Server is a relational database management system. It supports the Structured Query language and comes with its own implementation of the SQL language which is the Transact-SQL(T-SQL). It has an integrated environment to handle SQL databases, which is the SQL Server Management Studio.
The key components of SQL Server are as follows:
Now, that you know what is MS SQL Server, let us move forward in this article on the SQL Server tutorial and understand how to install and setup the SQL Server.
Follow the below steps to install SQL Server:
Step 1: Go to the official page of Microsoft SQL Server download, where you will find the option to install SQL Server either on-premises or on the cloud.
Step 2: Now, scroll down and you will see two options: Developer & Enterprise edition. Here, I will be downloading the Developer edition. To download, you just have to click on the Download now option. Refer below.
Step 3: Once the application is downloaded, double click on the file and you will see the following window.
Step 4: Now, you can choose either of the 3 options to setup SQL Server. Here, I will just choose the Basic option. On selecting the installation type option, the next screen would be to accept the license agreement. To do that, click on Accept in the following window.
Step 5: Next, you have to specify the SQL Server installation location. Then, you have to click on Install.
Once you click on Install, you will see that the required packages are getting downloaded. Now, after the installation is complete, you will see the following screen:
Here, you can either go forward and click on Connect Now, or you can Customize the installation. For your better understanding, I will go forward and choose Customize.
Step 6: Once you click on Customize in the above window, you will see the following wizard opening up. in the following window, click on Next.
Step 7: After the rules get installed automatically, click on Next. Refer below.
Step 8: Next, you have to choose the installation type. So, choose the Perform a new installation of SQL Server 2017 option and then click on Next.
Step 9: In the wizard that opens, choose the edition: Developer. Then, click on Next. Refer below.
Step 10: Now, read and accept the license agreements by check-in the radio button and then click on Next. Refer below.
Step 11: In the below wizard you can choose the features which you wish to install. Also, you can choose the instance root directory and then click on Next. Here, I will choose the Database Engine Services.
Step 12: Next you have to name the instance, and automatically the instance ID will be created. Here, I will name the instance “edureka”. Then, click on Next.
Step 13: In the Server Configuration wizard, click on Next.
Step 14: Now, you have to enable the authentication modes. Here, you will see the Windows authentication mode and Mixed Mode. I will choose Mixed Mode. Then, mention the password and then I will add the current user as Admin by choosing the Add Current User option.
Step 15: Then, choose the configuration file path and click on Install.
After the installation is complete, you will see the following screen:
After the SQL Server is installed, your next step is to connect the SQL Server to the SQL Server Management Studio. To do that follow the below steps:
Step 1: Go back, to the following window, and click on the install SSMS option.
Step 2: Once you click on that option, you will be redirected to the following page, where you have to choose Download SSMS.
Step 3: After the setup has been downloaded, double-click on the application and you will see the following wizard opening up.
Step 4: Click on Install option, in the above window and you will see that that installation will begin.
Step 5: After the installation is complete you will get a dialog box as shown below.
After you install the SSMS, the next step is to access the Database Engine.
When you open the SQL server management studio from the start menu, a window will open similar to the window shown in the picture below.
Here, mention the Server Name, Authentication Mode and click on Connect.
After you click on Connect, you will see the following screen.
Well folks, thats how you install and setup the SQL Server. Now, moving forward in this SQL Server tutorial, let us understand the different components of the architecture of SQL Server.
SQL Server Architecture
The architecture of SQL Server is as follows:
Now, that you know how to set up and install SQL Server and its various components, let us get started with writing commands in SQL Server. But, before that let me cover how to write comments in SQL Server.
There are two ways in which you can comment in SQL, i.e. either use the single-line comments or the multi-line comments.
The single-line comments start with two hyphens (–). Hence, the text mentioned after (–), till the end of a single line will be ignored by the compiler.
--Example of single line comments
The multi-line comments start with /* and end with */. Hence, the text mentioned between /* and */ will be ignored by the compiler.
/* Example for multi-line comments */
Now in this article on the SQL Server tutorial, let us start with the first set of commands i.e. Data Definition Language commands.
This section of the article will give you an idea about the commands with the help of which you can define your database. The commands are as follows:
This statement is used to create a table, database or view.
This statement is used to create a database.
Syntax
CREATE DATABASE DatabaseName;
Example
CREATE DATABASE Students;
As the name suggests, this statement is used to create a table.
CREATE TABLE TableName ( Column1 datatype, Column2 datatype, Column3 datatype, .... ColumnN datatype );
Example
CREATE TABLE StudentInfo ( StudentID int, StudentName varchar(8000), ParentName varchar(8000), PhoneNumber int, AddressofStudent varchar(8000), City varchar(8000), Country varchar(8000) );
This statement is used to drop an existing table, database or view.
This statement is used to drop an existing database. The complete information present in the database will be lost as soon as you execute the below command.
Syntax
DROP DATABASE DatabaseName;
Example
DROP DATABASE Students;
This statement is used to drop an existing table. The complete information present in the table will be lost as soon as you execute the below command.
Syntax
DROP TABLE TableName;
Example
DROP TABLE StudentInfo;
The ALTER command is used to add, delete or modify columns or constraints in an existing table.
This statement is used to add, delete, modify columns in a pre-existing table.
The ALTER TABLE statement is used with ADD/DROP Column command to add and delete a column.
Syntax
ALTER TABLE TableName ADD ColumnName Datatype; ALTER TABLE TableName DROP COLUMN ColumnName;
Example
--ADD Column BloodGroup: ALTER TABLE StudentInfo ADD BloodGroup varchar(8000); --DROP Column BloodGroup: ALTER TABLE StudentInfo DROP COLUMN BloodGroup ;
The ALTER TABLE statement can be used with the ALTER column to change the data type of an existing column in a table.
ALTER TABLE TableName ALTER COLUMN ColumnName Datatype;
Example
--Add a column DOB and change the data type from date to datetime. ALTER TABLE StudentInfo ADD DOB date; ALTER TABLE StudentInfo ALTER COLUMN DOB datetime;
This SQL command is used to delete the information present in the table but does not delete the table itself. So, if you want to delete the information present in the table, and not delete the table itself, you have to use the TRUNCATE command. Else, use the DROP command.
Syntax
TRUNCATE TABLE TableName;
Example
TRUNCATE TABLE StudentInfo;
This statement is used to rename one or more tables.
Syntax
sp_rename 'OldTableName', 'NewTableName';
sp_rename 'StudentInfo', 'Infostudents';
Moving on in this article on SQL Server tutorial, let us understand the different data types supported by SQL Server.
Data Type Category | Data Type Name | Description | Range/ Syntax |
Exact numerics | numeric | Used to store numeric values and have fixed precision and scale numbers | – 10^38 +1 to 10^38 – 1. |
tinyint | Used to store integer values | 0 to 255 | |
smallint | Used to store integer values | -2^15 (-32,768) to 2^15-1 (32,767) | |
bigint | Used to store integer values | -2^63 (-9,223,372,036,854,775,808) to 2^63-1 (9,223,372,036,854,775,807) | |
int | Used to store integer values | -2^31 (-2,147,483,648) to 2^31-1 (2,147,483,647) | |
bit | Stores an integer data type which tale a value of 0, 1 or NULL | 0, 1, or NULL | |
decimal | Used to store numeric values and have fixed precision and scale numbers | – 10^38 +1 to 10^38 – 1. | |
smallmoney | Used to store monetary or currency values. | – 214,748.3648 to 214,748.3647 | |
money | Used to store monetary or currency values. | -922,337,203,685,477.5808 to 922,337,203,685,477.5807 (-922,337,203,685,477.58 to 922,337,203,685,477.58 for Informatica. | |
Approximate numerics | float | Used to store floating-point numeric data | – 1.79E+308 to -2.23E-308, 0 and 2.23E-308 to 1.79E+308 |
real | Used to store floating-point numeric data | – 3.40E + 38 to -1.18E – 38, 0 and 1.18E – 38 to 3.40E + 38 | |
Date and time | date | Used to define a date in SQL Server. | Syntax: date |
smalldatetime | Used to define a date that is combined with a time of day; where the time is based on a 24-hour day, with seconds always zero (:00) and without fractional seconds. | Syntax: smalldatetime | |
datetime | Used to define a date that is combined with a time of day with fractional seconds based on a 24-hour clock. | Syntax: datetime | |
datetime2 | datetime2 is as an extension of the existing datetime type that has a larger default fractional precision, larget date range. | Syntax: datetime2 | |
datetimeoffset | Used to define a date that is combined with a time of a day that has time zone awareness. It is based on a 24-hour clock. | Syntax: datetimeoffset | |
time | Used to define a time of a day. | Syntax: time | |
Character strings | char | Used to store fixed-size characters. | char [ ( n ) ] where n value varies from 1 – 8,000 |
varchar | Used to store variable-length characters. | varchar [ ( n | max ) ] where the n value varies from 1-8000 and the maximum storage allowed is 2GB. | |
text | Used to store variable-length non-Unicode data | Maximum string length allowed – 2^31-1 (2,147,483,647) | |
Unicode character strings | nchar | Used to store fixed-size characters. | nchar [ ( n ) ] where n value varies from 1-4000 |
nvarchar | Used to store variable-length characters. | varchar [ ( n | max ) ] where the n value varies from 1-4000 and the maximum storage allowed is 2GB. | |
ntext | Used to store variable-length Unicode data | Maximum string length allowed – 2^30-1 (2,147,483,647) | |
Binary strings | binary | Used to store binary data types of either fixed length | binary [ ( n ) ] where n value varies from 1 – 8,000 |
varbinary | Used to store binary data types of either fixed length | varbinary [ ( n ) ] where the n vale varies from 1-8000 and the maximum storage allowed is 2^31-1 bytes. | |
image | Used to store variable-length binary data | 0 – 2^31-1 (2,147,483,647) bytes | |
Other data types | cursor | It is a data type for stored procedure or variables OUTPUT parameters that contain a reference to a cursor. | – |
rowversion | Used to expose automatically generated, unique binary numbers within a database. | – | |
hierarchyid | Used to represent position in a hierarchy. | – | |
uniqueidentifier | Is a 16-byte GUID. | Syntax: uniqueidentifier | |
sql_variant | Used to store the values of various SQL Server-supported data types | Syntax: sql_variant | |
xml | Used to store XML data type. | xml ( [ CONTENT | DOCUMENT ] xml_schemacollection ) | |
Spatial Geometry Types | Used to represent data in a Euclidean (flat) coordinate system. | – | |
Spatial Geography Types | Used to store ellipsoidal (round-earth) data, such as GPS latitude and longitude coordinates. | – | |
table | Used to store a result set for processing at a later time | – |
Next, in this article let us understand the different types of keys and constraints in the database.
The following are the different types of keys used in database:
Constraints are used in a database to specify the rules for data stored in a table. The different types of constraints in SQL are as follows:
The NOT NULL constraint ensures that a column cannot have a NULL value.
CREATE TABLE StudentsInfo ( StudentID int NOT NULL, StudentName varchar(8000) NOT NULL, ParentName varchar(8000), PhoneNumber int , AddressofStudent varchar(8000) NOT NULL, City varchar(8000), Country varchar(8000) ); --NOT NULL on ALTER TABLE ALTER TABLE StudentsInfo ALTER COLUMN PhoneNumber int NOT NULL;
This constraint ensures that all the values in a column are unique.
Example
--UNIQUE on Create Table CREATE TABLE StudentsInfo ( StudentID int NOT NULL UNIQUE, StudentName varchar(8000) NOT NULL, ParentName varchar(8000), PhoneNumber int , AddressofStudent varchar(8000) NOT NULL, City varchar(8000), Country varchar(8000) ); --UNIQUE on Multiple Columns CREATE TABLE StudentsInfo ( StudentID int NOT NULL, StudentName varchar(8000) NOT NULL, ParentName varchar(8000), PhoneNumber int , AddressofStudent varchar(8000) NOT NULL, City varchar(8000), Country varchar(8000) CONSTRAINT UC_Student_Info UNIQUE(StudentID, PhoneNumber) ); --UNIQUE on ALTER TABLE ALTER TABLE StudentsInfo ADD UNIQUE (StudentID); --To drop a UNIQUE constraint ALTER TABLE StudentsInfo DROP CONSTRAINT UC_Student_Info;
The CHECK constraint ensures that all the values in a column satisfy a specific condition.
--CHECK Constraint on CREATE TABLE CREATE TABLE StudentsInfo ( StudentID int NOT NULL, StudentName varchar(8000) NOT NULL, ParentName varchar(8000), PhoneNumber int , AddressofStudent varchar(8000) NOT NULL, City varchar(8000), Country varchar(8000) CHECK (Country ='India') ); --CHECK Constraint on multiple columns CREATE TABLE StudentsInfo ( StudentID int NOT NULL, StudentName varchar8000) NOT NULL, ParentName varchar(8000), PhoneNumber int , AddressofStudent varchar(8000) NOT NULL, City varchar(8000), Country varchar(8000) CHECK (Country ='India' AND City = 'Hyderabad') ); --CHECK Constraint on ALTER TABLE ALTER TABLE StudentsInfo ADD CHECK (Country ='India'); --To give a name to the CHECK Constraint ALTER TABLE StudentsInfo ADD CONSTRAINT CheckConstraintName CHECK (Country ='India'); --To drop a CHECK Constraint ALTER TABLE StudentsInfo DROP CONSTRAINT CheckConstraintName;
The DEFAULT constraint consists of a set of default values for a column when no value is specified.
--DEFAULT Constraint on CREATE TABLE CREATE TABLE StudentsInfo ( StudentID int, StudentName varchar(8000) NOT NULL, ParentName varchar(8000), PhoneNumber int , AddressofStudent varchar(8000) NOT NULL, City varchar(8000), Country varchar(8000) DEFAULT 'India' ); --DEFAULT Constraint on ALTER TABLE ALTER TABLE StudentsInfo ADD CONSTRAINT defau_Country DEFAULT 'India' FOR Country; --To drop the Default Constraint ALTER TABLE StudentsInfo ALTER COLUMN Country DROP defau_Country;
The INDEX constraint is used to create indexes in the table, through which you can create and retrieve data from the database very quickly.
Syntax
--Create an Index where duplicate values are allowed CREATE INDEX IndexName ON TableName (Column1, Column2, ...ColumnN); --Create an Index where duplicate values are not allowed CREATE UNIQUE INDEX IndexName ON TableName (Column1, Column2, ...ColumnN);
CREATE INDEX idex_StudentName ON StudentsInfo (StudentName); --To delete an index in a table DROP INDEX StudentsInfo.idex_StudentName;
Moving forward in this article on SQL Server tutorial, let us now understand the different Data Manipulation Language commands used in Microsoft SQL Server.
This section of the article will cover all those commands through which you can manipulate the database. The commands are as follows:
Apart from these commands, there are also other manipulative operators/functions such as:
This statement is used to select the database to start performing various operations on it.
Syntax
USE DatabaseName;
USE Students;
The INSERT INTO statement is used to insert new records into an existing table.
INSERT INTO TableName (Column1, Column2, Column3, ...,ColumnN) VALUES (value1, value2, value3, ...); --If you don't want to mention the column names then use the below syntax INSERT INTO TableName VALUES (Value1, Value2, Value3, ...);
INSERT INTO StudentsInfo(StudentID, StudentName, ParentName, PhoneNumber, AddressofStudent, City, Country) VALUES ('06', 'Sanjana','Kapoor', '9977331199', 'Buffalo Street House No 10', 'Kolkata', 'India'); INSERT INTO StudentsInfo VALUES ('07', 'Vishal','Mishra', '9876509712', 'Nice Road 15', 'Pune', 'India');
The UPDATE statement is used to modify or update the records already present in the table.
UPDATE TableName SET Column1 = Value1, Column2 = Value2, ... WHERE Condition;
UPDATE StudentsInfo SET StudentName = 'Aahana', City= 'Ahmedabad' WHERE StudentID = 1;
The DELETE statement is used to delete the existing records in a table.
DELETE FROM TableName WHERE Condition;
DELETE FROM StudentsInfo WHERE StudentName='Aahana';
The MERGE statement is used to perform the INSERT, UPDATE and DELETE operations on a specific table, where the source table is provided. Refer below.
MERGE TagretTableName USING SourceTableName ON MergeCondition WHEN MATCHED THEN Update_Statement WHEN NOT MATCHED THEN Insert_Statement WHEN NOT MATCHED BY SOURCE THEN DELETE;
Example
To understand the MERGE statement, consider the following tables as the Source table and the Target table.
Source Table:
StudentID | StudentName | Marks |
1 | Vihaan | 87 |
2 | Manasa | 92 |
4 | Anay | 74 |
Target Table:
StudentID | StudentName | Marks |
1 | Vihaan | 87 |
2 | Manasa | 67 |
3 | Saurabh | 55 |
MERGE SampleTargetTable TARGET USING SampleSourceTable SOURCE ON (TARGET.StudentID = SOURCE.StudentID) WHEN MATCHED AND TARGET.StudentName <> SOURCE.StudentName OR TARGET.Marks <> SOURCE.Marks THEN UPDATE SET TARGET.StudentName = SOURCE.StudentName, TARGET.Marks = SOURCE.Marks WHEN NOT MATCHED BY TARGET THEN INSERT (StudentID,StudentName,Marks) VALUES (SOURCE.StudentID,SOURCE.StudentName,SOURCE.Marks) WHEN NOT MATCHED BY SOURCE THEN DELETE;
Output
StudentID | StudentName | Marks |
1 | Vihaan | 87 |
2 | Manasa | 92 |
4 | Anay | 74 |
The SELECT statement is used to select data from a database, table or view. The data returned is stored in a result table, called the result-set.
SELECT Column1, Column2, ...ColumN FROM TableName; --(*) is used to select all from the table SELECT * FROM table_name; -- To select the number of records to return use: SELECT TOP 3 * FROM TableName;
Example
-- To select few columns SELECT StudentID, StudentName FROM StudentsInfo; --(*) is used to select all from the table SELECT * FROM StudentsInfo; -- To select the number of records to return use: SELECT TOP 3 * FROM StudentsInfo;
We can also use the following keywords with the SELECT statement:
The DISTINCT keyword is used with the SELECT statement to return only different values.
SELECT DISTINCT Column1, Column2, ...ColumnN FROM TableName;
SELECT DISTINCT PhoneNumber FROM StudentsInfo;
This statement is used to sort the required results either in the ascending or descending order. By default, the results are stored in ascending order. Yet, if you wish to get the results in descending order, you have to use the DESC keyword.
SELECT Column1, Column2, ...ColumnN FROM TableName ORDER BY Column1, Column2, ... ASC|DESC;
Example
-- Select all students from the 'StudentsInfo' table sorted by ParentName: SELECT * FROM StudentsInfo ORDER BY ParentName; -- Select all students from the 'StudentsInfo' table sorted by ParentName in Descending order: SELECT * FROM StudentsInfo ORDER BY ParentName DESC; -- Select all students from the 'StudentsInfo' table sorted by ParentName and StudentName: SELECT * FROM StudentsInfo ORDER BY ParentName, StudentName; /* Select all students from the 'StudentsInfo' table sorted by ParentName in Descending order and StudentName in Ascending order: */ SELECT * FROM StudentsInfo ORDER BY ParentName ASC, StudentName DESC;
This statement is used with the aggregate functions to group the result-set by one or more columns.
SELECT Column1, Column2,..., ColumnN FROM TableName WHERE Condition GROUP BY ColumnName(s) ORDER BY ColumnName(s);
-- To list the number of students from each city. SELECT COUNT(StudentID), City FROM StudentsInfo GROUP BY City;
GROUPING SETS were introduced in SQL Server 2008, used to generate a result-set that can be generated by a UNION ALL of the multiple simple GROUP BY clauses.
SELECT ColumnNames(s) FROM TableName GROUP BY GROUPING SETS(ColumnName(s));
SELECT StudentID, StudentName, COUNT(City) from StudentsInfo Group BY GROUPING SETS ((StudentID, StudentName, City),(StudentID),(StudentName),(City));
This clause is used in the scenario where the WHERE keyword cannot be used.
SELECT ColumnName(s) FROM TableName WHERE Condition GROUP BY ColumnName(s) HAVING Condition ORDER BY ColumnName(s);
Example
SELECT COUNT(StudentID), City FROM StudentsInfo GROUP BY City HAVING COUNT(StudentID) > 2 ORDER BY COUNT(StudentID) DESC;
The INTO keyword can be used with the SELECT statement to copy data from one table to another. Well, you can understand these tables to be temporary tables. The temporary tables are generally used to perform manipulations on data present in the table, without disturbing the original table.
SELECT * INTO NewTable [IN ExternalDB] FROM OldTable WHERE Condition;
Example
-- To create a backup of table 'StudentsInfo' SELECT * INTO StudentsBackup FROM StudentsInfo; --To select only few columns from StudentsInfo SELECT StudentName, PhoneNumber INTO StudentsDetails FROM StudentsInfo; SELECT * INTO PuneStudents FROM StudentsInfo WHERE City = 'Pune';
CUBE is an extension of the GROUP BY clause. It allows you to generate the sub-totals for all the combinations of the grouping columns specified in the GROUP BY clause.
SELECT ColumnName(s) FROM TableName GROUP BY CUBE(ColumnName1, ColumnName2, ....., ColumnNameN);
Example
SELECT StudentID, COUNT(City) FROM StudentsInfo GROUP BY CUBE(StudentID) ORDER BY StudentID;
ROLLUP is an extension of the GROUP BY clause. This allows you to include the extra rows which represent the subtotals. These are referred to as super-aggregated rows along with the grand total row.
SELECT ColumnName(s) FROM TableName GROUP BY ROLLUP(ColumnName1, ColumnName2, ....., ColumnNameN);
SELECT StudentID, COUNT(City) FROM StudentsInfo GROUP BY ROLLUP(StudentID);
The OFFSET clause is used with the SELECT and ORDER BY statement to retrieve a range of records. It must be used with the ORDER BY clause since it cannot be used on its own. Also, the range that you mention must be equal to or greater than 0. If you mention a negative value, then it shows an error.
SELECT ColumnNames) FROM TableName WHERE Condition ORDER BY ColumnName(s) OFFSET RowsToSkip ROWS;
Example
Consider a new column Marks in the StudentsInfo table.
SELECT StudentName, ParentName FROM StudentsInfo ORDER BY Marks OFFSET 1 ROWS;
The FETCH clause is used to return a set of a number of rows. It has to be used in conjunction with the OFFSET clause.
SELECT ColumnNames) FROM TableName WHERE Condition ORDER BY ColumnName(s) OFFSET RowsToSkip FETCH NEXT NumberOfRows ROWS ONLY;
SELECT StudentName, ParentName FROM StudentsInfo ORDER BY Marks OFFSET 1 ROWS FETCH NEXT 1 ROWS ONLY;
The TOP clause is used with the SELECT statement to mention the number of records to return.
SELECT TOP Number ColumnName(s) FROM TableName WHERE Condition;
SELECT TOP 3 * FROM StudentsInfo;
PIVOT is used to rotate the rows to column values and runs aggregations when required on the remaining column values.
SELECT NonPivoted ColumnName, [First Pivoted ColumnName] AS ColumnName, [Second Pivoted ColumnName] AS ColumnName, [Third Pivoted ColumnName] AS ColumnName, ... [Last Pivoted ColumnName] AS ColumnName FROM (SELECT query which produces the data) AS [alias for the initial query] PIVOT ( [AggregationFunction](ColumName) FOR [ColumnName of the column whose values will become column headers] IN ( [First Pivoted ColumnName], [Second Pivoted ColumnName], [Third Pivoted ColumnName] ... [last pivoted column]) ) AS [alias for the Pivot Table];
To get a detailed example, you can refer to my article on SQL PIVOT and UNPIVOT. Next in this SQL Server Tutorial let us look into the different operators supported by Microsoft SQL Server.
The different types of operators supported by SQL Server are as follows:
Let us discuss each one of them one by one.
Operator | Meaning | Syntax |
+ | Addition | expression + expression |
– | Subtraction | expression – expression |
* | expression * expression | |
/ | Divison | expression / expression |
% | Modulous | expression % expression |
Operator | Meaning | Syntax |
= | Assign a value to a variable | variable = ‘value’ |
Operator | Meaning | Syntax |
& (Bitwise AND) | Used to perform a bitwise logical AND operation between two integer values. | expression & expression |
&= (Bitwise AND Assignment) | Used to perform a bitwise logical AND operation between two integer values. It also sets a value to the output of the operation. | expression &= expression |
| (Bitwise OR) | Used to perform a bitwise logical OR operation between two integer values as translated to binary expressions within Transact-SQL statements. | expression | expression |
|= (Bitwise OR Assignment) | Used to perform a bitwise logical OR operation between two integer values as translated to binary expressions within Transact-SQL statements. It also sets a value to the output of the operation. | expression |= expression |
Used to perform a bitwise exclusive OR operation between two integer values. | expression ^ expression | |
^= (Bitwise Exclusive OR Assignment) | Used to perform a bitwise exclusive OR operation between two integer values. It also sets a value to the output of the operation. | expression ^= expression |
~ (Bitwise NOT) | Used to perform a bitwise logical NOT operation on an integer value. | ~ expression |
Operator | Meaning | Syntax |
= | Equal to | expression = expression |
> | Greater than | expression > expression |
< | Less than | expression < expression |
>= | Greater than or equal to | expression >= expression |
<= | Less than or equal to | expression <= expression |
<> | Not equal to | expression <> expression |
Not equal to | expression != expression | |
!< | Not less than | expression !< expression |
!> | Not greater than | expression !> expression |
Operator | Meaning | Syntax |
+ = | Used to add value to the original value and set the original value to the result. | expression += expression |
-= | Used to subtract a value from the original value and set the original value to the result. | expression -= expression |
*= | Used to multiply value to the original value and set the original value to the result. | expression *= expression |
/= | Used to divide a value from the original value and set the original value to the result. | expression /= expression |
%= | Used to divide a value from the original value and set the original value to the result. | expression %= expression |
Used to perform a bitwise AND operation and set the original value to the result. | expression &= expression | |
^= | Used to perform a bitwise exclusive OR operation and set the original value to the result. | expression ^= expression |
|= | Used to perform a bitwise OR operation and set the original value to the result. | expression |= expression |
Operator | Meaning | Syntax |
ALL | Returns TRUE if all of set of comparisons are TRUE. | scalar_expression { = | <> | != | > | >= | !> | < | <= | !< } ALL ( subquery ) |
AND | Returns TRUE if both the expressions are TRUE. | boolean_expression AND boolean_expression |
ANY | Returns TRUE if any one of a set of comparisons are TRUE. | scalar_expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } { ANY } ( subquery ) |
BETWEEN | Returns TRUE if an operand is within a range. | sampleexpression [ NOT ] BETWEEN beginexpression AND endexpression |
EXISTS | Returns TRUE if a subquery contains any rows. | EXISTS (sub query) |
IN | Returns TRUE if an operand is equal to one of a list of expressions. | test_expression [ NOT ] IN( subquery | expression [ ,…n ]) |
Returns TRUE if an operand matches a pattern. | match_expression [ NOT ] LIKE pattern [ ESCAPE escape_character ] | |
Reverses the value of any boolean operator. | [ NOT ] boolean_expression | |
OR | Returns TRUE if either of the boolean expression is TRUE. | boolean_expression OR boolean_expression |
SOME | Returns TRUE if some of a set of comparisons are TRUE. | scalar_expression { = | < > | ! = | > | > = | ! > | < | < = | ! < } { SOME} ( subquery ) |
Operator | Meaning | Example |
:: | Provides access to static members of a compound data type. Compound data types are those data types which contain multiple methods and simple data types. Compound data types These include the built-in CLR types and custom SQLCLR User-Defined Types (UDTs). | DECLARE @hid hierarchyid; SELECT @hid = hierarchyid::GetRoot(); PRINT @hid.ToString(); |
There are mainly three set operations: UNION, INTERSECT, MINUS. You can refer to the image below to understand the set operations in SQL. Refer to the below image:
Operator | Meaning | Syntax |
UNION | The UNION operator is used to combine the result-set of two or more SELECT statements. | SELECT ColumnName(s) FROM Table1 UNION SELECT ColumnName(s )FROM Table2; |
The INTERSECT clause is used to combine two SELECT statements and return the intersection of the data-sets of both the SELECT statements. | SELECT Column1 , Column2 …. FROM TableName; WHERE Condition INTERSECT SELECT Column1 , Column2 …. FROM TableName; WHERE Condition | |
EXCEPT | The EXCEPT operator returns those tuples that are returned by the first SELECT operation, and are not returned by the second SELECT operation. | SELECT ColumnName FROM TableName; EXCEPT SELECT ColumnName FROM TableName; |
Operator | Meaning | Syntax/ Example |
+ (String Concatenation) | Concatenates two or more binary or character strings, columns, or a combination of strings and column names into a single expression | expression+expression |
+= (String Concatenation) | Used to concatenate two strings and sets the string to the result of the operation. | expression+=expression |
% (Wildcard Characters to match) | Used to matches any string of zero or more characters. | Example: ‘sample%’ |
Used to match a single character within the specified range or set that is specified between brackets []. | Example: m[n-z]%’ | |
[^] (Wildcard Characters to match) | Used to match a single character which is not within the range or set specified between the square brackets. | Example: ‘Al[^a]%’ |
_ (Wildcard Characters to match) | Used to match a single character in a string comparison operation | test_expression [ NOT ] IN( subquery | expression [ ,…n ]) |
The different aggregate functions supported by SQL Server are as follows:
Function | Description | Syntax | Example |
SUM() | Used to return the sum of a group of values. | SELECT SUM(ColumnName) FROM TableName; | SELECT SUM(Marks) FROM StudentsInfo; |
COUNT() | Returns the number of rows either based on a condition, or without a condition. | SELECT COUNT(ColumnName) FROM TableName WHERE Condition; | SELECT COUNT(StudentID) FROM StudentsInfo; |
Used to calculate the average value of a numeric column. | SELECT AVG(ColumnName) FROM TableName; | SELECT AVG(Marks) FROM StudentsInfo; | |
MIN() | This function returns the minimum value of a column. | SELECT MIN(ColumnName) FROM TableName; | SELECT MIN(Marks) FROM StudentsInfo; |
MAX() | Returns a maximum value of a column. | SELECT MAX(ColumnName) FROM TableName; | SELECT MAX(Marks) FROM StudentsInfo; |
FIRST() | Used to return the first value of the column. | SELECT FIRST(ColumnName) FROM TableName; | SELECT FIRST(Marks) FROM StudentsInfo; |
LAST() | This function returns the last value of the column. | SELECT LAST(ColumnName) FROM TableName; | SELECT LAST(Marks) FROM StudentsInfo; |
Microsoft SQL Server allows the users to create user-defined functions which are routines. These routines accept parameters, can perform simple to complex actions and return the result of that particular action as a value. Here, the value returned can either be a single scalar value or a complete result-set.
You can use user-defined functions to:
Also, there are different types of user-defined functions you can create. They are:
Well, apart from the user-defined functions, there is a bunch of in-built functions in SQL Server; which can be used to perform a variety of tasks. Moving on in this article on SQL Server tutorial, let us now understand what are nested queries.
Nested queries are those queries that have an outer query and inner subquery. So, basically, the subquery is a query which is nested within another query such as SELECT, INSERT, UPDATE or DELETE. Refer to the image below:
Next in this SQL Server tutorial, let us understand the different types of joins in SQL.
Joins are used to combine tuples from two or more tables, based on a related column between the tables. There are four types of joins:
Consider the following table along with the StudentsInfo table, to understand the syntax of joins.
SubjectID | StudentID | SubjectName |
10 | 10 | Maths |
2 | 11 | Physics |
3 | 12 | Chemistry |
Syntax
SELECT ColumnName(s) FROM Table1 INNER JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;
SELECT Subjects.SubjectID, StudentsInfo.StudentName FROM Subjects INNER JOIN StudentsInfo ON Subjects.StudentID = StudentsInfo.StudentID;
Syntax
SELECT ColumnName(s) FROM Table1 LEFT JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;
SELECT StudentsInfo.StudentName, Subjects.SubjectID FROM StudentsInfo LEFT JOIN Subjects ON StudentsInfo.SubjectID = Subjects.SubjectID ORDER BY StudentsInfo.StudentName;
Syntax
SELECT ColumnName(s) FROM Table1 RIGHT JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;
SELECT StudentsInfo.StudentName, Subjects.SubjectID FROM StudentsInfo RIGHT JOIN Subjects ON StudentsInfo.SubjectID = Subjects.SubjectID ORDER BY StudentsInfo.StudentName;
Syntax
SELECT ColumnName(s) FROM Table1 FULL OUTER JOIN Table2 ON Table1.ColumnName = Table2.ColumnName;
SELECT StudentsInfo.StudentName, Subjects.SubjectID FROM StudentsInfo FULL OUTER JOIN Subjects ON StudentsInfo.SubjectID = Subjects.SubjectID ORDER BY StudentsInfo.StudentName;
Next, in this article on SQL Server tutorial, let us understand the different types of loops supported by the SQL Server.
The different control-of-flow commands are as follows:
Let us discuss each one of them one by one.
These keywords are used to enclose a series of SQL statements. Then, this group of SQL statements can be executed.
BEGIN { SQLStatement | StatementBlock } END
This statement is used to exit the current WHILE loop. In case, the current WHILE loop is nested inside another loop, then the BREAK statement exits only the current loop and the control is passed on to the next statement in the current loop. The BREAK statement is generally used inside an IF statement.
BREAK;
The CONTINUE statement is used to restart a WHILE loop. So, any statements after the CONTINUE keyword will be ignored.
Syntax
CONTINUE;
Here, Label is the point after which processing starts if a GOTO is targeted to that particular label.
Used to alter the flow of execution to a label. The statements written after the GOTO keyword are skipped and processing continues at the label.
Syntax
Define Label: Label: Alter Execution: GOTO Label
Here, Label is the point after which processing starts if a GOTO is targeted to that particular label.
Like any other programming language, the If-else statement in SQL Server tests the condition and if the condition is false then ‘else’ statement is executed.
Syntax
IF BooleanExpression { SQLStatement | StatementBlock } [ ELSE { SQLStatement | StatementBlock } ]
Used to exit unconditionally from a query or procedure. So, the statements which are written after the RETURN clause are not executed.
RETURN [ IntegerExpression ]
Here, an integer value is returned.
The WAITFOR control flow is used to block the execution of a stored procedure, transaction or a batch until a specific statement modifies, returns at least one row or a specified time or time interval elapses.
Syntax
WAITFOR { DELAY 'TimeToPass' | TIME 'TimeToExecute' | [ ( RecieveStatement ) | ( GetConversionGroupStatement ) ] [ , TIMEOUT timeout ] }
where,
This loop is used to set a condition for repeated execution of a particular SQL statement or a SQL statement block. The statements are executed as long as the condition mentioned by the user is TRUE. As soon as the condition fails, the loop stops executing.
Syntax
WHILE BooleanExpression { SQLStatement | StatementBlock | BREAK | CONTINUE }
Now, that you guys know the DML commands, let’s move onto our next section in this article on SQL Tutorial i.e. the DCL commands.
This section of SQL Server tutorial will give you an idea about the command through which are used to enforce database security in multiple user database environments. The commands are as follows:
The GRANT command is used to provide access or privileges on the database and its objects to the users.
Syntax
GRANT PrivilegeName ON ObjectName TO {UserName |PUBLIC |RoleName} [WITH GRANT OPTION];
where,
-- To grant SELECT permission to StudentsInfo table to user1 GRANT SELECT ON StudentsInfo TO user1;
The REVOKE command is used to withdraw the user’s access privileges given by using the GRANT command.
REVOKE PrivilegeName ON ObjectName FROM {UserName |PUBLIC |RoleName}
Example
-- To revoke the granted permission from user1 REVOKE SELECT ON StudentsInfo TO user1;
Moving on in this SQL Server tutorial, let us understand the how to create and use Stored Procedures.
Stored Procedures are reusable units that encapsulate a specific business logic of the application. So, it is a group of SQL statements and logic, compiled and stored together to perform a specific task.
Syntax
CREATE [ OR REPLACE] PROCEDURE procedure_name [ (parameter_name [IN | OUT | IN OUT] type [ ])] {IS | AS } BEGIN [declaration_section] executable_section //SQL statement used in the stored procedure END GO
--Create a procedure that will return a student name when the StudentId is given as the input parameter to the stored procedure Create PROCEDURE GetStudentName ( @StudentId INT, --Input parameter , @StudName VARCHAR(50) OUT --Output parameter, AS BEGIN SELECT @StudName = StudentName FROM StudentsInfo WHERE StudentID=@StudentId END
The above procedure returns the name of a particular student, on giving that students id as input. Next in this SQL Server tutorial, let us understand the transaction control language commands.
This section of SQL Server tutorial will give you an insight into the commands which are used to manage transactions in the database. The commands are as follows:
The COMMIT command is used to save the transaction into the database.
COMMIT;
The ROLLBACK command is used to restore the database to the last committed state.
Syntax
ROLLBACK;
NOTE: When you use ROLLBACK with SAVEPOINT, then you can directly jump to a savepoint in an ongoing transaction. Syntax: ROLLBACK TO SavepointName;
The SAVEPOINT command is used to temporarily save a transaction. So if you wish to rollback to any point, then you can save that point as a ‘SAVEPOINT’.
Syntax
SAVEPOINT SAVEPOINTNAME;
Consider the below table to understand the working of transactions in the database.
StudentID | StudentName |
1 | Rohit |
2 | Suhana |
3 | Ashish |
4 | Prerna |
Now, use the below SQL queries to understand the transactions in the database.
INSERT INTO StudentTable VALUES(5, 'Avinash'); COMMIT; UPDATE StudentTable SET name = 'Akash' WHERE id = '5'; SAVEPOINT S1; INSERT INTO StudentTable VALUES(6, 'Sanjana'); SAVEPOINT S2; INSERT INTO StudentTable VALUES(7, 'Sanjay'); SAVEPOINT S3; INSERT INTO StudentTable VALUES(8, 'Veena'); SAVEPOINT S4; SELECT * FROM StudentTable;
Next in this article on SQL Server tutorial let us understand how to handle exceptions in Transact-SQL.
There are two types of exceptions, i.e, the system-defined exceptions and the user-defined exceptions. As the name suggests, exception handling is a process through which a user can handle the exceptions generated. To handle exceptions you have to understand the following control flow statements:
This clause is used to raise an exception and transfers the execution to a CATCH block of a TRY…CATCH construct.
Syntax
THROW [ { ErrorNumber | @localvariable }, { Message | @localvariable }, { State | @localvariable } ] [ ; ]
THROW 51000, 'Record does not exist.', 1;
Used to implement exception handling in Transact-SQL. A group of statements can be enclosed in the TRY block. In case an error occurs in the TRY block, control is passed to another group of statements that are enclosed in a CATCH block.
Syntax
BEGIN TRY { SQLStatement | StatementBlock} END TRY BEGIN CATCH [ { SQLStatement | StatementBlock } ] END CATCH [ ; ]
BEGIN TRY SELECT * FROM StudentsInfo; END TRY BEGIN CATCH SELECT ERROR_NUMBER() AS ErNum , ERROR_MESSAGE() AS ErMsg; END CATCH
With this, we come to the end of this article on SQL Server Tutorial. I hope you enjoyed reading this article on SQL Server Tutorial For Beginners. If you wish to get a structured training on MySQL, then check out our MySQL DBA Certification Training which comes with instructor-led live training and real-life project experience. This training will help you understand MySQL in-depth and help you achieve mastery over the subject. Got a question for us? Please mention it in the comments section of ”SQL Server Tutorial” and I will get back to you.
Course Name | Date | Details |
---|---|---|
Microsoft SQL Server Certification Course | Class Starts on 19th October,2024 19th October SAT&SUN (Weekend Batch) | View Details |
Microsoft SQL Server Certification Course | Class Starts on 14th December,2024 14th December SAT&SUN (Weekend Batch) | View Details |
edureka.co