Managing tables is a fundamental skill in any relational database. With SQL, you can efficiently create, modify, and manipulate tables to store and organize data. In this guide, you’ll learn step by step how to create tables in SQL, add primary and foreign keys, and perform basic operations like inserting, updating, and deleting data.

1. What is a Table in SQL?

A table in SQL is a structure that organizes data into rows and columns. Each row represents an entity, and each column represents an attribute or characteristic of that entity. For example, a table named Customers might have columns like First Name, Last Name, Email, and Phone, with each row representing a different customer.

Advantages of using tables in SQL:

• Clear and structured organization of data.
• Easily defined relationships through primary and foreign keys.
• Fast and efficient queries using SQL.

2. How to create a Table in SQL

Creating a table is the first step in designing a database. To do this, we use the CREATE TABLE command.

Basic example of table creation:

CREATE TABLE Customers (
CustomerID INT PRIMARY KEY,
FirstName VARCHAR(50),
LastName VARCHAR(50),
Email VARCHAR(100),
Phone VARCHAR(15)
);

Code Explanation:

• CREATE TABLE is the command we use to create a new table.
• Customers is the name of the table.
• CustomerID INT PRIMARY KEY defines the CustomerID column as an integer (INT) and the primary key, meaning each value must be unique.
• The columns FirstName, LastName, Email, and Phone are defined with data types that specify the maximum length of the values (e.g., VARCHAR(50) means a maximum of 50 characters).

3. How to add Primary and Foreign Keys

Primary and foreign keys are essential for establishing relationships between tables.

Primary Key:

A primary key uniquely identifies each row in a table. In the example above, the CustomerID column is the primary key.

Foreign Key:

A foreign key creates a relationship between two tables. For example, if we have an Orders table, the foreign key CustomerID will connect orders to customers.

Example of a table with a foreign key:

CREATE TABLE Orders (
OrderID INT PRIMARY KEY,
OrderDate DATE,
CustomerID INT,
FOREIGN KEY (CustomerID) REFERENCES Customers(CustomerID)
);

Explanation:

• FOREIGN KEY (CustomerID) creates a relationship with the Customers table, ensuring that each value in CustomerID in the Orders table exists in the Customers table.

4. How to insert data into a Table

To add data to a table, we use the INSERT INTO command.

Example of inserting data:

INSERT INTO Customers (CustomerID, FirstName, LastName, Email, Phone)
VALUES (1, ‘John‘, ‘Doe‘, ‘john.doe@example.com‘, ‘555-1234‘);

Explanation:

• INSERT INTO is used to add a new row to the Customers table.
• The values are inserted into the columns in the same order.

5. How to update data in a Table

If you need to change the values of an existing row, use the UPDATE command.

Example of updating data:

UPDATE Customers
SET Email = ‘new.email@example.com‘
WHERE CustomerID = 1;

Explanation:

• UPDATE updates the Customers table.
• SET defines the new information.
• WHERE specifies which row will be updated (in this case, the row with CustomerID = 1).

6. How to delete data from a Table

To remove data from a table, we use the DELETE command.

Example of deleting data:

DELETE FROM Customers
WHERE CustomerID = 1;

Explanation:

• DELETE FROM removes the row from the table.
• WHERE ensures that only the row with CustomerID = 1 is deleted.

7. Best practices for SQL Table design

1. Normalization: Avoid data redundancy by breaking down tables into smaller entities.
2. Use descriptive names: Ensure that table and column names are clear and easy to understand.
3. Indexes: Add indexes to columns that are frequently used in queries to improve performance.
4. Use foreign keys: Whenever possible, use foreign keys to ensure referential integrity between tables.

8. Common SQL queries for manipulating Tables

SQL queries allow you to extract and manipulate data efficiently. Here are some examples:

Select Query (SELECT):

SELECT FirstName, LastName
FROM Customers
WHERE Phone = ‘555-1234‘;

Join Query to relate tables:

SELECT Customers.FirstName, Orders.OrderDate
FROM Customers
JOIN Orders ON Customers.CustomerID = Orders.CustomerID;

Conclusion

Now that you know the basics of how to create and manipulate tables in SQL, you can start applying these techniques to your own projects. Always design your tables with efficiency and scalability in mind. Consistent practice will help you become an expert in relational databases!

If you have any questions or want to share your experience, leave us a comment.

Recommended Book:

Aprende SQL en un fin de semana

Tutorial: How to create and manipulate SQL Tables – Platita Software
Entity Relationship Blog
Link your Data!!