Create a view showing all the data plus

Assignment Help Database Management System

Reference no: EM133055443

Learning Activity:

You may work in small groups to perform this activity.

Downloaded the spreadsheet containing the account information for large electricity users in the Geelong region.

Use this spreadsheet and perform the following:

Create a database called mi602_geelong_power.
Create a table to hold the meter reading data from the spreadsheet.
Insert the data from the spreadsheet into you table. Column L in the spreadsheet contains a sample excel formula that will generate SQL statements to insert data into your table however keep in mind the following
- The formula has some deliberate mistakes, you will need to alter the formula.
- You will need to supply a table name.
- You may wish to change the column names in the formula to suit the columns in your table.
- You may need to format the data to suit the data types you choose for your table.
- There may be data quality issues.
Once you have corrected the formula, you can copy and paste the formula into the remaining cells of column L and then copy the completed SQL statements into a query window to populate your table with data.
Create a view showing all the data plus the number of days for each reading. This value can be called "Usage Days".
Create queries for the following:
- List the total peak and off-peak power demand for the recorded period.
- List the total peak usage by year for all businesses in Geelong (postcode 3220). Use the TO_DATE field to determine the year in which a value is to be recorded.
It was discovered that all the peak readings for Lara were off by a factor of 10, i.e.
For the dates 20140701 to 20140731. The value of the reading should have been 3692.961 instead of 369.2961.
For the dates 20160401 to 20160430. The value of the reading should have been 4047.880 instead of 404.788.
etc

Update the Lara data so that the values are correct. Can you do this in a single query?
It is suspected that one reading was accidentally entered twice.
Create queries to locate the duplicate entry
Create queries to remove the duplicate row.

Attachment:- geelong_power.rar

Reference no: EM133055443

Questions Cloud

Recidivist sexual offender behavior at distance : Registration is a concept or deterrence effort to keep recidivist sexual offender behavior at a distance.

Calculate the manufacturing cost before separation : Profit allowed for By-Product T is 20% of sales and for By-Product V is 15% of sales. Calculate the manufacturing cost before separation

Compute taxable income and income taxes payable : Windsor's pretax financial income for 2020 is $281,200, and the tax rate is 30% for all years. Compute taxable income and income taxes payable

Spouse and school administrators to proceed : Write a report outlining how you would tell your spouse and school administrators to proceed.

Create a view showing all the data plus : Create a view showing all the data plus the number of days for each reading - It is suspected that one reading was accidentally entered twice

Prepare a schedule of cash receipts from sales for April : X-Tel budgets sales of $102,000 for April, $174,000 for May, and $114,000 for June. Prepare a schedule of cash receipts from sales for April

Prepare the journal entry to record the purchase : Land is appraised at $234,000; land improvements are appraised at $78,000; Prepare the journal entry to record the purchase

Maintain good community relations : What environmental laws will apply to the new plant construction? What steps should you take to maintain good community relations?

What is the present worth of the contract : What is the present worth of the contract (in year 0) if the company will receive a total of nine $13,000 payments beginning in year 2 and ending in year 10

User Account

All Pages