Use the SaleCo ERD listed below and the provided SQL scripts to construct its relational database and answer the following queries.

1. Write a query to count the number of invoices.
2. Write a query to count the number of customers with a customer balance over $500.
3. Generate a listing of all purchases made by customers.
4. Generate a listing of customer purchases, including the subtotals for each of the invoice line numbers.
5. List the balance characteristics of the customers who have made purchases during the current invoice cyclethat is, for the customers who appear in the INVOICE table.
6. Find a listing of customers who did not make purchases during the invoicing period.
7. Create a query to produce a summary of the value of products currently in inventory.

BEGIN TRANSACTION;

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

.schema VENDOR

.schema 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, FOREIGN KEY (CUS_CODE ) 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 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

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

.read LoadRowsIntoDB.sql

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, DATE() - 90 AS CUTDATE FROM PRODUCT WHERE P_INDATE <= DATE() - 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

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

-- DROPING A TABLE

DROP TABLE PART;