Step-by-Step Guide to Building SQL Queries in SQL Server

SQL (Structured Query Language) is the standard language for interacting with databases, allowing you to access and manipulate data efficiently. With SQL, you can query, update, insert, and delete data, as well as create and modify databases and their objects.

Relational databases - the most common type - rely on SQL. Microsoft SQL Server is one of the most widely used relational database management systems.

This guide provides step-by-step instructions for building various SQL queries in SQL Server.

What is an SQL query?

An SQL query is essentially a request to retrieve or manipulate data stored within a relational database.

The most common data handling operations in SQL Server involve the following commands:

• SELECT - extracts data from a database
• INSERT - adds new data to a database
• UPDATE - modifies existing data in a database
• DELETE - removes data from a database

The statements built with predefined keywords according to the strict rules allow us to communicate with databases correctly.

Now, let's examine the syntax of SQL queries in SQL Server.

SQL Server query syntax

SQL queries have basically the same structure. A query begins with a statement, followed by additional parameters and operators that define how the query functions and what data it retrieves.

For instance, let us take the SELECT statement that allows us to retrieve specific data portions based on our needs. In its simplest form, it consists of two components:

• SELECT specifies the columns from which to fetch data.
• FROM defines the table containing the required columns.

For example, we want to retrieve all data stored in the Product table of the AdventureWorks2022 database (a commonly used test database that we will use in our tutorial for demonstration). To retrieve all columns, we use the asterisk (*) symbol. The query would be:

SELECT 
* 
FROM Production.Product p;

The output would display all records and fields from the Product table.

We are using dbForge Studio for SQL Server to demonstrate our queries. This multi-featured IDE enables us to construct and execute queries directly against databases. It also offers a wide range of features and options for various database tasks in SQL Server, both on-premises and in the cloud.

However, retrieving all data is unnecessary in most cases, especially when dealing with large tables containing millions of records. Instead, we usually need a filtered subset of data. The WHERE clause is a tool that allows us to specify conditions, ensuring we only retrieve rows that meet our criteria.

For instance, we need a list of products that are size XL, and we only want to see their IDs, names, colors, and list prices. Our query is:

SELECT
  ProductID
 ,Name
 ,Color
 ,ListPrice
FROM Production.Product p
WHERE p.Size = 'XL';

The WHERE clause acts as a filter, returning only the products that match the condition (Size = 'XL').

In the next section, we'll explore how to construct basic SQL queries in more detail.

How to write basic SQL queries in SQL Server

In this section, we will learn how to construct SQL queries for data operations in SQL Server. We'll begin with basic operations and gradually move to advanced query-building techniques.

Retrieve data with SELECT

As mentioned earlier, relational databases like SQL Server store data in tables consisting of columns and rows. SQL queries interact with this structure to retrieve information from the database.

The SELECT statement is the most commonly used command in SQL for retrieving information. The basic syntax of this statement is:

SELECT column1, column2,...columnN  
FROM schema_name.table_name;

This query allows us to retrieve data from one or more columns in a table. In a previous example, we demonstrated how to fetch all data from a table using SELECT *. However, this should only be used when necessary, as it can lead to performance issues and increased resource consumption.

It's always better to specify the exact columns you need rather than having the query scan the entire table and return all records. For instance, if we want to retrieve a list of departments in an organization, the query would be:

SELECT
  DepartmentID
 ,Name
FROM HumanResources.Department d;

In this query, we specify that we only need data from two columns (DepartmentID and Name) from the HumanResources.Department table. In SQL Server, the table name includes the schema name.

Filter data with WHERE

The WHERE clause allows us to filter the data based on our conditions. The basic syntax of the query is:

SELECT column1, column2,...columnN 
FROM schema_name.table_name
WHERE condition;

We demonstrated the work of the simple SELECT query with the WHERE condition in the previous section. However, conditions may be more complex to ensure more precise data selection. For that, we use such operators as =, >, <, LIKE, and NOT to compare column values and specify our requirements for the data portion to retrieve more precisely.

Equal to (=)

This operator retrieves records where a certain column's value matches a specified value. For instance, we want to get the list of addresses of our customers who live in Seattle.

SELECT
  AddressLine1
 ,City
 ,PostalCode
FROM Person.Address a
WHERE City = 'Seattle';

Greater than (>)

This operator filters records where a column's value is greater than the specified value. If we want to see the list of products with a list price greater than 1000, we use the following query:

SELECT
  ProductID
 ,Name
 ,StandardCost
 ,ListPrice
FROM Production.Product p
WHERE ListPrice > 1000;

Less than (<)

