/* 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							*/


EXEC SP_PASSWORD NULL, 'abc', 'userid'


--======== 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)


-- Checking table structure in SQL Server

SP_HELP VENDOR

SP_HELP 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 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     	INTEGER PRIMARY KEY,
CUS_CODE        INTEGER NOT NULL REFERENCES CUSTOMER(CUS_CODE),
INV_DATE        DATETIME DEFAULT CURRENT_TIMESTAMP NOT NULL,
CONSTRAINT INV_CK1 CHECK (INV_DATE > '01-JAN-2008'))


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 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','11-03-07',8,5,109.99,0.00,21225)

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

SELECT * FROM PRODUCT

-- Insert with null attribute

INSERT INTO PRODUCT
VALUES ('BRT-345','Titanium drill bit', '10-18-07', 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 = '18-JAN-2008'
WHERE  P_CODE = '13-Q2/P2'

SELECT * FROM PRODUCT

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

SELECT * FROM PRODUCT

--======== ROLLBACK

BEGIN TRANSACTION
ROLLBACK

SELECT * FROM PRODUCT

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

BEGIN TRANSACTION

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

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
/* Use: isql or osql to run the drive:\path\ch06dbinit.sql file	*/

--======== 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 >= '20-JAN-2008'

-- 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, CURRENT_TIMESTAMP - 90 AS CUTDATE
FROM   PRODUCT
WHERE  P_INDATE <= CURRENT_TIMESTAMP - 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 > '15-JAN-2008'

SELECT P_DESCRIPT, P_INDATE, P_PRICE, V_CODE
FROM   PRODUCT
WHERE (P_PRICE < 50 AND P_INDATE > '15-JAN-2008')
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 < '25-DEC-2007'

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

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

-- OR

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


-- DROPING A TABLE 

DROP TABLE PART

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