Using the CoffeeDB database, do the following queries (again, label each query w

Question

Using the CoffeeDB database, do the following queries (again, label each query with a number, as in --1) 1. Display employee first and last name, commission rate and order date for all employees who have a commission rate greater than .08%. Sort by rate, from highest to lowest. 2. Display inventory name and price, and order quantity and price, for all inventory items where the inventory price is greater than the order price. 3. Display consu state population and land area, for those states where the population is less than or mer first and last name, zip, phone, and whether they are favored or not, plus equal to 30 million or for those consumers who are favored. Sort by population, for greatest to smallest. 4. Display employee last name, phone, commission rate, hire date, and order date for all employees who were hired before 1995 and who have a phone number that starts with a 552. 5. Display consumer first and last name, fax, order date, and employee last name and gender, by order date, from newest to oldest. employee serviced), for all employees that were born in the 1960s or for all male employees. for all employees who are male and for all consumers who do not have a fax number. Sort 6. Display first and last name of employee, birth date, first and last name of consumers (the Sort by consumer's credit limit. 7. Display state name (not the 2 letter abbreviation), first and last name of consumer, and order dates, and discount (on orders) for all orders where the discount is between 02 and.08 and where the credit limit of the consumer is greater than 5000. 8. CREATE a VIEW; call it v_employee_order. Include first and last name of employee, order date of order and customer purchase order (customer po). Drop the view at the top of the homework script

Explanation / Answer

1. SELECT FIRST_NAME,LAST_NAME,COMMISION_RATE,ORDER_DATE from EMPLOYEES where COMMISION_RATE > .08 ORDER BY COMMISION_RATE DESC;

2. SELECT INVENTORY_NAME,INVENTORY_PRICE,ORDER_QUANTITY,ORDER_PRICE from INVENTORY where INVENTORY_PRICE > ORDER_PRICE;

3. SELECT FIRST_NAME,LAST_NAME,ZIP,PHONE, FAVOURED_STATUS,STATE_POPULATION,LAND_AREA FROM CONSUMER where STATE_POPULATION <= 30000000 OR FAVOURED_STATUS = 'YES' ORDER BY POPULATION DESC;

4. SELECT LAST_NAME,PHONE,COMMISION_RATE,HIRE_DATE,ORDER_DATE from EMPLOYEES where DATEPART(yyyy, HIRE_DATE) <1995 AND PHONE like '552%';

5. SELECT CONSUMER.FIRST_NAME,CONSUMER.LAST_NAME,CONSUMER.FAX,CONSUMER.ORDER_DATE,CONSUMER,EMPLOYEES.LAST_NAME,EMPLOYEES.GENDER FROM CONSUMER INNER JOIN
EMPLOYEES where EMPLOYEES.GENDER = 'MALE' AND CONSUMER.FAX = NULL ORDER BY CONSUMER.ORDER_DATE DESC;

6. SELECT EMPLOYEES.FIRST_NAME,EMPLOYEES.LAST_NAME,CONSUMER.DOB,CONSUMER.FIRST_NAME,CONSUMER.LAST_NAME FROM
CONSUMER INNER JOIN EMPLOYEES ON EMPLOYEES.EMP_ID = CONSUMER.SERVED_EMP_ID where DATEPART(yyyy, EMPLOYEES.DOB) >= 1960 and DATEPART(yyyy, EMPLOYEES.DOB) < 1970 OR EMPLOYEES.GENDER = 'MALE' ORDER BY CONSUMER.CREDIT_LIMIT;

7. SELECT STATE_NAME,FIRST_NAME,LAST_NAME,ORDER_DATE,DISCOUNT from CONSUMERS where DISCOUNT > .02 AND DISCOUNT < .08 AND CREDIT_LIMIT > 5000;

8. CREATE VIEW V_EMPLOYEE_ORDER AS SELECT EMPLOYEES.FIRST_NAME,EMPLOYEES.LAST_NAME,CONSUMER.ORDER_DATE,CONSUMER.CUSTOMER_PO from CONSUMER INNER JOIN
EMPLOYEES ;

Related Questions

Navigate

• Browse (All)
• Browse U
• Subjects

---

---

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