Oracle for Absolute Beginners: Part 4 - Multi-table queries

Read: Part 1, Part 2, Part 3

A wise man* once said: To build the Great Wall of China, you must start with a brick. In our previous articles we acquainted ourselves with our bricks; now it’s time to build. If we consider what we’ve learned so far – Select, Update, Insert, Delete – as unicellular organisms, what we’re about to do next is multicellular, big and beautiful – it’s like going from an amoeba to a bee, a butterfly, to Beyoncé.

[*that wise man was me]

Consider a real-world requirement that we might have of our Addressbook database; since it contains a list of our friends and their phone numbers, we will naturally want to see a list of their names and their phone numbers. Ah, but that presents a problem. Our friends’ names are in the FRIEND_NAME table, while their phone numbers are in the PHONE_NUMBER table. And complicating things further, we can only tell which number belongs to which friend by looking in the FRIEND_PHONE table. Aaargh!

JOINS

We could, of course, get the information by running a series of queries: one select to find our friends’ names and their friend_id; a second to find the phone_id of the phone number linked to each friend in FRIEND_PHONE; and a third query to find the number from PHONE_NUMBER using the phone_id we identified in our second query. So yes, it can be done. But hey, you can probably ride a unicycle across Siberia – but that didn’t stop them from inventing the car.

What we need are joins. We need a select statement that can query multiple tables at the same time.

The syntax for a multi-table select statement is as follows:

Continue reading...

Oracle for Absolute Beginners: Part 3 - Update, Insert, Delete

Read: Part 1, Part 2

A wise man* once said: So no-one told you life was gonna be this way. Your job’s a joke, you’re broke, your love life’s DOA. It’s like you’re always stuck in second gear. And when it hasn’t been your day, you week, your month, or even your year.

[*That wise man wasn’t me; but stick with me, I promise to bring this back around to Oracle SQL imminently. ]

So – since it hasn’t been your day, your week, your month, or even your year – you decide to go to Central Perk with your friends Ross, Rachel, Monica, Phoebe, Chandler and Joey.  Oh, and that new friend you made that’s also named Ross Geller.  The music is loud, the atmosphere is great, and a good time is had by everyone.

You wake up the next morning with a bit of a sore head and try to recall everything that went on the night before: you’d discovered that Chandler’s middle name is Muriel, and that Ross’ is Eustace. Rachel gave you her new phone number. Phoebe gave you her new address. Oh, and you had a big fight with new Ross and decided you no longer want to be friends with him.

You roll out of bed and groggily wipe your eyes: all this new information needs recording; you’d better fire up your database.

UPDATE

Since we thoroughly looked into SELECT statements in the previous part, we can now turn our attention to UPDATE statements. In SQL we use update statements to change existing records – not to create new records or to delete them – just to change them.

The syntax for update statements is as follows:

Continue reading...

Oracle for Absolute Beginners: Part 2 - SQL

Read Part 1

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

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.

Continue reading...

Oracle for Absolute Beginners: Part 1 - Databases

A wise man* once said, an expert is someone who uses big words and acronyms where simple phrases would do just as nicely. So stand back and listen to this: Database, Relational Database, DBMS, RDBMS, SQL, Sub-queries, normalisation.

[* that wise man was me.]

So now that I’ve established my credentials by bamboozling you with arcane words and capital letters, let me tell you what the purpose of this series of articles is. By the end of it, you will be able to re-read that first paragraph and understand every word; or, if you would prefer that in more practical terms, you will be able to read – and write –  SQL, which is the programming language of databases.

Definitions

Let’s meet the main characters of our story: I’ll give you a couple of definitions; one building on the other.

A Database is an organised collection of data. Not yet sure what that means? Well, do you own an address book, either on your phone or in a physical book? That’s a database. After all, the addresses and phone numbers areorganised – with all friends whose names start with A being grouped separately from people whose names start with B or C or D.

A Relational Database is a database in which the data is organised according to type with the relationships being maintained between the differing types. Okay, that sounds a bit like Greek (or Dutch, if you’re Greek; or German, if you’re Dutch; or Xhosa if you’re German…), but it makes sense if you let me explain.

Dig out your address book again. Imagine all the names grouped together; and all the phone numbers grouped together in another list; and all the addresses in a third. On their own these individual lists might be interesting but not useful; but if we establish the relationship between the lists – this address is where that person lives and that’s their phone number – then our database takes shape.

Make sense? Don’t worry about it too much if it doesn’t; we’ll come back to it a little later. Let’s talk about Oracle now.

Continue reading...