Question

stuck on creating view

Use a SQL statement to create a view vIBM_MMM (year, month, IBM_price,
MMM_price) that contains the year and month, average close prices of IBM and MMM for the
year/month. Your view should directly access the Historial_price table to have the latest data.

mysql> desc Historical_prices;

+-----------+-------------+------+-----+---------+----------------+

| Field | Type | Null | Key | Default | Extra |

+-----------+-------------+------+-----+---------+----------------+

| id | bigint(20) | NO | PRI | NULL | auto_increment |

| symbol | varchar(20) | YES | MUL | NULL | |

| date | date | YES | | NULL | |

| open | float | YES | | NULL | |

| high | float | YES | | NULL | |

| low | float | YES | | NULL | |

| close | float | YES | | NULL | |

| volumne | bigint(20) | YES | | NULL | |

| adj_close | float | YES | | NULL | |

+-----------+-------------+------+-----+---------+----------------+

9 rows in set (0.00 sec)

Answer 1

CREATE VIEW vIBM_MMM AS SELECT year(date) as year, month(date) as month, t1.IBM_Price, t2.MMM_price FROM Historical_prices hp, (SELECT avg(close) as IBM_Price FROM Historical_prices hp1 WHERE month(hp1.date) = month(hp.date) and year(hp1.date) = year(hp.date) and symbol = 'IBM') as t1, (SELECT avg(close) as MMM_Price FROM Historical_prices hp2 WHERE month(hp2.date) = month(hp.date) and year(hp2.date) = year(hp.date) and symbol = 'MMM');