Overview

Assignment Two Practice Data Manipulation by using the SELECT command to manipulate data in one table.

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, 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 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 owner number, last name, and first name of every condo owner.
  2. List the complete LOCATION table (all rows and all columns).
  3. List the last name and first name of every owner who lives in Bowton.
  4. List the last name and first name of every owner who does not live in Bowton.
  5. List the location number and unit number for every condo whose square footage is equal to or less than 1,200 square feet.
  6. List the location number and unit number for every condo with three bedrooms.
  7. List the unit number for every condo with two bedrooms that is located in location number 2.
  8. List the condo ID for every condo with a condo fee that is between $550 and $650. USE THE BETWEEN.
  9. Repeat number 8 but don’t use the BETWEEN.
  10. List the unit number for every condo in location number 1 whose condo fee is less than $500
  11. Labor is billed at the rate of $35 per hour. List the condo ID, category number, estimated hours, and estimated labor cost for every service request. To obtain the estimated labor cost multiply the estimated hours by 35. Use the column names ESTIMATED_COST for the estimated labor cost.
  12. List the owner number and last name for all owners who live in Florida (FL), Georgia (GA), or South Carolina (SC). USE THE IN
  13. Repeat number 12 but don’t use the IN
  14. List the location number, unit number, square footage, and condo fee for all units. Sort the results by condo fee within the square footage.
  15. List all service requests with the word “paint” anywhere in the description field