CH8JPHILLIPS.sql

USE master;
GO

PRINT 'Check for the existance of DATABASE and TABLES, if exists DROP'
GO

IF EXISTS (SELECT name FROM sys.databases WHERE name = N'CH_08SimpleCO_JP')
DROP DATABASE CH_08SimpleCO_JP;
GO

IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'trg_updatecustbalance' AND type = 'TR')
   DROP TRIGGER trg_updatecustbalance;
GO
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'trg_updatecustbalance2' AND type = 'TR')
   DROP TRIGGER trg_updatecustbalance2;
GO

IF EXISTS (SELECT name FROM sysobjects 
   WHERE name = 'prc_invoice_delete' AND type = 'P')
   DROP PROCEDURE prc_invoice_delete
GO

IF EXISTS (SELECT name FROM sysobjects 
   WHERE name = 'prc_cust_add' AND type = 'P')
   DROP PROCEDURE prc_cust_add
GO

IF EXISTS (SELECT name FROM sysobjects 
   WHERE name = 'prc_invoice_add' AND type = 'P')
   DROP PROCEDURE prc_invoice_add
GO
IF OBJECT_ID ('INVOICE', 'V') IS NOT NULL 
DROP TABLE INVOICE;
GO 
IF OBJECT_ID ('CUSTOMER', 'V') IS NOT NULL 
DROP TABLE CUSTOMER;
GO 
IF OBJECT_ID ('CUSTOMER_2', 'V') IS NOT NULL 
DROP TABLE CUSTOMER_2;
GO 

--Create Database
PRINT 'Create database for homework to run in'
GO
CREATE DATABASE CH_08SimpleCO_JP;
GO

USE CH_08SimpleCO_JP;
GO

-- Problem 1
Print 'Problem 1 CREATE TABLES'
CREATE TABLE CUSTOMER (
CUST_NUM      INTEGER		PRIMARY KEY, 
CUST_LNAME    VARCHAR(20), 
CUST_FNAME    VARCHAR(20), 
CUST_BALANCE  INTEGER)		;

CREATE TABLE CUSTOMER_2 (
CUST_NUM   INTEGER PRIMARY KEY, 
CUST_LNAME VARCHAR(20), 
CUST_FNAME VARCHAR(20));

CREATE TABLE INVOICE (
INV_NUM     INTEGER PRIMARY KEY, 
CUST_NUM    INTEGER FOREIGN KEY REFERENCES CUSTOMER(CUST_NUM),
INV_DATE    DATETIME, 
INV_AMOUNT  NUMERIC(5,2))
GO

-- Problem 2
PRINT 'Problem 2 INSERT data into tables'
INSERT INTO CUSTOMER VALUES(1000 ,'Smith'  ,'Jeanne' , 1050.11);
INSERT INTO CUSTOMER VALUES(1001 ,'Ortega' ,'Juan'   ,840.92);

INSERT INTO CUSTOMER_2 VALUES(2000 ,'McPherson' ,'Anne');
INSERT INTO CUSTOMER_2 VALUES(2001 ,'Ortega'    ,'Juan');
INSERT INTO CUSTOMER_2 VALUES(2002 ,'Kowalski'  ,'Jan');
INSERT INTO CUSTOMER_2 VALUES(2003 ,'Chen'      ,'George');

INSERT INTO INVOICE VALUES(8000 ,1000 ,'23-APR-2010' ,235.89);
INSERT INTO INVOICE VALUES(8001 ,1001 ,'23-MAR-2010' ,312.82);
INSERT INTO INVOICE VALUES(8002 ,1001 ,'30-MAR-2010' ,528.1);
INSERT INTO INVOICE VALUES(8003 ,1000 ,'12-APR-2010' ,194.78);
INSERT INTO INVOICE VALUES(8004 ,1000 ,'23-APR-2010' ,619.44);
GO

-- Problem 3
PRINT 'PROBLEM 3 Combined list of customers, duplicates not included'
SELECT CUST_FNAME, CUST_LNAME FROM CUSTOMER
UNION 
SELECT CUST_FNAME, CUST_LNAME FROM CUSTOMER_2;
GO

--Problem 4
PRINT 'PROBLEM 4 generate listing of combined list, include duplicates'
SELECT CUST_FNAME, CUST_LNAME FROM CUSTOMER
UNION ALL 
SELECT CUST_FNAME, CUST_LNAME FROM CUSTOMER_2;
GO


