Group by

Note the "having" that is tagged on the end there. It makes it display onle the records with a count of more than 5. Try lopping it off, and see what happens...

Now we want to see the maximum, minimum, average, and total values from account.

To make it look pretty:

Note the use of the single quote ' to allow for a "." in our column header

and now just to find the ammounts where the type is checking:

Max, Min, Avg, Sum

Now if we want to list all of those values for each particular product code (checking, money market, etc):

Now we want to not only see the product type, but we want to see the branch that account was opened at. So we want to do a double group by. It's very simple. Don't think too hard about any of this, the stupid obvious "that won't work" answer seems to ussually be right:

Now let's say we only want to see the ones with a sum of over 10,000. We would just add the "having" statement we used earlier.

Now, let's make the code a bit easier to write. We aliased sum(avail_balance) to total, so let's use that alias!

Now let's insert the last name of the employee that opened that product_cd

Extract

Now we are going to learn how to extract things. We are going to extract the year from the start_date column

Now let's find just the ones during and after the year 2004

Note that we used ">=" which means greater than or equal to 2004.

Now let's group by year and branch id

Special Functions

You can also do some crazy math with SQL

select max (pending_balance - avail_balance)
from account;

and sin and cosin

select cos(20), sin(20);

and sqaure root

select SQRT(2);

you don't even have to have them inside of actual DB querries. You can just type it in.

cool.

Left Outer Join

Now we need to select all of the account id's and to see what customer owns that account

Now we also want to see the business the customer is in. So we want the business name as well...

We will need to join the account table to the business table using the customer id, since the account and business tables have no relation

Now, alot of results got cut out since not all of the accounts are business accounts. This happened since we did "inner join". So lets try something else

How this works is it takes whatever is on the left side that it finds, and outputs it no matter what. Even if there is no match on the right side. If there is no match it just puts "NULL" there. If there IS a match it goes ahead and displays the data. This keeps all of our original values intact (including the personal accounts) and displays the business names for the business accounts.

Now he is only interested in the individual accounts.

Show all accounts including the business customer, but for people with individual accounts you see the last name.

Next try to make it show the business name if it has a business name, and a last name if it is a personal account. (Do this at home)

Now let's take that self join we did last week where we had the problem of the "big cheese" getting dropped off since they didn't have a boss.

This time we are going to use an outer join left to take care of the problem

Now, what if we have a worker table, and a children table. We want to list the workers and how many children they have.

Create two tables and see if you can do this on your own

It is very important when you say "left outer join", whoever is the left is the one that drives the number of rows it is going to select out. Basically, this is equivalent to saying the right table is a "right outer join". So, you can do it either way. Just make sure the one you want to drive the query is on the side you specify.

Next time we will cover more joins

Find out (for mySql), find out exactly how to work around mySql not allowing for except (minus).