This operator filters records by selecting only those where a column's value is less than a specified amount. For example, if we want to identify products with a stock quantity of less than 10 in our warehouse, we can use this operator.

SELECT
  ProductID
 ,Shelf
 ,Quantity
 ,ModifiedDate
FROM Production.ProductInventory pi
WHERE quantity < 10;

LIKE

The LIKE operator in the WHERE clause filters records based on a specified pattern, typically with wildcard characters. The % wildcard represents zero or more characters.

The basic syntax of the query is:

SELECT column1, column2...columnN
FROM schema_name.table_name
WHERE column_name
      LIKE 'pattern';

We need a list of products marked as Mountain and their additional details. The query is:

SELECT
  Name
 ,ProductNumber
 ,Color
 ,ListPrice
 ,SafetyStockLevel
FROM Production.Product
WHERE Name LIKE '%Mountain%';

NOT

The NOT operator in the WHERE clause negates a condition, allowing you to exclude specific values from the result set. It is often used with the LIKE operator to filter out records that match a certain pattern. For example, if we want to retrieve all products except those marked by Mountain, we can use NOT LIKE to exclude them from the results.

SELECT
  ProductID
 ,Name
 ,Color
 ,ListPrice
FROM Production.Product
WHERE Name NOT LIKE 'Mountain%';

Sort results with ORDER BY

The output of a SELECT query is a list of values in no particular order, which can be inconvenient, especially for large datasets. One of the most common operations is sorting the results in ascending or descending order. You can add the ORDER BY clause to the query to achieve this. This ensures that the results are returned in the specified order, making the data easier to analyze and interpret.

The basic syntax of the query looks like this:

SELECT column1, column2,...columnN
FROM schema_name.table_name
WHERE conditions
ORDER BY column1, column2, .. columnN [ASC | DESC];

• ASC sorts the results in ascending order and is the default sorting method, so adding ASC is optional.
• DESC sorts the results in descending order, which must be specified explicitly in the query if you want this sorting.

In the following test case, we want the list of products marked as Mountain ordered by the list price.

SELECT
  Name
 ,ProductNumber
 ,Color
 ,ListPrice
 ,SafetyStockLevel
FROM Production.Product
WHERE Name LIKE '%Mountain%'
ORDER BY ListPrice;

Note that dbForge Studio for SQL Server also lets you order the results visually with a single click. Simply select the column and choose whether to sort the results in ascending or descending order.

Advanced SQL query techniques

Advanced SQL query techniques in SQL Server allow users to perform complex data manipulations, optimize query performance, and efficiently retrieve sophisticated results from multiple tables or datasets. The next section will focus on techniques such as joins, aggregate functions, and subqueries.

JOIN tables

In SQL Server, the JOIN clause allows us to query and retrieve data from multiple tables simultaneously, based on logical relationships between those tables. In other words, JOIN instructs SQL Server to use data from one table to select rows from other tables. SQL Server supports several types of JOINs:

• INNER JOIN - combines rows with matching values in two or more tables, creating a result table with only the matched rows.
• LEFT OUTER JOIN - creates a result table that includes all records from the left table and the matching rows from the right table. Unmatched rows from the right table are excluded.
• RIGHT OUTER JOIN - creates a result table that includes all records from the right table and the matching rows from the left table. Unmatched rows from the left table are excluded.
• SELF JOIN - compares rows within the same table, allowing us to join a table to itself.
• CROSS JOIN - creates a result table that includes all possible combinations of rows from the first table with all rows from the second table, resulting in a Cartesian product.

The most popular JOIN type in SQL Server queries is the INNER JOIN. It retrieves only the rows with matching values in both tables (the desired behavior in many data retrieval scenarios). It is efficient and widely used for combining related data from multiple tables based on common fields.

The basic syntax of the INNER JOIN is:

SELECT column1, column2, ...
FROM table1
INNER JOIN table2
    ON table1.column = table2.column;

Suppose we need employee data, including their names, job titles, and payment rates. In the AdventureWorks2022 database, this information is stored across three different tables. Instead of fetching data from each table separately and combining the records, we can write a query that joins all three tables to retrieve the necessary information in a single step.

SELECT
  p.BusinessEntityID
 ,p.FirstName
 ,p.LastName
 ,e.JobTitle
 ,eph.Rate
FROM Person.Person AS p
INNER JOIN HumanResources.Employee AS e
  ON p.BusinessEntityID = e.BusinessEntityID
INNER JOIN HumanResources.EmployeePayHistory AS eph
  ON p.BusinessEntityID = eph.BusinessEntityID
  ORDER BY e.JobTitle;

