Relational Databases: Working with Rows

Working with Rows

In a relational database, a row, also known as a record or tuple, represents a single, structured data item within a table. Each row contains a collection of values, where each value corresponds to a specific column defined in the table's schema. Understanding how to manipulate and query rows is fundamental to database operations.

Row Structure

Consider a table named Customers with the following columns: CustomerID (integer), FirstName (text), LastName (text), Email (text), and RegistrationDate (date).

CustomerID	FirstName	LastName	Email	RegistrationDate
101	Alice	Smith	alice.smith@example.com	2023-01-15
102	Bob	Johnson	bob.j@example.com	2023-02-20
103	Charlie	Williams	charlie.w@mail.net	2023-03-10

Each horizontal line in the table above represents a distinct row. For instance, the first row contains the data for customer Alice Smith.

Inserting Rows

To add new data to a table, you use the INSERT statement in SQL. The basic syntax is:

INSERT INTO table_name (column1, column2, column3, ...)
VALUES (value1, value2, value3, ...);

Example: Inserting a new customer

INSERT INTO Customers (CustomerID, FirstName, LastName, Email, RegistrationDate)
VALUES (104, 'Diana', 'Miller', 'diana.m@company.org', '2023-04-05');

If you are providing values for all columns in the order they are defined, you can omit the column names:

INSERT INTO Customers
VALUES (105, 'Ethan', 'Davis', 'ethan.d@service.com', '2023-05-12');

Selecting Rows

The SELECT statement is used to retrieve data from one or more tables. You can specify which columns you want and add conditions to filter which rows are returned using the WHERE clause.

SELECT column1, column2, ...
FROM table_name
WHERE condition;

Example: Selecting all customers from California (assuming an Address column)

Note: This example assumes an 'Address' column exists and contains state information.

SELECT CustomerID, FirstName, LastName
FROM Customers
WHERE Address LIKE '%CA%';

Example: Selecting a specific customer by ID

SELECT *
FROM Customers
WHERE CustomerID = 102;

The asterisk (*) is a wildcard that selects all columns in the table.

Updating Rows

The UPDATE statement allows you to modify existing data in one or more rows. It's crucial to use a WHERE clause to target specific rows; otherwise, all rows in the table will be updated.

UPDATE table_name
SET column1 = value1, column2 = value2, ...
WHERE condition;

Example: Updating a customer's email

UPDATE Customers
SET Email = 'bob.johnson.updated@example.com'
WHERE CustomerID = 102;

Deleting Rows

The DELETE statement is used to remove one or more rows from a table. Similar to UPDATE, using a WHERE clause is essential to avoid unintended data loss.

DELETE FROM table_name
WHERE condition;

Example: Deleting a customer by ID

DELETE FROM Customers
WHERE CustomerID = 103;

Example: Deleting all customers registered before a certain date

DELETE FROM Customers
WHERE RegistrationDate < '2023-01-01';

Key Concepts

Row/Record/Tuple: A single entry in a database table.
Primary Key: A column or set of columns that uniquely identifies each row.
CRUD Operations: The four basic operations: Create (INSERT), Read (SELECT), Update (UPDATE), and Delete (DELETE).