SQL 예시 4

Create table department
(Name varchar(10),
Dean varchar(10),
primary key (dname));

create table student
(sno numeric,
same varchar(20) not null,
eyear numeric,
name varchar(!0),
primary key (son) # 기본키는 여기 테이블의 sno으로
foreign key (dname) references department # 외래키는 dname으로 ,  그게 department 테이블에서 함

take_course 에서 학생별 sno과 각 학생들이 들은 con 개수를 투영한 테이블을 take_course_number 뷰로 정의하시오.

create view take_course_nu mber(snow, cum_of_cno)
select son, count(con) as num_of_cno
from take_course
group by son;

create trigger sud_del_trigger
before delete on student1
execute delete student2 wheres no = :obj.sno;

delete stuent1
where sno=‘1234’;


DML
insert into professor
values(‘0001’,’송연지’,’7000’,’컴공학과’); -> 튜플임

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

검색문

select name from professor;
select distinct name from professor;

select * from Student 
where name <> ‘컴공학과’
order by son, name, year

select name, salary
from professor
order by salary desc;

select cno
from teach_course
where eyear between 2010 and 2019;

select *
from student
where son like ‘2020%’;

select ‘신입생’ ||sname ‘소속학과’ ||dname|| ‘입니다’
from student
where year =2020;

select professor.pname, professor.dname, department.dean
from professor, department

select distint name cno
from professor, teach_course


집성화 함수
select avg(salary) as avg_salary
from professor
where dname=‘컴공학과’;

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

select name, avg(salary) as acv_salary
from professor
where avg(salary) >= 6000;

select count(distinct pro)
from teach_course
where term=‘1’ and year=2019;

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

중첩부질의
select 교수번호, 이름
from 테이블
where 
not exists (select * from take_course Tc where P.pno=Tc.pno

집합간 비교하기
select name
from professor
where name not in (‘통계학과’) and salary > som(select salary from professor where dname=‘통계학과’);

select name
from professor
where name not in (‘통계학과’) 
and salary > all(select salary from professor where dname=‘통계학과);

from 절 내 부질의 포함하기
만들어낸 결과값을 테이블 취급하기

select 부서명, 그들의 평균연봉
from 테이블
where 평균 봉급이 5000이상

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

집합 연산자 사용

select 교수의 이름
from 
where 

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

select professor.pname
from professor, teach_course
where teach_course.eyear = 2020
	and professor.pno = teach_course.pno
difference
select course.cname
from curse, teach_course
where teach)_course.eyear = 2019
and course.cno = teach_course.cno