A wise man* once said, no one’s ever learned how to cook just by reading recipes. And so, since we painted in the background in Part 1, we are now going to roll up our sleeves and dive in. By the end of this article you will be reading and writing SQL, the lingua franca of databases.
[* that wise man was me.]
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 Informix, 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 transferrable.
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.
It’s easy. Let me show you.
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 workspaces 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.)
Requesting and setting up a workspace is reasonably straightforward, no harder than setting up a Facebook profile. You’ll need an email address and a name for your workspace and schema (a schema name is reasonably analogous to a username) – and no, unlike Facebook, you don’t need to trawl through all your photos looking for a profile pic in which you’re not making a silly face.
Once you’ve created a workspace and logged in, you’ll arrive at a screen with a number of choices; for our purposes, I need you to click the SQL Workshop button. (The rest are to do with building Apex applications.)
We now have, effectively, what is pretty much an empty database (it’s not completely empty; there are some demo tables). Before we can really start tearing into SQL, we need to create our tables.
I’ve put together a script (Oracle_For_Absolute_Beginners.sql) that will create and populate the objects we require, and that I’ll need you to run. To do this, click on the SQL Scripts button, upload the file, and run it. Once that is done, go back to the SQL Workshop: this time click the Object Browser button. You’ll notice that in amongst the demo and Apex tables are our tables – FRIEND_NAME, ADDRESS, PHONE_NUMBER, FRIEND_ADDRESS and FRIEND_PHONE. Go on, click on them: go to the Data tab; you should recognise the records.
All there? Good. Now, roll up your sleeves; we’re going to write some SQL.
Let’s talk data manipulation: there are four main ways in which we can manipulate our data – we can SELECT it, we can INSERT new data, we can DELETE data, or we can UPDATE it. I’ll get us started with SELECT.
Imagine you decide to enter a team into this all-male basketball tournament, and you’re wondering if you actually have enough male friends. Here’s how you find out:
SELECT first_name, middle_name, last_name FROM friend_name WHERE gender = 'M';
Click on the SQL Commands button, and paste the above code into the upper window. Hit run. You should see a list of all your male friends. Chandler’s probably really rubbish at basketball, but that’s not the point. You can probably see a clear line from the code to the resultset, but it’s important that we study the syntax of SELECT statements to find out how they’re stitched together.
SELECT <comma-separated column list> FROM <table name> WHERE <condition(s)>;
The required keywords are the SELECT – which must be followed by the name of one column, a comma-separated list of columns or an asterisk (*) which indicates that you want ALL columns – and the FROM – which must be followed by one table name or a comma-separated list of tables. The WHERE clause isn’t mandatory; if you do not apply any conditions to your query, it’ll return ALL matching records.
Here’s what I mean. You take a look at your male friends, realise that Chandler Bing couldn’t play ball to save his life, and decide that you need to build your team from all your friends, not just the male ones.
SELECT * FROM FRIEND_NAME;
Notice the difference? This time we use the asterisk (*) in the SELECT clause indicating that we want all columns; and we do not include a WHERE clause, indicating we do not want to limit our resultset with any criteria.
Oh, and did I forget to mention that you must end your statement with a semi-colon? Yeah, that’s mandatory for all SQL statements.
We’ll talk a bit more about the WHERE clause a little later, but I should stop hogging the limelight and let you have a go. Let’s say you need to view all the addresses your friends live at. Write a query to select all the columns from the ADDRESS table (hint: you don’t need a WHERE clause for this).
Done that? Good work. Now, try writing another query selecting only the HOUSE_NO and STREET columns from the ADDRESS table (hint: to select specified columns you’ll need to put them in a comma-separated list in the SELECT clause).
Hopefully you’ve managed that and are now looking, slightly smugly, at a list of addresses. However, what if you did not want a list of ALL addresses; what if you only wanted a list of addresses at House 90? Then we’ll need to write a WHERE clause telling Oracle not to return ALL the records in the table, but only those matching the condition we set. Let me show you what I mean.
SELECT HOUSE_NO, STREET FROM ADDRESS WHERE HOUSE_NO = 90;
The Where clause comes after the FROM clause and is made up of one or more conditions that may be true or false with Oracle returning all records that evaluate as true.
Talking about where clauses gives me the opportunity to mention a few other things that you’ll want to remember. I’ll group these facts according to the different data types.
- 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).
- 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.
SELECT FIRST_NAME, MIDDLE_NAME, LAST_NAME FROM FRIEND_NAME WHERE GENDER = 'F' OR UPPER(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 resultset in descending order (the default order is ascending).
SELECT LAST_NAME, FIRST_NAME FROM FRIEND_NAME WHERE MIDDLE_NAME IS NULL ORDER BY LAST_NAME DESC;
Please note that, as in the example above, your ORDER BY clause must be the final clause, coming after your WHERE clause (if you have one).
This is all getting very exciting, but I think we should stop and take a breath here. We’ve learned how to construct select statements, modify the results we get and control its order. Next time we’ll talk about adding data to our tables, deleting it, modifying it, and writing select statements that get data from more than one table. But just so you don’t miss me too much in the meantime, here are some teasers for you to wrap your head around.
- Write a query selecting the first name, middle name and last name of all your male friends and any friend named Phoebe. Make your query case-insensitive.
- Amend the query you’ve written above to order the result set by your friends’ genders. Find out if you can order using a column that you have not selected.
- Write a query selecting all columns in the PHONE_NUMBER table for records with a phone_id between 2 and 6. Using the phone_number column, rearrange your resultset in descending order.
- Rewrite your query to select only the phone_number column for all records with a phone_id that is greater than or equal to 2.
- List the middle name and last name of all your friends who have a middle name, in descending order of surname.
- Write a query to find out if you have any friends that do not have a last name.