SQL

1. Theory

2. SQL vs NoSQL (Relational vs non-relational)

3. Web-scaled

4. When to use SQL and NoSQL

5. Expression, Statement, Operators

6. Data types SQL

   1. null, bit
   2. int, real / float
   3. char, varchar, text
   4. boolean
   5. date, datetime, timestamp
   6. xml/json
   7. – char vs varchar vs text
   8. – datetime vs timestamp
   9. – JSON vs JSONB

7. Operators

8. Arithmetic, Logical, Comparison, Bitwise

9. Primitives: Integer, Numeric, String, Boolean

10. Structured: Date/Time, Array, Range / Multirange, UUID

11. Document: JSON/JSONB, XML, Key-value (Hstore)

12. Geometry: Point, Line, Circle, Polygon

13. Customizations: Composite, Custom Types

14. Postgres

15. complex queries

    1. Aggregation
    2. Subquery
    3. Window Function

16. foreign keys

17. triggers

    1. Trigger Timing
    2. - BEFORE and AFTER
    3. - Uses

18. updatable views

19. transaction integrity

20. multiversion concurrency control

21. functions

    1. Stored Procedures
    2. Window functions
    3. Aggregate functions

22. operators

23. aggregate functions

24. index methods

25. procedural language

26. Forks

27. client/server model

28. Data types Unique to Postgres

    1. interval
    2. point
    3. bigserial
    4. etc…

29. Database cluster

30. Constraints

    1. UNIQUE
    2. NOT NULL
    3. PRIMARY KEY
       1. as UUID
    4. FOREIGN KEY
    5. CHECK (<condition>)
    6. - Adding & removing constraints after creating table

31. Commands

    1. list db
    2. to connect
    3. list tables
    4. Move to super
    5. list specific table
    6. List current table

32. Creating

    1. Database
    2. Table

33. Drop

    1. Drop DB
    2. Drop Table
    3. Drop constraints

34. Commands

    1. – or /* */
    2. Database migration
       1. Add, Delete, Migration
       2. Up migration
       3. Dow migration

35. Functions

    1. SELECT
       1. LIMIT
       2. FETCH
       3. OFFSET
       4. AS
       5. DISTINCT
       6. GROUP BY
          1. HAVING
          2. GROUPING SETS
          3. ROLLUP
          4. CUBE
       7. Having vs Where
       8. Limit vs Fetch
    2. FROM
    3. WHERE
       1. AND, OR
       2. LIKE, ILIKE
       3. BETWEEN
       4. IN
       5. IS NULL, IS NOT NULL
    4. ORDER BY
       1. DESC, ASC
    5. DELETE
    6. DELETING FOREIGN KEY
       1. CASCADE
    7. UPDATE
       1. SET
    8. RENAME COLUMN
    9. JOIN
       1. INNER JOIN
          1. ON
       2. LEFT JOIN
       3. RIGHT JOIN
       4. FULL JOIN (FULL OUTER JOIN)
       5. SELF JOIN
       6. CROSS JOIN
       7. NATURAL JOIN
    10. VIEWS
        1. Pros and Cons
        2. CREATE VIEW
        3. Materialized View
           1. Write amplification
    11. UNION
    12. COALESCE
    13. NULLIF
    14. Index
        1. multi index

36. AUTO_INCREMENT

37. ON CONFLICT

    1. DO NOTHING
    2. Upserting
    3. - DO UPDATE
       1. EXCLUDED

38. Date functions

    1. INTERVAL vs AGE

39. Aggregate functions

    1. AVG, MIN, MAX, SUM, ROUND, COUNT, CONCAT

40. Scalar Functions

    1. LCASE, CASE, LEN, MID, ROUND, NOW, FORMAT ,
    2. INITCAP , LEFT , RIGHT , CONCAT , ABS , CEIL , FLOOR,
    3. UPPER AND LOWER in psql.

41. Aggregate vs Scalar

42. Window function

    1. OVER
    2. - PARTITION BY, RANK, LEAD, LAG
    3. CASE

43. SQL Commands

    1. DDL
       1. CREATE, ALTER, DROP, TRUNCATE
       2. DROP vs TRUNCATE
    2. DML
       1. INSERT, SELECT, UPDATE, DELETE
    3. DCL
       GRANT, REVOKE
    4. TCL
       1. COMMIT
       2. ROLLBACK
       3. SAVE POINT
    5. DQL
       1. SELECT

44. 3-Schema architecture

    1. Internal level
    2. Conceptual level
    3. External level

45. BIGINT VS BIGSERIAL

46. Combining queries

    1. UNION, UNION ALL
    2. INTERSECT, INTERSECT ALL
    3. EXCEPT, EXCEPT ALL

47. Normalisation

    1. Levels
       1. 1NF, 2NF, 3NF etc..
       2. BCNF
    2. Anomalies
    3. - Insertion anomalies
       1. Data redundancy
       2. Missing data
    4. - Deletion anomalies
       1. Losing data
    5. - Updation anomalies
       1. inconsistency
       2. Updating values on so many records unnecessarily

48. Relationship

    1. one to one
    2. one to many
    3. many to may

49. Transaction & ACID

50. - Transaction

    1. COMMIT
    2. ROLLBACK
    3. SAVE POINT
       1. RELEASE SAVEPOINT
    4. LOCK
       1. Exclusive Locks (X-Locks)
       2. Shared Locks (S-Locks)
       3. Update Locks (U-Locks)
       4. Intent Locks
       5. Read and Write Locks

51. - ACID

    1. - Atomicity
    2. - Consistency
       1. Consistency in data
       2. Consistency in reads
    3. - Isolation
       1. Read phenomena
       2. - Dirty reads
       3. - Non-repeatable reads
       4. - Phantom reads
          1. Serialotions
       5. - (Lost updates)
       6. Isolation level
       7. - Read uncommitted
       8. - Read committed
       9. - Repeatable Reads
       10. - Transactions are Serialized
    4. - Durability
    5. How to implement ACID properties

52. EXPLAIN

53. Heap Scan

54. Parallel Scan

55. Planner

56. Other theory and functions

57. COPY

58. OLTP

59. MUCC

60. Pendings

61. Delete vs truncate

62. candidate key vs super key

63. stored procedure

64. ER diagram.

65. Practice nested queries.