Sql Exercise 1:
Use Sql and the solmaris Condominium Group database to complete the following exercises.
List the owner number, last name and first name of every condo owner.
List the last name and first name of every owner who lives inbowton
List the last name and first name of every owner who does not live in Bowton
List the last name and first name of every owner who does not live in Bowton
List the location number and unit number for every condo whose square footage is equal to or less tha 1,200 square feet.
List the location number and unit number for every condo with three bedrooms
List the unit number for every condo with two bedrooms that is located in location number 2
List the condo ID for every condo with a condo fee that is between $550 and $650
List the unit number for every condo in location number 1 whose condo fee is less than $500
Labor is billed at the rate of $35 per hours. List the condo ID, category number, and estimated labor cost for ever services request. To obtain the estimated labor cost, multiply the estimated hours by 35. Use the column name ESTIMATED_COST for the estimated labor cost.
List the owner number and last name for all owners who live in Florida(FL), Georiga(GA), OR south Carolina(SC).
List the location number, unit number, square footage, and condo fee for all unit, short the results by condo fee within the square footage
Calculate the total condo fees Solmaris received each month
How many one-bedroom condos are located at each lacation?
There are two ways to create the query in Step 11. Write the SQL command that you used and then write the alternate command that also would abtain the correct result
What WHERE clause would you use to find all services requests with the word “pantry” anywhere in the Description field?
Sql Exercise 2:
Use SQL and the Solmaris Condominium Group database to complete the following exercises
For every condo, list the location number, unit number, condo fee, owner number, owner’s first name, and owner’s last name
For every completed or open service request for janitorial work, list the condo ID, description, and status
For every services request for janitorial work, list the condo ID, location number, unit number, estimated hours, spent hours, owner number, and owner’s last name.
List the first and last names of all owners who have a three-bedroom condo. Use the IN operator in your query.
Repeat Exercise 4, but this time use the EXISTS operator in you query
List the unit numbers of any pair of condos that have the same square footage, For example, one pair would be unit number 201 and unit number 401, because the square footage for both units is 1030 square feet. The first unit number listed should be the major sort key and the second unit number should be the minor sort key.
List the square footage, owner number, owner last name, and owner first name for each condo in location number 1.
Repeat Exercise 7, but this time include only those condos with three bedrooms
List the location number, unit number, and condo fee for condos whose owners live in Bowton or own one-bedroom condos.
List the location number, unit number, and condo fee for condos whose owners live in Bowton but do not own one-bedroom condos.
Find the service ID and condo ID for each service request whose estimated hours is greater than the number of estimated hours on every service request on which the category number is 5.
Find the service ID and condo Id for each service request whose estimated hours is greater than the number of estimated hors on every services request on which the category number is 5.
List the condo ID, square footage, owner number, service ID, number of estimated hours. And number of spent hours for each service request on which the category number is 4.
Repeat Exercise 14, but this time be sure each condo is included regardless of whether the condo currently has any service requests for category 4.
Repeat Exercise 15 using a different SQL command to obtain the same result. What is the different between the two commands?
Sql Exercise 3:
Use SQL to make the following changes to the Condominium Group database
Create a LARGE_CONDO table with the structure shown in fig.
Insert into the LARGE_CONDO table the location number, unit number, bedrooms, baths, condo fee, and owner number for those condos whose square footage is greater than 1500 square feet.
Solmaris has increased the condo fee of each large condo by $150. Update the condo fees I the LARGE_CONDO table accordingly.
After increasing the condo fee of each large conda by $150(Exercise 3), Solmaris decides to decrease the condo fee of any condo whose monthly fee is more than $750 by one percentage. Update the condo fees in the LARGE_CONDO table accordingly.
Insert a row into the LARGE_CONDO table for new condo. The location number is 1, the unit number is 605, the number of bedrooms is 3, the number of bathrooms is3, the condo fee is $775, and the owner number is FE182
Delete all the condos in the LARGE_CONDO table for which the owner number is AN175
The condo in location 1 and unit 503 is in the process of being remodeled and the number of bedrooms is unknown. Change the value of the bedroom in the LARGE_CONDO table to null.
Add to the LARGE_CONDO table a new character column named OCCUPIED that is one character in length. (This column will indicate whether the condo is currently occupied.) Set the value for the OCCUPIED column on all rows to Y.
Change the OCCUPIED column in the LARGE_CONDO table to N for unit C06
Change the CONDO_FEE column in the LARGE_CONDO table for reject nulls.
Delete the LARGE_CONDO table form the database
Use the Internet to research another data type that you can use in Oracle for numeric values that store only whole numbers, and then re-write the SQL command to create the LARGE-CONDO table using the other data type.