SQL Server Advanced Data Analysis Queries With CREATE TABLE, INSERT DATA, Query and Result

 

. Create Sales Table

CREATE TABLE Sales
(
    SaleID INT PRIMARY KEY,
    ProductName VARCHAR(100),
    Category VARCHAR(50),
    CustomerName VARCHAR(100),
    City VARCHAR(50),
    Quantity INT,
    Price DECIMAL(10,2),
    Amount DECIMAL(10,2),
    SaleDate DATE
);

2. Insert Sample Data

INSERT INTO Sales VALUES
(1,'Laptop','Electronics','Amit','Nagpur',1,50000,50000,'2026-05-01'),

(2,'Mouse','Electronics','Rahul','Pune',2,400,800,'2026-05-02'),

(3,'Laptop','Electronics','Sneha','Mumbai',1,55000,55000,'2026-05-03'),

(4,'Chair','Furniture','Vikas','Nagpur',2,3500,7000,'2026-05-04'),

(5,'Mouse','Electronics','Amit','Nagpur',3,400,1200,'2026-05-05'),

(6,'Table','Furniture','Rahul','Pune',1,9000,9000,'2026-05-06'),

(7,'Mobile','Electronics','Sneha','Mumbai',2,15000,30000,'2026-05-07'),

(8,'Laptop','Electronics','Amit','Nagpur',1,60000,60000,'2026-05-08');

Table Data

SaleIDProductNameCategoryCustomerNameCityQuantityPriceAmountSaleDate
1LaptopElectronicsAmitNagpur150000500002026-05-01
2MouseElectronicsRahulPune24008002026-05-02
3LaptopElectronicsSnehaMumbai155000550002026-05-03
4ChairFurnitureVikasNagpur2350070002026-05-04
5MouseElectronicsAmitNagpur340012002026-05-05
6TableFurnitureRahulPune1900090002026-05-06
7MobileElectronicsSnehaMumbai215000300002026-05-07
8LaptopElectronicsAmitNagpur160000600002026-05-08

3. Total Sales Analysis

Query

SELECT 
    SUM(Amount) AS TotalSales
FROM Sales;

Result

TotalSales
213000

4. Product Wise Sales Analysis

Query

SELECT 
    ProductName,
    SUM(Amount) AS TotalSales
FROM Sales
GROUP BY ProductName
ORDER BY TotalSales DESC;

Result

ProductNameTotalSales
Laptop165000
Mobile30000
Table9000
Chair7000
Mouse2000

5. City Wise Revenue Analysis

Query

SELECT 
    City,
    SUM(Amount) AS Revenue
FROM Sales
GROUP BY City
ORDER BY Revenue DESC;

Result

CityRevenue
Nagpur118200
Mumbai85000
Pune9800

6. Monthly Sales Analysis

Query

SELECT 
    YEAR(SaleDate) AS Year,
    MONTH(SaleDate) AS Month,
    SUM(Amount) AS MonthlyRevenue
FROM Sales
GROUP BY YEAR(SaleDate), MONTH(SaleDate);

Result

YearMonthMonthlyRevenue
20265213000

7. Top Customer Analysis

Query

SELECT 
    CustomerName,
    SUM(Amount) AS TotalPurchase
FROM Sales
GROUP BY CustomerName
ORDER BY TotalPurchase DESC;

Result

CustomerNameTotalPurchase
Amit111200
Sneha85000
Rahul9800
Vikas7000

8. Rank Highest Sales Using RANK()

Query

SELECT 
    ProductName,
    CustomerName,
    Amount,
    RANK() OVER(ORDER BY Amount DESC) AS RankNo
FROM Sales;

Result

ProductNameCustomerNameAmountRankNo
LaptopAmit600001
LaptopSneha550002
LaptopAmit500003
MobileSneha300004
TableRahul90005
ChairVikas70006
MouseAmit12007
MouseRahul8008

9. Running Total Analysis

Query

SELECT 
    SaleDate,
    ProductName,
    Amount,
    SUM(Amount) OVER(ORDER BY SaleDate) AS RunningTotal
FROM Sales;

Result

SaleDateProductNameAmountRunningTotal
2026-05-01Laptop5000050000
2026-05-02Mouse80050800
2026-05-03Laptop55000105800
2026-05-04Chair7000112800
2026-05-05Mouse1200114000
2026-05-06Table9000123000
2026-05-07Mobile30000153000
2026-05-08Laptop60000213000

