Write the queries to answer the following questions. Every column MUST have a column heading. ( 1
Question:
Write the queries to answer the following questions. Every column MUST have a column heading.
(1 mark) What is the ward name (name_2) and councillor for each Ward in Edmonton? You can use the EdmontonWard table to answer this question.
(1 mark) What is the number, name, and description of each neighbourhood that contains the word beautiful in its description? Sort the results by the neighbourhood name. You can use the EdmontonNeighbourhood table to answer this question.
(1 mark) What is the averaged assessed value for a residential property in Edmonton in the tax year of 2022? You can use the EdmontonPropertyAssessment table to answer this question.
(1 mark) What is the total tax rate (Municipal + Education + Education Requestion Allowance) in the tax year of 2022? You can use the EdmontonPropertyTaxRate table to answer this question.
(1 mark) How many payments were made on June 30, 2022? You can use the EdmontonPropertyTaxPayment table to answer this question.
(1 mark) What is the highest, lowest, and average payment amount in December 2022? You can use the EdmontonPropertyTaxPayment table to answer this question.
(1 mark) For each tax year, what is the average assessed value for a residental property? Sort the results descending by the year You can use the EdmontonPropertyAssessment table to answer this question.
(1 mark) How many properties are there in each neighbourhood that have more than 5000 residental properties? Display the neighbourhood number and the count. Sort the results descending by the count. You can use the EdmontonProperty table to answer this question.
(2 marks) What is the property address (house number and street name), neighbourhood name, and assessed value for all houses in the neighbourood of Windermere on the street name “171 STREET SW” and is between $425000 and $450000. Sort the results by the assessed value. You can use the EdmontonProperty, EdmontonPropertyAssessment, and EdmontonNeighbourhood tables to answer this question.
(2 marks) What is the account owners, account email, propery address (house number and street name), legal description, neighbourhood name, and ward name for account number 10002852 in the tax year of 2022? You can use the EdmontonProperty, EdmontonPropertyAssessment, EdmontonNeighbourhood, EdmontonWard and EdmontonPropertyOwner tables to answer this question.
(2 marks) What is the lowest, average, and highest assessed value for each neighbourhood? Show the neighbourhood descriptive name for each. Sort the results by the neighbourhood descriptive name. You can use the EdmontonProperty, EdmontonPropertyAssessment, EdmontonNeighbourhood, and EdmontonWard tables to answer this question.
(2 marks) In the tax year of 2022, what is the property address (house number and street name) and assessed value of the residential property with the highest assessed value? You can use the EdmontonProperty and EdmontonPropertyAssessment tables to answer this question. Hint: you will need to use a subquery to answer this question.
(2 marks) What is the neighbourhood number, descriptive neighbourhood name, and property count for all neighbourhoods including neighbourhoods that do not have any residential property? You can use the EdmontonProperty and EdmontonNeighbourhood tables to answer this question.
(5 marks) What is the property address (house number and street name), assessed value, Municipal tax, Eduction tax, and Education Requisition Allowance tax, and Total Tax for account number 10006194 in the tax year of 2022? You can use the EdmontonPropertyTaxRate, EdmontonProperty, and EdmontonPropertyAssessment tables to answer this question. (Hint: you may need use both joins and subqueries in the select list components to answer this question.)
(4 marks) What is the tax amount for each tax rate type including the total tax for account number 10006194 in tax year 2022? You can use the EdmontonPropertyTaxRate and EdmontonPropertyAssessment tables to answer this question. (Hint: you may need to use a union operator and subqueries in the select list components to answer this question.)
Views
(1 mark) Create a view called PropertyAssessmentHistory that will select the account number, property address (as one column with house number and street name), tax year, and assessed value. (2 marks)
(1 mark) Use the PropertyAssessmentHistory view to select the address, tax year, and assessed value for account numbers 10007189, 10009253, and 10013055.
DML
(2 marks) Insert the following record in the EdmontonPropertyAssessment table given the following data.
account_number | tax_year | assessed_value |
9990687 | 2023 | 10% increase from last year (2022) |
(1 mark) Insert the following record in the EdmontonPropertyTaxPayment table given the following data.
account_number | payment_amount | payment_date | Tax_year |
10015271 | 199.88 | 2023-03-01 | 2022 |
(2 marks) Update the payment balance in the EdmontonPropertyTaxNotice table to subtract the payment amount of $199.98 that was made to account number 10015271 on 2023-03-01.
(2 marks) Delete all neighbourhoods from the EdmontonNeighbourhood table that does not have residential properties.
Intermediate Financial Management
ISBN: 9780357516669
14th Edition
Authors: Eugene F Brigham, Phillip R Daves