Reference no: EM133055432

Learning Activity:

Create your own database with the following name format mi602_your_student_number.

Create a single table that would hold information about a citizen entity for the tax department i.e. you would need to include fields to hold information such as:

1. Name
2. Address
3. Data of Birth
4. Tax File Number
5. Other fields that you deem necessary

Make sure that you include a variety of data types. e.g date, character,number

Insert some rows of data into your table using SQL statements and create queries that will answer the following questions. You will need at least 15 rows to provide meaningful results.

1. What is the average age of citizens?
2. What is the average income of citizens?
3. Which state has the highest income earner?
4. The department wishes to add a citizen's title to the table i.e. a field to hold the value of "Mr", "Mrs", "Dr" etc.
   Alter your table to add the new column and update your data using SQL statements for both activities.
5. Given the following tax table create a view that shows how much tax each person will pay. The view should show title, name, state and tax paid.
   You can name this view vw_TaxPayable.
6. How many people pay no tax?

   Taxable income	Tax on this income
   0 - $18,200	Nil
   $18,201 - $45,000	19 cents for each $1 over $18,200
   $45,001 - $120,000	$5,092 plus 32.5 cents for each $1 over $45,000
   $120,001 - $180,000	$29,467 plus 37 cents for each $1 over $120,000
   $180,001 and over	$51,667 plus 45 cents for each $1 over $180,000

7. Calculate the total tax collected for all citizens. 
8. Write a query to remove data from your table for people who pay no tax.
   Document your solution to this activity in a Word document and post your database create, data insert, table alter, create view statement and SQL statement results into Module 5.1 mi602_your_student_number database discussion forum.