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.