One of the most utilized ways to communicate with relational database management systems (RDBMS) is through Structured Query Languages (SQL). As noted in What Is SQL, there are several SQL sub-languages. The statements that these sub-languages encompass may vary depending who is talking about them. Two of the broadest categorize are:
Data Description Language (DDL) statements for management of objects within the database.
Table 1 shows the DML keywords for four popular databases. This list may not be complete depending on the version of the database you are utilizing. It does however, give you an idea how each vendor implementation of SQL varies.
Table 1: Data Manipulation Language (DML) statements per RDBMS
The rest of this guide will focus on the DML statements that are available across the databases listed in Table 1 that allow users to select (query), insert (add), update (modify), and delete data.
There are several sites that allow you to practice utilizing SQL. I will be utilizing SQL Fiddle which, at the time of the writing of this guide, supports several different databases and versions. The syntax I will show was tested using the Oracle database. This guide does not cover DDL statements but to be able to do DML you have to execute some DDL. To create tables within SQL Fiddle, execute the following in the "Build Schema" window:
Other example statements within this guide will need to be executed within the "Run SQL" window.
A basic INSERT
statement can take on two forms. The first is to specify columns names and the values to be inserted.
NOTE: The
commit
here is a transactional control key word that saves the data entered.
When specifying columns, you should be specific in the data you enter but the order does not matter.
If you are going to insert data into all columns, then you can use the second form and just specify the values. The order of the values must match the table column order.
If you are following along in SQL Fiddle, you will need to do some additional inserts into the EMPLOYEES table to have data in it as well.
The SELECT statement is used to retrieve data from one or more objects within a database. The simplest form that a SELECT statement can take in Oracle is along the lines of:
This statement retrieves all the data from the table employees
, unfiltered.
NOTE: If your
SELECT
statements start to return no rows, put all the insert statements into the "Build Schema" window with the create table statements and build the schema again.
The following statement only returns the last name and department ID from the employees table.
You can go further and filter the data based on other criteria using the WHERE
clause. This statement filters that data based on the DEPTID
column and only returns the data for when the DEPTID
=1.
You do not have to display the column you are filtering on.
You can use the select statement to display information from multiple tables when there is some type of connection between them. In our examples, the employees
table only has the department ID. If you want to display the department name, you have to include the departments table and use the WHERE
clause join the tables together on the DEPTID
field.
These are very simple SELECT
statements. Depending on the vendor specification, things can get a lot more complicated but, at the same time, very useful. For example, Oracle provides analytic functions that enable calculation of rankings, percentiles, doing moving window calculations, linear regression statistics, lag/lead analysis, and first/last analysis.
Update statements allow you to change the data in specific columns for rows stored in a table. You can choose a specific row to update by including theWHERE
clause and utilize a unique set of values. In the employees table the EMPLOYEEID
is unique. If Fredrick decided to change his last name then we can update the LASTNAME
column based on the EMPLOYEEID
of 3.
You can also update multiple rows at the same time, based on the WHERE
clause.
WARNING: If you don't use the
WHERE
clause, you will be updating all the rows.
NOTE: The
ROLLBACK
here is a transactional control key word that returns the data to its original state. If you find an error in yourUPDATE
orDELETE
statement, you useROLLBACK
to reverse the changes. If youCOMMIT
the results, thenROLLBACK
is not possible.
If there is data in a table that you no longer need, then you execute a DELETE
statement to remove the data. Similar to the UPDATE
statement, you need to use the WHERE
clause to be specific on the data being deleted.
If you don not use the WHERE
clause you will delete all the rows within the table.
That concludes our brief introduction to the SQL Data Manipulation Language. Each database has its own flavor of SQL and things improve with each version. If you get a chance check out the Row Limiting SQL Clause section in my PluralSight course OCP 12c Enhancements for the DBA.
Tim Boles is an Oracle Database Administrator who has published multiple courses with Pluralsight. Check out the library of his courses here: https://app.pluralsight.com/profile/author/tim-boles.
He can be reached at [email protected] and twitter @timboles_dba.