all select statements are:
select ... from ... where ... groupby ... having ... order by ...
We want to select the dept id and name from the department table, so we would use the following sql statement:
Ok, so let's say we want to select from the employee table, and we want to select the last name and the employee id. But let's say that we want it in all upper case. So what we can do is surround the column you want to be converted to uppercase with a "function". So upper() would go aruond lname.
Now, if you look at the column above lname, it looks really ugly. So let's say we want to change that to something nice looking. We need to define an Alias.
Now, let's say desc account; to see the account table.
We see that we want to get the information from the column "open_emp_id". We see that there are alot of repeats from this.
But let's say we only want to see unique numbers here. We would say:
Ok, we have done enough sql by this point to get the impression that "from" is always followed by a table. However, it is really just a datasource. This could be a table, but this could also be another query. So let's look at another example to see this happen.
Another data source could be something called a "view". Let's say you have a table that has columns and rows. Let's say you give access to people as a table. They can select all of the data. That would compromise confidential data like salary, and age. So instead of letting people directly access the table, we are going to create a "table" that isn't really a table, but rather it is a view.
So when people select from the "people" table (really a view. It's not a table) and try to select name and salary, it won't work because you didn't put that in the view. Some companies have different views for different types of people. So let's work with a view
When we use the where clause it will narrow things down. Without the where cluase, if you ran a delete command, it would get rid of everything in the whole entire table. When you use a where clause it always looks at a row and evaluates true or false. There is no maybe. So now let's look at how to use the where clause.
When you have a table, it is not stored in the database like a table. It is not stored in the database one row after another. There is no order to it. You have to explicitly put an order if you want one. In order to have order come out, you need to use the order clause.
So far we have talked about putting things into upper case. We have talked about creating aliases. We have talked about creating views, sorting, and using the where clause.
The book has a list of fairly common functions of most db on pages 91 & 92.
Now we will go through doing our exercises
Homework. Go to the book. Look at the index. Find things like inner join, outer join, and stuff like that before you come to the next class. Next class we will do things like 'in' or 'not in' or 'between'. Next class we are going to focus on table joining.