IS3280 Data Management – SQL Name:______________________________________________________ Use the tables listed at the end of this document to answer the following questions. Please submit both the...

IS3280 Data Management – SQL

Name:______________________________________________________

Use the tables listed at the end of this document to answer

the following questions. Please submit

both the question and the SQL statement for that question. You may simply edit

this document by adding your answers below each question.

There are 15 questions – Questions 1-8 are worth 3 pts;

questions 9-15 are worth 4 pts.

SQL Commands

After each question, construct the SQL statement that

answers the question. When specific

attributes are not asked for, you may choose which attributes you wish to display.

Part I: Constructing

SQL Commands

After each question, construct the SQL statement that

answers the question.

1) List all

the information available about convicts, sort your results by last name (A-Z

order).

2) How much

does the heaviest convict weigh?

3) What are

the make of the vehicles?Make sure your results do not contain any duplicates.

4) How many

officers are there by their rank?

5) On what

date or dates did convicts escape while they were being transported?

6) List the

corrections officers (Last name and First name) whose Last name starts with the

letter J.

7) Which

convicts (by name) have a transport record?

8) Provide

me a list of the convicts whose last names are Brown, Grey, and White [For this

you must use the IN keyword].

9) List the

number of convicts by height. I would

like this list to show those whose heights are more than 67 inches.

10) List the

officers’ namesand their rank,who are either waiting or in route to transport a

convict.

11) List the

names of the convicts who were inroute on 03/25/2015, weighing less than200

pounds.

12) Which

convict (first and last name) did Officer Banks transport in the blue van?

(Provide the SQL query and the answer.)

13) List the

names of the convicts and their status who have been transported and also the

names of the convicts who have not been transported.

14) List the

names of the convicts that were transported, the names of the officer who

transported them, the make and model of the vehicle they were transported in

and the date they were transported.

15) Create a

view that lists all sergeants. Change

the output column name to something relevant.

Data Tables

ER Diagram/Schema:

Tables with sample fictitious data:

CONVICT VEHICLE

OFFICER TRANSPORT