before class review

REVIEW
INNER JOIN vs LEFT JOIN
inner join: common data only
left join: all from left + matches form right


WHERE diagnosis_name = ‘Diabetes’ or ‘Hypertension’ => WRONG
WHERE diagnosis_name in ( ‘Diabetes’ or ‘Hypertension’ )
or
WHERE diagnosis_name= ‘Diabetes’ or diagnosis_name= ‘Hypertension’


SQL- Execution Oder
FROM → JOIN → WHERE → GROUP BY


When using aggregate functions like COUNT or MAX, we must include a GROUP BY clause to define the groups


UNION- ALL

SELECT patient_id AS id
FROM patients WHERE patient_id <=10;
UNION ALL -> 위의 환자 ID, 밑의 의사 아이디 합쳐서 결과 나옴
-> combine high content’s result and low content’s result
SELECT doctor_id AS id
FROM doctors
WHERE doctor_id BETWEEN 10 AND 20;

EXISTS ( ) , NOT EXISTS ()

SELECT d.doctor_id, d.first_name, d.specialty
from doctors d
where exists(
select 1
from admissions a
where a.attending_doctor_id = d.doctor id
)
select *
from admissions
where exists (
select 1
from patients p
where p.allergies is not null
and p.patient_id = a.patient_id
);

Today class

1.SQL Basic process

Create , Read, Update, Delete
Insert, Select, Update, Delete

2.DATABASE CREATE & DELETE

CREATE DATABASE db_name ; ⇨ USE db_name ;
DROP DATABASE db_name;

3. TABLE CREATE & DELETE

   CREATE TABLE table_name( column1 datatype , column2 datatype~) ;
   DROP table_name;

4. check or watch

   DESC table_name -> read a summery
   SHOW tables; -> table list
   SHOW CREATE TABLE table_name; -> check all SQL tables

5. Alter tables

   add column: ALTER TABLE (table_name) ADD (-) condition;
   del column: ALTER TABLE (table_name) DROP COLUMN (-);
   change column type: ALTER TABLE (table_name) MODIFY column Type;
   change column name: ALTER TABLE (table_name) CHANGE column condition;
   change basic value: ALTER TABLE (table_name) ALTER(value) SET DEFAULT(-);
   change table name: ALTER TABLE (table_name) RENAME TO (-);

6.INSERT

INSERT INTO table_name (column1, column2, column3 ….)
VALUES (‘a1’,’a2’,’a3’.....)

7.UPDATE

UPDATE table_name
SET column1 = value1, column2 = value2, column3=value3……
WHERE condition; –=> very important

8.DELETE

DELETE FROM table_name
WHERE condition;
실무에서는 delete 대신 soft delete 사용