5.3 Summarize methods used to interface with databases.

📘CompTIA ITF+ (FC0-U61)

Relational databases are commonly managed using Structured Query Language (SQL). SQL allows users, applications, and administrators to interact with the database, such as retrieving data, adding new data, modifying existing data, and controlling the structure of the database.

Relational methods are usually divided into two main categories:

Data Manipulation
Data Definition

Each category contains several SQL commands that allow interaction with the database.

1. Data Manipulation

Data Manipulation refers to the process of working with the data stored inside database tables.
These commands allow users or applications to retrieve, add, remove, or modify records.

These operations are often called DML (Data Manipulation Language) commands.

The main data manipulation commands include:

SELECT
INSERT
DELETE
UPDATE

SELECT

The SELECT command is used to retrieve data from a database table.

It allows a user or application to view specific information stored in the database.

Key Points

Retrieves data from one or more tables.
Can display all records or only selected records.
Can filter results using conditions.
Often used by applications to display information to users.

Example (IT Environment)

A company stores user account information in a table called Users.

An administrator may run a query like:

SELECT username, email FROM Users;

This command retrieves the username and email fields from the Users table.

Why SELECT is Important

Allows applications to display data to users
Used for report generation
Helps administrators analyze stored data
Used by dashboards and monitoring systems

In most database systems, SELECT is the most frequently used command.

INSERT

The INSERT command is used to add new records to a database table.

It allows applications or administrators to store new information in the database.

Key Points

Adds a new row (record) to a table
Data must follow the table structure
Required fields must be provided
Often used when new data is collected

Example (IT Environment)

A helpdesk system stores support tickets in a table called Tickets.

When a new support request is submitted, the application inserts a record:

INSERT INTO Tickets (ticket_id, issue, status)
VALUES (101, 'Network connectivity issue', 'Open');

Why INSERT is Important

Stores new data collected from users
Allows applications to record system events
Used when registering new accounts or entries
Ensures the database stays updated with new information

DELETE

The DELETE command is used to remove records from a table.

It removes specific rows based on conditions defined in the query.

Key Points

Removes one or more records
Usually used with conditions to avoid deleting all data
Permanently removes the data unless backups exist

Example (IT Environment)

If a temporary test account is no longer needed:

DELETE FROM Users
WHERE username = 'test_user';

This removes the record of the test_user.

Why DELETE is Important

Removes outdated or unnecessary records
Cleans up test data
Maintains accurate databases
Helps manage storage space

Administrators must be careful because DELETE can permanently remove data.

UPDATE

The UPDATE command is used to modify existing data in a table.

It changes the value of one or more fields in selected records.

Key Points

Modifies existing rows
Requires conditions to identify which records should change
Helps keep information accurate and up to date

Example (IT Environment)

If a helpdesk ticket status changes:

UPDATE Tickets
SET status = 'Resolved'
WHERE ticket_id = 101;

This updates the ticket status.

Why UPDATE is Important

Keeps database information accurate
Reflects system changes
Allows applications to modify stored records
Used frequently in transaction-based systems

2. Data Definition

Data Definition refers to commands used to define and manage the structure of the database rather than the data itself.

These commands are called DDL (Data Definition Language).

They control the schema, tables, and database objects.

The main commands include:

CREATE
ALTER
DROP
PERMISSIONS

CREATE

The CREATE command is used to create new database objects.

These objects can include:

Tables
Databases
Indexes
Views

Key Points

Defines the structure of new objects
Specifies fields and data types
Establishes the database schema

Example (IT Environment)

Creating a table to store employee accounts:

CREATE TABLE Employees (
    employee_id INT,
    name VARCHAR(100),
    department VARCHAR(50)
);

This defines a new table with three fields.

Why CREATE is Important

Used when building a new database system
Defines how data will be stored
Establishes the structure of the database

ALTER

The ALTER command is used to modify the structure of an existing database object.

It allows administrators to update table definitions.

Key Points

Adds new columns
Modifies existing columns
Removes columns
Updates constraints

Example (IT Environment)

If the organization decides to store employee email addresses:

ALTER TABLE Employees
ADD email VARCHAR(100);

This adds a new column to the table.

Why ALTER is Important

Allows database structures to evolve
Supports new application requirements
Updates database design without deleting data

DROP

The DROP command is used to permanently remove database objects.

This can include:

Tables
Databases
Views
Indexes

Key Points

Completely removes the object
Deletes both structure and stored data
Cannot be undone without backups

Example (IT Environment)

Removing a table used only for testing:

DROP TABLE TestAccounts;

Why DROP is Important

Removes unused database objects
Helps maintain a clean database structure
Prevents unnecessary storage usage

Because it permanently deletes data, DROP should be used carefully.

Permissions

Permissions control who is allowed to access or modify database objects.

Database administrators use permissions to protect sensitive data and control user access.

Key Points

Permissions determine whether a user can:

View data
Insert records
Modify data
Delete records
Change database structure

Example (IT Environment)

A database administrator may allow developers to read data but not delete it.

Example permission command:

GRANT SELECT ON Employees TO developer_user;

This allows the user to view the data but not modify it.

Why Permissions are Important

Protects sensitive data
Prevents unauthorized modifications
Enforces security policies
Supports role-based access control

Permissions are essential for database security and data integrity.

Summary

Relational databases are commonly managed using SQL commands that allow interaction with both data and database structure.

Data Manipulation (Working with Data)

Command	Purpose
SELECT	Retrieve data from tables
INSERT	Add new records
DELETE	Remove records
UPDATE	Modify existing records

Data Definition (Managing Structure)

Command	Purpose
CREATE	Create new database objects
ALTER	Modify database structure
DROP	Remove database objects
Permissions	Control user access and security

Together, these relational methods allow administrators, developers, and applications to store, retrieve, update, and manage data efficiently in relational databases.