CREATE DATABASE demo ON (
 NAME=demo,
 FILENAME='D:\Courses\db2005\demo.MDF',
 SIZE=10MB,
 MAXSIZE=50MB,
 FILEGROWTH=5
);
 
CREATE TABLE VENDOR (
    V_CODE	int		PRIMARY KEY,
    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,
);

CREATE TABLE PRODUCT (
    P_CODE	varchar(10)	NOT NULL,
    P_DESCRIPT	varchar(10)	NOT NULL,
    P_INDATE	datetime	NOT NULL,
    P_ONHAND	smallint	NOT NULL,
    P_MIN	smallint	NOT NULL,
    P_PRICE	numeric(8,2)	NOT NULL,
    P_DISCOUNT	numeric(5,2)	DEFAULT 0.10	NOT NULL,
    V_CODE	int	REFERENCES VENDOR (V_CODE),
    PRIMARY KEY (P_CODE),
    FOREIGN KEY (V_CODE) REFERENCES VENDOR (V_CODE)
        ON DELETE NO ACTION
        ON UPDATE CASCADE,
);

INSERT INTO VENDOR
    VALUES (21226, 'Superloo, Inc.', 'Flushing', '904', '215-8995', 'FL', 'N');
INSERT INTO VENDOR
    VALUES (21225, 'Bryson, Inc.', 'Smithson', '615', '223-323', 'TN', 'Y');
INSERT INTO PRODUCT
    VALUES ('11QER/31', 'Power Painter, 15psi, 3-nozzle', '20011203', 8, 5, 109.99, 0.00, 25595);
    
INSERT INTO VENDOR
    VALUES (25595, 'Rubicon Sis.', 'Orton', '904', '456-0092', 'FL', 'Y');  
    
ALTER TABLE PRODUCT
    ALTER COLUMN P_DESCRIPT varchar(40) NULL;  
    
INSERT INTO PRODUCT 
    VALUES ('23109-HB', NULL, '20020120', 23, 10, 5.95, 0.10, 21225);    
    
INSERT INTO PRODUCT (P_CODE, P_INDATE, P_ONHAND, P_MIN, P_PRICE, P_DISCOUNT)
    VALUES ('BRT-345', '20020224', 25, 12, 6.99, 0.10);    
    
UPDATE PRODUCT
    SET P_DESCRIPT='Titanium drill bit'
    WHERE P_CODE='BRT-345';
UPDATE VENDOR
     SET V_PHONE='223-3234'
     WHERE V_CODE=21225;

ALTER TABLE PRODUCT
    ALTER COLUMN P_PRICE smallmoney;
    
INSERT INTO VENDOR
    VALUES (21231, 'D&E Supply', 'Singh', '615', '228-3245', 'TN', 'Y');
INSERT INTO VENDOR
    VALUES (21344, 'Gormez 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', 'Y');
INSERT INTO VENDOR
    VALUES (24228, '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', '8903529', 'TN', 'N');
    
ALTER TABLE  VENDOR
    ADD CONSTRAINT ORDER_VALUES CHECK (V_ORDER IN ('Y','N'));         
             
INSERT INTO PRODUCT
    VALUES ('13-Q2/P2', '7.25in. pwr. saw blade', '20020113', 32, 15, 14.99, 0.05, 21344);
INSERT INTO PRODUCT
    VALUES ('14-Q1/L3', '9.00-in. pwr. saw blade', '20020113', 18, 12, 17.49, 0.00, 21344);
INSERT INTO PRODUCT
    VALUES ('1546-QQ2', 'Hrd. cloth, 1/4-in., 2x50', '20011215', 15, 8, 39.95, 0.00, 23119);
INSERT INTO PRODUCT
    VALUES ('1558-QW1', 'Hrd. cloth, 1/2in., 3x50', '20020115', 23, 5, 43.99, 0.00, 23119);
INSERT INTO PRODUCT
    VALUES ('2232/QTY', 'B&D jigsaw, 12-in. blade', '20011230', 8, 5, 109.92, 0.05, 24228);
INSERT INTO PRODUCT
    VALUES ('2232/QWE', 'B&D jigsaw, 8-in. blade', '20020124', 6, 5, 99.87, 0.05, 24228);     
INSERT INTO PRODUCT
    VALUES ('2238/QPD', 'B&D cordless drill, 1/2-in.', '20011120', 12, 5, 38.95, 0.05, 25595);
INSERT INTO PRODUCT
    VALUES ('23109-HB', 'Claw hammer', '20020120', 23, 10, 5.95, 0.10, 21225);
INSERT INTO PRODUCT
    VALUES ('23114-AA', 'Sledge hammer, 12lb.', '20020202', 8, 5, 14.40, 0.05, NULL);
