Overview of Oracle SQL Course Essentials

Hello and welcome to this introductory course on Oracle SQL. I'm Philip Burton of filecats.co.uk and I'm the creator of so far eight best-selling courses on Udemy. Oracle SQL is the world's most widely used database engine and therefore is required knowledge for a huge number of jobs. And in this one hour video I'll be introducing you to Oracle SQL. We'll start by installing for free on your computer Oracle Express Edition that's the backend the actual database and Oracle SQL Developer that's the part that you use to write code. If you don't want to install them at this time that's fine as well. I'll then be teaching you how to write SQL select statements. We'll be using the six principal clauses. Select from where, group by having an order by. We'll also see how to remember their order quite easily and that could be very important for job interviews. Once you have done this then you will have learned an important skill and be way ahead of most people. And that knowledge alone plus a bit of practice could be the difference between you being able to get your next dream job or not. I'll also be giving you a practice activity so you can be sure that you have learned this important skill. We'll do and see what else there is, what else Oracle wants you to know, how you can progress further. So thank you for joining me in this video course and without any further ado, let's start learning. Now the first thing we have to do is install the database engine. So this is the bit that runs in the background that does all of the hard work. But which version do we need to install? Well let's scroll down and you can see that this exam is validated against 11g release 2 and 12c release 1. Okay so what is 11g and 12c and are they the latest? Well if you go to the Wikipedia page you can see that 10 was first released in 2003, 10g. 11 was released in September 2007 and updated September 2009. And then 12C was released in July 2013. And the latest at the time of recording is 18C, which was released in February 2018. Though at the time of recording they are working on 19C. So you can see that the years are not represented by the numbers, except for the latest ones which are. So what is the 10, 11, 12? Well that's just the standard release number. So it started from 2 and it went up to 10, 11, 12 and finally Oracle decided it would make year versions, so 18 and 19. OK, so what is G as opposed to C? Well, 11G refers to something called grid computing, which supports clusters of servers which are treated as one unit. 12C refers to the cloud. It is allowed you to put databases into public or private clouds. So don't be too confused by the lettering. There wasn't a 10F, 10E, 10D or 11A, 11B. It jumped from 10G to 11G to 12C to 18C. So, so what? What on earth does all of this mean for you? Well, the only thing really that it means, well two things maybe. First of all, this exam is validated against 11g2 and 12c1. So you need to install at least one of those versions. So you can install later versions like I'm going to recommend you do with 18c. Now there are changes between each database type. So there's a jump from 11g to 12c they've added a few new commands but you'll see on the right hand side the extra commands that they have added and to be honest at this sort of course it doesn't make too much of a difference. There may be one or two commands maybe which are missing in 12c that we might use or which were available in 12c which are missing in 11g. But why am I talking about older versions? for one really important reason. If you have a 32-bit computer, you cannot use 12C or 18C. You have to use 11G. So, how can you check if you've got a 32-bit machine? Well, if you open up Windows Explorer and you right-click on My Computer or This PC and go to Properties, you'll see whether you have got a 64-bit operating system or a 32-bit operating system. So if you've got a 32-bit operating system, then 11g is the latest you can install. If not, I recommend you install 18c. But which version? As you can imagine there are lots of different types of version. There's the Oracle Database Enterprise Edition. This is the fully featured and very expensive version. I don't recommend you get that for some strange reason unless you do have a huge number of dollars available. The Standard Edition, that requires less money. The Personal Edition, that costs about $460. So I recommend you get the Express Edition, which is a free to use version. Now it does have limitations. You can use only up to two CPUs, which isn't going to be much of a problem with what we're doing, but if you're going to use much more intensive computing it would be. And the other issues here up to 12 gigabytes of user data, two gigabytes of RAM. We're not going to go anywhere near it on this course. So how can you download Oracle Database 11g XE? That's the Express Edition. So XE to distinguish it from EE being the Enterprise Edition or version 18c. Well, it's actually quite easy to download the Oracle Database Express Edition. 18c, it's a lot harder to download 11g, but I'll show you how to do it. If you go to oracle.com forward slash downloads and scroll down, you'll see here we have databases and keep scrolling down, we have the database 18c express edition. So if I open that in a new tab, This is where you download it if you've got a 64-bit computer. And you can see here Oracle Database 18c Express Edition for Windows X 64, that's a 64-bit. First of all you have to accept the license agreement, read it first, then click download, and then you have to sign in. So if you have got an Oracle account then use your username and password, if not it's free, just click. create account type in your details you'll get a email asking you to validate your details that you have given and then you'll be able to download it so i'll just go back and i will type in my username and password and click sign in and i've done that and you can see it's downloading here it's 2gb it will take a little while. Now let's suppose you don't have 64-bit, you've got a 32-bit computer. Well, bizarrely, you have to click on the Database 11g Enterprise Stroke Standard Editions, even though we want the Express Edition. And what we can do, first of all, you can accept the license agreement and obviously read it first, but then scroll down. This is a 19c I was telling you about, the current version that the are developing, scroll all the way down to the 11G and then keep scrolling. Because here you can see near the bottom Oracle Database Express Edition 11G for Windows. Now, this is the only version which is not for Windows 64. So clicking on this express edition will get you back to downloading this for 64 bit. There is no option here for downloading for 32 bit. It's this bottom one you want. Oracle Database 11g for Windows. So just click on that, click on Accept License Agreement. And here we have Windows X 32. Click Download and you can see the instructions. Unzip the download and then run disk1 slash setup. So there you can see it's a much smaller install but I recommend if you can to install the larger version. It's the latest version, it's got slightly more functionality. So I'm going to pause the video here and on the next video we're going to install Oracle Database. Oracle Database Express Edition has now been downloaded. So I'm going to open it up in my Downloads folder and I'm going to extract it into its own folder. So here's the folder. I go down to the setup, double click on it And here you can see it is now installing. So first of all it's installing some prerequisites, some things that it needs beforehand, before it can install. Now on my computer it then mysteriously, the window mysteriously vanished so I'm going to run it again. So click setup.exe Now you can see configuring Windows installer and this is what I'm expecting to see. They will install Oracle Database 18c express edition, have a read of everything and accept the terms of the license agreement if you so wish. You can see the default place that it's going to install it. So I'll click Next. Now this is important. You're going to have to enter in a password which will be used for SYS system and PDB admin accounts. So the important thing about this is this is going to be the major password that can get access to everything within the database. So I'm going to type in a password twice and make it slightly complex. But the important thing is I can remember it. So click install and it's going to install and it says this may take several minutes. It will indeed because it was a very hefty download. So I'm just going to speed up this part of the video so you can see it install. So it took about 20 minutes to install but there were no problems whatsoever. So I'll click finish and that is the back-end installed on my 64-bit machine. So that's the back-engine installed what about the front-engine? What about being able to actually use it? Well for this we're going to use a program called Oracle SQL Developer. Now there are alternatives to this. Some are really much older, but this one allows us to use a lot of functionality. Plus, if you did want to go further into programming language, the PL SQL, then SQL Developer also allows you to do this. So, I'm going to see that there are two versions. There is a Windows 64-bit with... JDK, that's the Java Development Kit installed. Or there is a version for 32-bit or 64-bit which requires the Java Development Kit to be installed separately. So I'm going to download this 64-bit. First of all you need to agree the license agreement. And I'm also going to download the 32-bit and this takes two downloads. First of all, if you've got a 32-bit machine, you need to download this 32-bit and you need to download the JDK8. Now if you do this, the JDK8 needs to be installed first. So let's just scroll down. and we can see Windows and you can choose 86 or 64. Now you might be asking what is 86? It's a really really old-fashioned name for 32-bit. So you can click whichever download you're going to install it on. So first of all you need to accept the license agreement which is just here and download which version you're going to use. Now it's a lot easier, it's just one install, if you are just using the Windows 64-bit with JDK 8 included. But it's not really that much more difficult with JDK 8 and Windows 32-bit. So now we've got these downloading in the next video, we'll install them. I should also say that the version that I'm installing is SQL Developer 19.1 of the April 2019. And the version you'll be installing will be a slightly later version. However, there really is minimal change between the one that I'm installing and the one that you're installing. So whichever the latest version is, as long as it doesn't say it's a beta version, a test version, then you are okay to install it, to download and install it. So Oracle SQL Developer has been downloaded. So we're going to do exactly the same thing. We're going to extract the files. It's a zip file. It's taken about 30 seconds to extract. I'm going to the SQL Developer folder, scroll down and click on the SQL Developer application. So here you can see it doing the first part, the installing the Java development kit. I don't want it to import preferences from previous version because I don't have one. And then it just installs Oracle SQL Developer. So allow automated usage reporting to Oracle. I'm going to skip the automatic check for updates. This is an XE version. There won't be any new patches. And so here we have Oracle SQL Developer. So that's how you install it on a 64-bit machine. When you open up Oracle SQL Developer, it looks very busy. There's this big welcome page. But do I just click an X and it disappears? So you can see that there isn't actually much going on. There's a report which we're not going to be using and there's connections. Now what do we need a connection for? Well Oracle SQL Developer is the front engine and we've previously installed Oracle Database Express Edition. So we need those two to talk to each other and the easiest way to do that is just click on the plus next to the connections. So we have this dialog box a new database connection we only have to set this up the once. So we can call this anything, so I'm going to call this SQL database. Now you can give it a colour, and I would advise you to give it a colour, simply because when you connect it will display that colour in a couple of places. Now you can see which database we're going to be connecting to, it's the local one. Everything else is basically set up for you. The only thing that's not set up is your username and password. So I'm going to come in as sys. You remember we, as part of the installation process, created a password for the sys. So I'm going to use that username and the password that I previously set up and I'm going to click save the password. Because this is just a demo database it's not going to contain any real data. And then the final thing we need to do is change the role. Now if you look down here at the status, the status is blank. If I click on test you'll see that the role should either be SYSDBA or SYSOPER. Well you can see from the online database, the online library, that SYSDBA is for fully empowered database administrators. It allows you to see everything. Whereas Sys OPR allows you to do basic operational tasks but without the ability to look at your data. Well as we're going to be learning SQL we certainly want to see our data so we're going to have the role of SYSDBA. So just change role to SYSDBA, click test again and you should now have status success. So it's very complicated or it looks very complicated but the good news is you only have to do this. this once, at least for our course. So click connect and you'll see that we now have a new connection, this SQL database. And you'll also notice it's in red and we have a new window which is also in red. Now if you don't like this particular color, no problem, just right and click on it, go to properties, and you can change the colour. So I'm going to change it to a more standard white and connect. Now, or maybe black in fact would be better. Now the reason for having all of these colours is if you connect to multiple databases then you'll be able to see very swiftly which database you are in. So let's close all of these tabs right and click close all. So now we have created a connection. to our database. Now that for me is the most complicated bit done. We have installed the back end, we've installed the front end which is SQL Developer and we have now created a connection. The rest of this course actually allows us to focus on what we actually want to do. The select statements, all of the coding and everything else that's required for this certification and to learn SQL generally. Now in this video, I just want to mention one problem that you may get from time to time when trying to connect to SQL Developer. So if I click on my recent connection here, you'll see that it starts to run, but then at the bottom, but then finally says the network adapter could not establish the connection. Vendor code 17002. So this is just in case you get this. You go to start and you find the command. window. Now it may be in different places, the command prompt. What I do is I go down to the search engine at the bottom, the start menu and I type in CMD. That gets me the command prompt. So from here we can see if there's something called the listener. So this is the database listening for new connections, whether it is just taking a rest. And to find this out I type in lsnrctl status and if it is taking a rest then you will see unknown error and no such file or directory. So to start the listener exactly the same lsnrctl start and it hasn't worked for me. I've got a protocol adapter error so if I'm getting this then I'm going to do a reboot. Well, I've tried rebooting the computer and that didn't work. So we'll go into the next thing we can do. Go to start and go to administrative tools. So again, I'm just going to type in administrative tools into my search and we're going into services, double click onto services and then scroll down to Oracle. Now the exact name might be slightly different, but it's the Oracle TNS Listener. So you can see it's not running and I'm going to click start. So there are the services now started. So now if I open up again SQL Developer and now click on my recent connection, it may initially refuse. but then try it again and you should get it working. So here are two solutions. If you get the error, network adapter could not establish the connection. Now I've just closed SQL Developer just to show you that if you want to reopen it, go to the folder where you have extracted it from and double click on SQL Developer application. Alternatively, you can right-click on it and you can pin it to the Start menu or to the Taskbar. And you'll see at the moment I've done both. So if I just re-pin it, here you can see pinned to Taskbar. And then it's included in my Taskbar. with no underline if it's closed. So that gives me easy access to it. So I'm just going to open up SQL Developer, either by double clicking on the application or clicking where you have pinned it. And so you get back to where you are, where you were. Now, you remember in the previous video, we created a new database connection. Let's expand this database connection and see what we've got. And you can see we've got an awful lot of different types of objects, tables, views, indexes, packages and so forth. Now in this section of the course I want to talk to you about the SELECT statement. The SELECT statement, well other programming languages use the word print. And that's essentially what SELECT does. It prints information somewhere. Now I don't mean it prints to pieces of paper. It can print to a new pane at the bottom of the window. Or it could store what you are putting into the select statement internally. Now the select statement is built up of six different clauses. And those clauses are select, from, where, group by, having and order by. And we'll be looking at each one in turn. So when we say select statement, that includes, well, it's got to include as a minimum, the SELECT clause and the FROM clause, and it can include any of the other four. So what I'm going to do is have a look at one particular table. Now let's expand this tables over here, and we can see that there are an awful lot of tables. And in fact, there are probably some also hidden as well. Now I'm going to just temporarily have a look at one particular table and that is called tabs or tab with a dollar and if you click on it you can see some information about it so you can see that it's got various column names called ob object number file number tab again table number and so forth now we're not actually going to use the information that's in here I just wanted to just do a very quick select statement based on tab$. And that statement is select star from tab$ semicolon. So what this does is it's going to print all of the fields, that's what that star or asterisk is, from the table. Tab dollar and the semicolon ends this sentence, ends this select statement. So I'm going to highlight this and click on the play button. And you'll see that there is a keyboard shortcut, which I highly recommend you remember, which is control and enter. And in fact, there's going to be a few keyboard shortcuts. So I'm going to be making a note for them, at least the important ones. And earlier on. There was a lecture called Resources and it had attached various things including a list of all of the keyboard shortcuts, at least all of the important ones. So I'm going to play or press Ctrl and Enter and you can see down here at the bottom the first 50 rows, the first 50 records. And you can also see all of the data in these individual columns. So just like a spreadsheet we have rows and we have columns. Now, are there only 50 tables? No, there aren't. If you want to force the computer to find some more, then scroll down to the bottom and it will retrieve and retrieve and retrieve. And you might be thinking this might take some time with bigger tables, and indeed it would do. So another way of doing that is just click somewhere inside the table and press Ctrl and A. That forces the computer to get all of the information back. So now you can see all rows fetched, 2,174 rows. Now let's say I didn't want all of these columns. Suppose I just wanted the first five or six or something like that. Well let's go into this tab dollar and expand. Click on the plus next to it. So we can see we have fields called OBJ hash or pound sign, data OBJ hash or pound sign and so forth. What I'm going to do is replace this star, this asterisk, by OBJ and then the hash or pound sign. So now let's play and here we can see the result. It has the same number of rows, which I can confirm quickly by clicking somewhere in the results and pressing Ctrl and A. So 2174. It's just that we have restricted the number of columns. Suppose I want a second column. That's fine, I go to the Select clause, I type comma, and I put in the next one. Data, or bj, pound sign or hash, play, and there we are. We now have two columns. OK, let's add a third column. Let's add ts hash, but I'm tired of doing all of this typing. So what I'm going to do is I'm going to drag this TS hash over here. Now, there is something very interesting. When I release, we get this dialog box and it says, OK, what is it you want me to do? And I just want you to insert the object name. So click apply. And so now if I run, you'll see there's a third column. But let's add a new select query. So, and I'm not even going to type this select query. I'm going to just drag this TS hash or pound sign across to our worksheet. And I'm going to leave it at this, select individual statements. Look how much work this can save you. Equally, I'm going to delete that and I'm going to drag the table across. Now this is a bit more difficult. Let's see what happens if I get it wrong. I click on it, I start dragging, I accidentally go up. Well, it's now highlighted the table that's above and has given me the information about the table above. That's not what I want. So I have to drag directly right until I leave this connections pane. And now I can go up and down and so forth when I'm in the worksheet. So now if I release and select individual statements, this gives me every single field from this particular table. And now let's say I wanted the first five columns, five fields, that's fine. I just highlight the ones I don't want, press delete. And as long as it doesn't end with a comma, it'll be fine. You can see there is a squiggly underline underneath the from. That's the computer telling you, hang on, I'm not really sure that this is right. And if I click play, you'll get an error message saying missing expression. In other words, it's expecting something after the comma. So let's get rid of that comma, run it again. And there we go. We have 2700. 2174 rows and five columns brought back. Now in this video, we're going to create a different select statement based on a different table. So tab dollar gives you data about things. It's called metadata. And in Oracle SQL, all of these metadata system tables are called the data dictionary. So we're going to use a different table in the data dictionary, and this is called col dollar. So just as tab Dollar gives you all the information about tables. Cold dollar gives you all of the metadata about the columns within the tables. So I'm just going to delete what we currently got and type select star from cold dollar. And you can see I moved it up too quickly. It's one of those things that you just got to get right. You've got to move it straight to the right. And for that reason, quite frankly, that's why I Often have this pane open as little as possible so there's less distance. So if I have a look at this, we can see that there are all of these columns. Now I don't necessarily want all of these columns. I want the column number. I want the name and I want the type. So run that. We have the same sort of information. It's just that we now have three columns. So we have 120,000 rows retrieved in about one and two thirds seconds. Now for me, col, number, name, type, name, they're not the best names that I would want. So what I'm going to do is rename this first column. It's currently got col and then a hash or pound sign. So let's call it column number. And what I'm going to do is I'm just going to type the one word column number immediately after it. So space, column number and then the comma. So let's run that and see whether that's worked. And you can see, yes, that has worked. We now have a new first column called column number. Now, we don't just have to have new field names made up entirely of letters. We could add numbers to it. That would work. And we can add an underscore. So that separates out the words. And you'll also notice that column number is displayed up here entirely in upper letters. So given that that's what happens, we probably would be best to use open letters here as well. I'm going to change select to upper, from to upper and name to upper. So they're all consistent, but it doesn't completely matter so much whether you're using lowercase or uppercase. It does in some contexts, but just not at the moment. Now, what if I wanted to replace this underscore with a space? Would that work? Let's have a look. Well, the squiggly underline underneath the word number. tells its own story. The error message is not actually helpful. From keyword not found where expected. What this really is trying to tell you is that this new name, it doesn't know what the new name is. Is it column space number? Is it column space number, comma, name? Is it columns, number, name, type? That could be the new name. If I'd wanted column number to be the new name, then I need to put quotation marks around it. So the computer knows where it starts and where it ends. So now you can see that column space number is the new field name. Equally, if I put the end quote right at the end, how many columns would I get back? I'll just get the one back because this new column is called column number, comma, name, comma, type number. So But that is why we need to have these quotation marks around this new field name. And that is why this is called a quartered identifier. Ones with quotation marks around it. Now there is another version called non-quartered identifiers. So name is a non-quartered identifier. Type number is a non-quartered identifier. because they don't have these quote marks around. So what's the difference between a quartered identifier and a non-quartered identifier? Well, both can use letters, both can use numbers, and both of them can use the underscore, the dollar sign, and the pound or the hash sign. Both of them have to be 1 to 30 characters, so you can't have really long column names, but you probably won't want to. If you wanted to put a comment for a column, we can do that later on. There are also certain words that you can't use. I mean, you couldn't call the column called Select, for example. So avoid words that Oracle SQL will want to use. And finally, non-quoted identifiers must start with a letter. So anything that doesn't follow these rules, so you have, for instance, a space, or you start with a number. I don't recommend that at all, but if you did, then these are quartered identifiers and they're fine, it's just that you must have quotation marks around them. Now you can have quotation marks around identifiers that don't need them. So here I've got quotation marks around name and that's fine. It's just not usual. So basically try to use non-quoted identifiers whenever possible, simply because it saves you having to use quotation marks. But if you do need to, no sweat. Now, this isn't how I would normally do these column renames. I would put the word as in between, A S. So that says, give me this original column, but give me as if it was named something else. So rename it to that. Now, how important do I think using as is? Well, it's completely optional. It's down to you. But let me type in. Another SELECT statement. So SELECT and I'm going to put in the first three columns here. So object, column and SEG column from col$. Have you spotted what I've done wrong? So let's have a look. Let's play this. See if you're right. It returns two columns only, and that's because it is treating this one as saying, give me back the column originally called column number, but rename it segColNumber. Now, this may be what you actually want. It just so happens there is another column in there called segColumn, but it may be that you do want to rename it. But from a human level, this is ambiguous. Not from a computer level. The computer level The computer knows what it's going to do, it's going to bring back two columns. But my question is, on a human level is, did you forget the comma, which would bring back three columns, or was this deliberate? And if this was deliberate, I'll put the word as in the middle, just so it tells me, the human at the end, that this is really what you did want to do. So this is how to rename fields. You have quartered identifiers and non-quartered identifiers. And the letters, the symbols that you are allowed in non-quartered identifiers, or letters, numbers, have three different types of punctuation, and it must start with a letter. Otherwise, you're going to have to use quotation marks around your new column name. In the previous video, we had put two queries, each properly ending with a semicolon. But how important... is that semicolon really? Let's take it out and find out. So let's play this first query. Seems to work. Let's play the second query. Again, seems to work. And the reason it works is because Oracle SQL Developer implicitly adds the semicolon to the end where it's missing. Other applications might not. So they might just reject the statement because it doesn't have that semicolon. But let's see where a problem does lie in Oracle SQL Developer. Let's run both of these queries together. Press play. And you can see we have an error. SQL command not properly ended. Whereas, if I put the semicolon at the end of each, and press play, you can see that we have two answers, two query results. It's difficult to see that, I'm going to close all of the windows by right-clicking on one of them and selecting close all, play again, and here we can see two results. So semicolons are important in Oracle SQL Developer. Even if you can get away with them occasionally, my advice is don't. Now you might just want to annotate what you've done. For instance, you might want to say semicolons are needed at the end of queries. But the problem is the computer now thinks that is genuine SQL that you might try to execute and it goes, no, no idea what you're doing. But you want this to be a comment. Well, there were two ways to have comments in Oracle SQL. The first way is to put two hyphens at the beginning. You can now see it is grayed out. So if I run that. Nothing happens. No windows return, no nothing. So it is literally just a comment now, it is completely ignored. The alternative way is to put a slash and then an asterisk or star and then your text. You'll notice that the entirety of the rest of this is now greyed out. And that's the difference between the two. The hyphen hyphen just affects one line. The slash asterisk affects multiple lines. And you end by having it the other way around. So star asterisk and then you can have new select statements. So that's two ways of creating comments. Now you notice that we've got some blank space. In some versions of Oracle SQL programs, you can't have multi-line comments with white space, as this is called. You've got to close it up. However, in In other versions like Oracle SQL Developer, you can have it. And I quite like it for the readability, but it's important to know whether it is actually going to work. So if you want a more universal version, then don't have these white spaces. But for me, for legibility, I quite like them. And finally, there is a keyboard shortcut if you want to have the line that you're currently on be a comment. And that keyboard shortcut is control and slash. So just like the slashes we were using earlier, that toggles comments on and off. So you can see it's very easy to have a huge area commented out or not commented out. So that would be the equivalent of having this multi-line comment. Now I call them multi-line comments. They don't need to be multi-line comments. You could have them just on one line if you wanted to. So semicolons, very important even if you're allowed occasionally to forget them. And if you want comments then either you do that over an entire range, so multi-line comment, or you put hash hash at the beginning and the keyboard shortcut for that is control and slash. Again, that will be in the keyboard shortcuts document, which is in the resources earlier in this course. Now in the video before last, we limited this query by number of columns. So the original table had quite a few columns and we've said we only want to bring back these through these three. However, we have not limited it in terms of number of rows. This has got around 120,000 rows. Suppose we wanted to reduce that. Well, we need some sort of criteria. And let's say we wanted all of those columns which are number one in that particular table. So what we can do is use the third of the select clauses. So we've previously had select, we've had from, the next one is where. So this goes before the semicolon, so the semicolon ends the entire select statement, all of these clauses. So if I put where and we have the column number is equal to one. So you may be used perhaps to use languages like C sharp where if you are having equality. In other words, is something equal to something else, you have to put two equal signs, not in Oracle SQL, it is just one equal sign. So let's play that and see if it works. And now you can see that all of the column numbers retrieved are number one, and we have reduced the number of rows down from 120,000 to 10,000. Now you may also notice something else. I have used the original column name. and not the renamed column name. So let's put in the rename column name and see what happens. And you can see it says invalid identifier. What does that mean? It means I haven't a clue what you're talking about, mate. So what happens in logical terms is the computer gets your table and restricts it and only after that and some other clauses, does it say, well, how many columns do you want to bring back? So at this point, The computer in logical terms hasn't identified that the current table name or column name called colNumber has now been renamed. It still expects the original names. So the WHERE clause you have to have your original column names. You can't have the renamed. So now if I play that you can see that we get all of the column numbers back where the column number is equal to one. Now just a quick word about this query results table. You can see that there are these little arrows. Well they allow you to sort and if I double click you can see that it sorts not just the individual row but the entirety on the table based on this column. So here you can see we've got type one one two one one one. If I double click we get 2, oh, do it again, double click, I get 1, 2, 2, 2, 2, 2, because it goes from the top of the table all the way down to the bottom of the table, and at the bottom table we have 1, 1, 2, 1, 1, 1. So if I re-sort this table, we have 1, 1, 2, 1, 1, 1. So simply because we are sorting by name doesn't mean that that's the only thing that gets sorted. All of the rows get sorted with it. And you can see if double click, we go from ascending order, A to Z, descending order, Z to A, and then the original order, in other words, no further sort. There's also, just as in Excel, a filter next to it as well. So if I click on that, I can filter for a specific value in the column. So for instance, I might want TS and hash or pound sign. So press enter. And now you can see that we have 29 rows only which have the name of TS hash. Now I should point out firstly if you want to remove that then you can click inside there and double click on remove and that gets me back to my original 10,000 query where the column number is equal to one. And secondly this is only temporary so if I rerun this query We won't get 29 results, we'll get back to our original 10,000. So it was 120 in the table col$. We then said I only want it where the col number is equal to 1, that got it back to 10,000. And previously we had put on a restriction so that it had to be ts hash, which brought it back down to 29. But if I rerun the query, that last filter gets completely ignored, as does any sorting that you have done. So if I rerun the sorting goes. We'll have a look in a couple of videos time, or maybe three videos time, about how to permanently get these names in a particular order. Now there's a lot more I can say about the WHERE, and we will be saying a lot more later on. But this is just a brief introduction to the SELECT statement. So we have done now the SELECT clause, the FROM clause, the WHERE clause. The next one is the GROUP BY clause. In the previous video, we restricted the number of rows that were returned. My next question is something a bit more of an analytical question. OK, how many rows contain TS hash? I mean, we previously saw that the answer is 29 by putting on a filter. But then how many are obj hash and user hash? What we need to do is group together all of these different names. And we can do that choosing the group by clause. However, this is perhaps a bit more tricky than using the where clause. So I'm going to create a new query at the bottom and it's going to be select name from called dollar where The column number is equal to one, a semicolon. So we just have one field returned and that field is called name. But notice that I'm using the column number. So I don't need in the where clause all of the columns or any of the columns that I use in the select clause. Next. And incidentally I could have said is greater than 1, or is less than 1, not that there are any, but is less than 4 let's say, or is not equal to 1. But let's stick to our is equal to one. It gives us the 10,000 results that we've got. But it's still one row per result. I want to group them up. And I can do that choosing group by. So group by name. So group by the name of the column, which in this case is called name. So if we were doing it by something else, if we were doing it by col hash, then it would be select col hash. The pound sign from col$ or col hash is equal to one. Well, I don't think that would work in this particular case because there would only be one row, but I could group by col hash. So if I look at this, here we have all of the column names and here we have all of the column numbers. So you notice that I need the SELECT clause, I need the FROM clause, I don't need the WHERE clause if I'm doing a GROUP BY. It is not actually necessary. But you notice I'm grouping by the same name. And this is important, again, as in the WHERE. If I rename this as column number, this query will work because I'm referencing the original column name. If, however, I try to reference the new column name, then this will not work. Again, in terms of logical terms, the way the computer processes information, it starts at the from, goes down to the where, goes down to the group by, we haven't got there yet, but it goes to the having, and then only then does it go to the select clause. So at the moment the computer believes that this column, this table called col$, will have a column called col$. pound or coal hash. So that works. So going back to our original example, these are all of the names and you can see that there are 1361. But how many of each type is there? Well, I'm going to put in an aggregation. An aggregation is something like sum, count, average, that sort of thing. And indeed the word count can be used. Now I'm going to talk about aggregations later on. But here is one way of getting this count aggregation. It's count open bracket star or asterisk close bracket. So now if I run this, let's see what happens. We have all of the names, 1361, and the number of times each of them come. So the table dummy comes once, so the column dummy comes once, but the column name comes 221 times. Now notice incidentally, the column is called count open bracket star clause bracket. If I want to put in an alias, if I want to give it a new name, then I can do so using the as, so number of times. So what happens if we try this without the group by name? So I'll just commit that out by pressing Ctrl and slash. So now it doesn't have the group by name. It's the same as if I deleted it. What would happen? It would give me an error saying that countHash is not a single group group function. OK, that's not really very educational. But what it really means is you need a group by. It's not going to give me an answer per line if you're not using a group by. But with the group by, it works. Now you'll also notice that it works with the semicolon being on a different line. And in fact it would also work if any of these were on different lines. You can have it like this if you'd like. The only thing that wouldn't work is if you were to split up a word like the word select. Like that. That would not work. Now, my style is to have select and where possible everything on one line, from on one line, where on one line, and group by on one line, and the semicolon at the end. We will be getting into more complicated queries, and you'll see that that's not actually the entirety of my style, but I generally start from the basis of one clause on one line. Now suppose I wanted to put in another column. So I wanted to put in, which column did we have previously? Type hash or type pound. I can do that, but this would not work as is because I'm not grouping by everything which is not an aggregation. So if I have an aggregation like count, I need everything else to be in the group by. So this would work. So Group By, it summarises data. By itself, that's all it does. It just reduces everything and gets rid of duplicates. But you're then allowed to add aggregation such as count and others that we'll have a look at later. So that's the Group By. Now the fifth clause, the having clause, is the least used. It's the equivalent of a where clause after the Group By. has been done. So let's have a look at our query again and I'll put the simplified version here. Here we have the field, the column called name and how many times it has been used and you can see it's been used a maximum of 1305 times but it's also been used a minimum of one time. So suppose I wanted to remove all of those times where the number of times used is under 6. So it starts at 6 or above. Now I can't put it here in the WHERE clause because the WHERE clause just restricts the number of rows. It doesn't group them. So if I say SELECT name from col$ WHERE the column name is equal to 1. That gets me my 10,000 rows. I can't then say AND where the count is under 6, oh it's greater than 5 in this case, we want from 6 and above, because that to the computer makes no sense whatsoever. Group function is not allowed here. So what we need is the group done first. So now we have 1361 rows and now we can put in our having. So the having now works just like a where on the table that we've got to by this stage. So having the count being greater than five. So now if I run that and you can see that we are now down to 271 rows and the minimum Number of times is 6 and the maximum is still 1305. Now you might be saying, okay, this doesn't quite work to me because I thought you said the SELECT clause happens after the FROM, WHERE, GROUP BY and HAVING. But we're using a column in the HAVING. And that is true. We are using a column. Or actually we're using a calculation. We're using an aggregate calculation. Now the thing is, this calculation doesn't need to be in this table. This would be perfectly legitimate. I'm using the having clause based on count star without count star being in the select clause. That's perfectly legitimate. So that's equivalent of having that. It's just a calculation which I then use in the select clause. What wouldn't be legitimate is if I were to use the rename of count star. So if I put it in as having number of times is greater than five, that would not be legitimate because we have not reached in logical terms, the select clause. We've gone from where group by having next is select. So I can use a function. The function can appear in the select clause. It just doesn't need to. Now, just as a point of reference, we have this error. ORA00904. What we can do, if you want more information about this, is you can Google. So if I Google ORA00904, you'll see this nice website, Tech on the Net, which explains the error message, invalid identifier, and explains in fairly plain English what is happening. and how to resolve it. So I quite like this website, Tech on the Neck. It gives a huge number of error messages. and how to resolve them. Now if you prefer a more official source then you can type Google error message help and you'll see that the first link we have here is for docs.oracle.com and again we have the number of message types so if I find this one 904 You can see that it says it is for invalid identifier. It tells you what the problem is and it tells you an action. Now for me, the techonthenet.com is better because it actually gives examples as to how to create this error and how to resolve this error. So I prefer Tech on the Net to the official website, but you may prefer the... official one. So having, it's like having a second where but only used after a group by. So it is the least used of the six but if you're having a group by and then want to restrict the number of rows further, having is your clause. The final clause is the order by clause. Now we have previously seen that we can temporarily change the order in SQL Developer. But that's not available for all versions, all programs that can access Oracle SQL. Sometimes you might want to say, this is the order I want it in. The order by happens right at the end. So we've done the from, we've got the data from the table, we've reduced the number of rows, we've grouped the table by group by, we've reduced it further using the having clause. And then we reduce the number of columns and maybe renamed columns, maybe added functions. So what fields, what columns we want to retrieve. Order by then works on this list. And so it's the only clause where you can use the aliases, the renamed of the fields. So we can order this by number of times. So now let's execute that and see what happens. So you can see that it is ordered it in terms of number of times starting at the bottom and having the biggest at the top. Now, I should point out that it would also work for counts using order by count star. Usually, however, if you're doing things other than functions. If you have renamed an original table column, you have to use the new name. Now, what if we wanted it the other way round? So we wanted the big numbers to be first. Well, we can add after it the word DESC. D-E-S-C, short for descending. We can't put the word descending, however, it has to be the short-hand version. So now if we execute this query you can see that the number 1305 comes at the beginning and the number 6 right at the end. However notice that we have got two rows which have been repeated 91 times so they are 91 columns in all of the tables. What happens then? Which one comes first? Is it Object ID or is it table name? And the answer is it could be either. It could be entirely what the computer wants. So this is when you can say, well, actually, I want the name to be ascending. Now, ASC for ascending is completely optional. You could just have name. But when you've got descending and descending, I think it adds a lot of clarity. So if I play this, you can see that object ID. is ordered before table name. However, if I said name descending and run that, then you can see it's the other way around. So it does it by number of times first, so we still have the big numbers at the top, but then when it gets to a tie, it now orders by name descending. Now, what happens if I was to get rid of both of these descendings? Well we would have both of them going in ascending order. What if I put name descending? Is it going to do number of times ascending? Or is it going to do the number of times descending? In other words, is the descending going to apply to both these fields? And you can see The answer is no, it is still number of times ascending and name descending. So you can see because of this potential ambiguity, that is why I would put ASC for ascending if you are using descending as well. Otherwise, I wouldn't bother. So we have now gone through the basics of the SELECT statement. There's a lot of detail that I've missed. For instance, how do you get two queries and put them together, for instance. But we'll be going through all of these again during the course. There is one thing, however, I want to finish off this brief introduction with. How do you remember the order of these clauses? And this is quite important because this shows whether you are actually used to SQL or whether you are a newbie. Whether this is your first time and you're in a job interview and you're sweating and you're going, um... I think it's select then from then order by and then group by getting the order wrong. And it is quite common to get the order of where and group by in the wrong order. It's also quite common for new users of SQL to completely forget that you need the group by when you're using get aggregated function and some non aggregated columns. So how can you remember the order of this. And my best way is by using what you've got right in front of you, your keyboard. So if you're at a job interview, you will undoubtedly have a computer in front of you. Now let's just have a look at the first letter of all of these. Select, from, where, group by, having and order by. Have you noticed something about five out of these six clauses? They go from left to right. Select from group by having order. In fact, three of them are consecutive from group by having. So this way you should not be able to forget the order of five of them. Select from group by having an order by. The only one that you have to remember is the where. And you just need to remember that that comes after the two compulsory clauses. So it comes third. Select from where. So if you can remember that, then in any job interview, or indeed when you're actually creating SQL statements for real, you will be in no doubt as to the order of the clauses. So how did you do with this practice activity? It's good to do this practice because it refreshes in your mind what you've just learned. Oh yes, I learned something about this. this and allows you to build on just seeing it but done on the screen. You actually do it and you enable your mind to go ah these are the connections that I missed. So first of all we want to create a select statement that gets all of the columns from the table called view dollar. Now to get anywhere we need a window first of all so click on SQL worksheet and click ok. So I'm going to create a select statement That's going to get all of the columns from a table. So all of the columns is a star, an asterisk, and then the word from and view dollar. And how should I end my SQL statements? With a semicolon. Now I've separated it onto two lines. That was not necessary. You could have it on one line, you could have it on four lines if you like. But this is generally the style that I use. As long as you don't try and separate words. That's fine. So now let's run this SELECT statement and here you can see the results. Lots of columns. So number two is instead of getting all of the columns back, can you just get obg and then a hash or pound sign and the word cols. So instead of using the asterisk, the star, we put in obg hash or pound and then a comma and then col dollar. So if I run this, we will get two columns back. However, you notice I made a mistake if I typed in col$. It says invalid identify. In other words, you've made a mistake with the name. So it's cols with an s at the end. And the comma is important. If you don't have that comma, then you'll just get one column back. It will be this one, the obg column, but it will have at the top the word cols. That's because I'm in this case renaming the OBJ column with the word Coles. But I actually want both columns back, so I need that comma in between. Next, filter it so that only of those rows where Coles equals 12 are shown. So after the select, after the from, that's where we can put in a where and where Coles equals 12. Now you can put in spaces if you like. Again, generally, that's what I like to do, but it's a sort of a house style. I think it aids readability. So if I run this there we can see the answer. Now we don't know how many rows it is. It's at least 50 rows. If I click in there and click ctrl and a you can see that we have 252 rows. Next please sort it so that it is in a descending order of rbj hash or pound sign. So several ways of doing this. Firstly the sort it should get you the two words Order By. And so I can order by rbj hash so you notice right at the moment it's not completely ordered in that way. Here we can see 17,000 in the middle of some four thousands. So if I run that you can see that now it is really in that order. Now another way of doing it could be order by one and that would order by the very first column. But it can be considered bad form to do that unless you've got a particular reason. So order by name would be much better. And then the final question, can you remember the names of the other two clauses in the SELECT statement and in what order you would use them? So we've got SELECT, FROM, WHERE and the other two clauses are GROUP BY and HAVING and they go in between WHERE and order by. So I hope you enjoyed this first practice activity. I hope you're getting really good at it. Now if you're not, if there's something that didn't quite work, then the great thing about this video course series is you can just go back to an earlier video and replay it, put on subtitles maybe, and just see what it is that you can... oh what's it what did I miss? And then hopefully you'll be able to come back to this practice activity and get a hundred percent. So we'll be building on these skills as we go throughout the course. So wherever you go next, whether it's back to a previous video or onto the next part of this course, I'll see you there. Well, thank you for joining me on this course about Oracle Database SQL. And the question you may have now is, well, where do I go from here? So, so far you have learned the six principal clauses select from where? Group by having an order by. But what else is it useful for you to learn? I mean, you could find all sorts of information, but what is it actually useful for you to have? Well, let's ask the experts. Oracle. Oracle have got something called Oracle University, which has a series of training courses and also certifications. So you don't have to do the training course to go for the certification. Now, the certification in this question is... IZO or IZO071 so IZ0071 and you can see it's not an inexpensive certification $245 but ignoring that it's a two hour exam with 78 questions and at the end it would give you a Oracle database SQL certified associate but what's much more interesting is what do you actually need for this certification? So this is stuff that Oracle actually want you to have. And if you have a look at review exam topics, you can see that it's divided into about 18 different topics with lots of subtopics added in. So we have got things like relational database concepts, restricting and sorting data. So limiting rows, we've had a look at the WHERE clause. Self joins, outer joins, Cartesian joins, insert, update and delete statements, sub queries, restricting group results and creating groups of data. We had a look at the GROUP BY earlier and we also had to look at the HAVING clause as well. So this is probably a good place to start. The curriculum for the IZ0071 certification. I'm not necessarily suggesting that you go for the certification and certainly not at this stage. It's just that these items could be quite useful for you to have. So how do you get this training? Well there is a recommended training and well let's just say if you click on any of these and I've opened it before because it does take a while, this isn't the swiftest website, you can see that the price for their certification is around $3,400. And if you want to do it in a classroom, it's more like $4,000. So very expensive. So maybe we won't use the Oracle University database courses. They're just a bit too pricey. Maybe you want something in book form. Well, the official book is this one. It's the OCA Oracle Database SQL exam guide. Basically, just go to your favourite bookseller and type in IZ0071. It's a good book. It's fairly comprehensive. Comment how many pages? About a thousand pages? 832 pages. The average customer review of only three stars doesn't seem that good, but I've... Yeah, there's maybe some errors in it, but I found it a nice read. If you want something free, then you can go to docs.oracle.com forward slash en, en for English. Here we have all sorts of online help. So if I click on Oracle Database, database, I click on get started. So we currently have this version. So it depends which version you're talking about. 19c is the latest one as of the time of recording, but maybe you need to have a look at an older version. So get started. Introduction to SQL and then Oracle Database SQL Language Reference. So this again is a nice read. It's around 1000 pages, but you don't have to sit at your computer just clicking on page to page. Unless of course you actually want to do that. Instead there's a download button here and that allows you to download it as a PDF and then you can save it or print it. You know, email it to yourself, put it on an iPad of 2000 pages. I thought it was a bit shorter than that. It's very comprehensive. Possibly not the best way to start. But if you already know a fair bit about a bit, this will give you a way to hone your talents on one particular skill perhaps. So these are some of the ways of getting the training that Oracle recommends. And it goes from free, downloading a manual, 2000 pages, to a 832 page book, to some very expensive training. Well congratulations, you made it through this hour-long course. So let's just have a recap of what's happened. You may have installed for free on your computer Oracle Express Edition and Oracle SQL Developer, so you can continue to practice your skills. We looked at the SELECT statement and the six principal clauses. SELECT from where, GROUP BY having and ORDER BY. And you now know how you can remember them just by looking down on your keyboard. So, I hope you found this a lot of fun and I hope to see you on future courses. So, whatever you do, thank you for joining me and keep learning.

