Question

What would I have to write into the MySQL Workbench query to execute these commands?

- Show all vendor information with vendor contact information in upper case like "SMITH".

- Show the customer balance in total, in average, in minimized and in maximized value.

- Show all customer information whose balance is greater than $500.

- Aggregate the total cost of products grouped by V_CODE, and only show the records with total exceeds $500, and list the records in descending order in total cost.

- Join PRODUCT table and VENDOR table on V_CODE.

- Show all records from PRODUCT that price is above the average price.

LINE CUSTOMER PK CUS CODE | Hit generates PK,FK1 INVOICE PK INV NUMBER FK1 CUS_CODE INV DATE РК INV NUMBER LINE NUMBER FK2 CUS_LNAME CUS_FNAME CUS_INITIAL CUS_AREACODE CUS PHONE CUS_BALANCE P_CODE LINE_UNITS LINE_PRICE is found in VENDOR PK V CODE PRODUCT P_CODE РК V_NAME V CONTACT HE-Supplies V_AREACODE V PHONE V STATE V ORDER P DESCRIPT P_INDATE P_QOH P MIN P PRICE P DISCOUNT V_CODE FK1 V_CO

Answer 1

- Show all vendor information with vendor contact information in upper case like "SMITH".

SELECT *
FROM VENDOR
WHERE V_CONTACT REGEXP '^[A-Z]+$';

- Show the customer balance in total, in average, in minimized and in maximized value.

SELECT SUM(CUS_BALANCE) as Total,AVG(CUS_BALANCE) as Average,MIN(CUS_BALANCE) as Minimum,MAX(CUS_BALANCE) as Maximum
FROM CUSTOMER;

- Show all customer information whose balance is greater than $500.

SELECT *
FROM CUSTOMER
WHERE CUS_BALANCE >500;

- Aggregate the total cost of products grouped by V_CODE, and only show the records with total exceeds $500, and list the records in descending order in total cost.

SELECT *
FROM PRODUCT
GROUP BY V_CODE
HAVING SUM(P_PRICE) >500
ORDER BY SUM(P_PRICE);

- Join PRODUCT table and VENDOR table on V_CODE.

Select *
FROM PRODUCT as a INNER JOIN VENDOR as b
ON a.V_CODE = b.V_CODE;

- Show all records from PRODUCT that price is above the average price.

SELECT *
FROM PRODUCT
HAVING P_PRICE > AVG(P_PRICE);