SQL - Query Analysis - Form - Report | Information Technology Assignment Help

Solution Code: 1AII

Question:

This assignment is related to ”Information Technology” and experts atGrade Saviourssuccessfully delivered HD quality work within the given deadline.

Human Resource Assignment

Introduction

Students have to complete all the following parts of this assignment:

SQL Query
Query Analysis
Form
Report

Part A – SQL Query

- Using your database, write SQL queries to answer all the questions in this part. Each of the following questions has an information request followed by the expected results when your database has the given sample data.

Hint: To get the desired output, you need to check the column headings; grouping and sorting of data displayed; removal of duplicate data; and other aspects of the query.
Display the details of all the listed shares/companies along with its share registry details.

code	shareName	ShareRegistryName	contactNumber
AGL	AGL Energy Limited	Link Market services	1300554474
BHP	BHP Billiton Limited	Computer Share	1300787272
CSL	CSL Limited	Computer Share	1300787272
RIO	RIO Tinto Limited	Computer Share	1300787272
A2M	The A2 milk company	Link Market services	1300554474

Display the share name/company name for which trade orders have been placed including the number of trade orders placed.

sharename	numberOftradeOrders
BHP Billiton Limited	1
RIO Tinto Limited	2

List the details of shares(s)/company(s) for which there has been no trade order placed so far.

code	shareName	ShareRegistryName
A2M	The A2 milk company	Link Market services
CSL	CSL Limited	Computer Share
AGL	AGL Energy Limited	Link Market services

Display the transacted amount of the highest valued-share transaction(s) performed.

HighestValuedTransaction

$20,000.00

Display the share(s)/company(s) name and details of trade order for which only one transaction was required to complete the trade.

orderId	orderDate	shareName
2	04-Jan-16	BHP Billiton Limited

Display the total number of transactions performed for each of the buy orders placed

orderId	OrderDate	OrderType	Code	OrderedQuantity	numTransactionPerBuyTradeOrder
1	04-Jan-16	BUY	RIO	700	2
2	04-Jan-16	BUY	BHP	1000	1

Display the details of share registry company whose name contains the word ‘computer’.

ShareRegistryName	ContactNumber
Computer Share	1300787272

Display the details of each share trade order including net amount payable/receivable due to that order.

Hint: The net amount is payable for a buy order whereas net amount is receivable for a sell order. Gross amount = orderedQunatity X OrderedPrice Net amount for buy order = Gross amount + brokerage amount Net amount for sell order = Gross amount – brokerage amount

OrderId	OrderDate	OrderType	Code	OrderedQuantity	OrderedPrice	BrokerageID	NetAmount
1	04-Jan-16	BUY	RIO	700	$40.00	2	$28,070.00
2	04-Jan-16	BUY	BHP	1000	$16.00	1	$16,048.00
3	05-Jan-16	SELL	RIO	500	$41.00	2	$20,448.75

For each share trade order(s) placed, display the order details and quantity of shares either bought/sold by the relevant transaction(s).

OrderId	code	orderType	orderedQuantity	Quantitycompleted
1	RIO	BUY	700	700
2	BHP	BUY	1000	1000
3	RIO	SELL	500	150

Display the details of share trade orders that have not been completed by transactions along with the pending quantity of shares to be completed.

OrderId	OrderDate	OrderType	Code	OrderedQuantity	OrderedPrice	BrokerageID	pendingQuantity
3	05-Jan-16	SELL	RIO	500	$41.00	2	350

Part B – Query Analysis

Explain the methodology of the SQL Query that you used to answer the question 8 in Part A.

Part C – Form

Using your database, develop a form which can be used for data entry for Transactions. You are allowed to use any number of tables/ any suitable layout for developing that form. Name the form as “Transaction Entry”.

Part D – Report

Using your database, develop a report to display the details of Share, ShareTrade and Transactions. Name the report as “Share Transactions History”.

These assignments are solved by our professional Information Technology at Grade Saviours and the solution are high quality of work as well as 100% plagiarism free. The assignment solution was delivered within 2-3 Days. Our Assignment Writing Experts are efficient to provide a fresh solution to this question. We are serving more than 10000+ Students in Australia, UK & US by helping them to score HD in their academics. Our Experts are well trained to follow all marking rubrics & referencing style.

Solution:

PART A

Display the details of all the listed shares/companies along with its share registry details.

Code	shareName	ShareRegistryName	contactNumber
AGL	AGL Energy Limited	Link Market services	1300554474
BHP	BHP Billiton Limited	Computer Share	1300787272
CSL	CSL Limited	Computer Share	1300787272
RIO	RIO Tinto Limited	Computer Share	1300787272
A2M	The A2 milk company	Link Market services	1300554474

SELECT Share.code, Share.shareName, Share.ShareRegistryName, ShareRegistry.ContactNumber FROM ShareRegistry INNER JOIN Share ON ShareRegistry.ShareRegistryName = Share.ShareRegistryName ORDER BY Share.shareName;

Display the share name/company name for which trade orders have been placed including the number of trade orders placed.

Sharename	numberOftradeOrders
BHP Billiton Limited	1
RIO Tinto Limited	2

SELECT Share.shareName, Count(Share.shareName) AS NoOftradeorders FROM Share INNER JOIN ShareTrade ON Share.code=ShareTrade.Code GROUP BY Share.shareName ORDER BY Share.shareName;

List the details of shares(s)/company(s) for which there has been no trade order placed so far.

code	shareName	ShareRegistryName
A2M	The A2 milk company	Link Market services
CSL	CSL Limited	Computer Share
AGL	AGL Energy Limited	Link Market services

SELECT Share.code, Share.shareName, Share.ShareRegistryName FROM Share, ShareTrade WHERE (((Share.code) Not In (select ShareTrade.Code from shareTrade))) GROUP BY Share.code, Share.shareName, Share.ShareRegistryName;

