SQL Review

sql-review.md

Schema definition / migrations

CREATE TABLE test (
    test_key INTEGER PRIMARY KEY,
    test_value INTEGER NOT NULL UNIQUE,
);

CREATE TABLE test2 (
    test2_key INTEGER PRIMARY KEY,
    test_key INTEGER NOT NULL,

    CONSTRAINT test2_test_fkey FOREIGN KEY (test_key) REFERENCES test(test_key)
    ON DELETE RESTRICT ON UPDATE CASCADE
);

ALTER TABLE test 
ADD test2_key INTEGER;

ALTER TABLE test
ADD CONSTRAINT test_test2_key_fkey FOREIGN KEY (test2_key) REFERENCES test2(test2_key) 
ON DELETE SET NULL ON UPDATE CASCADE;

Insert / Update / Select / Delete

INSERT INTO test (test_key, test_value) 
VALUES (1, 'test');

INSERT INTO test2 (test2_key, test_key)
VALUES (1, 1);

UPDATE test
SET test2_key = 1
WHERE test_key = 1;

SELECT test_key, test_value, test2_key 
FROM test
WHERE test_key = 1;

DELETE FROM test
WHERE test_key = 1;

Aggregate Functions

SELECT MIN(test_value)
FROM test;
SELECT MAX(test_value)
FROM test;
SELECT AVG(test_value)
FROM test;
SELECT SUM(test_value)
FROM test;
SELECT COUNT(test_value)
FROM test;

-- Group By
SELECT test_value, COUNT(test_value)
FROM test
GROUP BY test_value;
-- Group By

-- Having
SELECT test_value, COUNT(test_value)
FROM test
GROUP BY test_value
HAVING COUNT(test_value) > 1;
-- Having

-- Order By
SELECT test_value, COUNT(test_value)
FROM test
GROUP BY test_value
HAVING COUNT(test_value) > 1
ORDER BY test_value DESC;
-- Order By

Joins

SELECT *
FROM test
INNER JOIN test2 ON test.test_key = test2.test_key;

SELECT *
FROM test
LEFT JOIN test2 ON test.test_key = test2.test_key;

SELECT *
FROM test
RIGHT JOIN test2 ON test.test_key = test2.test_key;

SELECT *
FROM test
FULL OUTER JOIN test2 ON test.test_key = test2.test_key;

Sub-queries

SELECT *
FROM test
WHERE test_key IN (SELECT test_key FROM test2);

SELECT *
FROM test
WHERE test_key IN (SELECT test_key FROM test2 WHERE test2_key = 1);