INSERT INTO PRODUCT
    VALUES ('54778-2T', 'Rat-tail file, 1/8in. fine', '20020115', 43, 20, 4.99, 0.00, 21344);
INSERT INTO PRODUCT
    VALUES ('89-WRE-Q', 'Hicut chain saw, 16-in.',' 20011207', 11, 5, 256.99, 0.05, 24228);
INSERT INTO PRODUCT
    VALUES ('PVC23DRT', 'PVC pipe, 3.5-in. 8-ft', '20020120', 188, 75, 5.87, 0.00, NULL);
INSERT INTO PRODUCT
    VALUES ('SM-18277', '1.25-in. metal screa, 25', '20011229', 172, 75, 6.99, 0.00, 21225);
INSERT INTO PRODUCT
    VALUES ('SW-23116', '2.5-in. wd. screw, 50', '20011224', 237, 100, 8.45, 0.00, 21231);
INSERT INTO PRODUCT
    VALUES ('WR3/TT3', 'Steel matting, 4x8x1/6, 5 mesh', '20020117', 18, 5, 119.95, 0.10, 25595);                
            
SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_CODE < '1558-QW1'
ORDER BY P_INDATE;            

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE, P_ONHAND, P_MIN, P_PRICE*P_ONHAND AS TOTAL
FROM PRODUCT
WHERE P_INDATE>= '20020120'
ORDER BY P_INDATE;

SELECT *
FROM PRODUCT
WHERE P_PRICE BETWEEN 50.00 AND 100.00;

SELECT V_CODE, V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM VENDOR
WHERE V_CONTACT LIKE 'Smith%';

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM PRODUCT
WHERE V_CODE IS NOT NULL
ORDER BY P_INDATE;  

SELECT *
FROM PRODUCT
WHERE V_CODE IN (
    SELECT V_CODE 
    FROM VENDOR
    WHERE V_CONTACT LIKE 'Smit%'
);

ALTER TABLE PRODUCT
    ADD P_SALECODE char(1);
    
UPDATE PRODUCT
    SET P_SALECODE='2'
    WHERE P_INDATE < '20011225';

UPDATE PRODUCT 
SET P_SALECODE='1'
WHERE P_INDATE>= '20020116' AND P_INDATE < '20020121';

UPDATE PRODUCT
SET P_SALECODE='3'
WHERE P_INDATE>='20011225' AND P_INDATE<'20020116';

UPDATE PRODUCT
SET P_SALECODE='4'
WHERE P_INDATE>= '20020121';
    
CREATE TABLE PART (
    PART_CODE	char(8)	NOT NULL	UNIQUE,
    PART_DESCRIPT	char(35),
    PART_PRICE	numeric(8,2),
    PRIMARY KEY (PART_CODE),
);

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

SELECT COUNT(DISTINCT V_CODE) FROM PRODUCT;
SELECT COUNT(DISTINCT V_CODE) AS V_NO FROM PRODUCT; 
SELECT COUNT(DISTINCT V_CODE)  FROM PRODUCT WHERE P_PRICE <=10;       
SELECT COUNT(*) FROM PRODUCT WHERE P_PRICE <=10;
SELECT MAX(P_PRICE)FROM PRODUCT;
SELECT MIN(P_PRICE)FROM PRODUCT;

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

SELECT P_CODE, P_DESCRIPT, P_PRICE
FROM PRODUCT
WHERE P_PRICE IN (SELECT MAX(P_PRICE) FROM PRODUCT);

SELECT AVG(P_PRICE) AS AVG_PRICE FROM PRODUCT;

SELECT P_CODE, P_DESCRIPT, P_PRICE, V_CODE
FROM PRODUCT
WHERE P_PRICE > (SELECT AVG(P_PRICE) FROM PRODUCT)
ORDER BY P_PRICE;



SELECT P_SALECODE, MIN(P_PRICE) AS MIN_PRICE
FROM PRODUCT
GROUP BY P_SALECODE;

SELECT P_SALECODE, AVG(P_PRICE) AS AVG_PRICE,  MIN(P_PRICE) AS MIN_PRICE, MAX(P_PRICE) AS MAX_PRICE
FROM PRODUCT
GROUP BY P_SALECODE;

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

SELECT V_CODE, COUNT(P_CODE) AS P_NO, AVG(P_PRICE) AS AVG_PRICE
FROM PRODUCT
GROUP BY V_CODE
HAVING AVG(P_PRICE)<10;

SELECT V_CODE, COUNT(P_CODE) AS P_NO, AVG(P_PRICE) AS AVG_PRICE
FROM PRODUCT
GROUP BY V_CODE
HAVING AVG(P_PRICE)<10
ORDER BY AVG_PRICE;

SELECT V_CODE, COUNT(P_CODE) AS P_NO, AVG(P_PRICE) AS AVG_PRICE
FROM PRODUCT
GROUP BY V_CODE
HAVING AVG(P_PRICE)<10
ORDER BY AVG_PRICE DESC;