And in this one hour video I'll be introducing you to Oracle SQL. We'll start by installing for free on your computer Oracle Express Edition that's the backend the actual database and Oracle SQL Developer that's the part that you use to write code. If you don't want to install them at this time that's fine as well. I'll then be teaching you how to write SQL select statements.

We'll be using the six principal clauses. Select from where, group by having an order by. We'll also see how to remember their order quite easily and that could be very important for job interviews.

Once you have done this then you will have learned an important skill and be way ahead of most people. And that knowledge alone plus a bit of practice could be the difference between you being able to get your next dream job or not. I'll also be giving you a practice activity so you can be sure that you have learned this important skill. We'll do and see what else there is, what else Oracle wants you to know, how you can progress further. So thank you for joining me in this video course and without any further ado, let's start learning.

Now the first thing we have to do is install the database engine. So this is the bit that runs in the background that does all of the hard work. But which version do we need to install?

Well let's scroll down and you can see that this exam is validated against 11g release 2 and 12c release 1. Okay so what is 11g and 12c and are they the latest? Well if you go to the Wikipedia page you can see that 10 was first released in 2003, 10g. 11 was released in September 2007 and updated September 2009. And then 12C was released in July 2013. And the latest at the time of recording is 18C, which was released in February 2018. Though at the time of recording they are working on 19C.