--Problem 5
PRINT 'PROBLEM 5 only the duplicate customer records'
SELECT CUST_FNAME, CUST_LNAME FROM CUSTOMER
INTERSECT
SELECT CUST_FNAME, CUST_LNAME FROM CUSTOMER_2;
GO

--Problem 6
PRINT 'PROBLEM 6 Unique rows in CUSTOMER_2 TABLE'
SELECT CUST_FNAME, CUST_LNAME FROM CUSTOMER
EXCEPT
SELECT CUST_FNAME, CUST_LNAME FROM CUSTOMER_2;
GO

-- Problem 7 
PRINT 'PROBLEM 7 Combine data point for CUSTOMER BALANCE over $1000 '
SELECT I.INV_NUM, C.CUST_NUM, C.CUST_FNAME + ' ' + C.CUST_LNAME, I.INV_DATE, I.INV_AMOUNT
FROM CUSTOMER C INNER JOIN INVOICE I ON C.CUST_NUM = I.CUST_NUM
WHERE C.CUST_BALANCE > 1000
GO

--Problem 8 
PRINT 'PROBLEM 8  Show the invoice number, invoice amount, the average invoice amount, and the difference between the average and actual'
SELECT INV_NUM, INV_AMOUNT, 
				(SELECT AVG(INV_AMOUNT) FROM INVOICE) AS AVG_INVOICE, 
					(INV_AMOUNT - (SELECT AVG(INV_AMOUNT) FROM INVOICE))  AS DIFF
FROM INVOICE
GROUP BY INV_NUM, INV_AMOUNT
GO 

--Problem 9
PRINT 'PROBLEM 9 CREATE A ORACLE SEQUENCE to produce automatic numbers from 1000 to 5000'
PRINT 'SINCE THIS WILL NOT RUN IN SQL SERVER I AM PRINTING OUT THE CODE'
PRINT 'CREATE SEQUENCE CUS_CODE_SEQ	  START WITH 1000 NONCACHE'
PRINT 'CREATE SEQUENCE INV_NUMBER_SEQ START WITH 5000 NONCACHE'
GO 

-- Problem 10 
PRINT 'PROBLEM 10 MODIFY CUSTOMER Table with new field data'
PRINT 'ALTER TABLE ADD CUST_DOB, CUST_AGE'
ALTER TABLE CUSTOMER
	ADD CUST_DOB DATE,
	 CUST_AGE INT
GO
PRINT 'UPDATE CUSTOMER 1000 AND 1001 WITH NEW DATA'
UPDATE CUSTOMER
	SET CUST_DOB = '15-MAR-1979'
	WHERE CUST_NUM = 1000	 
GO
UPDATE CUSTOMER
	SET CUST_DOB = '22-DEC-1988'
WHERE CUST_NUM = 1001
GO 
	
--Problem 11 
PRINT 'PROBLEM 11 SHOW changes from PROBLEM 11 with customer ages'
SELECT CUST_FNAME + ' ' + CUST_LNAME, DATEDIFF(YEAR,CUST_DOB,GETDATE()) AS AGE FROM CUSTOMER;
GO

--Problem 12
PRINT 'PROBLEM 12 Use the previous query to update the CUSTOMER TABLE'
PRINT 'To do this I am creating a view and then calling the veiw to update the original Customer table'
UPDATE CUSTOMER
SET CUST_AGE = DATEDIFF(YEAR,CUST_DOB,GETDATE());
GO

--Problem 13 
PRINT 'PROBLEM 13 query the average age of the customers'
SELECT AVG(CUST_AGE) FROM CUSTOMER;
GO

--Problem 14
PRINT 'PROBLEM 14 CREATE TRIGGER to update CUST_BALANCE on creation of 
new invoice'
GO
CREATE TRIGGER trg_updatecustbalance
ON INVOICE 
FOR INSERT
AS
DECLARE 
@INVCUSCODE INT,
@INVTOT INT

