QUERRY (CODING) on My SQL WorkBench.

Q( a,b,c,d,e,f,g,h.)

Screenshots

Additional Requirements:

1. Specify primary keys, foreign keys, and alternative keys

2. Specify data types

3. Specify Null/NOT NULL constraints Specify unique constraints. As a database designer, you need to decide wither to use surrogate keys or not.

4. Write CREATE TABLE statements for each of the tables.

5. Add a constraint to the SALESREP table so HireDate can not be NULL.

6. Write the needed MySQL code to insert all rows in each table.

The following database will be used in this question: SALESREP Sales Rep No 654 734 345 RepName Jones Smith Chen Johnson HireDate 01/02/2005 02/03/2007 01/25/2004 11/23/2004 434 CUSTOMER CustNo 9870 8590 7840 4870 CustName Winston Gonzales Harris Miles Balance 500 350 800 Sales RepNo 345 434 1654 100 345 a) Create the two tables above and insert the data in the rows accordingly. b) Write the standard MySQL query to retrieve all information about the customer whose customer number is 8590 c) Write the standard MySQL query to retrieve the Name and CustNumber of customers with a balance greater than 50 d) Write the standard MySQL query to retrieve the Name and CustNumber of customers whose name begins with 'S'. e) Write the standard MySQL query to retrieve the average customer balance grouped by SalesRepNo f) Write the standard MySQL query to retrieve only the name of the sales representative and the name of the customer for each customer that has a balance greater than 400 g) Write the needed MySQL code to change the Sales Rep number of the employee with CustName "Winston" to 765. h) Write the needed MySQL code to delete the rows for customers with name 'Miles'. The following database will be used in this question: SALESREP Sales Rep No 654 734 345 RepName Jones Smith Chen Johnson HireDate 01/02/2005 02/03/2007 01/25/2004 11/23/2004 434 CUSTOMER CustNo 9870 8590 7840 4870 CustName Winston Gonzales Harris Miles Balance 500 350 800 Sales RepNo 345 434 1654 100 345 a) Create the two tables above and insert the data in the rows accordingly. b) Write the standard MySQL query to retrieve all information about the customer whose customer number is 8590 c) Write the standard MySQL query to retrieve the Name and CustNumber of customers with a balance greater than 50 d) Write the standard MySQL query to retrieve the Name and CustNumber of customers whose name begins with 'S'. e) Write the standard MySQL query to retrieve the average customer balance grouped by SalesRepNo f) Write the standard MySQL query to retrieve only the name of the sales representative and the name of the customer for each customer that has a balance greater than 400 g) Write the needed MySQL code to change the Sales Rep number of the employee with CustName "Winston" to 765. h) Write the needed MySQL code to delete the rows for customers with name 'Miles