ConsoleFlare
  • Python
    • Python Installation
    • Pandas and SQL
  • Projects
    • Data Analytics Project
      • Courier Analytics Challenge
      • Solution
    • Skytrax Airline Review Analysis Pipeline
      • Setting up Azure SQL Database
      • SkyTrax Web Scraping
  • Reporting
    • Power BI
      • Installation
      • Data Sources
      • Important Links
  • PySpark & Databricks
    • Spark vs Hadoop
    • Cluster Computing
    • PySpark
    • Databricks Introduction
    • PySpark in Databricks
    • Reading Data with PySpark
    • PySpark Transformation Methods
    • Handling Duplicate Data
    • PySpark Action Methods
    • PySpark Native Functions
    • Partitioning
    • Bucketing
    • Partitioning vs Bucketing
  • Live Data Streaming
    • Spark Streaming
      • Installation Issues
      • Jupyter Notebook Setup
  • Data Pipeline
    • Azure Data Factory
  • Blockchain
    • Smart Contract Guide
      • Setting up a Node project
      • Developing smart contracts
  • Interview Questions
    • SQL Interview Questions
    • Power BI Interview Questions
  • T-SQL Exercises
    • Exercise 0
    • Exercise 1
    • Exercise 2
    • Exercise 3
  • CHEAT SHEET
    • Ultimate SQL Server Cheat Sheet
Powered by GitBook
On this page

Was this helpful?

  1. T-SQL Exercises

Exercise 0

Function in SQL

  • Create a database named practice.

  • Run the below queries to generate a table.

CREATE TABLE SalesData (
    SaleID INT PRIMARY KEY,
    SaleDate DATE,
    ProductName NVARCHAR(50),
    Quantity INT,
    UnitPrice DECIMAL(10, 2),
    Discount DECIMAL(5, 2),
    Region NVARCHAR(50),
    Salesperson NVARCHAR(50)
);

INSERT INTO SalesData (SaleID, SaleDate, ProductName, Quantity, UnitPrice, Discount, Region, Salesperson)
VALUES
(1, '2024-01-01', 'Laptop', 2, 1500.00, 0.10, 'North', 'Alice'),
(2, '2024-01-05', 'Mouse', 5, 25.00, 0.05, 'South', 'Bob'),
(3, '2024-01-10', 'Keyboard', 3, 50.00, 0.15, 'East', 'Charlie'),
(4, '2024-01-15', 'Monitor', 1, 300.00, 0.20, 'West', 'Alice'),
(5, '2024-01-20', 'Laptop', 1, 1500.00, 0.00, 'North', 'Eve'),
(6, '2024-01-25', 'Mouse', 10, 25.00, 0.10, 'South', 'Bob'),
(7, '2024-01-30', 'Keyboard', 7, 50.00, 0.00, 'East', 'Charlie'),
(8, '2024-02-01', 'Monitor', 2, 300.00, 0.05, 'West', 'Alice'),
(9, '2024-02-05', 'Laptop', 3, 1500.00, 0.15, 'North', 'Eve'),
(10, '2024-02-10', 'Mouse', 4, 25.00, 0.10, 'South', 'Bob');

Write SQL Queries to solve for the following:

  1. Calculate the total revenue (Quantity * UnitPrice) for all sales.

  2. Find the average discount given across all products.

  3. Determine the maximum quantity sold in a single transaction.

  4. Count the number of sales transactions in the North region.

  5. Write a SELECT query to extract the first three characters of each product name.

  6. Use a SELECT query to convert all salesperson names to uppercase.

  7. Write a SELECT query to replace "Laptop" with "Notebook" in the product names.

  8. Use a SELECT query to extract the month from each sale date.

  9. Write a SELECT query to calculate the number of days between the earliest and latest sale dates.

  10. Use a SELECT query to calculate the year-to-date total revenue.

  11. Write a SELECT query to round the UnitPrice to the nearest whole number for all products.

  12. Use a SELECT query to calculate the square of the quantity sold for each transaction.

  13. Use a SELECT query to categorize sales as "High Value" if the revenue exceeds $1000, otherwise "Low Value."

  14. Write a SELECT query to determine if any sales had a discount greater than 10%.

  15. Write a SELECT query to calculate the average discount, ignoring any NULL values (simulate NULLs if needed).

  16. Use a SELECT query to convert the SaleDate into a formatted string like YYYY-MM-DD and display it.

  17. Write a SELECT query to cast the UnitPrice as an integer and display it.

PreviousPower BI Interview QuestionsNextExercise 1

Last updated 5 months ago

Was this helpful?