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 training courses

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

Software ==>	Postgres SQL (18 exercises)
Topic ==>	Calculations (8 exercises)
Level ==>	Average difficulty
Subject ==>	PostgreSQL training

Work out the average length of songs in a list of albums using calculations and casting

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 title and album duration (in minutes and seconds) from the album table, along with a calculated column to work out the total length of an album in seconds.

Multiply the minutes by 60 and add on the seconds to get the final figure.

Add the Tracks column to your query and then create a calculated column to work out the average track length in seconds for each album. Make sure that your result is a decimal number rather than an integer.

You should have a few more decimal places than you really need!

Add another calculated column which shows the average track length in minutes. You can divide the previous column by 60 to do this. Make sure the result can display up to 4 digits in total, with 2 digits after the decimal point.

The first few results really only need to display 3 digits, but there may be bigger numbers further down the list...

Modify the query so that it returns albums whose average track length is 10 minutes or more. You can comment out the first two calculated columns you created to tidy up the results.

Albums with tracks longer than 10 minutes

The only two albums with average track length exceeding 10 minutes.

Save the script as Really long players.sql and then close it down.

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

PostgreSQL training courses

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

Work out the average length of songs in a list of albums using calculations and casting

Head office

London

Manchester