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 (18 exercises)
Topic ==>	Calculations (8 exercises)
Level ==>	Average difficulty
Subject ==>	PostgreSQL training

Use CASE expressions to categorise venues by capacity, era and stattus

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 show the venue and capacity columns from the venue table (in venue name order), and add a calculated column called venue_size which describes the venues according to the table below:

Value of Capacity	Description
NULL	Unknown
Less than 1000	Intimate
Less than 10000	Small
Less than 50000	Medium
Less than 100000	Large
Everything else	Enormous

Here's what this should show:

The first few venues in name order, with their capacity descriptions.

Write another query to show the venue and opening_date columns from the venue table (sorted alphabetically by venue name again). Add a calculated column called venue_era which describes venues according to the table below:

Value of Opening_date is	Description
NULL	Unknown
Before the 1st of January 1801	Ancient
Before the 1st of January 1901	19th Century
Before the 1st of January 2001	20th Century
Everything else	21st Century

Here's what this should show:

Each venue should have its era next to it.

Finally, write a query to show the venue, opening_date, closing_date and demolition_date columns from the venue table (again sorted alphabetically by venue name). Add a calculated column called venue_status to categorise venues according to the table below:

Condition	Description
The *Demolition_date* column has a value	Demolished
The *Closing_date* column has a value	Closed but undemolished
Everything else	Still open

Add criteria to pick ouit only those venues whose names contain the letters UM (any case) and whose names start with the letters AL (again, in any case):

You should see these 8 vvenues.

Save the scripting as Capacity, era and status.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

Use CASE expressions to categorise venues by capacity, era and stattus

Head office

London

Manchester