/* 
Create the tables 
Things between the stars and slashes are comments just like in regular programming
One exception is they cannot be used inside of a query, so below you will see -- used
as a marker for a one line comment, everything after the -- becomes a comment

I had to comment the constraint stuff out because it will not work in the MS SQL 
running on the lab computer

=== READ THIS!!! ===
If you are typing comments in your code, try not to use single quotes in them
If you use one by itself (that is without pairing it off before closing the comment)
in the comments it will royally screw up SQL, even though it is supposed to completely ignore comments
*/

CREATE TABLE Vendor
(
	v_code		int				UNIQUE NOT NULL,
	v_name		nvarchar( 35 )	NOT NULL,
	v_contact	nvarchar( 25 )	NOT NULL,	
	v_areacode	nchar( 3 )		NOT NULL,
	v_phone		nchar( 8 )		NOT NULL,
	v_state		nchar( 2 )		NOT NULL,
	v_order		nchar( 1 )		NOT NULL, 
	PRIMARY KEY( v_code )--,
	--CONSTRAINT chk_ord CHECK( v_order = 'Y' or v_order = 'N' )
) ;


CREATE TABLE Product
(
	p_code		nvarchar( 10 )	NOT NULL UNIQUE,
	p_descript	nvarchar( 35 )	NOT NULL,
	p_indate	datetime		DEFAULT GETDATE() NOT NULL,
	p_qoh		smallint		NOT NULL,
	p_min		smallint		NOT NULL,
	p_price		decimal( 8, 2 )	NOT NULL,
	p_discount	decimal( 5, 2 )	NOT NULL DEFAULT 0.00,
	v_code		int,
	PRIMARY KEY( p_code ),
	FOREIGN KEY( v_code ) REFERENCES Vendor ON UPDATE CASCADE--,
    --CONSTRAINT chk_discount CHECK( p_discount > 0 AND p_discount < .2 ),
	--CONSTRAINT chk_min CHECK( p_min > 0 ),
	--CONSTRAINT chk_prc CHECK( p_price > 0 )
) ;

/* 
Create some indexes 
The last one could be used if a frequent query is to find all the products 
by a particular vendor that are out of stock
*/
CREATE INDEX idx_area ON Vendor( v_areacode ) ;
CREATE INDEX idx_prodsByVendor ON Product( v_code ) ;
CREATE INDEX idx_prodsByVendorPrice ON Product( v_code, p_qoh) ;

-- Add some data
INSERT INTO Vendor VALUES ( 1, 'ASUS', 'Joe Blow', '859', '123-4567', 'KY', 'Y' ) ;
INSERT INTO Vendor VALUES ( 2, 'Dell', 'Jane Doe', '513', '321-7654', 'OH', 'Y' ) ;
INSERT INTO Product VALUES ( 'xyz', 'a sweet laptop', '2010-03-25', 1, 1, 100000.00, .2, 1 ) ;
INSERT INTO Product( p_code, p_descript, p_qoh, p_min, p_price, v_code ) 
  VALUES ( '123', 'a lame laptop', 50, 5, 500.23, 2 ) ;

-- see if it worked
SELECT * FROM Vendor ;
SELECt * FROM Product ;

/* 
that fancy insert where we fill a table 
with a select statement
 */

-- first create a table to put the values in, note the different column names but same data types!
CREATE TABLE OtherProduct
(
	id		nvarchar( 10 )	NOT NULL UNIQUE,
	des		nvarchar( 35 )	NOT NULL,
	indate		datetime		DEFAULT GETDATE() NOT NULL,
	qoh		smallint		NOT NULL,
	min		smallint		NOT NULL,
	prc		decimal( 8, 2 )	NOT NULL,
	disc		decimal( 5, 2 )	NOT NULL DEFAULT 0.00,
	vend		int,
	PRIMARY KEY( id ),
	FOREIGN KEY( vend ) REFERENCES Vendor( v_code ) ON UPDATE CASCADE
    -- make sure to specify the column(s) in the referenced table otherwise it is a bit wonky
) ;

-- now get the data in from Product
INSERT INTO OtherProduct SELECT * FROM Product ;

-- see if it worked
SELECT * FROM OtherProduct ;

/*
update!
a bad update that would change ALL descriptions, we will comment it out so it does not run
note that you can't comment out the update with a block comment, SQL code can't be inside a block comment

UPDATE Product
SET p_descript = 'foobar'
*/

UPDATE Product 
SET p_descript = 'even lamer!'
WHERE p_code = '123' ;

-- see if it worked
SELECT * FROM Product ;

/*
delete
okay the dell laptop is so lame we want to get rid of it entirely
let us do it a different way with the where clause

=== READ THIS!!! ===
 if we had used let''s there with a single quote MS SQL would have gotten messed up
but because I paired it off with a second one it is okay
*/
DELETE FROM Product
WHERE v_code = 2 ;
-- why might this be bad
-- if there is more than one product with vendor code is 2 they all get deleted!

SELECT * FROM Product ;