BEGIN
	SET @INVCUSCODE = (SELECT INVOICE.CUST_NUM  FROM INVOICE, inserted WHERE INVOICE.INV_NUM = inserted.INV_NUM)
	SET @INVTOT = (SELECT INSERTED.INV_AMOUNT FROM inserted)
	UPDATE CUSTOMER 
		SET CUST_BALANCE =  CUST_BALANCE + @INVTOT
		WHERE CUST_NUM = @INVCUSCODE;
	PRINT 'CUSTOMER ' + CAST(@INVCUSCODE AS CHAR(5)) + 'S balance was updated by ' + CAST(@INVTOT AS CHAR(5))
END;
GO
-- Test Trigger for update
PRINT 'Testing by inserting new INVOICE'
SELECT * FROM CUSTOMER WHERE CUST_NUM = 1001;
INSERT INTO INVOICE	VALUES(8005,1001, '27-APR-10', 225.40);
SELECT * FROM CUSTOMER WHERE CUST_NUM = 1001;

--Problem 15 
PRINT 'PROBLEM 15 CREATE PROCEDURE to add customer'
GO
CREATE PROCEDURE prc_cust_add 
@CUST_NUM      INTEGER,	
@CUST_LNAME    VARCHAR(20),
@CUST_FNAME    VARCHAR(20),
@CUST_BALANCE  INTEGER	
AS
BEGIN
INSERT INTO CUSTOMER (CUST_NUM, CUST_LNAME, CUST_FNAME, CUST_BALANCE)
	VALUES(@CUST_NUM, @CUST_LNAME, @CUST_FNAME, @CUST_BALANCE)
	PRINT ('CUSTOMER ' + @CUST_LNAME + 'ADDED')
END;
GO

EXEC prc_cust_add 1002,'Rauthor', 'Peter', 0.00
GO
SELECT * FROM CUSTOMER;
GO 

--PROBLEM 16
PRINT 'PROBLEM 16 CREATE PROCEDURE prc_invoice_add'
GO
CREATE PROCEDURE prc_invoice_add
@INV_NUM     INTEGER, 
@CUST_NUM    INTEGER,
@INV_DATE    DATETIME, 
@INV_AMOUNT  NUMERIC(5,2)
AS
BEGIN 
INSERT INTO INVOICE (INV_NUM, CUST_NUM, INV_DATE, INV_AMOUNT)
	VALUES (@INV_NUM, @CUST_NUM, @INV_DATE, @INV_AMOUNT)
	PRINT 'INVOICE' + CAST(@INV_NUM AS CHAR(5)) + ' has been added.'
END;
GO  
--Test prc_invoice_add
PRINT 'Test new procedure for Invoice'
SELECT * FROM INVOICE;
EXEC prc_invoice_add 8006,1001,'30-APR-10',301.72;
SELECT * FROM INVOICE;
GO
SELECT * FROM CUSTOMER;
GO

-- Problem 17
PRINT 'PROBLEM 17 CREATE TRIGGER  that updates a customer balance when invoice deleted'
GO

CREATE TRIGGER trg_updatecustbalance2
ON INVOICE 
FOR DELETE 
AS
DECLARE 
@INVCUSCODE2 INT,
@INVTOT2 INT;
BEGIN
	SET @INVCUSCODE2 = (SELECT CUST_NUM FROM deleted)
	SET @INVTOT2 = (SELECT INV_AMOUNT FROM deleted)
	PRINT @INVTOT2
	UPDATE CUSTOMER 
		SET CUST_BALANCE =  CUSTOMER.CUST_BALANCE - @INVTOT2
		WHERE CUST_NUM = @INVCUSCODE2;
	PRINT 'CUSTOMER ' + CAST(@INVCUSCODE2 AS CHAR(5)) + 'S balance was updated by -' + CAST(@INVTOT2 AS CHAR(5))
END;
GO
-- Test trigger
DELETE FROM INVOICE WHERE INV_NUM = 8001;
SELECT * FROM INVOICE;
SELECT * FROM CUSTOMER;
GO 

--Problem 18
PRINT ' DELETE INVOICE BY giving a paramater'
GO 

CREATE PROCEDURE prc_inv_delete(
@DLT_INVNUM AS INT)
AS BEGIN
	DELETE INVOICE WHERE INVOICE.INV_NUM = @DLT_INVNUM;
	END;
GO 

--TEST prc_inv_delete
EXEC PRC_INV_DELETE 8002;	
SELECT * FROM INVOICE;
SELECT * FROM CUSTOMER; 
GO