3_2

• SQL includes between comparison operator
  find the names of all instructors with the salary between 90,000 and 100,000

select name from instructor where salary between 900000 and 1000000;

• Tuple comparison

select name, course_id
from instructor,teaches
where(instructor.ID,dept_name)=(teaches.ID,'Biology');

---

DELETE CLAUSE

• Delete all instructors

delete from instructor;

• Delete all instructors from the Finance department

delete from instructoer
where dept_name='Finance';

• Delete all instructors associated with a department located in the AI building

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

---

UPDATE CLAUSE

• Increase salaries of instructors whose salary is over 100,000 by 3%

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

• Unsafe option issue

update medical
set bmi = 40
where bmi > 40;

---

FROM CLAUSE

• Find the Castesian product instructor x teaches

select * from instructor, teaches;

---

Additional Basic Operations

• Find the names of all instructoers who have a higher salary than some instructor in 'Comp.Sci'

select distinct T.name 
from instructors as T, instructors as S
where T.salary > S.salary and S.dept_name='Com.Sci';

---

String Operations

Percent (%) - matches any substring
underscore (_) - matches any single char

• Find the names of all instructoers whose name includs the substring "dar"

select name from instructor where name like '%dar%';

• Find the names that begin with the letter m and have the letter d as the third letter

select name from instructor where name like 'm_d%';

• Match the string "100%"
  it means percentage so we have to use \(back slach).
  We use backslash as the escape char.

select number from table_name where condition like '100\%' escape '\';

💡 Patterns are case sensitive

• 'Intro%' matches any string beginning with 'Intro'
• '%Comp%' matches any string containing 'Comp' as a substring
• '___' matches any string of exactly three char
• '___%' matches any string of at least three char

• Concatenation

select concat(LastName,',',FristName) as Name from Person
/* Jason | doe -> Jason, doe */

• Converting from upper to lower case

select UPPER('louder please');
select lower('AABB');

• Finding string length, extracting substrings

select length("SQL Tutorial") as LengthOfString; //return the length of string
select substring("MySqltest",1,5); 
//search the substring and select 1 to 5 

sql에서는 1 to 5면 012345 중에 1-5 인가 12345 중에 1-5인가

---

Ordering the display of Tuples

• List the names of all insructors in an alphabetic order

select distinct name from instructor order by name;
select distinct name from instructor order by name desc;

---

Set Operations

중복 제거는 되지 않는다. 따로 distinct 해줘야한다.

• Find courses that ran in Fall 209 or in Spring 2010

(select course_id from section where sem='Fall' and year= 2009) 
union (select course_id from section where sem='Spring' and year=2010);

• Find courses that ran in Fall 2009 and in Spring 2010

(select course_id from section where sem='Fall' and year=2009)
intersect
(select course_id from section where sem='Spring' and year=2010);

• Find courses that ran in Fall 2009 but not in Spring 2010

(select course_id from section where sem='Fall' and year=2009)
except
(select course_id from section where sem='Spring' and year=2010);

---

Aggregate Functions

• Find the average salary of instructors in the Computer Science department

select avg(salary) from instructor where dept_name='Comp.Sci';

• Find the total number of instructors who teach a course in the Spring 2010 semester

select count(distinct ID)
from teaches
where semester='Spring' and year=3020;

• Find the number of tuples in the course relation

select count(*) from course;

여기는 답 확인 필요

• Find the average bmi of male

select avg(bmi) from health where sex='male';

• Find the number of smokers who have more than two children

select count(smoker) from smoke where IsSmoke='yes' and smoke_child >= 2;

• Find the minimum age who is smoker

select min(age) from smoke where IsSmoke = 'yes';

---

Group By

'group by + column name' 일 때, column name이 위의 select 에도 꼭 있어야한다.
ex) select name ... group by name;

• Find the average salary of instructors in each department

select dept_name, avg(salary) as avg_salary 
from instructor group by dept_name;

Having

Where 는 group by 랑 상관 없다. group by 이전의 조건문
Having 은 group by 이후에 group에 필요한 조건문

• Find the names and average salaries of all departments whose average salary is greater than 42000

select dept_name, avg(salary) 
from instructor 
group by dept_name having salary > 42000;

• Find the names and average salaries of each departments whose average salary is greater than 42000 and in each department employee's salary have to more than 30000

select dept_name, avg(salary) 
from instructor
where salary > 30000
group by dept_name having salary > 42000;

• Total all salaries

(1) select sum(salary) from instructor;	//not contain null
(2) select sum(*) from instructor;	//contain null

---

Medical table

• smoker로 그룹화해서 데이터 개수 조회

select smoker, count(*) from medical group by smoker; 

• Age가 40 이상인 경우에만 smoker로 그룹화해서 데이터 개수 조회

select smoker,count(*) from medical where age >= 40 group by smoker;

• Children에 대해 그룹화하여 데이터 개수 출력 후, 그룹내 데이터 개수가 5개 이상인 데이터 조회

select Children, count(*) as cnt 
from medical
group by Children having cnt >= 5;

