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:

Courses in 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 ==>	Derived tables and CTEs (3 exercises)
Level ==>	Harder than average
Subject ==>	PostgreSQL training

Use multiple CTEs query to return details of venues and statistics of shows held at them

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/

The aim of this exercise is to show details of each venue, together with few summary statistics:

The left-hand columns come directly from the venue table; the right-hand ones are aggregated data.

You could do this with a single query, but it would be messy and have lots of GROUP BY fields, so instead will split it up into 3 separate tasks and use multiple CTEs to produce the final result:

Task	CTE name	What it will do
1	v	Bring in the venue columns
2	s	Calculate the venue statistics
3	N/A	Link the first two to show the final results

Note that this is a typical use of CTEs, allowng you to break a complex problem down into a series of easily understandable tasks.

When you've got this working, save your script as High CapaCTE.sql and then close it down.

You can unzip this file to see the answers to this exercise, although please remember this is for your personal use only.

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

Courses 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

Use multiple CTEs query to return details of venues and statistics of shows held at them

Head office

London

Manchester