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:
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:-
column1 [,column2 [,column3 [...,columnN]]]
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.
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
WHERE department_name > 'ACCOUNTS'
ORDER BY name;
the results of which would be something like the following: