Basic SQL Commands

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.Your first paragraph ...

The verbs for the basic SQL commands are:

  • SELECT  - to retrieve (query) data
  • UPDATE - to modify existing data
  • DELETE - to modify existing data
  • INSERT - 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 mostcommon type of SQL command used and because there's little point in having a database if you can't retrieve information from it.

This tutorial focuses on using sql with Oracle. Click here to learn about using SQL with MS SQL Server.

For a quick introduction to the SELECT statement in Oracle SQL, watch our video tutorial.

The basic syntax for queries is:-

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


  • 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/view.

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

Oracle Tips & Tricks to SKYROCKET Your Career!

If you're not 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. Subscribe now and ignite your career.

If we only wanted the details of some of the departments we would restrict (filter) the departments selected by use of a where clause. For example

SELECT * FROM departments; WHERE name <> 'ACCOUNTS';

The above example would retrieve all the details of all the departments other than the accounts department.

If we only wanted to return certain attributes about the departments we would just specify the column(s) we want. For example if we only want the names of the departments  we would juset select the department_name column like so:

SELECT department_name FROM departments;

Of course we can combine these two query formats to return only teh details we want of teh departments of interest, 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
department_name > 'ACCOUNTS'
ORDER BY name;

the results of which would be something like the following: