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:

Learn PostgreSQL

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

Software ==>	Postgres SQL (35 exercises)
Topic ==>	Calculations using dates (3 exercises)
Level ==>	Average difficulty
Subject ==>	PostgreSQL training

Use date functions to calculate the length of concert tours ad show gross revenue per day

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 fields from the Tour table:

Some of these tours looks quite long. I wonder ... how long?

Subtract the start date from the end date to show the length of each tour in days:

That IS a long time to be on the road.

Add another column to calculate the daily revenue of a tour by dividing its gross by its length in days. Sort the results in descending order of this column, adding a criteria that the tour length must be at least 1000 days:

Your query should show these results (we should have filtered out null revenue rows too).

Finally, add a column to show the duration of a tour as an interval (you'll need to convert the dates to timestamp data type first to do this):

The same data with an additional column giving the tour duration as an interval.

Save the script as Goodbye Elton.sql then close it down.

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

Learn 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

Use date functions to calculate the length of concert tours ad show gross revenue per day

Head office

London

Manchester