SQL
1) Write a query that will return a list of Vendors that have: > An 800 number > The first number of their zipcode is from 1 to 5 > The second number of their zipcode is from 3 to 5 > The last number of their zip code is a 5
Include columns: VendorName, VendorPhone, and VendorZipCode
VendorName is varchar(50) , VendorPhone is varchar(50) , and VendorZipCode is varchar(20) on table Vendors The actual output below:
VendorName VendorPhone VendorZipCode Publishers Weekly (800) 555-1669 43305 Champion Printing Company (800) 555-1957 45225
===================================================
2) Write a query that returns all vendors that have a PO Box. Note, there are many forms that are listed (e.g., PO Box, P O Box, P.O. Box, etc.) so you should only look for Box. on Vendors Table > Exclude all records that do not have a phone number. > Return VendorName, VendorAddress1, VendorAddress2, and VendorPhone. > Do not show NULLs for any of the fields. You should account for NULLS being in VendorAddress1 and VendorAddress2 > Sort the list by VendorName
First 5 rows listed below:
VendorName VendorAddress1 VendorAddress2 VendorPhone American Express Box 0001 (800) 555-3344 BFI Industries PO Box 9369 (559) 555-1551 Blue Cross PO Box 9061 (800) 555-0912 Blue Shield of California PO Box 7021 (415) 555-5103 Cahners Publishing Company Citibank Lock Box 4026 8725 W Sahara Zone 1127 (301) 555-2162
Vendorld VendorZip Code 53707 NULL 20090 2 3 Default Account No 552 540 403 572 394 572 540 1 2 3 4 5 6 7 8 4 5 6 7 8 9 521 521 9 10 10 11 11 523 523 574 12 12 13 13 590 589 14 14 15 VendorName US Postal Service National Information Data Ctr Register of Copyrights Jobtrak Newbrige Book Clubs California Chamber of Commerce Towne Advertiser's Mailing Svcs BFI Industries Pacific Gas & Electric Robbins Mobile Lock And Key Bill Marvin Electric Inc City Of Fresno Golden Eagle Insurance Co Expedata Inc ASC Signs Internal Revenue Service Blanchard & Johnson Associates Fresno Photoengraving Compa.. Crown Printing Diversified Printing & Pub The Library Ltd Micro Center Yale Industrial Trucks-Fresno Zee Medical Service Co California Data Marketing Small Press Rich Advertising Vision Envelope & Printing Costco Enterprise Communications Inc 15 VendorAddress 1 Vendor Address2 Attn: Supt. Window Services PO Box 7005 PO Box 96621 Library of Congress NULL 1990 Westwood Blvd Ste 260 NULL 3000 Cindel Drive NULL 3255 Ramos Cir NULL Kevin Minder 3441 W Macarthur Blvd PO Box 9369 NULL Box 52001 NULL 4669 N Fresno NULL 4583 E Home NULL PO Box 2069 NULL PO Box 85826 NULL 4420 N. First Street, Suite 108 NULL 1528 N Sierra Vista NULL NULL NULL 27371 Valderas NULL 1952 "H" Street P.O. Box 1952 1730 "H" St NULL 2632 Saturn St NULL 7700 Forsyth NULL 1555 W Lane Ave NULL 3711 W Franklin NULL 4221 W Sierra Madre #104 NULL 2818 E Hamilton NULL 121 E Front St - 4th Floor NULL 12 Daniel Road NULL PO Box 3100 NULL Fresno Warehouse 4500 W Shaw 1483 Chain Bridge Rd, Ste NULL 20559 90025 07882 95827 92704 93792 94152 93726 93703 93718 92186 93726 93703 93888 92691 93718 93721 92621 63105 43221 93706 52353 93721 49684 VendorCity Vendor State Madison WI Washington DC Washington DC Los Angeles CA Washington NJ Sacramento CA Santa Ana CA Fresno CA San Francisco CA Fresno CA Fresno CA Fresno CA San Diego CA Fresno CA Fresno CA Fresno CA Mission Viejo CA Fresno CA Fresno CA Brea CA St Louis MO Columbus OH Fresno CA Washington Fresno CA Traverse City MI Fairfield NJ Gardena CA | Fresno CA Mclean VA VendorPhone VendorContactLName (800) 555-1205 Alberto (301) 555-8950 Irvin NULL Liana (800) 555-8725 Quinn (800) 555-9980 Marks (916) 555-6670 Mauro NULL Maegen (559) 555-1551 Kaleigh (800) 555-6081 Anthoni (559) 555-9375 Leigh (559) 555-5106 Hostlery (559) 555-9999 Mayte NULL Blanca (559) 555-9586 Quintin NULL NULL Aileen (214) 555-3647 Keeton (559) 555-3005 Chaddick (559) 555-7473 Randrup (714) 555-4541 Lane (314) 555-8834 Marques (614) 555-4435 Evan (559) 555-2993 Alexis NULL Hallie (559) 555-3801 Jonessen NULL Colette (201) 555-9742 Neil (310) 555-7062 Raven NULL Jaquan (770) 555-9558 Lawrence Darien VendorContact FName Default TermsID Francesco 1 Ania 3 Lukas 3 Kenzie 3 Michelle 4 Anton 3 Ted 3 Erick 3 Kaitlyn 3 Bill 2 Kaitlin 2 Kendall 3 Korah 3 Marvin 3 Elisabeth 1 Joan 1 Gonzalo 3 Derek 3 Leann 2 Vanesa 3 Malia 3 Emily 2 Alexandro 3 Juliana 3 Moises 4 Dusty 3 Ingrid 3 Jamari 3 Aaron 3 Eileen 2 546 16 17 16 17 18 19 18 19 20 20 21 22 23 21 22 23 24 235 540 403 400 400 540 160 532 570 540 540 540 551 570 536 24 25 26 27 07004 25 26 27 29 30 31 28 29 90247 93711 30 22101