Display the transacted amount of the highest valued-share transaction(s) performed.

HighestValuedTransaction

$20,000.00

SELECT max(ShareTrade.OrderedPrice) AS highestvaluedtransaction FROM ShareTrade;

Display the share(s)/company(s) name and details of trade order for which only one transaction was required to complete the trade.

orderId	orderDate	shareName
2	04-Jan-16	BHP Billiton Limited

SELECT ShareTrade.OrderId, ShareTrade.OrderDate, Share.shareName FROM Share, sharetrade WHERE sharetrade.code= share.code and sharetrade.code in ( SELECT code from sharetrade group by code having count(code)=1) GROUP BY orderid, sharetrade.code, orderdate, share.sharename;

Display the total number of transactions performed for each of the buy orders placed.

orderId	OrderDate	OrderType	Code	OrderedQuantity	numTransactionPerBuyTradeOrder
1	04-Jan-16	BUY	RIO	700	2
2	04-Jan-16	BUY	BHP	1000	1

SELECT ShareTrade.OrderId, ShareTrade.OrderDate, ShareTrade.OrderType, ShareTrade.Code, ShareTrade.OrderedQuantity, count(transaction.orderid) AS numtransactionperbuytradeorder FROM ShareTrade, [transaction] WHERE sharetrade.orderid=transaction.orderid And sharetrade.ordertype='BUY' GROUP BY ShareTrade.OrderId, ShareTrade.OrderDate, ShareTrade.OrderType, ShareTrade.Code, ShareTrade.OrderedQuantity;

Display the details of share registry company whose name contains the word ‘computer’.

ShareRegistryName	ContactNumber
Computer Share	1300787272

SELECT ShareRegistry.ShareRegistryName, ShareRegistry.ContactNumber FROM ShareRegistry WHERE shareregistryname like 'Computer Share';

Display the details of each share trade order including net amount payable/receivable due to that order.

OrderId	OrderDate	OrderType	Code	OrderedQuantity	OrderedPrice	BrokerageID	NetAmount
1	04-Jan-16	BUY	RIO	700	$40.00	2	$28,070.00
2	04-Jan-16	BUY	BHP	1000	$16.00	1	$16,048.00
3	05-Jan-16	SELL	RIO	500	$41.00	2	$20,448.75

SELECT ShareTrade.OrderId, ShareTrade.OrderDate, ShareTrade.OrderType, ShareTrade.Code, ShareTrade.OrderedQuantity, ShareTrade.OrderedPrice, ShareTrade.BrokerageID, IIf(sharetrade.code='BUY',((orderedquantity*orderedprice)+Round((orderedquantity*orderedprice)*brokerageratepercent)),((orderedquantity*orderedprice)-Round((orderedquantity*orderedprice)*brokerageratepercent))) AS netamount FROM Brokerage INNER JOIN ShareTrade ON Brokerage.BrokerageId = ShareTrade.BrokerageID ORDER BY ShareTrade.OrderId;

For each share trade order(s) placed, display the order details and quantity of shares either bought/sold by the relevant transaction(s).

OrderId	code	orderType	orderedQuantity	Quantitycompleted
1	RIO	BUY	700	700
2	BHP	BUY	1000	1000
3	RIO	SELL	500	150

SELECT Transaction.OrderId, ShareTrade.OrderType, ShareTrade.Code, ShareTrade.OrderedQuantity, Sum(Transaction.QuantityCompleted) AS QuantityCompleted FROM ShareTrade, [Transaction] WHERE (((ShareTrade.OrderId)=[Transaction].[OrderId])) GROUP BY Transaction.OrderId, ShareTrade.OrderType, ShareTrade.Code, ShareTrade.OrderedQuantity;

Display the details of share trade orders that have not been completed by transactions along with the pending quantity of shares to be completed.

OrderId	OrderDate	OrderType	Code	OrderedQuantity	OrderedPrice	BrokerageID	pendingQuantity
3	05-Jan-16	SELL	RIO	500	$41.00	2	350

SELECT Transaction.OrderId, ShareTrade.orderdate, ShareTrade.OrderType, ShareTrade.Code, ShareTrade.OrderedQuantity, ShareTrade.OrderedPrice, ShareTrade.BrokerageID, (sharetrade.orderedquantity-Sum(Transaction.QuantityCompleted)) AS PendingQuantity FROM ShareTrade, [Transaction] WHERE (((ShareTrade.OrderId)=[Transaction].[OrderId])) GROUP BY Transaction.OrderId, ShareTrade.orderdate, ShareTrade.OrderType, ShareTrade.Code, ShareTrade.OrderedQuantity, ShareTrade.OrderedPrice, ShareTrade.BrokerageID; PART B: Query Analysis of Q8 The query8 of Part A is the display of the net amount calculated. In that net amount calculation, it has two clauses. The first clause is for BUY and the other one is for SELL Gross amount = orderedQunatity X OrderedPrice Net amount for buy order = Gross amount + brokerage amount Net amount for sell order = Gross amount – brokerage amount The methodology used for this query is the IFF clause in the query statement. It is an transact sql query, it returns two values depending on the Boolean expression given PART C TRANSACTION ENTRY – FORM NAME It helps in the data entry of the table transactions. PART D REPORTS SHARE TRANSACTION HISTORY – REPORT NAME I have created 2 reports on the transaction history. One on the transaction basis and the other one on the share basis.

Find Solution for Information Technology assignment by dropping us a mail at help@gradesaviours.com along with the question’s URL. Get in Contact with our experts at Grade Saviours and get the solution as per your specification & University requirement.

Hey MAS, I need Assignment Sample of

Looking For Complete Assignment Sample

Get It On WhatsApp