Create the database that contains each of the relations

Assignment Help Database Management System
Reference no: EM13305091

Task

Part A: SQL Queries 

Use the prime_minister  database (prime_minister.sql) from the Interact Resources site. Answer the following queries from that database.

You are required to submit:

  1. The appropriate SQL statements for each query, which should be copied from your SQL code in MySQL and pasted into your submission file;
  2. Your SQL script, which is either for each question--one-step solution, or for the two sub-questions of each question-- two-step solution; and
  3. 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.

NOTE:

    1.  Your SQL code should be correct for any data, not just any sample data that you create.

    2.  If your query results are contradict with the fact, please still present your answers based on the given prime_minister database.

    3. The final query results by either one-step solution or two-step solution for each question should be the same.

Write SQL SELECT statements to retrieve the following information from the prime_minister database: Answer all questions

1. 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.

You can choose to answer  this question in two steps:

   a. Restrict your query to Governors General who were Barons at the time of their appointment

   b. Then 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 all the Leaders of the Opposition and their date of appointment who gained their position after 01/01/1980, their wife's name and their date of marriage. Order the list by ascending date of appointment.

You can choose to answer this question in two steps:
   a.  list those who were the Leaders of the Opposition  and their date of appointment who gained their  position after 01/01/1980.
   b. For each Leader of the Opposition listed as above, add their wife's name and their date of marriage. (2 marks)

 3. Who are 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.

 You can choose to answer this question in two steps:
   a. List whose who are the Leaders of the Opposition who were not members of a Labor party. Order the list by  ascending date of appointment
   b. Based on the result of  Q.3a, list whose who gained their position after 01/01/1980. Order the list by ascending date of appointment

4. List the title, name, 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.

You can choose to answer this question in two steps:
   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. Together with the result of Q.4a, list the Prime Ministers who appointed them and all Leaders of the Opposition who served during their appointment. Order by ascending date of appointment.

5. 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.

6.. 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 Bicycle Rental ERD as shown below:

 

Tasks:

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

id_r

r_lname

rent_fname

r_address

r_town

r_state

r_pcode

r_phone

r_dob 

12345

Jones

George

123 Bentinck St

Bathurst

NSW

2795

0212345678

01/05/1964

23456

Jones

Pauline

48 Summer St

Orange

NSW

2800

0223456789

11/09/1986

28765

Wilson

Paul

23 Long St

Blayney

NSW

2799

0298745621

22/12/1977

You are required to submit:

  1. The appropriate SQL command which should be copied from your source code in MySQL and pasted into your submission file; and
  2. The resultant tables, which must be screenshots to show the change due to the execution of the SQL commands.

Reference no: EM13305091

Questions Cloud

What swap arrangement will convert the firm borrowings : What swap arrangement will convert the firm's borrowings to a synthetic fixed-rate loan? What interest rate will it pay on that synthetic fixed-rate loan?
How to design for a two-lane road in mountainous terrain : A horizontal curve is to be designed for a two-lane road in mountainous terrain. The following data are known: intersection angle: 50 degrees, tangent length: 400 ft, station of PI: 2000+00, e: .08, fs:.16
Compute the rms speed of hydrogen molecules : The temperature near the top of Saturn's cloud layer is about 110K. The temperature at the top of the earth's troposphere, Calculate the rms speed of hydrogen (H2) molecules in both of these environments
Of which the shape is described by the equation r : The double collar B is pin-connected together such that one collar slides over the rotating rod and the other slides over the horizontal curved rod, of which the shape is described by the equation r= 1.5(2-cos)
Create the database that contains each of the relations : 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.
Determine how many photons per second enter the eye : The threshold of dark-adapted (scotopic) vision is 4.0 multiplied by 10-11 W/m2 at a central wavelength of 500 nm. how many photons per second enter the eye
What is the sound level at that distance : The source of a sound wave has a power of 1.60 µW. Assume it is a point source. What is the sound level at that distance
Determine the type of water surface profile for depths : A 2.45 m wide rectangular channel has a bed slope of 0.0004 and Manning's roughness coefficient n of 0.015. For a discharge of 2.83 m^3/s, determine the type of water surface profile for depths of 1.52 m, 0.61 m, 0.30 m.
Discuss the limitations of the monitoring approach : A passive dosimeter is to be designed for use in obtaining monthly average concentrations of atrazine in a stretch of river. Typical concentrations range from 10 to 50 g/L. Atrazine, a herbicide commonly found in runoff from agricultural lands.

Reviews

Write a Review

 

Database Management System Questions & Answers

  Explaining unclustered b pus tree index

Suppose you have a table which contains 27,000 data records, and you have unclustered B+ Tree Index on the table.

  Stack and queue data structure

Identify and explain the differences between a stack and a queue data structure

  How many green books it has

Webster Library wants to know how many green books it has. Out of the 500 shelves of books, 3 shelves were selected. These shelves hold 150 books each. 54 of the books sampled were green.

  Executing well-planned strategy-manage technology solution

You have a company, specializing in managing technology solutions. you have ben just hired by big firm to execute a well-planned strategy.

  How referential integrity constraint prevent data

In physical database design, referential integrity constraints can be defined. What actions does referential integrity constraint prevent from happening when data is inserted in table which contains this constraint?

  Drawing active directory hierarchy in terms of forests

Draw Active Directory hierarchy in terms of forests, trees, domains, organizational units, and sites which are most suitable for this company and their security concerns.

  Design work breakdown structure for designing database

Design a work breakdown structure for the task below: Designing the database, Working on the website content

  Creating erd containing order and customer entity

Create ERD containing Order and Customer entity types connected by 1-M relationship from Customer to Order. Select suiatble relationship name using your common knowledge of intersection.

  Create a clustered index on the groupid column

Write the CREATE INDEX statements to create a clustered index on the GroupID column and a nonclustered index on the IndividuallD column of the GroupMembership table.

  How to use spool command

How to interactively query a database by using SQL statements and SQL*PLUS commands and how to use spool command to save an interactive session within SQL*PLUS.

  Sketch dfsa for identifiers-contain only letters and digits

Sketch a DFSA for identifiers which contain only letters and digits, where identifier should have at least one letter, but it need not be first character.

  Expanded entity relationship model diagram

Each department has many employees assigned to it, but each employee works for only one department at one of the factories. An employee cannot work at more than one factory at a time.

Free Assignment Quote

Assured A++ Grade

Get guaranteed satisfaction & time on delivery in every assignment order you paid with us! We ensure premium quality solution document along with free turntin report!

All rights reserved! Copyrights ©2019-2020 ExpertsMind IT Educational Pvt Ltd