Hi there,

i attached the screenshots of the assignment and also the two SQL files.

thanks!

28% E .. T-Mobile Back 1:24 AM Assignment Details SQL Assignment Instructions: Use the SaleCo ERD listed below and the provided SQL scripts to construct its relational database and answer the following queries. Note: Purchases = LINE_PRICELINE_UNITS Note: Inventory value = P_QOH * P_PRICE 1. 1. (2 pts) How many invoices are there? (3 pts). List the invoice numbers and the invoice dates. 2. (2 pts) How many customers are there? (3 pts) List the customer codes and customer names. 3. (2 pts) List vendor numbers and vendor names. (3 pts) Show the vendor count per state. 4. (2 pts) Based on price, what is the most expensive product? (3 pts) How much quantity on hand is available for the most expensive product? 5. (2 pts) Write a query to count the number of customers with a customer balance Submit Assignment Previous Next Dashboard Calendar To Do Notifications Inbox ..1 T-Mobile 1:24 AM 28% 4 Back Assignment Details SQL 5. (2 pts) Write a query to count the number of customers with a customer balance over $500. (3 pts) List the customer code, customer name and balance amount of these customers. 6. (5 pts) Generate a listing of products offered by each Vendor. List vendor name, product code and product name. Sort by vendor name and product code. 7. (5 pts) Generate a listing of customer purchases, including the subtotals for each of the invoice line numbers; sort output by customer code, invoice number and the line_number. 8. (5 pts) List the balance characteristics of the customers who have made purchases during the current invoice cycle-that is, for the customers who appear in the INVOICE table; sort by customer code. 9. (5 pts) Find a listing of customers who did not make purchases during the invoicing period; sort by customer code. 10. (5 pts) Create a query to produce a summary of the value of products currently in inventory. Submit Assignment Previous Next BED Dashboard Calendar To Do Notifications Inbox .. T-Mobile 1:24 AM 29% 4 Back Assignment Details SQL Download two files: Build-DB-SaleCo- ver1.sql (will create the database tables) and LoadRowsintoDBver1.sql(which will load the data into the tables). SQL Script: Run SQLite 3 and at the sqlite prompt, enter the following command. Note that the Build script automatically calls the Load data script. sqlite>.read Build-DB-SaleCo- ver1.sql LINE WMMH CUSTOMER CINCODE LICNO NIR LINEN PODE GS BACOOR CUS CUSOODE GUSON OLS ME H FOOD VENDOR P_DISCOUNT VAME VOOR VOME + VORR DESCRIPTION V. STAR VAREACODE Submit Assignment Previous Next Dashboard Calendar To Do Notifications Inbox 30% Student .ill 1:26 AM AA . dn.inst-fs-iad-prod.inscloudgate.net e 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)); === 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_U11 UNIQUE (CUS_LNAME, CUS_FNAME)); CREATE TABLE INVOICE INV_NUMBER INTEGER PRIMARY KEY, CUS_CODE INTEGER NOT NULL, INV DATE DATETIME NOT NULL, Student .il 1:27 AM 30% AA dn.inst-fs-iad-prod.inscloudgate.net NADUAN KUA MUMMY 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_U11 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); -======== DATA ENTRY COMMIT; .read LoadRows IntoDBver1.sql --==== LOOK AT TABLES CREATED .print " Below are the tables that were created :" .print " ===== .tables .print " "