Practical introduction to relational databases

Renaming columns in result set

This part is going to be rather short. Let's use the last example from the previous lesson about fetching only some columns:

SELECT table_schema, table_name, table_type FROM information_schema.tables WHERE table_schema = 'auth';

Occasionally using the actual names of the columns might not be informative, or it might be overly long, or it might be named weirdly. In those cases you can adjust what they are known by using select actual_column_name as whatever_you_want_to_call_it from .... In the above it would look like:

SELECT table_schema as schema, table_name as name, table_type as type FROM information_schema.tables WHERE table_schema = 'auth';

This results in the same actual data, with the difference being the headers:

A practical application of this is when you're doing some calculations / operations on the fetched data:

An advanced use of this is when you're doing operations on the fetched data before you're presenting it to yourself, for example:

SELECT table_schema || '.' || table_name FROM information_schema.tables WHERE table_schema = 'auth';

This will concatenate the value in the schema row with the value in the name row using a dot in the middle. But more importantly, the title used for the result set is going to be ?column?

This is not great. To deal with that, you would add an as modifier to the result set:

SELECT table_schema || '.' || table_name as fully_qualified_table_name FROM information_schema.tables WHERE table_schema = 'auth';

If you're okay with everything, let's more on to restricting the result set with a where clause.