SQL stands for Structured Query Language (pronounced ess-cue-ell or sequel) and is the programming language used in the management of relational databases. And not just Oracle RDBMS; the code we are about to learn will work just as well with Microsoft’s SQL Server, IBM’s Inform ix, MySQL and dozens of others. SQL is very much the English of the database world; it is spoken in many environments. This is one reason why the skills you are about to learn are very valuable; they are eminently transferable.SQL consists of a data definition language (DDL) and data manipulation language (DML). What this means is that we use SQL not only to define the tables into which we plan to put our data, but to manipulate (query, edit, delete, stuff like that) the data once it’s in place.Manipulating data using SQL is easy, as the syntax isn’t a million miles from the way we speak. For instance, to select all the data from a table you would use the SELECT … FROM table_name command. If, on the other hand, you wanted to update data, you’d use the UPDATE command; and the DELETE and INSERT commands pretty much do what you’d expect them to, too.
Creating An Environment
Obviously we can’t write database code without first having a database, so we’re going to have to take something of a detour here and set ourselves up with an Oracle database that we can use. We’ve got options: we can download one from the Oracle.com website. Only problem with that choice is that I like you and I don’t want to put you through the torture of installing a database on our second date. So I’d recommend that we go for option 2, and use one of the hosted work spaces that Oracle makes available on their Apex.Oracle.com website. (Apex – or Application Express – is this really great software development tool. It is beyond the scope of what we’re doing now, but I’d recommend that you look into it when you’re more confident with your SQL and PL/SQL. Now, however, we’ll just take advantage of the database space available for free on the Apex website.)
VARCHAR2:
If you want to compare a VARCHAR2 character string you must enclose it in single quotes. (Where street =‘Bedford Street‘ is correct; where street = “Bedford Street” is not.) You must use the straight quote marks (‘); if you use curly quotes, Oracle will error.
Comparisons of varchar2 strings are case-sensitive. (Where street = ‘Bedford Street’ is not the same thing as where street = ‘BEDFORD STREET’.) Often you will not want your query to be so finicky with cases; in those instances you can use the following function: where UPPER(street) = UPPER(‘Bedford Street’). What this does, is convert both strings to upper case. (You can use the LOWER function – where LOWER(street) = LOWER(‘Bedford Street’) – which converts them to lowercase).
NUMBER:
You do not need to enclose numbers in quotes to compare them. That is why, in our example, we could say where house_no = 90;
The equals sign isn’t the only operator you can use with numbers (or other data types). All the following also make sense:
House_no < 90;
House_no <= 90
House_no > 90;
House_no >= 90;
House_no != 90; – which, in case you’re unsure, means the house number is NOT equal to 90.
You can also use ranges in your comparisons. If, for example, you knew the house number you were looking for was above 80 but below 100, there are two ways you could write your query.
SELECT HOUSE_NO, STREET
FROM ADDRESS
WHERE HOUSE_NO >= 80
AND HOUSE_NO <= 100;
However, the following works just as well:
SELECT HOUSE_NO, STREET
FROM ADDRESS
WHERE HOUSE_NO BETWEEN 80 AND 100;
Got that? There is one more concept that I would like to discuss, and that is the NULL. A NULL is the term we use to describe something that is undefined, that has no value. It is not the same thing as the number 0 (because 0 itself is a value); it is undefined, nothing. Look at the data in our FRIEND_NAME table again (navigate to it by going back to the SQL Workshop screen and clicking the Object Browser button). Some of our friends have middle names: Joey’s middle name is Francis, but Phoebe’s middle name is undefined, nothing. It is NULL.
MIDDLE_NAME is a VARCHAR2 column, but you can have nulls in all types of columns from VARCHAR2 to NUMBER to DATE.
Because nulls have no value, the usual comparison operators (=, >, < and the rest) do not work with them (think about it; how can anything be equal or greater than something that is undefined?). For this reason, if we wanted to write a query to return all our friends who have a middle name, we would have to phrase it as follows:
SELECT *
FROM FRIEND_NAME
WHERE MIDDLE_NAME IS NOT NULL;
Conversely, if we wanted to return only those who do NOT have a middle name, it would be:
SELECT *
FROM FRIEND_NAME
WHERE MIDDLE_NAME IS NULL;
The IS NULL and IS NOT NULL operators work with columns of all data types – VARCHAR2, NUMBER and DATE.
Speaking of the Date data type, I know I should now speak about the operators that work with them, but I would rather we go back to discussing Where clauses. Once we’re old pros with them, we’ll return to Dates.
You may have noticed that we can have more than one condition in our Where clauses. In fact, using ANDs and ORs, we can build rather complex Where statements. I’ll show you what I mean.
We’ve already established that your friend Chandler is rubbish at basketball. So when you see a flyer for an all-female cheerleading team, you think it’ll be funny to send it to all your female friends and to Chandler. But how do we write a query that’ll give us all the names of your female friends – and Chandler.
Here’s how.
SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME
FROM FRIEND_NAME
WHERE GENDER = 'F'
OR UPPER(FIRST_NAME) = 'CHANDLER';
You might want to pay attention to how we’ve used the OR to link two conditions in our Where clause. It’s also worth noting how we’ve used the UPPER function to make our query case-insensitive.
We’ve talked about the SELECT clause (in which we list the columns we want our query to return), the FROM clause (in which we list the table(s) we want to query) and the WHERE clause (in which we apply conditions to our resultset). There is one further clause that we need to discuss – the ORDER BY clause. Using the order by clause we can sort the resultset according to rules we tell Oracle. Run the two following queries and notice the difference.
SELECT LAST_NAME, FIRST_NAME
FROM FRIEND_NAME;
SELECT LAST_NAME, FIRST_NAME
FROM FRIEND_NAME
ORDER BY LAST_NAME;
You can use more than one column in your ORDER BY clause; if you do, Oracle will sort your resultset using the first named column and, in cases where the values in the first column are identical, will sort by the second column (and then the third etc).
You can also instruct Oracle to order your result set in descending order (the default order is ascending).
SELECT LAST_NAME, FIRST_NAME
FROM FRIEND_NAME
WHERE MIDDLE_NAME IS NULL