We using the query below to answer the question in the screen shot and we using MySQl/* Query run at 2023-02-03 21:30:44 * DB : */ CREATE DATABASE `matt_metals`; /* Non-Returning Query Executed */ /* ================================= */ /* Import run at 2023-02-03 21:30:59 * Import File: learningSQL/metals.sql * DB : matt_metals * Import Completed */ /* ================================= */ /* Query run at 2023-02-03 21:31:38 * DB : matt_metals */ SHOW TABLES; /* QUERY RESULTS +-----------------------+ | Tables_in_matt_metals | +-----------------------+ | CUSTOMER | +-----------------------+ | ORDER | +-----------------------+ | PRODUCT | +-----------------------+ Row Count: 3 */ /* ================================= */ /* Query run at 2023-02-03 21:31:46 * DB : matt_metals */ DESCRIBE `CUSTOMER`; /* QUERY RESULTS +----------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +----------------+--------------+------+-----+---------+-------+ | CUSTOMER_ID | tinyint(4) | NO | PRI | | | +----------------+--------------+------+-----+---------+-------+ | NAME | varchar(22) | YES | UNI | | | +----------------+--------------+------+-----+---------+-------+ | REGION | varchar(9) | YES | | | | +----------------+--------------+------+-----+---------+-------+ | STREET_ADDRESS | varchar(16) | YES | | | | +----------------+--------------+------+-----+---------+-------+ | CITY | varchar(8) | YES | | | | +----------------+--------------+------+-----+---------+-------+ | STATE | varchar(2) | YES | | | | +----------------+--------------+------+-----+---------+-------+ | ZIP | mediumint(9) | YES | | | | +----------------+--------------+------+-----+---------+-------+ Row Count: 7 */ /* ================================= */ /* Query run at 2023-02-03 21:31:58 * DB : matt_metals */ DESCRIBE `ORDER`; /* QUERY RESULTS +-------------+-------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+-------------+------+-----+---------+-------+ | ORDER_ID | int(4) | NO | PRI | | | +-------------+-------------+------+-----+---------+-------+ | ORDER_DATE | date | YES | | | | +-------------+-------------+------+-----+---------+-------+ | SHIP_DATE | date | YES | | | | +-------------+-------------+------+-----+---------+-------+ | CUSTOMER_ID | tinyint(4) | YES | | | | +-------------+-------------+------+-----+---------+-------+ | PRODUCT_ID | tinyint(4) | NO | PRI | | | +-------------+-------------+------+-----+---------+-------+ | ORDER_QTY | smallint(6) | YES | | | | +-------------+-------------+------+-----+---------+-------+ Row Count: 6 */ /* ================================= */ /* Query run at 2023-02-03 21:32:05 * DB : matt_metals */ DESCRIBE `PRODUCT`; /* QUERY RESULTS +-------------+--------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +-------------+--------------+------+-----+---------+-------+ | PRODUCT_ID | tinyint(4) | NO | PRI | | | +-------------+--------------+------+-----+---------+-------+ | DESCRIPTION | varchar(9) | YES | UNI | | | +-------------+--------------+------+-----+---------+-------+ | PRICE | decimal(4,2) | YES | | | | +-------------+--------------+------+-----+---------+-------+ Row Count: 3 */ /* ================================= */ /* Query run at 2023-02-03 21:33:04 * DB : matt_metals */ SELECT DISTINCT `ORDER_ID` FROM `CUSTOMER` INNER JOIN `ORDER` ON `CUSTOMER`.`CUSTOMER_ID`=`ORDER`.`CUSTOMER_ID` WHERE `ORDER_DATE` = '2021-08-03' AND `SHIP_DATE` IS NULL AND `NAME` LIKE "Rex Tooling Inc" ORDER BY `ORDER_ID`; /* QUERY RESULTS +----------+ | ORDER_ID | +----------+ | 88002 | +----------+ | 88010 | +----------+ | 88021 | +----------+ | 88064 | +----------+ | 88068 | +----------+ | 88119 | +----------+ | 88126 | +----------+ Row Count: 7 */ /* ================================= */ /* Query run at 2023-02-03 21:33:29 * DB : matt_metals */ SELECT * FROM `CUSTOMER` INNER JOIN `ORDER` ON `CUSTOMER`.`CUSTOMER_ID`=`ORDER`.`CUSTOMER_ID` INNER JOIN `PRODUCT`ON `PRODUCT`.`PRODUCT_ID`=`ORDER`.`PRODUCT_ID` WHERE `ORDER_DATE`  1990 AND `PRICE` > 14.50 ORDER BY `ORDER_DATE`; /* QUERY RESULTS +-------------+------------------------+-----------+------------------+----------+-------+-------+----------+------------+------------+------------+-----------+-------------+-------+ | CUSTOMER_ID | NAME | REGION | STREET_ADDRESS | CITY | STATE | ZIP | ORDER_ID | ORDER_DATE | SHIP_DATE | PRODUCT_ID | ORDER_QTY | DESCRIPTION | PRICE | +-------------+------------------------+-----------+------------------+----------+-------+-------+----------+------------+------------+------------+-----------+-------------+-------+ | 5 | Marsh Lane Metal Works | Southeast | 9143 Marsh Ln | Avondale | LA | 79782 | 215 | 2020-01-02 | 2020-01-07 | 3 | 1995 | Silver | 15.00 | +-------------+------------------------+-----------+------------------+----------+-------+-------+----------+------------+------------+------------+-----------+-------------+-------+ | 3 | Re-Barre Construction | Southwest | 9043 Windy Dr | Irving | TX | 75032 | 330 | 2020-01-03 | 2020-01-05 | 3 | 1998 | Silver | 15.00 | +-------------+------------------------+-----------+------------------+----------+-------+-------+----------+------------+------------+------------+-----------+-------------+-------+ | 3 | Re-Barre Construction | Southwest | 9043 Windy Dr | Irving | TX | 75032 | 420 | 2020-01-03 | 2020-01-08 | 3 | 1999 | Silver | 15.00 | +-------------+------------------------+-----------+------------------+----------+-------+-------+----------+------------+------------+------------+-----------+-------------+-------+ | 4 | Prairie Construction | Southwest | 264 Long Rd | Moore | OK | 62104 | 363 | 2020-01-03 | 2020-01-05 | 3 | 1998 | Silver | 15.00 | +-------------+------------------------+-----------+------------------+----------+-------+-------+----------+------------+------------+------------+-----------+-------------+-------+ | 2 | Rex Tooling Inc | Southwest | 6129 Collie Blvd | Dallas | TX | 75201 | 1043 | 2020-01-07 | 2020-01-08 | 3 | 1996 | Silver | 15.00 | +-------------+------------------------+-----------+------------------+----------+-------+-------+----------+------------+------------+------------+-----------+-------------+-------+ | 5 | Marsh Lane Metal Works | Southeast | 9143 Marsh Ln | Avondale | LA | 79782 | 1387 | 2020-01-10 | 2020-01-12 | 3 | 1999 | Silver | 15.00 | +-------------+------------------------+-----------+------------------+----------+-------+-------+----------+------------+------------+------------+-----------+-------------+-------+ | 3 | Re-Barre Construction | Southwest | 9043 Windy Dr | Irving | TX | 75032 | 2493 | 2020-01-17 | 2020-01-22 | 3 | 1994 | Silver | 15.00 | +-------------+------------------------+-----------+------------------+----------+-------+-------+----------+------------+------------+------------+-----------+-------------+-------+ | 4 | Prairie Construction | Southwest | 264 Long Rd | Moore | OK | 62104 | 2829 | 2020-01-19 | 2020-01-21 | 3 | 1997 | Silver | 15.00 | +-------------+------------------------+-----------+------------------+----------+-------+-------+----------+------------+------------+------------+-----------+-------------+-------+ | 2 | Rex Tooling Inc | Southwest | 6129 Collie Blvd | Dallas | TX | 75201 | 3769 | 2020-01-25 | 2020-01-30 | 3 | 1998 | Silver | 15.00 | +-------------+------------------------+-----------+------------------+----------+-------+-------+----------+------------+------------+------------+-----------+-------------+-------+ | 2 | Rex Tooling Inc | Southwest | 6129 Collie Blvd | Dallas | TX | 75201 | 3933 | 2020-01-26 | 2020-01-28 | 3 | 1998 | Silver | 15.00 | +-------------+------------------------+-----------+------------------+----------+-------+-------+----------+------------+------------+------------+-----------+-------------+-------+ Row Count: 10 */ /* ================================= */ /* Query run at 2023-02-03 21:33:42 * DB : matt_metals */ SELECT `PRODUCT`.`PRODUCT_ID`, `DESCRIPTION`, SUM(`ORDER_QTY`) AS "OUTSTANDING_QTY" FROM `CUSTOMER` INNER JOIN `ORDER` ON `CUSTOMER`.`CUSTOMER_ID`=`ORDER`.`CUSTOMER_ID` INNER JOIN `PRODUCT` ON `PRODUCT`.`PRODUCT_ID`=`ORDER`.`PRODUCT_ID` WHERE `STATE` = "TX" AND `SHIP_DATE` IS NULL GROUP BY `PRODUCT`.`PRODUCT_ID`, `DESCRIPTION` ORDER BY `PRODUCT`.`PRODUCT_ID`; /* QUERY RESULTS +------------+-------------+-----------------+ | PRODUCT_ID | DESCRIPTION | OUTSTANDING_QTY | +------------+-------------+-----------------+ | 1 | Copper | 4373288 | +------------+-------------+-----------------+ | 2 | Aluminum | 4316736 | +------------+-------------+-----------------+ | 3 | Silver | 4154037 | +------------+-------------+-----------------+ | 4 | Steel | 4254164 | +------------+-------------+-----------------+ | 5 | Bronze | 4346285 | +------------+-------------+-----------------+ | 6 | Duralumin | 4326144 | +------------+-------------+-----------------+ | 7 | Solder | 4329488 | +------------+-------------+-----------------+ | 8 | Stellite | 4315223 | +------------+-------------+-----------------+ | 9 | Brass | 4325734 | +------------+-------------+-----------------+ Row Count: 9 */ /* ================================= */ /* Query run at 2023-02-03 21:33:57 * DB : matt_metals */ SELECT `NAME`, ROUND(SUM(CASE WHEN `DESCRIPTION`="Copper" THEN `ORDER_QTY` ELSE 0 END)/1000000,2) AS "Copper", ROUND(SUM(CASE WHEN `DESCRIPTION`="Aluminum" THEN `ORDER_QTY` ELSE 0 END)/1000000,2) AS "Aluminum", ROUND(SUM(CASE WHEN `DESCRIPTION`="Silver" THEN `ORDER_QTY` ELSE 0 END)/1000000,2) AS "Silver", ROUND(SUM(CASE WHEN `DESCRIPTION`="Steel" THEN `ORDER_QTY` ELSE 0 END)/1000000,2) AS "Steel", ROUND(SUM(CASE WHEN `DESCRIPTION`="Bronze" THEN `ORDER_QTY` ELSE 0 END)/1000000,2) AS "Bronze", ROUND(SUM(CASE WHEN `DESCRIPTION`="Duralumin" THEN `ORDER_QTY` ELSE 0 END)/1000000,2) AS "Duralumin", ROUND(SUM(CASE WHEN `DESCRIPTION`="Solder" THEN `ORDER_QTY` ELSE 0 END)/1000000,2) AS "Solder", ROUND(SUM(CASE WHEN `DESCRIPTION`="Stellite" THEN `ORDER_QTY` ELSE 0 END)/1000000,2) AS "Stellite", ROUND(SUM(CASE WHEN `DESCRIPTION`="Brass" THEN `ORDER_QTY` ELSE 0 END)/1000000,2) AS "Brass", ROUND(SUM(`PRICE`*`ORDER_QTY`)/1000000,2) AS "Market_Value" FROM `CUSTOMER` INNER JOIN `ORDER` ON `CUSTOMER`.`CUSTOMER_ID`=`ORDER`.`CUSTOMER_ID` INNER JOIN `PRODUCT`ON `PRODUCT`.`PRODUCT_ID`=`ORDER`.`PRODUCT_ID` WHERE `SHIP_DATE` IS NULL GROUP BY `NAME` ORDER BY `NAME`; /* QUERY RESULTS +------------------------+--------+----------+--------+-------+--------+-----------+--------+----------+-------+--------------+ | NAME | Copper | Aluminum | Silver | Steel | Bronze | Duralumin | Solder | Stellite | Brass | Market_Value | +------------------------+--------+----------+--------+-------+--------+-----------+--------+----------+-------+--------------+ | LITE Industrial | 1.46 | 1.46 | 1.38 | 1.42 | 1.49 | 1.42 | 1.38 | 1.44 | 1.49 | 115.60 | +------------------------+--------+----------+--------+-------+--------+-----------+--------+----------+-------+--------------+ | Marsh Lane Metal Works | 1.51 | 1.46 | 1.43 | 1.40 | 1.44 | 1.39 | 1.44 | 1.43 | 1.47 | 116.51 | +------------------------+--------+----------+--------+-------+--------+-----------+--------+----------+-------+--------------+ | Prairie Construction | 1.35 | 1.36 | 1.36 | 1.39 | 1.37 | 1.43 | 1.38 | 1.40 | 1.37 | 112.25 | +------------------------+--------+----------+--------+-------+--------+-----------+--------+----------+-------+--------------+ | Re-Barre Construction | 1.42 | 1.44 | 1.37 | 1.40 | 1.39 | 1.44 | 1.47 | 1.44 | 1.38 | 115.10 | +------------------------+--------+----------+--------+-------+--------+-----------+--------+----------+-------+--------------+ | Rex Tooling Inc | 1.49 | 1.42 | 1.40 | 1.43 | 1.47 | 1.46 | 1.48 | 1.44 | 1.46 | 117.51 | +------------------------+--------+----------+--------+-------+--------+-----------+--------+----------+-------+--------------+ Row Count: 5 */ /* ================================= */ 

