1. Display all product information for products that contain the string hammer anywhere in their description. Remember to use the * for all attributes here and the LIKE keyword and the % wild card character where needed. [hint: you should have 2 rows of output].
2. Display the product code, description, and price for products provided by vendor 25595. Do NOT use the * here but list specific attributes and also use the WHERE clause. [hint: you should have 3 rows of output].
3. Display all product information for products currently selling for less than $30.00 and which have more than 50 units in stock (recorded in attribute P_QOH or quantity on hand). You will use the * for all attributes and also the AND keyword. [hint: you should have 3 rows of output].
4. Display the product code, product description, price and price after discount (a computed column that uses the P_PRICE and P_DISCOUNT [a percent discount] to be named with an alias of DiscountPrice [ ... AS DiscountPrice]) for products for which no vendor is specified in table PRODUCT. You will specify attribute names in the SELECT and use the WHERE clause for this query. [hint: you should have 2 rows of output].5. Display the product code, product description, product price and vendor name for products provided by vendors located in the state of Georgia. Since the attributes required for this query come from 2 separate tables, you will need to include those in your FROM clause and also join those 2 tables together using the PK/FK pairs WHERE T1.C1 = T2.C1 [refer to the diagram for the proper PK/FK from each table]. [hint: you should have 2 rows of output].
6. Display the full names of employees (including title and initial) who manage other employees, ordered by last name in descending order. Don't forget to include the title, initial, first name, and last name and look at the data given to determine how to designate those employees who manage others (i.e., EMP_MGR field is null). [hint: you should have 3 rows of output ordered properly].
7. Display the count only of distinct products ordered so far by customers (i. e., t h a t exist in table LINE). Make sure you position the DISTINCT keyword in the proper place. [hint: you
should have 1 value output].
8. For every manager (i. e., group on EMP_MGR), display the manager employee code (EMP_MGR) along with the total number (i. e., use COUNT() here) of employees s/he manages (requires a WHERE clause checking all EMP_MGRs that are not null). [hint: you should have 3 rows of output].
9. Display the product description, the full customer name and the customer balance for products ordered by customers having a customer balance between $300.00 and $400 (inclusive). Order by customer first name, then last name, then initial. Look at the table layouts above and determine which tables to include here. You will need to use all tables and join those appropriately in the WHERE clause using PK/FK pairs (3 joins). The BETWEEN keyword should be used in the WHERE clause for the proper balances [hint: you should have 3 rows of output ordered properly].
10. For every invoice, display the invoice number, invoice date and the total dollar amount for all products purchased in the invoice, ordered by invoice number in ascending order. Here you will use an aggregate SUM(x,y) function in your SELECT clause to calculate the total dollar amount (a calculated field - name it with the alias TOTAL). Don't forget to join on the tables as well. [hint: you should have 8 rows of output ordered properly].

 

Transcribed Image Text:

