a guest Feb 19th, 2019 64 Never
Not a member of Pastebin yet? Sign Up, it unlocks many cool features!
- se MySQL and create the “Solmaris Condominium Group” database.Use the appropriate CREATE TABLE command to create the LOCATION table in the Solmaris Condominium Group database. The information you need to create the table appears in Table-1. [Note: Set the primary key by following the proper constraint naming convention.]Table-1: LOCATION Table Design InformationColumnType LengthDecimal placeNulls allowed?DescriptionLOCATION_NUMDecimal20NoLocation number(primary key)LOCATION_NAMEChar25Location nameADDRESSChar25Location street addressCITYChar25Location citySTATEChar2Location statePOSTAL_CODEChar5Location postal codeQuestion 2 (15 points)Add the location information shown in Table-2 to the Location table using Insert command. Table-2: Location Table DataLOCATION_NUMLOCATION_NAMEADDRESSCITYSTATEPOSTAL_CODE1Solmaris Ocean100 Ocean Ave.BowtonFL313132Solmaris Bayside405 Bayside Blvd.Glander BayFL31044Run the script "solmaris.sql" (download from Canvas) to create the other four tables and insert data in those tables.
- Question 3 (56 points, 9 points each)Single Table Query (answer any 6 queries)a. List the last name and first name of every owner who does not live in Bowton.b. List the location number and unit number for every condo with three bedrooms.c. List the unit number for every condo with two bedrooms that is located in location number 2.d. 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 name ESTIMATED_COST for the estimated labor cost.e. List the owner number and last name for all owners who live in Florida (FL), Georgia (GA), or South Carolina (SC).f. How many one-bedroom condos are located at each location?g. Calculate the total condo fees Solmaris receives each month.Question 4 (24 points, 6 points each [a to d])Updating DataRun the create table command given below to create the LARGE_CONDO table CREATE TABLE LARGE_CONDO(LOCATION_NUM DECIMAL(2,0),UNIT_NUM CHAR(3),BDRMS DECIMAL(2,0),BATHS DECIMAL(2,0),CONDO_FEE DECIMAL(6,2),OWNER_NUM CHAR(5),PRIMARY KEY(LOCATION_NUM, UNIT_NUM) );
- a. 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 1,500 square feet.b. Solmaris has increased the condo fee of each large condo by $100. Update the condo fees in the LARGE_CONDO table accordingly.c. 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.d. Change the OCCUPIED column in the LARGE_CONDO table to N for unit C06
RAW Paste Data