Introduction
Students have to complete all the following parts of this assignment:
A. SQL Query
B. Query Analysis
C. Form
D. Report
Important
Download the Microsoft Access database, Shares.accdb,from the course website and use it to complete this assignment.
If you get a security warning message when opening the downloaded database,then you need to open/click the options and then choose the option "enable this content".
Do not modify the structures of the tables and relationships. Do not create additional tables. It is not recommended to delete any of the existing records from the downloaded database (hereafter referred as ‘your database').
You need to write SQL queries in your database and make sure those queries produce the desired results as shown in Part A. Save and name those queries suitably such as Q1, Q2, ......, Q10 respective to the question that you are answering.
You need to create aMS Access-form and also a MS Access-report that are related to Part C and Part D and save them in your database.
Shares.accdb
The database, Shares.accdb, is maintained by an individual performing share trading activities. It contains five tables thatcan store the data related to trading of shares of some of the listed companies in Australian Stock Exchange.The following table provides the details of the data stored in those tables.
|
Table name
|
Data stored
|
|
Share
|
Some of the listed companies in ASX (Australian Stock Exchange) in which the individual is interested.
|
|
ShareRegistry
|
Share registry companies and their contact numbers.[Each listed company, stored in shares-table, must be related to one and only one share registry company].
|
|
ShareTrade
|
Trade orders issued for buying or selling of shares of the listed companies that are found in the shares-table.
|
|
Brokerage
|
Rate of brokerage applicable for a share trade.
|
|
Transaction
|
Completed transaction(s) related to the relevant share trade order(s). [Each share trade order can be completed by one or many transactions. Only after the completion of transactions, the amount is payable/receivable. Further details can be seen in part A-Q8].
|
Part A - SQL Query
Using your database, write SQL queries toanswer all the questions in this part.Each of the following questions has an information request followed by theexpected 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 datadisplayed;removal of duplicate data; and other aspects of the query.
1. Display the details of all the listed shares/companies along with its share registrydetails.
|
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
|
2. 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
|
3. 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
|
4. Displaythe transacted amountof the highest valued-share transaction(s)performed.
|
HighestValuedTransaction
|
|
$20,000.00
|
5. 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
|
6. 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
|
7. Display the details of share registry company whose name contains the word ‘computer'.
|
ShareRegistryName
|
ContactNumber
|
|
Computer Share
|
1300787272
|
8. 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
|
9. 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
|
10. 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
Usingyourdatabase, 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
Usingyourdatabase, develop a report to display the details of Share, ShareTrade andTransactions. Name the report as "Share Transactions History".
Attachment:- IT 2.rar