10.05.26 DB 수업

문제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')