Sort the result brand name figure select brand

DATA BASE/WAREHOUS
16:137:538:90
Zhejing Wang
Chapter 7 P41-55

Problem 41.

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 (Figure P7.42).

SELECT lgdepartment.DEPT_NUM, lgdepartment.DEPT_NAME,
lgdepartment.DEPT_PHONE, lgemployee.EMP_NUM, lgemployee.EMP_LNAME FROM lgdepartment, lgemployee WHERE lgdepartment.EMP_NUM =
lgemployee.EMP_NUM
ORDER BY lgdepartment.DEPT_NAME

Problem 44.

Problem 45.

Write a query to display the largest average product price of any brand (Figure P7.45).

SELECT lgbrand.BRAND_ID, lgbrand.BRAND_NAME, lgbrand.BRAND_TYPE,

AVG(lgproduct.PROD_PRICE) AS AVERAGEPRICE
FROM lgbrand, lgproduct WHERE lgbrand.BRAND_ID = lgproduct.BRAND_ID GROUP BY lgbrand.BRAND_ID, lgbrand.BRAND_NAME, lgbrand.BRAND_TYPE HAVING AVG(lgproduct.PROD_PRICE) = (SELECT MAX(AVGPRICE)
FROM (SELECT AVG(PROD_PRICE) AS AVGPRICE FROM lgproduct
GROUP BY BRAND_ID) AS AVGPRICE)

Problem 48.

Write a query to display the current salary for each employee in department 300. Assume that only current employees are kept in the system, and therefore the most current salary for each employee is the entry in the salary history with a NULL end date. Sort the output in descending order by salary amount (Figure P7.48).

Write a query to display the starting salary for each employee. The starting salary would be the entry in the salary history with the oldest salary start date for each employee. Sort the output by employee number (Figure P7.49).

SELECT lgemployee.EMP_NUM, lgemployee.EMP_LNAME, lgemployee.EMP_FNAME, lgsalary_history.SAL_AMOUNT
FROM lgemployee, lgsalary_history WHERE lgemployee.EMP_NUM =
lgsalary_history.EMP_NUM AND lgsalary_history.SAL_FROM = (SELECT
MIN(lgsalary_history.SAL_FROM) FROM lgsalary_history WHERE

SELECT L.INV_NUM, L.LINE_NUM, P.PROD_SKU, P.PROD_DESCRIPT,
LL.INV_NUM, LL.LINE_NUM, PP.PROD_SKU, PP.PROD_DESCRIPT, P.BRAND_ID FROM (lgline AS L INNER JOIN lgproduct AS P ON L.PROD_SKU = P.PROD_SKU) INNER JOIN (lgline AS LL INNER JOIN lgproduct AS PP ON LL.PROD_SKU =

PP.PROD_SKU) ON L.INV_NUM = LL.INV_NUM
WHERE P.BRAND_ID = PP.BRAND_ID AND P.PROD_CATEGORY = 'Sealer' AND PP.PROD_CATEGORY = 'Top Coat'
ORDER BY L.INV_NUM, L.LINE_NUM

Problem 52.

Write a query to display the customer code, first name, and last name of all customers who have had at least one invoice completed by employee 83649 and at least one invoice completed by employee 83677. Sort the output by customer last name and then first name (Figure P7.52).

SELECT lgcustomer.CUST_CODE, CUST_FNAME, CUST_LNAME,CUST_STREET, CUST_CITY, CUST_STATE, CUST_ZIP, INV_DATE, INV_TOTAL
FROM lgcustomer INNER JOIN lginvoice ON lgcustomer.CUST_CODE =
lginvoice.CUST_CODE WHERE lgcustomer.CUST_STATE = 'AL' AND INV_TOTAL = (SELECT MAX(INV_TOTAL) FROM lginvoice AS I2 WHERE I2.CUST_CODE = lgcustomer.CUST_CODE)
UNION SELECT CUST_CODE, CUST_FNAME, CUST_LNAME, CUST_STREET, CUST_CITY, CUST_STATE, CUST_ZIP, NULL, 0
FROM lgcustomer WHERE CUST_STATE = 'AL' AND CUST_CODE NOT IN (SELECT CUST_CODE FROM lginvoice)
ORDER BY CUST_LNAME, CUST_FNAME

Problem 55.

+-----------------+------------+------------+--------------+---------------+
| name | continent | area | population | gdp |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan | Asia | 652230 | 25500100 | 20343000 |
| Albania | Europe | 28748 | 2831741 | 12960000 |
| Algeria | Africa | 2381741 | 37100000 | 188681000 |
| Andorra | Europe | 468 | 78115 | 3712000 |
| Angola | Africa | 1246700 | 20609294 | 100990000 |
+-----------------+------------+------------+--------------+---------------+
A country is big if it has an area of bigger than 3 million square km or a population of more than 25 million.

Write a SQL solution to output big countries' name, population and area.