SQL 쿼리문 정리 - BASIC

[INTRO]

DB

- how to search and store large data

- not stored in main memory (not file system)

 

Data scientist

- Data Analyst : SQL, phython을 사용하여 분석하는 역할

- Data Scientist : develop algorithm or model 즉 query 문의 알고리즘을 제작하는 역할

 

File system의 문제점

- Difficulty in accessing data : 데이터에 접근이 불편하다

- Data isolation and separation (multiple file) : insert, delete 시 정보접근이 불편하며 불정확하다

- Integrity problems

- Atomicity of updates : 중요한 연산들이 update 시 atomic 하게 실행안되는 에러발생여부

- Concurrent access by multiple users : 여러명이 동시에 접근시 에러발생

- Security problems : 보안문제

 

DB abstrictions

- Physical level : 실제 memory에 저장되는 형태

- Logical level : DB에 데이터를 어떻게 저장할 지 구조를 짜는 부분 (table) : 개발자들이 고려하면 되는 부분

- View level : 저장되어있는 DB 내용을 보여주는 부분

 

Schema

- DB의 logical structure를 의미, schema를 통해 relation이 생성된다

  ex) instructor(ID, name, dept_name, salary)

- Instance : 실제 DB에 저장되어 있는 내용

 

Physical Data Independence

- Phisical level 과 Logical level은 서로 independent 관계이므로 개발자는 logical structure만 고민하면 된다

 

Data model

- Relation Model : relation들로 이루어진 형태

- Entity Relationship Model : entity, relationship으로 이루어진 형태

- NoSQL : sql 쿼리문이 없는 형태

 

Model 1 : Relation Model

- table, 즉 relation이 저장되어 있는 형태

- attribute : relation의 key 부분

- tupples : 각 key값을 지닌 하나의 Data (행)

 

Key

- 다른 tupple 들과 구별이 되는 attribute 값들의 부분집합

- Candidate key (후보키) : Primary key가 될 수 있는 key들 중 minimal한 key, 즉 구별이 가능한 attribute 값들

- Super key : Candidate key들의 조합으로 이루어진 부분집합으로, 구별이 가능한 집합 (not minimal)

- Primary Key (기본키) : Candidate key 중에서 선택된 key로, 구별가능한 attribute 값

- PK -> CK -> SK 방향이 성립된다

 

SQL

- Structured Query Language

- non procedural language

- 1. DDL : Data Definition Language (attribute, types)

- 2. DML : Data Manipulation Language (search, insert, delete, update)

 

Select : 찾고자 하는 attribute 내용

from : 찾고자 하는 attribute가 위치한 relation

where : 찾고자 하는 attribute에 관한 조건 (and, or, not 연산이 가능)

 

Cartesian product : 두 relation 의 각 tupple 의 모든조합의 relation

Join : where 의 조건에 알맞게 연결된 모든조합의 relation

 

Delete : delete from relation where attribute = "value"

Insert : insert into relation values(each attributes)

 

Bad design

- attribute 값들이 특정 attribute 값에따라 결정되는 경우 -> 연관되는 attribute값들을 분리된 relation으로 넣는다

- attribute 값이 null값으로 들어가는 경우가 존재

- attribute 삭제시 연관된 attribute 값들이 삭제되기 어려운 경우가 존재

 

Model 2 : Entity - Relationship Model

- Entity : object 개념

- Relation : object 간의 관계

 

Database Engin : DB 내부 동작 알고리즘

1. Storage manager

- DB <-> OS 간의 interface (Storage access, File organization, indexing, hashing)

- Transaction Manager

  - atomic 한 연산을 담당 (all or nothing)

2. Query processor

- Query optimization : Query문 -> relational algebra -> select 1 과정 알고리즘

- Query Processing : cost를 비교하여 한가지 operation을 선택한다

 

 

 

 

 

 

[BASIC]

1. types

char(n) -- n 크기의 문자, 크기가 고정된다
varchar(n) -- 최대 n 크기의 문자, 저장공간이 유동적으로 적용
int -- 정수형 타입
smallint -- int 보다 적은 int
numeric(p,d) -- p:총 자리수, d:소수점 뒷 자리수 ex) (4,2) : 12.34
real, double presicion -- 4바이트 소수
float(n) -- 4바이트 이상의 소수

 

 

2. create Table : table 스키마 생성

