Create and query databases using SQL
gurkna_kaiPart A: SQL Queries (50 marks)
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:
- The appropriate SQL statements for each query, which should be copied from your SQL code in MySQL and pasted into your submission file;
- 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
- 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 (35 marks)
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. (2 marks).
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 (1 mark)
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. (1 Mark)
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. (3 marks).
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. (1 marks)
b. For each Leader of the Opposition listed as above, add their wife’s name and their date of marriage. (2 marks)
a. list those who were the Leaders of the Opposition and their date of appointment who gained their position after 01/01/1980. (1 marks)
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. (5 marks)
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 (2.5 marks)
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 (2.5 marks)
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 (2.5 marks)
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 (2.5 marks)
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. (5 marks)
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. (2.5 marks)
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. (2.5 marks)
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. (2.5 marks)
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. (2.5 marks)
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. (10 marks)
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. (10 marks)
Part B. Create Tables using SQL DDL (40 marks)
The following questions are based on the Bicycle Rental ERD as shown below:
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; (20 marks)
- A screenshot showing each table that is created. (10 marks)
- Write and execute SQL definition commands for each of the following queries:
- Add the attributes Email, Comments and Send Newsletter to the Renters table. Give those attributes an appropriate size and data type (3 marks).
- Change the attribute Renters.renterDOB from type Varchar(15) to type DATE. (2 marks)
- Write and execute SQL commands for the following:
- Add the following customers to the Renters table. (3 marks)
- Write a command that will remove the Jones that lives in Orange from the Renters table (2 marks)
| 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:
- The appropriate SQL command which should be copied from your source code in MySQL and pasted into your submission file; and
- The resultant tables, which must be screenshots to show the change due to the execution of the SQL commands.
Typing or manually drawing the results will NOT be accepted.
Rationale
This assignment will assess your knowledge and ability to use SQL to create database tables. It will also test your ability to write both simple and complex queries given a set of results that are required and to the run those queries to return the requested data.
Marking criteria
The following criteria will be used to assess Assignment 4:
| Question | HD | DI | CR | PS | FL |
|---|---|---|---|---|---|
| Part A, Q1 | Accurate and elegant SQL that answers all parts of the question | Good SQL that answers >75% but <85% of the question | SQL that answers >65% but < 75% of the question | SQL that answers >50% but < 65% of the question | Incomplete or irrelevant SQL that answers <50% of the question |
| Part A, Q2 | Accurate and elegant SQL that answers all parts of the question | Good SQL that answers >75% but <85% of the question | SQL that answers >65% but < 75% of the question | SQL that answers >50% but < 65% of the question | Incomplete or irrelevant SQL that answers <50% of the question |
| Part A, Q3 | Accurate and elegant SQL that answers all parts of the question | Good SQL that answers >75% but <85% of the question | SQL that answers >65% but < 75% of the question | SQL that answers >50% but < 65% of the question | Incomplete or irrelevant SQL that answers <50% of the question |
| Part A, Q4 | Accurate and elegant SQL that answers all parts of the question | Good SQL that answers >75% but <85% of the question | SQL that answers >65% but < 75% of the question | SQL that answers >50% but < 65% of the question | Incomplete or irrelevant SQL that answers <50% of the question |
| Part A, Q5 | Accurate and elegant SQL that answers all parts of the question | Good SQL that answers >75% but <85% of the question | SQL that answers >65% but < 75% of the question | SQL that answers >50% but < 65% of the question | Incomplete or irrelevant SQL that answers <50% of the question |
| Part A, Q6 | Accurate and elegant SQL that answers all parts of the question | Good SQL that answers >75% but <85% of the question | SQL that answers >65% but < 75% of the question | SQL that answers >50% but < 65% of the question | Incomplete or irrelevant SQL that answers <50% of the question |
| Part A, Q7 | Accurate and elegant SQL that answers all parts of the question | Good SQL that answers >75% but <85% of the question | SQL that answers >65% but < 75% of the question | SQL that answers >50% but < 65% of the question | Incomplete or irrelevant SQL that answers <50% of the question |
| Part B, Q1 | Accurate and elegant SQL that describes each table accurately with complete diagrams | Accurate SQL that describes each table with >75% but <85% accuracy with complete diagrams | SQL that describes each table with >65% but <75% accuracy with diagrams | SQL that describes each table with >50% but <65% accuracy with diagrams | SQL that describes each table with <50% accuracy OR does not supply diagrams |
| Part B, Q2 | Accurate and elegant SQL that answers all parts of the question | Good SQL that answers >75% but <85% of the question | SQL that answers >65% but < 75% of the question | SQL that answers >50% but < 65% of the question | Incomplete or irrelevant SQL that answers <50% of the question |
| Part B, Q3 | Accurate and elegant SQL that answers all parts of the question | Good SQL that answers >75% but <85% of the question | SQL that answers >65% but < 75% of the question | SQL that answers >50% but < 65% of the question | Incomplete or irrelevant SQL that answers <50% of the question |
Presentation
Assignments 2 and 4 are required to be submitted in Word format (.doc, or .docx). Each assignment must be submitted as a single document.
All screenshots that are required should be captured and inserted into the document in the appropriate position to show that you have completed the requirement. Screenshots that are submitted in addition to the assignment document will not be marked.
Databases must not be included as part of a submission.
Any extensions required must be applied for before the due date of the assignment.
12 years ago
60
Answer(0)
Bids(1)
other Questions(10)
- On a given day in the winter in ST. Louis, Missouri it began snowing in the morning
- E-BUSINESS FINAL
- Comprehensive Problem- Heintz and Parry
- 4 Question (Trigen Corp. management)
- MGT 521 WEEK 4 DQ 1
- Intro to Media
- Contracts and Procurement
- auditing and assurance 433A
- MGT 521 WEEK 1 DQ 1
- uestion >> Ethical Performance Appraisal Issues I. OBJECTIVES A. To make you aware that many performance appraisal decisions involve ethical issues. B. To familiarize you with some of the many ethical performance appraisal issues. C. To familiarize yo
