Assignment #$1$ Subqueries

Due Date: As per dropbox

Purpose: The purpose of this assignment is to help you:

$$ SQL and subqueries

Instructions: Be sure to read the following general instructions carefully:

This assignment should be completed individually by all the students. Submit your solution through the dropbox.

Assignment $1$:

Your files must be in sql script format $(.$txt for Turnitin$)$ so that they can be copied and run in SQL Developer. Marks will be deducted.

Using the Just Lee and City Jail database.

Use an sql sub$-$query statement to answer the following:

The city$$s Crime Analysis unit has submitted the following data requests. Provide the SQL statements using subqueries to satisfy the requests.

$1.$ List the shipping city and state for the order that had the shortest shipping delay.

$2.$ Determine which author or authors wrote the books least frequently purchased by customers of JustLee Books.

$3.$ Determine which customers placed orders for the most expensive book $($in terms of regular retail price$)$ carried by JustLee Books.

City jail

$4.$ List the names of all criminals who have had any of the crime code charges involved in crime ID $10088.$

$5.$ List the information on crime charges for each charge that has had a fine below average and a sum paid above average.

$6.$ Use a correlated subquery to determine which criminals did not have had probation period assigned.

HINT:

The following memo was used to create an initial database design $($E$-$R model$)$ for the City Jail that indicates entities, attributes $($columns$),$ primary keys, and relationships.

MEMO

To: Database Consultant

From: City Jail Information Director

Subject: Establishing a Crime$-$Tracking Database System

It was a pleasure meeting with you last week. I look forward to working with your company to create a much$-$needed crime$-$tracking system. As you requested, our project group has outlined the crime$-$tracking data needs we anticipate. Our goal is to simplify the process of tracking criminal activity and provide a more efficient mechanism for data analysis and reporting. Please review the data needs outlined below and contact me with any questions.

Criminals: name, address, phone number, violent offender status $($yes$/$no$),$ probation status $($yes$/$no$),$ and aliases

Crimes: classification $($felony$,$ misdemeanor, other$),$ date charged, appeal status $($closed$,$ can appeal, in appeal$),$ hearing date, appeal cutoff date $($always $60$ days after the hearing date$),$ arresting officers $($can be more than one officer$),$ crime codes $($such as burglary, forgery, assault; hundreds of codes exist$),$ amount of fine, court fee, amount paid, payment due date, and charge status $($pending$,$ guilty, not guilty$).$

Sentencing: start date, end date, number of violations $($such as not reporting to probation officer$),$ and type of sentence $($ jail period, house arrest, probation$).$

Appeals: appeal filing date, appeal hearing date, status $($pending$,$ approved, and disapproved$).$

Note: Each crime case can be appealed up to three times.

Police officers: name, precinct, badge number, phone contact, status $($active$/$inactive$)$

Additional notes:

$$ A single crime can involve multiple crime charges, such as burglary and assault.

$$ Criminals can be assigned multiple sentences. For example, a criminal might be required to serve a jail sentence followed by a period of probation.

Answer each or the questions with an sql statement that contains at least one subquery.

Hint: Use subqueries, and work $$inside out$$ toward the result; that is$,$ retrieve the

employee number of N$.$ Smith, search for the codes of all courses he ever taught, and so on$.$

Marks are and will entirely based only on correct result of code submitted.