So you can see that the years are not represented by the numbers, except for the latest ones which are. So what is the 10, 11, 12? Well that's just the standard release number. So it started from 2 and it went up to 10, 11, 12 and finally Oracle decided it would make year versions, so 18 and 19. OK, so what is G as opposed to C? Well, 11G refers to something called grid computing, which supports clusters of servers which are treated as one unit.

12C refers to the cloud. It is allowed you to put databases into public or private clouds. So don't be too confused by the lettering.

There wasn't a 10F, 10E, 10D or 11A, 11B. It jumped from 10G to 11G to 12C to 18C. So, so what?

What on earth does all of this mean for you? Well, the only thing really that it means, well two things maybe. First of all, this exam is validated against 11g2 and 12c1.

So you need to install at least one of those versions. So you can install later versions like I'm going to recommend you do with 18c. Now there are changes between each database type. So there's a jump from 11g to 12c they've added a few new commands but you'll see on the right hand side the extra commands that they have added and to be honest at this sort of course it doesn't make too much of a difference. There may be one or two commands maybe which are missing in 12c that we might use or which were available in 12c which are missing in 11g.

But why am I talking about older versions? for one really important reason. If you have a 32-bit computer, you cannot use 12C or 18C. You have to use 11G.

So, how can you check if you've got a 32-bit machine? Well, if you open up Windows Explorer and you right-click on My Computer or This PC and go to Properties, you'll see whether you have got a 64-bit operating system or a 32-bit operating system. So if you've got a 32-bit operating system, then 11g is the latest you can install. If not, I recommend you install 18c. But which version?

