제 2 장 SQL 활용

1. 제 2 장 SQL 활용

2. SQL 활용 예제(1) create table student ( id int not null, name char(20) not null, age int, height int, weight float, primary key(id) ); insert into studentvalues(10, ‘Kim', 40, 170, 68); insert into studentvalues(20, ‘Lee', 30, 175, 74); insert into student values(30, 'Park', 40, 165, 58); insert into student values(40, 'Choi', 50, 184, 87); select * from student;

3. SQL 활용 예제(1) insert into student values(40, 'Chung', 50, 154, null); //error insert into student values(50, 20, ‘Chung', 154, null); //error insert into student values(50, 'Chung', 30, 154, null); insert into student values(null, 'Lim', 50, 162, null); //error select * from student;

4. SQL 활용 예제(1) select name, age from student where height >= 170 and weight <= 80; select max(height), avg(height), min(height) from student; select age, avg(height), avg(weight) from student group by age;

5. SQL 활용 예제(1) update studentset age = 45, height = 165 where id = 30; delete from student where age <= 40; delete from student; select * from student; drop table student; select * from student;

6. SQL 활용 예제(2) create table dept ( dno int not null, dname char(20) not null, mgreno int, mgrstartdate char(10), primary key(dno) ) ; create table emp ( eno int not null, ename char(20) not null, sex char(1), salary int, dno int, primary key(eno), foreign key(dno) references dept(dno) );

7. SQL 활용 예제(2) select ename, salary, dno from emp select ename, salary from emp where salary >=20000 select ename, salary from emp where salary >=20000 and dno = 2 select dno from dept where dname = 'Research'

8. SQL 활용 예제(2) select dname, ename from emp, dept where mgreno = eno select ename, dname from emp, dept where emp.dno = dept.dno and dname='Research‘ select ename, salary from emp where dno in (select dno from dept where dname = 'Research') select ename, salary from emp where exists (select * from dept where dno = emp.dno and dname = 'Research')

9. SQL 활용 예제(2) update emp set dno = 5 where eno = 100; //참조 무결성 위반 delete from dept where dno = 1; //참조 무결성 위반 update dept set dno = 6 where dno = 1; //참조 무결성 위반

10. SQL 활용 예제(2) create table emp ( eno int not null, ename char(20) not null, sex char(1), salary int, dno int, primary key(eno), foreign key(dno) references dept(dno) on delete set null on update cascade);

11. SQL 활용 예제(2) update emp set dno = 5 where eno = 100; //참조 무결성 위반 delete from dept where dno = 1; //emp의 dno가 1이 null 바뀜 update dept set dno = 6 where dno = 1; // emp의 dno가 1이 6으로 바뀜