create table instructor (
    ID			char(5),
    name		varchar(20), not null,
    dept_name           varchar(2),
    salary		numeric(8,2),
    primary key (ID),  -- table 내에서 기준값
    foreign key (dept_name) references department)  -- table attribute 중 외부 table의 attribute 값을 지니는 경우

 

 

3. insert data : 스키마에 새로운 데이터를 생성

- 일반적인 추가

insert into course
values('CS-437', 'Database Systems', 'Comp. Sci.', 4);

- 데이터 순서를 지정하여 추가

insert into course(course_id, title, dept_name, credits)
values ('CS-437', 'Database, Systems', 'Comp. Sci.', 4);

- 데이터에 null값을 넣어 추가

insert into student
values('3003', 'Green', 'Finance', null);

- 특정 table에서 데이터를 가져와 다른 table에 데이터를 추가

insert into student
	select id, name, dept_name, 0
	from instructor;

- 특정 table에서 데이터를 가져와 동일 table에 추가도 가능하다

insert into student
	select *
	from student;

 

 

4. alter + add, drop : 특정 table에 특정 attribute를 생성, 제거

alter table instructor add birthday char(8);

alter table instructor drop birthday;

 

 

5. delete : 데이터 제거

 

※ 스키마까지 제거할 경우 ※

drop table instructor;

 

 

delete, from, where를 통해 제거할 수 있다

delete from instructor;

- 스키마 유지하면서 특정 데이터 제거

Q : dept_name 이 Finance인 instructor 제거

delete from instructor
where dept_name = 'Finance';

Q : building 값이 'Watson' 인 department 의 dept_name 과 동일한 dept_name 을 지닌 instructor 를 제거

delete from instructor
where dept_name in (select dept_name
		from department
		where building = 'Watson');

Q : instructor 에서 평균 salary 값보다 적은 salary인 instructor 를 제거

delete from instructor
where salary < (select avg(salary)
			   from instructor);

 

 

6. update : 데이터 내용 수정

- update, set, where를 통해 수정한다

update instructor
set salary = salary*1.03
where salary > 10000;

update instructor
set salary = salary*1.05
where salary <= 10000;

 

- case : 조건을 여러가지로 나눌 수 있다

- case, when then , else , end

update instructor
set salary = case
	when salary <= 100000 then
		salary*1.05
	else
		salary*1.03
	end;

- subquery를 통한 update : sclalar subquery 를 통해 값을 업데이트 할 수 있다

update student as S
set tot_cred = (select sum(credits)
	from takes natural join course
	where S.id = takes.id and
		takes.grade <> 'F' and
		takes.grade is not null);

- subquery + case 문의 경우

update student as S
set tot_cred = (select case
				when sum(credits) is not null then
					sum(credits)
				else
					0
				end
				 from takes natural join course
				 where S.id = takes.id and
				 	takes.grade <> 'F' and
				 	takes.grade is not null);

 

 

 

※ 테스트를 위한 table 생성, data 삽입 코드 ※

create table classroom
	(building		varchar(15),
	 room_number		varchar(7),
	 capacity		numeric(4,0),
	 primary key (building, room_number)
	);

create table department
	(dept_name		varchar(20), 
	 building		varchar(15), 
	 budget		        numeric(12,2) check (budget > 0),
	 primary key (dept_name)
	);

create table course
	(course_id		varchar(8), 
	 title			varchar(50), 
	 dept_name		varchar(20),
	 credits		numeric(2,0) check (credits > 0),
	 primary key (course_id),
	 foreign key (dept_name) references department
		on delete set null
	);

create table instructor
	(ID			varchar(5), 
	 name			varchar(20) not null, 
	 dept_name		varchar(20), 
	 salary			numeric(8,2) check (salary > 29000),
	 primary key (ID),
	 foreign key (dept_name) references department
		on delete set null
	);

create table section
	(course_id		varchar(8), 
         sec_id			varchar(8),
	 semester		varchar(6)
		check (semester in ('Fall', 'Winter', 'Spring', 'Summer')), 
	 year			numeric(4,0) check (year > 1701 and year < 2100), 
	 building		varchar(15),
	 room_number		varchar(7),
	 time_slot_id		varchar(4),
	 primary key (course_id, sec_id, semester, year),
	 foreign key (course_id) references course
		on delete cascade,
	 foreign key (building, room_number) references classroom
		on delete set null
	);