• Children이 1 이상인 사람들을 그룹화하여 그룹내 데이터 개수를 가져온 후,
  그룹내 데이터 개수가 5개 이상인 데이터 조회

select Children, count(*) as cnt
from medical
where Children >= 1
group by children having cnt >= 5;

📌 Region별로 담배 피는 사람들을 그룹화하여 그룹내 데이터 개수를 가려온 후,
데이터 개수에 대해 내림차순 정렬로 조회

select Region, count(*) as cnt
from medical
where smoker='yes'
group by Region order by cnt desc;

---

Hero collection table

여기는 * 아니고 count(name) 인 이유?

• type으로 그룹화해서 데이터 개수 조회

select type, count(*) from hero_collection group by type;

• type이 2 이상인 경우에만 그룹화 해서 데이터 개수 조회

select type, count(name) 
from hero_collection
where type >= 2
group by type

• type 그룹화하여 데이터 개수 출력 후 , 그룹내 데이터 개수가 2개 이상인 데이터 조회

select type, count(name) as cnt 
from hero_collection
group by type having cnt >= 2;

• type이 2이상인 사람들을 그룹화하여 그룹내 데이터 개수를 가져온 후, 그룹내 데이터 개수가 2개 이상인 데이터 조회

select type, count(name) as cnt
from hero_collection
where type >= 2
group by type having cnt >= 2;

📌 type이 2이상인 사람들을 그룹화하여 그룹내 데이터 개수를 가져온 후, 그 중에 수가 2개 이상인 데이터를 type 내림차순 정렬로 조회

select type, count(name) as cnt
from hero_collection
where type >= 2
group by type having cnt >= 2
order by type decs;

---

Subqueries

IN : 리턴되는 값 중에서 조건에 해당하는 값이 있으면 참
ANY, SOME : 서브쿼리에 의해 리턴되는 각각의 값과 조건을 비교하여 하나 이상을 만족시키면 참
ALL : 서브쿼리에 의해 리턴되는 모든 값과 조건값을 비교하여 모든 값을 만족해야만 참

[1]select * from t1 where col=[2](select col1 from t2);
//[1] is outer query(or outer statement) 
//[2] is subquery

• below queries are the same meaning

바로 그냥 where age=min(age) 하면 안되나?

세번째 코드 in 조건이 항상 참인거 아닌가?

[1] select * from medical
    where age=(select min(age) from medical);
    
[2] select * from medical where age=18;

• information of oldest and youngest people

select min(age) as age from medical
union select max(age) from medical
// the column name will be age

select min(age) from medical
union select max(age) as age from medical
//the column name will be min(age) because union follows the first column's name

select *
from medical
where age in (select min(age) from medical
			   union select max(age) from medical);
               
select * from medical
where age = (select min(age) from medical); 
// '=' can use only the value is one thing

• Find the courses offered in Fall 2022 and in Spring 2023

select distinct course_id from section
where semester='Fall' and year=2022 
and course_id 
in (select course_id from section where semester = 'Spring' and year = 2023);

• Two nested subquery - 2022 Fall / 2023 Spring / 2023 Fall

select distinct course_id from section
where semester='Fall'
and year=2022
and course_if in 
	(select course_id from section
    where semester = 'Spring' and year = 2023
    and course_id in
    	(select course_id from section
        where semester = 'Fall' and year = 2023));
        
// both are same meaning

select distinct course_id from section
where semester = 'Fall'
and year = 2022
and course_id in(
	select course_id from section
    where semester = 'Spring' and year = 2023)
and course_id in(
	select course_id from section 
    where semester = 'Fall' and year = 2023);

• Find the number of (distinct) students who have taken course taught by the instructor with ID 10101 (Table : taeches, takes)

select count(distinct ID) from takes
where (course_id, sec_id,semester,year) 
in(select course_id, sec_id, semester,year) 
from teaches
where ID=10101);

---

IN vs Not IN

null은 () 안에 들어가면 안되고 is null, is not null만 가능함
ex) 아래 예제같은 경우는 안됨

select * from food_table as f where f.number not in(7,8,9,10,null) 

select * from food_table f where f.number in(1,2,3,4,5,6);
//search 1,2,3,4,5,6 from food_table

select * from food_table as f where f.number not in(7,8,9,10) 
and f.number is not null
//search the numbers execpt 7,8,9,10 and null

---

ANY vs ALL

any(some) = min
all = max

select distinct T.name
from instructor as T, instructor as S
where T.salary > S.salary and S.dept_name='Biology';

• Below two queries are same meaning

[1] select name
    from instructor
    where salary > some (select salary from instructor
                          where dept_name='Biology');

[2] select name
    from instructor
    where salary > (select min(salary) from instructor
                               where dept_name='Biology');

• Find the names of all instructors whose salary is greater than the salary of all instructors in the Biology department

select name
from instructor
where salary > all (select salary from instructor
                           where dept_name='Biology');
            

---

Exists

IN 과 유사하다.

sub query의 반환값이 있으면 true, 없으면 flase

select *from customers
where exists(select * from orders where orders.c_id=customers.c_id);

select *from customers
where not exists(select * from orders where orders.c_id=customers.c_id);