My SQL 8.16-8.30

Using MySQL, alter the table to populate the CUST_NUM field, beginning the increment with 2000.

ALTER TABLE CUST_MYSQL AUTO_INCREMENT = 2000;

Write a procedure named prc_inv_add to add a new invoice record to the INV_MYSQL table. Use the following values in the new record:

CREATE PROCEDURE prc_inv_add(INUM INTEGER,CNUM INTEGER,IDATE DATE,IAMOUNT DOUBLE)INSERT INTO INV_MYSQL(INV_NUM,CUST_NUM,INV_DATE,INV_AMOUNT) VALUES(INUM,CNUM,IDATE,IAMOUNT); CALL prc_inv_add(9006,2000,'2018-04-30',301.72);SELECT * FROM INV_MYSQL;

Write the set of SQL commands necessary to insert the data into the CUSTOMER table you created in Problem 16, as illustrated in Figure P8.16.

USE Ch08_SimpleCo; INSERT INTO CUSTOMER VALUES(1000, 'Smith', 'Jeanne', 1050.11); INSERT INTO CUSTOMER VALUES(1001, 'Ortega', 'Juan', 840.92);

Create the INVOICE table structure illustrated in Figure P8.16. INV_NUM should store integer values. INV_DATE should store date values. INV_AMOUNT should support up to eight digits to the left of the decimal place and two digits to the right of the decimal place. Use INV_NUM as the primary key. Note that the CUST_NUM is the foreign key to CUSTOMER, so be certain to enforce referential integrity.

USE Ch08_SimpleCo; create table INVOICE ( INV_NUM INT PRIMARY KEY, CUST_NUM INT,INV_DATE DATE, INV_AMOUNT DECIMAL(10,2), FOREIGN KEY (CUST_NUM) REFERENCES CUSTOMER (CUST_NUM) );

Modify the CUSTOMER table to include the customer's date of birth (CUST_DOB), which should store date data

alter table CUSTOMER add column CUST_DOB date;select * from CUSTOMER;

Using MySQL, alter the table to populate the INV_NUM field, beginning the increment with 9000.

ALTER TABLE INV_MYSQL AUTO_INCREMENT = 9000;

Write a procedure to delete an invoice, giving the invoice number as a parameter. Name the procedure prc_inv_delete. Test the procedure by deleting invoices 9000 and 9001

CREATE PROCEDURE prc_inv_delete(IN INUM INTEGER)DELETE FROM INV_MYSQL WHERE INV_NUM=INUM; CALL prc_inv_delete(9000);CALL prc_inv_delete(9001); SELECT * FROM INV_MYSQL;SELECT * FROM CUST_MYSQL;

Using MySQL, create a table named INV_MYSQL with the same fields as in Problem 17, except, use the AUTO_INCREMENT feature for the INV_NUM field.

CREATE TABLE INV_MYSQL ( INV_NUM INT(11) PRIMARY KEY AUTO_INCREMENT , CUST_NUM INT(11), INV_DATE DATE, INV_AMOUNT DECIMAL(10,2) );

Write a procedure named prc_cust_add to add a new customer to the CUST_MYSQL table. Use the following values in the new record:

DELIMITER // -- creating procedure name prc_cust_add; -- IN parameter use to pass a value to the subprogram; CREATE PROCEDURE prc_cust_add( IN Customer_Number INTEGER, IN Customer_LastName VARCHAR(50), IN Customer_FirstName VARCHAR(50), IN Customer_Balance DECIMAL(20, 2)) BEGIN -- inserting given details into the table INSERT INTO CUST_MYSQL(CUST_NUM, CUST_LNAME, CUST_FNAME, CUST_BALANCE) VALUES(Customer_Number, Customer_LastName, Customer_FirstName, Customer_Balance); END // DELIMITER ; -- calling procedure to inserts given details CALL prc_cust_add(2003, 'Rauthor','Peter', 0.00);

Insert the following customer into the CUST_MYSQL table, allowing the AUTO_INCREMENT attribute set up in Problem 20.a and Problem 20.b to generate the customer number automatically:

INSERT INTO CUST_MYSQL (CUST_LNAME, CUST_FNAME, CUST_BALANCE) VALUES ('Powers', 'Ruth', 500)

Write the set of SQL commands necessary to insert the data into the INVOICE table you created in Problem 17, as illustrated in Figure P8.16.

INSERT INTO INVOICE VALUES(8000, 1000, '2016-03-23', 235.89); INSERT INTO INVOICE VALUES(8001, 1001, '2016-03-23', 312.82); INSERT INTO INVOICE VALUES(8002, 1001,'2016-03-30', 528.10); INSERT INTO INVOICE VALUES(8003, 1000, '2016-04-12', 194.78); INSERT INTO INVOICE VALUES(8004, 1000, '2016-04-23', 619.44);

