/* INTRO TO SQL - SQL EXAMPLES 									*/
/* SQL-TEXT-1.TXT                                                                               */
/* This file contains sql commands used in the textbook.					*/
/* Teachers and students can use this file to "copy and paste"					*/
/* the SQL commands from this file to the SQL Query Analyzer in MS Sql Server.			*/
/* This way, the class loses less time in typing (and error corrections!).			*/
/* Please be aware that date formats may differ among DBMS programs.				*/
/* Most commands here are for MS SQL Server or MS ACCESS where indicated.			*/
/* The ";" has been removed to allow the teacher to explain the command before executing it 	*/

/* ======== CHANGING YOUR PASSWORD								*/
/* This Data Control Lanaguage command is given at the beginning of class to show students	*/
/* how to change their respective password							*/

SET PASSWORD FOR 'user-name-here'@'hostname-name-here' = PASSWORD('new-password-here')


--======== CREATING TABLE STRUCTURES

CREATE TABLE VENDOR ( 
 V_CODE     INTEGER     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 PRODUCT ( 
 P_CODE     VARCHAR(10)  NOT NULL UNIQUE, 
 P_DESCRIPT VARCHAR(35)  NOT NULL, 
 P_INDATE   DATETIME     NOT NULL, 
 P_QOH      SMALLINT     NOT NULL,  
 P_MIN      SMALLINT     NOT NULL, 
 P_PRICE    NUMERIC(8,2) NOT NULL, 
 P_DISCOUNT NUMERIC(4,2) NOT NULL, 
 V_CODE     INTEGER, 
 PRIMARY KEY (P_CODE),
 FOREIGN KEY (V_CODE) REFERENCES VENDOR (V_CODE));


-- Checking table structure in SQL Server

DESCRIBE VENDOR;

DESCRIPBE PRODUCT;


--======== CONSTRAINTS, CHECKS and DEFAULTS

CREATE TABLE CUSTOMER(
CUS_CODE       	INTEGER 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),
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     	INTEGER PRIMARY KEY,
CUS_CODE        INTEGER NOT NULL,
INV_DATE        DATETIME NOT NULL,
CONSTRAINT INV_CUS_CODE_FK FOREIGN KEY REFERENCES CUSTOMER(CUS_CODE));


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


--======== INDEXES

-- Q: Create index on P_INDATE
CREATE INDEX P_INDATEX ON PRODUCT(P_INDATE)

-- Q: Create composite index on V_CODE and P_CODE
CREATE INDEX VENPRODX ON PRODUCT(V_CODE,P_CODE)

-- Q: Create index on P_PRICE descendent order
CREATE INDEX PROD_PRICEX ON PRODUCT(P_PRICE DESC)

-- Q: Delete the PROD_PRICEX index
DROP INDEX PRODUCT.PROD_PRICEX

--======== DATA ENTRY

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

SELECT * FROM VENDOR

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

SELECT * FROM VENDOR

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

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

SELECT * FROM PRODUCT

-- Insert with null attribute

INSERT INTO PRODUCT
VALUES ('BRT-345','Titanium drill bit', '2015-10-18', 75, 10, 4.50, 0.06, NULL)

-- Insert with optional attributes

INSERT INTO PRODUCT(P_CODE, P_DESCRIPT)
VALUES ('BRT-345','Titanium drill bit')

--======== COMMIT 

BEGIN TRANSACTION
COMMIT

--======== LISTING TABLE CONTENTS - SELECT QUERIES


SELECT * FROM PRODUCT

SELECT P_CODE, P_DESCRIPT, P_INDATE, P_QOH, P_MIN, P_PRICE, P_DISCOUNT, V_CODE
FROM   PRODUCT

--======== UPDATE TABLE ROWS 

UPDATE PRODUCT
SET    P_INDATE = '2016-01-18'
WHERE  P_CODE = '13-Q2/P2'

SELECT * FROM PRODUCT

UPDATE PRODUCT
SET    P_INDATE = '2016-01-18', 
       P_PRICE = 16.99, 
       P_MIN = 10
WHERE  P_CODE = '13-Q2/P2'

SELECT * FROM PRODUCT

--======== ROLLBACK

BEGIN;
ROLLBACK;

SELECT * FROM PRODUCT;

--======== DELETE TABLE ROWS 

BEGIN;

DELETE FROM PRODUCT
WHERE  P_CODE = '13-Q2/P2';

--Note Safe Mode must be disabled before the following command
DELETE FROM PRODUCT
WHERE  P_MIN = 5;

-- NOTE: use rollback to restore table rows

ROLLBACK

--======== INSERTING ROWS WITH SELECT SUBQUERY

/* Run script to create P and V tables (CREATE_P_V.SQL)	*/
/* Use: isql or osql from the command-line		*/

DELETE FROM PRODUCT;

DELETE FROM VENDOR;

INSERT INTO VENDOR SELECT * FROM V;

INSERT INTO PRODUCT SELECT * FROM P;

SELECT * FROM VENDOR;

SELECT * FROM PRODUCT;

--======== CREATE ALL DATABASE TABLES


--======== SELECT WITH WHERE CLAUSE 

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM   PRODUCT
WHERE  V_CODE = 21344;

-- Other comparison operators >, >=, <, <=, <>

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM   PRODUCT
WHERE  V_CODE < > 21344;

-- Access user <>  w/o spaces

SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE
FROM   PRODUCT
WHERE  P_PRICE <= 10;

-- Using comparison operators in character attributes

SELECT P_CODE, P_DESCRIPT, P_QOH, P_MIN, P_PRICE
FROM   PRODUCT
WHERE  P_CODE < '1558-QW1';

-- Using comparison operators on dates

SELECT P_DESCRIPT, P_QOH, P_MIN, P_PRICE, P_INDATE
FROM   PRODUCT
WHERE  P_INDATE >= '2016-01-20';

-- Using computed columns and aliases 

SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE
FROM   PRODUCT;

SELECT P_DESCRIPT, P_QOH, P_PRICE, P_QOH*P_PRICE AS TOTVALUE
FROM   PRODUCT;

SELECT P_CODE, P_INDATE, CURDATE() - 90 AS CUTDATE
FROM   PRODUCT
WHERE  P_INDATE <= CURDATE() - 90;

SELECT P_CODE, P_INDATE, P_INDATE + 90 AS EXPDATE
FROM   PRODUCT;

-- Logical operators AND, OR, NOT

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 > '2016-01-15';

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

SELECT *
FROM   PRODUCT
WHERE  NOT (V_CODE = 21344);

-- Special operators : BETWEEN, IS NULL, LIKE, IN, EXITS

SELECT *
FROM   PRODUCT
WHERE  P_PRICE BETWEEN 50.00 AND 100.00;

SELECT P_CODE, P_DESCRIPT
FROM   PRODUCT
WHERE  P_MIN IS NULL;

SELECT P_CODE, P_DESCRIPT
FROM   PRODUCT
WHERE  V_CODE IS NULL;

-- Note: MS Access and SQL Server are case insensitive
-- Note: MS Access uses * and %, SQL Server uses % and _

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

-- or use this version if case sensitive 

SELECT V_NAME, V_CONTACT, V_AREACODE, V_PHONE
FROM   VENDOR
WHERE  UPPER(V_CONTACT) LIKE 'SMITH%';

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

SELECT *
FROM   VENDOR
WHERE  V_CONTACT LIKE 'Johns_n';

SELECT *
FROM   PRODUCT
WHERE  V_CODE IN (21344, 24288);

-- EXISTS
/* Returns true if the inner query returns at least 1 row, otherwise it returns false	*/
/* Q: List all vendors but only if there are products to order (P_QOH <= P_MIN)	*/

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

-- Q: List all vendors but only if there are products with the qty on hand less than double the min qty
SELECT * FROM VENDOR
WHERE  EXISTS (SELECT * FROM PRODUCT WHERE P_QOH < P_MIN*2);

-- Q: List the products that are supplied by a vendor
SELECT *
FROM   PRODUCT
WHERE  V_CODE IS NOT NULL;

-- Q: List the v_code of vendors that provide products
SELECT V_CODE FROM PRODUCT;      		-- includes duplicates and Nulls

SELECT DISTINCT V_CODE FROM PRODUCT;      	-- includes Nulls

-- Q: List the V_CODE and V_NAME of vendors that provide products
SELECT V_CODE, V_NAME
FROM   VENDOR 
WHERE  V_CODE IN (SELECT DISTINCT V_CODE FROM PRODUCT);

/* Three value logic (NULL) problem - Null values in FK cause problems		*/
/* Q: List the V_CODE and V_NAME of vendors that do not provide products	*/

SELECT V_CODE, V_NAME
FROM   VENDOR 
WHERE  V_CODE NOT IN (SELECT DISTINCT V_CODE FROM PRODUCT);   -- will list no rows!

-- Correct answer
SELECT V_CODE, V_NAME
FROM   VENDOR 
WHERE  V_CODE NOT IN (SELECT DISTINCT V_CODE FROM PRODUCT WHERE V_CODE IS NOT NULL);


--======== ADVANCED DATA MGMT

/* ALTER DATA TYPE								*/
/* Will not run unless column is empty - only increasing size will work		*/
-- Q: Change the V_CODE data type to CHAR
ALTER TABLE PRODUCT
ALTER COLUMN V_CODE VARCHAR(5);

-- Q: Increase the width of P_PRICE column to nine digits
ALTER  TABLE PRODUCT
ALTER COLUMN P_PRICE NUMERIC(9,2);

-- ADD A COLUMN TO TABLE

ALTER TABLE PRODUCT
ADD   P_SALECODE CHAR(1);

-- DROPING A COLUMN FROM TABLE

ALTER TABLE VENDOR
DROP  COLUMN V_ORDER;

-- UPDATE COMMANDS

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 < '2015-12-25';

UPDATE PRODUCT
SET    P_SALECODE = '1'
WHERE  P_INDATE >= '2016-01-16'
AND    P_INDATE <  '2016-02-10';

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 PARTS OF TABLES									*/
/* Q: Create a PART table with only using the code, description and price columns of PRODUCT	*/

CREATE TABLE PART (
PART_CODE     CHAR(8) NOT NULL UNIQUE,
PART_DESCRIPT CHAR(35),
PART_PRICE    DECIMAL(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 P_CODE, P_DESCRIPT, P_PRICE 
INTO   PART 
FROM   PRODUCT;

-- ADDING PK AND FK WITH THE ALTER TABLE COMMAND

ALTER TABLE PART 
  ADD PRIMARY KEY(PART_CODE);

ALTER TABLE PRODUCT
  ADD PRIMARY KEY(P_CODE);

ALTER TABLE PRODUCT
  ADD FOREIGN KEY(V_CODE) REFERENCES VENDOR (V_CODE);

-- OR

ALTER TABLE PRODUCT 
  ADD PRIMARY KEY(P_CODE),
  FOREIGN KEY (V_CODE) REFERENCES VENDOR (V_CODE);


-- DROPING A TABLE 

DROP TABLE PART;

/*======== END PART I		*/