[DB] 조건별 sql select문(oracle/ANSI 구문)

0930과제

 

1. 차량가액에 2000~3500 사이인 차량의 제조국, 제조사, 모델명, 연식, 가격, 옵션, 입력일을 조회하라

단, 연식의 오름차순으로 정렬했을 때, 위에서 2~5번째 레코드만 출력한다.

이때, 차량가액은 3자리마다 ','를 찍어서 출력하고 입력일은 년, 월만 출력한다.

옵션은 첫 번째 ,까지만 출력하라

 

-- orcle 구문(solution key : equi join)

select cc.country, cc.maker, cm.model, cmo.car_year, to_char(cmo.price,'9,999') car_price, substr(cmo.car_option,1,instr(cmo.car_option,',')) car_option, to_char(cmo.hiredate,'yyyy-mm') car_hiredate

from car_country cc, car_maker cm,

(

select rownum r, model, car_year, price, cc, car_option, car_img, hiredate

from

(

select *

from car_model cmo

where price between 2000 and 3500

order by car_year

)

) cmo

where cc.maker = cm.maker and cm.model = cmo.model and r between 2 and 5;

 

-- ANSI 구문

select cc.country, cc.maker, cm.model, cmo.car_year, to_char(cmo.price,'9,999') car_price, substr(cmo.car_option,1,instr(cmo.car_option,',')) car_option, to_char(cmo.hiredate,'yyyy-mm') car_hiredate

from car_country cc

join car_maker cm

on cc.maker = cm.maker

join

(

select rownum r, model, car_year, price, cc, car_option, car_img, hiredate

from

(

select *

from car_model cmo

where price between 2000 and 3500

order by car_year

)

) cmo

on cm.model = cmo.model

where r between 2 and 5;

 

-----------------------------------------------------------------------------

 

2. 차량의 옵션에 ABS가 있고, 제조국이 국산인 차량의 제조국, 제조사, 모델명, 년식, 가격, 옵션, 차량이미지를 조회하라.

단, 모델명의 오름차순으로 정렬하되 모델명이 같다면 가격의 내림차순으로 정렬하여 출력한다.

차량이미지는 파일의 확장자만 출력한다.

 

-- orcle 구문(solution key : equi join)

select cc.country, cc.maker, cmo.model, cmo.car_year, cmo.price, cmo.car_option, substr(cmo.car_img,instr(cmo.car_img,'.')+1)

from car_country cc, car_maker cm, car_model cmo

where cc.maker = cm.maker and cm.model = cmo.model and instr(cmo.car_option,'ABS') != 0 and cc.country = '국산'

order by cmo.model, price desc;

 

-- ANSI 구문

select cc.country, cc.maker, cmo.model, cmo.car_year, cmo.price, cmo.car_option, substr(cmo.car_img,instr(cmo.car_img,'.')+1)

from car_country cc

join car_maker cm

on cc.maker = cm.maker

join car_model cmo

on cm.model = cmo.model

where instr(cmo.car_option,'ABS') != 0 and cc.country = '국산'

order by cmo.model, price desc;

 

-----------------------------------------------------------------------------

 

3. 제조사가 '현대, 기아, BENZ, AUDI'인 모든 차량의 제조국, 제조사, 모델명, 년식, 가격, 우편번호, 시도, 구군, 동, 번지를 조회하라.

단, 년식의 내림차순으로 정렬하되 년식이 같다면 가격의 오름차순으로 정렬하여 출력한다.

각 컬럼에 데이터가 없으면 문자열인 경우 '데이터 없음'을 숫자인 경우는 '0'을 날짜인 경우는 현재날짜를 출력한다.

출력은 정렬된 데이터의 10에서 20번째 레코드만 출력한다.

차량의 년식과 우편번호의 seq를 조건으로 사용할 것(강제 ㅇㅇ)

 

-- orcle 구문

select *

from

(

select rownum r, country, maker, model, car_year, price, sido, gugun, dong, bunji

from

(

select

cc.country, cm.maker,

nvl(cmo.model, '데이터 없음') model,

nvl(cmo.car_year, '데이터 없음') car_year,

nvl(cmo.price,0) price,

nvl(cmo.sido,'데이터 없음') sido,

nvl(cmo.gugun, '데이터 없음') gugun,

nvl(cmo.dong, '데이터 없음') dong,

nvl(cmo.bunji, '데이터 없음') bunji

from car_country cc, car_maker cm,

(

select *

from car_model cmo, zipcode zc

where cmo.car_year = zc.seq

) cmo

where (cc.maker = cm.maker and cm.model = cmo.model(+)) and cm.maker in ('현대', '기아', 'BENZ', 'AUDI')

order by cmo.car_year desc, cmo.price

)

)

where r between 10 and 20;

 

-- ANSI 구문

select *

from

