Category: Back To Basics

June 10th, 2019 by Mathias

It is time for aggregating data, using the GROUP BY keyword.

Hint: I will be using the emp/dept tables used by Oracle for demo in all demonstrations. I recommend Live SQL for playing, it is a great place to test SQL and learn from others. You can also sign up for a database for free (for a short time) on Oracle’s cloud. I recommend beginning with Live SQL and moving to paid services when you are ready,

Hint II: You can run the code in here by going through the little tutorial I created.

In here we will use the hr.employees table that lists data of employees including the department they work in. From this we want a small report showing the number of employees that works for each department.

For this we want to count each employee and sum it up by department ID.

Lets first use this to show all rows in the hr.employees table:

1
SELECT * FROM hr.employees;

Now we’ll use the function count(*) to count up all the rows. Without anything else it would just give us a count of all rows in the table. Since we want to get the number of rows (employees) per department we use the keyword “group by” to specify that we want data to be grouped by department id.

Thus we’ll use this SQL:

1
2
3
4
SELECT department_id
     , COUNT(*)
  FROM hr.employees
 GROUP BY department_id;

When we run this we will get a report like this:

DEPARTMENT_ID COUNT(*)
50 45
40 1
110 2
90 3
30 6
70 1
1
10 1
20 2
60 5
100 6
80 34

Pretty nice, now we know how many people there are in each department. JUst having the ID is not all that nice, but it can be found by looking in the department table. In a future post, I’ll talk about how to combine the two tables in one SQL.

The observant reader has noticed that there is one employee that does not belong to any department, that row has an ID of “-“. Can you figure out who it is by applying the last episode of BTB?  It is actually not a dash in the column, but it is a NULL. That is database speak for no value has been given. That too will be a future post.

Posted in Back To Basics

May 28th, 2019 by Mathias

Let’s start with baby steps

We’re all very excited to read a new blog with a neat trick or a cool and complex feature or even a very useful undocumented function. However that is preaching to the choir. We do that and then we get together and complain that there is no new fresh talent in the database business. Well, it may be a bit hard to get into it if all we do are talking new features and really cool uses of analytical functions.

I think we need a bit of both, some stuff that are not advanced. In fact it does not have to be perfectly accurate or usable in all cases, the most important thing is that for someone new to the field it is understandable and usable for just getting something that works for their experience level.

My intent will be to challenge´myself to write things I feel is cringeworthy simple when read by anyone who has been around the block a time or two. If you find something to be “ridiculously basic, annoying that anyone would blog about such newbie content”, then I have achieved my goal.

Still reading? Then you must be looking for such material. I will end this post with a light overview of the mandatory keywords in a select, the stuff often referred to as vanilla SQL. This is the stuff I pretty much assume a reader will be aware of before looking for blogs on more.

Hint: I will be using the emp/dept tables used by Oracle for demo in all demonstrations. I recommend Live SQL for playing, it is a great place to test SQL and learn from others. Just cut and paste the SQL below and runt it and you have the result displayed immediately. You can also sign up for a database for free (for a short time) on Oracle’s cloud. I recommend beginning with Live SQL and moving to paid services when you are ready,

A SQL as basic as they come will look something like this:

1
2
3
4
5
6
SELECT department_id
     , department_name
     , manager_id
     , location_id
  FROM hr.departments
 WHERE department_id BETWEEN 40 AND 70;

There are three keywords to this SQL – Select, From and Where. Technically a SQL can do without the where clause but that rarely happens in practical SQL used for a purpose.

Select

The select keyword is where you list the columns you want to select the data from.

From

The from keyword lets you list the table(s) you want to get data from.

Where

The where keyword lists the conditions.

In plain english the SQL translates to “Get me the id and name of the department, the IDs for the manager and the location. I want this data to be fetched from the department table where the department ID is between 40 and 70. Not too far from the SQL itself.

Now, go run it on Live SQL. What is holding you back?

Posted in Back To Basics