1 once you implement premiere database with constraints create two views a create th 5346664

1.       Once you implement premiere database with constraints, create two views:

a.       Create the TopLevelCust view. It consists of the number, name, address, balance, and credit limit of all customers with credit limits that are greater than or equal to $10,000.

b.      Create the PartOrder view. It consists of the part number, description, price, order number, order date, number ordered, and quoted price for all order lines currently on file.

c.       Do the following SQL: In the following exercises, you will use the data in the Premiere products database you created before. In MySQL, use SQL commands to obtain the desired results.

1. List the number and name of all customers.

2. List the number of name of every customer represented by sales rep 35.

3. List the number and name of all customers that are represented by sales rep 35 or that have credit limits of $10,000.

4. List the number of name of all customers represented by Juan Perez.

5. Find the total of the balances for all customers represented by sales rep 35.

6. List all columns and all rows in Part table. Sort the results by part description.

7. List all columns and all rows in the Part table. Sort the results by part number within item class. (here is about major sorting key and minor sorting key).

8. Create a new table named SportingGoods to contain the columns PartNum, Description, OhHand, Warehouse, and Priced for all rows in which the item class is SG.

9. In the SportingGoods table, change the description of part BV06 to “Fitness Gym.”

10. In the SportingGoods table, delete every row in which the price is greater than $1,000.  

Write the code for the following triggers.

a. When adding a customer, add the customer's balance times the sales rep's commission rate to the commission for the corresponding sales rep.

b. When updating a customer, add the difference between the new balance and the old balance multiplied by the sales rep's commission rate to the commission for the corresponding sales rep.

c. When deleting a customer, subtract the balance multiplied by the sales rep's commission rate from the commission for the corresponding sales rep.

You  can test the triggers by inserting, updating and deleting a customer.

For the homework, turn in the following in word document :

(a)Create table SQL commands which implement primary keys and foreign keys for premiere database.

(b) SQL commands for the two views. Also attach screen shots from mySQL after execution of each SQL create view command:

        mysql> select * from TopLevelCust;

        mysql> select * from PartOrder;

(you can use ALT+PrtScrn button to capture the screen shots).

(c) Type each question of SQL. After each question, type each command in the word. Then attach the screen shot of execution of each command in mySQL

(d) Two triggers (b) (c) in word documents.  Test the triggers and attach the screen shots.