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:

Calculate the total revenue (Quantity * UnitPrice) for all sales.
Find the average discount given across all products.
Determine the maximum quantity sold in a single transaction.
Count the number of sales transactions in the North region.
Write a SELECT query to extract the first three characters of each product name.
Use a SELECT query to convert all salesperson names to uppercase.
Write a SELECT query to replace "Laptop" with "Notebook" in the product names.
Use a SELECT query to extract the month from each sale date.
Write a SELECT query to calculate the number of days between the earliest and latest sale dates.
Use a SELECT query to calculate the year-to-date total revenue.
Write a SELECT query to round the UnitPrice to the nearest whole number for all products.
Use a SELECT query to calculate the square of the quantity sold for each transaction.
Use a SELECT query to categorize sales as "High Value" if the revenue exceeds $1000, otherwise "Low Value."
Write a SELECT query to determine if any sales had a discount greater than 10%.
Write a SELECT query to calculate the average discount, ignoring any NULL values (simulate NULLs if needed).
Use a SELECT query to convert the SaleDate into a formatted string like YYYY-MM-DD and display it.
Write a SELECT query to cast the UnitPrice as an integer and display it.

PreviousPower BI Interview Questions NextExercise 1

Last updated 5 months ago

Was this helpful?

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');