Overview

Assignment Three Practice Data Manipulation by using the SELECT command to manipulate data in one table. In this homework assignment, you will practice with the aggregate functions, group by, order by and having statements.

Chapters in the Book

This assignment aligns with Chapter 4 in the textbook.

Learning Outcomes

  • The learning outcomes for this assignment are hands-on experience using Oracle SQL Live software, and understanding the SELECT command and its multiple variations by using this command to manipulate data in a single table.

Due Date(s)

  • See instructor distributed material and/or request a due date from the instructor

Instructions

  1. For this homework assignment, you have to create and insert the data for the SOLMARIS database.
  2. The create and insert commands can be found in the folder “DDL for Tables to be Used in Class/Homework.” This folder is found within “Information.”
  3. Copy and paste the DDL for the CREATE statements for the SOLMARIS database in the Oracle Live Worksheet and execute
  4. Copy and paste the DDL for the INSERT statements for the SOLMARIS tables in the Oracle Live Worksheet and execute
  5. Double-check to see the data is in the tables
  6. Once you have the tables and the data in the tables for the SOLMARIS database you can begin answering the homework questions below.
  7. The homework assignment has to include screenshots of the code and the output
  8. The screenshots should be pasted in this document so that screenshots for question one should be pasted under question one.

Assignment Grading

Each question is worth 10 points. Partial credit is given if part of the code is correct, however, if the code is correct but does not answer the assignment question, no credit will be given.

Additional Resources (in addition to the lecture and chapters in the textbook)

Assignment

  1. List the average condo fee for condo units in location number 1. Your output should only include the average condo fee. Do not include any other field.
  2. List the average square feet for each location. Include location number in your output along with the average square feet.
  3. List the maximum condo fee for each type of condo (1 bedroom, 2 bedrooms, and 3 bedrooms. Include the BDRMS field in your output along with the maximum condo fee. Order your results from the most number of bedrooms to the least number of bedrooms.
  4. List the condo ID and total spent hours for all condos with a category number of 1 or 2. Order the output by condo ID in descending order.
  5. List the maximum condo fee for each type of condo (1 bedroom, 2 bedrooms, 3 bedrooms) whose square footage is more than 1,000. Include the BDRMS field in your output along with the maximum condo fee.
  6. List the average condo fee for units in each location number but only for those units whose maximum square feet is greater than 1000. Include location number in your output along with the average condo fees.
  7. List the owner number and the total condo fees for any condo that has 1 or 3 bedrooms and whose total condo fee is more than 500. Order your output by owner number in descending order
  8. List the condo ID and the total spent hours for condo 2,5, 11 and 14 but only for those condos who total spent hours is less than 5. Order the output by condo ID in ascending order.