1 / 11

제 2 장 SQL 활용

제 2 장 SQL 활용. 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 student values(10, ‘ Kim', 40, 170, 68); insert into student values(20, ‘ Lee', 30, 175, 74);

mahlah
Télécharger la présentation

제 2 장 SQL 활용

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  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으로 바뀜

More Related