View the step-by-step solution to:

Homework #4 You may do this homework with Oracle or Teradata database software. Write and run SQL queries for the two problems described below.

Please look at the attachment file, YOU WILL NEED ORACLE SQL DEVELOPER SOFTWARE TO DO IT. HELP PLEASE!

Homework #4 You may do this homework with Oracle or Teradata database software. Write and run SQL queries for the two problems described below. Create a new Microsoft Word document and write your name at the top of the document. For each of the two queries, write query # and paste the screenshot below it. QUERY 1 : How many different (unique) products each customer has purchased from this business? (Note: sales records are available in the LINE table.) Output should be sorted in descending order of unique products customers bought. Your query output should like the sample (partial) output shown below. Please capture and paste a single screenshot of the query and its complete output in the Word document. QUERY 2: For each vendor, display vendor code and vendor name along with total dollar amount of sales generated by selling products supplied by each vendor. Your query output should like the partial sample output shown below. Please capture and paste a single screenshot of the query and its complete output in the Word document. Please sort the output in descending order of dollar amount of sales.
Background image of page 1
/* ADVSQLDBINIT.SQL */ /* Advanced SQL */ /* Script file for ORACLE DBMS */ /* This script file creates the following tables: */ /* V - default vendor table data */ /* P - default product table data */ /* VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE */ /* EMPLOYEE, EMP */ /* and loads the default data rows */ DROP TABLE LINE; DROP TABLE INVOICE; DROP TABLE CUSTOMER; DROP TABLE CUSTOMER_2; DROP TABLE PRODUCT; DROP TABLE VENDOR; DROP TABLE EMPLOYEE; DROP TABLE EMP; DROP TABLE P; DROP TABLE V; CREATE TABLE V ( V_CODE INTEGER 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 P ( P_CODE VARCHAR2(10) PRIMARY KEY, P_DESCRIPT VARCHAR2(35) NOT NULL, P_INDATE DATE NOT NULL, P_QOH NUMBER NOT NULL, P_MIN NUMBER NOT NULL, P_PRICE NUMBER(8,2) NOT NULL, P_DISCOUNT NUMBER(4,2) NOT NULL, V_CODE NUMBER, P_MIN_ORDER NUMBER, P_REORDER NUMBER); CREATE TABLE VENDOR ( V_CODE INTEGER, 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 VARCHAR2(10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY, P_DESCRIPT VARCHAR2(35) NOT NULL, P_INDATE DATE NOT NULL, P_QOH NUMBER NOT NULL, P_MIN NUMBER NOT NULL, P_PRICE NUMBER(8,2) NOT NULL, P_DISCOUNT NUMBER(4,2) NOT NULL, V_CODE NUMBER,
Background image of page 1
P_MIN_ORDER NUMBER, P_REORDER NUMBER, CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR); CREATE TABLE CUSTOMER ( CUS_CODE NUMBER 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 NUMBER(9,2) DEFAULT 0.00, CONSTRAINT CUS_UI1 UNIQUE(CUS_LNAME,CUS_FNAME)); CREATE TABLE CUSTOMER_2 ( CUS_CODE NUMBER PRIMARY KEY, CUS_LNAME VARCHAR(15) NOT NULL, CUS_FNAME VARCHAR(15) NOT NULL, CUS_INITIAL CHAR(1), CUS_AREACODE CHAR(3), CUS_PHONE CHAR(8)); CREATE TABLE INVOICE ( INV_NUMBER NUMBER PRIMARY KEY, CUS_CODE NUMBER NOT NULL REFERENCES CUSTOMER(CUS_CODE), INV_DATE DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT INV_CK1 CHECK (INV_DATE > TO_DATE('01-JAN-2002','DD-MON-YYYY'))); CREATE TABLE LINE ( INV_NUMBER NUMBER NOT NULL, LINE_NUMBER NUMBER(2,0) NOT NULL, P_CODE VARCHAR(10) NOT NULL, LINE_UNITS NUMBER(9,2) DEFAULT 0.00 NOT NULL, LINE_PRICE NUMBER(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)); CREATE TABLE EMPLOYEE ( EMP_NUM NUMBER PRIMARY KEY, EMP_TITLE CHAR(10), EMP_LNAME VARCHAR(15) NOT NULL, EMP_FNAME VARCHAR(15) NOT NULL, EMP_INITIAL CHAR(1), EMP_DOB DATE, EMP_HIRE_DATE DATE, EMP_YEARS NUMBER, EMP_AREACODE CHAR(3), EMP_PHONE CHAR(8)); CREATE TABLE EMP ( EMP_NUM NUMBER PRIMARY KEY, EMP_TITLE CHAR(10), EMP_LNAME VARCHAR(15) NOT NULL, EMP_FNAME VARCHAR(15) NOT NULL, EMP_INITIAL CHAR(1), EMP_DOB DATE,
Background image of page 2
Show entire document

Top Answer

View the full answer
HW4-Instructions Solution.docx

Homework #4 You may do this homework with Oracle or Teradata database software. Write and run SQL queries for the two
problems described below.
Create a new Microsoft Word document and write your...

Sign up to view the full answer

Why Join Course Hero?

Course Hero has all the homework and study help you need to succeed! We’ve got course-specific notes, study guides, and practice tests along with expert tutors.

-

Educational Resources
  • -

    Study Documents

    Find the best study resources around, tagged to your specific courses. Share your own to gain free Course Hero access.

    Browse Documents
  • -

    Question & Answers

    Get one-on-one homework help from our expert tutors—available online 24/7. Ask your own questions or browse existing Q&A threads. Satisfaction guaranteed!

    Ask a Question
Ask a homework question - tutors are online