(

select rownum r, country, maker, model, car_year, price, sido, gugun, dong, bunji

from

(

select

cc.country, cm.maker,

nvl(cmo.model, '데이터 없음') model,

nvl(cmo.car_year, '데이터 없음') car_year,

nvl(cmo.price,0) price,

nvl(cmo.sido,'데이터 없음') sido,

nvl(cmo.gugun, '데이터 없음') gugun,

nvl(cmo.dong, '데이터 없음') dong,

nvl(cmo.bunji, '데이터 없음') bunji

from

(

select *

from car_model cmo

join zipcode zc

on cmo.car_year = zc.seq

) cmo

right join car_maker cm

on cmo.model = cm.model

join car_country cc

on cm.maker = cc.maker

where cm.maker in ('현대', '기아', 'BENZ', 'AUDI')

order by cmo.car_year desc, cmo.price

)

)

where r between 10 and 20;

 

-----------------------------------------------------------------------------

 

4. 각 모델별 보유차량대수, 차량가격의 합, 최고가액, 최저가액, 평균가액을 조회하라.

단, 평균가액은 소수점 이하 두자리 까지만 출력하라.

차량가격의 합은 원단위 절삭하여 출력하라

 

-- oracle 구문

select cm.model model, count(cmo.model) count, nvl(trunc(sum(price),-1),0) sum_price, nvl(max(price),0) max_price, nvl(min(price),0) min_price, nvl(trunc(avg(price),2),0) avg_pricefrom car_maker cm, car_model cmowhere (cm.model = cmo.model(+))group by cm.modelorder by model;

 

-- ANSI 구문

select cm.model model, count(cmo.model) count, nvl(trunc(sum(price),-1),0) sum_price, nvl(max(price),0) max_price, nvl(min(price),0) min_price, nvl(trunc(avg(price),2),0) avg_pricefrom car_maker cmleft join car_model cmoon (cm.model = cmo.model)group by cm.modelorder by model;

 

-----------------------------------------------------------------------------

 

5. 차량 옵션에 '파워핸들'이 있으면서, 년식이 2010년 이하인 모든 차량의 제조국 제조사 모델명, 년석, 가격, 배기량, 입력일을 조회하라

단, 가격의 오름차순으로 정렬하되 가격이 같다먼 배기량의 내림차순으로 정렬하고 배기량이 같다면 년식의 내림 차순으로 정렬한다.

각 컬럼에 데이터가 없는 경우 문제 3번의 처리를 따른다.

출력되는 데이터는 정렬된 데이터에 2~4번째 데이터만 아래의 형식으로 출력한다.

=> 모델명'xx'인 차량의 제조사는 'xx(제조국)'이고, 'yyyy'년식 입니다. 가격은 'xx'로 형성되어 있고,

배기량은 'xx'cc로 매물로 등록된 날은 '입력일xx'입니다.

입력인은 년-월-일만 출력할 것

 

-- oracle 구문

from

(

select

rownum r,

nvl(cc.country,'데이터 없음') country,

nvl(cm.maker, '데이터 없음') maker,

nvl(cmo.model, '데이터 없음') model,

nvl(cmo.car_year, '데이터 없음') car_year,

nvl(cmo.price, 0) price,

nvl(cmo.cc, 0) cc,

nvl(cmo.hiredate, sysdate) hiredate

from car_country cc, car_maker cm,

(

select *

from car_model

where instr(car_option,'파워핸들') != 0 and car_year <= 2010

order by price, cc desc, car_year desc

)cmo

where cc.maker = cm.maker and cm.model = cmo.model(+)

)

where r between 2 and 4;

 

-- ANSI 구문select '모델명' || model || '인 차량의 제조사는 ' || country || '이고, ' || car_year || '년식입니다. 가격은 ' || price || '로 형성되어 있고, 배기량은 ' || cc || 'cc로 매물로 등록된 날은 ' || hiredate || '입니다.' resultfrom( select rownum r, nvl(cc.country,'데이터 없음') country, nvl(cm.maker, '데이터 없음') maker, nvl(cmo.model, '데이터 없음') model, nvl(cmo.car_year, '데이터 없음') car_year, nvl(cmo.price, 0) price, nvl(cmo.cc, 0) cc, nvl(cmo.hiredate, sysdate) hiredate from ( select * from car_model where instr(car_option,'파워핸들') != 0 and car_year <= 2010 order by price, cc desc, car_year desc )cmo right join car_maker cm on cmo.model = cm.model join car_country cc on cm.maker = cc.maker)

 

where r between 2 and 4;
--------------------------------------------------------------------------------
다른 사람이 푼 방법 팁

- instr(car_option,'파워핸들') != 0 => car_option like '%파워핸들%'

- 함수를 사용할 때에는 alias를 달아 준다(나중에 java와의 호환을 위해) 안써줘도 무방한데 그럼 인덱스를 기준으로 해야하기 때문에 모호해짐