Table of Contents
Insertion
Data is inserted into tables ubiquitously. Inserting a row of data means adding it to a table, which is easy in SQL. Insertion is also referred to as ‘INSERT INTO’ and ‘CREATE TABLE’. A INSERT statement may be used to populate a new table with data. SQL is the most popular database management system in the world, and is used for most database applications and a variety of business uses.
Insertion is not great for inserting duplicate rows, because it has no knowledge of the data already there or the meaning of duplicate values. A situation where this may occur is when we are inserting data from an old table and want to give something familiar to the user but unfamiliar to SQL (i.e. data from an old table that has changed). This means that customer address will end up duplicating data from the customer table.
The following quote describes one way of overwriting existing data being inserted:
Deletion
Deleting data is easy in SQL because it uses DELETE statements. To delete a row of existing data, the table name is used then followed by ‘DELETE FROM’. It is important to know that the delete statement only works with LIKE conditions. This is because the name of a record cannot be automatically removed except by using a wildcard. For example, if you wanted to delete a row of data that had the first name of “John” and last name of “Doe” you would use:
DELETE FROM FirstName John Doe WHERE LastName like “Doe%”;
A wildcard entry is made a lot easier if the table is named. This is done by placing an asterisk (a number) before the table name. For example:
DELETE FROM *;
Deleting rows can be done by a range of data. For example, if you wanted to remove all rows from a table named peoples which had an ID that was greater than 100, and less than 1000 you would use:
DELETE FROM peoples WHERE Id > 100 AND Id < 1000;
Modification
Modifying existing data is also easy in SQL. Much like the INSERT statement, the ‘UPDATE’ keyword allows modification to an existing table or adding new rows to it. The use of UPDATE gives the user the ability to make changes like adding new rows to the table. There are conditions to apply with the UPDATE key word, this will include conditions on a column or entire row. Using the UPDATE keyword, a user can make changes like adding new rows to the table.
The example below will show how to add records into an existing table. The ‘UPDATE’ keyword needs to have the WHERE clause with it which defines some conditions. We need to insert at least one column or new row otherwise it would be an error in SQL. In this example we can see that we are inserting new rows into the Employees table by changing the values in Department and Salary columns for employee ‘101’.
The following example will show how to change existing records based on the results of a condition. In example, if we want to find the employee with salary greater than 100,000 and change his/her salary to his/her current salary plus a bonus the query would be like this.
Retrieval
Retrieval is performed through the SELECT statement. The SELECT statement retrieves data from one or more tables and returns this data to the programmer. The SELECT statement is the most commonly used statement.
The syntax for the SELECT statement is given below:
SELECT column-list FROM table-name;
SELECT uses an expression list and returns the values of the specified columns. A column-list specifies all the columns that are to be returned. In some occasions, only a subset of all columns in a table is needed, and therefore it is essential to specify only those columns which are needed for subsequent processing.
SELECT uses the table or tables specified in the FROM clause to retrieve data. If no tables are specified, database rows will be retrieved from all tables.
Referential Integrity
Database referential integrity is the process of maintaining the relationship between a foreign key column and its primary key, to ensure that every foreign key in a table refers to a valid primary key in another table. When there are multiple foreign keys referencing a single primary key, referential integrity rules determine which of those keys is valid and keeps only that one.
Because referential integrity is so important to database use and maintenance, many database vendors have developed specialized languages (known as data definition or DDL statements) for adding and changing data in the database. These languages are called “referential integrity languages” and include such statements as:
The above examples would produce the following referential integrity rules:
Depending on the database vendor, the above rules may be implemented using either triggers or foreign keys. The SQL standard states that triggers are a preferred mechanism for implementing these types of functions.
