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.
More SQL
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.