0150 Having, Insert, Update, Delete and Built-in Variables

Where VS Having

Where and Having are two clauses that we will best explain using examples. This time we will create only one table.

1) CREATE TABLE WhereOrHaving ( Letter CHAR DEFAULT 'D', Number TINYINT ); 2) INSERT INTO WhereOrHaving VALUES ( 'A', 1 ), ( 'A', 2 ),                                    ( 'B', 3 ), ( 'B', 4 ),                                    ( 'C', 5 ), ( 'C', 6 );

Where

Where is simple. It is a filter that will exclude all the rows which don't satisfy the condition. SELECT * FROM WhereOrHaving WHERE Number > 3;

Having

Having is more complex. First, we will group our table by the first column.   SELECT Letter, SUM( Number ) As sumOfGroup FROM WhereOrHaving GROUP BY Letter;

Then we will remove all the rows where sumOfGroup is smaller than 3. This time we do not use WHERE clause. We are not filtering detail rows, this time we are filtering groups. For that we use HAVING clause. SELECT Letter, SUM( Number ) As sumOfGroup FROM WhereOrHaving GROUP BY Letter HAVING SUM( Number ) > 3

Let's combine these two clauses to better understand them.

1) This is our starting table. We will transform it step by step.

2) Next, we will filter only those rows where Number is bigger than 3.

3) We will now group the table from the previous step by the "Letter" column.

4) And finally, we will filter only groups that are bigger than 6.

So, first we have to use WHERE. WHERE is always used before grouping, because it works on the detail rows. After this comes grouping. When we get our groups then is the time to pick only some of them by using HAVING condition.

INSERT

INSERT statement is used to enter data into table. We will insert some more rows to table WhereOrHaving. Basic way of using INSERT is to make a list of values. These values will be placed in the table as a new row. Our table WhereOrHaving has two columns of CHAR and TINYINT types, so we should be careful to insert data of that data types.

INSERT INTO WhereOrHaving VALUES ( 'E', 36 );

It is also possible to enter several rows at once. INSERT INTO WhereOrHaving VALUES ( 'X', 36 ), ( 'Y', 46 ), ( 'Z', 56 );

IF we don't provide values for all the columns, insert will fail. INSERT INTO WhereOrHaving VALUES ( 'M' );
	We have to provide values for all the columns, even if some of values are nulls. INSERT INTO WhereOrHaving VALUES ( 'M', null );

Sometimes we want to be specific into which columns we want to enter values. Now we can provide values in any order we want.

INSERT INTO WhereOrHaving ( Number, Letter ) VALUES ( 13, 'P'); We will first provide value for Number and then for Letter.

If we use syntax where some of the columns are specified, but other are not, then all other columns will be filled with nulls.

INSERT INTO WhereOrHaving ( Letter ) VALUES ( 'G' );

If we enter value for Number, and not for Letter, then the DEFAULT value will be used for the Letter.

INSERT INTO WhereOrHaving ( Number ) VALUES ( 77 );

This image will revise all of our results.

INSERT With SELECT

We will take small sample from the table above.

SELECT * FROM WhereOrHaving WHERE Letter = 'D';

We can now add this sample into any other table. Presumption is that destination table should have the same structure as our sample. For simplicity we will not add this sample to same other table, but we will add it back to our WhereOrHaving table. Then, our table will have two ( 'D', 77 ) rows.

INSERT INTO WhereOrHaving SELECT * FROM WhereOrHaving WHERE Letter = 'D';

UPDATE

We saw that we now have two rows in WhereOrHaving table with nulls. We have "M, null" and "G, null".

UPDATE WhereOrHaving SET Number = 101 WHERE Letter = 'G';

We have updated "G, null" to "G, 101".

We can update values "M, null" to "Q, null".

UPDATE WhereOrHaving SET Letter = 'Q' WHERE Number IS null;

To find rows where column value is null, we use operator IS, and not =.

DEFAULT value for Letter columns is "D". We can set any field in Letter column to this default value.

UPDATE WhereOrHaving SET Letter = DEFAULT WHERE Number = 101;

Now we have default letter "D" beside number 101.

DELETE

We use DELETE for deletion of rows. We can return our table, WhereOrHaving, to its original state, by deleting all the added rows.     DELETE FROM WhereOrHaving WHERE NOT Letter in ( 'A', 'B', 'C' );

Built-In Variables

MonetDB has a collection of built-in variables. Those variables can provide us with valuable information about our current session and current time.

CURRENT_SCHEMA	CURRENT_DATE	CURRENT_TIMESTAMP	LOCALTIME
CURRENT_USER	CURRENT_TIME	CURRENT_TIMEZONE	LOCALTIMESTAMP

SELECT CURRENT_SCHEMA AS CurrentSchema, CURRENT_USER AS CurrentUser, CURRENT_DATE AS CurrentDate
     , CURRENT_TIME AS CurrentTime, CURRENT_TIMESTAMP AS CurrentTimestamp, CURRENT_TIMEZONE AS CurrentTimezone
     , LOCALTIME AS LocTime, LOCALTIMESTAMP AS LocTimeStamp;