테이블이 따로 명시되어 있지 않은 문제는 없이 푸는 문제임.
다음 테이블에서 sql문 시행 결과 값으로 옳은 것은?
(무시*전화번호 저장, null값 저장)
c1 | c2 | c3
-------------------------
A 2050-01-01 1
A 2050-01-02 1
B 2050-01-01 1
B 2050-01-02 1
C 2050-01-01 1
C 2050-01-02 1
null null null
-------------------------
1)SELECT max(c1) FROM t.t1;
-->null
2)SELECT c1,c2,sum(c3) FROM t.t1
-->6
3)SELECT distinct c1,c2 FROM t.t1;
-->
A 2050-01-01
A 2050-01-02
B 2050-01-01
B 2050-01-02
C 2050-01-01
C 2050-01-02
4)SELECT c1, c2 FROM t.t1 order by c1 desc, c2 asc;
-->
C 2050-01-02
C 2050-01-01
B 2050-01-02
B 2050-01-01
A 2050-01-02
A 2050-01-01
===============================================================
(연계문제 테이블은 위와 동일)다음 작동하는 sql 문장은?
a)SELECT c1,c2,count(c3) FROM t.t1 group by c1,c2
b)SELECT c1 FROM t.t1 group by c1 having count(*)>1
c)SELECT c1,c2,avg(c2) FROM t.t1 group by c1,c2 having count(*)>1
d)SELECT c1,c2,avg(c2) FROM t.t1 group by c1,c2 with rollup
1)a
2)a,b
3)a,b,c
4)a,b,c,d
5)all error
===========================================================
다음 staff과 senior engineer만 추출할려 한다. 옳은 문장은?
1)
SELECT `titles`.`emp_no`,
`titles`.`title`,
`titles`.`from_date`,
`titles`.`to_date`
FROM `employees`.`titles`
where `titles`.`title` like in('Staff','Senior Engineer' )
2)
SELECT emp_no,
title,
from_date,
to_date
FROM titles;
where title like 'Staff' or title like 'Senior Engineer'
3)
SELECT emp_no,
title,
from_date,
to_date
FROM titles
where title like 'Staff' or title like 'Senior Engineer'
4)
SELECT emp_no,
title,
from_date,
to_date
FROM titles
where title like 'Staff' or 'Senior Engineer'
=======================================================
다음 실행되는 문장으로 sql문은?(단 sql문은 위에서 아래로(a에서 f로) 실행함.)
CREATE TABLE TBL(
id int primary key,
amt int not null,
degree varchar(1)
)
a)insert into tbl values(1,100)
b)insert into tbl(id, amt, degree) values(2,200,'AB')
c)insert into tbl values(1,100,'C')
d)insert into tbl values(1,25,'Z')
e)insert into tbl values(3,100,'A')
f)insert into tbl (id,amt) values(4,100)
1)c,e,f
2)a,b
3)c,b,e,f
4)a,b,c,d,e,f
5)all error
===================================================================
다음sql이 성공적으로 수행되었을때 아래 문장이 차례로 시행됐을 때 결과값은?
-------------------------------------------------
CREATE TABLE 부서(
부서번호 char(10),
부서명 char(10) primary key
)
CREATE TABLE 직원(
직원번호 char(10), 소속부서 char(10),
primary key(직원번호),
foreign key(소속부서) references 부서(부서번호) on delete cascade);
insert into 부서 values('10','영업과')
insert into 부서 values('20','기획과')
insert into 직원 values('1000','10')
insert into 직원 values('2000','20')
insert into 직원 values('3000','20')
-------------------------------------------------
select count(직원번호) from 직원
DELETE from 부서 where 부서번호 ='20';
select count(직원번호)from 직원 commit
1)3, null
2)3,1
3)3,2
4)3,3
====================================================
다음 sql 중 오류가 발생하는 것은?
1)
select 지역,sum(매출금액) as 매출금액
from 지역별매출
gruop by 지역
order by 매출금액 desc
2)
select 지역,매출금액 as 매출금액
from 지역매출
order by 년 asc
3)
select 지역,sum(매출금액) as 매출금액
from 지역별매출
gruop by 지역
order by 년 desc
4)
select 지역,sum(매출금액) as 매출금액
from 지역별매출
group by지역
having sum(매출금액)>1000
order by count(*) desc
===================================================
다음 실행 결과를 리턴하는 sql문을 작성하시오.
c1 | c2 | c3
-------------------------
A 2050-01-01 1
A 2050-01-02 1
B 2050-01-01 1
B 2050-01-02 1
C 2050-01-01 1
C 2050-01-02 1
-------------------------
▼▼▼▼▼▼▼▼▼▼▼▼▼▼
A 2050-01-01 1
B 2050-01-01 1
C 2050-01-01 1
2050-01-01 3
A 2050-01-02 1
B 2050-01-02 1
C 2050-01-02 1
2050-01-02 3
6
===============================================================================
팀별 포지션 FW,MF,DF,GK 포지션별 인원수와 팀별 전체의 인원수를 구하는 sql일때 결과가 다른 것은?
1)
select team_id,
isnull(sum(case when position ='FW' then 1 end),0) FW,
isnull(sum(case when position ='MF' then 1 end),0) MF,
isnull(sum(case when position ='DF' then 1 end),0) DF,
isnull(sum(case when position ='GK' then 1 end),0) GK,
count(*) sum
from player
group by team_id;
2)
select team_id,
NVL(sum(case when position ='FW' then 1 end),0) FW,
NVL(sum(case when position ='MF' then 1 end),0) MF,
NVL(sum(case when position ='DF' then 1 end),0) DF,
NVL(sum(case when position ='GK' then 1 end),0) GK,
count(*) sum
from player
group by team_id;
3)
select team_id,
NVL(sum(case when position ='FW' then 1 end),0) FW,
NVL(sum(case when position ='MF' then 1 end),0) MF,
NVL(sum(case when position ='DF' then 1 end),0) DF,
NVL(sum(case when position ='GK' then 1 end),0) GK,
count(*) sum
from player
group by team_id;
4)
select team_id,
nvl(sum(case when position ='FW' then 1 else 1 end),0) FW,
nvl(sum(case when position ='MF' then 1 else 1 end),0) MF,
nvl(sum(case when position ='DF' then 1 else 1 end),0) DF,
nvl(sum(case when position ='GK' then 1 else 1 end),0) GK,
count(*) sum
from player
group by team_id;