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 (35 exercises)
Topic ==>	Basic joins (2 exercises)
Level ==>	Average difficulty
Subject ==>	PostgreSQL training

Use joins to link music albums to their genres and then apply criteria to the resulting list of rows

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 title and us_sales_(m) fields from the album table. Add a WHERE clause to show only gold or platinum certified albums (that is, those with sales of at least 0.5 million but less than 10 million):

A few of the results you should see.

Modify the FROM clause to include the subgenre table. Use an inner join to join it to the album table using the subgenre_ID columns. You can see a reminder of the join syntax below:

FROM

Table1 AS t1

INNER JOIN Table2 AS t2 ON t1.Column = t2.Column

Add the Subgenre column to the SELECT list and run the query.

That's better - now we know what to avoid.

Modify the FROM clause again to include the Genre table. Join it to the Subgenre table using the Genre_ID columns.

The relationships as they appear in ERD view.

Add a condition to the WHERE clause to show only albums whose Genre is Metal. Sort the results in ascending order of Title and run the query.

An anti-playlist?

Save the script as Noisy music.sql and 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 joins to link music albums to their genres and then apply criteria to the resulting list of rows

Head office

London

Manchester