■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
練習用コンテストの問1
select DISTRICT_NAME as 都道府県名,
TOTAL_AMT as 総人口
from POPULATION
where LVL = 2
order by TOTAL_AMT desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
練習用コンテストの問2
select
a.PF_CODE as 都道府県コード,
b.PF_NAME as 都道府県名,
round(
cast(INP_YES as real)/
(INP_YES+INP_NO+UNIDENTIFIED)*100,1) as 入院率
from HOSPITALIZATION a
join PREFECTURE b
on a.PF_CODE = b.PF_CODE
order by 入院率 desc,a.PF_CODE
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
練習用コンテストの問3
with tmp as(
select
a.PF_CODE as 都道府県コード,
b.PF_NAME as 都道府県名,
max(case SURVEY_YEAR when 2015 then a.TOTAL_AMT end) as "総人口2015年",
max(case SURVEY_YEAR when 2020 then a.TOTAL_AMT end) as "総人口2020年"
from POPU_TRANSITION a
join PREFECTURE b
on a.PF_CODE = b.PF_CODE
group by a.PF_CODE,b.PF_NAME)
select 都道府県コード,都道府県名,"総人口2015年","総人口2020年",
round(
cast("総人口2020年" as real) /
"総人口2015年" * 100 , 0) as 人口増加率
from tmp
where "総人口2015年" < "総人口2020年"
order by 人口増加率 desc,都道府県コード
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
練習用コンテストの問4
select b.AGE_NAME as 年齢階層,
sum(case TIME_CODE when 120 then a.TARGET_POP end) as "5時間未満",
sum(case TIME_CODE when 130 then a.TARGET_POP end) as "5時間以上6時間未満",
sum(case TIME_CODE when 140 then a.TARGET_POP end) as "6時間以上7時間未満",
sum(case TIME_CODE when 150 then a.TARGET_POP end) as "7時間以上8時間未満",
sum(case TIME_CODE when 160 then a.TARGET_POP end) as "8時間以上9時間未満",
sum(case TIME_CODE when 170 then a.TARGET_POP end) as "9時間以上",
sum(case TIME_CODE when 180 then a.TARGET_POP end) as "不詳"
from SLEEP_TIME_DTL a
join AGE_GRP b
on a.AGE_CODE = b.AGE_CODE
group by a.AGE_CODE,b.AGE_NAME
order by a.AGE_CODE
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第1回の問1
select DISTRICT_CODE as 地区コード,
DISTRICT_NAME as 地区名,
TOTAL_AMT as 総人口
from POPULATION
order by TOTAL_AMT desc,DISTRICT_CODE desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第1回の問2
select
a.AGE_CODE as 年齢コード,
b.AGE_NAME as 年齢階層名,
sum(ifnull(SP_TIME_5,0)) as "5時間未満",
sum(ifnull(SP_TIME_6,0)) as "5時間以上6時間未満",
sum(ifnull(SP_TIME_7,0)) as "6時間以上7時間未満",
sum(ifnull(SP_TIME_8,0)) as "7時間以上8時間未満",
sum(ifnull(SP_TIME_9,0)) as "8時間以上9時間未満",
sum(ifnull(SP_TIME_9OVER,0)) as "9時間以上"
from SLEEP_TIME_DTL a
join AGE_GRP b
on b.AGE_CODE = a.AGE_CODE
join PREFECTURE c
on c.PF_CODE = a.PF_CODE
and c.PF_NAME in('北海道','青森県','岩手県','宮城県','福島県')
group by a.AGE_CODE,b.AGE_NAME
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第1回の問3
with tmp as(
select
a.PORT_CODE as 港コード,
c.PORT_NAME as 港名,
max(case a.KIND_CODE when '110' then a.AMT end) as 入国者数,
max(case a.KIND_CODE when '120' then a.AMT end) as 出国者数
from IMMIGRATION a
join GRP b
on b.GROUP_CODE = a.GROUP_CODE
join PORT c
on c.PORT_CODE = a.PORT_CODE
where b.GROUP_NAME = '外国人'
group by a.PORT_CODE,c.PORT_NAME)
select 港コード,港名,入国者数,出国者数,
入国者数-出国者数 as 差分
from tmp
where 差分 > 0
order by 差分 desc,港コード desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第1回の問4
with tmp as(
select
a.PF_CODE as 都道府県コード,
b.PF_NAME as 都道府県名,
c.NATION_NAME as 国名,
a.amt,
Row_Number() over(partition by a.PF_CODE
order by a.amt desc,a.NATION_CODE) as rn
from FOREIGNER a
join PREFECTURE b
on b.PF_CODE = a.PF_CODE
join NATIONALITY c
on c.NATION_CODE = a.NATION_CODE
where c.NATION_CODE != '113')
select 都道府県コード,都道府県名,
max(case rn when 1 then 国名 end) as "1位 国名",
max(case rn when 1 then amt end) as "1位 人数",
max(case rn when 2 then 国名 end) as "2位 国名",
max(case rn when 2 then amt end) as "2位 人数",
max(case rn when 3 then 国名 end) as "3位 国名",
max(case rn when 3 then amt end) as "3位 人数",
sum(amt) as 合計人数
from tmp
group by 都道府県コード,都道府県名
order by 合計人数 desc,都道府県コード
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第2回の問1
update POPULATION set DISTRICT_NAME = '不明'
where DISTRICT_NAME is null or DISTRICT_NAME = ''
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第2回の問2
select DISTRICT_CODE as CODE,
DISTRICT_NAME as NAME,
TOTAL_AMT as TOTAL
from POPULATION
where TOTAL_AMT >= 100000
and instr(DISTRICT_NAME , '東') > 0
order by TOTAL_AMT desc , DISTRICT_CODE
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第2回の問3
with tmp as(
select PF_CODE,
sum(case when CATEGORY_CODE = '120'
and GENDER_CODE in('2','3') then AMT end) as sumval,
sum(case when CATEGORY_CODE = '110'
and GENDER_CODE in('2','3') then AMT end) as totalval
from DRINK_HABITS a
group by PF_CODE)
select PF_CODE as CODE,
(select b.PF_NAME
from PREFECTURE b
where b.PF_CODE = a.PF_CODE) as NAME,
round(cast(sumval as REAL)
/cast(totalval as REAL)
* 100 , 1) as PERCENTAGE
from tmp a
order by PERCENTAGE desc,PF_CODE desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第2回の問4
with kind_tmp(kind_key,kind_name) as(
values(1, '小学校'),
(2, '中学校'),
(3, '高校' ),
(4, '短大' ),
(5, '大学' ),
(6, '大学院'))
select SURVEY_YEAR as SV_YEAR,
(select b.PF_NAME
from PREFECTURE b
where b.PF_CODE = a.PF_CODE) as PREFECTURE,
kind_name as KIND,
sum(case kind_key when 1 then ELEMENTARY
when 2 then MIDDLE
when 3 then HIGH
when 4 then JUNIOR_CLG
when 5 then COLLEGE
when 6 then GRADUATE end)
as AMT
from ENROLLMENT_STATUS a join kind_tmp
where SURVEY_YEAR = 2020
and (kind_key = 1 and ELEMENTARY is not null
or kind_key = 2 and MIDDLE is not null
or kind_key = 3 and HIGH is not null
or kind_key = 4 and JUNIOR_CLG is not null
or kind_key = 5 and COLLEGE is not null
or kind_key = 6 and GRADUATE is not null)
group by PF_CODE,kind_key
order by PF_CODE,kind_key
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第3回の問1
select PF_CODE as CODE,
TOTAL_VALUE as SALES_AMT
from CONVENIENCE
where SURVEY_YEAR = 2019
and KIND_CODE = '100'
order by SALES_AMT DESC,CODE
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第3回の問2
insert into CONVENI_SALE_2018
select SURVEY_YEAR,PF_CODE,TOTAL_VALUE
from CONVENIENCE
where SURVEY_YEAR = 2018
and KIND_CODE = '100'
and TOTAL_VALUE is not null
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第3回の問3
with tmp as(
select CLASS_NAME as CLASS,CLASS_CODE,
PERSON_NAME as PERSON,
amt,
max(amt) over(partition by CLASS_CODE) as maxamt
from HOUSEHOLD
where PERSON_CODE != '1'
and CLASS_CODE != '01')
select CLASS,PERSON,amt as HOUSEHOLDS
from tmp
where amt= maxamt
order by CLASS_CODE
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第3回の問4
with tmp1 as(
select USER_ID,CONTEST_ID,POINT,ENTRY_ID,
datetime(SUBMITTED_AT) as SUBMITTED_AT,
max(case status when 'AC' then datetime(SUBMITTED_AT) end)
over(partition by USER_ID,ENTRY_ID,PROBLEM_ID) as ACTime
from SUBMISSIONS
where ENTRY_ID is not null
and CONTEST_ID = 2),
tmp2 as (
select USER_ID,
max(ACTime) as maxACTime,
sum(case when SUBMITTED_AT < ACTime
then 1 else 0 end) as WRONG_ANS,
sum(POINT) as SumPoint,
(select b.STARTED_AT
from ENTRIES b
where b.CONTEST_ID = a.CONTEST_ID
and b.ENTRY_ID = a.ENTRY_ID
and b.USER_ID = a.USER_ID) as STARTED_AT
from tmp1 a
where ACTime is not null
group by USER_ID,ENTRY_ID
having sum(POINT) > 0),
tmp3 as(
select *,
strftime('%s',maxACTime) - strftime('%s',STARTED_AT) + WRONG_ANS * 300 as EX_TIME
from tmp2
where STARTED_AT is not null)
select rank() over(order by SumPoint desc, EX_TIME) as RANK,
USER_ID,SumPoint as POINT,EX_TIME,WRONG_ANS
from tmp3
order by RANK,WRONG_ANS,USER_ID
●解説
最初にWindow関数のmax関数で、(USER_ID,ENTRY_ID,PROBLEM_ID)ごとの最終AC日時を求める。
これを使い、
ACしてない問題のフィルタと、AC前のWAの件数の集計を行う
後は、0点の人を除外してから、SQLiteの日時型の演算を行う
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第4回の問1
select
EMP_CODE as CODE,
EMP_ENG_NAME as ENG_NAME
from EMP
where EMP_ENG_NAME glob '*[sS]*[sS]*'
order by EMP_CODE
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第4回の問2
delete from EMP
where VALID_FLG != '1'
and EMP_CODE IN(select EMP_CODE from EMP_INVALID)
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第4回の問3
with tmp as(
select TOTAL_VALUE,
Row_Number() over(order by TOTAL_VALUE) as Rank,
count(*) over() as RecordCount
from CONVENIENCE
where SURVEY_YEAR = 2019
and KIND_CODE = '100')
select round(avg(TOTAL_VALUE)) as SA_MEDIAN
from tmp
where abs(2*Rank - RecordCount - 1) <= 1
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第4回の問4
with tmp(REGIST_DATE,WK) as(
values('2022-08-01' , '月'),
('2022-08-02' , '火'),
('2022-08-03' , '水'),
('2022-08-04' , '木'),
('2022-08-05' , '金'),
('2022-08-06' , '土'),
('2022-08-07' , '日'),
('2022-08-08' , '月'),
('2022-08-09' , '火'),
('2022-08-10' , '水'),
('2022-08-11' , '木'),
('2022-08-12' , '金'),
('2022-08-13' , '土'),
('2022-08-14' , '日'),
('2022-08-15' , '月'),
('2022-08-16' , '火'),
('2022-08-17' , '水'),
('2022-08-18' , '木'),
('2022-08-19' , '金'),
('2022-08-20' , '土'),
('2022-08-21' , '日'),
('2022-08-22' , '月'),
('2022-08-23' , '火'),
('2022-08-24' , '水'),
('2022-08-25' , '木'),
('2022-08-26' , '金'),
('2022-08-27' , '土'),
('2022-08-28' , '日'),
('2022-08-29' , '月'),
('2022-08-30' , '火'),
('2022-08-31' , '水'))
select REGIST_DATE,WK,
(select count(*)
from USERS b
where b.VALID_FLG = '1'
and substr(b.CONFIRMED_AT,1,10) = a.REGIST_DATE) as TOTAL
from tmp a
order by REGIST_DATE
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第5回の問1
select
a.ITEM_CODE as ITEM,
a.ITEM_NAME as I_NAME,
b.CL_NAME as C_NAME,
c.SIZE_NAME as S_NAME
from ITEM a, COLOR_TBL b ,SIZE_TBL c
where a.STOCK_MANAGEMENT_TYPE = 1
order by a.ITEM_CODE,b.CL_CODE,c.SIZE_CODE
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第5回の問2
select
a.EMP_CODE as CODE,
a.EMP_LAST_NAME as SURNAME,
a.EMP_FIRST_NAME as NAME
from EMP a
where a.VALID_FLG = '1'
and exists(select 1
from EMP b
where b.VALID_FLG = '1'
and replace(a.EMP_LAST_NAME,' ','')
= replace(b.EMP_LAST_NAME,' ','')
and replace(a.EMP_FIRST_NAME,' ','')
= replace(b.EMP_FIRST_NAME,' ','')
and a.EMP_CODE != b.EMP_CODE)
order by replace(a.EMP_LAST_NAME ,' ',''),
replace(a.EMP_FIRST_NAME,' ',''),a.EMP_CODE
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第5回の問3
with tmp1 as(
select POINT,USER_ID,
avg(POINT) over() as val1,
POINT - avg(POINT) over() as val2
from TEST_RESULTS
where TEST_ID = '100'),
tmp2 as(
select POINT,USER_ID,
avg(val2*val2) over() as val3,
sqrt(avg(val2*val2) over()) as val4,
10 * (POINT-val1) as val5
from tmp1),
tmp3 as(
select POINT,USER_ID,
case when val4 = 0 then 50
else val5/val4 + 50 end as val7
from tmp2)
select USER_ID as USER,
POINT as PT,
round(val7,1) as DEV_VAL
from tmp3
order by DEV_VAL desc,USER_ID
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第5回の問4
with tmp as(
select min(MONTHLY) as StaDay,
date(max(MONTHLY),'+1 months') as EndDay
from MRR_DATA
),
rec(day) as(
select StaDay as day -- 日付区間の開始
from tmp
union all
select date(day , '+1 days')
from rec
where day < (select EndDay from tmp)) -- 日付区間の終了
select a.day as YM,
case when a.day = min(a.day) over()
then 0
else sum(ifnull(b.NEW_MRR,0)
+ifnull(b.EXPANSION_MRR,0)
-ifnull(b.DOWNGRADE_MRR,0)
-ifnull(b.CHURN_MRR,0))
over(order by a.day rows between unbounded preceding
and 1 preceding) end as MRR
from rec a
left join MRR_DATA b
on a.day = b.MONTHLY
where day like '%01' -- 月ごとの初日のみ
order by YM
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第6回の問1
select ITEM_POPULAR_RANK as RANK,
ITEM_CODE as CODE,
ITEM_NAME as NAME
from ITEM
where SALE_END_DATE is null
order by ITEM_POPULAR_RANK,ITEM_CODE
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第6回の問2
select a.PF_CODE as CODE,
b.PF_NAME as NAME,
round(cast(a.PROMOTE_MUNICIPALITY as real) /
a.MUNICIPALITY_TTL * 100,2) as RATIO
from SDGS a
join PREFECTURE b
on b.PF_CODE = a.PF_CODE
where SURVEY_YEAR = 2022
order by RATIO desc,a.PF_CODE
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第6回の問3
insert into DEPARTMENT
select
'B300',
'2023-04-01',
NULL,
'新設部門',
LAYER,
UP_DEPT_CODE,
'1',
UPDATE_USER_NAME,
'2023-04-01 00:00:00'
from DEPARTMENT
where DEPT_CODE = 'A100';
update DEPARTMENT
set END_DATE = '2023-03-31',
VALID_FLG = '0',
UPDATE_USER_NAME = 'ORG_CHG',
USER_UPDATE_DATETIME = '2023-03-31 23:59:59'
where DEPT_CODE IN('A100','A200');
update EMP
set DEPT_CODE ='B300',
START_DATE = '2023-04-01',
UPDATE_USER_NAME = 'ORG_CHG',
USER_UPDATE_DATETIME = '2023-04-01 00:00:00'
where DEPT_CODE IN('A100','A200')
and VALID_FLG = '1';
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第6回の問4
select
a.DISTRICT_CODE as CODE,
b.DISTRICT_NAME as NAME,
a.LATITUDE as LAT,
a.LONGITUDE as LON
from LOCATION_TBL a
join DISTRICT b
on a.DISTRICT_CODE = b.DISTRICT_CODE
where a.DISTRICT_CODE != '1101'
order by (select (a.LATITUDE-c.LATITUDE)
*(a.LATITUDE-c.LATITUDE)
+(a.LONGITUDE-c.LONGITUDE)
*(a.LONGITUDE-c.LONGITUDE)
from LOCATION_TBL c
where c.DISTRICT_CODE = '1101') desc,CODE
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第8回の問1
select MEMBER_ID as ID,
SCORE1 - SCORE2 as DIFF
from CONTEST_RESULTS
where abs(SCORE1 - SCORE2) >= 20
order by DIFF desc,MEMBER_ID desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第8回の問2
with tmp as(
select CUST_CODE as CODE,
CUST_NAME as NAME,
(select count(distinct b.ORDER_DATE)
from ORDERS b
where b.CUST_CODE = a.CUST_CODE
and b.ORDER_DATE between '2023-07-01' and '2023-07-31') as CNT
from CUSTOMER a)
select * from tmp
where cnt > 0
order by CNT desc,CODE desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第8回の問3
with tmp as(
select PF_CODE,CATEGORY_CODE,GENDER_CODE,AGE,AVERAGE_VALUE,
ROUND(avg(AVERAGE_VALUE)
over(partition by CATEGORY_CODE,GENDER_CODE,AGE)
,1) as win_avg
from SCHOOL_HEALTH
where SURVEY_YEAR = 2019),
tmp2 as(
select AGE,CATEGORY_CODE,GENDER_CODE,
avg(AVERAGE_VALUE) as avg1,
cast(
sum(case when AVERAGE_VALUE >= win_avg
then 1 else 0 end)
as real) / count(*) as avg2
from tmp
group by AGE,CATEGORY_CODE,GENDER_CODE),
tmp3 as(
select AGE,GENDER_CODE,
case GENDER_CODE when 20 then 'MALE'
when 30 then 'FEMALE' end as GENDER,
max(case CATEGORY_CODE when 10 then avg1 end) as H_AVG,
max(case CATEGORY_CODE when 10 then avg2 end) as H_PER,
max(case CATEGORY_CODE when 20 then avg1 end) as W_AVG,
max(case CATEGORY_CODE when 20 then avg2 end) as W_PER
from tmp2
group by AGE,GENDER_CODE)
select AGE,GENDER,
ROUND(H_AVG,1) as H_AVG,
ROUND(H_PER*100,1) || '%' as H_PER,
ROUND(W_AVG,1) as W_AVG,
ROUND(W_PER*100,1) || '%' as W_PER
from tmp3
order by AGE desc,GENDER_CODE desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第8回の問4
with tmp as(
select a.item_code,
max(b.order_date) as max_order_date,
sum(a.ORDER_QTY) as sum_order_qty
from ORDERS_DTL a
join ORDERS b
on a.order_no = b.order_no
where b.ORDER_DATE between '2023-04-01' and '2023-06-30'
group by a.item_code),
tmp2 as(
select a.item_code,
case when b.item_code is null then 0
else Row_Number() over(order by sum_order_qty desc,max_order_date desc,a.item_code desc)
end as rn
from ITEM a
left join tmp b
on a.item_code = b.item_code)
update ITEM
set ITEM_POPULAR_RANK = (select rn
from tmp2
where tmp2.item_code = ITEM.item_code)
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第9回の問1
select EMP_CODE as CODE,
UPPER(EMP_ENG_NAME) as ENG_NAME
from EMP
order by EMP_CODE desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第9回の問2
with tmp as(
select LOGIN_DATETIME,MEMBER_CODE,
min(substr(LOGIN_DATETIME,1,10)) over(partition by MEMBER_CODE) as MIN_LOGIN_DATETIME
from ACCESS_LOG)
select MIN_LOGIN_DATETIME as FIRST_LOGIN,
count(distinct MEMBER_CODE) as MEMBER_CNT
from tmp
where substr(LOGIN_DATETIME,1,10) = MIN_LOGIN_DATETIME
and MIN_LOGIN_DATETIME between '2023-08-01' and '2023-08-31'
group by MIN_LOGIN_DATETIME
order by FIRST_LOGIN desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第9回の問3
with tmp as(
select MEMBER_CODE,ORDER_DATETIME,
lag(ORDER_DATETIME)
over(partition by MEMBER_CODE
order by ORDER_DATETIME) as LAG_ORDER_DATETIME
from EC_ORDERS),
tmp2 as(
select MEMBER_CODE,ORDER_DATETIME
from tmp
where date(substr(LAG_ORDER_DATETIME,1,10),'+12 month') < substr(ORDER_DATETIME,1,10)
and substr(ORDER_DATETIME,1,10) between '2023-07-01' and '2023-07-31')
select b.ORDER_DATETIME as ORDER_DATE,
a.MEMBER_CODE as CODE,
case OPTOUT_TYPE when 0 then '可'
when 1 then '不可'
else '不明' end as OPTOUT
from MEMBER_MST a
join tmp2 b
on a.MEMBER_CODE = b.MEMBER_CODE
order by ORDER_DATETIME desc,CODE desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第9回の問4
with tmp as(
select PF_CODE,
sum(case KIND_CODE when 100 then TOTAL_VALUE else 0 end) as sum1,
sum(case KIND_CODE when 150 then TOTAL_VALUE else 0 end) as sum2
from CONVENIENCE
where SURVEY_YEAR = 2019
group by PF_CODE),
tmp2 as(
select PF_CODE,sum1,sum2,
Row_Number() over(order by sum1 desc,sum2,PF_CODE) as rn,
sum(sum1) over() as sum3
from tmp)
select Row_Number() over(order by (rn-1) / 3) as NO,
sum(sum1) as TTL_SAL,
round(cast(sum(sum1) as REAL)
/cast(sum3 as REAL)
* 100 , 1) as PER_SAL,
round(cast(sum(sum(sum1)) over(order by (rn-1) / 3) as REAL)
/cast(sum3 as REAL)
* 100 , 1) as CUM_SAL,
sum(sum1)/sum(sum2) as AVG_SAL
from tmp2
where rn <= 30
group by (rn-1) / 3,sum3
order by NO
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第10回の問1
select EMP_CODE as CODE,
'''' || TRIM(EMP_KANA_NAME,'_') || '''' as KANA_NAME
from emp
order by EMP_CODE desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第10回の問2
with tmp as(
select a.CUST_CODE as CODE,
a.CUST_NAME as NAME,
count(*) as CNT,
sum(ORDER_AMNT) as TTL_AMT,
round(avg(ORDER_AMNT),0) as AVG_AMT,
Row_Number() over(order by count(*) desc,
round(avg(ORDER_AMNT),0) desc,a.cust_code) as rn
from CUSTOMER a Join
ORDERS b
on a.cust_code = b.cust_code
where ORDER_DATE between '2023-09-01' AND '2023-09-30'
group by a.cust_code/*,a.emp_code*/
having count(*) >= 5)
select CODE,NAME,CNT,TTL_AMT,AVG_AMT
from tmp
where rn <= 5
order by rn;
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第10回の問3
with tmp as(
select b.SURVEY_YEAR,a.AREA_CODE as CODE,
a.AREA_NAME as NAME,
round(
cast(b.FOOD_EXP as real)/
cast(b.CONSUMPTION_EXP as real) * 100,1) as RATIO,
dense_rank() over(partition by SURVEY_YEAR
order by round(
cast(b.FOOD_EXP as real)/
cast(b.CONSUMPTION_EXP as real) * 100,1)) as rn
from AREA a
Join HOUSEHOLD_SURVEY b
on a.AREA_CODE = b.AREA_CODE
where b.SURVEY_YEAR in(2022,2017,2012))
select CODE,NAME,
max(case SURVEY_YEAR when 2022 then rn end) as "2022_RANK",
max(case SURVEY_YEAR when 2022 then RATIO end) || '%' as "2022_RATIO",
max(case SURVEY_YEAR when 2017 then rn end) as "2017_RANK",
max(case SURVEY_YEAR when 2017 then RATIO end) || '%' as "2017_RATIO",
max(case SURVEY_YEAR when 2012 then rn end) as "2012_RANK",
max(case SURVEY_YEAR when 2012 then RATIO end) || '%' as "2012_RATIO"
from tmp
group by CODE,NAME
order by "2022_RANK",CODE desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第10回の問4
with tmp as(
select SESSION_ID,PROCESS_ID,
case PROCESS_ID when 'STEP1' then 1
when 'STEP2' then 2
when 'STEP3' then 3
when 'STEP4' then 4
when 'STEP5' then 5 end as Step,
Row_Number() over(partition by SESSION_ID order by EX_TIMESTAMP) as rn
from PROCESS_LOG),
rec(SESSION_ID,PROCESS_ID,Step,rn) as(
select SESSION_ID,PROCESS_ID,Step,rn
from tmp
where Step = 1 and rn = 1
union all
select b.SESSION_ID,b.PROCESS_ID,b.Step,b.rn
from rec a
Join tmp b
on b.SESSION_ID = a.SESSION_ID
and b.Step = a.Step+1
and b.rn = a.rn+1)
select 'STEP1' as PROCESS,(select count(*) from rec where PROCESS_ID = 'STEP1') as CNT union all
select 'STEP2' as PROCESS,(select count(*) from rec where PROCESS_ID = 'STEP2') as CNT union all
select 'STEP3' as PROCESS,(select count(*) from rec where PROCESS_ID = 'STEP3') as CNT union all
select 'STEP4' as PROCESS,(select count(*) from rec where PROCESS_ID = 'STEP4') as CNT union all
select 'STEP5' as PROCESS,(select count(*) from rec where PROCESS_ID = 'STEP5') as CNT
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第11回の問2
select MEMBER_CODE as MEMBER,
EMAIL,
replace(substr(EMAIL,instr(EMAIL,'@') + 1) ,
'.co.jp','') as DOMAIN
from MEMBER_MST
order by DOMAIN desc, MEMBER desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第12回の問1
insert into DEPARTMENT(DEPT_CODE,START_DATE,END_DATE,DEPT_NAME,LAYER,
UP_DEPT_CODE,VALID_FLG,UPDATE_USER_NAME,USER_UPDATE_DATETIME)
select '1100','2024-04-01',NULL,'営業1課',2,'1000','1','TANAKA','2024-04-01 00:00:00' union
select '2200','2024-04-10',NULL,'製造2課',2,'2000','1','YAMADA','2024-04-10 00:00:00'
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第12回の問2
select EMP_CODE as CODE,
EMP_LAST_NAME || EMP_FIRST_NAME as NAME,
EMP_ENG_NAME as ENG_NAME,
case when EMP_ENG_NAME is null then '未入力'
else substr(replace(EMP_ENG_NAME,' ',''),1,10) end as CUTOUT_STR
from emp
order by CUTOUT_STR desc,code desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第12回の問3
select a.ITEM_CODE as CODE,a.ITEM_NAME as NAME,'UPDATED' as COMP_RSLT
from ITEM a Join ITEM_HISTORY b
on a.ITEM_CODE = b.ITEM_CODE
and a.USER_UPDATE_DATETIME != b.USER_UPDATE_DATETIME
union all
select a.ITEM_CODE as code,a.ITEM_NAME as name,'ADDED' as COMP_RSLT
from ITEM a Left Join ITEM_HISTORY b
on a.ITEM_CODE = b.ITEM_CODE
where b.ITEM_CODE is null
union all
select b.ITEM_CODE as code,b.ITEM_NAME as name,'DELETED' as COMP_RSLT
from ITEM a Right Join ITEM_HISTORY b
on a.ITEM_CODE = b.ITEM_CODE
where a.ITEM_CODE is null
order by 1 desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第12回の問4
with tmp(REGIST_DATE,WK) as(
values('2024-03-01' , '金'),
('2024-03-02' , '土'),
('2024-03-03' , '日'),
('2024-03-04' , '月'),
('2024-03-05' , '火'),
('2024-03-06' , '水'),
('2024-03-07' , '木'),
('2024-03-08' , '金'),
('2024-03-09' , '土'),
('2024-03-10' , '日'),
('2024-03-11' , '月'),
('2024-03-12' , '火'),
('2024-03-13' , '水'),
('2024-03-14' , '木'),
('2024-03-15' , '金'),
('2024-03-16' , '土'),
('2024-03-17' , '日'),
('2024-03-18' , '月'),
('2024-03-19' , '火'),
('2024-03-20' , '水'),
('2024-03-21' , '木'),
('2024-03-22' , '金'),
('2024-03-23' , '土'),
('2024-03-24' , '日'),
('2024-03-25' , '月'),
('2024-03-26' , '火'),
('2024-03-27' , '水'),
('2024-03-28' , '木')),
tmp2 as(
select WK,REGIST_DATE,
count(b.SALES_NO) as cnt,coalesce(sum(b.SALES_AMT),0) as sumVal
from tmp a Left Join SALES b
on a.REGIST_DATE = b.SALES_DATE
and (case when SALES_TYPE = 2
or UPDATED_NO is not null
then 1 else 0 end) = 0
group by WK,REGIST_DATE),
tmp3 as (
select WK as WEEK,
round(avg(cnt)) as AVG_CNT,
replace(round(avg(sumVal)),'.0','') as xxx
from tmp2
group by WK)
select WEEK,AVG_CNT,
case when length(xxx) > 6
then substr(xxx,1,length(xxx)-6) || ','
|| substr(xxx,length(xxx)-5,3) || ','
|| substr(xxx,length(xxx)-2,3)
when length(xxx) > 3
then substr(xxx,1,length(xxx)-3) || ','
|| substr(xxx,length(xxx)-2,3)
else xxx end || '円' AS AVG_AMT
from tmp3
order by case WEEK when '日' then 0
when '月' then 1
when '火' then 2
when '水' then 3
when '木' then 4
when '金' then 5
when '土' then 6 end
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
初級編第1回の問2
select CUST_CODE as CODE,
CUST_NAME as NAME,
CUST_USER_NAME as USER_NAME
from CUSTOMER
where CUST_NAME like '株式会社%'
or CUST_NAME like '%商事%'
order by CODE desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
初級編第1回の問3
select a.SALES_DATE as SAL_DATE,
a.SALES_NO as NO,
b.ITEM_CODE as ITEM_CODE,
b.SALES_QTY as SAL_QTY,
b.DELIVERED_QTY as DEL_QTY,
case when b.DELIVERED_QTY = 0 then '未出荷'
when b.SALES_QTY > b.DELIVERED_QTY then '一部出荷'
when b.SALES_QTY = b.DELIVERED_QTY then '出荷済'
end as SHIP_STS
from SALES a join SALES_DTL b
on a.SALES_NO = b.SALES_NO
where a.SALES_DATE between '2024-04-01' and '2024-04-10'
order by a.SALES_DATE desc,
a.SALES_NO asc,b.ITEM_CODE asc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
初級編第1回の問4
with tmp as(
select WH_CODE
from WAREHOUSE
where WH_NAME = '浦和倉庫')
select b.ITEM_CODE as CODE,
(select c.item_name
from ITEM C
where c.ITEM_CODE = b.ITEM_CODE) as NAME,
sum(b.ORDER_QTY) as SUM_QTY
from ORDERS a Left Join ORDERS_DTL b
on a.ORDER_NO = b.ORDER_NO
where a.WH_CODE in(select WH_CODE from tmp)
group by b.ITEM_CODE
having sum(b.ORDER_QTY) >= 50
order by SUM_QTY desc,CODE desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第13回の問1
select
MEMBER_CODE as CODE,
LAST_NAME as L_NAME,
FIRST_NAME as F_NAME
from MEMBER_MST
where LAST_NAME like '_田%'
order by MEMBER_CODE desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第13回の問2
select a.ITEM_CODE as CODE,
case when b.ITEM_CODE is null
then a.ITEM_NAME
when a.ITEM_NAME = b.ITEM_NAME
then null
else a.ITEM_NAME end as NAME
from ITEM a left join ITEM_HISTORY b
on a.ITEM_CODE = b.ITEM_CODE
order by a.ITEM_CODE desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第13回の問3
with tmp as(
select LINE_ID,
SEAT_NO,
Lead(SEAT_NO) over(partition by LINE_ID order by SEAT_NO) as Lead1,
Lead(SEAT_NO,2) over(partition by LINE_ID order by SEAT_NO) as Lead2
from SEAT_RESERVE
where RSV_STATUS = '0')
select LINE_ID as LINE,
SEAT_NO as SEAT_F,
Lead2 as SEAT_T
from tmp
where Lead1 = SEAT_NO+1
and Lead2 = SEAT_NO+2
order by LINE_ID desc,SEAT_NO asc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
第13回の問4
with tmp1 as(
select MEMBER_CODE,WEIGHT,CHECKUP_DATE,
WEIGHT*4294836225-
Lead(WEIGHT)
over(partition by MEMBER_CODE order by CHECKUP_DATE desc)*4294836225 as CHG_WT,
Row_Number()
over(partition by MEMBER_CODE order by CHECKUP_DATE desc) as rn
from HEALTH_CHECKUP)
select CHECKUP_DATE as CK_DATE,
MEMBER_CODE as CODE,
(select c.LAST_NAME || c.FIRST_NAME
from MEMBER_MST c
where c.MEMBER_CODE = b.MEMBER_CODE) as NAME,
CHG_WT/4294836225 as CHG_WT
from tmp1 b
where rn = 1
and (CHG_WT >= 5*4294836225 or -5*4294836225 >= CHG_WT)
order by CHG_WT desc,MEMBER_CODE desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
初級編第2回の問2
select ORDER_NO as OD_NO,
SLIP_COMMENT as COMMENT,
length(SLIP_COMMENT) -
length(replace(SLIP_COMMENT , ',' ,'')) + 1 as CNT
from ORDERS
order by CNT DESC , OD_NO desc
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
初級編第2回の問3
delete from ORDERS_DTL
where ORDER_NO
in (select ORDER_NO from ORDERS
where ORDER_DATE between '2010-01-01' and '2010-12-31');
delete from ORDERS
where ORDER_DATE between '2010-01-01' and '2010-12-31';
■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■■
初級編第2回の問4
with tmp as(
select GOODS_CODE,round(avg(STD_COST_PRICE)) as avgval
from SKU
group by GOODS_CODE)
update SKU as a
set UPDATE_USER_NAME = 'MODIFY',
USER_UPDATE_DATETIME = '2024-08-31 00:00:00',
STD_COST_PRICE = (select c.STD_COST_PRICE
from GOODS c
where c.GOODS_CODE = a.GOODS_CODE)
from tmp as b
where a.GOODS_CODE = b.GOODS_CODE
and a.STD_COST_PRICE < b.avgval