Data Insertion and Retrieval

Data Manipulation Language (DML) commands are the most frequently used set of SQL statements. They are responsible for managing the actual records (rows) within the database tables.

1. Inserting Data (INSERT INTO)

The INSERT INTO statement is used to add new rows of data into a table. There are two primary syntaxes for this command.

A. Specifying Columns (Recommended)

This method explicitly lists the columns you are providing values for. This is the best practice because it allows you to omit optional columns (those that accept NULL or have DEFAULT values) and makes your code immune to column reordering in the table structure.

Syntax:

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

Execution Example:

Let’s insert a new employee record into the Employees table we created in the previous chapters.

USE HR_Management;

INSERT INTO Employees (first_name, last_name, email, hire_date, salary)
VALUES ('John', 'Doe', 'john.doe@example.com', '2023-08-15', 65000.00);

Note: The employee_id column is omitted because it was set to AUTO_INCREMENT, allowing MySQL to generate its value automatically.

B. Inserting All Columns (Less Flexible)

This method does not list the columns, requiring you to provide values for every single column in the exact order they were defined in the table.

INSERT INTO Employees 
VALUES (NULL, 'Jane', 'Smith', 'jane.smith@example.com', '2024-01-20', 72000.00);

Note: We use NULL as a placeholder for the AUTO_INCREMENT primary key, telling MySQL to automatically assign the next ID.

2. Retrieving Data (SELECT)

The SELECT statement is the most powerful and complex command in SQL. It is used to query the database and retrieve data based on specific criteria.

A. Selecting All Columns (SELECT *)

The asterisk (*) is a wildcard used to select every column from the specified table.

Syntax:

SELECT * FROM table_name;

Execution Example:

-- Retrieve all columns and all rows from the Employees table
SELECT * FROM Employees;

B. Selecting Specific Columns

To improve query efficiency and network load, you should always limit the results to only the columns you actually need.

Syntax:

SELECT column1, column2, column3 FROM table_name;

Execution Example:

-- Retrieve only the names and emails of all employees
SELECT first_name, last_name, email FROM Employees;

C. Using the DISTINCT Keyword

If you only want to see the unique values in a specified column, use the DISTINCT keyword.

Execution Example:

Assuming multiple employees share the same salary, this will return a list of non-duplicate salary values.

-- Show only the unique salary values present in the table
SELECT DISTINCT salary FROM Employees;

D. Using Column Aliases

Aliases provide temporary, user-defined names to columns or tables in the result set, making the output more readable. The keyword AS is optional.

Execution Example:

-- Rename the output columns for clarity
SELECT 
    first_name AS "First Name",
    email AS "Contact Email"
FROM Employees;