SQL Command Essentials: CREATE, INSERT, DELETE, UPDATE

Are you looking for that first SQL query to run in your environment and get cracking on SQL? Well, you’re in luck because, in this article, we’ll explore the essentials of SQL commands like CREATE, INSERT, DELETE, and UPDATE, which is fundamental for everyone desiring to work with SQL.

Business Problem

I have created a hypothetical problem. This looks like what you might find in the real world. It might not be exact, but it gives you a glimpse of what you’re going to see when you start working with SQL because SQL is meant to solve a business problem.

A business named TechCorp is struggling to manage employee information effectively. They need a systematic approach to store, retrieve, update, and delete employee data. The lack of a centralized database makes it challenging to track employees’ details, such as names, ages, and departments. To address this issue, TechCorp aims to implement a basic SQL solution for managing the processes.

Please take note of this employee information because it will give us an idea of what we should implement when we want to implement the solution.

Prerequisite

1. SQL Server, or any environment you choose.

Let’s code

Create the database

To perform this function, use the CREATE command. TheCREATE command is used to create new data in a database, such as tables, databases, or columns. Here, we are going to create a database called EmployeesDB.

You can check out my previous post on What is SQL to get a better understanding of these statements.

CREATE DATABASE EmployeesDB;

Create the table

After creating your database, the next step is to create the objects in the database. These objects include tables, the data for the tables, adding data types, and defining primary keys. The data types are attributes associated with each column that tell the system how to respond to the data in that column.

CREATE TABLE Employees (
    EmployeeID INT PRIMARY KEY,
    FirstName VARCHAR(50),
    LastName VARCHAR(50),
    Age INT,
    Department VARCHAR(50)
);

When you run this query, you will discover that a table has been created.

View the table

To view the table, use the SELECT command. The SELECT command is used to retrieve data from a table in a database. You can specify which columns you want to view and apply filters to get the exact data you need. For example, to view all the data in a table called “employees,” you would use the following command:

SELECT *
FROM Employees:

You will discover the table exists even though we haven’t added any data to it yet.

Insert the values into the created table

Now, we’ll insert some sample data into the Employees table. To do this, use the INSERT INTO command, specifying the columns and values you want to add. For example:

INSERT INTO Employees VALUES
(1, 'John', 'Doe', 32, 'IT'),
(2, 'Jane', 'Smith', 27, 'Sales'),
(3, 'Michael', 'Johnson', 41, 'Marketing'),
(4, 'Emily', 'Williams', 35, 'HR'),
(5, 'David', 'Brown', 29, 'Finance');

To see what you’ve done, use the SELECT command. Remember that this command retrieves data from the database for the user.

SELECT *
FROM Employees;

After running this query, you will notice a difference between this displayed table and the previous one you retrieved initially. The newly inserted data now appears in the Employees table, whereas these records were absent before. This illustrates how the INSERT INTO command adds new rows of data to your table.

Filter by First Name

The filter statement in SQL is used to sort out unnecessary values or extract only the required values from a table. Here, we are going to use the WHERE clause. The WHERE clause allows you to specify conditions that the data must meet to be included in the results. For example, to retrieve only the employee with the first name “John,” we use this command:

SELECT *
FROM Employees
WHERE FirstName = 'John';

This will return the row where the first name is John.

Filter by Employee ID

We have been able to find the employee with the first name “John.” Now, let’s do more sorting and find the employee with the employee ID “3.” To do this, we will use the WHERE clause again:

SELECT *
FROM Employees
WHERE EmployeeID > 3;

This command will return the details of the employee with an employee ID of 3.

Update the table

The UPDATE command is used to modify the objects in a database. This sort of modification is done where typos or incorrect data exist. For example, if we need to update the first name column of all rows in the employee table to have the value'J'.

UPDATE Employees
SET FirstName = 'J';

To see the change in what we have done, simply use the SELECT command to retrieve that table.

SELECT *
FROM Employees;

Now, let’s try the actual modification we wanted to do. For this, we need to update the first name column to change the name of the employee with employee ID “4” to “Rob.”.

UPDATE Employees
SET FirstName = 'Rob'
WHERE EmployeeID = 4;

Delete the data

To delete a record from a table, use the DELETE command. For example, to delete the record of the employee with EmployeeID 4, you would use the following command:

DELETE FROM Employees
WHERE EmployeeID = 4;

This removes the employee with EmployeeID “4”.

Conclusion

In this guide, we’ve created a database, designed a table, inserted data, and performed basic SQL operations like retrieval, update, and deletion. These fundamentals are essential for managing data effectively in real-world scenarios.

Stay tuned for more on SQL queries and data manipulation techniques in upcoming tutorials!