Prime Minister SQL

Part A: SQL Queries

Use the Prime_Minister database (prime_minister.sql) Answer the following queries from that database.

Write SQL SELECT statements to retrieve the following information from the Prime_Minister database:

• Find the Governors General of Australia who were Barons at the time of their appointment. List them by Title, Name and the date that they were appointed to the position. The date is to be formatted 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.
• Who were the Leaders of the Opposition who were not members of a Labor party and who gained their position after 01/01/1980. List their name, date of election in the format month, day of week and year, eg: January 1st, 1980, spouse’s name and their year of marriage. Order the list by ascending date of appointment, then date of marriage
• List the Title, Name and date of appointment for Governors General of Australia who were appointed between 01 January 1930 and 01 January 1960, together with the Prime Ministers who appointed them and all Leaders of the Opposition who served during their appointment. The date is to be formatted in dd/mm/yyyy format. Order by ascending date of appointment.
• Who are the Opposition Leaders who subsequently became Prime Minister after 1930? List their name, the date they were elected Opposition Leader and the date they were elected Prime Minister and their Deputy Prime Minister’s name and the party that they led. 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 by ascending date of appointment as Opposition Leader.
• Who are the Prime Ministers who led a non-ALP party before 1930? List the Prime Ministers name, the year they commenced as Prime Minister, their party, their Deputy Prime Minister, the number of times that they were elected as Prime Minister and the title and name of the Governor General at the time they commenced office.

Part B. Create Tables using SQL DDL

The following questions are based on the Apartments ERD as shown below. See image.

Tasks:

• Write the SQL DDL to create the database that contains each of the relations shown in the above ERD. You will need to provide:
  • Your DDL code for each table that you create
  • A screenshot showing each table that is created
• Write and execute SQL definition commands for each of the following queries:
  • Add the attributes Email, Comments and Send Newsletter to the Guests table. Give those attributes an appropriate size and data type
  • Change the attribute Guests.DOB from type Varchar(15) to type DATE.
• Write and execute SQL commands for the following queries
  • Add the following customers to the Customer table See image.
• Write a command that will remove the Jones who lives in Orange from the Customer table