10. Percentage Contribution Analysis

Query

SELECT 
    ProductName,
    Amount,
    CAST(
        Amount * 100.0 / SUM(Amount) OVER()
        AS DECIMAL(10,2)
    ) AS ContributionPercent
FROM Sales;

Result

ProductNameAmountContributionPercent
Laptop5000023.47
Mouse8000.38
Laptop5500025.82
Chair70003.29
Mouse12000.56
Table90004.23
Mobile3000014.08
Laptop6000028.17

11. Find Duplicate Products

Query

SELECT 
    ProductName,
    COUNT(*) AS DuplicateCount
FROM Sales
GROUP BY ProductName
HAVING COUNT(*) > 1;

Result

ProductNameDuplicateCount
Laptop3
Mouse2

12. Pivot Table Analysis

Query

SELECT *
FROM
(
    SELECT Category, Amount
    FROM Sales
) AS SourceTable
PIVOT
(
    SUM(Amount)
    FOR Category IN ([Electronics], [Furniture])
) AS PivotResult;

Result

ElectronicsFurniture
19700016000

13. Second Highest Sale

Query

SELECT MAX(Amount) AS SecondHighestSale
FROM Sales
WHERE Amount < (SELECT MAX(Amount) FROM Sales);

Result

SecondHighestSale
55000

14. Average Sales Per Product

Query

SELECT 
    ProductName,
    AVG(Amount) AS AverageSales
FROM Sales
GROUP BY ProductName;

Result

ProductNameAverageSales
Laptop55000
Mouse1000
Chair7000
Table9000
Mobile30000

Comments

Post a Comment

Popular posts from this blog

STORE PROCEDURE BASIC PROGRAMMING - PART 1

  1. Create a Stored Procedure CREATE PROCEDURE sp_GreetUser AS BEGIN PRINT 'Welcome to SQL Server Stored Procedure Programming' ; END ; ================================================================================ Stored Procedure with Parameters Create Procedure CREATE PROCEDURE sp_AddNumbers @Num1 INT , @Num2 INT AS BEGIN DECLARE @ Result INT ; SET @ Result = @Num1 + @Num2; PRINT 'Addition Result = ' + CAST (@ Result AS VARCHAR ( 10 )); END ; Execute Procedure EXEC sp_AddNumbers 10 , 20 ; ========================================================================== Stored Procedure with Parameters Create Procedure CREATE PROCEDURE sp_AddNumbers @Num1 INT , @Num2 INT AS BEGIN DECLARE @ Result INT ; SET @ Result = @Num1 + @Num2; PRINT 'Addition Result = ' + CAST (@ Result AS VARCHAR ( 10 )); END ; Execute Procedure EXEC sp_AddNumbers 10 , 20 ; ======================...
Read more

SQL SERVER HAVING CLAUSE QUERY

SQL Server HAVING Clause Example The HAVING clause is used to filter grouped data after using GROUP BY . Syntax SELECT column_name, aggregate_function(column_name) FROM table_name GROUP BY column_name HAVING condition ; Example: Find Departments Having Total Salary Greater Than 100000 Employee Table EmpID EmpName Department Salary 1 Amit HR 25000 2 Ravi IT 40000 3 Neha IT 35000 4 Priya HR 30000 5 Karan Sales 45000 6 Simran Sales 60000 SQL Query SELECT Department, SUM(Salary) AS TotalSalary FROM Employee GROUP BY Department HAVING SUM(Salary) > 100000 ; Result Department TotalSalary Sales 105000 Explanation GROUP BY Department → Groups employees department-wise. SUM(Salary) → Calculates total salary of each department. HAVING SUM(Salary) > 100000 → Displays only departments whose total salary is greater than 100000. Difference Between WHERE and HAVING WHERE HAVING Filters rows before grouping Filters groups after grouping Cannot use aggregate functions direc...
Read more

Python Introduction

PYTHON PROGRAMING https://thonny.org/ Python supports multiple programming paradigms, including object-oriented, procedural, and functional programming . It provides a rich standard library and a large ecosystem of third-party packages, which helps developers to build applications faster and more efficiently. Python is platform-independent, meaning Python programs can run on Windows, Linux, and macOS without modification. Due to its flexibility and powerful features, Python is widely used in fields such as: ·          Web Development ·          Data Science & Data Analytics ·          Artificial Intelligence & Machine Learning ·          Automation & Scripting ·          Desktop Application Development · ...
Read more