a guide to mysql ch 7

Topics: SQL, Database, Foreign key Pages: 9 (1287 words) Published: November 1, 2014
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: ___________

Verified by Instructor: ___________

Staple this page to all of your homework, all homework must be turned in at the same time.
1.A picture of the database for the user or an application. 2.CREATE VIEW
3.The query used for displaying the correct view
4.The user is only able to see a piece of the database, yet all the information is still located within the tables. It is just not visible. 5.Security of data, simplicity for removing extra information and the ability to better examine data. 6.Ones where permissions have not been granted

7.DROP VIEW
8.GRANT
9.REVOKE
10.A mechanism for increasing efficiency in a database when using queries 11.CREATE INDEX CREATE UNIQUE INDEX
12.DROP INDEX
13.DBMS
14.SHOW TABLES, SHOW COLUMNS
15.SHOW INDEX
16.SHOW GRANTS
17.A rule for the data within the database to prevent incorrect additions. 18.During the CREATE TABLE command, but can be modified using the ADD PRIMARY KEY command 19.ADD FOREIGN KET

Premere Products

mysql> CREATE VIEW MAJOR_CUSTOMER (CUSTOMER_NUM, CUSTOMER_NAME, BALANCE, CREDIT_LIMIT, REP_NUM) AS SELECT CUSTOMER_NUM, CUSTOMER_NAME, BALANCE, CREDIT_LIMIT, REP_NUM FROM CUSTOMER WHERE CREDIT_LIMIT SELECT * FROM MAJOR_CUSTOMER WHERE CREDIT_LIMIT < BALANCE;

+--------------+---------------------+---------+--------------+---------+ | CUSTOMER_NUM | CUSTOMER_NAME | BALANCE | CREDIT_LIMIT | REP_NUM | +--------------+---------------------+---------+--------------+---------+ | 408 | The Everything Shop | 5285.00 | 5000.00 | 35 | | 842 | All Season | 8221.00 | 7500.00 | 20 | +--------------+---------------------+---------+--------------+---------+ 2 rows in set (0.00 sec)

mysql> SELECT CUSTOMER_NUM, CUSTOMER_NAME, BALANCE, CREDIT_LIMIT, REP_NUM FROM CUSTOMER WHERE CREDIT_LIMIT < BALANCE AND CREDIT_LIMIT CREATE VIEW PART_ORDER (PART_NUM, DESCRIPTION, PRICE, QUOTED_PRICE, ORDER_NUM, DATE, NUM_ORDERED) AS -> SELECT PART.PART_NUM, DESCRIPTION, PRICE, QUOTED_PRICE, ORDERS.ORDER_NUM, ORDER_DATE, NUM_ORDERED

-> FROM ORDERS, ORDER_LINE, PART
-> WHERE PART.PART_NUM = ORDER_LINE.PART_NUM AND ORDERS.ORDER_NUM = ORDER_LINE.ORDER_NUM; Query OK, 0 rows affected (0.00 sec)

mysql> SELECT PART_NUM, DESCRIPTION, ORDER_NUM, QUOTED_PRICE FROM PART_ORDER WHERE 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> SELECT PART.PART_NUM, DESCRIPTION, ORDERS.ORDER_NUM, QUOTED_PRICE NUM_ORDERED -> FROM ORDERS,...

References: BOOK(PUBLISHER_CODE);
Query OK, 33 rows affected (0.05 sec)
Records: 33 Duplicates: 0 Warnings: 0
Continue Reading

Please join StudyMode to read the full document

You May Also Find These Documents Helpful

  • Mysql Essay
  • Mysql Essay
  • Guide to Hardware 6e Ch 9-7 Questions Research Paper
  • Ch 7-8 Exam Study Guide Essay
  • CH 7 holes anatomy physiology study guide Essay
  • Introduction to Mysql Essay
  • Ch 7 Holt Physics Essay
  • mysql Essay

Become a StudyMode Member

Sign Up - It's Free