SQL Server SELECT query examples to display and filter data from tables

Example queries with the SQL Server SELECT function to select, filter and display data.

This tutorial offers an extensive collection of SQL Server SELECT query examples to display and filter data from tables. The SELECT query is a powerful and versatile command that allows you to extract and manipulate data with precision and ease. By mastering its usage, you can enhance your Structured Query Langage skills and improve your data querying efficiency.

This tutorial includes various examples that demonstrate how to select single and multiple columns, use aliases and column headings, filter data with the WHERE clause, use aggregation functions with GROUP BY, and filter grouped data with the HAVING clause. Whether you are new to SQL or a seasoned professional, this tutorial provides a valuable resource to enhance your SELECT statement expertise and its applications in SQL Server.

1. Prepare the data to query using the SQL Server Select

1.1 Create the table used in the SELECT queries

First, before running the selection commands, create the example table of sales by month with this script.

-- Test if the table already exists
-- If it doaes then delete the table
IF EXISTS(
   SELECT 1 FROM sys.objects
   WHERE object_id = object_id(N'[dbo].[SALES]')
      AND type in (N'U')
)
DROP TABLE [dbo].[Sales]
GO

-- Create the example table to store SALES
CREATE TABLE [dbo].[Sales] (
   [MonthName] nvarchar(20),
   [Amount] numeric(8)
)
GO

1.2 Insert sample data to query into the sales table

Second step, insert rows with monthly sales amounts for the 12 months of the year.

-- Inserting the Sales data for our example
INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'January', 1000);
INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'February', 2000);
INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'March', 3000);
INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'April', 1000);
INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'May', 2000);
INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'June', 3000);
INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'July', 1000);
INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'August', 2000);
INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'September', 3000);
INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'October', 1000);
INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'November', 2000);
INSERT INTO dbo.SALES ([MonthName], Amount) VALUES (N'December', 3000);
GO

-- Select results for checking
SELECT *
FROM dbo.SALES;

2. Single column query using Select

In the example below, select only the MONTH column from the sales table.

SELECT [MonthName]
FROM   dbo.SALES;

Here is a screenshot of the SQL Server Select example query to display only the month from the sales table, created in the section above.

3. Select Star From type query to display all columns

On the other hand, in this example, the query displays all the columns of the table, i.e. MONTH and AMOUNT.

SELECT *
FROM   dbo.SALES;

4. Using aliases in SQL Server Select

Return all rows and columns from the Sales table, using table aliasing:

SELECT s.*  
FROM dbo.Sales AS s  
ORDER BY MonthName;  

5. Rename a column in a MS SQL SELECT clause

Return all rows and a subset of columns from the Sales table, with a renamed column. Here it is the Amount column that is renamed as TotalAmount

SELECT MonthName, Amount AS TotalAmount  
FROM dbo.Sales  
OR

6. SQL Server SELECT using the WHERE clause to filter data

The WHERE clause allows you to filter the data by adding one or more conditions and thus display only the rows of the table that match the condition. The WHERE clause in SQL is certainly the most used LMD clause in the database field. LMD stands for Data Manipulation Language, which is the language used to manipulate data and not the objects themselves.

Data filtering in SQL Server can be done in two ways:

1. Horizontal filtering
2. Vertical filtering

Horizontal filtering involves selecting one or more columns from a table. This allows you to limit the data returned to only the columns you are interested in, which can be useful for performance and readability.

Vertical filtering, on the other hand, involves filtering the data based on specific conditions using the WHERE clause. This allows you to retrieve only the rows that meet certain criteria, such as a specific date range, a particular customer, or any other condition you specify.

For example, if you have a table of sales data with columns for date, product, and amount, you could use horizontal filtering to select only the date and amount columns, and vertical filtering to retrieve only the rows where the amount is greater than a certain value.

6.1 Selection with a filter on the Month column

To display the month name and amount of the row whose month name is exactly January.

SELECT
  [MonthName],
  [Amount]
FROM  dbo.SALES
WHERE [MonthName] = 'January';

6.2 Selection query with the WHERE clause and an OR operator

The OR operator is a logical OR. To display the months whose name is January or February. Note that the OR clause allows you to mix conditions on different columns and thus filter the data in the table precisely. Either in an inclusive or more exclusive way.

SELECT
  [MonthName],
  [Amount]
FROM  dbo.SALES
WHERE [MonthName] = 'January'
  OR  [MonthName] = 'February'
  OR  [Amount] > 1000;

6.3 MS SQL selection query with the IN operator

Similarly, to display all the rows in the table whose month is equal to a specific value, use the SQL IN operator. For example, this query will display the first three months of the year:

• January
• February
• March

SELECT
  [MonthName],
  [Amount]
FROM dbo.SALES
WHERE [MonthName] IN ('January', 'February', 'March');

6.4 SQL query with WHERE clause and the different operator

To display a list of all customers whose name is different from MAMMADOU. This example query uses the star keyword: *. The SLECT * keyword displays all the columns of the table. The SELECT star keyword, also called SELECT STAR in English, or allows to display all the columns without naming them explicitly.

SELECT *
FROM dbo.SALES
WHERE [MonthName] <> 'December';

Good practices on using Select query and naming columns

