SQL Beginner

Database

A database is an organized collection of structured information or data, typically stored electronically in a computer system. Let me provide you with more details:

Definition:

A database consists of data that is logically organized and efficiently managed. It can store various types of information, such as text, numbers, images, and more. Databases are usually controlled by a Database Management System (DBMS).

Key Features:

Structured Data: Data within databases is typically modeled in rows and columns, organized into tables for efficient processing and querying. Access and Management: Databases allow easy access, modification, and organization of data. SQL: Most databases use Structured Query Language (SQL) for writing and querying data.

Evolution of Databases:

1960s - Hierarchical Databases: Organized data in tree-like structures (e.g., IBM's IMS).

Late 1960s to early 1970s - Network Databases: Introduced many-to-many relationships (network model).

1970s - Relational Databases: Edgar F. Codd's relational model with tables, keys, and relationships (e.g., Oracle, MySQL).

1970s - SQL (Structured Query Language): Standard language for relational databases.

1980s to 1990s - Object-Oriented Databases: Stored data in objects (OODBMS).

2000s onwards - NoSQL Databases: Scalable, flexible solutions for unstructured data (e.g., MongoDB, Cassandra).

2010s onwards - NewSQL Databases: Blend of SQL and NoSQL features for high-throughput workloads.

Present - Cloud and Serverless Databases: Scalable, managed solutions in the cloud with serverless options (e.g., AWS RDS, Azure SQL Database, Google Cloud Spanner).

Difference Between Databases and Spreadsheets:
Databases:

Designed for large collections of organized information.

Allow multiple users simultaneous access using complex logic and language. Spreadsheets (e.g., Microsoft Excel):

Originally designed for single users.

Suitable for simpler data manipulation.

DBMS

Database Management System (DBMS) acts as an interface between the database and end-users.

It is software used to store, retrieve, define, and manage data in a database.

Supports easy CRUD operations (Create, Read, Update, Delete).

Manages authentication, concurrency, logging, backup, optimization, etc.

Types of Databases:

Hierarchical databases

Network databases

Object-oriented databases

Relational databases

Cloud Database

Centralized Database

Operational Database

NoSQL databases

Relational Data Base
A Relational Database is a type of database that organizes data into tables with predefined relationships between them. This type of database follows the relational model, which was first proposed by Edgar F. Codd in 1970. Relational databases are based on the principles of relational algebra and use Structured Query Language (SQL) for querying and managing data.

Key features of relational databases include:
Tables: Data is stored in tables, where each table represents an entity (e.g., customers, products, orders) and consists of rows and columns. Each row in a table represents a record or instance of the entity, and each column represents an attribute or property of the entity.

Relationships: Relational databases establish relationships between tables using keys. The primary key uniquely identifies each record in a table, while foreign keys establish links between related tables. Common types of relationships include one-to-one, one-to-many, and many-to-many.

Normalization: Relational databases use normalization techniques to reduce redundancy and improve data integrity. Normalization involves organizing data into multiple related tables to avoid data duplication and inconsistencies.

ACID Properties: Relational databases adhere to the ACID properties, which ensure data consistency, reliability, and atomicity. ACID stands for Atomicity, Consistency, Isolation, and Durability.

Structured Query Language (SQL): Relational databases use SQL for querying and manipulating data. SQL allows users to perform operations such as selecting, inserting, updating, and deleting data, as well as defining database structures and constraints.

Examples of popular relational database management systems (RDBMS) include:

MySQL: An open-source RDBMS widely used for web applications and small to medium-sized databases.

PostgreSQL: An open-source RDBMS known for its advanced features, scalability, and extensibility.

Oracle Database: A commercial RDBMS known for its robustness, scalability, and enterprise-level capabilities.

Microsoft SQL Server: A commercial RDBMS developed by Microsoft, commonly used in Windows-based environments and enterprise applications.

SQLite: A lightweight, embedded RDBMS suitable for mobile apps, IoT devices, and small-scale applications.

SQL?
SQL stands for Structured Query Language is a computer language that we use to interact with a relational database. SQL is a tool for organizing, managing, and retrieving archived data from a computer database. The original name was given by IBM as Structured English Query Language, abbreviated by the acronym SEQUEL.

When data needs to be retrieved from a database, SQL is used to make the request. The DBMS processes the SQL query retrieves the requested data and returns it to us. Rather, SQL statements describe how a collection of data should be organized or what data should be extracted or added to the database.

SQL Data Base
Mysql ⇒ Open Source

SQL Server ⇒ Microsoft

Oracle ⇒ IBM

PostgreSQL ⇒ Opensource

SQL REQUIRES

/*
  multiline command line
 /*

 -- single command line

DataTypes

Character Data

char - eg:char(5) stores fixed length string of length 5. Max 255 bytes.

varchar - eg:varchar(5) stores variable length string of length 5. Max 65535 bytes.

SHOW CHARACTER SET; -- shows various character sets that are supported.

latin1 is the default character set. We can also choose a specific character set like below,

varchar(10) character set utf8    -- the particular column is set to utf8
create database foreign_sales character set utf8;   -- entire database is set to utf8

Text Data

All images in the section are from Learning SQL by Alan Beaulieu

BLOB - Binary Large Object File → TinyBlob, Blob, MediumBlob, LongBlob

Numerical Data

Whole Numbers:BLOB - Binary Large Object File → TinyBlob, Blob, MediumBlob, LongBlob

Numerical Data

Whole Numbers:

Tinyint is used to store Bool or Boolean

Decimal Numbers: