貓頭鷹的家

Creating Table
CREATE TABLE books (id INTEGER PRIMARY KEY, name TEXT, rating INTEGER) ;
CREATE TABLE exercise_logs (id INTEGER PRIMARY KEY AUTOINCREMENT,
   type TEXT, minutes INTEGER,
   calories INTEGER, heart_rate INTEGER);

Inserting Data
INSERT INTO books VALUES(1, "Two Worlds", 70) ;
INSERT INTO books VALUES(2, "One of the Labs", 90) ;
INSERT INTO books VALUES(3, "The Doll", 80);
INSERT INTO artists (name, country, genre) VALUES ("Taylor Swift", "US", "Pop");

Querying the Table
SELECT name FROM books;
SELECT * FROM books;
SELECT * FROM movies  WHERE release_year >= 2000 ORDER BY release_year [ASC, DESC] ;
SELECT * FROM exercise_logs WHERE type IN ("biking", "hiking", "tree climbing", "rowing");
SELECT * FROM exercise_logs WHERE type IN (SELECT type FROM drs_favorites);
// NOT IN : invert IN
SELECT * FROM exercise_logs WHERE type NOT IN (SELECT type FROM drs_favorites);
// LINK : reason contain "cardiovascular"
SELECT * FROM exercise_logs WHERE type IN (SELECT type FROM drs_favorites WHERE reason LIKE "%cardiovascular%");
// AS : show specific title
// HAVING : check group result
SELECT type, SUM(calories) AS total_calories FROM exercise_logs
   GROUP BY type
   HAVING total_calories > 150
   ;
//  AVG fucntion
SELECT type, AVG(calories) AS avg_calories FROM exercise_logs
   GROUP BY type
   HAVING avg_calories > 70
   ;
// COUNT : GROUP item count
SELECT type FROM exercise_logs GROUP BY type HAVING COUNT(*) >= 2;
// CASE : add column
SELECT type, heart_rate,
    CASE
        WHEN heart_rate > 220-30 THEN "above max"
        WHEN heart_rate > ROUND(0.90 * (220-30)) THEN "above target"
        WHEN heart_rate > ROUND(0.50 * (220-30)) THEN "within target"
        ELSE "below target"
    END as "hr_zone"
FROM exercise_logs;
// CASE + COUNT : add column
SELECT COUNT(*),
    CASE
        WHEN heart_rate > 220-30 THEN "above max"
        WHEN heart_rate > ROUND(0.90 * (220-30)) THEN "above target"
        WHEN heart_rate > ROUND(0.50 * (220-30)) THEN "within target"
        ELSE "below target"
    END as "hr_zone"
FROM exercise_logs
GROUP BY hr_zone;

Aggregating data
SELECT SUM(minutes) FROM todo_list ;
SELECT aisle, SUM(quantity) FROM groceries GROUP BY aisle;

JOIN
//cross join - no useful
SELECT * FROM student_grades, students;
//implicit inner join 
SELECT * FROM student_grades, students
    WHERE student_grades.student_id = students.id;
//explicit inner join 
SELECT students.first_name, students.last_name, students.email, student_grades.test, student_grades.grade FROM students
    JOIN student_grades
    ON students.id = student_grades.student_id
    WHERE grade > 90;
//LEFT OUTER JOIN
SELECT customers.name,customers.email,SUM(orders.price) AS total_money
    FROM customers
    LEFT OUTER JOIN orders
    ON customers.id = orders.customer_id
    GROUP BY orders.customer_id
    ORDER BY total_money DESC;'
// Combining multiple joins
SELECT persons.fullname,f.fullname FROM friends
    JOIN persons
    ON friends.person1_id = persons.id
    JOIN persons f
    ON friends.person2_id = f.id ;

UPDATE
UPDATE documents SET author = "Jackie Draper" WHERE author = "Jackie Paper" ;
UPDATE users SET deleted = true WHERE id = 1 LIMIT 1;

DELETE
DELETE FROM documents WHERE  title = "Things I'm Afraid Of" ;
DELETE FROM users WHERE id = 1 LIMIT 1;

ALTER
ALTER TABLE diary_logs ADD emotion TEXT default "unknown";

DROP TABLE
DROP TABLE diary_logs;

TRANSACTION 
BEGIN TRANSACTION;
UPDATE people SET husband = "Winston" WHERE user_id = 1;
UPDATE people SET wife = "Winnefer" WHERE user_id = 2;
COMMIT;

SQL comment
-- comment goes here
/*
* Author: TechOnTheNet.com>
* Purpose: To show a comment that spans multiple lines in your SQL statement.>
*/