MySQL: Giving column as alias name

This entry is part 13 of 26 in the series Learn MySQL

At times, especially for generating reports, we might want to give a different column name or an alias to a table.

For example, in our example, we can use the following statement to change the artiste column to name.

select artiste as Name, album, price from artist

column_name

Another simple example is:

select now() as Date;

column_name1

 

 

 

MySQL: Handling empty string

This entry is part 12 of 26 in the series Learn MySQL

At times, we might come across a data that has an empty record or an empty string.

As in the screenshot below, this is inserted by:

insert into artist (artiste, album, price) values ('Bruce Springsteen','',9.60)

empty

 

If you are new to SQL, you could have wrongly interpreted this as a NULL string.

It is actually an empty string and it can be queried by =” as in:

select * from artist where album=''

MySQL: Handling null value

This entry is part 11 of 26 in the series Learn MySQL

NULL is not a special character in SQL but it requires special treatment.

In SQL, NULL indicates no values.  It can mean unknown, missing or out of range.

To insert a NULL value into SQL, the NULL value should not have enclosing quotes.

insert into artist (artiste, album, price) values ('Madonna',NULL,8.60)

After inserting, you can see how NULL is presented in Toad client as in the screenshot below.

NULL

 

Take note of the technique to query a record with NULL value.

For our case it is:

select * from artist where album is NULL

MySQL: Handling special characters

This entry is part 10 of 26 in the series Learn MySQL

We always encounter special characters such as quotes or backslashes in a string of characters.

Say we need to insert an album with the title Tha’s Life.

It contains a single quote and to make the statement legal, the quote could be escaped by preceding it with a backslash or a  single quote.

insert into artist (artiste, album, price) values ('Frank Sinatra','That\'s Life',8.20)

insert into artist (artiste, album, price) values ('Frank Sinatra','That''s Life',8.20)

 

MySQL: Using user-defined variables

This entry is part 9 of 26 in the series Learn MySQL

We can assign a value returned from a select statement to a user-defined variable.

This allows us to refer to it in later statements in the same session.

Those user-defined variables will not persist across sessions.

select @max_price := max(price) from artist;
select @max_price;

The max price was assigned to variable @max_price.

If you execute the 2 statements on Toad, the screenshot will look like below:

sql_variables