CodeBlanks Logo Icon CodeBlanks
Login Register

SQL vs NoSQL

Compare SQL and NoSQL.

NoSQL comparison

NoSQL covers non-relational databases: document, key-value, column-family, graph. SQL demands a strict structure; NoSQL offers a flexible one. The CAP theorem limits you to two of three guarantees.

SELECT data->>'name' FROM documents
WHERE data->>'status' = 'active';

NoSQL offers flexible structure.

data->>'name' is selected from documents. WHERE keeps only the rows where data->>'status' = 'active' holds.

NoSQL comparison — details

CAP: consistency, availability, partition tolerance. During a network partition you pick two. SQL picks consistency + partition tolerance; most NoSQL picks availability + partition tolerance.

Practice NoSQL comparison

Select data->>'name' from the documents table; return only active documents.

Starter code

query.sql
-- NoSQL query
SELECT

Sample solution

query.sql
SELECT data->>'name' FROM documents WHERE data->>'status' = 'active';

Expected output

name
Ayşe
Mehmet

Questions and answers

Review the lesson questions together with their answers. To test yourself, solve the lesson interactively.

1 Document access

Question 
SELECT data____'name' FROM documents
Answer 
SELECT data->>'name' FROM documents

Explanation: ->> returns a JSON field as text, while -> returns it as JSON.

2 SQL vs NoSQL: match the terms

Term Meaning
NoSQL non-relational
document document
CAP three guarantees
key-value key-value

3 How many guarantees does CAP limit?

  • Both of them
  • All of them
  • Only one of them
  • Two of the three Correct answer

Explanation: CAP limits you to two of three guarantees.

All lessons in Expert

1 Database design

  1. ER model
  2. Cardinality
  3. Surrogate keys
  4. Natural keys
  5. Denormalization
  6. Summary - Design

2 Index tuning

  1. B-tree structure
  2. Covering index
  3. Partial index
  4. Index selectivity
  5. Index maintenance
  6. Summary - Index tuning

3 Query optimizer

  1. What is an optimizer?
  2. Statistics
  3. Cost model
  4. Join strategies
  5. Plan cache
  6. Summary - Optimizer

4 Project: Performance tuning

  1. Scenario design
  2. Reading plans
  3. Adding indexes
  4. Rewriting queries
  5. Comparing results

5 Replication and sharding

  1. What is replication?
  2. Master-slave
  3. Sync vs async
  4. What is sharding?
  5. Shard key
  6. Summary - Replication and sharding

6 Distributed and big data

  1. Distributed queries
  2. MPP
  3. Columnar storage
  4. SQL on Hadoop
  5. SQL vs NoSQL
  6. Summary - Distributed and big data

7 Security and backup

  1. SQL injection
  2. Parameterised queries
  3. Authorisation
  4. Backup
  5. Recovery
  6. Summary - Security and backup

8 Expert final

  1. Design challenge
  2. Optimization challenge
  3. Security challenge
  4. Backup challenge