Sales Transaction Application
This application is used by convenient stores for their daily transactions and invoicing
purposes.
Create at least following tables:
1. Product
2. Customer
3. Sales Transaction
4. Invoice
Based on created tables, create the following using TSQL
1. Store Procedures (Json as parameter for all SP preferred):
i. Create stored procedure for all CRUD operations.
ii. Create Invoice with following conditions:
• Once the invoice is generated for the customer, tag the sales transaction with the
correct invoice.
• Even if a customer buys multiple items, create a single invoice (bill) only.
• Should calculate a discount of 5% if the total invoice amount is less than or equal
to 1000 and 10% if the total invoice amount is greater than 1000.
Note: On above Stored procedures try to use rollback concept.
2. Query to return following:
1. List of customers whose name starts with the letter "A" or ends with the letter "S" but
should have the letter "K".
2. Customers whose invoice is not processed yet.
3. Name of customer who has spent highest amount in a specific date range.
4. Remove the product which is not bought in the current year.
5. The product should have a remaining column which shows the remaining quantity of
the product. This should be updated on the basis of sales transactions. List out the
products whose remaining quantity is less than 2.
6. Get the product of the year (The product that was bought by maximum customers this
year.)
7. Return the list of customers who bought more than 10 products.
3. Create a function with following:
• Input Parameters:
• Customer (can pass multiple ids e.g.: 103,904)
• Start Date and End Date
• Requirement:
• Should return total bill amount of the customer in the given date range.
1|Page
4. Create a Store Procedure:
• Input Parameters:
• Start Date and End date
• Customer Id (if no customer Id is passed then return all data else the given customer’s
data only)
• Requirement:
• Should return all the customers’ information entered in the database within the date
range
• Should return the total invoice (bill) amount of the customer
Try to use Json for input parameter and to return values.
2|Page
