| Pythonista Planet

Microsoft SQL Server (MS SQL Server) is a relational database management system (RDBMS) created by Microsoft. We can use MS SQL Server for storing, accessing, managing, and manipulating data. This database stores data in the form of tables with rows and columns.

In this post, let’s see how to set up an environment in Visual Studio to connect to the MS SQL Server database and run SQL queries.

Let’s dive right in.

Setting up the MS SQL Server Environment in Visual Studio

When you install Visual Studio, make sure you have the “Data Storage and Processing” package included in it.

You can checkmark the above option when you install Visual Studio.

Open Visual Studio. You can either go to your project directory or click on the “Continue without code” option.

Navigate to the following options in Visual Studio:

View -> SQL Server Object Explorer -> SQLServer -> LocalDB -> Right Click -> New Query

You can write your SQL queries here. To start, let’s write a query to create a database.

create database mssqlbasics

To execute this query, you can select the query you want to execute, then right-click on the query and click on “Execute”.

You can also click on the below button to execute the SQL query after selecting it.

running an sql query in MS SQL Server Visual Studio

Now we have created a new database called mssqlbasics.

Right-click on the newly created database and click on “New Query”. Let’s write the queries here so that the operations would be done on this database.

We can run our SQL queries here. Let’s explore the different queries we have and learn how to use them.

SQL Queries in MS SQL Server

Let’s start by creating a table. We will create the IPL 2022 points table in MS SQL Server.

Creating Tables

The syntax for creating a table is:

create table table_name ( column_name1 type1, column_name2 type2, ... )

Now, let’s create a table with 8 columns.

create table IPL(
	Rank int primary key,
	Team varchar(30),
	Matches int,
	Won int,
	Lost int,
	NetRunRate float,
	Points int,
	Last5Matches varchar(5)
);

We use int (or integer) as the data type for integer values and varchar(size) as the data type for string values. In the above query, the column Rank is considered the primary key. A primary key is a column in a table that is distinctive for each record. We use the primary key to uniquely identify a row in a table.

To check the columns created in a table, use the following query.

EXEC sp_columns IPL;

Inserting Data into a Table

Let’s insert some data into the table. To insert a row of data into the table, use the following query.

insert into IPL values (1,'Gujarat Titans',14,10,4,'+0.316',20,'LWWLL');

We can also insert multiple rows at the same time. This is how we do it:

insert into IPL values
	(2,'Rajasthan Royals',14,9,5,'+0.298',18,'WWLWL'),
	(3, 'Lucknow Supergiants',14,9,5,'+0.251',18,'WLLWW'),
	(4,'Royal Challengers Bangalore',14,8,6,'-0.253',16,'WLWWL');

Retrieving Data from the Table

Now, let’s see the data in the table. To retrieve data from a table, we can use the select command.

select * from IPL;

We can add additional conditions to the select command using the where clause.

select * from IPL where Points>17;

We can use the order by command to sort the data in the order of any column.

select * from IPL order by Points;

To get the data in descending order, we can add the DESC keyword at the end of the query.

select * from IPL order by Points DESC;

We can use the group by clause to group the data based on a column.

For example, the following query groups the data based on the column Points and finds out the count of Teams having the same point and the corresponding point.

select Points, count(Points) from IPL group by Points;

We can add additional conditions to a group by command by using the having clause.

select Points, count(Points) from IPL group by Points having Points<19;

We can get the data having the specified pattern using the like command in SQL.

For example, the below query shows the details of the Teams with their names starting with the letter “R”.

select  * from IPL where Team like 'R%';

The following query displays the Teams with the second letter “u”.

select  * from IPL where Team like '_u%';

The following query displays the Teams with the last letter “s”.

select  * from IPL where Team like '%s';

Aggregate Functions

Aggregate functions are inbuilt functions in SQL that we can use to perform a calculation on a column. These functions will return a single value after calculation.

Let’s look at some examples.

We can find the maximum value in a column using the max() function.

select max(Points) from IPL;

We can find the minimum value in a column using the min() function.

select min(Points) from IPL;

