Additional Sample Data for Customer Table CustomerName CustomerAddressID 1 Weste
ID: 3817932 • Letter: A
Question
Additional Sample Data for Customer Table
CustomerName
CustomerAddressID
1
Western Supply Company
1
2
Nick Harper
3
3
Alice Harper
3
4
Abacus Consulting
4
Additional Sample Data for the Address Table
Street
City
State
Zip Code
1
2400 Broadway Drive
Missoula
MT
59802
2
320 21st Street
Billings
MT
59101
3
439 Skyline Blvd
Denver
CO
80002
4
56 Park Avenue
New York
NY
10001
1. Construct and execute a SQL query to list all customers with their corresponding cities and states. The list should be sorted in ascending numerical order by ZIP code, followed by customer name alphabetically. 2. Construct and execute a SQL Query to list the Street, City, State, and Zip Code of all addresses that do not have a customer associated with them. 3. Construct and execute a SQL query to delete all customers who live in zipcode 10001. 4. Construct and execute a SQL query to change Alice Harper’s address to 320 21st St, Billing, MT 59101.
CustomerID
CustomerName
CustomerAddressID
1
Western Supply Company
1
2
Nick Harper
3
3
Alice Harper
3
4
Abacus Consulting
4
Explanation / Answer
Solution:
1.)
SELECT City, State FROM Customer INNER JOIN Address ON Customer.CustomerAddressID= Address.AddressID ORDER BY ZIP Code ACS, CustomerName ASC
2.)
SELECT Street, City, State, Zip Code FROM Customer INNER JOIN Address ON Customer.CustomerAddressID != Address.AddressID
3.)
DELETE FROM Customer INNER JOIN Address ON Customer.CustomerAddressID = Address.AddressID WHERE AddressID= '1001'
4.)
UPDATE Customer INNER JOIN Address ON Customer.CustomerAddressID = Address.AddressID SET Street= '320 21st St' AND City= 'Billing' AND State= 'MT' AND ZIP= '59101'.
I hope this helps. Don't forget to give a thumbs up if you like this.
Related Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.