Query Commands for Relational Database Management Systems
INSERT, UPDATE and DELETE are important commands in SQL. INSERT, used in conjunction with INTO can be used to insert rows of data into a table. INSERT is used to put data into all the columns of a table at once or to specify which columns get data.
Examples of the SQL commands INSERT, UPDATE and DELETE
If, for example a four column table exists, the following will insert one row of data into all the columns:
INSERT INTO Employees VALUES(4011, ‘Grady’, ‘Belinda’, ‘09/05/07’) while, INSERT INTO Employees (startdate) VALUES(‘09/05/07’);
will insert that one piece of information into one column. When inserting data into columns the data must be arranged in the same order as the corresponding rows. The data types should match the corresponding columns as well.
UPDATE and DELETE affect data that exists on tables in the database. UPDATE will change data values in records on a table. Adding some criteria to an UPDATE statement will improve the likelihood that the desired results are obtained. An example with UPDATE is:
UPDATE Employees SET firstname = ‘Maria’ WHERE lastname = ‘Grady’;
SET will change whatever is in the firstname column to ‘Maria’ and the condition in WHERE ensures that only rows with a last name of ‘Grady’ will be altered.
DELETE is similar to UPDATE in that it uses the WHERE statement.
DELETE FROM name_of_table WHERE name_of_column = value
SELECT, FROM and WHERE
At the heart of most queries is the SELECT-FROM-WHERE
SELECT draws the desired data from the database, FROM states which table(s) to pull the information from and WHERE sets a criteria so that only certain records are considered when data is extracted. An example of this type of statement is:
SELECT * FROM Employees WHERE Lastname ="Smith”;
The asterisk means that data from all columns in the table will be displayed. Naming specific columns after SELECT will return only those columns, like:
SELECT Lastname, Firstname FROM Employees WHERE Lastname = “Smith”;
FROM allows you to choose one or more tables for your query. Adding more than one table to your query is useful for more advanced commands like Joins or when criteria needs to be met in more than one table.
WHERE lets the programmer set some logical condition before a query is carried out. The following are just some of the operators that can be used in a WHERE clause:
<, >, =, <=, >=, AND, CASE, LIKE and NOT LIKE.
Other commands, functions and techniques exist in the SQL query language to aid you as a database developer in the task of creating an efficient database. Joins are used to combine two or more tables when executing a query. Aggregate and scalar functions manipulate numerical, character and date data, while control flow functions manage how data is extracted from a query.