Provide a Microsoft SQL Server (T-SQL) example
for each of the following topics using a single
sample database: ORDER BY, GROUP BY, ALL and
ANY clauses, TOP clause
Include the full database schema and sample data and demonstrate each concept with a
complete SQL query.
Below is a comprehensive Microsoft SQL Server (T-SQL) example for each requested topic,
using a single sample database. The example includes a full database schema, sample data,
and queries for ORDER BY, GROUP BY, ALL and ANY clauses, and TOP clause.
Database Schema and Sample Data
Let's create a sample database called SampleSales with a single table SalesData to demonstrate
all concepts.
-- Create the SampleSales database
IF NOT EXISTS (SELECT * FROM [Link] WHERE name = 'SampleSales')
CREATE DATABASE SampleSales;
GO
USE SampleSales;
GO
-- Create the SalesData table
CREATE TABLE SalesData (
SaleID INT PRIMARY KEY,
ProductName NVARCHAR(50),
Category NVARCHAR(30),
SalesAmount DECIMAL(10, 2),
Location NVARCHAR(30),
SaleDate DATE
);
GO
-- Insert sample data
INSERT INTO SalesData (SaleID, ProductName, Category, SalesAmount, Location, SaleDate)
VALUES
(1, 'Laptop', 'Electronics', 999.99, 'New York', '2025-01-10'),
(2, 'Smartphone', 'Electronics', 699.99, 'Chicago', '2025-01-11'),
(3, 'Desk', 'Furniture', 299.99, 'New York', '2025-01-12'),
(4, 'Chair', 'Furniture', 199.99, 'Chicago', '2025-01-13'),
(5, 'Laptop', 'Electronics', 999.99, 'Chicago', '2025-01-13'),
(6, 'Smartphone', 'Electronics', 699.99, 'New York', '2025-01-14'),
(7, 'Desk', 'Furniture', 299.99, 'Chicago', '2025-01-15'),
(8, 'Chair', 'Furniture', 199.99, 'New York', '2025-01-15');
GO
Example 1: ORDER BY
Sort sales by SalesAmount in descending order.
SELECT SaleID, ProductName, SalesAmount
FROM SalesData
ORDER BY SalesAmount DESC;
Example 2: GROUP BY
Group sales by Category and calculate the total sales amount for each category.
SELECT Category, SUM(SalesAmount) AS TotalSales
FROM SalesData
GROUP BY Category;
Example 3: ALL and ANY Clauses
Find products whose sales amount is greater than ALL sales of chairs, and products whose sales
amount is greater than ANY sale of chairs.
-- ALL: SalesAmount > ALL sales of chairs
SELECT ProductName, SalesAmount
FROM SalesData
WHERE SalesAmount > ALL (
SELECT SalesAmount
FROM SalesData
WHERE ProductName = 'Chair'
);
-- ANY: SalesAmount > ANY sale of chairs
SELECT ProductName, SalesAmount
FROM SalesData
WHERE SalesAmount > ANY (
SELECT SalesAmount
FROM SalesData
WHERE ProductName = 'Chair'
);
Example 4: TOP Clause
Select the top 3 sales with the highest sales amount.
SELECT TOP 3 SaleID, ProductName, SalesAmount
FROM SalesData
ORDER BY SalesAmount DESC;
These examples use a single database and table to demonstrate key T-SQL concepts, aligning
with your interest in SQL concepts and functions [1] [2] . The schema and data are simple but
realistic, making it easy to understand how each clause works.
<div style="text-align: center">⁂</div>
1. learning.database_systems
2. [Link]
3. [Link]
4. [Link]
5. [Link]
base-schema?view=sql-server-ver17
6. [Link]
7
7. [Link]
ver17
8. [Link]
9. [Link]
-transact-sql?view=sql-server-ver17
10. [Link]