SQL

• SQL is not programming language but a data sublanguage
• MS Access 89에서는 UNIQUE, CHECK, DEFAULT,NUMERIC(m,n), LIKE, on update casacade, on delete casacade 제약조건을 지원하지 않음
• MS Access에서는 AutoNumber를 지원하지 않음. 대신 Identity를 사용
• MS Access에서는 database 생성을 지원하지 않음

DDL (Data Definition Language)

1. CREATE (database, tables, views, indexes)
2. ALTER: modify columns / constraints
3. DROP (database, tables, views, indexes)
4. TRUNCATE: delete table data while keeping structure.
   MS Access에서는 지원하지 않음 => DELETE FROM table

CREATE TABLE student (
    id INT NOT NULL,
    CourseID INT NOT NULL,
    Name VARCHAR(100) UNIQUE, # unique는 자동으로 index 생성
    Age INT,
    CONSTRAINT STUDENT_PK PRIMARY KEY (id),
    CONSTRAINT 
    COURSE_FK FOREIGN KEY (CourseID) 
    REFERENCES Course(CourseID) 
    ON UPDATE CASACADE 
    ON DELETE NO ACTION
);
ALTER TABLE student ADD COLUMN major VARCHAR(100);
ALTER TABLE student ADD CONSTRAINT STUDENT_FK FOREIGN KEY (CourseID) REFERENCES Course(CourseID) ON DELETE CASCADE;
ALTER TABLE student ADD CONSTRAINT AGE_CHECK CHECK (Age > 0);
ALTER TABLE student DROP CONSTRAINT AGE_CHECK;
DROP TABLE student;
TRUNCATE TABLE student;

CREATE VIEW [view name] AS SELECT * FROM student;

DML (Data Manipulation Language)

INSERT INTO student VALUES (1, 'Alice', 20);
UPDATE student SET age = 21, Name = 'babo' WHERE id = 1;
DELETE FROM student WHERE id = 1;

DQL (Data Query Language)

A query create temporarily a new table.
this allows a query to create a new relation and feed information to another query as a subquery

SELECT * FROM student;
SELECT name 
FROM student 
WHERE age > 20
ORDER BY name DESC, age ASC;
SELECT DISTINCT name FROM student;
SELECT name, age FROM student WHERE Age > (SELECT AVG(Age) FROM student);

JOIN

• inner join(equijoin)
  • explicit join: FROM table1 INNER JOIN table2 ON table1.id = table2.id
    (MS Access에서는 INNER를 명시해야됨)
  • implicit join: FROM table1, table2 WHERE table1.id = table2.id
• outer join
  • left outer join: FROM table1 LEFT JOIN table2 ON table1.id = table2.id
  • right outer join: FROM table1 RIGHT JOIN table2 ON table1.id = table2.id