Note that it is good SQL Server practice not to use the SELECT * keyword. Especially in programs or scripts where the columns change. Indeed, if a column name changes then the program no longer recognizes the column. Unless the program manages the column names dynamically. It is therefore recommended to name all columns explicitly to control :

1. The exact number of columns used
2. The name of the columns
3. And the order of the columns

7. Using SELECT with column headings and calculations

The seventh example presented below returns all rows from the Sales table, and calculate the total amount for each month.

SELECT MonthName, Amount, Amount * 12 AS TotalAmount  
FROM dbo.Sales  
ORDER BY MonthName;  

This query selects all rows from the Sales table and calculates the total amount for each month by multiplying the Amount column by 12 and assigning the result to the TotalAmount column. The results are sorted by MonthName.

8. Using DISTINCT with SELECT and Order By

Return a list of all unique month names in the Sales table. The script below selects only the unique MonthName values from the Sales table and sorts them in ascending order.

SELECT DISTINCT MonthName  
FROM dbo.Sales  
ORDER BY MonthName;  

9. Using GROUP BY in a select query

Generally the GROUP BY is combined with aggregation functions, like SUM, AVG, MIN or MAX. For example, to find the total amount for all sales:

SELECT SUM(Amount) AS TotalSales  
FROM dbo.Sales;  

This query calculates the sum of the Amount column for all rows in the Sales table and returns it as a single row with the column name TotalSales.

10. Using GROUP BY with multiple groups

This T-SQL query calculates the average and total sales for each unique MonthName value contained in the Sales table. The results are sorted by MonthName. To to find the average and total sales for each month, copy, paste and execute this query in SSMS for example.

SELECT MonthName, AVG(Amount) AS AvgSales, SUM(Amount) AS TotalSales  
FROM dbo.Sales  
GROUP BY MonthName  
ORDER BY MonthName;  

11. Using GROUP BY and WHERE

Find the total sales for each month, but only for the months with sales greater than 2000. Indeed, this GROUP BY query calculates the total sales for each unique MonthName value in the Sales table. But only for the months where the Amount is greater than 2000. The results are sorted by MonthName. By default, the sort order is ascendant, i.e. equivalent to ASC.

SELECT MonthName, SUM(Amount) AS TotalSales  
FROM dbo.Sales  
WHERE Amount > 2000  
GROUP BY MonthName  
ORDER BY MonthName;  

12. Using GROUP BY with an expression

Group the sales by the first letter of the month name. This query groups the sales from the sample by the first three letters of the MonthName value using the LEFT function. The results are sorted by MonthAbbreviation.

SELECT LEFT(MonthName, 3) AS MonthAbbreviation, SUM(Amount) AS TotalSales  
FROM dbo.Sales  
GROUP BY LEFT(MonthName, 1)  
ORDER BY MonthAbbreviation;  

13. Using GROUP BY with ORDER BY

Find the total sales for each month, and order the results by the amount in descending order.

SELECT MonthName, SUM(Amount) AS TotalSales  
FROM dbo.Sales  
GROUP BY MonthName  
ORDER BY TotalSales DESC;  

This second-to-last query calculates the total sales for each unique MonthName value in the Sales table and orders the results by the TotalSales column in descending order.

14. Using the HAVING clause in a SQL Select statement

This last query finds the total sales for each month but only for the months with total sales greater than 4000, using the HAVING clause. The HAVING clause allows you to filter the results of a GROUP BY query based on aggregate values.

SELECT MonthName, SUM(Amount) AS TotalSales  
FROM dbo.Sales  
GROUP BY MonthName  
HAVING SUM(Amount) > 2000  
ORDER BY MonthName;

Explanation of the code, step by step:

1. The SELECT statement retrieves the MonthName column and the sum of the Amount column, aliased as TotalSales.
2. The FROM clause specifies the dbo.Sales table.
3. The GROUP BY clause groups the rows by MonthName.
4. The HAVING clause filters the groups by the sum of the Amount column, selecting only those groups with a sum greater than 4000.
5. The ORDER BY clause sorts the results by MonthName.

The result will display the month names and the total sales for each month, but only for the months with total sales greater than 4000. The output gives something like this, if using the sample sales data.

MonthName	TotalSales
March	3000
June	3000
September	3000
December	3000

Conclusion on the SQL Server Select statement

In conclusion, this tutorial provides a comprehensive overview of SQL Server SELECT query examples for selecting, filtering, and displaying data from tables. The SELECT query is the most widely used and indispensable query in SQL, and this tutorial covers various aspects of its usage, including selecting single and multiple columns, using aliases and column headings, filtering data with the WHERE clause, using aggregation functions with GROUP BY, and filtering grouped data with the HAVING clause.

By following the examples and explanations provided in this tutorial, you can gain a solid understanding of how to use the SQL Server SELECT query to extract and manipulate data from tables. Whether you are a beginner or an experienced SQL user, this tutorial is a valuable resource for mastering the SELECT statement and its applications in SQL Server.

CREATE TABLE with SQL Server

More T-SQL tutorials on how to manage tables

• How to change a SQL Server table structure with ALTER TABLE?
• Tutorial on the SQL Server insert into from a select statement.
• Hoe to check if a table exists in SQL Server?