SQL: Comparing SQL and NoSQL and getting started with Datatypes

SQL: Comparing SQL and NoSQL and getting started with Datatypes

Akshobya KL's photo
Akshobya KL
·

3 min read

Common difference between SQL and NoSQL

1. Data Model

  • SQL: Relational databases use structured data organized in tables (rows and columns). Each row represents a record, and each column represents an attribute of that record. These databases follow a strict schema.

  • NoSQL: NoSQL databases store data in various formats like key-value pairs, documents, graphs, or wide-column stores. They provide more flexibility as they don’t require a predefined schema.

2. Schema

  • SQL: Schema-based, meaning the structure (tables, fields, data types) is defined upfront and must be followed. Changes to the schema can be complex.

  • NoSQL: Schema-less or flexible schemas, allowing for dynamic changes in data structure. This makes it easier to modify the data model without needing extensive migrations.

3. Scalability

  • SQL: Typically scales vertically (by adding more powerful hardware). Horizontal scaling (adding more servers) is challenging due to the relational nature of the data.

  • NoSQL: Designed for horizontal scaling (distributing data across multiple servers), making it more suitable for large-scale, distributed systems.

4. ACID vs. CAP

  • SQL: Adheres to ACID properties (Atomicity, Consistency, Isolation, Durability), ensuring high data integrity and reliability in transactions.

  • NoSQL: Often follows the CAP theorem (Consistency, Availability, Partition Tolerance), where systems may prioritize two of these over the third. For example, NoSQL databases may sacrifice consistency for higher availability.

5. Query Language

  • SQL: Uses Structured Query Language (SQL) to query and manipulate data. It is a standard language with well-defined syntax.

  • NoSQL: Different databases have their own query mechanisms, such as MongoDB's query language or using RESTful APIs. Some NoSQL databases also support SQL-like querying.

6. Use Cases

  • SQL: Ideal for applications requiring complex queries, transactions, and data integrity, such as financial systems, inventory management, and CRM systems.

  • NoSQL: Suitable for applications that require flexibility, scalability, and handle large volumes of unstructured or semi-structured data, such as real-time analytics, content management, and big data applications.

7. Examples

  • SQL: MySQL, PostgreSQL, SQLite, Oracle, Microsoft SQL Server.

  • NoSQL: MongoDB (document), Redis (key-value), Cassandra (wide-column), Neo4j (graph).

SQL Data types

1. Integer Types

  • INT: Stores whole numbers, typically up to 4 bytes.

  • TINYINT: Stores very small integers, typically 1 byte.

  • SMALLINT: Stores small integers, typically 2 bytes.

  • BIGINT: Stores large integers, typically up to 8 bytes.

  • DECIMAL / NUMERIC: Stores exact numeric values with defined precision and scale (useful for financial calculations).

2. String Types

  • VARCHAR(n): Stores variable-length strings with a defined maximum length (n). Commonly used for text data where the length can vary.

  • CHAR(n): Stores fixed-length strings. If the string is shorter than n characters, it will be padded with spaces.

  • TEXT: Stores large variable-length strings, typically used for long text data like descriptions or blog posts.

  • NVARCHAR(n): Like VARCHAR but supports Unicode characters, useful for internationalization (multilingual data).

  • BLOB: Binary Large Object used to store binary data like images, audio, or files.

3. Date and Time Types

  • DATE: Stores a date in the format YYYY-MM-DD.

  • TIME: Stores time in the format HH:MM:SS.

  • DATETIME: Stores both date and time in the format YYYY-MM-DD HH:MM:SS.

  • TIMESTAMP: Stores a Unix timestamp (seconds since 1970-01-01), commonly used for tracking changes (e.g., created_at, updated_at).

  • YEAR: Stores a year in either 2 or 4 digits (YY or YYYY).

4. Boolean Type

  • BOOLEAN (or BOOL): Typically stores TRUE or FALSE values. In some systems (like MySQL), TRUE is treated as 1 and FALSE as 0.

5. Floating Point Types

  • FLOAT: Stores floating-point numbers, typically 4 bytes, used for approximate values.

    DOUBLE: Similar to FLOAT, but with double the precision (8 bytes).

    DECIMAL(p, s) / NUMERIC(p, s): Fixed-point numbers with precision (p) and scale (s). Often used for exact calculations, like financial data.

Creating Database

  • Use CREATE database test command to create a database

  • CREATE DATABASE database_name;

  •   CREATE database record_company;
  USE record_company;
  create table test (
  test_column INT 
  );
  alter table test
  add another_column varchar(255);
  drop table test;
SQL