create table teaches
	(ID			varchar(5), 
	 course_id		varchar(8),
	 sec_id			varchar(8), 
	 semester		varchar(6),
	 year			numeric(4,0),
	 primary key (ID, course_id, sec_id, semester, year),
	 foreign key (course_id,sec_id, semester, year) references section
		on delete cascade,
	 foreign key (ID) references instructor
		on delete cascade
	);

create table student
	(ID			varchar(5), 
	 name			varchar(20) not null, 
	 dept_name		varchar(20), 
	 tot_cred		numeric(3,0) check (tot_cred >= 0),
	 primary key (ID),
	 foreign key (dept_name) references department
		on delete set null
	);

create table takes
	(ID			varchar(5), 
	 course_id		varchar(8),
	 sec_id			varchar(8), 
	 semester		varchar(6),
	 year			numeric(4,0),
	 grade		        varchar(2),
	 primary key (ID, course_id, sec_id, semester, year),
	 foreign key (course_id,sec_id, semester, year) references section
		on delete cascade,
	 foreign key (ID) references student
		on delete cascade
	);

create table advisor
	(s_ID			varchar(5),
	 i_ID			varchar(5),
	 primary key (s_ID),
	 foreign key (i_ID) references instructor (ID)
		on delete set null,
	 foreign key (s_ID) references student (ID)
		on delete cascade
	);

create table time_slot
	(time_slot_id		varchar(4),
	 day			varchar(1),
	 start_hr		numeric(2) check (start_hr >= 0 and start_hr < 24),
	 start_min		numeric(2) check (start_min >= 0 and start_min < 60),
	 end_hr			numeric(2) check (end_hr >= 0 and end_hr < 24),
	 end_min		numeric(2) check (end_min >= 0 and end_min < 60),
	 primary key (time_slot_id, day, start_hr, start_min)
	);

create table prereq
	(course_id		varchar(8), 
	 prereq_id		varchar(8),
	 primary key (course_id, prereq_id),
	 foreign key (course_id) references course
		on delete cascade,
	 foreign key (prereq_id) references course
	);




