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.
Table of Contents
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:
- Horizontal filtering
- 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:
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 :
- The exact number of columns used
- The name of the columns
- 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
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
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
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:
SELECTstatement retrieves the
MonthNamecolumn and the sum of the
Amountcolumn, aliased as
FROMclause specifies the
GROUP BYclause groups the rows by
HAVINGclause filters the groups by the sum of the
Amountcolumn, selecting only those groups with a sum greater than 4000.
ORDER BYclause sorts the results by
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.
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.