SQL 명령어

데이터 정의문 (테이블 생성문)

테이블 생성, 컬럼명과 타입 정의, 기본키와 외래키 설정

# 테이블 생성하는 방법
# create table 테이블명
create table department
# 컬럼명과 타입 설정
(dname varchar(10),
dean varchar(10),
# 기본키 설정
primary key (dname));

create table student
(sno numeric,
sname varchar(20) not null,
eyear numeric,
dname varchar(10),
primary key (sno), # 기본키는 여기 테이블의 sno으로하고
foreign key (dname) references department # 외래키는 dname으로 하는데 그게 department 테이블에서 옴

take_course에서 학생별 학번(sno)과 각 학생들이 들은 교과목(cno) 개수를 투영한 테이블을 take_course_number 뷰로 정의하시오

create view take_course_number(sno, num_of_cno)
as
select sno, count(cno) as num_of_cno
from take_course
group by sno;

 

Student1 에 저장되어 있던 한 튜플이 삭제될 때 stdent2 테이블에서도 같은 학번(sno)를 갖는 튜플을 삭제하시오.

create trigger sud_del_trigger
before delete on student1 # stente1에서 delete가 일어나면 이벤트로 간주한다
execute delete student2 where sno = :obj.sno; # obj.sno 지워진 튜플을 의미한다.

delete student1
where sno = '1234';

---

데이터 조작문(DML)

Professor의 교수번호(pno)와 교수이름(pname), 연봉(salary), 학과명(dnmae)에 <0001, 송연지, 7000, 컴공학과> 인 튜플을 삽입

insert into professor
values('0001','송연지','7000','컴공학과');

Professor 테이블에서 연봉이 5000만원 미만인 교수들의 연봉을 10% 올리시오.

update professor
set salary = salary * 1.1
where salary < 5000;

검색문

Professor 테이블 교수들의 소속학과 명(dname)들을 모두 나열하시오.

select dname from professor;

# 중복제거
select distint dname from professor;

Student 테이블에서 학과명이 '컴공학과'인 학생들은 제외하고, 그 외 학새들을 학번(sno), 학과(dname), 입학년도(eyear) 순으로 검색하시오.

select *
from student
where dname <> '컴공학과'
order by sno, dname, eyear

교수들의 연봉이 줄어드는 순으로 보여주시오.

select pname, salary
from professor
order by salary desc;

2010년부터 2019년까지 설강된 모든 교과목 번호를 나열하시오.

select cno
from teach_course
where eyear between 2010 and 2019;

테이블 student에서 학번으로 2020년 입학생을 검색하시오.

select *
from student
# 2020뒤에 뭐가나오던지 
where sno like '2020%';

질의 결과값에 문자열을 포함시켜 출력하시오.

select '신입생'||sname '소속학과는' ||dname|| '입니다'
from student
where eyear = 2020;

모든 교수들의 이름을 그들의 소속 학과 및 학과장 이름과 함께 보여주시오

select professor.pname, professor.dname, department.dean
from professor, department
where professor.dnmae = department.dname;

수업을 하는 교수들의 이름과 가르치는 교과목 번호를 알려주시오.

select distint pname cno
from professor, teach_course
where professor.pno = teach_course.pno;

# 자연조인 방법
# 큐그리드에서는 불가
select distint pname, cno
from professor natural-join teach_course;

집성화함수

컴공학과 교수들의 평균 연봉을 알려주시오

select avg(salary)
from professor
where dnmae='컴공학과';

Avg 함수 결과값에 애트리뷰트명을 부여하고 싶을 땐

select avg(salary) as avg_salary
from professor
where dnmae='컴공학과';

교수들의 평균 연봉을 학과별로 보여주시오

select dname, avg(salary) as avg_salary
from professor
group by dnmae;

# pno같은 없는 애트리뷰트 넣으면 에러난다
# 아래는 에러 문구
select dname, pno, avg(salary) as avg_salary
from professor
group by dnmae;

평균 연봉이 6000만원 이상인 학과를 대상으로 학과명과 평균 연봉을

select dname, avg(salary) as acv_salary
from professor
group by dname
having acv_salary > 6000;

2019년도 1학기에 과목을 하나이상 강의한 교수들의 수를 알려달라

select count(distinct pno)
from teach_course
where term= '1' and year = 2018;

teach_course에서 교과목을 강의하지 않은 교수들의 교수번호와 이름을 모두 나열하시오.

select pno, pname
from professor
where pno not in 
(selet pno
from teach_course
where professor.pno = teach_course.pno);

중첩부질의

학생들이 교과목을 하나도 수강하지 않은 교수들의 교수번호와 이름을 나열하시오

select pno, pname
from professor P
where
# Tc는 as Tc 란 뜻
not exists (select * from take_course Tc 
wehre P.pno=Tc.pno);

집합간 비교하기

통계학과 소속이 아닌 교수들 중 통계학과의 누군가 보다 연봉이 높다면 이 교수들의 이름을 모두 나열하시오

select pname
from professor
# some : 이 중 하나라도 있으면 return
where dname not in ('통계학과')and salary > some(select salary from professor where dname='통계학과');

통계학과 소속이 아닌 교수들 중 통계학과의 모든 보다 연봉이 높다면 이 교수들의 이름을 모두 나열하시오

select pname
from professor
# all : 모두가 되어야 return
where dname not in ('통계학과')and salary > all(select salary from professor where dname='통계학과');

from 절 내 부질의 포함하기

만들어낸 결과값을 테이블 취급하기

평균 봉급이 5000만원 이상되어 학과 소속인 교수들의 부서명과 그들의 평균 연봉을 알려주시오.

select dname, avg_salary
from (select dname, avg(salary) as avg_salary
from professor group by dname)
where avg_salary > 5000;

# From 절 내 부질의 에 테이블명 부여하기
select dname, avg_salary
from (select dname, avg(salary) as avg_salary
from professor group by dname) 
as dept_avg(dept_name, avg_salary)
where avg_salary > 5000;

집합 연산자 사용 예

2019년이나 2020년에 강의를 개설한 교수의 이름을 알아보시오

select professor.pname
from professor, teach_course
where teach_course.eyear = 2019 and professor.pno = teach_course.pno
union # 합집합
select professor.pname
from professor, teach_course
where teach_course.eyear = 2020 and professor.pno = teach_course.pno

2019년과 2020년에 강의를 개설한 교수의 이름을 알아보시오

select professor.pname
from professor, teach_course
where teach_course.eyear = 2019 and professor.pno = teach_course.pno
intersect
select professor.pname
from professor, teach_course
where teach_course.eyear = 2020 and professor.pno = teach_course.pno

2020년에는 했는데 2019년에는 강의가 이루어지지 않은 교과목명을 알려주시오.

select course.cname
from course, teach_course
where teach_course.eyear = 2020
and course.cno = teach_course.cno
difference
select course.cname
from course, teach_course
where teach_course.eyear = 2019
and course.cno = teach_course.cno