Datatype: Text, Integer, Decimal
Constraints: Primary key, Foreign key, Default, Not null, Check, Unique, Autoincrement
DDL: CREATE, ALTER, DROP
DML: INSERT, UPDATE, DELETE, SELECT
DROP TABLE IF EXISTS students;
DROP TABLE IF EXISTS marks;
CREATE TABLE students (
sid INTEGER PRIMARY KEY AUTOINCREMENT NOT NULL,
class INTEGER NOT NULL CHECK (class BETWEEN 0 AND 12),
roll INTEGER UNIQUE NOT NULL,
name TEXT NOT NULL CHECK (name <> 'invalid'));
INSERT INTO students (sid, class, roll, name) VALUES ('S001', 10, 101, 'Alice');
INSERT INTO marks (mid, M1, m2) VALUES (1, 85.5, 92);
DELETE FROM marks WHERE mid = 1;
INSERT INTO marks (mid, M1, m2) VALUES (1, 85.5, 92);
1.SELECT s.*, m.m1, m.m2 FROM students s INNER JOIN marks m ON s.id = m.student_id;
2.SELECT s.*, m.*FROM students s INNER JOIN marks m ON s.id = m.student_id;
3.SELECT s.id AS student_id, s.roll, s.name, m.m1,m.m2, m.id AS mark_id FROM students s INNER JOIN marks m ON s.id = m.student_id;
4.SELECT s.name, m.m1, m.m2 FROM students s LEFT JOIN marks m ON s.id = m.student_id;
CREATE TABLE t AS SELECT *FROM t';
CREATE VIEW v(c,c') AS SELECT c,c' FROM t;
CREATE INDEX i ON t (c,c');
CREATE UNIQUE INDEX i ON t(c,c');
CREATE /MODIFY TRIGGER tri
[BEFORE/AFTER] [INSERT/UPPER/DELETE]
TRIGGER_TYPE[FOR EACH ROW/FOR EACH STATEMENT]
EXECUTE stored_procedured;
ALTER TABLE t
[ADD c t/ DROP COLUMN c];
[ADD/DROP constraints];
RENAME [TO tt/ c TO cc];
DROP TABLE VIEW /INDEX /TRIGGER <name>;
INSERT INTO t (columns) VALUES (value),(value);
INSERT INTO t1 (columns) SELECT (columns) FROM t2/ Where<match order/datatype with condition>;
UPDATE t SET c1=v1, c2=v2/ Where condition;
DELETE FROM t/Where condition;
SELECT:
Select All from table t.
Select Specific c from table t.
Select Multiple c,c' from table t.
Select with Alias c from t, renaming column in output to cc.
Select with DISTINCT unique (non-duplicate) values of column c from table t.
Select with Expression c and calculated value (c+1) from t.
SELECT */c/c' AS cc /DISTINCT c/c+1 AS c' FROM t;
Select with WHERE rows value in c equals 'value'.
SELECT * FROM t WHERE c = 'v';
Select with Multiple Conditions rows where c equals 'value' AND c' is greater than 10.
SELECT * FROM t WHERE c = 'value' AND c' > 10;
Select with LIKE rows where c contains the substring 'pat'.
SELECT * FROM t WHERE c LIKE '%aha%';
Select with ORDER BY all rows and sorts by c in ASC (default).
SELECT * FROM t ORDER BY c;
Select with DESC all rows and sorts by c in descending order.
SELECT * FROM t ORDER BY c DESC;
Select with LIMIT only the first 10 rows from table t.
SELECT * FROM t LIMIT 10;
Count Rows in table t. Count Distinct unique values in c. Sum calculates the sum of values in column c. Avg/Min/Max Calculates the average, minimum, or maximum value of c.
SELECT COUNT(*), COUNT(DISTINCT c, SUM(c), AVG(c), MIN(c'), MAX(c) FROM t;
Group By groups rows on values in c and calculates the count for each group. Group By HAVING groups by c and filters the groups, only showing groups where the count is greater than 1.
SELECT c, COUNT(*) FROM t GROUP BY c /(HAVING COUNT(*) > 1);
INNER JOIN retrieves rows that have matching values in both tables, joining on columns c from t and c' from t'. LEFT JOIN retrieves all rows from the left table (t) and the matched rows from the right table (t'). RIGHT JOIN retrieves all rows from the right table (t') and matched rows from left table (t). FULL (OUTER) JOIN retrieves all rows when there is a match in one of the tables.
SELECT t.c, t'.c'/* FROM t INNER/LEFT/RIGHT/FULL(OUTER) JOIN t' ON t.c = t'.c';
Subquery retrieves rows from t where the value in c is present in the set of c' values from t'.
SELECT *FROM t WHERE c IN (SELECT c' FROM t');
Subquery in FROM Treats result of inner SELECT on t' as temporary (sub) to query from.
SELECT c FROM (SELECT c' AS c FROM t') AS sub;