Find certain governors general of australia

Assignment Help PL-SQL Programming

Reference no: EM131227615

Assignment - SQL Queries

Tasks

Part 1 -

Open the database prime_minister database (prime_ministersql ) from the ITC556 Interact Resources Databases folder. Answer the following queries using this database.

1. Find certain Governors General of Australia.

a. Find all Governors General of Australia who were Barons at the time of their appointment. List them by Title and name and date that they were appointed to the position. Order the list by ascending date of appointment.

b. Now, format the date of appointment as day of the week, day of the month, month and year; eg. Monday, 01 January, 1901. Order the list by ascending date of appointment.

2. Find certain Leaders of the Opposition.

a. Find all Leaders of the Opposition and their date of appointment, who assumed their position after 01/01/1980.

b. For each Leader of the Opposition listed, add their wife's name and their date of marriage.

3. Find certain Governors General of Australia.

a. List the title, name, date of appointment for Governors General of Australia who were appointed between 01 January 1930 and 01 January 1960. Order by ascending date of appointment.

b. Now add to the results of q3a, the list of Prime Ministers who appointed them and all Leaders of the Opposition who served during their appointment as Governor General of Australia. Order by ascending date of appointment.

4. Who are the Opposition Leaders who subsequently became Prime Minister after 1930?

a. List their name, the date they were elected Opposition Leader and the date they were elected Prime Minister. The dates must be formatted as day of the week, day of the month, month in digits and year in four digits: eg. Monday, 01/01/1901. Order the list in ascending date of appointment as Prime Minister.

b. Now add their Deputy Prime Minister's name and the party that they led. Order the list by ascending date of appointment as Opposition Leader.

Part 2 -

The design of the Prime_Ministers database is now very old. You have been asked to review this design, as shown in the ERD below and advise how it could be updated. You are to complete the following tasks:

5. Advise how you would improve the ability to query information in this database. For simplicity, use only the tables prime_minister, governor_general, ministry and opposition in your answer.

a. What new integrity constraints would you use in each of these tables?

b. Why would you use these integrity constraints? Explain how your constraints would improve queries on the tables.

c. Write the DDL code that would implement your new integrity constraints for the following tables:

i. Prime minister

ii. Governor General

iii. Ministry

iv. Opposition

(Note: You must keep all of the data attributes currently in these tables. Your implementation should include all existing data attributes and any new integrity constraints)

Attachment:- Assignment.rar

Reference no: EM131227615

Questions Cloud

Why does the budget require a forecast of the economy : Why does the budget require a forecast of the economy? Under what circumstances would actual government spending and tax revenue fail to match the budget as approved?

Explain central limit theorem in context of current problem : Explain the central limit theorem in the context of the current problem, and indicate whether it was needed to find these probabilities in a, b, and c (might differ for each of a, b, and c).

Describe a situation in which this might reasonably happen : Explain why TIME_WAIT is a somewhat more serious problem if t server initiates the close than if the client does. Describe a situation in which this might reasonably happen. (150 Word Minimum).

Why robert barro argues that if parents are concerned : (Case Study: An Intergenerational View of Deficits and Debt) Explain why Robert Barro argues that if parents are concerned about the future welfare of their children, the effects of deficit spending on the economy will be neutralized.

Find certain governors general of australia : Find certain Governors General of Australia. Find all Governors General of Australia who were Barons at the time of their appointment. List them by Title and name and date that they were appointed to the position. Order the list by ascending date o..

The cyclical nature of government tax revenues : Given the cyclical nature of government tax revenues and spending, how would the resulting budget deficit or surplus vary over the business cycle?

Create a brief methodology section for research paper : Create a brief methodology section for research paper. - You can choose between Qualitative or Quantitative methods.

Interactive multimedia in general : Do you think hypermedia documents (or interactive multimedia in general) will eclipse certain kinds of books and other media? If so, which ones and why?

Legitimizing the personal computer as a business tool : Spreadsheets are sometimes credited with legitimizing the personal computer as a business tool. Why do you think they had such an impact?

Reviews

len1227615

10/1/2016 3:33:11 AM

This assignment requires you to use MySQL to complete the tasks. The instructions listed above relate to MySQL. Typing the SQL statements or the results is NOT acceptable and will result in 0 marks for the assessment You are required to submit: The appropriate SQL statements for each query, which should be copied from your SQL code in MySQL and pasted into your submission file, and Screenshots of the resultant tables which are to be pasted into your submission file immediately after the SQL code for that query. Typing or manually drawing the results is NOT acceptable. This assessment will test your ability to implement a database design using Structured Query Language and to query a database using SQL.

Write a Review

Required(*) Message

User Account

All Pages