how to make sqplite3 table

[지원 형식]
INTEGER, REAL, TEXT, BLOB

[테이블 만들기]
CREATE TABLE Students (
id INTEGER PRIMARY KEY AUTOINCREMENT,
fname TEXT NOT NULL,
lname TEXT NOT NULL );

CREATE TABLE Tests (
id INTEGER PRIMARY KEY AUTOINCREMENT,
testname TEXT,
weight REAL DEFAULT .10 CHECK (weight<=1));

[자료삽입]
INSERT into Students
(fname, lname)
VALUES
('Harry', 'Potter');

INSERT into Tests
(testname, weight)
VALUES
('YourW', .25);

[테이블 조회]
SELECT * FROM Tests;
집계확인
SELECT SUM(weight) FROM Tests;

[조회 결과를 조합하여 출력]
SELECT fname||' '|| lname AS fullname, id FROM Students;

[외부키와 복합키가 있는 테이블 만들기]
CREATE TABLE TestResults (
studentsid INTEGER REFERENCES Students(id),
testid INTEGER REFERENCES Tests(id),
score INTEGER CHECK (score<=100 AND score>=0),
PRIMARY KEY (studentsid, testid));

출력해보기
SELECT studentid, testid, weig FROM TestResults;

[수정하기]
UPDATE TestResults
SET score=50
WHERE studentid=2 AND testid=2;

[지우기]
DELETE FROM TestResults WHERE studentid=2 AND testid=2;
*WHERE로 지정하지 않으면 테이블 전체가 삭제되니 주의

[다중 테이블 질의]
SELECT
Students.fname||' '|| Students.lname AS StudentName,
Tests.testname,
TestResults.score
FROM TestResults
JOIN Students
                   ON (TestResults.studentid=Students.id)

JOIN Tests

                   ON (TestResults.testid=Test.id)

WHERE testid=6;

[계산식 추가]
SELECT
Students.fname||' '|| Students.lname AS StudentName,
SUM((Tests.weight*TestResults.score)) AS TotalWeightedScore
FROM TestResults
JOIN Students
                   ON (TestResults.studentid=Students.id)

JOIN Tests

                   ON (TestResults.testid=Test.id)

WHERE testid=6;


[필터링과 정렬]
SELECT
Students.fname||' '|| Students.lname AS StudentName,
SUM(Tests.weight*TestResults.score) AS TotalWeightedScore
FROM TestResults
JOIN Students
                   ON (TestResults.studentid=Students.id)

JOIN Tests

                   ON (TestResults.testid=Test.id)

GROUP BY TestResults.studentid
ORDER BY TotalWeightedScore DESC;

[하위 질의 활용]
SELECT
Students.fname||' '|| Students.lname AS StudentName,
Students.id AS StudentID,
(SELECT COUNT(*)
FROM TestResults
WHERE TestResults.studentid=Students.id
AND TestResults.score>60)
AS TestsPassed
FROM Students;

[테이블 삭제]
DROP TABLE TestResults;