SQL Refresher : Part - III

Welcome back to the SQL Refresher series! In this post, we’re diving into the following SQL concepts—how to DELETE and UPDATE records, understand DELETE rules, explore smart table design with normalization (1NF), and learn how to ALTER tables.

1. DELETE and UPDATE Statements

DELETE Statement

The DELETE statement removes rows from a table based on a condition.

Example – Delete galaxies that are not observable:

DELETE FROM Galaxies
WHERE is_observable = false;

Be careful! If you omit the WHERE clause, all rows will be deleted:

DELETE FROM Galaxies;

What You CANNOT Do:

• You can’t delete specific columns (SQL doesn't support partial deletes).

• Cannot delete rows from a view unless it’s updatable.

• Cannot delete if there are foreign key constraints blocking the operation unless:

  • You cascade deletes

  • Or manually delete related child records first

UPDATE Statement

The UPDATE statement modifies existing records in a table.

Example – Update the status of galaxies in the Virgo cluster:

UPDATE Galaxies
SET classification = 'Elliptical'
WHERE cluster_name = 'Virgo';

Always use a WHERE clause to avoid unintended changes.

2. Smart Table Design: 1NF, Atomicity & Primary Keys

Designing good tables avoids redundancy, improves performance, and ensures consistency.

First Normal Form (1NF)

A table is in 1NF if:

• It has a primary key

• All columns contain atomic (indivisible) values

• No repeating groups or arrays

Primary Key Rules

• Must be unique and not null

• Only one primary key per table

• Can span multiple columns (composite key)

Example:

CREATE TABLE Galaxies (
    galaxy_id INT PRIMARY KEY,
    name VARCHAR(100) NOT NULL,
    type VARCHAR(50),
    age_in_million_years INT
);

3. ALTER TABLE – Change Structure on the Fly

The ALTER TABLE command lets you modify an existing table.

ADD Column

ALTER TABLE Galaxies
ADD distance_light_years BIGINT;

MODIFY Column

ALTER TABLE Galaxies
MODIFY distance_light_years INT;

CHANGE Column

ALTER TABLE Galaxies
CHANGE COLUMN name galaxy_name VARCHAR(100);

DROP Column

ALTER TABLE Galaxies
DROP COLUMN type;

Be cautious—DROP deletes the column and all its data permanently.

Wrapping Up

In this blog, we covered:

• How to update and delete records

• Rules and constraints with DELETE

• Smart design with 1NF, atomic columns, and proper primary keys

• The power of ALTER TABLE to evolve your schema

Keep your tables clean, atomic, and cosmic!