Database Fundamentals

A database is an organized collection of data that is stored, managed, and retrieved electronically, and a Database Management System (DBMS) is the software layer that sits between an application and that stored data. Instead of an application reading and writing raw files directly, it talks to a DBMS, which handles storage, retrieval, concurrent access, security, and consistency on the application's behalf.

Before DBMSs became standard, applications stored data in flat files (plain text or binary files), and every program that needed the data had to implement its own logic for searching, updating, and preventing corruption when two processes wrote at once. This led to duplicated logic, inconsistent data, and fragile systems. A DBMS solves this once, centrally, so every application built on top of it inherits reliable data handling for free.

Why it matters

Almost every application you will build stores its data in a database: users, orders, messages, payments. A database keeps that data safe when the program stops, lets many users work at once, and answers questions about the data quickly. SQL is the language for talking to it, and it has stayed essentially the same for decades.

What a DBMS Actually Does

A DBMS provides a defined set of guarantees and services so that applications do not need to reinvent them.

• Data storage and retrieval — organizes data on disk and provides efficient ways to find it (indexes, query optimization).
• Concurrency control — allows many users or processes to read and write data at the same time without corrupting it.
• Data integrity — enforces rules (constraints) so invalid data cannot be saved, such as a negative age or a duplicate email.
• Security and access control — restricts which users or applications can read or modify which data.
• Backup and recovery — protects against data loss from crashes, power failure, or hardware faults.

Relational Databases and SQL

A relational database organizes data into tables made of rows and columns, based on the relational model proposed by E. F. Codd in 1970. SQL (Structured Query Language) is the standard language used to define, query, and manipulate data in a relational database. Popular relational database systems include PostgreSQL, MySQL, Microsoft SQL Server, and Oracle Database — all of them understand a large common core of standard SQL, even though each adds its own extensions.

SQL Databases vs. NoSQL Databases

SQL (relational) databases enforce a fixed schema — every row in a table has the same columns and column types — and they excel at representing structured data with clear relationships, using SQL to query across tables. NoSQL databases (such as document, key-value, or graph databases) relax or remove the fixed schema, trading some consistency guarantees for flexibility and horizontal scalability. Neither is universally "better" — relational databases are usually the right default for data with clear structure and relationships, such as orders, customers, and inventory, while NoSQL options often fit unstructured or extremely high-volume data better.

Example

CREATE TABLE employees (
    id INTEGER PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    department VARCHAR(50)
);

INSERT INTO employees (id, name, department)
VALUES (1, 'Asha Rao', 'Engineering');

SELECT * FROM employees;

Returns 1 row: (1, Asha Rao, Engineering)

Exercise

Create a books table with an integer primary key id, a title that cannot be empty, and a numeric price. Insert two books: Clean Code at 450 and SQL Basics at 90. Then select the titles of the books that cost more than 100.

Expected output: title then Clean Code

-- TODO: create the books table (id, title, price)

-- TODO: insert the two books

-- TODO: select the title of each book priced above 100

CREATE TABLE books (
  id INTEGER PRIMARY KEY,
  title TEXT NOT NULL,
  price INTEGER
);

INSERT INTO books (id, title, price) VALUES (1, 'Clean Code', 450), (2, 'SQL Basics', 90);

SELECT title FROM books WHERE price > 100;

Quick quiz

Interview questions