Read our blogs, tips and tutorials
Try our exercises or test your skills
Watch our tutorial videos or shorts
Take a self-paced course
Read our recent newsletters
License our courseware
Book expert consultancy
Buy our publications
Get help in using our site
545 attributed reviews in the last 3 years
Refreshingly small course sizes
Outstandingly good courseware
Whizzy online classrooms
Wise Owl trainers only (no freelancers)
Almost no cancellations
We have genuine integrity
We invoice after training
Review 30+ years of Wise Owl
View our top 100 clients
Search our website
We also send out useful tips in a monthly email newsletter ...
Software ==> | SQL (203 exercises) |
Topic ==> | Simple Queries (7 exercises) |
Level ==> | Average difficulty |
Subject ==> | SQL training |
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.
This will generate the database that you'll need to use in order to do this exercise (remember to refresh your list of databases to see it!). Note that any files provided are only to be used for exercises published on this website, and may not be reused or distributed in any form without the prior written permission of Wise Owl.
If you don't have a copy of the Music_01 database, follow the instructions at the following link to create it: www.wiseowl.co.uk/sql/exercises/standard/using-sql-server/5257/
Create a query to select the columns shown in the image below from the Album table. Sort the results in descending order of US sales.
If you're curious about who made the top selling album you could copy the Wiki URL into a web browser.
Modify the query so that it returns only the top five best-selling albums. You can use the SELECT TOP n syntax to specify the number of rows you want to return.
These are the results you should see.
The fifth album in the list sold 18 million copies. At the moment we can't see if any other albums have the same sales figures. Modify the query so that it returns any additional albums whose sales are tied with the fifth one. You can use the WITH TIES syntax to do this.
You should return one more row than the five you've requested.
In the same script, write a new query to return the three most recently released albums. Make sure to include any additional albums that were released on the same date as the third one.
You should return more than the three results you request.
Write another query in the same script which shows the ten longest albums. The image below shows the last few rows you should return:
You'll need to sort by two columns to get the results in the correct order.
Create another query in the same script to show the ten shortest albums.
The first few results you should see.
Save the query as The Long and Short of it.sql and close it down.
You can find other training resources for the subject of this exercise here:
Kingsmoor House
Railway Street
GLOSSOP
SK13 2AA
Landmark Offices
99 Bishopsgate
LONDON
EC2M 3XD
Holiday Inn
25 Aytoun Street
MANCHESTER
M1 3AE
© Wise Owl Business Solutions Ltd 2024. All Rights Reserved.