IS301_BookExamplesCH7.sql

-- CREATE TABLES
USE IS301
GO
-- ADDED FOR THE COMMIT 
BEGIN TRANSACTION

-- Simple Drops to work on files
DROP TABLE PART;
DROP TABLE LINE;
DROP TABLE INVOICE;
DROP TABLE CUSTOMER;
DROP TABLE PRODUCT;
DROP TABLE VENDOR;


-- Create Table with Primary Key
CREATE TABLE VENDOR (
V_CODE			INT					NOT NULL UNIQUE,
V_NAME			VARCHAR(35)			NOT NULL,
V_CONTACT		VARCHAR(15)			NOT NULL,
V_AREACODE		CHAR(3)				NOT NULL,
V_PHONE			CHAR(8)				NOT NULL,
V_STATE			CHAR(2)				NOT NULL,
V_ORDER			CHAR(1)				NOT NULL,
PRIMARY KEY (V_CODE)
)

-- Create Table with Primary Key and Foreign Key
CREATE TABLE PRODUCT(
P_CODE		VARCHAR(10)				NOT NULL UNIQUE,
P_DESCRIPT	VARCHAR(35)				NOT NULL,
P_INDATE	DATE					NOT NULL,
P_QOH		SMALLINT				NOT NULL,
P_MIN		SMALLINT				NOT NULL,
P_PRICE		NUMERIC(8,2)			NOT NULL,
P_DISCOUNT	NUMERIC(5,2)			NOT NULL,
-- CHANGE Book required V_Code to accept NULLS for example below
V_CODE		INT,
PRIMARY KEY (P_CODE),
FOREIGN KEY (V_CODE) REFERENCES VENDOR)


-- Create tables with Constraints

CREATE TABLE CUSTOMER (
CUS_CODE		INT			PRIMARY KEY,
CUS_LNAME		VARCHAR(15)	NOT NULL,
CUS_FNAME		VARCHAR(15)	NOT NULL, 
CUS_INITIAL		CHAR(1),
CUS_AREACODE	CHAR(3)		DEFAULT '615' NOT NULL
							CHECK(CUS_AREACODE IN ('615', '713', '931')),
CUS_PHONE		CHAR(8)		NOT NULL,
CUS_BALANCE		NUMERIC(9,2)DEFAULT 0.00,
CONSTRAINT CUS_UI1 UNIQUE (CUS_LNAME , CUS_FNAME))

CREATE TABLE INVOICE(
INV_NUMBER		INT			PRIMARY KEY, 
CUS_CODE		INT			NOT NULL REFERENCES CUSTOMER(CUS_CODE),
INV_DATE		DATE		DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT INV_CK1 CHECK (INV_DATE > '01-JAN-2010'))

CREATE TABLE LINE (
INV_NUMBER		INT				NOT NULL,
LINE_NUMBER		NUMERIC(2,0)	NOT NULL,
P_CODE			VARCHAR(10)		NOT NULL,
LINE_UNITS		NUMERIC(9,2)	DEFAULT 0.00,
LINE_PRICE		NUMERIC(9,2)	DEFAULT 0.00,
PRIMARY KEY	(INV_NUMBER,LINE_NUMBER),
FOREIGN KEY	(INV_NUMBER) REFERENCES INVOICE, 
FOREIGN KEY	(P_CODE) REFERENCES PRODUCT(P_CODE),
CONSTRAINT LINE_UI1 UNIQUE (INV_NUMBER, P_CODE))

-- Create and Drop Index								
CREATE INDEX P_INDATEX ON PRODUCT(P_INDATE)
CREATE INDEX P_CODEX ON PRODUCT(P_CODE)
CREATE INDEX P_PRICEX ON PRODUCT(P_PRICE DESC)
DROP INDEX P_PRICEX ON PRODUCT

				
							
--INSERT DATA
INSERT INTO VENDOR
VALUES (21225,'Bryson, Inc.','Smithson','615','223-3234','TN','Y')

INSERT INTO VENDOR
VALUES (21226,'Superloo, Inc.','Flushing','904','215-8995','FL','N')


INSERT INTO PRODUCT
VALUES ('11QER/31','Power painter, 15 psi., 3-nozzle','11-03-09',8,5,109.99,0.00,21225)

