Get more out of your subscription*
- Access to over 100 million course-specific study resources
- 24/7 help from Expert Tutors on 140+ subjects
- Full access to over 1 million Textbook Solutions
*You can change, pause or cancel anytime
Asked by Compaqt4
THIS IS DATABASE PROGRAMMING. CATEGORY_NUM CATEGORY DESCRIPTION...
THIS IS DATABASE PROGRAMMING
Image transcription text
CATEGORY_NUM CATEGORY DESCRIPTION Plumbing 2 Heating Lai Painting Electrical Systems Carpentry Furniture replacement The SERVICE_REQUEST table shows requests that residents have put into the offices for maintenance. Each row contains a unique service ID number, the property ID, the category number associated with the type of work, the office managing the property, a description of the request, the current status of the request, the estimated hours to complete the request, the hours spent on the request, and the scheduled service date.
Image transcription text
The PROPERTY table contains information on each property, including the property ID, managing office number, address, square feet, bedrooms, floors, monthly rent, and the owner. PROPERTY_ID OFFICE_NUM ADDRESS SQR_FT BORMS FLOORS MONTHLY_RENT OWNER_NUM 30 West Thomas Rd. 1600 NULL BU106 2 782 Queen Ln. 2100 A 2 NULL AK102 3 9800 Sunbeam Ave. 1005 2 NULL BI109 105 North Illinois Rd. 1750 NULL KO104 un 887 Vine Rd. 1125 2 NULL SI105 8 Laurel Dr. 2125 2 NULL MO100 447 Goldfield St. 1675 2 NULL CO103 8 2 594 Leatherwood Dr. 2700 5 NULL KO104 For each task, copy and paste your working code in the yellow box and the screenshot of the results table in the blue box.
Image transcription text
Here is the database script to get started with >> SWM-DB2 Script You may need to repair the script's syntax to make it work in your SQL platform Task Objectives Create, use, and drop views Update the data over views instead of tables Create and test constraints such as foreign keys . . . . Use mathematical functions over numerical data Calculate dates with SQL functions 1. The property management team of SWM is considering separating the database operations of its Colombia City and Georgetown offices. Therefore, you need to create a DALLAS_COUNTY_PROPERTY VIEW to filter the properties with OFFICE_NUM equal to 1. 2. Dallas office employees started using DALLAS_COUNTY_PROPERTY and they want to update the rent of PROPERTY_ID=1 to 1200. You need to update the rent information over the VIEW.
Image transcription text
3. The property management team wants to continue property.I database operations by using the oommon PROPERTY table. Therefore, they no longer require the DALLAS_CDUNTY_PROPERTY VIEW, and they want you to remove it fro the database. 4. The property management team wants to ensure that the property and owner information always correspond. In ot words, they do not want any property in the database where the [llAlNER_NUl-l information does not match with any( in the OlrIfl'l-lER table. You need to alter the PROPERTY table by changing the UlnlNEFl_NUM field to refer to the OWNER ti l—l
Image transcription text
5. The marketing team wants to print out branded name tags for the properties with the SWM logo and resident names. They require the first and last names of the residents combined and converted to uppercase for printing. You need to send them a single-column table with the column name of LABEL. I—l |:| 6. The property management team wants to learn and track the average size of the properties in SWM. You need to return the average square feet of each property, calculated and rounded to an integer value, displayed as AVG_SQUARE_FEEL l—l l—l 7. The maintenance team wants to send SMS reminders in relation to upcoming service operations. Therefore, they request the SERVICE_ID 1 day prior to the planned service dates of the service requests as the REMINDER oolumn. However, they only require this information for those service requests where the NEXT_SERVICE_DATE is set. l—l l—l
Solved by verified expert
Answered by messagetome14
gue vel laoreet ac, dictum vitae
pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinarUnlock full access to Course Hero
Explore over 16 million step-by-step answers from our library
Subscribe to view answer
acinia pulvinar tortor nec facilisis. Pellentesque dapi
e ve
gue
ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit.
gue
pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie
gue
sus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac,
gue
ctum vitae odio
itur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dict
gue
gue
itur laoree
gue
nec facilisiDonec aliquet. Lorem ipsum dolor sit a
sus ante, dapibus a molesti
gue
gue
m imolestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio
usce dui lectus,
itur laoreet. Nam risus ante, dapi
x
ur laoreet. Nam risus
Donec aliquet. Lorem
m iipiscing elit. Nam
ce dui lectus, congue vel laoreet ac, dic
gue
gue
icitur laoreet. Nam risu
gue
a. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adi
molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac,
iscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitu
iscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque da
sum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec fac
facilisis. Pellentesque
gue
sus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lec
gue
gue
gue
s ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac,
gue
gue
itur laoree
gue
nec facilisiDonec aliquet. Lorem ipsum dolor sit a
sus ante, dapibus a molesti
gue
gue
,ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, con
a. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lor
gue
m icing elit. Nam lacinia p
or nec facilisis. Pellentesque dapibus efficitur l
gue
icitur laoreet. Nam risu
gue
ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. P
lestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, di
facilisis. Pellentesque
gue
sus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lec
gue
gue
Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet.
gue
gue
itur laoree
gue
nec facilisiDonec aliquet. Lorem ipsum dolor sit a
sus ante, dapibus a molesti
gue
gue
m ice dui lectus, congue vel laoreet ac, dictum
ac, dictum vitae odio. Donec aliquet.
gue
gue
icitur laoreet. Nam risu
gue
onec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. N
ec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing eli
gue
gue
pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a mole
gue
itur laoree
gue
nec facilisiDonec aliquet. Lorem ipsum dolor sit a
sus ante, dapibus a molesti
gue
gue
consecm risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui
lestie consequat, ul
m ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis
gue
gue
icitur laoreet. Nam risu
gue
ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem
ia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapib
a molestie consequat, ultrices ac magna. Fusce dui
gue
gue
consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapi
gue
gue
itur laoree
gue
nec facilisiDonec aliquet. Lorem ipsum dolor sit a
sus ante, dapibus a molesti
gue
gue
Donecentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. F
Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Na
gue
gue
icitur laoreet. Nam risu
gue
at, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, co
m ipsum dolor sit amet, consectetur adipi
ultrices ac magna. Fusce dui lectus, con
a. Fusce dui lectus, congue vel laoreet ac, dictum
ec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis.
facilisis. Pellentesque
gue
sus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lec
gue
pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectu
gue
gue
itur laoree
gue
nec facilisiDonec aliquet. Lorem ipsum dolor sit a
sus ante, dapibus a molesti
gue
gue
, ultret, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam
ec aliquet. Lorem ipsum dolor sit amet, consectetur adipisci
ng elit. Nam
e vel laoreet ac, dictum v
gue
icitur laoreet. Nam risu
gue
tesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum
, dictum vitae odio. Donec aliquet. Lorem ipsum dolor
inia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur la
facilisis. Pellentesque dapibus efficitur laoreet. Nam r
gue
dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvin
facilisis. Pellentesque
gue
sus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lec
gue
ia pulvinar tortor nec faciliec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie conseq
itur laoree
gue
nec facilisiDonec aliquet. Lorem ipsum dolor sit a
sus ante, dapibus a molesti
gue
gue
or necfficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. F
rem ipsum dolor sit amet, con
ac, dictum vitae odi
iscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapib
at, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, cons
gue
gue
icitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoreet ac,
Fusce dui lectus, congue vel laoreet ac, dictum vit
et, consectetur adipiscing elit. Nam lacinia pulvinar
gue
icitur laoreet. Nam risu
gue
gue vel laoreet ac, dictum vitae odio. Donec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam
ia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel
onec aliquet. Lorem ipsum dolor sit amet, consectetur adipiscing elit. Nam lacinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet
or nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lectus, congue vel laoree
ac, dictum vitae odio. Donec aliquet. Lo
gue
ec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie conse
facilisis. Pellentesque
gue
sus ante, dapibus a molestie consequat, ultrices ac magna. Fusce dui lec
gueinia pulvinar tortor nec facilisis. Pellentesque dapibus efficitur laoreet. Nam risus ante, dapibus a molestie consequat,
gue
gue
gue
