Could you please help me on these MySQL questions (please don\'t resend answers

Question

Could you please help me on these MySQL questions (please don't resend answers found in Chegg, they are WRONG). Please send me results if possible. Appreciate your help

Make the following changes to the Premiere Products database.

2. Define a view named PART_ORDER. It consists of the part number, description, price, order number, order date, number ordered, and quoted price for all order lines currently on file.

a. Write and execute the CREATE VIEW command to create the PART_ORDER view.

b. Write and execute the command to retrieve the part number, description, order number, and quoted price for all orders in the PART_ORDER view for parts with quoted prices that exceed $100.

c. Write and execute the query that the DBMS actually executes.

d. Type the answer to this question in the “write submission” area of the assignment in Blackboard: Does updating the database through this view create any problems? If so, what are they? If not, why not?

3. Define a new named ORDER_TOTAL. It consists of the order number and order total for each order currently on file. (The order total is the sum of the number of units ordered times the quoted price of each order line for each order.) Sort the rows by order number. Use TOTAL_AMOUNT as the name for the order total.

a. Write and execute the CREATE VIEW command to create the ORDER_TOTAL view.

b. Write and execute the command to retrieve the order number and order total for those orders totaling more than $1,000.

c. Write and execute the query that the DBMS actually executes.

d. Type the answer to this question in the “write submission” area of the assignment in Blackboard: Does updating the database through this view create any problems? If so, what are they? If not, why not?

4. Perform the following tasks:

a. Create an index named PART_INDEX1 on the PART_NUM column in the ORDER_LINE table.

b. Create an index named PART_INDEX2 on the CLASS column in the PART table.

c. Create an index named PART_INDEX3 on the CLASS and WAREHOUSE columns in the PART table. List item classes in descending order.

d. Delete the index named PART_INDEX3

MySQL Database file:

Explanation / Answer

/*2. Define a view named PART_ORDER. It consists of the part number, description, price, order number, order date,

number ordered, and quoted price for all order lines currently on file.-*/

CREATE VIEW PART_ORDER AS

SELECT p.PART_NUM,p.description,p.price, ol.ORDER_NUM,o.ORDER_DATE,ol.NUM_ORDERED,ol.QUOTED_PRICE

FROM PART p INNER JOIN ORDER_LINE OL on p.PART_NUM=ol.PART_NUM

INNER JOIN ORDERS o ON ol.ORDER_NUM=o.ORDER_NUM;

--a. Write and execute the CREATE VIEW command to create the PART_ORDER view.

SELECT * FROM PART_ORDER;

--b. Write and execute the command to retrieve the part number, description,

--order number, and quoted price for all orders in the PART_ORDER view for parts with quoted prices that exceed $100.

SELECT * FROM PART_ORDER WHERE QUOTED_PRICE > 100;

--c. Write and execute the query that the DBMS actually executes.

SELECT p.PART_NUM,p.description,p.price, ol.ORDER_NUM,o.ORDER_DATE,ol.NUM_ORDERED,ol.QUOTED_PRICE

FROM PART p INNER JOIN ORDER_LINE OL on p.PART_NUM=ol.PART_NUM

INNER JOIN ORDERS o ON ol.ORDER_NUM=o.ORDER_NUM;

/*d. Type the answer to this question in the “write submission” area of the assignment in Blackboard:

Does updating the database through this view create any problems? If so, what are they? If not, why not?*/

Updating data through a view depend on the type of view. In this case it wont create any problem because we have primary keys query and any update will depend keys.

SO it wont cause any problem.

/*3. Define a new named ORDER_TOTAL. It consists of the order number and order total for each order currently on file.

(The order total is the sum of the number of units ordered times the quoted price of each order line for each order.)

Sort the rows by order number. Use TOTAL_AMOUNT as the name for the order total.*/

CREATE VIEW ORDER_TOTAL AS

SELECT ORDER_NUM,SUM(QUOTED_PRICE * NUM_ORDERED) AS 'Order_Total' FROM ORDER_LINE

GROUP BY ORDER_NUM;

--a. Write and execute the CREATE VIEW command to create the ORDER_TOTAL view.

SELECT * FROM ORDER_TOTAL;

--b. Write and execute the command to retrieve the order number and order total for those orders totaling more than $1,000.

SELECT * FROM ORDER_TOTAL WHERE Order_Total>1000;

--c. Write and execute the query that the DBMS actually executes.

SELECT ORDER_NUM,SUM(QUOTED_PRICE * NUM_ORDERED) AS 'Order_Total' FROM ORDER_LINE

GROUP BY ORDER_NUM;

--d. Type the answer to this question in the “write submission” area of the assignment in Blackboard:

-- Does updating the database through this view create any problems? If so, what are they? If not, why not?

In this case it wont create any problem because it contain only one table

--4. Perform the following tasks:

--a. Create an index named PART_INDEX1 on the PART_NUM column in the ORDER_LINE table.

CREATE INDEX PART_INDEX1 ON ORDER_LINE(PART_NUM);

--b. Create an index named PART_INDEX2 on the CLASS column in the PART table.

CREATE INDEX PART_INDEX2 ON PART (CLASS);

--c. Create an index named PART_INDEX3 on the CLASS and WAREHOUSE columns in the PART table. List item classes in descending order.

CREATE INDEX PART_INDEX3 ON PART (CLASS,WAREHOUSE);

--d. Delete the index named PART_INDEX3

DROP INDEX PART_INDEX3 ON PART;

Related Questions

Navigate

• Browse (All)
• Browse C
• Subjects

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