Lab \#6 - Schema Modification Purpose: Modify an existing database Instructions 1. NOTE In this lab, you may find that if you make a mistake, you need to remove the database, and reimport it. We will be using the metals database from Lab \#5. Please save a copy you perform so that you can easily "replay" them if this were to happen 2. NOTE Please continue to log only the "final" versions of each query that you perform, while not logging the experimental or test queries 3. The following are the tasks that you will need to perform. I recomment performing them in the order that they are listed, but you can change the order if you prefer 4. You will be using the ALTER TABLE query a lot, please reference the manual for exact syntax and options 5. Rename the following tables: - "ORDER" to "cartitem" - "CUSTOMER" to "customer" - "PRODUCT" to "product" 6. Rename the following columns of the "customer" table:(note, we are using MariaDB 10.3.32, which does not have the "RENAME COLUMN" syntax that was added in 10.5) - "CUSTOMER_ID" to "customer_id" - "NAME" to "customer_name" - "REGION" to "customer_region" - "STREET_ADDRESS" to "customer_address" - "CITY" to "customer_city" - "STATE" to "customer_state" - "ZIP" to "customer_zipcode" 7. Rename the following columns of the "cartitem" table: - "ORDER_ID" to "ci_cart" - "SHIP_DATE" to "ci_shipDate" - "PRODUCT_ID" to "ci_product