SQL Refresher : Part - I

Exploring the Universe with SQL: A Galactic Guide

Welcome to an exciting SQL refresher! Imagine we are building a universe database to store details about different solar systems and their celestial bodies. This refresher covers database fundamentals with examples.

1. What is a Database?

A database is an organized collection of data that allows efficient storage and retrieval.

Within a database there are:

• Tables: Store data in structured formats, similar to spreadsheets.

• Columns: Define data attributes (e.g., planet name, mass, discovery date).

• Rows: Hold individual records of data.

Our universe database will have a solarsystem table to store details about different solar systems.

2. Creating a Database

We first need to create a database to store our cosmic data:

CREATE DATABASE universe;

3. Using the Database

Before working with tables, we must select our newly created database:

USE universe;

4. Creating the Solar System Table

Let’s create a solarsystem table to store data about different solar systems.

CREATE TABLE solarsystem (
    id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    galaxy VARCHAR(100) DEFAULT 'Milky Way',
    num_planets INT,
    mass DECIMAL(10,2),
    discovery_date DATE,
    last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP,
    description BLOB
);

5. Understanding Data Types

Here’s a breakdown of the data types:

• VARCHAR(n): Variable-length string (e.g., VARCHAR(100) for solar system names).

• CHAR(n): Fixed-length string (e.g., CHAR(2) for planet codes).

• DECIMAL(10,2): Numeric data with precision (e.g., DECIMAL(10,2) for mass of a system).

• NUMERIC(precision, scale): Similar to DECIMAL, used for fixed-point arithmetic (e.g., NUMERIC(8,3) for planetary distances).

• FLOAT: Approximate numeric data type for scientific measurements (e.g., FLOAT(8) for gravitational pull values).

• INT: Whole number values (e.g., num_planets).

• DATE: Stores only the date (YYYY-MM-DD format).

• TIMESTAMP: Stores both date and time, useful for tracking updates.

• BLOB: Used for storing large binary data (e.g., images, descriptions).

  ---

6. Describing the Table

To check the structure of our solarsystem table:

DESCRIBE solarsystem;

7. Dropping a Table

If needed, we can delete our table using:

DROP TABLE solarsystem;

8. Inserting Data into the Table

We can insert records in multiple ways:

Basic Insert

INSERT INTO solarsystem (id, name, num_planets, mass, discovery_date)
VALUES (1, 'Alpha Centauri', 3, 4.32, '2015-07-14');

Inserting Only Specific Columns

INSERT INTO solarsystem (id, name) VALUES (2, 'TRAPPIST-1');

Inserting Multiple Rows

INSERT INTO solarsystem (id, name, num_planets) 
VALUES (3, 'Kepler-90', 8),
       (4, 'Proxima Centauri', 1);

Variations on an Insert Statement

Changing the Order of Columns While Inserting

Note: As long as the values match the specified column names, the order of columns in the INSERT statement can be changed freely.

INSERT INTO solarsystem (name, id, num_planets)
VALUES ('Gliese 581', 5, 4);

Omitting Column Names (Not Recommended)

Note: When omitting column names, all values must be provided in the exact same order as defined in the table schema.

INSERT INTO solarsystem
VALUES (6, 'Wolf 1061', 'Milky Way', 3, 2.34, '2009-02-16', DEFAULT, NULL);

Inserting a Few Columns and Leaving Some Out

INSERT INTO solarsystem (id, name, num_planets)
VALUES (7, 'Luyten's Star', NULL);

9. Understanding NULL and NOT NULL

• NULL means the value is unknown or missing.

• NOT NULL ensures a value must always be provided.

• Example: The name column in our table is NOT NULL, meaning every solar system must have a name.

10. Using DEFAULT in CREATE TABLE

We used DEFAULT in our table definition:

galaxy VARCHAR(100) DEFAULT 'Milky Way',
last_updated TIMESTAMP DEFAULT CURRENT_TIMESTAMP

If no value is given, these defaults will be used automatically.

11. Understanding Views

A view is a virtual table that does not store data but represents a saved SQL query. Views help simplify complex queries and improve security by restricting access to specific columns.

Example: Creating a View

Suppose we want a view that only displays solar systems with more than one planet:

CREATE VIEW multi_planet_systems AS
SELECT id, name, num_planets FROM solarsystem WHERE num_planets > 1;

Querying a View

We can retrieve data from the view just like a regular table:

SELECT * FROM multi_planet_systems;

Note: Since views do not store data, any updates to the original table reflect immediately in the view.

Conclusion

This refresher covered the basics of databases, tables, data types, inserting data, and handling defaults—all while exploring the universe.

References: Head First SQL[Book]