Operation Sheet-2.
1 Arithmetic operators and string function
Operation title: Arithmetic operators and string function
Purpose: To show functionalities of arithmetic operators and string functions in a table
Equipment tools and materials: SQL server 2008 and above
Step 1: Use merchant database from operation sheet 1.1
Step 2: Calculate the total amount (UnitPrice * Quantity) for each order item and display the result with the order item ID
and the total amount.
SELECT Id, UnitPrice * Quantity AS TotalAmount FROM OrderItem;
Quality Criteria: your output should look like this
Step 3 Calculate the average of quantity orderd by customer
select AVG(Quantity) from OrderItem
Quality Criteria: your output should look like this
Step 4 to count or calculate the total record in a table
SELECT COUNT(*) AS totalProduct FROM Product;
Quality Criteria: your output should look like this
Step 5 to calculate quantity of the total product orderd by the customer
select SUM(Quantity) AS totalOrderdQuantity from OrderItem;
Quality Criteria: your output should look like this
Step 6: To retrieve the product names that start with the letter "A" and display them in uppercase.
➢ SELECT UPPER(ProductName) AS ProductName FROM Product
WHERE ProductName LIKE 'A%';
Quality Criteria: your output should look like this
Step 6 to retrieve the customers firstname first latter asci code and display
➢ SELECT ASCII(FirstName) AS NumCodeOfFirstChar FROM Customers;
Quality Criteria: your output should look like this
Step5 to compare customers firstName and LastName SOUNDEX values
➢ SELECT DIFFERENCE(FirstName, LastName) as sounddiff from Customers;
Quality Criteria: your output should look like this
Step 6 to calculating the number of bytes required to store a field
➢ SELECT DATALENGTH(FirstName)as fNamebyteLength FROM Customers;
Quality Criteria: your output should look like this
Step 7 to calculate the character length of atable columon
SELECT len(FirstName)as fNameDataLength FROM Customers;
Quality Criteria: your output should look like this
Step 8 to searches a substring in a string, and returns the position
SELECT CHARINDEX('a',FirstName,2)AS MatchPosition from Customers;
Quality Criteria: your output should look like this
Operation Sheet-2.2 Date function
Operation title: Date function
Purpose: To show functionalities of some date functions
Equipment tools and materials: SQL server 2008 and above
Step 1: Use merchant database from operation sheet 1.1
Step 2: let us create a table called orders which stores orders of customers with their order id, product name and the date, which the
order is placed. We can write the table as shown below
➢ CREATE TABLE Orders (
OrderId int NOT NULL PRIMARY KEY IDENTITY,
ProductName varchar(50) NOT NULL, OrderDate datetime DEFAULT GETDATE()
)
Step 3: to insert order data in the orders table we can write the following statement
➢ INSERT INTO Orders1111 (ProductName) VALUES ('Shola Milk')
select *from orders
Quality Criteria: your output should look like this
Step 4: From the previous table, if we want to retrieve the date in year, month, date format, we can write the following query
➢ SELECT DATEPART(yyyy,OrderDate) AS OrderYear, DATEPART(mm,OrderDate) AS OrderMonth,
DATEPART(dd,OrderDate) AS OrderDay
FROM Orders1111 WHERE OrderId=1
Quality Criteria: your output should look like this
Step 5: If we want to add 30 days to the "OrderDate", to find the payment date. We can write the following query
➢ SELECT OrderId,DATEADD(day,30,OrderDate) AS OrderPayDate FROM Orders1111
Quality Criteria: your output should look like this
Lap Test
Instruction: Use ABC database (from unit one lap test) in order to perform the following tasks
Task 1: Retrieve the total number of products
Task 2: Calculate the total amount (ORD_AMOUNT + ADVANCE_AMOUNT) for each order
and display the result with the order number and the total amount.
Task 3: Calculate the average commission for all agents and display the result rounded to two
decimal places.
