2008년 4월 16일 수요일 문제 (SQL 기본 질의어)
주안점 : group by, having, 내장(연산)함수
# 본 문항들은 시험을 위한 것이 아닌 연습을 위한 것이므로 너무 부담 갖지 않으시기를 바랍니다. 풀이후 제출하지 마시고 본인이 지참 정리하여 필요 부분은 블로깅 하십시오. 기본 교재는 정원혁 교재이며 요약은 ppt 자료를 참조합니다.
기본전제) MS SQL 2000에 있는 pubs database를 이용하며 별다른 전제가 없을 경우 titles 테이블을 이용합니다. 다만 12번 부터는 sales 테이블을 기정 테이블로 간주하면 됩니다.
문제1) 난이도(평이) (내장 함수)
Titles 테이블에 있는 품목(도서)에 대한 최고가격과 최저 가격을 동시에 화면에 출력합니다. 이때 별다른 컬럼의 이름은 출력하지 않습니다.
Select max(price), min(price) from titles;
문제2) 난이도(평이) (내장 함수)
Titles 테이블의 도서의 총 갯수를 구하여 화면에 출력합니다. 마찬가지로 별도의 컬럼 이름이 나오지 않습니다.
Select count(*) from titles;
문제3) 난이도(평이) (내장함수)
Titles 테이블에서 advance가 “5000”인 품목의 개수를 구하십시오.
select count(*) from titles
where advance = 5000;
단, 여기서 COUNT(*) 대신에 count(title_id) 이런 식으로 다른 필드를 삽입하여도 동일한 결과를 얻을 수 있습니다.
select count(type) from titles
where advance = 5000;
그리고 유의할 사항은 값을 삽입할 경우 advance = “5000” 혹은 advance = ‘5000’ 이라고 표기하면 에러가 출력됩니다. 왜냐하면 이것의 데이터형(자료형)은 money로써 사실상 정수형(int)이나 다름없기 때문에 따옴표를 생략해야 합니다.
문제4) 난이도(평이) (내장함수)
Royalty가 10~20 사이인 도서의 개수를 화면에 출력하십시오.
select count(*) from titles
where royalty >=10 and royalty <=20;
select count(*) from titles
where royalty between 10 and 20;
위의 두가지 형식으로 답을 적을 수 있습니다. 다만 count(*)를 적을 때는 역시 3번 문제와 같이 다른 필드를 적어도 좋습니다. 물론 비교 연산자에서 ( )를 삽입하여도 무방합니다.
문제5) 난이도(평이) (내장함수)
전체 도서 가격의 평균을 구하십시오.
select avg(price)
from titles;
물론 이것은 이와 같이 표현할 수도 있습니다.
select sum(price) / count(price)
from titles;
문제6) 난이도(중) (내장함수)
select avg(price) from titles;
select sum(price) / count(*) from titles;
위의 두가지의 결과가 다릅니다. 그 이유를 기술(설명)하십시오.
전체적으로 테이블을 검색을 해보면 price에 NULL 값이 대입되어 있는 경우를 보게 됩니다. 이러한 경우가 2가지가 있습니다. 그렇기 때문에 실제로 count(price)와 count(*)가 다른 것을 보게 됩니다. 그래서 결과가 다를 수 밖에 없습니다.
문제7) 난이도(평이) (내장함수)
가격이 기재되어 있지 않은 title 필드(컬럼)의 개수를 출력하십시오.
select count(title) from titles
where price is null;
문제8) 난이도(중) (내장함수)
위의 문제에서 “is” 대신 “=”연산자를 사용할 수 있도록 하려면 조치할 때의 명령문과 쿼리를 적어봅시다.
set ansi_nulls off;
select count(title) from titles
where price = null;
문제9) 난이도(중) (select)
다음 중 나머지와 결과가 다른 것은 무엇일까요?
1) select price from pubs...titles;
2) select price from titles;
3) select price from dbo.titles;
4) select price from pubs.dbo.titles;
1)의 경우는 에러가 납니다. 제대로 실행하려면 select price from pubs..titles; 같이 기입해야 합니다.
문제10) 난이도(평이) (isnull)
가격이 기재되어 있지 않은 항목을 ‘0’으로 채워서 price 컬럼 자리에 화면 출력합니다.
select price= isnull(price, 0) from titles;
문제11) 난이도(평이) (isnull)
ANSI 경고를 표현하지 않도록 셋팅하는 명령을 기입하십시오.
set ANSI_warnings off
###### 다음 문제 부터는 sales 테이블이 기정의 테이블이 됩니다 ######
문제12) 난이도(중) (연산자, 내장함수)
Sales 테이블에서 1994년 1월 1일에서 1994년 12월 30일까지 판매되었던 책의 총 개수를 구하도록 쿼리를 작성하십시오.
select count(*) from sales
where ord_date between '94.1.1' and '94.12.30';
select count(*) from sales
where ord_date >= '1994-01-01' and ord_date<='1994-12-30';
select count(*) from sales
where ord_date between '1994-01-01' and '1994-12-30';
두가지 경우를 사용할 수 있습니다.
물론 이렇게 날짜 형식을 적어도 좋습니다.
select count(*) from sales
where ord_date between '1994/01/01' and '1994/12/30';
select count(*) from sales
where ord_date >= '1994/01/01' and ord_date<='1994/12/30';
문제13) 난이도(중) (group by, 연산함수)
1994년 1년간의 도서판매량이 30권 이상인 것만을 각 ID 별로 조회하고자 합니다. 화면에는 “title_id”, “1994년 판매량”이라는 두가지의 필드로 출력될 수 있도록 조치합니다.
select title_id, sum(qty) as '1994년 판매량'
from sales
where ord_date between '1994/01/01' and '1994/12/30'
group by title_id -- group by all title_id
having sum(qty) >=30;
물론 여기서 날짜를 기입하는 방법은 “/” 대신에 “-“를 기입할 수 있으면 between 대신에 비교 연산자를 사용하실 수 있습니다.
문제14) 난이도(평이) (compute)
Sales 테이블에서 가게 ID, 판매량, 평균을 보고자 합니다. 이것을 가게 ID 각각 결과를 내림차순으로 볼 수 있도록 조치합니다.
select stor_id, qty
from sales
order by stor_id desc
compute avg(qty) by stor_id;
문제15) 난이도(중) (compute)
가게ID, 가게별 판매량을 인쇄하고 화면 마지막에 전체 평균 판매량을 보고자 합니다.
해당 쿼리를 기입합니다.
select stor_id, qty
from sales
compute avg(qty)
마지막에 전체 판매량을 볼 수 있는 것은 compute 를 이용하도록 합니다.
문제16) 난이도(고) (group by, 연산함수)
1993년 상반기 도서 판매량 20권 이상에 한해서 평균을 구하되 각 title ID 별로 평균을 내림차순으로 출력하되, title_id, “1993년 상반기 평균 판매량” 두 항목으로 출력할 수 있도록 조치합니다.
select title_id, avg(qty) as '1993년 상반기 평균 판매량'
from sales
where ord_date between '1993/01/01' and '1993/06/30'
group by title_id
having sum(qty) >=20
order by avg(qty) desc;