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

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.

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

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

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

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

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

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

• 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

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

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

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”.

PART A

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

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.

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.

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.

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.

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.

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’.

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.

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

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).

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.

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.