카테고리 없음

db실습1

bo97037 2014. 12. 17. 16:28

select deptno1,count(studno) from student s where S.DEPTNO1=101 group by deptno1;

select  count(profno) from professor p where P.DEPTNO=202;

 

select deptno
, dname
,(select count(STUDENT.STUDNO) from student where DEPARTMENT.DEPTNO = student.DEPTNO1 )
from department
;

select deptno
, dname
,(select count(PROFESSOR.PROFNO) from professor where DEPARTMENT.DEPTNO = professor.DEPTNO )
from department
;

drop table dept_stats;


create table dept_stats(deptno varchar(10),dname varchar2(30),student_count number,professor_count number);

select * from dept_stats;

insert into dept_stats(deptno, dname, student_count,professor_count) select department.deptno, DEPARTMENT.DNAME,
(select count(STUDENT.STUDNO) from student where DEPARTMENT.DEPTNO = STUDENT.DEPTNO1),null from department;

update dept_stats
set professor_count =(
select count(profno)
from professor
where dept_stats.DEPTNO=PROFESSOR.DEPTNO
)