Question

1. Based on the tables below, write SQL command to perform the following tasks for MySql:

1. Create SALESREP and CUSTOMER tables
2. Create primary and foreign keys as appropriate. The custNo should use a surrogate key as the primary key with auto-increment
3. increase the balance of the Gonzales account by $100 to a total of $450?
4. Find an average customer balance
5. Display the name of the sales representative and the name of the customer for each customer that has a balance greater than 400

SALESREP

SalesRepNo	RepName	HireDate
654	Jones	01/02/2005
734	Smith	02/03/2007
345	Chen	01/25/2004
434	Johnson	11/23/2004

CUSTOMER

CustNo	CustName	Balance	SalesRepNo
9870	Winston	500	345
8590	Gonzales	350	434
7840	Harris	800	654
4870	Miles	100	345

Answer 1

a. Create SALESREP and CUSTOMER tables

Create SalesRep table

CREATE TABLE SALESREP (
SalesRepNo int NOT NULL,
RepName varchar(20),
HireDate DATE
);

Create Customer table

CREATE TABLE CUSTOMER (
CustNo int NOT NULL,
CustName varchar(20),
Balance int,
SalesRepNo int
);

b. Create primary and foreign keys as appropriate. The custNo should use a surrogate key as the primary key with auto-increment

ALTER TABLE SALESREP
ADD PRIMARY KEY (SalesRepNo);

ALTER TABLE CUSTOMER
change CustNo CustNo int AUTO_INCREMENT PRIMARY KEY;

ALTER TABLE CUSTOMER
ADD FOREIGN KEY (SalesRepNo) REFERENCES SALESREP(SalesRepNo);

c. Increase the balance of the Gonzales account by $100 to a total of $450

UPDATE CUSTOMER
SET Balance = 400
WHERE CustName = 'Gonzales';

d. Find an average customer balance

SELECT AVG(Balance)
FROM CUSTOMER;

e. Display the name of the sales representative and the name of the customer for each customer that has a balance greater than 400

SELECT a.CustName, b.RepName FROM 
CUSTOMER a join SALESREP b
on a.SalesRepNo = b.SalesRepNo
WHERE a.Balance > 400;