Create a MySQL trigger named trg_updatecustbalance to update the CUST_BALANCE in the CUST_MYSQL table when a new invoice record is entered. (Assume that the sale is a credit sale.) Whatever value appears in the INV_AMOUNT column of the new invoice should be added to the customer's balance. Test the trigger using the following new INV_MYSQL record, which would add 225.40 to the balance of customer 2001:

CREATE TRIGGER trg_updatecustbalance AFTER INSERT ON INV_MYSQL FOR EACH ROW UPDATE CUST_MYSQL SET CUST_MYSQL.CUST_BALANCE=CUST_MYSQL.CUST_BALANCE+NEW.INV_AMOUNT WHERE CUST_MYSQL.CUST_NUM=NEW.CUST_NUM; INSERT INTO INV_MYSQL (INV_NUM,CUST_NUM,INV_DATE,INV_AMOUNT) VALUES(9005,2001,'2018-04-27',225.40); SELECT * FROM INV_MYSQL; SELECT * FROM CUST_MYSQL;

Write a trigger to update the CUST_BALANCE when an invoice is deleted. Name the trigger trg_updatecustbalance2. (You should delete INV_NUM 9006 from INV_MYSQL to ensure your code is correct.)

CREATE TRIGGER trg_updatecustbalance2 AFTER DELETE ON INV_MYSQL FOR EACH ROWUPDATE CUST_MYSQL SET CUST_MYSQL.CUST_BALANCE=CUST_MYSQL.CUST_BALANCE-OLD.INV_AMOUNT WHERE CUST_MYSQL.CUST_NUM=OLD.CUST_NUM; DELETE FROM INV_MYSQL WHERE INV_NUM=9006; SELECT * FROM INV_MYSQL;SELECT * FROM CUST_MYSQL;

Using MySQL, populate the CUST_LNAME, CUST_FNAME, and CUST_BALANCE fields with the following customers:

INSERT INTO CUST_MYSQL(CUST_LNAME,CUST_FNAME,CUST_BALANCE) VALUES ("Smith","Jeanne",1050.11); INSERT INTO CUST_MYSQL(CUST_LNAME,CUST_FNAME,CUST_BALANCE) VALUES ("Ortega","Juan",840.920);

Populate the CUST_NUM, INV_DATE, and INV_AMOUNT fields with the following customers:

INSERT INTO INV_MYSQL (CUST_NUM, INV_DATE,INV_AMOUNT) VALUES (1000, '2016-03-23', 235.89); INSERT INTO INV_MYSQL (CUST_NUM, INV_DATE,INV_AMOUNT) VALUES(1001, '2016-03-23',312.82); INSERT INTO INV_MYSQL (CUST_NUM, INV_DATE,INV_AMOUNT) VALUES (1001,'2016-03-30', 528.10); INSERT INTO INV_MYSQL (CUST_NUM, INV_DATE,INV_AMOUNT) VALUES (1000, '2016-04-12', 194.78); INSERT INTO INV_MYSQL (CUST_NUM, INV_DATE,INV_AMOUNT) VALUES (1000, '2016-04-23', 619.44);

Modify customer 1001 to indicate the date of birth on December 22, 1988.

UPDATE CUSTOMER SET CUST_DOB = '1988-12-22' WHERE cust_num = 1001;

Modify customer 1000 to indicate the date of birth on March 15, 1989.

UPDATE CUSTOMER SET CUST_DOB = '1989-03-15' WHERE cust_num = 1000;

Create the CUSTOMER table structure illustrated in Figure P8.16. The customer number should store integer values. The CUST_FNAME and CUST_LNAME attributes should support variable length character data up to 30 characters each. CUST_BALANCE should support up to eight digits on the left of the decimal place and two digits to the right of the decimal place. Use CUST_NUM as the primary key.

USE Ch08_SimpleCo; drop table if EXISTS CUSTOMER; drop table if EXISTS INVOICE; create table CUSTOMER ( CUST_NUM INT PRIMARY KEY, CUST_FNAME VARCHAR(30), CUST_LNAME VARCHAR(30), CUST_BALANCE DECIMAL(10,2) # this means 2 digits after decimal place and total 8 + 2 =10 significant digits );

Using MySQL, create a table named CUST_MYSQL with the same fields as in Problem 16, except, use the AUTO_INCREMENT feature for the CUST_NUM field.

create table CUST_MYSQL(CUST_NUM INT PRIMARY KEY AUTO_INCREMENT,CUST_LNAME VARCHAR(30),CUST_FNAME VARCHAR(30),CUST_BALANCE DECIMAL(10,2));