{"id":73216,"date":"2014-01-13T18:23:54","date_gmt":"2014-01-13T18:23:54","guid":{"rendered":"https:\/\/www.red-gate.com\/simple-talk\/uncategorized\/oracle-for-absolute-beginners-part-2-sql\/"},"modified":"2021-07-14T13:07:36","modified_gmt":"2021-07-14T13:07:36","slug":"oracle-for-absolute-beginners-part-2-sql","status":"publish","type":"post","link":"https:\/\/www.red-gate.com\/simple-talk\/databases\/oracle-databases\/oracle-for-absolute-beginners-part-2-sql\/","title":{"rendered":"Oracle for Absolute Beginners: Part 2 \u2013 SQL"},"content":{"rendered":"

A wise man* once said, no one\u2019s ever learned how to cook just by reading recipes. And so, since we painted in the background in Part 1<\/a>, 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.<\/p>\n

[* that wise man was me.]<\/em><\/p>\n

SQL<\/h2>\n

SQL stands for Structured Query Language (pronounced ess-cue-ell<\/i> or sequel<\/i>) 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\u2019s SQL Server, IBM\u2019s Informix, MySQL and dozens of others. SQL is very much the English of the database world; it is spoken in many environments.\u00a0 This is one reason why the skills you are about to learn are very valuable; they are eminently transferrable.<\/p>\n

SQL consists of a data definition language (DDL) and data manipulation language (DML).\u00a0 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\u2019s in place.<\/p>\n

Manipulating data using SQL is easy, as the syntax isn\u2019t a million miles from the way we speak.\u00a0 For instance, to select all the data from a table you would use the SELECT \u2026 FROM table_name<\/i> command.\u00a0 If, on the other hand, you wanted to update data, you\u2019d use the UPDATE command; and the DELETE and INSERT commands pretty much do what you\u2019d expect them to, too.<\/p>\n

It\u2019s easy.\u00a0 Let me show you.<\/p>\n

Creating An Environment<\/h2>\n

Obviously we can\u2019t write database code without first having a database, so we\u2019re going to have to take something of a detour here and set ourselves up with an Oracle database that we can use.\u00a0 We\u2019ve got options: \u00a0we can download one from the Oracle.com<\/a> website. Only problem with that choice is that I like you and I don\u2019t want to put you through the torture of installing a database on our second date.\u00a0 So I\u2019d recommend that we go for option 2, and use one of the hosted workspaces that Oracle makes available on their Apex.Oracle.com<\/a> website. (Apex \u2013 or Application Express \u2013 is this really great software development tool. It is beyond the scope of what we\u2019re doing now, but I\u2019d recommend that you look into it when you\u2019re more confident with your SQL and PL\/SQL. Now, however, we\u2019ll just take advantage of the database space available for free on the Apex website.)<\/p>\n

Requesting and setting up a workspace is reasonably straightforward, no harder than setting up a Facebook profile.\u00a0 You\u2019ll need an email address and a name for your workspace and schema (a schema name is reasonably analogous to a username) \u2013 and no, unlike Facebook, you don\u2019t need to trawl through all your photos looking for a profile pic in which you\u2019re not making a silly face.<\/p>\n

Once you\u2019ve created a workspace and logged in, you\u2019ll arrive at a screen with a number of choices; for our purposes, I need you to click the SQL Workshop<\/b> button. \u00a0(The rest are to do with building Apex applications.)\"Apex-SQL<\/p>\n

We now have, effectively, what is pretty much an empty database (it\u2019s not completely<\/i> empty; there are some demo tables). Before we can really start tearing into SQL, we need to create our tables.<\/p>\n

I\u2019ve put together a script (Oracle_For_Absolute_Beginners.sql<\/a>) that will create and populate the objects we require, and that I\u2019ll need you to run. To do this, click on the SQL Scripts<\/b> button, upload the file, and run it. Once that is done, go back to the SQL Workshop: this time click the Object Browser<\/b> button. You\u2019ll notice that in amongst the demo and Apex tables are our tables \u2013 FRIEND_NAME, ADDRESS, PHONE_NUMBER, FRIEND_ADDRESS and FRIEND_PHONE. Go on, click on them: go to the Data<\/b> tab; you should recognise the records.<\/p>\n

All there? Good. Now, roll up your sleeves; we\u2019re going to write some SQL.<\/p>\n

SQL Cont’d<\/h2>\n

Let\u2019s talk data manipulation: there are four main ways in which we can manipulate our data \u2013 we can SELECT it, we can INSERT new data, we can DELETE data, or we can UPDATE it.\u00a0 I\u2019ll get us started with SELECT.<\/p>\n

Imagine you decide to enter a team into this all-male basketball tournament, and you\u2019re wondering if you actually have enough male friends.\u00a0 Here\u2019s how you find out:<\/p>\n

SELECT first_name, middle_name, last_name\r\nFROM friend_name\r\nWHERE gender = 'M';<\/pre>\n
Click on the SQL Commands <\/b>\u00a0button, and paste the above code into the upper window. Hit run. You should see a list of all your male friends. Chandler\u2019s probably really rubbish at basketball, but that\u2019s not the point. You can probably see a clear line from the code to the resultset, but it\u2019s important that we study the syntax of SELECT statements to find out how they\u2019re stitched together.<\/p>\n
SELECT <comma-separated column list>\r\nFROM <table name>\r\nWHERE <condition(s)>;<\/pre>\n
The required keywords are the SELECT \u2013 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 \u2013 and the FROM \u2013 which must be followed by one table name or a comma-separated list of tables. The WHERE clause isn\u2019t mandatory; if you do not apply any conditions to your query, it\u2019ll return ALL matching records.<\/p>\n
Here\u2019s what I mean. You take a look at your male friends, realise that Chandler Bing couldn\u2019t play ball to save his life, and decide that you need to build your team from all<\/i> your friends, not just the male ones.<\/p>\n
SELECT *\r\nFROM FRIEND_NAME;<\/pre>\n
Notice the difference? This time we use the asterisk (*) in the SELECT clause indicating that we want all<\/i> columns; and we do not include a WHERE clause, indicating we do not want to limit our resultset with any criteria.<\/p>\n
Oh, and did I forget to mention that you must end your statement with a semi-colon? Yeah, that\u2019s mandatory for all SQL statements.<\/p>\n
We\u2019ll 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\u2019s 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\u2019t need a WHERE clause for this).<\/p>\n
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\u2019ll need to put them in a comma-separated list in the SELECT clause).<\/p>\n
Hopefully you\u2019ve 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\u2019ll 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.<\/p>\n
SELECT HOUSE_NO, STREET\r\nFROM ADDRESS\r\nWHERE HOUSE_NO = 90;<\/pre>\n
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.<\/p>\n
Talking about where clauses gives me the opportunity to mention a few other things that you\u2019ll want to remember.\u00a0 I\u2019ll group these facts according to the different data types.<\/p>\n
VARCHAR2:<\/h3>\n