INSERT INTO PRODUCT
VALUES ('13-Q2/P2','7.25-in. pwr. saw blade','12-13-09',32,15,14.99, 0.05,21225)

		
-- Inserting row with a NULL for the VENDOR
INSERT INTO PRODUCT
	VALUES('BTR-345','Titanium drill bit','18-OCT-09',75,10,4.50,0.06,NULL)	
/*	 Another way to do this it below:
INSERT INTO PRODUCT	(P_CODE , P_DESCRIPT) VALUES (	'BTR-345','Titanium drill bit')
*/

-- Simply put saves all changes.
COMMIT


-- Trick to using the intelisense is to fill out the FROM [TABLE] first
SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE,P_DISCOUNT,V_CODE
FROM PRODUCT

-- UPDATE QUERIES
-- REPLACE P_INDATE WHERE P_CODE = 13-Q2/P2
UPDATE PRODUCT
SET		P_INDATE ='18-JAN-2010'
WHERE	P_CODE = '13-Q2/P2'


-- REPLACE THREE ITEMS WHERE P_CODE = 13-Q2/P2
UPDATE PRODUCT
SET		P_INDATE ='18-JAN-2010',P_PRICE = 17.99, P_MIN = 10
WHERE	P_CODE = '13-Q2/P2'


-- DELETE Queries
/* Removing to keep data for next section
DELETE FROM PRODUCT
WHERE P_CODE = 'BTR-345'

DELETE FROM PRODUCT
WHERE P_MIN = 5

*/

/*
    SELECT QUERIES with conditional restricitions
	Adding data to help with this section
	Loading data rows					*/

/* VENDOR rows						*/
-- ADDED ABOVE INSERT INTO VENDOR VALUES(21225,'Bryson, Inc.'    ,'Smithson','615','223-3234','TN','Y');
-- ADDED ABOVE INSERT INTO VENDOR VALUES(21226,'SuperLoo, Inc.'  ,'Flushing','904','215-8995','FL','N');
INSERT INTO VENDOR VALUES(21231,'D&E Supply'     ,'Singh'   ,'615','228-3245','TN','Y');
INSERT INTO VENDOR VALUES(21344,'Gomez Bros.'     ,'Ortega'  ,'615','889-2546','KY','N');
INSERT INTO VENDOR VALUES(22567,'Dome Supply'     ,'Smith'   ,'901','678-1419','GA','N');
INSERT INTO VENDOR VALUES(23119,'Randsets Ltd.'   ,'Anderson','901','678-3998','GA','Y');
INSERT INTO VENDOR VALUES(24004,'Brackman Bros.'  ,'Browning','615','228-1410','TN','N');
INSERT INTO VENDOR VALUES(24288,'ORDVA, Inc.'     ,'Hakford' ,'615','898-1234','TN','Y');
INSERT INTO VENDOR VALUES(25443,'B&K, Inc.'      ,'Smith'   ,'904','227-0093','FL','N');
INSERT INTO VENDOR VALUES(25501,'Damal Supplies'  ,'Smythe'  ,'615','890-3529','TN','N');
INSERT INTO VENDOR VALUES(25595,'Rubicon Systems' ,'Orton'   ,'904','456-0092','FL','Y');

