Database
"Customers might have up to nine different database types, and many instances of each. A data fabric brings order to those data silos."
— Edward Calvesbert VP of Product Management, watsonx platform software ІВМ
SQL vs NoSQL
| SQL (Relational) | NOSQL (Non-Relational) |
|---|---|
| Vertically Scalable | Horizontally Scalable |
| Follows ACID property | Follows CAP property |
| Tables with fixed rows and column | JSON documents, Key-value pairs, tables with rows and dynamic column, nodes and edges |
| Vertical Scaling (Scale-up with a larger server) | Horizontal Scaling (Scale-out across commodity server) |
| Strict Schema | Dynamic Schema |
| Ideal choice for complex query | Not a good fit for complex query |
| Oracle, MySQL, Microsoft SQL Server, IBM DB2, PostgreSQL, MariaDB | MongoDB, CassandraDB, DynamoDB, HBase, Neo4j, Redis, Firebase |
| Structured Data Type (ie. online transaction processing data) | Unstructured Data type (molecular structure data) |
| Tabular Format | Hierarchical Format |
| Stores relationship of values | Don't store relationship of values |
Practice SQL easily
🔗 https://sqlzoo.net
🔗 https://w3resource.com/sql-exercises/
🔗 https://codewars.com
🔗 https://hackerrank.com
🔗 https://sqlbolt.com
🔗 SQL Cheatsheet
Basic SQL commands
- SELECT: Select data from database
- FROM: Specify table we're puling from
- WHERE: Filter query to match a condition
- AS: Rename column or table with alias
- JOIN: Combine rows from 2 or more tables
- AND: Combine query conditions. All must be met
- OR: Combine query conditions. One must be met
- LIMIT: Limit rows returned. See also FETCH & TOP
- IN: Specify multiple values when using WHERE
- CASE: Return value on a specified condition
- IS NULL: Return only rows with a NULL value
- LIKE: Search for patterns in column
- COMMIT: Write transaction to database
- ROLLBACK: Undo a transaction block
- ALTER TABLE: Add/Remove columns from table
- UPDATE: Update table data
- CREATE: Create TABLE, DATABASE, INDEX or VIEW
- DELETE: Delete rows from table
- INSERT: Add single row to table
- DROP: Delete TABLE, DATABASE or INDEX
- GROUP BY: Group data into logical sets
- ORDER BY: Set order of result. Use DESC to reverse order
- HAVING: Same as WHERE but filters groups
- COUNT: Count number of rows
- SUM: Return sum of column
- AVG: Return average of column
- MIN: Return min value of column
- MAX: Return max value of column
How to learn SQL
Database Selection Process
Top 6 database models
Top 10 most popular Open-Source Databases
This list is based on factors like adoption, industry impact, and the general awareness of the database among the developer community.
- MySQL
- PostgreSQL
- MariaDB
- Apache Cassandra
- Neo4j
- SQLite
- CockroachDB
- Redis
- MongoDB
- Couchbase
Database Scaling Cheatsheet
Data structures
What are the data structures used in daily life?
A good engineer needs to recognize how data structures are used in our daily lives.
- list: keep your Twitter feeds
- stack: support undo/redo of the word editor
- queue: keep printer jobs, or send user actions in-game
- heap: task scheduling
- tree: keep the HTML document, or for AI decision
- suffix tree: for searching string in a document
- graph: for tracking friendship, or path finding
- r-tree: for finding the nearest neighbor
- vertex buffer: for sending data to GPU for rendering
To conclude, data structures play an important role in our daily lives, both in our technology and in our experiences. Engineers should be aware of these data structures and their use cases to create effective and efficient solutions.
What Happens When a SQL is Executed?
SQL cheat sheet
SQL Joins explained
SQL Query Execution Order
Database categories
Types of Database
Database Sharding
Database sharding is the process of storing a large database across multiple machines. A single machine, or database server, can store and process only a limited amount of data. Database sharding overcomes this limitation by splitting data into smaller chunks, called shards, and storing them across several database servers. All database servers usually have the same underlying technologies, and they work together to store and process large volumes of data.
Database locks
Here are the common types of locks used in databases:
Shared Lock (S Lock): It allows multiple transactions to read a resource simultaneously but not modify it. Other transactions can also acquire a shared lock on the same resource.
Exclusive Lock (X Lock): It allows a transaction to both read and modify a resource. No other transaction can acquire any type of lock on the same resource while an exclusive lock is held.
Update Lock (U Lock): It is used to prevent a deadlock scenario when a transaction intends to update a resource.
Schema Lock: It is used to protect the structure of database objects.
Bulk Update Lock (BU Lock): It is used during bulk insert operations to improve performance by reducing the number of locks required.
Key-Range Lock: It is used in indexed data to prevent phantom reads (inserting new rows into a range that a transaction has already read).
Row-Level Lock: It locks a specific row in a table, allowing other rows to be accessed concurrently.
Page-Level Lock: It locks a specific page (a fixed-size block of data) in the database.
Table-Level Lock: It locks an entire table. This is simple to implement but can reduce concurrency significantly.
7 Must-know Strategies to Scale Your Database
Cheatsheet on Relational Database Design
A relational database is a type of database that organizes data into structured tables, also known as relations. These tables consist of rows (records) and columns (fields).
