1. Once you implement premiere database with constraints, create two views: a. C
ID: 3541413 • Letter: 1
Question
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.<?xml:namespace prefix = o ns = "urn:schemas-microsoft-com:office:office" />
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.
Explanation / Answer
Using data from Premiere Products database, define a view named BigDogCust. It consists of the number,
name, state, balance, and credit limit of all customers with credit limits that are greater than or equal to $10,000.
a. Using SQL, write the view definition for BigDogCust.
CREATE VIEW BigDogCust AS
SELECTCustomerNum, CustomerName, State, Zip, Balance, CreditLimit
FROM Customer
WHERE (CreditLimit>=10000);
b. Write and SQL query to retrieve the number and name of all customers
in the BigDogCust view with balances that exceed their credit limits.
SELECT CustomerNum, CustomerName
FROM BigDogCust
WHERE ([CreditLimit]<[Balance]);
c. Convert the query you wrote in Question 1b to the query that the database program will actually
execute.
SELECT CustomerNum, CustomerName, Street, City, State, Zip, Balance, CreditLimit
FROM Customer
WHERE (CreditLimit>=10000) And ([CreditLimit]<[Balance]);
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.