Okay. Welcome to the next chapter, reading data. We're going to do quite a lot of demonstrations in this lecture rather than slides. So, what I'm going to do is going to go into the Management Studio where I have already clicked the new query button. Now, I have a beautiful blank canvas on which we can operate. And so, I'm going to reproduce one of the statements you ran in a previous exercise. First, there's select. Now, I know this is an asterisk, but this is generally referred to as a star and then from department. So, I've got select star from dept where dept is the name of one of the tables in that database. I then hit the F5 function key to run this query. So, this is the most basic form of a select statement because what it's going to do is bring back everything from the dept table, everything in terms of every row and complete rows. I like to think of the star or asterisk as being shorthand for complete rows and every column from that row. 

Even in this very simple query, I can illustrate a lot of features of SQL. I could write in that it is in lowercase, I could put in a capital D on dept. SQL generally doesn't care about upper or lowercase. SQL is a non-case-sensitive language. If you do put two hyphens anywhere on a line, everything after it is considered a comment, but it's not case-sensitive. So, there is a caveat in that if you are using MySQL and running on Linux or Unix, if you're running on Linux or Unix, then the database and the database table names are case-sensitive. There is an option in MySQL to force names into lowercase anyway, and I would frankly suggest that table names and column names are probably best written in lowercase. It's a very common convention to put SQL keywords in uppercase. Now, I've written out my line, but SQL is free format. So, I could, if I wished, write it completely vertically instead, and it will still work. I would suggest the reasonable compromise would be to put each clause in a separate line because yes, a statement comprises a number of clauses as in an English sentence. Another thing is although it's completely free format and case-insensitive, it is structured in a sense that if you try swapping these clauses around, you will not get the same result. There is a defined order to the clauses. 

Okay, let's move on from this very simple query to one where perhaps we have a little bit more involvement in tailoring. In the results by the way, not only are these complete rows from the table, but the order of these columns is as defined in the table, which incidentally is also as described in their diagram: dept number, dept name, manager, sales target. This is all well and good if you just want a dump of a table. But let's say I want specific columns. Then rather than using the star, what I can do is say which columns I want to see. In fact, the job of the select clause is to say what to bring back. And star is kind of just like the shorthand. So, I could say start with the manager. In fact, if I just run that, I will just get department manager. If however I want manager, department name, I put a comma between department name and whatever else I want. Now, this particular tool does have some form of auto-completion. If we run that, it brings back manager, dept name, sales target in that order. This is what I would describe as a comma-separated list. 

SQL as well as being structured in a sense that select has to come before from, punctuation is very important. The comma is a seperator as in it goes between things. If I put a comma on the end of this line, it becomes an error message. Here incidentally there's been an error. What's potentially even worse is that what happens if you miss the comma out? And this does depend on how complex the expressions are in your select list. But in its simplest form, we might take that comma out because what I'm about to do looks like nonsense. See now I've got it down to two columns. The first column is manager. The second column is now labeled sales target. But hang on a minute, the sales target is for numbers we've talked about. So, what's going on? What's going on is that those are in fact department names. So, the point is, I could specify that this column heading isn't department name but department name instead. I can set how this record is described in the results. In other words, I could give what's known as a column alias. Now, I would recommend if you're going to use column aliases deliberately, use an option of as keyword. Basically by having dept name and sales target without a comma in between, what I've said is display depart name as sales target. 

Now, strictly speaking, according to the SQL standard, a SQL statement is terminated with a semicolon. Indeed the SQL Server recognise semicolon as end of statement. However, tools like Management Studio don't force you to do it. So, if you're using a low-level interface to MySQL, you would need to put a semicolon by the end of each line or where there are other options that you would need to terminate the space. So, here it says star equals all. The from clause specifies where the source of the data is, and the select clause gives you the ability to say what pieces of information you wish to see. It's free format language, syntactically quite fuzzy, and you can use new lines or line breaks, a tab, characters, and even spaces if you want. The next is white spaces, which are ignored by the parser. 

Now, there's a couple of issues here. The SQL parser is something which takes some code breaks and it's built to parse trees but it's a thing that breaks the statement up and analyses it. It's not true that it totally ignores white space. What I would suggest is it means it's the way you could have one white space and you can press a couple of tabs, you could have a combination of, or if you don't want to distinguish one and many of them, comments, everything from two hyphens to the end of line. But for the record, you may have noticed this that is here. This thing up here is also a comment as it starts with that character. The reason why that's not on the slide is that strictly speaking, it isn't part of the SQL standard, but that is a form of comment that won't be recognised by the three: SQL Server, Oracle, or MySQL. Anyway, it gives us the ability to write multi-line comments whereas this one is a single line comment. It's just useful for temporary commenting things just to let you know. This will be quite the common format. But in fact laying your columns out vertically, well, you can do it if that's what you're trying to do. Obviously you have to be a little bit careful about how you go about that. This is the comment after dept, underscore, name. You can use certain characters. Okay. So, either you use star as a shorthand or specify your columns explicitly. Then you have complete control over the order in which you specify those columns. 

