Prod lgproductwhere prod water andprod sealer

--1. Write a query to display the eight departments in the LGDEPARTMENT table.

SELECT *

FROM LGPRODUCT

WHERE PROD_BASE = 'WATER' AND

SELECT EMP_FNAME,EMP_LNAME,EMP_EMAIL

FROM LGEMPLOYEE

work in department 300 or have the title “CLERK I

-- Sort the output by last name and then by first name.

ORDER BY EMP_LNAME,EMP_FNAME

--5. Write a query to display the employee number, last name, first name, salary “from” date, salary end date, and salary

ON E.EMP_NUM = S.EMP_NUM

WHERE E.EMP_NUM = 83731 OR

Foresters Best brand top coat between July 15, 2013,

-- and July 31, 2013. If a customer purchased more than one such product, display the customer’s information

FROM LGCUSTOMER C INNER JOIN LGINVOICE I

ON C.CUST_CODE = I.CUST_CODE

INNER JOIN LGBRAND B

ON P.BRAND_ID = B.BRAND_ID

C.CUST_CITY, C.CUST_STATE, C.CUST_ZIP

ORDER BY C.CUST_STATE, C.CUST_LNAME, C.CUST_FNAME

FROM LGEMPLOYEE E INNER JOIN LGDEPARTMENT D

ON E.DEPT_NUM = D.DEPT_NUM

SELECT B.BRAND_NAME, COUNT(P.PROD_SKU) AS 'PRODCOUNT'

FROM LGBRAND B INNER JOIN LGPRODUCT P

SELECT COUNT(PROD_BASE) AS 'WATERBASE'

FROM LGPRODUCT

GROUP BY PROD_BASE, PROD_TYPE

--11. Write a query to display the total inventory—that is, the sum of all products on hand for each brand ID. Sort the output

ORDER BY BRAND_ID DESC

--12. Write a query to display the brand ID, brand name, and average price of products of each brand. Sort the output by

ON B.BRAND_ID = P.BRAND_ID

GROUP BY B.BRAND_ID, B.BRAND_NAME

SELECT E.DEPT_NUM, MAX(E.EMP_HIREDATE)

FROM LGEMPLOYEE E

-- Sort the output by largest salary in descending order.

SELECT E.EMP_NUM, E.EMP_FNAME, E.EMP_LNAME,

GROUP BY E.EMP_NUM, E.EMP_FNAME, E.EMP_LNAME, E.DEPT_NUM

ORDER BY LARGESTSALARY DESC

FROM LGCUSTOMER C INNER JOIN LGINVOICE I

ON C.CUST_CODE = I.CUST_CODE

HAVING SUM(I.INV_TOTAL) > 1500

)

-- Sort the output by department name.

SELECT D.DEPT_NUM,D.DEPT_NAME,D.DEPT_PHONE,E.EMP_NUM,E.EMP_LNAME,E.EMP_TITLE

-- Sort the output by vendor name and then by brand name.

--DOESNT SEEM RIGHT

WHERE V.VEND_ID IN ( SELECT V.VEND_ID

FROM LGSUPPLIES S INNER JOIN LGVENDOR V ON S.VEND_ID =

WHERE P.BRAND_ID

IN ( SELECT B.BRAND_ID

GROUP BY V.VEND_ID, V.VEND_NAME, B.BRAND_NAME

ORDER BY V.VEND_NAME,B.BRAND_NAME

FROM LGEMPLOYEE E INNER JOIN LGINVOICE I ON E.EMP_NUM = I.EMPLOYEE_ID

WHERE E.EMP_NUM IN ( SELECT EMPLOYEE_ID

--19. Write a query to display the largest average product price of any brand.

SELECT MAX (AVGPRICE)

the largest average product price.

SELECT P.BRAND_ID, B.BRAND_NAME, B.BRAND_TYPE, MAX(AVGPRICE)

GROUP BY P.BRAND_ID, B.BRAND_NAME, B.BRAND_TYPE

--21. Write a query to display the manager name, department name, department phone number, employee name, customer

SELECT MANAGER, D.DEPT_NAME, D.DEPT_PHONE, CONCAT(E.EMP_FNAME, ' ', E.EMP_LNAME) AS

EMPNAME,