delete from prereq;
delete from time_slot;
delete from advisor;
delete from takes;
delete from student;
delete from teaches;
delete from section;
delete from instructor;
delete from course;
delete from department;
delete from classroom;
insert into classroom values ('Packard', '101', '500');
insert into classroom values ('Painter', '514', '10');
insert into classroom values ('Taylor', '3128', '70');
insert into classroom values ('Watson', '100', '30');
insert into classroom values ('Watson', '120', '50');
insert into department values ('Biology', 'Watson', '90000');
insert into department values ('Comp. Sci.', 'Taylor', '100000');
insert into department values ('Elec. Eng.', 'Taylor', '85000');
insert into department values ('Finance', 'Painter', '120000');
insert into department values ('History', 'Painter', '50000');
insert into department values ('Music', 'Packard', '80000');
insert into department values ('Physics', 'Watson', '70000');
insert into course values ('BIO-101', 'Intro. to Biology', 'Biology', '4');
insert into course values ('BIO-301', 'Genetics', 'Biology', '4');
insert into course values ('BIO-399', 'Computational Biology', 'Biology', '3');
insert into course values ('CS-101', 'Intro. to Computer Science', 'Comp. Sci.', '4');
insert into course values ('CS-190', 'Game Design', 'Comp. Sci.', '4');
insert into course values ('CS-315', 'Robotics', 'Comp. Sci.', '3');
insert into course values ('CS-319', 'Image Processing', 'Comp. Sci.', '3');
insert into course values ('CS-347', 'Database System Concepts', 'Comp. Sci.', '3');
insert into course values ('EE-181', 'Intro. to Digital Systems', 'Elec. Eng.', '3');
insert into course values ('FIN-201', 'Investment Banking', 'Finance', '3');
insert into course values ('HIS-351', 'World History', 'History', '3');
insert into course values ('MU-199', 'Music Video Production', 'Music', '3');
insert into course values ('PHY-101', 'Physical Principles', 'Physics', '4');
insert into instructor values ('10101', 'Srinivasan', 'Comp. Sci.', '65000');
insert into instructor values ('12121', 'Wu', 'Finance', '90000');
insert into instructor values ('15151', 'Mozart', 'Music', '40000');
insert into instructor values ('22222', 'Einstein', 'Physics', '95000');
insert into instructor values ('32343', 'El Said', 'History', '60000');
insert into instructor values ('33456', 'Gold', 'Physics', '87000');
insert into instructor values ('45565', 'Katz', 'Comp. Sci.', '75000');
insert into instructor values ('58583', 'Califieri', 'History', '62000');
insert into instructor values ('76543', 'Singh', 'Finance', '80000');
insert into instructor values ('76766', 'Crick', 'Biology', '72000');
insert into instructor values ('83821', 'Brandt', 'Comp. Sci.', '92000');
insert into instructor values ('98345', 'Kim', 'Elec. Eng.', '80000');
insert into section values ('BIO-101', '1', 'Summer', '2009', 'Painter', '514', 'B');
insert into section values ('BIO-301', '1', 'Summer', '2010', 'Painter', '514', 'A');
insert into section values ('BIO-399', '1', 'Summer', '2010', 'Painter', '514', 'A'); --새로 추가
insert into section values ('CS-101', '1', 'Fall', '2009', 'Packard', '101', 'H');
insert into section values ('CS-101', '1', 'Spring', '2010', 'Packard', '101', 'F');
insert into section values ('CS-190', '1', 'Spring', '2009', 'Taylor', '3128', 'E');
insert into section values ('CS-190', '2', 'Spring', '2009', 'Taylor', '3128', 'A');
insert into section values ('CS-315', '1', 'Spring', '2010', 'Watson', '120', 'D');
insert into section values ('CS-319', '1', 'Spring', '2010', 'Watson', '100', 'B');
insert into section values ('CS-319', '2', 'Spring', '2010', 'Taylor', '3128', 'C');
insert into section values ('CS-347', '1', 'Fall', '2009', 'Taylor', '3128', 'A');
insert into section values ('EE-181', '1', 'Spring', '2009', 'Taylor', '3128', 'C');
insert into section values ('FIN-201', '1', 'Spring', '2010', 'Packard', '101', 'B');
insert into section values ('HIS-351', '1', 'Spring', '2010', 'Painter', '514', 'C');
insert into section values ('MU-199', '1', 'Spring', '2010', 'Packard', '101', 'D');
insert into section values ('PHY-101', '1', 'Fall', '2009', 'Watson', '100', 'A');
insert into teaches values ('10101', 'CS-101', '1', 'Fall', '2009');
insert into teaches values ('10101', 'CS-315', '1', 'Spring', '2010');
insert into teaches values ('10101', 'CS-347', '1', 'Fall', '2009');
insert into teaches values ('12121', 'FIN-201', '1', 'Spring', '2010');
insert into teaches values ('15151', 'MU-199', '1', 'Spring', '2010');
insert into teaches values ('22222', 'PHY-101', '1', 'Fall', '2009');
insert into teaches values ('32343', 'HIS-351', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-101', '1', 'Spring', '2010');
insert into teaches values ('45565', 'CS-319', '1', 'Spring', '2010');
insert into teaches values ('76766', 'BIO-101', '1', 'Summer', '2009');
insert into teaches values ('76766', 'BIO-301', '1', 'Summer', '2010');
insert into teaches values ('83821', 'CS-190', '1', 'Spring', '2009');
insert into teaches values ('83821', 'CS-190', '2', 'Spring', '2009');
insert into teaches values ('83821', 'CS-319', '2', 'Spring', '2010');
insert into teaches values ('98345', 'EE-181', '1', 'Spring', '2009');
insert into student values ('00128', 'Zhang', 'Comp. Sci.', '102');
insert into student values ('12345', 'Shankar', 'Comp. Sci.', '32');
insert into student values ('19991', 'Brandt', 'History', '80');
insert into student values ('23121', 'Chavez', 'Finance', '110');
insert into student values ('44553', 'Peltier', 'Physics', '56');
insert into student values ('45678', 'Levy', 'Physics', '46');
insert into student values ('54321', 'Williams', 'Comp. Sci.', '54');
insert into student values ('55739', 'Sanchez', 'Music', '38');
insert into student values ('70557', 'Snow', 'Physics', '0');
insert into student values ('76543', 'Brown', 'Comp. Sci.', '58');
insert into student values ('76653', 'Aoi', 'Elec. Eng.', '60');
insert into student values ('98765', 'Bourikas', 'Elec. Eng.', '98');
insert into student values ('98988', 'Tanaka', 'Biology', '120');
insert into takes values ('00128', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('00128', 'CS-347', '1', 'Fall', '2009', 'A-');
insert into takes values ('12345', 'CS-101', '1', 'Fall', '2009', 'C');
insert into takes values ('12345', 'CS-190', '2', 'Spring', '2009', 'A');
insert into takes values ('12345', 'CS-315', '1', 'Spring', '2010', 'A');
insert into takes values ('12345', 'CS-347', '1', 'Fall', '2009', 'A');
insert into takes values ('19991', 'HIS-351', '1', 'Spring', '2010', 'B');
insert into takes values ('23121', 'FIN-201', '1', 'Spring', '2010', 'C+');
insert into takes values ('44553', 'PHY-101', '1', 'Fall', '2009', 'B-');
insert into takes values ('45678', 'CS-101', '1', 'Fall', '2009', 'F');
insert into takes values ('45678', 'CS-101', '1', 'Spring', '2010', 'B+');
insert into takes values ('45678', 'CS-319', '1', 'Spring', '2010', 'B');
insert into takes values ('54321', 'CS-101', '1', 'Fall', '2009', 'A-');
insert into takes values ('54321', 'CS-190', '2', 'Spring', '2009', 'B+');
insert into takes values ('55739', 'MU-199', '1', 'Spring', '2010', 'A-');
insert into takes values ('76543', 'CS-101', '1', 'Fall', '2009', 'A');
insert into takes values ('76543', 'CS-319', '2', 'Spring', '2010', 'A');
insert into takes values ('76653', 'EE-181', '1', 'Spring', '2009', 'C');
insert into takes values ('98765', 'CS-101', '1', 'Fall', '2009', 'C-');
insert into takes values ('98765', 'CS-315', '1', 'Spring', '2010', 'B');
insert into takes values ('98988', 'BIO-101', '1', 'Summer', '2009', 'A');
insert into takes values ('98988', 'BIO-301', '1', 'Summer', '2010', null);
insert into takes values ('98988', 'BIO-399', '1', 'Summer', '2010', 'A'); --새로 추가
insert into advisor values ('00128', '45565');
insert into advisor values ('12345', '10101');
insert into advisor values ('23121', '76543');
insert into advisor values ('44553', '22222');
insert into advisor values ('45678', '22222');
insert into advisor values ('76543', '45565');
insert into advisor values ('76653', '98345');
insert into advisor values ('98765', '98345');
insert into advisor values ('98988', '76766');
insert into time_slot values ('A', 'M', '8', '0', '8', '50');
insert into time_slot values ('A', 'W', '8', '0', '8', '50');
insert into time_slot values ('A', 'F', '8', '0', '8', '50');
insert into time_slot values ('B', 'M', '9', '0', '9', '50');
insert into time_slot values ('B', 'W', '9', '0', '9', '50');
insert into time_slot values ('B', 'F', '9', '0', '9', '50');
insert into time_slot values ('C', 'M', '11', '0', '11', '50');
insert into time_slot values ('C', 'W', '11', '0', '11', '50');
insert into time_slot values ('C', 'F', '11', '0', '11', '50');
insert into time_slot values ('D', 'M', '13', '0', '13', '50');
insert into time_slot values ('D', 'W', '13', '0', '13', '50');
insert into time_slot values ('D', 'F', '13', '0', '13', '50');
insert into time_slot values ('E', 'T', '10', '30', '11', '45 ');
insert into time_slot values ('E', 'R', '10', '30', '11', '45 ');
insert into time_slot values ('F', 'T', '14', '30', '15', '45 ');
insert into time_slot values ('F', 'R', '14', '30', '15', '45 ');
insert into time_slot values ('G', 'M', '16', '0', '16', '50');
insert into time_slot values ('G', 'W', '16', '0', '16', '50');
insert into time_slot values ('G', 'F', '16', '0', '16', '50');
insert into time_slot values ('H', 'W', '10', '0', '12', '30');
insert into prereq values ('BIO-301', 'BIO-101');
insert into prereq values ('BIO-399', 'BIO-101');
insert into prereq values ('CS-190', 'CS-101');
insert into prereq values ('CS-315', 'CS-101');
insert into prereq values ('CS-319', 'CS-101');
insert into prereq values ('CS-347', 'CS-101');
insert into prereq values ('EE-181', 'PHY-101');