As you can imagine there are lots of different types of version. There's the Oracle Database Enterprise Edition. This is the fully featured and very expensive version. I don't recommend you get that for some strange reason unless you do have a huge number of dollars available.

The Standard Edition, that requires less money. The Personal Edition, that costs about $460. So I recommend you get the Express Edition, which is a free to use version.

Now it does have limitations. You can use only up to two CPUs, which isn't going to be much of a problem with what we're doing, but if you're going to use much more intensive computing it would be. And the other issues here up to 12 gigabytes of user data, two gigabytes of RAM.

We're not going to go anywhere near it on this course. So how can you download Oracle Database 11g XE? That's the Express Edition. So XE to distinguish it from EE being the Enterprise Edition or version 18c. Well, it's actually quite easy to download the Oracle Database Express Edition.

18c, it's a lot harder to download 11g, but I'll show you how to do it. If you go to oracle.com forward slash downloads and scroll down, you'll see here we have databases and keep scrolling down, we have the database 18c express edition. So if I open that in a new tab, This is where you download it if you've got a 64-bit computer. And you can see here Oracle Database 18c Express Edition for Windows X 64, that's a 64-bit. First of all you have to accept the license agreement, read it first, then click download, and then you have to sign in.

So if you have got an Oracle account then use your username and password, if not it's free, just click. create account type in your details you'll get a email asking you to validate your details that you have given and then you'll be able to download it so i'll just go back and i will type in my username and password and click sign in and i've done that and you can see it's downloading here it's 2gb it will take a little while. Now let's suppose you don't have 64-bit, you've got a 32-bit computer.

