65 lines
1.7 KiB
Plaintext
65 lines
1.7 KiB
Plaintext
1:
|
|
select *
|
|
from LGBRAND
|
|
order by BRAND_TYPE;
|
|
2:
|
|
select BRAND_TYPE as `Brand Type` ,count(BRAND_TYPE) as `Number of each brand types`
|
|
from LGBRAND
|
|
group by BRAND_TYPE;
|
|
3:
|
|
select CUST_STATE as `State`, count(CUST_BALANCE) as `Number of non zero balances`
|
|
from LGCUSTOMER
|
|
where CUST_BALANCE != 0
|
|
group by CUST_STATE;
|
|
4:
|
|
select CUST_STATE as `State`, count(CUST_BALANCE) as `Number of non zero balances`
|
|
from LGCUSTOMER
|
|
where
|
|
CUST_BALANCE != 0
|
|
group by CUST_STATE
|
|
having
|
|
count(CUST_CODE) > 100;
|
|
5:
|
|
select CUST_STATE as `State`, count(CUST_BALANCE) as `Number of non zero balances`
|
|
from LGCUSTOMER
|
|
where
|
|
CUST_BALANCE != 0
|
|
group by CUST_STATE
|
|
having
|
|
count(CUST_CODE) > 100
|
|
order by STATE desc;
|
|
6:
|
|
select CUST_STATE as `State`, count(CUST_BALANCE) as `Number of non zero balances`
|
|
from LGCUSTOMER
|
|
where
|
|
CUST_BALANCE != 0
|
|
group by CUST_STATE
|
|
having
|
|
count(CUST_CODE) > 100
|
|
order by count(CUST_CODE);
|
|
7:
|
|
select SUM(LINE_NUM) as `Number of Products purchased`, BRAND_TYPE as `Brand`
|
|
from LGCUSTOMER
|
|
natural join LGINVOICE
|
|
natural join LGLINE
|
|
natural join LGPRODUCT
|
|
natural join LGBRAND
|
|
where
|
|
CUST_STATE = "ME"
|
|
group by BRAND_TYPE;
|
|
8:
|
|
select SUM(LINE_NUM) as `Number of Products purchased`, BRAND_TYPE as `Brand`, CUST_STATE as `State`
|
|
from LGCUSTOMER
|
|
natural join LGINVOICE
|
|
natural join LGLINE
|
|
natural join LGPRODUCT
|
|
natural join LGBRAND
|
|
group by BRAND_TYPE, CUST_STATE
|
|
having SUM(LINE_NUM) < 10;
|
|
9:
|
|
select
|
|
MAX(EMP_HIREDATE) as `Hire date`,
|
|
EMP_FNAME as `First Name`,
|
|
EMP_LNAME as `Last Name`,
|
|
DATEDIFF(CURRENT_DATE, MAX(EMP_HIREDATE)) as `Days since hire`
|
|
from LGEMPLOYEE; |