SQL BASICS
Training Assignments
Program Code BSQL
Version 3.1
Effective Date 01/11/2016
Hanoi, 11/2016
Training Assignments SQL BASICS Issue/Revision: x/y
RECORD OF CHANGES
*A - Added M - Modified D - Deleted
Date Changes A* Contents Version
M, D
14-Oct-2016 Create A Add the new assignments. v1.0
14-Oct-2018 Update M Template. v1.1
01-Jun-2019 Update M Update Objective v1.2
17e-BM/DT/FSOFT v1/1 Internal use 2/5
Training Assignments SQL BASICS Issue/Revision: x/y
Contents
For the following assignments:...........................................................................................................4
Day 2. Lesson 2: SQL Basic..............................................................................................................4
Assignment 2_Opt3: Movie Management......................................................................................4
17e-BM/DT/FSOFT v1/1 Internal use 3/5
Training Assignments SQL BASICS Issue/Revision: x/y
CODE: BSQL_Assignment2_Opt3
TYPE: n/a
LOC: n/a
DURATION: 180 MINUTES
For the following assignments:
• Print out respectively the screenshots to show the query results.
• Pack screenshots and SQL scripts or your answers into the zip file named
BSQL_Assignment<i>_AccountName.zip (for instance: BSQL_Assignment1_NamNT.zip) then handle to
the evaluator via email (XYZ@fsoft.com.vn ) or follow the guidance of the class admin.
Day 2. Lesson 2: SQL Basic
Assignment 2_Opt3: Movie Management
Barem: Q1 - 40%, Q2.a – 10%, Q2.b – 10%, Q3.each subquestion - 10%.
Objective: H5SD - SQL skills
Problem Description:
Building a Movie Collection database to store information about movies.
Questions to answer:
Q1: Create your tables
a. Create a table called Movie to store information about movies. Add columns in your table for movie
name, duration, genre, director, amount of money made at the box office and comments.
• Make sure you one of your columns works as a PRIMARY KEY.
• Genre: accepts value range from 1 to 8 only (1: Action, 2: Adventure, 3: Comedy, 4: Crime
(gangster), 5: Dramas, 6: Horror, 7: Musical/dance, 8: War)
• Duration: must be greater than or equal 1 hours
b. Create another table called Actor to store information about actors. Just like you did with Movie, add
several columns to store actor data for the actor's name, age, average movie salary, and Nationality.
Again, make sure there is a PRIMARY KEY in your table.
c. Create a final table called ActedIn to store information about which movies certain actors have acted
in. Think carefully about what the columns of this table should be. This table should make use of
FOREIGN KEYS.
Create above tables with the most appropriate/economic field/column constraints & types, all fields are
mandatory except Comments field.
Q2. Polulate tables
a. Add an ImageLink field to Movie table and make sure that the database will not allow the value for
ImageLink to be inserted into a new row if that value has already been used in another row.
b. Populate your tables with some data using the INSERT statement. Make sure you have at least 5
tuples per table.
You accidentally mis-typed one of the actors' names. Fix your typo by using an UPDATE statement.
Q3. Query tables
c. Write a query to retrieve all the data in the Actor table for actors that are older than 50.
17e-BM/DT/FSOFT v1/1 Internal use 4/5
Training Assignments SQL BASICS Issue/Revision: x/y
d. Write a query to retrieve all actor names and average salaries from ACTOR and sort the results by
average salary.
e. Using an actor name from your table, write a query to retrieve the names of all the movies that actor
has acted in.
f. Write a query to retrieve the names of all the action movies that amount of actor be greater than 3
Estimated Time to complete:180 mins.
-- THE END --
17e-BM/DT/FSOFT v1/1 Internal use 5/5
