Introduction to SQL & Databases Basics

What is a Database?

A database is an organized collection of structured information stored electronically in a computer system. Databases allow for efficient storage, retrieval, and manipulation of data.

Learning Objectives

By the end of this lesson, you will be able to:

1. Explain what a database is and why we use databases.

2. Describe differences between RDBMS and NoSQL systems.

3. Understand tables, rows, columns, and schemas.

4. Explain the difference between SQL, MySQL, PostgreSQL, and SQL Server.

5. Install and connect to a relational database.

6. Write basic SQL queries using SELECT and FROM.

7. Retrieve and filter data from a single table.

RDBMS vs NoSQL

RDBMS (Relational Database Management System)

• Stores data in tables with rows and columns

• Uses SQL (Structured Query Language)

• Enforces ACID properties (Atomicity, Consistency, Isolation, Durability)

• Examples: MySQL, PostgreSQL, SQL Server, Oracle

NoSQL (Not Only SQL)

• Flexible schema design

• Handles unstructured/semi-structured data

• Better for large-scale distributed systems

• Types: Document, Key-Value, Graph, Column-family

• Examples: MongoDB, Cassandra, Redis

Database Components

Tables: Collections of related data (e.g., "Users", "Products")
Rows: Individual records in a table
Columns: Attributes or fields defining the data structure
Schema: The blueprint defining tables, relationships, and constraints

SQL vs Database Systems

• SQL: Standard language for managing relational databases

• MySQL: Popular open-source RDBMS

• PostgreSQL: Advanced open-source RDBMS with more features

• SQL Server: Microsoft's enterprise RDBMS

• SQLite: Lightweight, file-based database (no server required)

Installing a Database

SQLite (Recommended for beginners):

bash

# Usually comes pre-installed, or download from sqlite.org

MySQL:

bash

# Ubuntu/Debian
sudo apt-get install mysql-server

# macOS
brew install mysql

# Windows: Download MySQL Installer

PostgreSQL:

bash

# Ubuntu/Debian
sudo apt-get install postgresql postgresql-contrib

# macOS
brew install postgresql

# Windows: Download PostgreSQL Installer

Basic SQL Syntax

SELECT Statement

The SELECT statement retrieves data from one or more tables.

Basic Syntax:

sql

SELECT column1, column2, ...
FROM table_name;

Examples with Single Table

Sample Users Table:

1. Select all columns:

sql

SELECT * FROM Users;

2. Select specific columns:

sql

SELECT name, email FROM Users;

3. Select with column aliases:

sql

SELECT name AS "Full Name", email AS "Email Address" FROM Users;

4. Select with expressions:

sql

SELECT name, age, age + 5 AS "Age in 5 years" FROM Users;

5. Select distinct values:

sql

SELECT DISTINCT city FROM Users;

6. Select with calculated columns:

sql

SELECT name, age, 
       CASE 
           WHEN age < 30 THEN 'Young'
           ELSE 'Adult'
       END AS age_group
FROM Users;

Practice Exercise

Create a simple table and practice SELECT statements:

sql

-- Create a sample table
CREATE TABLE Employees (
    id INTEGER PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    department TEXT,
    salary DECIMAL(10,2),
    hire_date DATE
);

-- Insert sample data
INSERT INTO Employees VALUES 
(1, 'John', 'Doe', 'IT', 75000, '2020-01-15'),
(2, 'Jane', 'Smith', 'HR', 65000, '2019-03-20'),
(3, 'Mike', 'Johnson', 'IT', 80000, '2018-07-10'),
(4, 'Sarah', 'Wilson', 'Finance', 70000, '2021-05-30');

-- Practice queries
SELECT * FROM Employees;
SELECT first_name, last_name, department FROM Employees;
SELECT first_name || ' ' || last_name AS full_name FROM Employees;
SELECT department, AVG(salary) AS avg_salary FROM Employees GROUP BY department;