/*PRODUCT rows						*/
-- ADDED ABOVE INSERT INTO PRODUCT VALUES('11QER/31','Power painter, 15 psi., 3-nozzle'     ,'03-NOV-2009',  8,  5,109.99,0.00,25595);
-- ADDED ABOVE INSERT INTO PRODUCT VALUES('13-Q2/P2','7.25-in. pwr. saw blade'              ,'13-DEC-2009', 32, 15, 14.99,0.05,21344);
INSERT INTO PRODUCT VALUES('14-Q1/L3','9.00-in. pwr. saw blade'              ,'13-NOV-2009', 18, 12, 17.49,0.00,21344);
INSERT INTO PRODUCT VALUES('1546-QQ2','Hrd. cloth, 1/4-in., 2x50'            ,'15-JAN-2010', 15,  8, 39.95,0.00,23119);
INSERT INTO PRODUCT VALUES('1558-QW1','Hrd. cloth, 1/2-in., 3x50'            ,'15-JAN-2010', 23,  5, 43.99,0.00,23119);
INSERT INTO PRODUCT VALUES('2232/QTY','B&D jigsaw, 12-in. blade'            ,'30-DEC-2009',  8,  5,109.92,0.05,24288);
INSERT INTO PRODUCT VALUES('2232/QWE','B&D jigsaw, 8-in. blade'             ,'24-DEC-2009',  6,  5, 99.87,0.05,24288);
INSERT INTO PRODUCT VALUES('2238/QPD','B&D cordless drill, 1/2-in.'         ,'20-JAN-2010', 12,  5, 38.95,0.05,25595);
INSERT INTO PRODUCT VALUES('23109-HB','Claw hammer'                          ,'20-JAN-2010', 23, 10,  9.95,0.10,21225);
INSERT INTO PRODUCT VALUES('23114-AA','Sledge hammer, 12 lb.'                ,'02-JAN-2010',  8,  5, 14.40,0.05,NULL);
INSERT INTO PRODUCT VALUES('54778-2T','Rat-tail file, 1/8-in. fine'          ,'15-DEC-2009', 43, 20,  4.99,0.00,21344);
INSERT INTO PRODUCT VALUES('89-WRE-Q','Hicut chain saw, 16 in.'              ,'07-FEB-2010', 11,  5,256.99,0.05,24288);
INSERT INTO PRODUCT VALUES('PVC23DRT','PVC pipe, 3.5-in., 8-ft'              ,'20-FEB-2010',188, 75,  5.87,0.00,NULL);
INSERT INTO PRODUCT VALUES('SM-18277','1.25-in. metal screw, 25'             ,'01-MAR-2010',172, 75,  6.99,0.00,21225);
INSERT INTO PRODUCT VALUES('SW-23116','2.5-in. wd. screw, 50'                ,'24-FEB-2010',237,100,  8.45,0.00,21231);
INSERT INTO PRODUCT VALUES('WR3/TT3' ,'Steel matting, 4''x8''x1/6", .5" mesh','17-JAN-2010', 18,  5,119.95,0.10,25595);

-- Find equal to number
Select P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
From PRODUCT
WHERE V_CODE = 21344

-- Find not equal to number
SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE <> 21344

-- Find less than or equal to number
SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_PRICE <=10

--Find string that is less than String A,B,C  Select * FROM Table WHERE variable < B -- Result A
SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE
FROM PRODUCT
WHERE P_CODE < '1558-QW1'

-- Find date that is compared to variable
SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE
FROM PRODUCT
WHERE P_INDATE >= '20-JAN-2010'

--SELECT QUIERIES WITH COMPUTATIONS
--This creates a third column with  no name
SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH * P_PRICE
FROM PRODUCT  

--To resolve the No Name we assign an alias
SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH * P_PRICE as TOTVALUE
FROM PRODUCT  

/* Using dates To get the dates to match googled the following solution 
REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]
it acutally converted to dd-Jan-yyy
*/
SELECT P_CODE, P_INDATE, GETDATE() - 90 AS CUTDATE
FROM   PRODUCT
WHERE  P_INDATE <= GETDATE() -90

--Increase the date the book uses p_indate + 90 in SQL 2008 you need to use the DATEADD() FUNCTION
SELECT P_CODE, P_INDATE,DATEADD(DAY,90,P_INDATE) AS EXPDATE
FROM   PRODUCT

-- SELECT QUERIES WITH AND, OR, NOT SYNTAX

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE = 21344 OR V_CODE = 24288

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_PRICE < 50 AND P_INDATE > '15-JAN-2010'

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE (P_PRICE <50 AND P_INDATE > '15-JAN-2010')
OR V_CODE = 24288

SELECT * 
FROM PRODUCT 
WHERE NOT(V_CODE = 21344)


--Using BETWEEN in the SELECT QUERIES 

Select *
FROM PRODUCT
WHERE P_PRICE BETWEEN 50.00 AND 100.00

-- IS NULL SYNATX
SELECT P_CODE,P_DESCRIPT, V_CODE
FROM PRODUCT
WHERE V_CODE IS NULL	

SELECT P_CODE, P_DESCRIPT, P_INDATE
FROM PRODUCT
WHERE P_INDATE IS NULL	

-- LIKE SYNTAX

-- Smilar to Smith~
SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM   VENDOR
WHERE  V_CONTACT LIKE 'Smith%'

