Structured Query Language (SQL) is a powerful tool for managing and manipulating relational databases. In this article, we’ll explore four fundamental SQL commands: CREATE TABLE, ALTER TABLE, INSERT INTO, and SELECT * FROM. We’ll illustrate their usage with a practical example of managing a customer database, which you can follow along in a PostgreSQL sandbox such as pgplayground.com.

1. CREATE TABLE

The CREATE TABLE statement is used to create a new table in the database. Let’s consider the following example:

CREATE TABLE customer (
    customer_id UUID PRIMARY KEY,
    first_name TEXT,
    last_name TEXT,
    email TEXT UNIQUE,
    home_phone TEXT,
    phone TEXT,
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

In this example:

• We create a table named customer.
• The table has columns for customer_id, first_name, last_name, email, home_phone, phone, and created_at.
• The customer_id column is designated as the primary key, ensuring uniqueness for each customer record.
• The email column has a unique constraint, ensuring that each email address is unique across all records.

2. ALTER TABLE

The ALTER TABLE statement is used to modify an existing table structure. Let’s say we want to make some changes to the customer table:

-- Dropping the "home_phone" column
ALTER TABLE customer DROP COLUMN IF EXISTS home_phone;

-- Adding the "address" column
ALTER TABLE customer ADD COLUMN IF NOT EXISTS address TEXT;

Here, we remove the home_phone column if it exists and add an address column to the customer table.

3. INSERT INTO

The INSERT INTO statement is used to insert new records into a table. Let’s insert some sample data into the customer table:

INSERT INTO customer (customer_id, first_name, last_name, email, phone, address) VALUES
('b1a4d84a-9f6f-4d67-8a93-6cabc213d6de', 'John', 'Doe', 'john.doe@example.com', '123-456-7890', '123 Maple St'),
('d28d8f15-5e6c-4c71-a8b2-9c6a13233f19', 'Jane', 'Smith', 'jane.smith@example.com', '987-654-3210', '456 Oak St'),
('e9d8b7d9-8176-4c5b-bcb5-a35e7a1e6d32', 'Alice', 'Johnson', 'alice.johnson@example.com', '555-123-4567', '789 Pine St');

This inserts three new customer records into the customer table, each with a unique customer_id and other relevant details.

4. SELECT * FROM

The SELECT * FROM statement is used to retrieve data from a table. Let’s fetch all records of customers with the last name “Doe”:

SELECT * FROM customer WHERE last_name = 'Doe';

Conclusion

In this article, we’ve covered the basics of creating, altering, inserting data into, and selecting data from a table using SQL. These fundamental commands form the backbone of database management and are essential for anyone working with relational databases. You can practice and follow along with these examples in a PostgreSQL sandbox such as pgplayground.com 👩‍💻.

If you found this article helpful, please give it a like 👏️ and leave a comment below! Your feedback and engagement help me create more content that benefits the community. Feel free to share your thoughts and any questions you might have. Happy coding!