Programming Question

The Premiere Products Company is a distributor of appliances, house wares, and sportinggoods. The company maintains sales representative, customer, order and inventory data. The
Premiere Products ERD model is shown below. The script to create the, schema, tables and
load the data is located in D2L. After creating the schema in MySQL answer the questions
below.
1. Find the number and name of each customer that did not place an order on October 21,
2007.
2. What is the part number, description, and price of the most expensive part in the
database?
3. List the sum of the balances of all customers for each sales rep, but restrict the output to
those sales reps for which the sum is more than $10,000.
4. Use the IN operator to list the part number and part description of each part in item class
AP or SG.
5. List the part number, part description, and on-hand value (units on hand * unit price) of
each part in item class AP. Assign the name on_hand_value to the computation.
Part. 2. For this one, You will need this schema: Premiere Products Company (it will be in
its own pdf.)
Using MySQL commands answer the questions listed below using the Premier Products
Company schema. Submit via the D2L drop box the final SQL scripts for each problem. You
must answer all parts of the question before receiving any partial credit. For example, do not
expect any points if all you answer is part b of question 1. Each part builds upon the correct
answer of the previous part.
1. Using Views (5 pts.)
1. Create a view called part_location that has the following attributes: part_num,
part_description, part_quantity, warehouse_name, warehouse_address. This data
comes from the part and warehouse entities.
2. Write a query using the view that shows the total number of parts ordered from each
warehouse. The output should look like this:
2. Using Triggers (5 pts.)
1. Execute the following SQL to create the customer_audit table in the premier
schema.
CREATE TABLE IF NOT EXISTS customer_audit (
customer_num CHAR(3) NOT NULL,
customer_name VARCHAR(35) NOT NULL,
street VARCHAR(15),
city VARCHAR(15),
state CHAR(2),
zip CHAR(5),
credit_limit DECIMAL(8,2),
date_changed DATETIME NOT NULL,
changed_by VARCHAR(45) NOT NULL);
2. Notice that the audit table does not have a primary key defined. Explain why this
might be acceptable.
3. Based on the current attributes in the customer_audit table, suggest a possible
primary key and explain why it might be unique.
4. Create a trigger that will insert a record into the customer_audit table when the
customer data is changed. Only insert a new record if the attributes that are
being audited change. The attributes being audited are those that exist in the
customer_audit table. For example the customer.balance attribute is not being
audited so if it is the only attribute that changes as part of the transaction then we
do not need to insert a new record into the audit table. Provide the SQL used to
create this trigger.
5. Write an SQL update statement that will change a customer and cause an audit
record to be created. Provide the SQL statement you executed for this.
6. Write an SQL statement that will display what is in the customer_audit table.
Provide the SQL used.
The Premiere Products Company is a distributor of appliances, house wares, and sporting
goods. The company maintains sales representative, customer, order and inventory data. The
Premiere Products ERD model is shown below. The script to create the, schema, tables and
load the data is located in D2L. After creating the schema in MySQL answer the questions
below.
1. Find the number and name of each customer that did not place an order on October 21,
2007.
2. What is the part number, description, and price of the most expensive part in the
database?
3. List the sum of the balances of all customers for each sales rep, but restrict the output to
those sales reps for which the sum is more than $10,000.
4. Use the IN operator to list the part number and part description of each part in item class
AP or SG.
5. List the part number, part description, and on-hand value (units on hand * unit price) of
each part in item class AP. Assign the name on_hand_value to the computation.
Part. 2. For this one, You will need this schema: Premiere Products Company (it will be in
its own pdf.)
Using MySQL commands answer the questions listed below using the Premier Products
Company schema. Submit via the D2L drop box the final SQL scripts for each problem. You
must answer all parts of the question before receiving any partial credit. For example, do not
expect any points if all you answer is part b of question 1. Each part builds upon the correct
answer of the previous part.
1. Using Views (5 pts.)
1. Create a view called part_location that has the following attributes: part_num,
part_description, part_quantity, warehouse_name, warehouse_address. This data
comes from the part and warehouse entities.
2. Write a query using the view that shows the total number of parts ordered from each
warehouse. The output should look like this:
2. Using Triggers (5 pts.)
1. Execute the following SQL to create the customer_audit table in the premier
schema.
CREATE TABLE IF NOT EXISTS customer_audit (
customer_num CHAR(3) NOT NULL,
customer_name VARCHAR(35) NOT NULL,
street VARCHAR(15),
city VARCHAR(15),
state CHAR(2),
zip CHAR(5),
credit_limit DECIMAL(8,2),
date_changed DATETIME NOT NULL,
changed_by VARCHAR(45) NOT NULL);
2. Notice that the audit table does not have a primary key defined. Explain why this
might be acceptable.
3. Based on the current attributes in the customer_audit table, suggest a possible
primary key and explain why it might be unique.
4. Create a trigger that will insert a record into the customer_audit table when the
customer data is changed. Only insert a new record if the attributes that are
being audited change. The attributes being audited are those that exist in the
customer_audit table. For example the customer.balance attribute is not being
audited so if it is the only attribute that changes as part of the transaction then we
do not need to insert a new record into the audit table. Provide the SQL used to
create this trigger.
5. Write an SQL update statement that will change a customer and cause an audit
record to be created. Provide the SQL statement you executed for this.
6. Write an SQL statement that will display what is in the customer_audit table.
Provide the SQL used.