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 database objects.

common 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.

The basic syntax for queries is:-

SELECT column1 [,column2 [,column3 [...,columnN]]]
FROM database_object
[WHERE condition1[,condition2[,...conditionx]]]

Where

• column1, 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 can be abbreviated with "*". 
• Database_object is a table or view (or a subquery containing any combination of tables and views - which can make SQL statements quite complicated).
• condition1[,condition2[,...conditionX]] are 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 of "=","<",">","#" 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 :-

SELECT * FROM departments;

This would select all columns from all rows in the departments table.

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 like so:

SELECT department_name FROM departments WHERE name > 'ACCOUNTS';

The above example would only retrieve the department names that are greater (alphabetically) than ACCOUNTS  (ie. sales, marketing, hr, etc.). 

One important point to note is that the order in which the results are returned is not guaranteed unless this is specified by the optional ORDER 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 following:

department_name
-------------------
FINANCE,
HR,
I.T.
MARKETING,
SALES

Simple Queries To Retrieve Data From The Database

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 interesting queries.

Let's just recap a little. Earlier in this sql tutorial we said that the simplest query is of the form :

SELECT * 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 course which has the following columns:

id, 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:-

SELECT * 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.

Oracle Tips & Tricks to SKYROCKET Your Career!

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:

SELECT * FROM course
WHERE title = 'BASIC SQL COMMANDS'
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:

SELECT * FROM course
WHERE title = 'BASIC SQL COMMANDS'
OR 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, delete and insert. The basic syntax of each of these is as follows:

UPDATE my_table 
SET col1 = val1
   [,col2 = val2
   [...,colY = valX]]
[WHERE conditions];

DELETE FROM my_table [WHERE conditions];

INSERT INTO my_table [(col1 [,col2...[,colY]])] VALUES (val1[,val2...[,valN]]);

where my_table 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 clause is optional for the UPDATE and DELETE commands and there may be multiple conditions joined by AND and/or OR.

Basic sql commands to INSERT data

The syntax for the basic sql commands to add data to the database is different and therefore best illustrated by examples. 

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, AUTHOR_FIRST_NAME, AUTHOR_LAST_NAME

To add details of new authors to our database the basic sql command would look something like this:

INSERT INTO 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 supplied 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 example

INSERT INTO authors (AUTHOR_LAST_NAME, AUTHOR_ID, AUTHOR_FIRST_NAME)
VALUES ('Jones',1,'Michael');

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:

INSERT INTO authors (AUTHOR_LAST_NAME, AUTHOR_ID, AUTHOR_FIRST_NAME)
VALUES ('Jones',1,'Mike');
VALUES ('Churchill',2,'Winston');
VALUES ('Orwell',3,'George');
VALUES ('Dickens',4,'Charles');

Basic sql commands to modify data

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 UPDATE statement.

Or this short video to learn how to use the DELETE statement.

In our earlier example of the use of the insert statement we mis-spelled 'Michael'.

So how we do correct it?

We just use the update statement as follows:

UPDATE 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. 

This is 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?

UPDATE 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 - if the "where" clause is not added to delete statements then they will affect every row. Therefore the following example:

DELETE FROM authors;

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" clause.

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:-

UPDATE authors
SET first_name = 'Michael'
, last_name = 'Johns'
WHERE last_name = 'Jones'; 

As well as reading our other Oracle tutorials, 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 as well as expert Oracle training in New Zealand .

Return from basic sql commands to Oracle tutorials