CREATE VIEW PROD AS
    SELECT P_DESCRIPT, P_ONHAND, P_PRICE
    FROM PRODUCT
    WHERE P_PRICE > 50.00
    
CREATE VIEW SUMPRDXVEN AS
    SELECT V_CODE, SUM(P_ONHAND) AS TOTQTY, MAX(P_ONHAND) AS MAXQTY
    FROM PRODUCT
    GROUP BY V_CODE
    
CREATE INDEX VENPRODX
ON PRODUCT(V_CODE);

SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME,
    VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.V_PHONE
    FROM PRODUCT, VENDOR
    WHERE PRODUCT.V_CODE=VENDOR.V_CODE;
    
SELECT PRODUCT.P_DESCRIPT, PRODUCT.P_PRICE, VENDOR.V_NAME,
    VENDOR.V_CONTACT, VENDOR.V_AREACODE, VENDOR.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 A, VENDOR B
    WHERE A.V_CODE=B.V_CODE
    ORDER BY P_PRICE;
    
SELECT P_CODE, VENDOR.V_CODE, V_NAME
FROM VENDOR LEFT JOIN PRODUCT ON VENDOR.V_CODE=PRODUCT.V_CODE;    

CREATE TABLE PMSALES (
    P_ID		varchar(10)		PRIMARY KEY,
    PMS_QTY	int		NOT NULL,
);

INSERT INTO PMSALES
    VALUES ('14-Q1/L3', 2);
INSERT INTO PMSALES
    VALUES ('SW-23116', 5);
INSERT INTO PMSALES
    VALUES ('PVC23DRT', 23);

DELETE FROM PMSALES
WHERE P_ID='SW-23116'; 

DELETE PMSALES; 
    
UPDATE PRODUCT, PMSALES
    SET PRODUCT.P_ONHAND=P_ONHAND-PMS_QTY
    WHERE PRODUCT.P_CODE=PMSALES.P_ID;
    
CREATE VIEW PMSVUPD AS (
    SELECT PRODUCT.P_CODE, PRODUCT.P_ONHAND, PMSALES.PMS_QTY
    FROM PRODUCT, PMSALES
    WHERE PRODUCT.P_CODE=PMSALES.P_ID)
    
UPDATE PMSVUPD
    SET P_ONHAND=P_ONHAND-PMS_QTY;

    
CREATE TRIGGER trigger_name 
ON { table | view } 
[ WITH ENCRYPTION ] 
{
    { ( FOR | AFTER | INSTEAD OF ) { [ DELETE ] [ , ] [ INSERT ] [ , ] [ UPDATE ] }
        [ WITH APPEND ]
        [ NO FOR REPLICATION ]
        AS
        { IF UPDATE ( column )
            [ { AND | OR } UPDATE ( column ) ]
                [ ...n ]
        | IF ( COLUMNS_UPDATED ( ) { bitwise_operator } updated_bitmask )
                { comparison_operator } column_bitmask [ ...n ]
        } ] 
        sql_statement [ ...n ] 
    } 
} 

    
    
USE pubs
IF EXISTS (SELECT name FROM sysobjects
      WHERE name = 'employee_insupd' AND type = 'TR')
   DROP TRIGGER employee_insupd
GO
CREATE TRIGGER employee_insupd
ON employee
FOR INSERT, UPDATE
AS
/* Get the range of level for this job type from the jobs table. */
DECLARE @min_lvl tinyint,
   @max_lvl tinyint,
   @emp_lvl tinyint,
   @job_id smallint
SELECT @min_lvl = min_lvl, 
   @max_lvl = max_lvl, 
   @emp_lvl = i.job_lvl,
   @job_id = i.job_id
FROM employee e INNER JOIN inserted i ON e.emp_id = i.emp_id 
   JOIN jobs j ON j.job_id = i.job_id
IF (@job_id = 1) and (@emp_lvl <> 10) 
BEGIN
   RAISERROR ('Job id 1 expects the default level of 10.', 16, 1)
   ROLLBACK TRANSACTION
END
ELSE
IF NOT (@emp_lvl BETWEEN @min_lvl AND @max_lvl)
BEGIN
   RAISERROR ('The level for job_id:%d should be between %d and %d.',
      16, 1, @job_id, @min_lvl, @max_lvl)
   ROLLBACK TRANSACTION
END

    
    
    
    
CREATE PROCEDURE PROD_SALE
    @CODE varchar(10),
    @QTYSOLD smallint
AS
    UPDATE PRODUCT
    SET P_ONHAND=P_ONHAND-@QTYSOLD
    WHERE P_CODE=@CODE
GO        

EXEC PROD_SALE '13-Q2/P2', 2
        