Notes 0003

Introduction

In these notes, we will learn how to add, view and update data.

Inserting Data

We insert data into the database via the INSERT command. It has the general syntax of:

INSERT INTO tablename VALUES (column1value,column2value,...);

In this particular case, the order of columns (column1value,column2value,...), has to match the order the columns were inserted in (the order they are in the database).

There is another more involved (but possibly safer) format which is:

INSERT INTO tablename (column1name, column2name,...) VALUES (column1value,column2value,...);

Where column1name has to match up with column1value, etc. In this command, columns do not need to be in any particular order, as long as they match up with their respective column names and values. You also don't have to specify all columns, since either NULL or default values will be inserted into those unspecified fields.

Updating Data

You update data in the database via the UPDATE statement. The general format is:

UPDATE tablename SET columname = newvalue WHERE searchcondition;

For details on the search condition, see the Search Condition section.

Set Section

The "set" section can have multiple name value pairs. For example:

UPDATE sometable SET fname='john',lname='doe',age='23';

Also note that the WHERE clause is optional. If it is not specified, then all the records in the database are effected (usually not what you want).

Deleting Data

You delete data using the DELETE statement. The general format is:

DELETE FROM tablename WHERE search condition;

This deletes any records from tablename that satisfy the search condition (and assuming they don't violate any of the constraints).

The WHERE clause is optional, but if you don't specify it, you delete all the data from the table. Ie:

DELETE FROM tablename;

Clears tablename of all data, but still keeps the table. This is different from the DROP TABLE tablename, which wipes out the table along with the data.

For information on the search condition, see the Search Condition section.

Retrieving Data

A vast majority of SQL statements executed by most databases are SELECT statements. These allow you to retrieve data using various conditions. The SQL itself, being a Standard QUERY Language is kind of synonymous with the ability to retrieve data from (or query) the database.

General format of a SELECT statement is:

SELECT columnlist FROM tablelist WHERE searchcondition;

The columnlist is a list of column names, separated by commas. The tablelist is a list of table names, also separated by commas. For information on the search condition, see the Search Condition section.

Instead of providing the column list, you can specify a an asterisk (*), which selects all columns.

You must specify at least one table in the tablelist. You specify several tables if you have data coming in from multiple tables (either for output columns, or for search condition).

There are many modifies that you can apply to the basic select statement. For example, you can specify to select only DISTINCT values from the table; which will eliminate any duplicates. For example:

SELECT DISTINCT something FROM sometable;

Sorting

When selecting, you sometimes want to sort the output. Best place to sort things is a database, since it is very often has the best optimized sorting routines, and can cache the results for later queries.

To sort, you use the ORDER BY clause in the SELECT statement. For example:

SELECT * FROM sometable WHERE someval < 32 ORDER BY somecol;

The above would sort the output by somecol, in ascending order. To sort in descending order, you would append DESC to the end, ie:

SELECT * FROM sometable WHERE someval < 32 ORDER BY somecol DESC;

Calculations

You can perform calculations inside the query. For example, instead of just returning columns, you can also select various operations:

SELECT start,end,end-start FROM somedata;

The idea that this will select two columns from the table somedata, the start and end, but the 3rd column will be created by taking the difference of end and start.

You can use the basic four operations, like: + - * /. You can also use parenthesis to group expressions.

Number Functions

In addition to basic calculations, you can also use number functions, like absolute value, etc. A few of these are described next:

ABS(n): Returns the absolute value of n.

POWER(n,power): Returns n raised to a specified power.

ROUND(n,precision): Returns n rounded to the specified precision.

TRUNC(n,precision): Returns n truncated to the specified precision.

You can test them out by selecting them from a DUAL table (described in the DUAL section later). For example:

SELECT POWER(2,4) FROM DUAL;

Would return 16... since 2⁴ is 16.

Column Alias

Using calculations or functions in the select field is useful, but the resulting column name is really long (usually representing an entire expressions used to calculate that field).

You can set a column alias, which will be return as the column name. For example:

SELECT start,end,end-start AS diff FROM somedata;

Now, instead of getting the result with 3 columns named: "start", "end" and "end-start", you'd get a result with 3 columns named "start", "end", and "diff".

You can use these types of aliases to make output more readable, and queries a bit more manageable. We'll talk later how you can apply similar aliases to table names.

Group Functions

Group functions are functions that operate on groups of data (as opposed to a single value). These include averaging, counting, etc. A few common ones are described next:

AVG: Returns the average.

COUNT: Returns the count of values

MAX: Returns the maximum value

MIN: Returns the minimum value

SUM: Returns the sum of all

To use them, you have to select them. For example:

SELECT MAX(grade), AVG(grade) FROM testdata;

The above would return the maximum and average grades from testdata table.

The data has to be properly grouped for these to work. For example, in the above query, we simply went through the whole table. If we wanted to generate a list of students with a maximum and average grade for each particular student, we'd have a problem. For that, we need to use the GROUP BY clause. This one is generally appended at the end of SQL statements (possibly after the ORDER BY clause). For example:

SELECT studentid, MAX(grade),AVG(grade) FROM testdata GROUP BY studentid;

Now, we will get a list of studentids, with each one having two numbers, one for the maximum grade, and the other one for the average grade. (note that this depends on the actual layout of the table; in this case, we're assuming that testdata has studentid and grade, and has many of these entries - which is kind of unlikely practically).

Search Condition

Search conditions are used in UPDATE, DELETE, and SELECT statements. Their format and intent is generally identical (they're used to select elements that match the condition).

The search condition has to evaluate to a true or false. You can use several operators:

= for equal to
> for greater than
< for less than
>= for greater than or equal to
<= for less than or equal to
<> for not equal to

To check for NULL value you use:

columname IS NULL

or

columnname IS NOT NULL

You can also group search condition expressions by using AND and OR. Note that AND has higher precedence than OR (it is evaluated first). You can also use parenthesis for grouping expressions.

You can also use NOT to negate conditions (if that becomes nessasary).

There are also several built-in functions that are available (we'll talk about them as we come upon them).

An example would be something like: something < 5 OR something is NULL AND somedate = TO_DATE('01/03/93','MM/DD/YY')

Transactions

A transaction is one or more related SQL commands that constitute a logical unit of work. They either all have to work, or they all have to fail.

Every time you type commands in SQL*Plus, you're in a transaction. Once you quit, SQL*Plus commits the transaction for you (so no explicit commit is needed).

Now, it's a good idea to commit the transaction yourself.

You commit a transaction by typing: COMMIT.

If something is not going well, you can roll back the transaction by typing: ROLLBACK.

Note that DDL is not rolled back (once you drop a table, you can't roll it back, or once you add a column, you can't remove it).

You can also define save points. These are points to which you can roll back the transaction (as opposed to rolling back the entire thing).

You define a save point by issuing: SAVEPOINT savepointname.

You rollback to that save point by: ROLLBACK TO savepointname.

TO_DATE

There is a very useful function for converting values to dates: TO_DATE. The general format is:

TO_DATE('date you are converting','date format mask')

For example, TO_DATE('07/19/02','MM/DD/YY') specifies July 19th, 2002 date. You can use TO_DATE to insert dates (and time!) into the database using various date/time formats.

Some useful formats: 'DD-MON-YYYY' for '19-JUL-2002', or 'HH:MI AM' for '01:17 PM'.

ADD_MONTHS

If you have a date, and you'd like to find a date a few months from then, you can use the ADD_MONTHS function to do it:

SELECT ADD_MONTHS(orderdate,2) AS expiration FROM sometable;

The above would select expiration, which is the date 2 months after the orderdate.

You can also select backwards in time, by adding negative months.

MONTHS_BETWEEN

Given two dates, you can find the number of months in between them. General format is:

MONTHS_BETWEEN(date1,date2)

Finds the month difference between date1 and date2.

DUAL

There is a table named DUAL which is accessible to everybody. It has various useful information, like the system time. For example, you can select time from it by:

SELECT SYSDATE FROM DUAL;

Selecting values form this table may prove very useful in more complicated queries (where for example you'll like to know the time difference between the date in database and the current time when you're doing the query [or generating a report via the query]).

Try It ALL

You are greatly encouraged to try everything talked about here on some database. Create a sample, run a few queries. Create tables, insert values, etc.,

Learn by doing!

© 2006, Particle