1 2 3 4 REM myCompany.sql REM This script file creates the following tables: VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE, EMPLOYEE and loads the default data rows select systimestamp from dual; 5 ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; 6 7 REM DROP TABLE LINE CASCADE CONSTRAINTS; 8 REM DROP TABLE INVOICE CASCADE CONSTRAINTS; 9 10 11 12 13 14 15 V CODE 16 V_NAME 17 V_CONTACT 18 V_AREACODE 19 V PHONE 20 V STATE 21 V ORDER 22 PRIMARY KEY REM DROP TABLE CUSTOMER CASCADE CONSTRAINTS; REM DROP TABLE PRODUCT CASCADE CONSTRAINTS; REM DROP TABLE VENDOR CASCADE CONSTRAINTS; REM DROP TABLE EMPLOYEE CASCADE CONSTRAINTS; CREATE TABLE VENDOR ( INTEGER, VARCHAR (35) NOT NULL, VARCHAR (15) NOT NULL, CHAR (3) NOT NULL, CHAR (8) NOT NULL, CHAR (2) NOT NULL, CHAR (1) NOT NULL, (V_CODE)); 23 24 25 CREATE TABLE 26 P_CODE 27 P DESCRIPT 28 P INDATE 29 P_QOH 30 P_MIN 31 P_PRICE 32 P_DISCOUNT 33 V CODE 34 35 PRODUCT ( VARCHAR2(10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY, VARCHAR2 (35) NOT NULL, DATE NOT NULL, NUMBER NOT NULL, NUMBER NOT NULL, NUMBER (8,2) NOT NULL, NUMBER (5,2) NOT NULL, NUMBER, CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR); 36 37 CREATE TABLE CUSTOMER ( 38 CUS_CODE NUMBER PRIMARY KEY, 39 CUS LNAME 40 CUS FNAME 41 CUS INITIAL 42 CUS_AREACODE 43 CUS PHONE 44 CUS BALANCE 45 VARCHAR (15) NOT NULL, VARCHAR (15) NOT NULL, CHAR(1), CHAR (3) DEFAULT '615' NOT NULL CHECK (CUS_AREACODE IN ('615', '713', '931')), CHAR (8) NOT NULL, NUMBER (9,2) DEFAULT 0.00, CONSTRAINT CUS U11 UNIQUE (CUS_LNAME, CUS_FNAME)); 46 47 48 CREATE TABLE INVOICE ( 49 INV_NUMBER 50 CUS_CODE 51 INV DATE 52 NUMBER PRIMARY KEY, NUMBER NOT NULL REFERENCES CUSTOMER (CUS_CODE), DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT INV_CK1 CHECK (INV_DATE > TO_DATE('01-JAN-2019', 'DD-MON-YYYY'))); 53 54 1 2 3 4 REM myCompany.sql REM This script file creates the following tables: VENDOR, PRODUCT, CUSTOMER, INVOICE, LINE, EMPLOYEE and loads the default data rows select systimestamp from dual; 5 ALTER SESSION SET NLS_DATE_FORMAT = 'DD-MON-YYYY'; 6 7 REM DROP TABLE LINE CASCADE CONSTRAINTS; 8 REM DROP TABLE INVOICE CASCADE CONSTRAINTS; 9 10 11 12 13 14 15 V CODE 16 V_NAME 17 V_CONTACT 18 V_AREACODE 19 V PHONE 20 V STATE 21 V ORDER 22 PRIMARY KEY REM DROP TABLE CUSTOMER CASCADE CONSTRAINTS; REM DROP TABLE PRODUCT CASCADE CONSTRAINTS; REM DROP TABLE VENDOR CASCADE CONSTRAINTS; REM DROP TABLE EMPLOYEE CASCADE CONSTRAINTS; CREATE TABLE VENDOR ( INTEGER, VARCHAR (35) NOT NULL, VARCHAR (15) NOT NULL, CHAR (3) NOT NULL, CHAR (8) NOT NULL, CHAR (2) NOT NULL, CHAR (1) NOT NULL, (V_CODE)); 23 24 25 CREATE TABLE 26 P_CODE 27 P DESCRIPT 28 P INDATE 29 P_QOH 30 P_MIN 31 P_PRICE 32 P_DISCOUNT 33 V CODE 34 35 PRODUCT ( VARCHAR2(10) CONSTRAINT PRODUCT_P_CODE_PK PRIMARY KEY, VARCHAR2 (35) NOT NULL, DATE NOT NULL, NUMBER NOT NULL, NUMBER NOT NULL, NUMBER (8,2) NOT NULL, NUMBER (5,2) NOT NULL, NUMBER, CONSTRAINT PRODUCT_V_CODE_FK FOREIGN KEY (V_CODE) REFERENCES VENDOR); 36 37 CREATE TABLE CUSTOMER ( 38 CUS_CODE NUMBER PRIMARY KEY, 39 CUS LNAME 40 CUS FNAME 41 CUS INITIAL 42 CUS_AREACODE 43 CUS PHONE 44 CUS BALANCE 45 VARCHAR (15) NOT NULL, VARCHAR (15) NOT NULL, CHAR(1), CHAR (3) DEFAULT '615' NOT NULL CHECK (CUS_AREACODE IN ('615', '713', '931')), CHAR (8) NOT NULL, NUMBER (9,2) DEFAULT 0.00, CONSTRAINT CUS U11 UNIQUE (CUS_LNAME, CUS_FNAME)); 46 47 48 CREATE TABLE INVOICE ( 49 INV_NUMBER 50 CUS_CODE 51 INV DATE 52 NUMBER PRIMARY KEY, NUMBER NOT NULL REFERENCES CUSTOMER (CUS_CODE), DATE DEFAULT SYSDATE NOT NULL, CONSTRAINT INV_CK1 CHECK (INV_DATE > TO_DATE('01-JAN-2019', 'DD-MON-YYYY'))); 53 54