This is 2 brief SQL Database Question(Use join) 1. We need to mail an invoice to

Question

This is 2 brief SQL Database Question(Use join)

1. We need to mail an invoice to each one of our customers. Create a query that contains the First Name, Last Name, and full mailing address information (Street, City, State, Zip Code). Make sure to use billing addresses and not shipping addresses.

These are the columns for the Customers table:

These are the columns for the Addresses table:

2. Our company needs to generate a list of customer sales. Create a query for the report that shows the first and last name of the customer, the date of the order, the name of the product they ordered, the price of the item, and the quantity. Sort the result set by the customer’s last name. Demonstrate the use of aliases for the tables.

These are the columns for the Addresses table:

These are the columns for the Products table:

These are the columns for the Orderitems table:

These are the columns for the Orders table:

Thanks

Explanation / Answer

1. We need to mail an invoice to each one of our customers. Create a query that contains the First Name, Last Name, and full mailing address information (Street, City, State, Zip Code). Make sure to use billing addresses and not shipping addresses.

select Addresses.FirstName, Addresses.LastName, Customers.Line1, Customers.Line2, Customers.City, Customers.State, Customers.ZipCode from Addresses inner join Customers on Addresses.BillingAddressID = Customers.AddressID;

Using inner join we have created a query to display the First Name, Last Name, and full mailing address information (Street, City, State, Zip Code).

2. Our company needs to generate a list of customer sales. Create a query for the report that shows the first and last name of the customer, the date of the order, the name of the product they ordered, the price of the item, and the quantity. Sort the result set by the customer’s last name. Demonstrate the use of aliases for the tables.

SELECT Addresses.FirstName, Addresses.LastName, Orders.OrderDate as DateOfOrder, Products.ProductName as NameofProduct, Products.ListPrice, Orderitems.Quantity as QuantityOfItems

FROM (((Addresses

INNER JOIN Orders ON Addresses.CustomerID = Orders.CustomerID)

INNER JOIN Orderitems ON Orders.OrderID = Orderitems.OrderID)

INNER JOIN Products ON Orderitems.ProductID = Products.ProductID);

Related Questions

Navigate

• Browse (All)
• Browse T
• Subjects

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