10.05.26 DB 수업

Study/Oracle 2011. 9. 3. 23:43
문제3.고객테이블에서 거리(cs)에서 'Main'이라는 포함한 고객명찾기

select cnm
from customer
where cs like '%Main%';

---------------------------------------------------------------
문제1.대출에서 지점명이 Perryridge에서 대출이 1200넘는 얘들 찾기
select lnr

from loan
where bnm =  'Perryridge' and amount>1200;
---------------------------------------------------------------
update enrol
set grade='f', final=final-50
where sno = 20063759 and cno = 'c413';
내점수 바꾸기
= c413이라는 과목번호의(데이터베이스) 20063759 학생의 grade 및 final 점수

바꾸기


select*
from enrol;
---------------------------------------------------------------
update student
set year=1
where sno=20063759;
내꺼 3학년에서 1학년바꾸기

select *
from student;
바꾼거 확인하기
---------------------------------------------------------------

우영운

교수가 가르치는 과목을 수강하는 학생의 이름과 학과 검색

select sname,s.dept
from student s, enrol e, course c
where s.sno=e.sno and e.cno=c.cno and c.prname='우영운';


화일구조를 듣는 학생의 이름,입력 검색
select s.sname, e.grade
from student s, course c, enrol e
where s.sno=e.sno and c.cno=e.cno and cname='화일구조';

기말점수가 90이상인 과목의 이름과 수강 학생이름, 기말점수 검색

select s.sname, e.grade, e.final
from student s, course c, enrol e
where s.sno=e.sno and c.cno=e.cno and e.final >=90
order by cname, sname desc


과목별 평균 점수

select cno, avg(final) as 과목별평균
from enrol
group by cno

과목별 수강인원

select cno, count(sno)
from enrol
group by cno;


3명이상 듣는 과목만 출력


select cno, count(sno) 또는(*) // 테이블에 이름넣기 as '수강인원'
from enrol
group by cno
having count(*)  >=3

90이상 과목 출력

----------------------------------------------------------------

-----------------------------------------
select cno, count(sno)
from enrol
group by cno;


select cno, count(sno)
from enrol
group by cno
having count(*)  >=3

 

select cno, count(sno), avg(final) as 평균
from enrol
group by cno
having count(*)  >=3  and avg(final)  >=90;


select Sname
from student
where sno in ( select sno
  from enrol
  where cno='c413');


select Sname
from student
where sno not in ( select sno
  from enrol
  where cno='c413');

select sno, cno
from enrol
where final> =  all(select final
  from enrol
  where sno = 300);

select Cno, Cname
from course
where Cno like 'c%';

select dept
from course
where dept like '전자%';

select sname
from student
where sname like '이%';

select cno
from course
where cno like 'C3________';

select sname, dept
from student
where sno not in (select sno
  from enrol
  where cno = 'c324')

select sname, dept
from student
where sno  in (select sno
  from enrol
  where cno = 'c324')

'Study > Oracle' 카테고리의 다른 글

10.05.26 DB 실습  (0) 2011.09.03
10.05.04 DB 실습  (0) 2011.09.03
10.04.28 DB 실습  (0) 2011.09.03
10.06.08 DB 수업  (0) 2011.09.03
10.06.01 DB 수업  (0) 2011.09.03
Posted by 코딩하는 야구쟁이
,