This query addresses the Person.Person table that contains personal details, including FirstName and LastName, the HumanResources.Employee that contains job-related details, including JobTitle, and the HumanResources.EmployeePayHistory table that contains pay history, including the Rate.

The INNER JOIN connects all three tables based on the common BusinessEntityID column, ensuring only records with matching BusinessEntityID in all tables are included.

For more information about different JOIN types in SQL Server and their work, see SQL JOIN statements.

Aggregate data with GROUP BY

The GROUP BY clause in SQL Server groups rows that share a common value in one or more columns. In most cases, GROUP BY works with aggregate functions like COUNT, SUM, AVG, MIN, or MAX. With its help, we can obtain summary statistics or perform complex calculations on data based on specific criteria.

The basic syntax of the query is:

SELECT column_name, AGGREGATE_FUNCTION(column_name)
FROM table_name
GROUP BY column_name;

Assume we want to retrieve the total number of orders placed by each customer. We use the COUNT() function to calculate the number of orders per customer and GROUP BY to group the results. The query is below.

SELECT
  CustomerID
 ,COUNT(SalesOrderID) AS TotalOrders
FROM Sales.SalesOrderHeader
GROUP BY CustomerID
ORDER BY TotalOrders DESC;

The output displays data from the Sales.SalesOrderHeader table, showing the total number of orders grouped by each customer. The ORDER BY clause sorts the results in descending order, allowing us to first identify the most active customers (those with the highest number of orders).

The above example demonstrates the usage of GROUP BY with the COUNT() function. Other popular functions used with GROUP BY are SUM() and AVG(). Let us see some examples of their work.

The SUM() function is mostly used when we need to calculate the total sum of a numeric column for each group. For instance, we want to calculate the total revenue generated from each product. The query is:

SELECT
  ProductID
 ,SUM(LineTotal) AS TotalRevenue
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY TotalRevenue DESC;

In this query, SUM(LineTotal) calculates the total revenue generated by each product, while GROUP BY groups the results by ProductID. We also added the ORDER BY clause to sort the results in descending order and show the best-selling items on the top.

The AVG() function calculates the average value of a numeric column in each group. Suppose we want to know the average unit price for each product. The query is as follows:

SELECT
  ProductID
 ,AVG(UnitPrice) AS AveragePrice
FROM Sales.SalesOrderDetail
GROUP BY ProductID
ORDER BY AveragePrice;

This query uses AVG() to calculate the average unit price of each product and GROUP BY to group the results by ProductID to get aggregated price data. The ORDER BY clause sorts the items according to the average price in ascending order by default.

Use subqueries

A subquery is a query nested inside another query. It serves as a condition within the main query, helping to break down complex queries into smaller, more manageable components. This step-by-step approach makes it easier to execute queries and retrieve the desired results. Additionally, subqueries enhance the readability of complex queries.

Subqueries can be structured in various ways depending on their position within the query. However, the subquery must always be in parentheses.

When we use the subquery in a SELECT statement, which is the most common scenario, the basic syntax of the query is:

SELECT  
    column_name1,  
    (SELECT column_name2 FROM table2 WHERE condition) AS alias_name  
FROM table1  
WHERE condition;

For instance, we want to know the number of orders placed for each product with a list price greater than 100. The query is below:

SELECT
  ProductID
 ,(SELECT
      COUNT(*)
    FROM Sales.SalesOrderDetail
    WHERE ProductID = P.ProductID)
  AS OrderCount
FROM Production.Product P
WHERE ListPrice > 100;

This query retrieves the ProductID and the number of orders (OrderCount) for each product in the Production.Product table where the ListPrice is greater than 100.

Another popular scenario is using the subquery in a WHERE clause, frequently with IN/NOT IN operators. This lets us specify multiple values and control how value matching should occur. The basic syntax of the query is:

SELECT  
    column_name1,  
    column_name2  
FROM table1  
WHERE column_name3 IN (  
    SELECT column_name4 FROM table2 WHERE condition
);

Assume we want the list of products ordered in quantities greater than 10. The query is:

SELECT  
    ProductID,  
    Name  
FROM Production.Product  
WHERE ProductID IN (  
    SELECT ProductID  
    FROM Sales.SalesOrderDetail  
    WHERE OrderQty > 10
);

The subquery filters ProductIDs that appear in the Sales.SalesOrderDetail table with the OrderQty value greater than 10. Then, the main query assigns the names to the ProductID.

We can use the subqueries in both the SELECT statement and the WHERE clause. Suppose we need a list of customers who have placed at least one order. The required information is spread across multiple tables, and we aim to retrieve it step by step using a single query.