This is where it does become a little bit like a math lesson. Let's just go down to where we start the statement. We have a number of one, two, three comments. We have operators available. So, we set yourselves a target here. We have arithmetic operators. So, you can use these operators to include calculations in your output. Maybe familiar with all of these like multiplication, which can describe an increased sales target by 10%. However, because it's no longer simply a column, it's a calculation, a calculated column, and therefore it doesn't know how to name it. It's just producing a new column with a calculation. This is one of the big reasons for why you would use an alias to select data. 

Now, different tools, different database management services would come up with different approaches to how we would do that. But in this particular server, it's a column and you can name it as you like. We can set a new target. This is where it really turns into a bit of math lesson or actually an IT lesson, really. It turns out that you can use SQL Server as a massively over-specified calculator. Before running that, I'm going to ask what you think the answer is going to be. If you think it's about 20, have another think. If you're about to say 24 for this calculation, have a look at the operators. It's three times four plus two. It does the multiplication before the addition, giving us an answer of 14. That is due to what we call BODMAS. BODMAS is a mnemonic. The letters in BODMAS, B stands for brackets. Many people argue about what O stands for. It stands for order as it's basically the next thing in the line. And then the next priority is divide, multiply, add, and subtract. Brackets first, orders, i.e. powers and square roots, division, then multiplication reading left to right, then addition and subtraction reading left to right. That is BODMAS. It's not a SQL thing, it's a general principle in computing mathematics. 

The other approach is to simply put the parenthesis where you think or know they should go and basically override BODMAS. My advice is to use aliases. If you want to break words up, put underscores in. You don't have to use brackets to avoid BODMAS, but you can. So, another thing we can do with names is what we call string concantenation. That is essentially sticking strings together. Now, this character here is the pipe character. We use that for concantenation. However, it won't work in Microsoft SQL Server or MySQL unless you switch the appropriate option on. The salesperson table has fname and lname columns, which stand for first name and last name. The pipe character, that's part of the ANSI SQL standard. That helps us concantenation version and variables together. concantenation basically means adding them together. As an example, we can take first name and last name from our sales database. Let's concantenate those together. 

Okay. Salesperson. Now, if we take that comma out and add a pipe operand or. In SQL Server, it doesn't recognise it, which is annoying, right? That is the ANSI standard. Microsoft SQL Server unfortunately doesn't recognise that one. So, we use the plus symbol for Microsoft products. And there we have concantenate the first name and last name fields with no space. Again, you really need to just be wary of which service, which database management service you are using. Here with a single quote, we're specifying a space, which is a quick way of us unconcantenating our names, which is where SQL becomes very clever 'cause there's many, many different ways of manipulating data. That's quite useful. All right. 

Now, we can also use an alias. So, we're going to put that together and select it as full name. So, we need to quote these really somehow. And each quote systems is specific to each of the database management systems. In MySQL, you can use double quotes or single quotes. 

In Microsoft SQL Server, you can use square brackets, double quotes, or single quotes. And there are a number of platform-specific variations unfortunately. With Microsoft Access, you use single quotes. So, the easiest thing to do is to not put any characters into your column that will require you to quote them in alias. 

Okay. Let's get into functions. Functions are a mathematical concept. You define a function as basically having some kind of formula. You can define the function so it takes pieces of information, which we will call arguments. Now in the function, these arguments will be defined as parameters. The purposes of the function is giving some information to return or yield a result. In SQL, there are a number of standard functions. The first one is upper and lowercase. These are standard functions as our substrings. The purpose of the substring function is to enable you to extract any part of a string or character value. Also a function called left, which allows you to work from the left. Effectively it defaults the third argument to one. Those are standard functions. The left, upper, lower, and substring functions. Left is supported by some but not all database management systems. A few other non-standard functions, one is the char function that will take a value of any data type and convert it to a character value. If you only have one column in your database and that's temporal, we can take temporal information, and that could be the order date. In this instance, this is a Microsoft SQL Server date and time. What's going on here is that we're extracting a value from that order date, and it's actually doing a data conversion. But you can also use the to, underscore, char to convert an integer value to a character value. It's a general purpose conversion function. This example here however, the Microsoft SQL Server one, datepart, is specifically for date and time values in the Microsoft SQL Server. This is simply a representation of the date in some internal format. Now, we can use an argument here of qq. What that does is it returns the quarter that the number is in. This has a quoted string. This is going to give me a quarter figure. 

Another function is distinct. Distinct shows me unique rows. If I put department number in this query as well, it shows me unique combinations of department number and county. So, it's removing duplicate rows from the result set. Now, this distinct function is an alternative to the default, which is the select all, i.e. to bring back every row. The last two rows are here we have three in London and three is Surrey. Take distinct statement out. We go back to six rows. That's distinct. The next function is order by. Let's say I want to order the values I get in my return string. I would use the order by function. The argument here is that in SQL databases, it's not easy to predict what sequence the rows will be returned in a result set. It's as if we're getting all the rows from the table, and it's reading through them in some kind of order and a physical order at that. If you want the largest number first, D-E-S-C is short for descending. 

So, that's our descending order. Now, let's put them in reverse order. If I add company number, we can sort on company. And then within the company, it will sort the largest sale first. The company number has been sorted in a default ascending order. Okay. So, that brings us to the end of reading data. Okay. So, that brings us to the end of sorting results. Remember you can order by D-E-S-C to override the default ordering, which is A-S-C or ascending. 

Okay. That brings us to the end of reading data. Let's close this lecture. I will see you in the next one.