Well, bizarrely, you have to click on the Database 11g Enterprise Stroke Standard Editions, even though we want the Express Edition. And what we can do, first of all, you can accept the license agreement and obviously read it first, but then scroll down. This is a 19c I was telling you about, the current version that the are developing, scroll all the way down to the 11G and then keep scrolling.

Because here you can see near the bottom Oracle Database Express Edition 11G for Windows. Now, this is the only version which is not for Windows 64. So clicking on this express edition will get you back to downloading this for 64 bit. There is no option here for downloading for 32 bit. It's this bottom one you want.

Oracle Database 11g for Windows. So just click on that, click on Accept License Agreement. And here we have Windows X 32. Click Download and you can see the instructions.

Unzip the download and then run disk1 slash setup. So there you can see it's a much smaller install but I recommend if you can to install the larger version. It's the latest version, it's got slightly more functionality. So I'm going to pause the video here and on the next video we're going to install Oracle Database. Oracle Database Express Edition has now been downloaded.

So I'm going to open it up in my Downloads folder and I'm going to extract it into its own folder. So here's the folder. I go down to the setup, double click on it And here you can see it is now installing.

So first of all it's installing some prerequisites, some things that it needs beforehand, before it can install. Now on my computer it then mysteriously, the window mysteriously vanished so I'm going to run it again. So click setup.exe Now you can see configuring Windows installer and this is what I'm expecting to see. They will install Oracle Database 18c express edition, have a read of everything and accept the terms of the license agreement if you so wish.

You can see the default place that it's going to install it. So I'll click Next. Now this is important. You're going to have to enter in a password which will be used for SYS system and PDB admin accounts.

So the important thing about this is this is going to be the major password that can get access to everything within the database. So I'm going to type in a password twice and make it slightly complex. But the important thing is I can remember it. So click install and it's going to install and it says this may take several minutes.

It will indeed because it was a very hefty download. So I'm just going to speed up this part of the video so you can see it install. So it took about 20 minutes to install but there were no problems whatsoever. So I'll click finish and that is the back-end installed on my 64-bit machine. So that's the back-engine installed what about the front-engine?

What about being able to actually use it? Well for this we're going to use a program called Oracle SQL Developer. Now there are alternatives to this.

Some are really much older, but this one allows us to use a lot of functionality. Plus, if you did want to go further into programming language, the PL SQL, then SQL Developer also allows you to do this. So, I'm going to see that there are two versions. There is a Windows 64-bit with... JDK, that's the Java Development Kit installed.

Or there is a version for 32-bit or 64-bit which requires the Java Development Kit to be installed separately. So I'm going to download this 64-bit. First of all you need to agree the license agreement. And I'm also going to download the 32-bit and this takes two downloads.

First of all, if you've got a 32-bit machine, you need to download this 32-bit and you need to download the JDK8. Now if you do this, the JDK8 needs to be installed first. So let's just scroll down.

and we can see Windows and you can choose 86 or 64. Now you might be asking what is 86? It's a really really old-fashioned name for 32-bit. So you can click whichever download you're going to install it on.

So first of all you need to accept the license agreement which is just here and download which version you're going to use. Now it's a lot easier, it's just one install, if you are just using the Windows 64-bit with JDK 8 included. But it's not really that much more difficult with JDK 8 and Windows 32-bit.

So now we've got these downloading in the next video, we'll install them. I should also say that the version that I'm installing is SQL Developer 19.1 of the April 2019. And the version you'll be installing will be a slightly later version. However, there really is minimal change between the one that I'm installing and the one that you're installing.

So whichever the latest version is, as long as it doesn't say it's a beta version, a test version, then you are okay to install it, to download and install it. So Oracle SQL Developer has been downloaded. So we're going to do exactly the same thing. We're going to extract the files.

It's a zip file. It's taken about 30 seconds to extract. I'm going to the SQL Developer folder, scroll down and click on the SQL Developer application.

So here you can see it doing the first part, the installing the Java development kit. I don't want it to import preferences from previous version because I don't have one. And then it just installs Oracle SQL Developer. So allow automated usage reporting to Oracle. I'm going to skip the automatic check for updates.

This is an XE version. There won't be any new patches. And so here we have Oracle SQL Developer. So that's how you install it on a 64-bit machine. When you open up Oracle SQL Developer, it looks very busy.

There's this big welcome page. But do I just click an X and it disappears? So you can see that there isn't actually much going on.

There's a report which we're not going to be using and there's connections. Now what do we need a connection for? Well Oracle SQL Developer is the front engine and we've previously installed Oracle Database Express Edition. So we need those two to talk to each other and the easiest way to do that is just click on the plus next to the connections.

So we have this dialog box a new database connection we only have to set this up the once. So we can call this anything, so I'm going to call this SQL database. Now you can give it a colour, and I would advise you to give it a colour, simply because when you connect it will display that colour in a couple of places. Now you can see which database we're going to be connecting to, it's the local one.

Everything else is basically set up for you. The only thing that's not set up is your username and password. So I'm going to come in as sys. You remember we, as part of the installation process, created a password for the sys. So I'm going to use that username and the password that I previously set up and I'm going to click save the password.

Because this is just a demo database it's not going to contain any real data. And then the final thing we need to do is change the role. Now if you look down here at the status, the status is blank.

If I click on test you'll see that the role should either be SYSDBA or SYSOPER. Well you can see from the online database, the online library, that SYSDBA is for fully empowered database administrators. It allows you to see everything. Whereas Sys OPR allows you to do basic operational tasks but without the ability to look at your data.

Well as we're going to be learning SQL we certainly want to see our data so we're going to have the role of SYSDBA. So just change role to SYSDBA, click test again and you should now have status success. So it's very complicated or it looks very complicated but the good news is you only have to do this.

this once, at least for our course. So click connect and you'll see that we now have a new connection, this SQL database. And you'll also notice it's in red and we have a new window which is also in red. Now if you don't like this particular color, no problem, just right and click on it, go to properties, and you can change the colour.

So I'm going to change it to a more standard white and connect. Now, or maybe black in fact would be better. Now the reason for having all of these colours is if you connect to multiple databases then you'll be able to see very swiftly which database you are in.

So let's close all of these tabs right and click close all. So now we have created a connection. to our database.

Now that for me is the most complicated bit done. We have installed the back end, we've installed the front end which is SQL Developer and we have now created a connection. The rest of this course actually allows us to focus on what we actually want to do.

The select statements, all of the coding and everything else that's required for this certification and to learn SQL generally. Now in this video, I just want to mention one problem that you may get from time to time when trying to connect to SQL Developer. So if I click on my recent connection here, you'll see that it starts to run, but then at the bottom, but then finally says the network adapter could not establish the connection. Vendor code 17002. So this is just in case you get this. You go to start and you find the command.

