Use the following tables to complete the exercises: EMPLOYEE_TBL EMP_ID VARCHAR(

Question

Use the following tables to complete the exercises:

EMPLOYEE_TBL
EMP_ID VARCHAR(9) NOT NULL primary key,
LAST_NAME VARCHAR(15) NOT NULL,
FIRST_NAME VARCHAR(15) NOT NULL,
MIDDLE_NAME VARCHAR(15),
ADDRESS VARCHAR(30) NOT NULL,
CITY VARCHAR(15) NOT NULL,
STATE CHAR(2) NOT NULL,
ZIP NUMERIC(5) NOT NULL,
PHONE CHAR(10),
PAGER CHAR(10),

EMPLOYEE_PAY_TBL
EMP_ID VARCHAR(9) NOT NULL primary key,
POSITION VARCHAR(15) NOT NULL,
DATE_HIRE DATE,
PAY_RATE DECIMAL(4,2),
DATE_LAST_RAISE DATE,
SALARY DECIMAL(8,2),
BONUS DECIMAL(6,2),
CONSTRAINT EMP_FK FOREIGN KEY (EMP_ID) REFERENCES EMPLOYEE_TBL (EMP_ID)

CUSTOMER_TBL
CUST_ID VARCHAR(10) NOT NULL primary key,
CUST_NAME VARCHAR(30) NOT NULL,
CUST_ADDRESS VARCHAR(20) NOT NULL,
CUST_CITY VARCHAR(15) NOT NULL,
CUST_STATE CHAR(2) NOT NULL,
CUST_ZIP NUMERIC(5) NOT NULL,
CUST_PHONE NUMERIC(10),
CUST_FAX NUMERIC(10),

ORDERS_TBL
ORD_NUM VARCHAR(10) NOT NULL primary key,
CUST_ID VARCHAR(10) NOT NULL,
PROD_ID VARCHAR(10) NOT NULL,
QTY NUMERIC(6) NOT NULL,
ORD_DATE DATE,


PRODUCTS_TBL

PROD_ID VARCHAR(10) NOT NULL primary key,
PROD_DESC VARCHAR(40) NOT NULL,
COST DECIMAL(6,2) NOT NULL,

1. List each customer who ordered products over 100 dollar. Hint: SUM().
2. Show IDs and descriptitons of those products that are ordered at least once by customers.hint: COUNT().
3. What would happen, if the following statements were run?
a.
SELECT
DATE_HIRE,SUM(SALARY)
FROM
EMPLOYEE_PAY_TBL
GROUP BY SALARY
HAVING
DATE_HIRE BETWEEN '1989-01-01' AND '1997-12-31' AND SALARY IS NOT NULL
b.
SELECT LAST_NAME, FIRST_NAME, CITY
FROM EMPLOYEE_TBL
GROUP BY LAST_NAME;

c.
SELECT
CITY, COUNT(*)
FROM
EMPLOYEE_TBL
GROUP BY
CITY

4. How does the HAVING clause differ from the WHERE clause?

Explanation / Answer

1)

SELECT CUST_NAME FROM CUSTOMER_TBL WHERE SUM (COST) > 100 AND CUSTOMER_TBL.CUST_ID = ORDERS_TBL.CUST_ID AND ORDERS_TBL.PROD_ID = PRODUCTS_TBL.PROD_ID

2)

SELECT PROD_ID, PROD_DESC FROM PRODUCTS_TBL GROUP BY PROD_ID WHERE COUNT (ORDER_DATE) > = 1

3)

(a)

The statement would return the Hire_date and salary of each employee who joined between the dates 31st dec 97 and 1st jan 89 and who has a salary

It may not give any records because there will be no date between31st dec 97 and 1st jan 89

(b)

Returns the last name, first name, city of employees with separate last names.

(c)

Results the number of various cities in the employee table

(4)

HAVING clause is used with the aggregate functions where as the WHERE clause cannot be used with the aggregate functions.

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.