[SQL 기초]오라클_기본함수 + WHERE, 검색, ORDER BY, GROUP BY, HAVING

예시로 쓸 테이블

create table student(
 sno varchar2(3) ,
 sname nvarchar2(10),
 year varchar2(10),
 dept nvarchar2(10) ,
 constraint   student_sno_pk   primary  key(sno));


create table course(
 cno varchar2(5) primary key,
 cname nvarchar2(10),
 credit varchar2(3),
 dept nvarchar2(10),
 prname nvarchar2(10));


create table enrol(
 sno varchar2(3) ,
 cno varchar2(5) ,
 grade varchar2(3),
 midterm int,
 final int ,
 constraint enrol_sno_fk foreign key(sno) references student(sno),
 constraint enrol_cno_fk foreign key(cno) references course(cno),
 constraint enrol_combo_pk primary key(sno, cno));

왼쪽부터 student, course, enrol 테이블

💡 기본함수 + WHERE

함수	설명
distince	중복제거 명령어. 컬럼의 최고 앞에 사용 가능
count(컬럼명)	칼럼의 수를 카운트 하는 기능
nvl(컬럼명,변경값)	칼럼이 null이면 변경값으로 반환
concat	문자열 더하기(2개만 가능)
increment	값을 증가시키는 기능
avg	평균
sum	합
max(컬럼명)	칼럼의 최고값 출력 가능
min(컬럼명)	칼럼의 최소값 출력 기능
round(컬럼명, 소수점 수)	소수점 자리 수에서 반올림
asc	오름차순
desc	내림차순

*칼럼의 출력 명칭 바꾸는 
1)칼럼 + as +"바꿀 명칭"
2)칼럼 + as + 바꿀 명칭
3)칼럼 + "바꿀 명칭"
4)칼럼 + 바꿀 명칭

📌 WHERE = 조건문

SELECT문으로 데이터를 조회할 때 특정 조건을 기준으로 원하는 행을 출력하는데 사용
ex) student 테이블에서 컴퓨터과 4학년을 출력하라

select * from student 
where dept = '컴퓨터' and year = 4;

ex)학과가 null이 아닌 학생을 출력하라

select sname from student where dept is not null;

💡 검색

📌 like = 문자찾기 함수

◾ select * from 테이블명 where 칼럼명 like '%검색어%';

-> student 테이블 sname칼럼에 '영'이 들어가는 사람 찾기

select * from student where sname like '%영%';

-> 마지막에 '영'이 들어가는 사람

select * from student where sname like '%영';

-> 두번째에 '찬'이 들어가는 사람

select * from student where sname like '_찬%';

💡 ORDER BY, GROUP BY, HAVIING

📌 ORDER BY는 검색된 결과를 컬럼을 기준으로 정렬하는데 사용됨.

📌 GROUP BY는 컬럼을 기준으로 집계를 내는데 사용됨.

📌 HAVIING은 Group by로 집계된 값 중 where 절 처럼 특정 조건을 추가할 수 있음.

종합 예시
ex) 학년 수 카운트, null값이면 그 자리에 0을 넣고 카운트

select count(year), count(nvl(year,0)) from student;

ex)학과별 학생수를 구하고 학생수 기준 내림차순으로 출력하라

select dept, count(dept) as "학생수" from student group by dept order by count(dept) desc;

ex) sequence ss1 를 만들고 1001부터 실행할 때마다1씩 증가시켜라

create sequence ss1 INCREMENT by 1 start with 1001;
select ss1.nextval from dual;

ex) 200번 이찬영 으로만 출력 || 파이프라인 사용
->concat은 2개만 붙일 수 있음. ||은 여러개 가능

select sno ||'번'||sname||'학생' as "결과값" from student where sno = '200' and sname = '이찬영';

ex) sno끼리 묶고 중간성적이 85점 이상인 학생 중,
중간성적의 합계, 평균(소수점 1자리까지 출력), 응시수, 최고값, 최소값을 sno를 기준으로 내림차순으로 출력하라

select sno, sum(midterm) "합계", round(avg(midterm),1) "평균" ,
count(midterm) "응시수", max(midterm)"최고값", min(midterm)"최솟값" 
from enrol group by sno having round(avg(midterm),1)>85 order by sno desc;