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

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

Software ==>	Postgres SQL (35 exercises)
Topic ==>	Views (1 exercise)
Level ==>	Average difficulty
Subject ==>	PostgreSQL training

Create a view to show a list of number 1 albums and fields from other tables

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 following fields for albums which peaked at number 1 in the US Billboard 200 chart.

You'll need to join the artist, album and record_label tables.

Add a CREATE VIEW statement to the script to create a view called Number_1_Albums. After executing the script, make sure your view appears in the Object Explorer window.

You might need to refresh the Views folder to see this.

Create a query which joins your view to the tour table using the album_id fields. Select the fields shown below:

A list of tours associated with number 1 albums, sorted into tour start date order.

In your script to generate your view, change the first command to CREATE OR REPLACE VIEW and add the US_sales_(m) field to the results. Execute the script to update the view.

You will probably also need to drop the view first, as you're changing the columns it contains. To do this use the DROP VIEW IF EXISTS command.

Add another a query based on your view to show the following statistics, using the column you've just added:

Group the data by the Artist field, and also sort by this field.

Save your script as Views are cool.sql and close it down.

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

PostgreSQL training

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

Create a view to show a list of number 1 albums and fields from other tables

Head office

London

Manchester