Files.transtutors.com



Write scripts for My Guitar Shop. Be sure to submit your answers in one Notepad file with either a .txt or .sql extension. Identify each solution with part and exercise number.?Part AWrite a SELECT statement that returns these columns from the Products table:The ListPrice columnThe DiscountPercent columnA column named DiscountAmount that uses the previous two columns to calculate the discount amount and uses the ROUND function to round the result to 2 decimal placesWrite a SELECT statement that returns these columns from the Orders table:The OrderDate columnA column that returns the four-digit year that’s stored in the OrderDate columnA column that returns only the day of the month that’s stored in the OrderDate column.A column that returns the result from adding thirty days to the OrderDate column.Write a SELECT statement that returns these columns from the Orders table:The CardNumber columnThe length of the CardNumber columnThe last four digits of the CardNumber columnWhen you get that working right, add the column that follows to the result set. This is more difficult because the column requires the use of functions within functions.A column that displays the last four digits of the CardNumber column in this format: XXXX-XXXX-XXXX-1234. In other words, use Xs for the first 12 digits of the card number and actual numbers for the last four digits of the number.?Part BCreate a view named CustomerAddresses that shows the shipping and billing addresses for each customer in the MyGuitarShop database.This view should return these columns from the Customers table: CustomerID, EmailAddress, LastName and FirstName.This view should return these columns from the Addresses table: BillLine1, BillLine2, BillCity, BillState, BillZip, ShipLine1, ShipLine2, ShipCity, ShipState, and ShipZip.Use the BillingAddressID and ShippingAddressID columns in the Customers table to determine which addresses are billing addresses and which are shipping addresses.HINT: You can use two JOIN clauses to join the Addresses table to Customers table twice (once for each type of address).Write a SELECT statement that returns these columns from the CustomerAddresses view that you created in exercise 1: CustomerID, LastName, FirstName, BillLine1?Part CWrite one Stored Procedure based on the MyGuitarShop database.Write one function based on the MyGuitarShop database. ................
................

In order to avoid copyright disputes, this page is only a partial summary.

Google Online Preview   Download