Question

Problem 33, chapter 7 from Database Systems 13th edition by Coronel

Write a query to display the employee number, last name, email address, title, and

department name of each employee whose job title ends in the word “ASSOCIATE.”

Sort the output by department name and employee title

Problem 36, chapter 7 from Database Systems 13th edition by Coronel

Write a query to display the number of products within each base and type combination,

sorted by base and then by type

Problem 37, chapter 7 from Database Systems 13th edition by Coronel

Write a query to display the total inventory—that is, the sum of all products on hand

for each brand ID. Sort the output by brand ID in descending order

Problem 38, chapter 7 from Database Systems 13th edition by Coronel

Write a query to display the brand ID, brand name, and average price of products

of each brand. Sort the output by brand name. Results are shown with the average

price rounded to two decimal places

Problem 40, chapter 7 from Database Systems 13th edition by Coronel

Write a query to display the employee number, first name, last name, and largest

salary amount for each employee in department 200. Sort the output by largest salary

in descending order

Problem 41, chapter 7 from Database Systems 13th edition by Coronel

Write a query to display the customer code, first name, last name, and sum of all

invoice totals for customers with cumulative invoice totals greater than $1,500. Sort

the output by the sum of invoice totals in descending order

Problem 42, chapter 7 from Database Systems 13th edition by Coronel

Write a query to display the department number, department name, department

phone number, employee number, and last name of each department manager. Sort

the output by department name

Answer 1

Solution

Problem 33

SELECT E.EMP_NUM, EMP_LNAME, EMP_EMAIL, EMP_TITLE, DEPT_NAME
FROM LGEMPLOYEE AS E, LGDEPARTMENT AS D
WHERE E.DEPT_NUM=D.DEPT_NUM AND
EMP_TITLE LIKE '*ASSOCIATE'
ORDER BY DEPT_NAME, EMP_TITLE;
---

Problem 36

SELECT PROD_BASE, PROD_TYPE, COUNT(*) AS NUMPRODUCTS

FROM LGPRODUCT

GROUP BY PROD_BASE, PROD_TYPE

ORDER BY PROD_BASE, PROD_TYPE;

---

Problem 37

SELECT BRAND_ID, SUM(POD_QOH) AS TOTALINVENTORY

FROM LGPRODUCT

GROUP BY BRAND_ID

ORDER BY BRAND_ID DESC;

---

Problem 38

SELECT P.BRAND_ID, BRAND_NAME, ROUND(AVG(PROD_PRICE),2) AS AVGPRICE

FROM LGBRAND AS B,LGPRODUCT AS P

WHERE B.BRAND_ID=P.BRAND_ID

GROUP BY P.BRAND_ID,BRAND_NAME

ORDER BY BRAND_NAME;

---

Problem 40

SELECT E.EMP_NUM, EMP_FNAME, EMP_LNAME, MAX(SAL_AMOUNT) AS LARGESTSALARY
FROM LGEMPLOYEE AS E, LGSALARY_HISTORY AS S

WHERE E.EMP_NUM=S.EMP_NUM AND DEPT_NUM=200
GROUP BY E.EMP_NUM, EMP_FNAME, EMP_LNAME
ORDER BY MAX(SAL_AMOUNT) DESC;

---

Problem 41

SELECT C.CUST_CODE, CUST_FNAME, CUST_LNAME, SUM(INV_TOTAL) AS TOTALINVOICES

FROM LGCUSTOMER AS C, LGINVOICE AS I

WHERE C.CUST_CODE=I.CUST_CODE

GROUP BY C.CUST_CODE, CUST_FNAME, CUST_LNAME

HAVING SUM(INV_TOTAL)>1500

ORDER BY SUM(INV_TOTAL) DESC;

---

Problem 42

SELECT D.DEPT_NUM, DEPT_NAME, DEPT_PHONE, D.EMP_NUM, EMP_LNAME
FROM LGDEPARTMENT AS D, LGEMPLOYEE AS E
WHERE D.EMP_NUM = E.EMP_NUM
ORDER BY DEPT_NAME;

---

ALL THE BEST