This is the continuation of our previous article, where we go over the basics of the main SQL Query to build a database and its tables. Also, we went over the SQL query that you use to get data from your database.

Now we will continue with the queries that you use to manipulate the data in the system.

DML SQL Query

DML stands for Data Manipulation Language and is one of the types of queries. These queries are used to change the data in your database or to add more data.

The first query that we are going to use is the INSERT query, which is used to add data to your tables.

INSERT SQL query

Provided that you still have the CARS table created from our previous article, we will continue using that table in our examples.

The query that we will use to insert information is:

INSERT INTO CARS
(BRAND, HORSEPOWER, COLOR, YEAR) VALUES 
('BMW',    250,    'RED',  2010)

 

Now, in order to review that our data is indeed stored in our CARS table, let’s just run the query that we learned in our previous article, the SELECT statement.

SELECT * FROM CARS

Our INSERT query will always need to follow this format:

• INSERT INTO – beginning of statement
• CARS – the name of the table followed by a list in parentheses with the columns you want to insert information into
• VALUES – mandatory element and must be specified
• a final set of parentheses that contains the values that are going to be inserted in the columns. The order of the values that you specify must correspond to the order of the columns you specified after the name of the table!

As a practice, add a few more cars to our table, as we will need more records in the table in order to continue our examples. Below is how our table looks like after we added a few more cars.

Let’s say that our BMW car had some modifications, and we increased the engine power and changed the car’s color. We will then have to modify the information in our table.

UPDATE SQL query

This statement is used in order to modify any data record in our tables. Let’s say our car has gained 30 more horsepower and has been painted green. We will specify this in the table with the following query.

But, be aware that if you have more cars already inserted in your table, all of them will have their horsepower and color changed to 280 and GREEN if we run the query below. (do not run it!)

UPDATE CARS
SET HORSEPOWER = 280,
    COLOR = 'GREEN'

This is why we will put a condition (the WHERE clause) on the query which will UNIQUELY IDENTIFY our BMW car for which the query will then run.

UPDATE CARS
SET HORSEPOWER = 280,
    COLOR = 'GREEN'
WHERE BRAND = 'BMW'

So, now our query has run and we will check with the SELECT statement what has changed.

In order to continue our examples, let’s pretend that our Audi car has been sold now, and must be removed from the system.

DELETE SQL query

The DELETE statement is the last in our list of DML queries and is obviously used to delete records from a table.

Thus, in order to remove our Audi car from the system, we will have to write a query that will UNIQUELY identify our car and remove it from the database. In this situation, the BRAND is enough to identify ONE record in the table (our car) so we can use a condition based on the brand name to remove the car.

DELETE FROM CARS
WHERE BRAND = 'AUDI'

 

Now let’s review the information in our table to see what has changed.

Now we have gone over all of the queries that you will most likely need in order to create a database to store your data and how to manipulate it depending on your needs. If you would like to find out more advanced information about writing queries, please follow our posts in the future.