How to Write and Execute SQL Queries in Microsoft SQL Server

Edited 2 months ago by ExtremeHow Editorial Team

Microsoft SQL ServerSQL QueriesProgrammingDatabase ManagementWindowsLinuxDevelopmentData RetrievalITServer

SQL Server is a relational database management system developed by Microsoft. It is one of the most popular and widely used systems for managing large amounts of data. As a database management system, it allows you to perform various operations to manage your data efficiently. In this article, we will discuss in depth how you can write and execute SQL queries in Microsoft SQL Server, and explore various aspects of working with SQL queries.

Understanding SQL

Structured Query Language (SQL) is a language used to communicate with databases. It lets you create, read, update, and delete database records, often abbreviated as CRUD operations. SQL is a standardized language, which means that its basic syntax is consistent across different database systems, although each system may have different features and extensions.

Setting up SQL Server

Before you start writing queries, you need to set up Microsoft SQL Server and SQL Server Management Studio (SSMS). SSMS serves as a primary tool for managing SQL Server, and you can perform various tasks such as database management and writing SQL queries using this interface.

Installing Microsoft SQL Server

Follow these basic steps to install SQL Server:

• Download the setup file from the official Microsoft website.
• Run the installation wizard and follow the on-screen instructions. Choose the appropriate settings for your needs.
• If SQL Server Management Studio (SSMS) is not included in the package then install it.

Connecting to SQL Server

After installing SQL Server and SSMS, you need to connect to the server. Open SSMS and enter the name of your server, followed by your login credentials. After successful authentication, you will get access to your database environment.

Creating your first database

In SQL Server, a database is a container that holds a collection of data schema and objects such as tables, views, stored procedures, etc. Let’s create a simple database named StudentDB.

Creating a new database

Open SSMS, go to the "Object Explorer" pane, right-click "Databases", and select "New Database". Enter the name "StudentDB" and click "OK".

Alternatively, you can also use SQL commands to create the database:

CREATE DATABASE StudentDB;

Creating a table in SQL Server

Tables are the basic structure for storing data in a SQL database. They consist of rows and columns, where each column represents an attribute of the data and each row represents a record.

Make the table

To create a table named Students in the StudentDB database, use the following SQL command:

CREATE TABLE Students ( StudentID INT PRIMARY KEY, FirstName NVARCHAR(50), LastName NVARCHAR(50), BirthDate DATE, Major NVARCHAR(50) );

Inserting data into tables

Once the table is created, you can insert data into it using the INSERT INTO statement.

Inserting values into a table

Here is how you can insert a new student into the Students table:

INSERT INTO Students (StudentID, FirstName, LastName, BirthDate, Major) VALUES (1, 'John', 'Doe', '2000-01-01', 'Computer Science');

Querying data from tables

To get data, you can use the SELECT statement. This is one of the most frequently used statements in SQL.

Retrieving data with SELECT

To retrieve all columns from the STUDENT table:

SELECT * FROM Students;

If you want to retrieve specific columns, the statement would look like this:

SELECT FirstName, LastName FROM Students;

Filtering data with the WHERE clause

To filter records, use the WHERE clause. For example, to find students with a specific subject:

SELECT * FROM Students WHERE Major = 'Computer Science';

Updating data in tables

Use the UPDATE statement to modify existing data in a table.

Updating a record

For example, to update a student's major:

UPDATE Students SET Major = 'Mathematics' WHERE StudentID = 1;

Deleting data from tables

You can use the DELETE statement to delete data from your tables.

Deleting a record

For example, to delete a student with a specific ID:

DELETE FROM Students WHERE StudentID = 1;

Joining tables

You often need to fetch data from multiple tables in a single query. This is done using the JOIN operation.

Using inner join

Imagine you have another table, departments, and you want to join it with students:

SELECT Students.FirstName, Students.LastName, Departments.DepartmentName FROM Students INNER JOIN Departments ON Students.Major = Departments.Major;

Collecting data with SQL

SQL provides functions to perform calculations such as sum, average, and count for analysis and reporting.

Using aggregate functions

To count the number of students majoring in each field:

SELECT Major, COUNT(*) as NumberOfStudents FROM Students GROUP BY Major;

Sorting and limiting result sets

To order and limit your results, SQL provides the ORDER BY and TOP clauses.

Using ORDER BY

To sort students by their last name:

SELECT * FROM Students ORDER BY LastName;

Using the top

To limit the number of records returned:

SELECT TOP 5 * FROM Students;

Using views and stored procedures

Creating the scene

A view is a virtual table based on the result-set of a SQL statement. To create a view:

CREATE VIEW ComputerScienceStudents AS SELECT * FROM Students WHERE Major = 'Computer Science';

Using stored procedures

Stored procedures allow you to save a group of SQL statements for future execution. Here's how to create one:

CREATE PROCEDURE GetStudentDetails AS BEGIN SELECT * FROM Students; END;

Conclusion

Writing and executing SQL queries in Microsoft SQL Server involves understanding key concepts such as databases, tables, and SQL syntax. With this guide, you have seen how to create and manage databases and tables, and how to write different types of queries. As you work more with SQL, you will become more proficient at manipulating data and using the complex functionalities that SQL Server provides.

  Share This Article

If you find anything wrong with the article content, you can request an update