informix - k1 - sql

//보고서 제출여부 확인
//select * from 'kone'.bbsreport where writer = '20331056';

//주별 강의안 수강 확인
//select * from 'kone'.attend where user_id = '20380184';

//학사디비 view table 정보 확인
//select * from chung.view_student where user_id='20380052';
//select * from chung.view_sugang where user_id='20323097';
//select * from chung.view_student where user_name='김종선';
//select * from chung.view_class where jojik_cd='78';
//select count(*) from chung.view_sugang
//where year='2003' and term='1' and lecture_cd='103642' and (user_id like '203_____') ;
//select count(*) from chung.view_student;


//학생정보 확인
//select * from 'kone'.member where user_id='20380065';
//select * from 'kone'.member where user_name='강선미';

//시험응시 여부 확인 및 정답 확인
//select * from 'kone'.evalapplyanswer where student_id='20307083';
//select * from 'kone'.evaltest where student_id='20380272';
//select * from 'kone'.evalquest_list where lec_cd='103642200654';

//select lec_cd, user_id, sum(attnd_cnt) sum1 from 'kone'.attend
//where lec_cd='103642202567' group by lec_cd, user_id order by user_id;

//select a.user_id, b.user_name, a.lec_cd from 'kone'.attend a, 'kone'.member b
//where a.user_id=b.user_id and b.user_name='정찬교';


//학생들의 핸드폰 번호 뽑아내기
//select user_id, user_name, mobile from member
//where c_usercode = 'S' and user_id in (select user_id from cmember where lec_cd like '103642%')
//order by user_id;

//학과별, 학생들의 핸드폰 번호,이메일 뽑아내기
select c.cd_name, a.user_id, a.user_name, a.mobile, a.email from member a,schoolreg b, codemaster c
where a.c_usercode = 'S' and
user_id in (select user_id from cmember where lec_cd like '103800%' and org_sect='nep01' and year='2003' and term='2' and reg_type='s' )
and a.org_sect=b.org_sect and a.user_id=b.student_id and b.FACULTY_DEPARTMENT_CD=c.minor_cd and c.major_cd='HJ98'
order by c.cd_name, a.user_id;

//장기결석자(3월29일 이후로 수업을 드지 않은 학생 리스트)
//select user_id from cmember
//where org_sect='nep01' and year='2003' and term='1' and reg_type='s' and lec_cd='103642200654'
//and user_id not in (select user_id from attend where org_sect='nep01' and year='2003' and term='1'
//and lec_cd='103642200654' and modify_dt>to_date('20030329','%Y%m%d')  );

//lec_cd='????????' ??????? 부분에 해당 강좌 코드 입력
//장기결석자에 학과, 이름 표시하기  (3월29일 이후로 수업을 듣지 않은 학생 리스트)
//select a.user_id,b.kor_name,c.cd_name from cmember a, schoolreg b, codemaster c
//where a.org_sect='nep01' and a.year='2003' and a.term='1' and a.reg_type='s' and a.lec_cd='103642200654'
//and a.user_id not in (select user_id from attend where org_sect='nep01' and year='2003' and term='1'
//and lec_cd='103642200654' and modify_dt>to_date('20030329','%Y%m%d')  )
//and a.org_sect=b.org_sect and a.user_id=b.student_id and b.FACULTY_DEPARTMENT_CD=c.minor_cd
//and c.major_cd='HJ98' order by c.cd_name,b.kor_name;

//2003.2학기 결석자 뽑아내기
select c.cd_name,a.user_id,b.kor_name from cmember a, schoolreg b, codemaster c
where a.org_sect='nep01' and a.year='2003' and a.term='2' and a.reg_type='s' and a.lec_cd='103800202627'
and a.user_id not in (select user_id from attend where org_sect='nep01' and year='2003' and term='2'
and lec_cd='103800202627' and modify_dt>to_date('20030825','%Y%m%d')  )
and a.org_sect=b.org_sect and a.user_id=b.student_id and b.FACULTY_DEPARTMENT_CD=c.minor_cd
and c.major_cd='HJ98' order by c.cd_name,b.kor_name;

// 100016, 200063, 200177, 200795, 200954, 202095, 202120, 202322, 202626, 202627

// schoolreg는 학적대장 테이블, codemaster는 코드가 정의되어 있는 테이블


//특정 날짜 사이에 쓰여진  전체 게시물 수
//select count(*) from bbswritecnt
//where year='2003' and term='1' and lec_cd like '103642%'
//and room_type != 'D' //토론형
////and room_type != 'P'  //자료실형
//and ( written_dt between to_date('20030609','%Y%m%d') and to_date('20030615','%Y%m%d'));
//

//특정 날짜 사이에 쓰여진  공지사항  게시물 수
select count(*) from lecannoun
where year='2003' and term='1' and lec_cd like '103642%'
and ( modify_dt between to_date('20030609','%Y%m%d') and to_date('20030615','%Y%m%d'));

//해당 학번의 학생이 해당 게시판에 쓴 글 목록 출력
//select * from commonbbs where Lec_Cd='103642100127' and Bbs_Id='96' and User_Id like '%20380089%';

//해당 학점자 수 출력
//select count(*) from testscore where af='B+';

//해당 학생의 학사에 입력된 최종 성적 확인
//select * from testscore where user_id='20305051';

//각 강좌별 성적 확인
//select * from grading where year='2003' and term='1' and lec_cd='103642100127'
//order by user_id;

//각 강좌별 성적리스트 출력
select c.cd_name,a.user_id,b.kor_name,a.attend_conv,a.task_conv,a.midterm_conv,a.final_conv,a.conv_sum,a.rank,a.e_mark
from grading a, schoolreg b, codemaster c
where a.org_sect='nep01' and a.year='2003' and a.term='1' and a.lec_cd='103642100127'
and a.org_sect=b.org_sect and a.user_id=b.student_id and b.FACULTY_DEPARTMENT_CD=c.minor_cd and c.major_cd='HJ98'
order by c.cd_name,a.user_id;

//학사 디비 수강정보 중 학과별 인원수 확인
select a.lecture_jojik_cd,b.jojik_name, count(a.user_id)  from chung.view_sugang a, chung.view_class b
where a.lecture_jojik_cd=b.jojik_cd and a.year='2003' and a.term='2' and a.lecture_cd='103800'
group by a.lecture_jojik_cd, b.jojik_name;
* itislord님에 의해서 게시물 복사되었습니다 (2005-04-24 14:11)