preview

a guide to mysql ch 7 Essay

Better Essays
Open Document

CIS 331 – Database Management Using SQL

Week 6 Homework - Due: Week 7

Name ¬¬¬

1. Review Questions – page 207 (A Guide to MySQL – Chapter 7)
Questions 1 - 19 (type out the answers)

2. Premier Products – pages 207 - 209 (A Guide to MySQL – Chapter 7) Exercises 1 – 9 (create in MySQL)
Except where indicated, use MySQL Query Browser to perform each operation and print the results.

3. Henry Books – pages 209 (A Guide to MySQL – Chapter 7) Exercises 1 – 9 (create in MySQL)
Except where indicated, use MySQL Query Browser to perform each operation and print the results.

Estimated Completion Time: ___________ Comments: _______________________________

Lab Assignment Grade: …show more content…

NUM = ORDER_LINE.ORDER_NUM AND PRICE > 100;

+----------+----------------+-----------+--------------+
| PART_NUM | DESCRIPTION | ORDER_NUM | QUOTED_PRICE |
+----------+----------------+-----------+--------------+
| DR93 | Gas Range | 21610 | 495.00 |
| DW11 | Washer | 21610 | 399.99 |
| KL62 | Dryer | 21613 | 329.95 |
| KT03 | Dishwasher | 21614 | 595.00 |
| BV06 | Home Gym | 21617 | 794.95 |
| CD52 | Microwave Oven | 21617 | 150.00 |
| DR93 | Gas Range | 21619 | 495.00 |
| KV29 | Treadmill | 21623 | 1290.00 |
+----------+----------------+-----------+--------------+
8 rows in set (0.00 sec)

mysql> CREATE VIEW ORDER_TOTAL AS -> SELECT ORDERS.ORDER_NUM, SUM(NUM_ORDERED * QUOTED_PRICE) AS TOTAL_AMOUNT FROM ORDERS, ORDER_LINE WHERE ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM GROUP BY ORDERS.ORDER_NUM -> ORDER BY ORDER_NUM;
Query OK, 0 rows affected (0.00 sec)

mysql> SELECT ORDER_NUM, TOTAL_AMOUNT FROM ORDER_TOTAL WHERE TOTAL_AMOUNT > 1000;
+-----------+--------------+
| ORDER_NUM | TOTAL_AMOUNT |
+-----------+--------------+
| 21613 | 1319.80 |
| 21614 | 1190.00 |
| 21617 | 1589.90 |
| 21623 | 2580.00 |
+-----------+--------------+
4 rows in set (0.00 sec)

mysql> SELECT ORDERS.ORDER_NUM, SUM(NUM_ORDERED * QUOTED_PRICE) AS T.TOTAL_AMOUNT FROM

Get Access
Related
Get Access