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:

PostgreSQL learning

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

Software ==>	Postgres SQL (15 exercises)
Topic ==>	Calculations (5 exercises)
Level ==>	Relatively easy
Subject ==>	PostgreSQL training

Casting and converting to show the correct average ticket price for each tour

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 return the following columns from the Tour table, withouit any ordering:

The attendance and tour_gross_$ values are stored as types of integer.

Add a calculated column called Avg_ticket_price which divides the tour gross by the attendance, and sort your query by this new column so the tours with the highest average price come first:

Dividing one integer by another produces another integer.

To produce a non-integer result you need to modify the type of at least one of the columns involved in the calculation. Use the CAST function or :: suffix to change the data type of the attendance values to numeric.

As a reminder, the syntax of the CAST function is CAST ( value AS data_type), or you can use value::data_type instead if you prefer.

If you run the query again, you should find the answer now has (plenty of) decimal places.

Perhaps a few more decimal places than you expected?

To control the number of decimal places you can either use the round function to show your existing expression to 2 decimal places, or else change the data type of the answer to numeric(5, 2) (that is, show the number with a maximum of 5 digits, with 2 of those digits after the decimal point).

You don't get much Red Hot Chilli Peppers for your dollar ...

Save the script as Tour prices.sql and then close it.

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

PostgreSQL learning

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

Casting and converting to show the correct average ticket price for each tour

Head office

London

Manchester