window. Now it may be in different places, the command prompt. What I do is I go down to the search engine at the bottom, the start menu and I type in CMD. That gets me the command prompt.

So from here we can see if there's something called the listener. So this is the database listening for new connections, whether it is just taking a rest. And to find this out I type in lsnrctl status and if it is taking a rest then you will see unknown error and no such file or directory.

So to start the listener exactly the same lsnrctl start and it hasn't worked for me. I've got a protocol adapter error so if I'm getting this then I'm going to do a reboot. Well, I've tried rebooting the computer and that didn't work. So we'll go into the next thing we can do. Go to start and go to administrative tools.

So again, I'm just going to type in administrative tools into my search and we're going into services, double click onto services and then scroll down to Oracle. Now the exact name might be slightly different, but it's the Oracle TNS Listener. So you can see it's not running and I'm going to click start.

So there are the services now started. So now if I open up again SQL Developer and now click on my recent connection, it may initially refuse. but then try it again and you should get it working.

So here are two solutions. If you get the error, network adapter could not establish the connection. Now I've just closed SQL Developer just to show you that if you want to reopen it, go to the folder where you have extracted it from and double click on SQL Developer application. Alternatively, you can right-click on it and you can pin it to the Start menu or to the Taskbar. And you'll see at the moment I've done both.

So if I just re-pin it, here you can see pinned to Taskbar. And then it's included in my Taskbar. with no underline if it's closed. So that gives me easy access to it.

So I'm just going to open up SQL Developer, either by double clicking on the application or clicking where you have pinned it. And so you get back to where you are, where you were. Now, you remember in the previous video, we created a new database connection. Let's expand this database connection and see what we've got. And you can see we've got an awful lot of different types of objects, tables, views, indexes, packages and so forth.

Now in this section of the course I want to talk to you about the SELECT statement. The SELECT statement, well other programming languages use the word print. And that's essentially what SELECT does. It prints information somewhere.

Now I don't mean it prints to pieces of paper. It can print to a new pane at the bottom of the window. Or it could store what you are putting into the select statement internally. Now the select statement is built up of six different clauses. And those clauses are select, from, where, group by, having and order by.

And we'll be looking at each one in turn. So when we say select statement, that includes, well, it's got to include as a minimum, the SELECT clause and the FROM clause, and it can include any of the other four. So what I'm going to do is have a look at one particular table.

Now let's expand this tables over here, and we can see that there are an awful lot of tables. And in fact, there are probably some also hidden as well. Now I'm going to just temporarily have a look at one particular table and that is called tabs or tab with a dollar and if you click on it you can see some information about it so you can see that it's got various column names called ob object number file number tab again table number and so forth now we're not actually going to use the information that's in here I just wanted to just do a very quick select statement based on tab$.

And that statement is select star from tab$ semicolon. So what this does is it's going to print all of the fields, that's what that star or asterisk is, from the table. Tab dollar and the semicolon ends this sentence, ends this select statement. So I'm going to highlight this and click on the play button.

And you'll see that there is a keyboard shortcut, which I highly recommend you remember, which is control and enter. And in fact, there's going to be a few keyboard shortcuts. So I'm going to be making a note for them, at least the important ones. And earlier on.

There was a lecture called Resources and it had attached various things including a list of all of the keyboard shortcuts, at least all of the important ones. So I'm going to play or press Ctrl and Enter and you can see down here at the bottom the first 50 rows, the first 50 records. And you can also see all of the data in these individual columns. So just like a spreadsheet we have rows and we have columns.

Now, are there only 50 tables? No, there aren't. If you want to force the computer to find some more, then scroll down to the bottom and it will retrieve and retrieve and retrieve. And you might be thinking this might take some time with bigger tables, and indeed it would do.

So another way of doing that is just click somewhere inside the table and press Ctrl and A. That forces the computer to get all of the information back. So now you can see all rows fetched, 2,174 rows. Now let's say I didn't want all of these columns.

Suppose I just wanted the first five or six or something like that. Well let's go into this tab dollar and expand. Click on the plus next to it.

So we can see we have fields called OBJ hash or pound sign, data OBJ hash or pound sign and so forth. What I'm going to do is replace this star, this asterisk, by OBJ and then the hash or pound sign. So now let's play and here we can see the result.

It has the same number of rows, which I can confirm quickly by clicking somewhere in the results and pressing Ctrl and A. So 2174. It's just that we have restricted the number of columns. Suppose I want a second column.

That's fine, I go to the Select clause, I type comma, and I put in the next one. Data, or bj, pound sign or hash, play, and there we are. We now have two columns. OK, let's add a third column. Let's add ts hash, but I'm tired of doing all of this typing.

So what I'm going to do is I'm going to drag this TS hash over here. Now, there is something very interesting. When I release, we get this dialog box and it says, OK, what is it you want me to do?

And I just want you to insert the object name. So click apply. And so now if I run, you'll see there's a third column.

But let's add a new select query. So, and I'm not even going to type this select query. I'm going to just drag this TS hash or pound sign across to our worksheet. And I'm going to leave it at this, select individual statements. Look how much work this can save you.

Equally, I'm going to delete that and I'm going to drag the table across. Now this is a bit more difficult. Let's see what happens if I get it wrong.

I click on it, I start dragging, I accidentally go up. Well, it's now highlighted the table that's above and has given me the information about the table above. That's not what I want.

So I have to drag directly right until I leave this connections pane. And now I can go up and down and so forth when I'm in the worksheet. So now if I release and select individual statements, this gives me every single field from this particular table.

And now let's say I wanted the first five columns, five fields, that's fine. I just highlight the ones I don't want, press delete. And as long as it doesn't end with a comma, it'll be fine. You can see there is a squiggly underline underneath the from. That's the computer telling you, hang on, I'm not really sure that this is right.

And if I click play, you'll get an error message saying missing expression. In other words, it's expecting something after the comma. So let's get rid of that comma, run it again.

And there we go. We have 2700. 2174 rows and five columns brought back. Now in this video, we're going to create a different select statement based on a different table. So tab dollar gives you data about things. It's called metadata.

And in Oracle SQL, all of these metadata system tables are called the data dictionary. So we're going to use a different table in the data dictionary, and this is called col dollar. So just as tab Dollar gives you all the information about tables.

Cold dollar gives you all of the metadata about the columns within the tables. So I'm just going to delete what we currently got and type select star from cold dollar. And you can see I moved it up too quickly.

It's one of those things that you just got to get right. You've got to move it straight to the right. And for that reason, quite frankly, that's why I Often have this pane open as little as possible so there's less distance. So if I have a look at this, we can see that there are all of these columns. Now I don't necessarily want all of these columns.

I want the column number. I want the name and I want the type. So run that. We have the same sort of information.

It's just that we now have three columns. So we have 120,000 rows retrieved in about one and two thirds seconds. Now for me, col, number, name, type, name, they're not the best names that I would want.

So what I'm going to do is rename this first column. It's currently got col and then a hash or pound sign. So let's call it column number.

And what I'm going to do is I'm just going to type the one word column number immediately after it. So space, column number and then the comma. So let's run that and see whether that's worked. And you can see, yes, that has worked. We now have a new first column called column number.

Now, we don't just have to have new field names made up entirely of letters. We could add numbers to it. That would work. And we can add an underscore. So that separates out the words.

And you'll also notice that column number is displayed up here entirely in upper letters. So given that that's what happens, we probably would be best to use open letters here as well. I'm going to change select to upper, from to upper and name to upper. So they're all consistent, but it doesn't completely matter so much whether you're using lowercase or uppercase.

It does in some contexts, but just not at the moment. Now, what if I wanted to replace this underscore with a space? Would that work? Let's have a look. Well, the squiggly underline underneath the word number.

tells its own story. The error message is not actually helpful. From keyword not found where expected. What this really is trying to tell you is that this new name, it doesn't know what the new name is. Is it column space number?

Is it column space number, comma, name? Is it columns, number, name, type? That could be the new name. If I'd wanted column number to be the new name, then I need to put quotation marks around it.

So the computer knows where it starts and where it ends. So now you can see that column space number is the new field name. Equally, if I put the end quote right at the end, how many columns would I get back? I'll just get the one back because this new column is called column number, comma, name, comma, type number. So But that is why we need to have these quotation marks around this new field name.

And that is why this is called a quartered identifier. Ones with quotation marks around it. Now there is another version called non-quartered identifiers. So name is a non-quartered identifier. Type number is a non-quartered identifier.

because they don't have these quote marks around. So what's the difference between a quartered identifier and a non-quartered identifier? Well, both can use letters, both can use numbers, and both of them can use the underscore, the dollar sign, and the pound or the hash sign.

Both of them have to be 1 to 30 characters, so you can't have really long column names, but you probably won't want to. If you wanted to put a comment for a column, we can do that later on. There are also certain words that you can't use.

I mean, you couldn't call the column called Select, for example. So avoid words that Oracle SQL will want to use. And finally, non-quoted identifiers must start with a letter. So anything that doesn't follow these rules, so you have, for instance, a space, or you start with a number.

I don't recommend that at all, but if you did, then these are quartered identifiers and they're fine, it's just that you must have quotation marks around them. Now you can have quotation marks around identifiers that don't need them. So here I've got quotation marks around name and that's fine. It's just not usual. So basically try to use non-quoted identifiers whenever possible, simply because it saves you having to use quotation marks.

But if you do need to, no sweat. Now, this isn't how I would normally do these column renames. I would put the word as in between, A S. So that says, give me this original column, but give me as if it was named something else. So rename it to that. Now, how important do I think using as is?

Well, it's completely optional. It's down to you. But let me type in.

Another SELECT statement. So SELECT and I'm going to put in the first three columns here. So object, column and SEG column from col$. Have you spotted what I've done wrong? So let's have a look.

Let's play this. See if you're right. It returns two columns only, and that's because it is treating this one as saying, give me back the column originally called column number, but rename it segColNumber.

Now, this may be what you actually want. It just so happens there is another column in there called segColumn, but it may be that you do want to rename it. But from a human level, this is ambiguous.

Not from a computer level. The computer level The computer knows what it's going to do, it's going to bring back two columns. But my question is, on a human level is, did you forget the comma, which would bring back three columns, or was this deliberate? And if this was deliberate, I'll put the word as in the middle, just so it tells me, the human at the end, that this is really what you did want to do.

So this is how to rename fields. You have quartered identifiers and non-quartered identifiers. And the letters, the symbols that you are allowed in non-quartered identifiers, or letters, numbers, have three different types of punctuation, and it must start with a letter.

Otherwise, you're going to have to use quotation marks around your new column name. In the previous video, we had put two queries, each properly ending with a semicolon. But how important... is that semicolon really? Let's take it out and find out.

So let's play this first query. Seems to work. Let's play the second query. Again, seems to work. And the reason it works is because Oracle SQL Developer implicitly adds the semicolon to the end where it's missing.

