Lesson 3 – Creating Records in a Table
The ultimate purpose of a database is store records. Records are stored in tables. To insert records in tables, the INSERT INTO query is used. Let’s insert a single record in the “categories” table.
Note: It is important to remember that you have to open the “RetailDB” into the DB Browser and then open the “ExecuteSQL” tab to execute queries:
INSERT INTO categories ( name, current_total ) VALUES ('Computers', 300);
You can see from the above script that to insert a record, you have to use the INSERT INTO statement followed by the columns of the table inside the opening and closing parenthesis. The values for the columns are specified via the VALUES clause. You simply have to specify the values inside the opening and closing parenthesis. It is important to mention that the type of values must match the column type. For instance, if the first column is varchar, the first value should be string specified inside quotes.
You can also add values without specifying the column. The type of the values must match the implicit column type. Look at the following example.
INSERT INTO categories VALUES (null, 'Mobiles', 150);
In this case, you should specify “null” for the first column which is “id” because it is automatically incremented. For the second and third columns i.e. name and current_total, you have to specify the values.
Let’s now add two more categories in our “categories”:
INSERT INTO categories VALUES (null, 'Cameras', 250), (null, 'Computer Accessories', 800)
You can see that we can INSERT multiple values by adding a comma between them.
Let’s now INSERT 10 records in the “products” table. Here I will show you how to insert multiple records at a time. Look at the following script:
INSERT INTO products VALUES (null, 'Samsung Galaxy 9', 700, 'yes', 2), (null, 'Core i7', 800, 'yes', 1), (null, 'Core i3' , 400, 'no', 1), (null, 'Apple Iphone X', 800, 'yes', 2), (null, 'Cannon super short', 350, 'yes', 3), (null, 'Sonny Headphones', 100, 'no', 4), (null, 'Core i5', 600, 'yes', 1), (null, 'Konica Minolta', 500, 'no', 4), (null, 'Samsung Galaxy 10', 900, 'yes', 2), (null, 'Mic Samsung', 50, 'yes', 4)
It is important to mention that the “category_id” column of the “products” table should have values only from the “id” column of the “category” table.
We have successfully added data into the “categories” and “products” table. In the next lesson, you will see how to select data from a table.