Data Manipulation Language Statements
In this chapter you will learn how to manipulate database tables and make them useful through data insertion, deletion and update. To accomplish this, you will be programming in SQLiteStudio using three Data Manipulation Language statements – INSERT, DELETE and DROP.
Normally, such tables are empty after they have been created. The data that you can store in your database objects can be in various formats – non-digital, semi-digital and fully digital. Non-digital format means that the data needs to be extracted from a non-electronic source, like customer information from business cards. In this case, you are required to store the data manually into your database. As for the semi-digital, the data could already be in some sort of digital form but not the same format as your database tables. For example, you could have records of your customers’ business cards stored in a Microsoft Excel file that you may need to translate into an appropriate format fit for your database. Lastly, fully digital means that all of your customer information is already in electronic format that also matches the layout of your database.
The current data format will further determine how you will be able to manipulate your database. This is where the DML commands become useful in entering new data, updating existing data and deleting data from tables.
INSERT Statement
The process of entering new data could be done either manually through individual commands or automatically using batch process programs. There are also factors that will determine what and how much data you can insert in your database tables – field length, column data type, table size and more. In populating tables with data, you will use the INSERT statement.
• One Row at a Time
When you want to enter all the data into a single row of your database table, you can create a form-based data entry application. In this feature, a screen is designed that contains fields where you can input the information being asked - for every column in the table. Use the following syntax in adding data one row at a time:
INSERT INTOTABLE_NAME[(column_1, column_2, … , column_n)]
VALUES (value_1, value_2, …, value_n) ;
Anything inside the square brackets are considered optional, meaning you don’t need to list the column names. By the way, “n” is the maximum number of table columns. The default order of the column list is the same order as your column tables. Thus, if you list the items inside the VALUES section in the same order as your table columns, then the values will be entered in the correct columns. You only need to indicate the column names if you need to specify the values in a different order.
Now, let us insert records to our Customer_TBL table using the customer information provided in the Chapter 2.
1. Type the following programming lines in the SQL editor:
INSERT INTO Customer_TBL (CustomerID, CustomerName, JobPosition, CompanyName, USState, ContactNo) VALUES (1, ‘Kathy Ale’, ‘President’, ‘Tile Industrial’, ‘TX’, 3461234567)
