This page has 0 threads | Add post

Exercises

Many people around the world enjoy and benefit from our exercises. If you're one of them - and can afford to - please make a small donation to help keep this and future blogs, exercises, skills assessment tests, tips, tutorials and videos freely available to everyone!

You can find other training resources for the subject of this exercise here:

Training classes in PostgreSQL

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

Software ==>	Postgres SQL (19 exercises)
Topic ==>	Calculations (9 exercises)
Level ==>	Harder than average
Subject ==>	PostgreSQL training

Extract information from addresses and list of venue names using text functions

This exercise is provided to allow potential course delegates to choose the correct Wise Owl Microsoft training course, and may not be reproduced in whole or in part in any format without the prior written consent of Wise Owl.

If you don't have a copy of the music_01 database, first follow the instructions at the following link to create it: https://www.wiseowl.co.uk/postgres/exercises/postgres-sql/using-postgres/5350/

Write a query to select the venue and address columns from the venue table, and add two calculated columns giving identical results to show the street address for every venue:

The street address is the bit of the address up to (but not including) the first comma.

Here are the two methods to use. You can decide which is the hard way and which the easy one!

Method	Details
1	Use the position (substring in string) function to work out where the first comma is in the address, and use the left function to extract this many characters (minus one) from the address.
2	Use the split_part (string, delimiter, index number of part to show) function to return the first extracted part if you split the address by the comma character.

If an address doesn't contain a comma you should return the full address (you may find it helpful to know that the position function returns 0 if it doesn't find the string you're searching for).

Add a new query which returns the venue and other_names columns from the venue table.

Venues may have multiple, one or no former names.

When a venue has multiple former names, each one is separated with a semi-colon character. Add a calculated column to work out the number of former names a venue has. One approach to doing this involves comparing the length of the other_names column with a version of the same column where the semi-colons have been removed.

Sort the results to show the most renamed venue at the top of your list. Don't forget to account for nulls!

Save the script as Ten changes and the same name.sql and then close it down.

You can find other training resources for the subject of this exercise here:

Training classes in PostgreSQL

You can also book hourly online consultancy for your time zone with one of our 7 expert trainers!

This page has 0 threads

Add a new post

EXERCISE TOPIC

POSTGRESQL EXERCISES

POSTGRES SQL EXERCISES

Extract information from addresses and list of venue names using text functions

Head office

London

Manchester