Other applications might not. So they might just reject the statement because it doesn't have that semicolon. But let's see where a problem does lie in Oracle SQL Developer. Let's run both of these queries together. Press play.

And you can see we have an error. SQL command not properly ended. Whereas, if I put the semicolon at the end of each, and press play, you can see that we have two answers, two query results.

It's difficult to see that, I'm going to close all of the windows by right-clicking on one of them and selecting close all, play again, and here we can see two results. So semicolons are important in Oracle SQL Developer. Even if you can get away with them occasionally, my advice is don't.

Now you might just want to annotate what you've done. For instance, you might want to say semicolons are needed at the end of queries. But the problem is the computer now thinks that is genuine SQL that you might try to execute and it goes, no, no idea what you're doing.

But you want this to be a comment. Well, there were two ways to have comments in Oracle SQL. The first way is to put two hyphens at the beginning. You can now see it is grayed out. So if I run that.

Nothing happens. No windows return, no nothing. So it is literally just a comment now, it is completely ignored. The alternative way is to put a slash and then an asterisk or star and then your text.

You'll notice that the entirety of the rest of this is now greyed out. And that's the difference between the two. The hyphen hyphen just affects one line.

The slash asterisk affects multiple lines. And you end by having it the other way around. So star asterisk and then you can have new select statements. So that's two ways of creating comments. Now you notice that we've got some blank space.

In some versions of Oracle SQL programs, you can't have multi-line comments with white space, as this is called. You've got to close it up. However, in In other versions like Oracle SQL Developer, you can have it. And I quite like it for the readability, but it's important to know whether it is actually going to work.

So if you want a more universal version, then don't have these white spaces. But for me, for legibility, I quite like them. And finally, there is a keyboard shortcut if you want to have the line that you're currently on be a comment. And that keyboard shortcut is control and slash. So just like the slashes we were using earlier, that toggles comments on and off.

So you can see it's very easy to have a huge area commented out or not commented out. So that would be the equivalent of having this multi-line comment. Now I call them multi-line comments. They don't need to be multi-line comments.

You could have them just on one line if you wanted to. So semicolons, very important even if you're allowed occasionally to forget them. And if you want comments then either you do that over an entire range, so multi-line comment, or you put hash hash at the beginning and the keyboard shortcut for that is control and slash.

Again, that will be in the keyboard shortcuts document, which is in the resources earlier in this course. Now in the video before last, we limited this query by number of columns. So the original table had quite a few columns and we've said we only want to bring back these through these three.

However, we have not limited it in terms of number of rows. This has got around 120,000 rows. Suppose we wanted to reduce that.

Well, we need some sort of criteria. And let's say we wanted all of those columns which are number one in that particular table. So what we can do is use the third of the select clauses.

So we've previously had select, we've had from, the next one is where. So this goes before the semicolon, so the semicolon ends the entire select statement, all of these clauses. So if I put where and we have the column number is equal to one.

So you may be used perhaps to use languages like C sharp where if you are having equality. In other words, is something equal to something else, you have to put two equal signs, not in Oracle SQL, it is just one equal sign. So let's play that and see if it works.

And now you can see that all of the column numbers retrieved are number one, and we have reduced the number of rows down from 120,000 to 10,000. Now you may also notice something else. I have used the original column name.

and not the renamed column name. So let's put in the rename column name and see what happens. And you can see it says invalid identifier.

What does that mean? It means I haven't a clue what you're talking about, mate. So what happens in logical terms is the computer gets your table and restricts it and only after that and some other clauses, does it say, well, how many columns do you want to bring back?

So at this point, The computer in logical terms hasn't identified that the current table name or column name called colNumber has now been renamed. It still expects the original names. So the WHERE clause you have to have your original column names. You can't have the renamed. So now if I play that you can see that we get all of the column numbers back where the column number is equal to one.

Now just a quick word about this query results table. You can see that there are these little arrows. Well they allow you to sort and if I double click you can see that it sorts not just the individual row but the entirety on the table based on this column. So here you can see we've got type one one two one one one. If I double click we get 2, oh, do it again, double click, I get 1, 2, 2, 2, 2, 2, because it goes from the top of the table all the way down to the bottom of the table, and at the bottom table we have 1, 1, 2, 1, 1, 1. So if I re-sort this table, we have 1, 1, 2, 1, 1, 1. So simply because we are sorting by name doesn't mean that that's the only thing that gets sorted.

All of the rows get sorted with it. And you can see if double click, we go from ascending order, A to Z, descending order, Z to A, and then the original order, in other words, no further sort. There's also, just as in Excel, a filter next to it as well. So if I click on that, I can filter for a specific value in the column. So for instance, I might want TS and hash or pound sign.

So press enter. And now you can see that we have 29 rows only which have the name of TS hash. Now I should point out firstly if you want to remove that then you can click inside there and double click on remove and that gets me back to my original 10,000 query where the column number is equal to one.

And secondly this is only temporary so if I rerun this query We won't get 29 results, we'll get back to our original 10,000. So it was 120 in the table col$. We then said I only want it where the col number is equal to 1, that got it back to 10,000. And previously we had put on a restriction so that it had to be ts hash, which brought it back down to 29. But if I rerun the query, that last filter gets completely ignored, as does any sorting that you have done. So if I rerun the sorting goes.

We'll have a look in a couple of videos time, or maybe three videos time, about how to permanently get these names in a particular order. Now there's a lot more I can say about the WHERE, and we will be saying a lot more later on. But this is just a brief introduction to the SELECT statement.

So we have done now the SELECT clause, the FROM clause, the WHERE clause. The next one is the GROUP BY clause. In the previous video, we restricted the number of rows that were returned. My next question is something a bit more of an analytical question.

OK, how many rows contain TS hash? I mean, we previously saw that the answer is 29 by putting on a filter. But then how many are obj hash and user hash? What we need to do is group together all of these different names.

And we can do that choosing the group by clause. However, this is perhaps a bit more tricky than using the where clause. So I'm going to create a new query at the bottom and it's going to be select name from called dollar where The column number is equal to one, a semicolon.

So we just have one field returned and that field is called name. But notice that I'm using the column number. So I don't need in the where clause all of the columns or any of the columns that I use in the select clause.

Next. And incidentally I could have said is greater than 1, or is less than 1, not that there are any, but is less than 4 let's say, or is not equal to 1. But let's stick to our is equal to one. It gives us the 10,000 results that we've got. But it's still one row per result.

I want to group them up. And I can do that choosing group by. So group by name. So group by the name of the column, which in this case is called name.

So if we were doing it by something else, if we were doing it by col hash, then it would be select col hash. The pound sign from col$ or col hash is equal to one. Well, I don't think that would work in this particular case because there would only be one row, but I could group by col hash.

So if I look at this, here we have all of the column names and here we have all of the column numbers. So you notice that I need the SELECT clause, I need the FROM clause, I don't need the WHERE clause if I'm doing a GROUP BY. It is not actually necessary.

But you notice I'm grouping by the same name. And this is important, again, as in the WHERE. If I rename this as column number, this query will work because I'm referencing the original column name. If, however, I try to reference the new column name, then this will not work. Again, in terms of logical terms, the way the computer processes information, it starts at the from, goes down to the where, goes down to the group by, we haven't got there yet, but it goes to the having, and then only then does it go to the select clause.

So at the moment the computer believes that this column, this table called col$, will have a column called col$. pound or coal hash. So that works. So going back to our original example, these are all of the names and you can see that there are 1361. But how many of each type is there?

Well, I'm going to put in an aggregation. An aggregation is something like sum, count, average, that sort of thing. And indeed the word count can be used.

Now I'm going to talk about aggregations later on. But here is one way of getting this count aggregation. It's count open bracket star or asterisk close bracket. So now if I run this, let's see what happens.

We have all of the names, 1361, and the number of times each of them come. So the table dummy comes once, so the column dummy comes once, but the column name comes 221 times. Now notice incidentally, the column is called count open bracket star clause bracket.

If I want to put in an alias, if I want to give it a new name, then I can do so using the as, so number of times. So what happens if we try this without the group by name? So I'll just commit that out by pressing Ctrl and slash. So now it doesn't have the group by name.

It's the same as if I deleted it. What would happen? It would give me an error saying that countHash is not a single group group function. OK, that's not really very educational. But what it really means is you need a group by.

It's not going to give me an answer per line if you're not using a group by. But with the group by, it works. Now you'll also notice that it works with the semicolon being on a different line.

And in fact it would also work if any of these were on different lines. You can have it like this if you'd like. The only thing that wouldn't work is if you were to split up a word like the word select.

Like that. That would not work. Now, my style is to have select and where possible everything on one line, from on one line, where on one line, and group by on one line, and the semicolon at the end. We will be getting into more complicated queries, and you'll see that that's not actually the entirety of my style, but I generally start from the basis of one clause on one line. Now suppose I wanted to put in another column.

So I wanted to put in, which column did we have previously? Type hash or type pound. I can do that, but this would not work as is because I'm not grouping by everything which is not an aggregation. So if I have an aggregation like count, I need everything else to be in the group by.

So this would work. So Group By, it summarises data. By itself, that's all it does.

It just reduces everything and gets rid of duplicates. But you're then allowed to add aggregation such as count and others that we'll have a look at later. So that's the Group By.

Now the fifth clause, the having clause, is the least used. It's the equivalent of a where clause after the Group By. has been done. So let's have a look at our query again and I'll put the simplified version here.

Here we have the field, the column called name and how many times it has been used and you can see it's been used a maximum of 1305 times but it's also been used a minimum of one time. So suppose I wanted to remove all of those times where the number of times used is under 6. So it starts at 6 or above. Now I can't put it here in the WHERE clause because the WHERE clause just restricts the number of rows. It doesn't group them.

So if I say SELECT name from col$ WHERE the column name is equal to 1. That gets me my 10,000 rows. I can't then say AND where the count is under 6, oh it's greater than 5 in this case, we want from 6 and above, because that to the computer makes no sense whatsoever. Group function is not allowed here.

So what we need is the group done first. So now we have 1361 rows and now we can put in our having. So the having now works just like a where on the table that we've got to by this stage.

So having the count being greater than five. So now if I run that and you can see that we are now down to 271 rows and the minimum Number of times is 6 and the maximum is still 1305. Now you might be saying, okay, this doesn't quite work to me because I thought you said the SELECT clause happens after the FROM, WHERE, GROUP BY and HAVING. But we're using a column in the HAVING. And that is true.

We are using a column. Or actually we're using a calculation. We're using an aggregate calculation. Now the thing is, this calculation doesn't need to be in this table.

This would be perfectly legitimate. I'm using the having clause based on count star without count star being in the select clause. That's perfectly legitimate. So that's equivalent of having that. It's just a calculation which I then use in the select clause.

What wouldn't be legitimate is if I were to use the rename of count star. So if I put it in as having number of times is greater than five, that would not be legitimate because we have not reached in logical terms, the select clause. We've gone from where group by having next is select. So I can use a function. The function can appear in the select clause.

