Database Related

SQL VS NoSQL

My-SQL-Query-Examples

Normalization VS Denormalization

DB briefs:

SQLite: store entire DB in 1 file locally without connecting to server. Phone, MP3

MYSQL: easy, cheap, reliable, supported by community, web w PHP; poor scaling performance

PostgreSQL: above advantage + non-complex config foreign key support; slower

Oracle DB: large applications, specific features for banks, expensive.

SQLServer: expensive, large enterprise. Free EXPRESS, windows only.

UNION(distinct) VS UnionALL, Intersect, Except

Union (same colume #s, combine/append data rows) into, say single column for same data type

Join: retrieve data from >=2 tables by logical relationships. Columns aligned next to each other

Atomic, Consistent, Isolation, Durability

WHERE and HAVING

Both used for filtering conditions. Having is used when there are aggregate functions such as ‘Count’ or group by conditions. Where is used to filtering out unmatched data rows, and having is used to filter summarized or grouped data.

Primary key vs foreign key:

Foreign key Name could be inconsistent. Foreign key could have null, duplicates, parent-child relationship, generate Referential integrity, works as links between tables.

Truncate vs delete

Truncate: fast, delete entire row, large dataset. DDefineL

Delete: WHERE. Slower as lock and logging. Fire trigger. Try batch delete. Selective data DML

View vs Table

View is virtual table that stores frequently queried data, and that does not hold actual data. The space is saved and changes are not permitted. Security is achieved as can just select non-sensitive columns.