-- Not Smilar to Smith~
SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM   VENDOR
WHERE  V_CONTACT NOT LIKE 'Smith%'

--Book uses Johns_n but that is not in the vendor table replaced with Sm_th
SELECT *
FROM   VENDOR
WHERE  V_CONTACT LIKE 'Sm_th'


-- IN SYNATX

-- This is instead of using a WHERE  V_CODE = 21344 OR V_CODE = 24288
SELECT * 
FROM PRODUCT
WHERE V_CODE IN (21344, 24288)

-- Can be combined with a inner query 
SELECT V_CODE,V_NAME
FROM VENDOR
WHERE V_CODE IN (SELECT V_CODE FROM PRODUCT)

--EXISTS Special Operator

SELECT * 
FROM VENDOR
WHERE EXISTS (SELECT * FROM PRODUCT WHERE P_QOH <= P_MIN)

SELECT * 
FROM VENDOR
WHERE EXISTS (SELECT * FROM PRODUCT WHERE P_QOH < P_MIN *2)

-- ALTER SYNATX

-- CHANGING DATA CHARACTERISTICS
-- Changing V_CODE From INT to CHAR(5) In PRODUCT table. This is an example and will not work due to being a PK in Vendor and FK in Product

/*
ALTER TABLE VENDOR
ALTER COLUMN V_CODE CHAR(5)

ALTER  TABLE PRODUCT
ALTER COLUMN V_CODE CHAR(5)
*/

ALTER TABLE PRODUCT
ALTER COLUMN P_PRICE DECIMAL (9,2)

-- ADDING COLUMN
ALTER TABLE PRODUCT 
	ADD P_SALECODE CHAR(1)
-- DROPPING COLUMN
ALTER TABLE VENDOR 
	DROP COLUMN V_ORDER

-- UPDATE SYNTAX 

UPDATE PRODUCT
SET P_SALECODE = '2'
WHERE P_CODE = '1546-QQ2'

UPDATE PRODUCT
SET P_SALECODE = 1
WHERE P_CODE IN ('2232/QWE', '2232/QTY')

UPDATE PRODUCT
SET P_SALECODE = '2'
WHERE P_INDATE < '23-DEC-2009'

UPDATE PRODUCT
SET P_SALECODE = 1
WHERE P_INDATE >= '16-JAN-2010' AND P_INDATE <= '10-FEB-2010'

UPDATE PRODUCT
SET P_QOH = P_QOH + 20
WHERE P_CODE = '2232/QWE'

UPDATE PRODUCT
SET P_PRICE = P_PRICE * 1.10
WHERE P_PRICE < 50.00


--COPYING TABLE TO A NEW TABLE 

CREATE TABLE PART (
PART_CODE		CHAR(8)		NOT NULL	UNIQUE,
PART_DESCRIPT	CHAR(35),
PART_PRICE		NUMERIC(8,2),
V_CODE			INT,
PRIMARY KEY	(PART_CODE)
)

INSERT INTO PART (PART_CODE, PART_DESCRIPT, PART_PRICE, V_CODE)
SELECT  P_CODE,P_DESCRIPT,P_PRICE,V_CODE FROM PRODUCT

-- Another way of creating a table PART from PRODUCT

DROP TABLE PART -- Clean-up PART table to create the one below.

SELECT	P_CODE AS PART_CODE,
		P_DESCRIPT AS PART_DESCRIPT,
		P_PRICE AS PART_PRICE,
		V_CODE
		INTO PART 
		FROM PRODUCT

-- ADD PK with ALTER
ALTER TABLE PART 
	ADD PRIMARY KEY (PART_CODE)
-- ADD FK with ALTER	
ALTER TABLE PART
	ADD FOREIGN KEY(V_CODE) REFERENCES VENDOR
	
-- ORDER BY SYNTAX

SELECT P_CODE, P_DESCRIPT, P_INDATE,P_PRICE
FROM PRODUCT	
ORDER BY P_PRICE

SELECT P_CODE, P_DESCRIPT, P_INDATE,P_PRICE
FROM PRODUCT	
ORDER BY P_PRICE DESC

SELECT P_DESCRIPT,V_CODE,P_INDATE,P_PRICE
FROM PRODUCT
WHERE	P_INDATE < '21-JAN-2010'
AND		P_PRICE <= 50.00
ORDER BY V_CODE, P_PRICE DESC

