BCS 360 : AssignmentOne - Programming in SQL
BCS360 - Programming in SQL - 21038
Assignment One
1) Write a SELECT statement that joins the Categories table to the Products table and
returns these columns: CategoryName, ProductName, ListPrice.
Sort the result set by CategoryName and then by ProductName in ascending order.
select CategoryName, ProductName, ListPrice
from Categories
join Products on Categories.CategoryID = Products.CategoryID
order by CategoryName, ProductName asc
2) Write a SELECT statement that joins the Customers table to the Addresses table and
returns these columns: FirstName, LastName, Line1, City, State, ZipCode.
Return one row for each address for the customer with an email address of
select FirstName, LastName, Line1, City, State, ZipCode
from Customers
join Addresses on Customers.CustomerID = Addresses.CustomerID
where EmailAddress = 'allan.sherwood@yahoo.com';
3) Write a SELECT statement that joins the Customers table to the Addresses table and
returns these columns: FirstName, LastName, Line1, City, State, ZipCode.
Code the join so only addresses that are the shipping address for a customer are
returned.
select FirstName, LastName, Line1, City, State, ZipCode
from Customers
join Addresses on Customers.CustomerID = Addresses.CustomerID
where AddressID = ShippingAddressID;
4) Write a SELECT statement that joins the Customers, Orders, OrderItems, and
Products tables. This statement should return these columns: LastName, FirstName,
OrderDate, ProductName, ItemPrice, DiscountAmount, and Quantity.
Use aliases for the tables.
Sort the final result set by LastName, OrderDate, and ProductName.
select LastName, FirstName, OrderDate, ProductName, ItemPrice, DiscountAmount,
Quantity
from Customers as C