We can use the sum() function to find the sum of values in a column.

select sum(Points) from IPL;

The avg() method can be used to find the average of values in a column.

select avg(Points) from IPL;

We use the count() function to find the count of values in a column. If any value is NULL, this function will not count it.

select count(Points) from IPL;

The count(*) function is used to find the number of rows in a table.

select count(*) from IPL;

Modifying the Table Structure

To modify the schema or structure of a table, we use the alter command.

The following command adds a new column to the table.

alter table IPL add captain varchar(30);

If you want to remove a column from a table, use the following query.

alter table IPL drop column captain;

The following command can be used to change the datatype of an existing column.

alter table IPL alter column Team varchar(40);

To rename a table in MS SQL Server, we use the following command.

EXEC sp_rename IPL, IPL2022;

This query changes the table name from IPL to IPL2022.

Updating Data in a Table

To update existing data in a table, we use the update command.

For example, the following query changes the Team name for Royal Challengers Bangalore.

update IPL2022 set Team='Royal Challengers Bengaluru' where Rank=4;

Deleting Data from a Table

To delete rows from a table, we use the delete command in SQL.

delete from IPL2022 where Rank=4;

This query deletes one row from the table.

Connecting Multiple Tables in SQL

Now let’s see how we can combine multiple tables in MS SQL Server.

Create a new table called Captains using the following queries.

create table Captains(
	Team varchar(40), 
	Captain varchar(40),
	Nationality varchar(40)
);

Let’s insert some data into the table.

insert into Captains values
	('Gujarat Titans','Hardik Pandya','India'),
	('Rajasthan Royals','Sanju Samson','India'),
	('Chennai Super Kings','MS Dhoni','India'),
	('Royal Challengers Bangalore','Faf du Plessis','South Africa'),
	('Mumbai Indians','Rohit Sharma','India');

Now let’s view the table.

select * from Captains;

Now we have two tables. Let’s try to connect these tables.

Subqueries in MS SQL Server

A subquery (or nested query) is a query within another SQL query and embedded within the WHERE clause. In subqueries, the inner query will get executed first and the corresponding result will be passed to the outer query. Then, the outer query will be executed.

For example, the following query selects the data of the Team whose captain is Sanju Samson.

select * from IPL2022 where Team in (select Team from Captains where Captain='Sanju Samson');

Joins in MS SQL Server

The JOIN clause in SQL is used to combine columns from one or more tables. The different types of Joins in SQL are inner join, left outer join, right outer join, full outer join, etc.

Inner Join

We use an inner join to get the records that have matching values in both tables. Inner join returns only the matching records present in both tables. It ignores the non-matching records.

For example, the query below selects the required columns from both tables only for the matching records.

select i.Rank, i.Team, i.Points, i.NetRunRate, c.Captain from IPL2022 i inner join Captains c on i.Team = c.Team;

Left Outer Join

We use the left outer join to return all the records from the left table (first table) and only the matching records from the right table (second table).

select i.Rank, i.Team, i.Points, i.NetRunRate, c.Captain from IPL2022 i left outer join Captains c on i.Team = c.Team;

Right Outer Join

We use the right outer join to return all the records from the right table (second table) and only the matching records from the left table (first table).

select i.Rank, i.Team, i.Points, i.NetRunRate, c.Captain from IPL2022 i right outer join Captains c on i.Team = c.Team;

Full Outer Join

We use a full outer join to return all the records from both tables when there is a match in either the left or the right table.

select i.Rank, i.Team, i.Points, i.NetRunRate, c.Captain from IPL2022 i full outer join Captains c on i.Team = c.Team;

Deleting Tables from the Database in MS SQL Server

To delete a table from a database, we can use the drop command.

drop table IPL2022;

drop table Captains;

Final Thoughts

This tutorial covers the fundamentals of MS SQL Server and how to run queries on it. Once your basics are clear, then you can explore more concepts and start building hands-on projects. You can create apps using technologies like C# and ADO.NET along with MS SQL Server as the database.

I hope this tutorial was helpful. Happy coding!

MS SQL Server Tutorial