by taking a screen shot of the Query Editor window after executing the SQL comma

Question

by taking a screen shot of the Query Editor window after executing the SQL command to show that it completed successfull 1. Construct and execute INSERT statements to add the sample data in Tables 5-15 and 5-16 to the Customer and Address tables in the HandsOnOne database Table 5-15 Sample data for the Customer table CustomeriD CustomerName Western Supply Company 1 Nick Harper Alice Harper Abacus Consulting CustomerAddressID O Cengage Learning Table 5-16 Sample data for the Address table State ZipCode Street 2400 Broadway 20 21st Street Billings 439 Skyline Blvd DenvrCO 80002 AddressID City Missoula MT 59802 MT 59101 4 56 Park Avenue New York NY 10001 O Cengage Learning 2. Construct and execute a query to list all customers with their corresponding city and state. The list should be sorted in ascending numerical order by ZIP code followed by 3. Construct and execute a SQL query to list the Street, City, State, and ZipCode of all 4. Construct and execute a SQL query to count the number of customers in each state. The customer name alphabetically addresses that do not have a customer associated with them. list should be ordered by the number of customers in descending order, then by state code in ascending order. 5. Construct and execute a SQL query to delete all customers who live in ZIP code 10001. 6. Construct and execute a SQL query to change Alice Harper's address to 320 21st Street, Billings, MT 59101 7. Construct and execute a SQL query to list the state and ZIP codes in a single column. The list should not contain any duplicate records and should be sorted in descending alphabetical order. 8. Construct and execute a SQL query to list the full name of all customers who have

Explanation / Answer

If you have any doubts, please give me comment...

-- 1)

CREATE TABLE Address(

AddressID INT NOT NULL PRIMARY KEY,

Street VARCHAR(255),

City VARCHAR(100),

State CHAR(2),

ZipCode CHAR(5)

);

INSERT INTO Address VALUES(1, '2400 Brodway', 'Missoula', 'MT', '59802');

INSERT INTO Address VALUES(2, '320 21st Street', 'Billings', 'MT', '59101');

INSERT INTO Address VALUES(3, '439 Skyline Blvd', 'Denver', 'CO', '80002');

INSERT INTO Address VALUES(4, '56 Park Avenue', 'New York', 'Ny', '10001');

CREATE TABLE Customer(

CustomerID INT NOT NULL PRIMARY KEY,

CustomerName VARCHAR(100),

CustomerAddressID INT,

FOREIGN KEY(CustomerAddressID) REFERENCES Address(AddressID)

);

INSERT INTO Customer VALUES(1, 'Western Supply Company', 1);

INSERT INTO Customer VALUES(1, 'Nick Harper', 3);

INSERT INTO Customer VALUES(1, 'Alice Harper', 3);

INSERT INTO Customer VALUES(1, 'Abacus Consulting', 4);

-- 2)

SELECT C.CustomerID, C.CustomerName, A.City, A.State

FROM Customer C, Address A

WHERE C.CustomerAddressID = A.AddressID;

-- 3)

SELECT Street, City, State, ZipCode

FROM Address

WHERE AddressID NOT IN(

SELECT CustomerAddressID

FROM Customer

);

-- 4)

SELECT State, COUNT(*) AS no_of_customers

FROM Address A, Customer C

WHERE A.AddressID = C.CustomerAddressID

GROUP BY State

ORDER BY no_of_customers DESC, State ASC;

-- 5)

DELETE FROM Customer WHERE CustomerAddressID IN (SELECT AddressID FROM Address WHERE ZipCode = '10001');

-- 6)

UPDATE Customer SET CustomerAddressID = 2 WHERE CustomerName = 'Alice Harper';

--7)

SELECT DISTINCT CONCAT(State, ' ', ZipCode) AS State_ZipCode

FROM Address

ORDER BY State_ZipCode DESC;

-- 8)

SELECT CustomerName

FROM Customer

WHERE CustomerName LIKE '%Harner%';

Related Questions

Navigate

• Browse (All)
• Browse B
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.