-- LISTING UNIQUE VALUES

SELECT DISTINCT V_CODE
FROM PRODUCT

-- AGGREGATE FUNCTIONS

-- COUNT
SELECT COUNT (DISTINCT V_CODE) -- DISTINCT WILL NOT COUNT DUPLICATES
FROM PRODUCT

SELECT COUNT (DISTINCT V_CODE)
FROM PRODUCT
WHERE P_PRICE<=10.00

SELECT COUNT (*)
FROM PRODUCT
WHERE P_PRICE <=10.00

-- MAX
SELECT MAX(P_PRICE)
FROM PRODUCT

SELECT P_CODE,P_DESCRIPT,P_PRICE
FROM PRODUCT
WHERE P_PRICE = (SELECT MAX(P_PRICE)FROM PRODUCT)

--MIN
SELECT MIN(P_PRICE)
FROM PRODUCT

--SUM 
SELECT SUM(CUS_BALANCE) AS TOTBALANCE
FROM CUSTOMER 

SELECT SUM (P_QOH * P_PRICE) AS TOTVALUE
FROM PRODUCT

-- AVG 

SELECT AVG(P_PRICE) 
FROM PRODUCT

--GROUP BY 
SELECT P_SALECODE, MIN(P_PRICE)
FROM PRODUCT
GROUP BY P_SALECODE

SELECT P_SALECODE, AVG(P_PRICE)
FROM PRODUCT
GROUP BY P_SALECODE

SELECT V_CODE, COUNT(DISTINCT P_CODE)
FROM PRODUCT
GROUP BY V_CODE

--GROUP BY with HAVING

SELECT V_CODE, SUM(P_QOH * P_PRICE) AS TOTCOST
FROM PRODUCT
GROUP BY V_CODE
HAVING (SUM(P_QOH * P_PRICE) >500)
ORDER BY SUM(P_QOH * P_PRICE) DESC

-- JOINING TABLES
SELECT P_DESCRIPT, P_PRICE, VENDOR.V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE 
FROM   PRODUCT, VENDOR
WHERE  PRODUCT.V_CODE = VENDOR.V_CODE

SELECT P_DESCRIPT, P_PRICE, VENDOR.V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM   PRODUCT, VENDOR
WHERE  PRODUCT.V_CODE = VENDOR.V_CODE
ORDER  BY P_PRICE


SELECT P_DESCRIPT, P_PRICE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM   PRODUCT, VENDOR
WHERE  PRODUCT.V_CODE = VENDOR.V_CODE
  AND  P_INDATE > '15-JAN-2010'


SELECT CUS_LNAME, INVOICE.INV_NUMBER, INV_DATE, P_DESCRIPT
FROM   CUSTOMER, INVOICE, LINE, PRODUCT
WHERE  CUSTOMER.CUS_CODE = INVOICE.CUS_CODE
  AND  INVOICE.INV_NUMBER = LINE.INV_NUMBER
  AND  LINE.P_CODE = PRODUCT.P_CODE
  AND  CUSTOMER.CUS_CODE = 10014
ORDER  BY INVOICE.INV_NUMBER

--JOINING WITH  ALIAS

SELECT P_DESCRIPT,P_PRICE,V_NAME,V_AREACODE,V_PHONE
FROM PRODUCT P, VENDOR V
WHERE P.V_CODE = V.V_CODE
ORDER BY P_PRICE 

-- EXAMPLE OF RECURSIVE JOIN
/*
SELECT E.EMP_MGR, M.EMP_LNAME, E.EMP_NUM, E.EMP_LNAME 
FROM EMP E, EMP M
WHERE E.EMP_MGR = M.EMP_NUM
ORDER BY E.EMP_MGR
*/

-- OUTER JOINS
 --LEFT
 SELECT P_CODE, VENDOR.V_CODE,V_NAME
 FROM VENDOR LEFT JOIN PRODUCT
 ON VENDOR.V_CODE = PRODUCT.V_CODE
 
 --RIGHT 
 SELECT PRODUCT.P_CODE, VENDOR.V_CODE, VENDOR.V_NAME
 FROM VENDOR RIGHT JOIN PRODUCT
 ON VENDOR.V_CODE = PRODUCT.V_CODE