How can you Restrict and sort data?

Restricting and sorting data, the where clause and sorting date of the order by Clause and we’re also going to do something called ampersand substitution which we’re going to get into someone had asked about can you like write it write some statements that allow you to prompt the user for some data and then use the data and so on and I mentioned that those are sequel + commands and they can be many of them can be used in sequel developer and this chapter is going to go over some of those and that’s called ampersand substitution and some other enhancements to that’s in chapter 3 when we get to chapter 4 that’s all these think a lot of these single-row functions we talked about like the power function the to char function to Candace play things properly and so on the upper and the lower function and so on that’s chapters four and five together to do the functions that are on single rose.

And the end the function is to convert data types like to char and so in chapter 6, we do aggregations so chapter six is where we do the group functions the group by the having clause and you know and how old r that works and then some min-max and so on chapter 7 is the joins displaying data from multiple tables so we get into all the types of joins chapter eight is the subqueries where a query can have a query in it sometimes called the subquery in a query it’s a child query it’s a query within a query so we’ll talk about subqueries is very powerful capability chapter nine is set operators and that’s where we do things like unions we do a select and in union with another select and it gets rose from multiple tables at one-time chapter 10 is manipulating data DML data manipulation language insert update delete how you change data within.

The table everything before that is selecting data getting data out chapter 10 is we go into a bit of inserting updating the leading and I’ve been told that you guys don’t do that a lot that’s not your major responsibilities analysts your major responsibilities pulling data out but we will cover the basics of that chapter 11 is DDL where you create and manage tables and I’ve been told that you will need to do that from time to time so we’ll get into how you create tables how you alter tables how you define the data types reach column and so on and chapter 2 that’s chapter 11 chapter 12 is the last chapter and that gets into other objects besides tables view of use indexes sequences what they are what they use for and so on, so that gives you a feel for where we’re going okay any questions’ anybody anybody wants something that doesn’t sound like it’s there so would be as an any special requests for anything that doesn’t sound like it sounds like I haven’t included it in where we’re going okay.

If you do think of anything you let me know I said I have a lot of other material and if we have time and if it’s appropriate for the whole class will try to include it okay chapter 3 restricting and sorting data limit the number of rows order the Rose because remember if you don’t order them the order is random and this so-called empathy and substitution which we’ll see how that works okay restricting Rose is the election remember I said there were three main things’ projection was the first one and that’s at which columns do we want and what kind of calculations we want into columns and so on what are we going to project and display to the user the second thing is a selection which rows are we selecting we don’t want all the rows from the table or tables we’re doing a select on we only want certain rose and the third thing is ordering them sorting the rows with the order by.

So the selection is done with the where clause and the sorting of the roast are done with the order by equal is so we’re going to do this select first and it’s part of the Select statement but it’s with the where Clause projection is the actual columns in the Select part of the clause the wording is I don’t like the wording too much because to go the projection and that’s in the Select Clause and then in-call its selection which is in the where clause so it sounds a little peculiar, but that’s how oracle refers to it okay we can have a whole host of different conditions we can have numeric conditions okay such as we’re salary greater than 20,000 we can have character and conditions such as we’re jobbed ID will SA rep all right, so there’s a bunch of conditions we can have in our where clause that says only return me certain roads that follow these conditions.

Well, we got to know a few things about these the first thing is Oracle has what’s known as implicit conversion here is used if the data types aren’t correct so, for instance, they don’t buy it by our incorrect they don’t match salary might be a number and therefore we say salary grade and 20,000 well that work that’s fine I’m comparing a number to a number or Oracle does that easily however what if I go and I say salary greater than the characters between 20,000 oracles will see salaries in number and it’s you can’t compare a number to a character string so oracle will do an implicit conversion and say I’m going to convert that character string to a number if it can well 20,000 it can convert to 20,000 that’s fine so then it will do salary greater than 20,000 and it works fine but that only works if that character string is a good form number 20 2000s are not a number.

Oracle will try at run time to convert that to a number and you will get an error at run it won’t work your query will compile fine but when you try to run it you’ll get an error at runtime, so Oracle will do these so-called implicit conversions when it works take a look at this one that looks like it should work but numbers when Oracle converts character string two numbers it doesn’t know from comas it’s just the number so 20 comma 0 0 0 it will not be able to convert into a number and you’ll get a runtime error for that okay plain numbers typed in do not have commas in Oracle they can have a single decimal point they can’t have two decimal points, okay they could have a minus sign again of a plus sign, but the can of commas okay, so that’s implicit conversions with uh with numeric data Ashley well-formed.

Leave a Comment