What is the difference between the list and minimum and alias in SQL?

The difference between the list and minimum and alias that max actual savings so here we have our list prices a second column min price is the third list minus list price minus min price as the fourth and then we have a column alias in double-quotes max actual savings so we get it displayed in mixed upper and lower case you need to double quotes even just to get the spaces in here and the last one says to calculate the maximum discount % that should be the column alias and the calculation is list price minus min price/list price which would give you a fraction so they multiply it by 100 now notice what I did here / ends I put/ends around list price minus min price to say it do that first if I didn’t do that if I just said list price minus min price/list price it would take them in price / the list price remember the priority order what gets done first division gets done before subtraction.

So you want to put/ends around this to make sure the subtraction gets done first then divides by list price and then we even put/ends around that to say and then you multiply by 100 can you get away with coding it without / end up just right but you’re always better putting/ends best that’s best practices then everybody knows exactly what you’re trying to do rather than try to guess what is or Oracle’s hi Rocky as far as how it executes things with plus minus x divided and so on from the product information table and I happen to say it happened to be a bigger table so I said I had a where clause where grownup less than or equal 100 grownups we haven’t talked about yet but it’s the row number as it’s retrieved from the database so retrieve whatever Rose it retrieves we don’t ever to buy it’s just the rows as it comes in before any order by and the only reason I did that is so I can display this and only display the first hundred rather than display.

If there were thousands I would display many more questions somebody hi Jeff i have a question this is Emily yes Emily earlier you said that describe um could be written as DES see do you have any other examples of what can be abbreviated that way all sequel plus commands describe disconnect um host h 0 st prompt we haven’t gotten into other commands but all sequel plus commands can always be abbreviated with four characters sequel commands like select insert cannot okay thank you you’re welcome good question any other questions’ anybody okay that was number four now let’s notice something in number four here’s my max actual savings which is this price minus min price let’s go down here list and men are both no minus no I don’t know- I don’t know is I don’t know.

Let’s go down a little further list is no minus 73 no- 73 is I don’t know- 73 which is I don’t know and it annuls it’s not- 73 that’s not the answer minus seventy-three, of course, any arithmetic were there no results in a no and the same thing here when we’re doing the discount noticed this is using list price and min price in this calculation, therefore, it’s an arithmetic expression at least one of them one of the things that’s using is a no and therefore any of those will result in no here also even if the list price were now and you / know anything with nose in it results in a no 10 / no is now null / no is no doesn’t matter okay and the last one was calculating the surface area of the earth tonight alright I’m sorry if you can ask a question yes how do we have this sequel plus I believe you folks always use sequel developer and you would be typing all of this in in the sequel developer we can so can you go through.

The solutions and sequel developer and sort of sick applause ah difference there is no difference at all it’s the same sequel commands the reason I do it in sequel plus is that it’s easy for me to show you this in sequel developer every solution in a separate window and it’s kind of you jumping I’m jumping all over the place to try to show it to you that’s the reason I show it to you in sequel plus but for instance whatever we type here we could type in a sequel developer window so let’s see if I did a tool sequel workshop and I did the OE schema okay and then let me grab some of this code let me grab for instance this over here copy and paste it in that’s the one the carrot that I’m doing now that calculates the area and I run it calculates the same result is the same code gets the same result as I would see down here’s the same code gets the same result I have so I have a couple of questions yes so I was so there’s one of the mathematical operations in the sequel.

So it doesn’t seem like there’s a power operator or there is no power-operated basically plus-minus x divided, but there is a function to raise something to a power ok so it will talk about that later right yes I don’t know that we talked about that one but let’s take a look because I was thinking if you do or you want to do something to the power 6 well it’s kind of right stupid all right it looks multiplied by itself six times here it is I just looked up our function in Oracle yes the Oracle documentation uses power and it gives you an example power of three to the second power will give you three squared is 9 so you’d use that use it with a function and we’re going to talk about functions in another pack in another chapter power happens not to be one of the functions that we discuss.

But if you you know if you want to raise something you know if you need something and you know it’s likely to be there Oracle likely has a function to do it if they don’t have an operator you know some languages into power with two asterisks or something like that Oracle doesn’t support it and sequin tried that the sequel doesn’t support matter it’s not just Oracle it’s equal doesn’t support that ok you have to insert that function into what we’re looking at right now would we have to put the function in parentheses or could we just put you know like the asterisks and then the word power you could do asterisk power that’s a good point let’s go look let me do it here for you uh I could do x power of power this 2 comma to let me see if I got that and I had an extra the only reason I got that and that that last close paren closes the whole thing I think and there are same result Thanks okay.

So what do we have 197 million square miles on the surface of the earth and of course I say oh and one more thing yes sorry Jeff and maybe you said this but I got distracted is it are you going to tell us later how we can truncate the decimals and output to make you like two decimal places absolutely we’re just not there yet okay that’s fine I can wait yep it’s a two char function it’s called to underscore CH AR and it formats your data formats your numeric or your carrot or your date data types to display them with as many decimal points this month a year you know however you want to display it’s a formatting function.

Leave a Comment