MIS 3220 - Homework 3

Criminal History Database SQL Queries

Student Information

First Name *

Last Name *

Your name will be included in the downloaded file for identification purposes.

Schema

Each database has some “schema”—the tables and columns into which the data is organized. In criminal_history.db you'll find a single table, CHistory. In the CHistory table, you'll find the following columns:

id: Primary key identifier

participantId: Unique identifier for each participant

firstName: First name of the participant

lastName: Last name of the participant

birthDate: Date of birth of the participant

adult: Whether the participant is an adult

startDate: Program start date

endDate: Program end date

status: Current status (Graduated, Active, etc.)

chargeDate: Date of the criminal charge

offense: Type of offense committed

class: Classification of the charge (Felony, Misdemeanor, etc.)

verdict: Court verdict for the charge

afterStart: Whether charge occurred after program start

beforeEnd: Whether charge occurred before program end

duringMhc: Whether charge occurred during MHC program

yearsBefore: Years before program start

yearsAfter: Years after program start

year19, year20, year21, year22: Participation in specific years

fy23, fy24, fy25: Participation in fiscal years

Specification

For each of the following questions, you should write a single SQL query that outputs the results specified by each problem. Your response must take the form of a single SQL query. Each query should return only the data necessary to answer the question: if the problem only asks you to output the participant IDs, for example, then your query should not also output each participant's name.

Note: Test your queries in the interface below, then click “Download SQL File” to get a .sql file with all your answers.

Your answers are automatically saved as you type and will be restored if you refresh the page or return later.

Database Status: Loading...

Question 1

Write a SQL query to list the participant IDs of all participants.

SQL Query:

Question 2

List the first name, last name, and status of each participant.

SQL Query:

Question 3

Find the birth date for participant 'PARTICIPANT_004'.

SQL Query:

Question 4

Write a query to find the participant IDs of participants who do not have a verdict recorded.

SQL Query:

Question 5

Find the first and last names of participants who have a 'Graduated' status.

SQL Query:

Question 6

List the participant IDs of participants who were charged between '2021-01-01' and '2022-12-31'.

SQL Query:

Question 7

Write a SQL query to list the first name, last name, and offense of all participants who have been charged with 'DWI'.

SQL Query:

Question 8

Write a query to find all participants who have a 'Felony' class charge.

SQL Query:

Question 9

Write a SQL query to list the participantId, firstName, lastName, and chargeDate of all participants. Order the results by chargeDate, from earliest to latest.

SQL Query:

Question 10

List the first and last names of participants who have 'Dismissed' verdicts, in alphabetical order by last name.

SQL Query:

Question 11

Find all participants who have an offense that contains the word 'Drug' (case insensitive).

SQL Query:

Question 12

Write a query to find participants who started the program (startDate) in 2021.

SQL Query:

Please enter your first and last name before downloading