This page has 0 threads | Add post

Exercises

Finding our exercises useful? Please consider helping us to share more!

Software ==>	SQL (203 exercises)
Topic ==>	Creating tables (7 exercises)
Level ==>	Average difficulty
Subject ==>	SQL training

Write a SQL script to create a table to store movies and use a foreign key to connect it to a table of albums.

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.

Before you can do this exercise, you'll need to download and unzip this file .

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/

Open the Create Movie Table.sql file in the folder shown above. You'll find a script which deletes any existing copy of a table called Movie.

Add code to create a table called Movie with the following columns:

A table and columns in SQL Server Management Studio

Make the Movie_ID column an IDENTITY column.

Include a foreign key constraint called FK_AlbumID so that the Soundtrack_Album_ID column references the Album_ID column of the Album table.

A foreign key column in the Object Explorer of SQL Server Management Studio

You may need to refresh the Object Explorer to see the updated column.

Include a check constraint called CHK_Score to restrict the Score column to a value between 1 and 10.

Keys and constraints in the Object Explorer in SQL Server Management Studio

You can see the keys and constraints in the Object Explorer.

Write an INSERT statement to add the following movies (or as many as you can be bothered with) to the new table:

A list of movies produced by a SQL Server query

The Movie_ID values will be created automatically. Feel free to assign your own scores.

Write a SELECT statement to return the following columns from the Movie, Album and Artist tables where the Album_type_ID equals 5: