Using Sales2014 available here: https://mega.nz/#!5vxAkbib!6nH47RuUatrmGGM49l23b

Question

Using Sales2014

available here: https://mega.nz/#!5vxAkbib!6nH47RuUatrmGGM49l23bACkCqCs0vMX9ZjHlYhyXtQ

Each query must be commented.

1. restore the database

2. add a table for product group (road, mountain, touring, other)

3, add a column for product group membership to Products (restrict the column to the

4 groups stated above) and populate the column

4. add primary key and foreign key constraints to the product group and products tables relationship

5. show total sales for customers whose last name starts with 'F'.

6. show total sales for each employee for the years between 2004 and 2008

7. show total sales by product group where the customer middle initial is not null

8. create a transaction that rolls back if the sale causes the inventory to become negative

9. create a stored procedure that generates a monthly sales report

10. create a trigger that puts the transaction number and the sale amount in a SalesTotals Table.

11. Show the first initial of the last name for each employee and how many other employees have that initial

12. add a PaymentType table listing these payment types (Visa, Amex, Paypal, Check, Cash, Other)

13. Add a payment type column to the sales table and create a relationship to the Payment Type table.

14. Assign a payment type to each record using a random assortment of payment types. (Use any sources to create this query).

15. Show the number of orders paid by each payment type for each salesperson.

Explanation / Answer

USE AdventureWorks2012; GO SELECT * FROM Production.Product ORDER BY Name ASC; -- Alternate way. USE AdventureWorks2012; GO SELECT p.* FROM Production.Product AS p ORDER BY Name ASC; GO

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

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