DML SQL Query
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.