It just doesn't need to. Now, just as a point of reference, we have this error. ORA00904.

What we can do, if you want more information about this, is you can Google. So if I Google ORA00904, you'll see this nice website, Tech on the Net, which explains the error message, invalid identifier, and explains in fairly plain English what is happening. and how to resolve it. So I quite like this website, Tech on the Neck.

It gives a huge number of error messages. and how to resolve them. Now if you prefer a more official source then you can type Google error message help and you'll see that the first link we have here is for docs.oracle.com and again we have the number of message types so if I find this one 904 You can see that it says it is for invalid identifier.

It tells you what the problem is and it tells you an action. Now for me, the techonthenet.com is better because it actually gives examples as to how to create this error and how to resolve this error. So I prefer Tech on the Net to the official website, but you may prefer the... official one. So having, it's like having a second where but only used after a group by.

So it is the least used of the six but if you're having a group by and then want to restrict the number of rows further, having is your clause. The final clause is the order by clause. Now we have previously seen that we can temporarily change the order in SQL Developer.

But that's not available for all versions, all programs that can access Oracle SQL. Sometimes you might want to say, this is the order I want it in. The order by happens right at the end. So we've done the from, we've got the data from the table, we've reduced the number of rows, we've grouped the table by group by, we've reduced it further using the having clause.

And then we reduce the number of columns and maybe renamed columns, maybe added functions. So what fields, what columns we want to retrieve. Order by then works on this list. And so it's the only clause where you can use the aliases, the renamed of the fields. So we can order this by number of times.

So now let's execute that and see what happens. So you can see that it is ordered it in terms of number of times starting at the bottom and having the biggest at the top. Now, I should point out that it would also work for counts using order by count star. Usually, however, if you're doing things other than functions.

If you have renamed an original table column, you have to use the new name. Now, what if we wanted it the other way round? So we wanted the big numbers to be first. Well, we can add after it the word DESC. D-E-S-C, short for descending.

We can't put the word descending, however, it has to be the short-hand version. So now if we execute this query you can see that the number 1305 comes at the beginning and the number 6 right at the end. However notice that we have got two rows which have been repeated 91 times so they are 91 columns in all of the tables. What happens then?

Which one comes first? Is it Object ID or is it table name? And the answer is it could be either.

It could be entirely what the computer wants. So this is when you can say, well, actually, I want the name to be ascending. Now, ASC for ascending is completely optional.

You could just have name. But when you've got descending and descending, I think it adds a lot of clarity. So if I play this, you can see that object ID. is ordered before table name. However, if I said name descending and run that, then you can see it's the other way around.

So it does it by number of times first, so we still have the big numbers at the top, but then when it gets to a tie, it now orders by name descending. Now, what happens if I was to get rid of both of these descendings? Well we would have both of them going in ascending order. What if I put name descending? Is it going to do number of times ascending?

Or is it going to do the number of times descending? In other words, is the descending going to apply to both these fields? And you can see The answer is no, it is still number of times ascending and name descending.

So you can see because of this potential ambiguity, that is why I would put ASC for ascending if you are using descending as well. Otherwise, I wouldn't bother. So we have now gone through the basics of the SELECT statement.

There's a lot of detail that I've missed. For instance, how do you get two queries and put them together, for instance. But we'll be going through all of these again during the course.

There is one thing, however, I want to finish off this brief introduction with. How do you remember the order of these clauses? And this is quite important because this shows whether you are actually used to SQL or whether you are a newbie. Whether this is your first time and you're in a job interview and you're sweating and you're going, um...

I think it's select then from then order by and then group by getting the order wrong. And it is quite common to get the order of where and group by in the wrong order. It's also quite common for new users of SQL to completely forget that you need the group by when you're using get aggregated function and some non aggregated columns. So how can you remember the order of this. And my best way is by using what you've got right in front of you, your keyboard.

So if you're at a job interview, you will undoubtedly have a computer in front of you. Now let's just have a look at the first letter of all of these. Select, from, where, group by, having and order by.

Have you noticed something about five out of these six clauses? They go from left to right. Select from group by having order. In fact, three of them are consecutive from group by having. So this way you should not be able to forget the order of five of them.

Select from group by having an order by. The only one that you have to remember is the where. And you just need to remember that that comes after the two compulsory clauses.

So it comes third. Select from where. So if you can remember that, then in any job interview, or indeed when you're actually creating SQL statements for real, you will be in no doubt as to the order of the clauses.

So how did you do with this practice activity? It's good to do this practice because it refreshes in your mind what you've just learned. Oh yes, I learned something about this. this and allows you to build on just seeing it but done on the screen. You actually do it and you enable your mind to go ah these are the connections that I missed.

So first of all we want to create a select statement that gets all of the columns from the table called view dollar. Now to get anywhere we need a window first of all so click on SQL worksheet and click ok. So I'm going to create a select statement That's going to get all of the columns from a table. So all of the columns is a star, an asterisk, and then the word from and view dollar.

And how should I end my SQL statements? With a semicolon. Now I've separated it onto two lines.

That was not necessary. You could have it on one line, you could have it on four lines if you like. But this is generally the style that I use. As long as you don't try and separate words. That's fine.

So now let's run this SELECT statement and here you can see the results. Lots of columns. So number two is instead of getting all of the columns back, can you just get obg and then a hash or pound sign and the word cols. So instead of using the asterisk, the star, we put in obg hash or pound and then a comma and then col dollar. So if I run this, we will get two columns back.

However, you notice I made a mistake if I typed in col$. It says invalid identify. In other words, you've made a mistake with the name.

So it's cols with an s at the end. And the comma is important. If you don't have that comma, then you'll just get one column back. It will be this one, the obg column, but it will have at the top the word cols. That's because I'm in this case renaming the OBJ column with the word Coles.

But I actually want both columns back, so I need that comma in between. Next, filter it so that only of those rows where Coles equals 12 are shown. So after the select, after the from, that's where we can put in a where and where Coles equals 12. Now you can put in spaces if you like. Again, generally, that's what I like to do, but it's a sort of a house style. I think it aids readability.

So if I run this there we can see the answer. Now we don't know how many rows it is. It's at least 50 rows.

If I click in there and click ctrl and a you can see that we have 252 rows. Next please sort it so that it is in a descending order of rbj hash or pound sign. So several ways of doing this.

Firstly the sort it should get you the two words Order By. And so I can order by rbj hash so you notice right at the moment it's not completely ordered in that way. Here we can see 17,000 in the middle of some four thousands. So if I run that you can see that now it is really in that order. Now another way of doing it could be order by one and that would order by the very first column.

But it can be considered bad form to do that unless you've got a particular reason. So order by name would be much better. And then the final question, can you remember the names of the other two clauses in the SELECT statement and in what order you would use them?

So we've got SELECT, FROM, WHERE and the other two clauses are GROUP BY and HAVING and they go in between WHERE and order by. So I hope you enjoyed this first practice activity. I hope you're getting really good at it. Now if you're not, if there's something that didn't quite work, then the great thing about this video course series is you can just go back to an earlier video and replay it, put on subtitles maybe, and just see what it is that you can... oh what's it what did I miss?

And then hopefully you'll be able to come back to this practice activity and get a hundred percent. So we'll be building on these skills as we go throughout the course. So wherever you go next, whether it's back to a previous video or onto the next part of this course, I'll see you there. Well, thank you for joining me on this course about Oracle Database SQL.

And the question you may have now is, well, where do I go from here? So, so far you have learned the six principal clauses select from where? Group by having an order by.

But what else is it useful for you to learn? I mean, you could find all sorts of information, but what is it actually useful for you to have? Well, let's ask the experts.

Oracle. Oracle have got something called Oracle University, which has a series of training courses and also certifications. So you don't have to do the training course to go for the certification. Now, the certification in this question is... IZO or IZO071 so IZ0071 and you can see it's not an inexpensive certification $245 but ignoring that it's a two hour exam with 78 questions and at the end it would give you a Oracle database SQL certified associate but what's much more interesting is what do you actually need for this certification?

So this is stuff that Oracle actually want you to have. And if you have a look at review exam topics, you can see that it's divided into about 18 different topics with lots of subtopics added in. So we have got things like relational database concepts, restricting and sorting data. So limiting rows, we've had a look at the WHERE clause.

Self joins, outer joins, Cartesian joins, insert, update and delete statements, sub queries, restricting group results and creating groups of data. We had a look at the GROUP BY earlier and we also had to look at the HAVING clause as well. So this is probably a good place to start.

The curriculum for the IZ0071 certification. I'm not necessarily suggesting that you go for the certification and certainly not at this stage. It's just that these items could be quite useful for you to have.

So how do you get this training? Well there is a recommended training and well let's just say if you click on any of these and I've opened it before because it does take a while, this isn't the swiftest website, you can see that the price for their certification is around $3,400. And if you want to do it in a classroom, it's more like $4,000. So very expensive.

So maybe we won't use the Oracle University database courses. They're just a bit too pricey. Maybe you want something in book form. Well, the official book is this one. It's the OCA Oracle Database SQL exam guide.

Basically, just go to your favourite bookseller and type in IZ0071. It's a good book. It's fairly comprehensive. Comment how many pages? About a thousand pages?

832 pages. The average customer review of only three stars doesn't seem that good, but I've... Yeah, there's maybe some errors in it, but I found it a nice read. If you want something free, then you can go to docs.oracle.com forward slash en, en for English. Here we have all sorts of online help.

So if I click on Oracle Database, database, I click on get started. So we currently have this version. So it depends which version you're talking about.

19c is the latest one as of the time of recording, but maybe you need to have a look at an older version. So get started. Introduction to SQL and then Oracle Database SQL Language Reference.

So this again is a nice read. It's around 1000 pages, but you don't have to sit at your computer just clicking on page to page. Unless of course you actually want to do that.

Instead there's a download button here and that allows you to download it as a PDF and then you can save it or print it. You know, email it to yourself, put it on an iPad of 2000 pages. I thought it was a bit shorter than that. It's very comprehensive. Possibly not the best way to start.

But if you already know a fair bit about a bit, this will give you a way to hone your talents on one particular skill perhaps. So these are some of the ways of getting the training that Oracle recommends. And it goes from free, downloading a manual, 2000 pages, to a 832 page book, to some very expensive training.

Well congratulations, you made it through this hour-long course. So let's just have a recap of what's happened. You may have installed for free on your computer Oracle Express Edition and Oracle SQL Developer, so you can continue to practice your skills.

We looked at the SELECT statement and the six principal clauses. SELECT from where, GROUP BY having and ORDER BY. And you now know how you can remember them just by looking down on your keyboard. So, I hope you found this a lot of fun and I hope to see you on future courses.

So, whatever you do, thank you for joining me and keep learning.

Transcript for:Overview of Oracle SQL Course Essentials

Transcript for:
Overview of Oracle SQL Course Essentials