The basic sql
commands fall into 2 categories: data manipulation language commands
(DML) and data definition language commands (DDL). As the names
suggest, DML is used to manipulate the data in the database - i.e. to
retrieve (query), modify, remove and add data. DDL is used to define
The verbs for the basic SQL commands are:
- to retrieve (query) data
- to modify existing data
- to modify existing data
- to add new data
Now that we have the verbs, let's look at putting them into complete
commands. We'll start with queries as they are the most
type of SQL command used and because there's little point in having
a database if you can't retrieve information from it.
For a quick introduction to the SELECT statement,
watch our video tutorial.
syntax for queries is:-
column1 [,column2 [,column3 [...,columnN]]]
column2 etc. represent the data
items in the table that you are interested in (i.e. the names of the
columns). The square brackets "["
"]" indicate that the item enclosed within is optional, i.e. you must
select at least one column. If all columns are required, this
abbreviated with "*".
is a table or view (or a
subquery containing any combination of tables and views - which can
make SQL statements quite complicated).
the conditions that the selected rows must meet to be
returned to the user and can be defined as column
operator value_or_column where "operator" is one
"=","<",">","#" or "<>";
"value or column" is either a value (eg. 1 or "1") or the name of a
column in the table.
Therefore an example of the most basic sql commands for retrieving data
from a database would be :-
* FROM departments;
would select all columns from all rows in the departments
If we only wanted some of the
departments we would
restrict the departments selected with a where clause if we only
waned to know the departments' names we would specify the
column we want is called department_name
department_name FROM departments
WHERE name > 'ACCOUNTS';
The above example would only retrieve the
department names that are
greater (alphabetically) than ACCOUNTS (ie. sales,
One important point to note is that the order in
results are returned is not guaranteed unless this is specified by the
BY clause which comes after the WHERE
clause in the query.
The following query specifies that the names of
the departments are to be returned in ascending alphabetical order
SELECT department_name FROM departments WHERE
name > 'ACCOUNTS' ORDER BY name;
the results of which would be something like the
Having looked at the basic syntax and a couple of simple examples so
far of the basic sql commands for querying tech database, let's move on
to incorporate some of the
other features of sql to build up slightly more complicated and more
Let's just recap a little. Earlier in this sql
tutorial we said that the simplest query is of the form :
* FROM my_table;
where my_table is any table in our database (not
strictly true but for the sake of simplicity assume it is).
This query would return all the columns in all
rows from whatever table we specify.
For example, let us suppose we have a table called
which has the following columns:
title, format, price, author
Let us further suppose that we have 1,000 courses
and that obviously we have one row for each course.
In this case running the query:-
* FROM course;
would return all the details for all the courses -
all 1,000 of them. But we're only interested in one course - the SQL
tutorial - so, obviously, we would
like to restrict our query to just select those details.
Tips & Tricks to
SKYROCKET Your Career!
already a subscriber to our ezine you're missing out on a myriad
of tips and tricks in SQL and PL/SQL to help
you become a better, faster, smarter Oracle developer.
How we do this? Simple, we just need to add some
conditions to our query as follows:
SELECT * FROM course WHERE title = 'BASIC SQL COMMANDS';
This query will now return the details for all
courses entitled "BASIC SQL COMMANDS" (there may be more than one).
If we wanted to refine the query further we would
add more conditions, if we knew any more facts about the course that we
are interested in.
For example, let's assume that the format of the
basic sql commands course that we are interested in is "EBOOK".
In which case we can just add that condition to
our query like so:
* FROM course
WHERE title = 'BASIC
AND format = 'EBOOK' ;
Assuming that there is only one ebook
with the title "BASIC
SQL COMMANDS" in our database, this query would
then return just one row.
This demonstrates that we can restrict the number
of rows returned by our queries by adding as many conditions to it as
we like. Each condition, in this case, is combined with the previous
condition by an "and" which means that all the conditions must be met
by a particular
record (row) in our table, for that record to be retrieved.
If we wanted to make a condition optional we would
replace the "and" with an "or".
For example the following query:
* FROM course
title = 'BASIC
format = 'EBOOK' ;
would retrieve details of all courses called with
the title "BASIC
SQL COMMANDS" as well as the details of all the
ebooks in our database whatever their title.
Updating The Database
So far we've just looked at basic sql
commands to retrieve information from our database. There are
two good reasons for this. As we said earlier, most of the sql that we
write any application is to query data and so this is
fundamental to our understanding of SQL. Also queries are
often used in update, insert
and delete statements as we'll see later.
Let's move on now to look at the basic sql commands to
update data in the database.
There are three basic sql commands to do this: update,
basic syntax of each of these is as follows:
INTO my_table [(col1 [,col2...[,colY]])] VALUES (val1[,val2...[,valN]]);
is the table name, col1, col2, colY are the column names and the WHERE
clause containing the conditions
determines which rows are deleted or updated in the same way as they
determine which rows are returned by a select statement.
As with SELECT
statements, the WHERE
is optional for the UPDATE
commands and there may be multiple conditions joined by AND
Basic sql commands to INSERT data
The syntax for the basic sql commands to add data
database is different and therefore best illustrated
Watch this short video for an overview of the INSERT statement.
Let's assume we have a table called AUTHORS
which has three columns AUTHOR_ID,
To add details of new authors to our database the basic sql command
would look something like this:
authors VALUES (1,'Mike','Jones');
This would insert the details of one
author into our table called authors.
Note that we don't have to name the columns in the
table. As shown in the above example, as long as we provide a value (of
the correct type) for each column in the table we do not need to
specify the columns. However, if we don't list the columns then the
values will be assigned to the columns in the same order as they exist
in the table and Oracle will generate an error if a value is not
for every column in the table.
This means if we want to provide the
values in a different order or only provide values
for some of the columns we would need to specify the columns as in this
authors (AUTHOR_LAST_NAME, AUTHOR_ID,
Otherwise we would be attempting to set the id as
'Jones', the first name as 1 and the last name as Mike. Depending on
how the table is defined Oracle might raise an error if we attempted to
do that but if not the results would probably not be what we want.
Oracle doesn't care
about the data we put in as long as the data type is correct
(notwithstanding other constraints defined on the columns which our
outside the scope of this tutorial). Therefore in the interests of
self-documenting code (which makes maintenance easier and cheaper) we
usually specify the columns to be inserted.
The other point to note is that this statement
will insert just one row into the table. Therefore we need one
statement for author whose details we need to add to our table - there's no
shortcut like this:
We've seen the syntax for the basic sql commands
to modify data - the
update and delete commands - so let's look at them in more detail.
Watch this video for an introduction to using the
Or this short video to learn how to use the DELETE
In our earlier example of the use of the insert statement we
So how we do correct it?
We just use the update statement as
authors set first_name = 'Michael' WHERE last_name = 'Jones';
For our author called Jones, this statement
updates the first_name column and sets it to Michael.
syntactically valid but is what we want?
What if there are several
authors with the last name of 'Jones'?
In that case the first name
column of all
the authors called 'Jones' would be updated. Therefore we need to be
careful that the conditions we put in the where
clause will match only
the rows that we're interested in.
What happens if we don't specify a where clause at all?
authors set first_name = 'Michael';
Again this is perfectly valid syntax and again it may not be what we
want. In this case the first_name column of every row in the table
would be set to Michael!
The same thing applies to the delete statement -
not added to delete statements then they will affect
every row. Therefore the following example:
would remove the details of every author (i.e. every row) from the
table, so we
would nearly always
specify the conditions for the update or delete by using the "where"
If no rows meet the conditions in the "where"
clause, then obviously no
changes are made by the update or delete statements.
If we want to update more than 1 column in the
table we just need to
specify the extra columns separating them with a comma like so:-
SET first_name = 'Michael'
, last_name = 'Johns'
WHERE last_name = 'Jones';
As well as reading our other Oracle
one of the best ways to
learn more about Oracle is
to attend a formal training course. Smartsoft Computing offer Oracle
training in the UK both on and off-site.
Looking for a SQL training course?
Learn from real-world experts with SQL
training in the UK. Advance your skills and sky rocket your career.