SELECT
  CustomerID
 ,(SELECT
      FirstName
    FROM Person.Person
    WHERE BusinessEntityID = C.PersonID)
  AS FirstName
 ,(SELECT
      LastName
    FROM Person.Person
    WHERE BusinessEntityID = C.PersonID)
  AS LastName
 ,(SELECT
      COUNT(*)
    FROM Sales.SalesOrderHeader
    WHERE CustomerID = C.CustomerID)
  AS OrderCount
FROM Sales.Customer C
WHERE CustomerID IN (SELECT DISTINCT
    CustomerID
  FROM Sales.SalesOrderHeader)
ORDER BY OrderCount DESC;

This query uses subqueries to filter customers who have placed at least one order and to count the number of orders each customer has placed. Additionally, the ORDER BY clause is used to sort the results in descending order, allowing us to view the most active customers at the top.

For more information about the subqueries in SQL Server, refer to the SQL Subqueries Tutorial.

Why use Query Builder in dbForge Studio for SQL Server

As requirements grow, SQL queries become increasingly complex, involving multiple subqueries, JOINs, functions, and grouping and filtering methods. Writing effective queries in SQL Server requires a deep understanding of SQL, precision, and attention to detail. Even experienced professionals benefit from tools that minimize manual effort and enhance accuracy.

One such tool is the Query Builder, available in dbForge Studio for SQL Server. This visual query designer allows you to create SQL queries that involve multiple tables, conditions, JOINs, and subqueries without manual coding. Instead of writing queries from scratch, you can visually arrange blocks and relationships, which are automatically converted into SQL code.

Let's explore how Query Builder can simplify creating SQL queries in practice.

Key features of Query Builder

The Query Builder is a functional tool with many features that simplify the SQL query construction. Among them, it is worth noticing the following features:

• Visual query diagram with drag-and-drop functionality
• Visualization of relationships between tables
• Simplified construction of JOINs, WHERE, and HAVING conditions
• Easy navigation through the database objects
• Automatic generation of the script templates
• Support for various query types

Now, let us look at the most essential Query Builder features.

Drag-and-drop query building

Query Builder allows us to eliminate manual code typing during the query construction. Instead, you can simply drag and drop the required tables from the Database Explorer pane into the query diagram area and select the necessary columns by checking them.

Next, you can visually create relationships between tables by dragging columns from one table to another. You can also apply WHERE and HAVING conditions using the visual condition editor.

Once your query is ready, you can execute it immediately. Additionally, you can view the generated SQL code in the corresponding tab.

Visual query diagram

One of the most valuable features of Query Builder is the visual query diagram, which helps us easily identify key query components. The visual approach is significantly more time-efficient than manually working with SQL code.

The diagram highlights tables, views, subqueries, and their relationships, clearly representing the query's logical structure. Additionally, the diagram overview window offers a more user-friendly way to navigate large diagrams.

Building complex queries

Managing conditions in an SQL query can be challenging, especially when working with subqueries and JOINs. The visual condition editor in Query Builder simplifies this process. It lets you edit JOIN, WHERE, and HAVING clauses, as well as group and sort the results effortlessly.

Query Builder provides a clear, organized view for subqueries, making it easy to track each subquery within a complex query. Each subquery is displayed on a separate tab, allowing smooth navigation and ensuring the overall query logic remains accurate and efficient.

SQL syntax highlighting and autocomplete

If you prefer writing SQL queries manually, dbForge Studio for SQL Server helps you produce high-quality code faster and ensures accuracy and readability. The built-in SQL Editor offers a range of coding assistance features, including:

• Code autocompletion - provides context-based suggestions to speed up coding.
• Syntax validation - checks queries in real time and detects errors.
• SQL formatting - automatically formats code, allowing you to maintain consistent coding standards.
• SQL snippets - enables code reuse by letting you edit snippets and create custom templates.

These features and many others are also available in Query Builder, so you can smoothly work with SQL code and its visual representation to achieve optimal results more efficiently.

Conclusion

SQL queries are the backbone of working with relational databases. They enable us to interact with databases efficiently. This tutorial introduced you to the fundamentals of query writing in SQL Server, covering everything from basic techniques to advanced query construction. It also explored graphical tools like Query Builder in dbForge Studio for SQL Server to simplify query creation.

dbForge Studio is a powerful and user-friendly alternative to SQL Server Management Studio, with enhanced functionality and customization options. You can explore its capabilities with a free fully functional 30-day trial. Simply download, install, and experience it firsthand.