Query Commands for Relational Database Management Systems
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)
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.
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:
SET firstname = ‘Maria’
WHERE lastname = ‘Grady’;
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.
WHERE name_of_column = value
SELECT, FROM and WHERE
At the heart of most queries is the SELECT-FROM-WHERE
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:
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”;
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.
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.