Scribd
Upload
5 views

Arithmetic operators and string function

Uploaded by

Amanuel Kassa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
5 views

Arithmetic operators and string function

Uploaded by

Amanuel Kassa
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as DOCX, PDF, TXT or read online on Scribd
You are on page 1/ 6

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.

You might also like