Oracle 12c: SQL Joan Casteel ISBN: 978-1-305-25103-8 Chapter 12 Case Study: City Jail Make sure you...
Oracle 12c: SQL Joan Casteel ISBN: 978-1-305-25103-8 Chapter 12 Case Study: City Jail
Make sure you have run the CityJail_8.sql script from Chapter 8.
This script makes all database
objects available for completing this case study.
The city's Crime Analysis unit has submitted the following data
requests. Provide the SQL
statements using subqueries to satisfy the requests. Test the
statements and show execution
results.
Use a sql sub-query statement to answer the following: (Please provide correct answer for questions 6 to 10)
6. List the information on crime charges for each charge that
has had a fine above average
and a sum paid below average.
7. List the names of all criminals who have had any of the crime
code charges involved in
crime ID 10089.
8. Use a correlated subquery to determine which criminals have had
at least one probation
period assigned.
9. List the names of officers who have booked the highest number of
crimes. Note that more
than one officer might be listed.
Note: Use a MERGE statement to satisfy the following request:
10. The criminal data warehouse contains a copy of the CRIMINALS
table that needs to be
updated periodically from the production CRIMINALS table. The data
warehouse table is
named CRIMINALS_DW. Use a single SQL statement to update the data
warehouse
Homework Answers
Add Answer to:
Oracle 12c: SQL Joan
Casteel ISBN: 978-1-305-25103-8
Chapter 12 Case Study: City Jail
Make sure you...
Note: Use the City Jail database created with the CityJail_8.sql script that you ran for the...
Note: Use the City Jail database created with the CityJail_8.sql script that you ran for the Chapter 8 case study. The following list reflects the current data requests from city managers. Provide the SQL statements that satisfy the requests. For each request, include one solution using the traditional method and one using an ANSI JOIN statement. Test the statements and show execution results 4. Create an alphabetical list of all criminals, including criminal ID, name, violent offender status, parole status,...
Oracle 12c SQL - Chapter 5: Case Study: City Jail, Part II Note: You are limited...
Oracle 12c SQL - Chapter 5: Case Study: City Jail, Part II Note: You are limited to using the Oracle Live interface to create solutions for the problems below. SQL Live does not allow as many commands, statements, and symbols as full Oracle SQL. Specifically, the ampersand substitution variable symbol (&) may not be used for any of the solutions below because Oracle Live does not recognize it. Oracle Live is available to use here: https://livesql.oracle.com/apex/f?p=590:1000 Execute the CityJail_5.sql script...
Case Study The City Jail Technologies Department is constructing an application to allow users in the...
Case Study The City Jail Technologies Department is constructing an application to allow users in the Crime Analysis Unit to query data more easily. This system requires creating a number of views, described in the following list. Provide the SQL statement to perform each task and test your views with a query. 1. Create a statement that always returns the names of the three criminals with the highest number of crimes committed. 2. Create a view that includes details for...
Connect to mySQL DBMS, open a SQL script file, navigate to CityJail.sql file and execute it...
Connect to mySQL DBMS, open a SQL script file, navigate to CityJail.sql file and execute it to create tables and populate with the supplied data. If there are any errors in the script fix them. It is your job to find the errors and correct them. This is important. You will need correct data for future exercises in this module. Then answer all questions. Write the answer below each question then take screen shot of the result. This script builds...
If possible please just send the SQL statement. Thank you Each question within deliverable 3 must begin on a new page and be sure to document the question as the title of each item at the top o...
If possible please just send the SQL statement. Thank you
Each question within deliverable 3 must begin on a new page and be sure to document the question as the title of each item at the top of each page. Also, using a 12-point font, include the SQL statement and then provide a screen shot of each query. The screen shots must include both the SQL statement and the results for each item below based on the data entered in...
Answer the following SQL study guide questions... Question 1 (1 point) Unless you assign a column...
Answer the following SQL study guide
questions...
Question 1 (1 point) Unless you assign a column name in the base table. the column name in the result set is the same as the 1) unique syntax 2) column alias 3) qualification 4) all of the above Question 2 (1 point) Which clause specifies the number of rows that should be skipped before rows are returned from the result set? 1) FETCH 2) OFFSET 3) FIRST 4) NEXT Question 3 (1...
Question 1.Write a SQL statement for each of the following questions based on the above tables...
Question 1.Write a SQL statement for each of the following
questions based on the above tables (50 Points).
1) Create “Enrollment” table.
2) Change the credits of “Database” course from 2 to 3.
3) List the course titles (not Course_No) and grades John Doe
had taken.
4) Calculate average of Café Balance, and name new field as
Average Balance.
5) Create a roster for “Database” course (list student ID, last
name, first name, major, and status for each student enrolled...
can someone solve these quick please and thank you! sql chapter 4 Query #1: List the...
can someone solve these quick
please and thank you!
sql chapter 4
Query #1: List the company name, contact name, contact title and
the phone number for customers who HAVE NOT put in an order. Use an
outer join.
Query #2: Create a listing displaying the employee first name,
last name and the full name (First name space Last Name) of the
person they report to (Supervisor). This is a self-join. If an
employee does not report to anyone then...
Chapter 8 Python Case study Baseball Team Manager For this case study, you’ll use the programming...
Chapter 8
Python Case study Baseball Team Manager
For this case study,
you’ll use the programming skills that you learn in Murach’s
Python Programming to develop a program that helps a
person manage a baseball team. This program stores the data for
each player on the team, and it also lets the manager set a
starting lineup for each game. After you read chapter 2, you can
develop a simple program that calculates the batting average for a
player. Then,...
148 Chapter 8 Refining the Continuum of Care Case Study Table 8.3 is a set of...
148 Chapter 8 Refining the Continuum of Care Case Study Table 8.3 is a set of admission criteria for medical and surgical admissions. Figure 8.10 shows an example of one patient's history and physical report. Use the patient history and physical report (figure 8.10) and the admission criteria (table 8.3) to determine whether this patient meets the criteria fo admission to the hospital. The patient must meet at least one criterion in severity of illness and one crite rion in...
If possible please just send the SQL statement. Thank you
Each question within deliverable 3 must begin on a new page and be sure to document the question as the title of each item at the top of each page. Also, using a 12-point font, include the SQL statement and then provide a screen shot of each query. The screen shots must include both the SQL statement and the results for each item below based on the data entered in...
Answer the following SQL study guide
questions...
Question 1 (1 point) Unless you assign a column name in the base table. the column name in the result set is the same as the 1) unique syntax 2) column alias 3) qualification 4) all of the above Question 2 (1 point) Which clause specifies the number of rows that should be skipped before rows are returned from the result set? 1) FETCH 2) OFFSET 3) FIRST 4) NEXT Question 3 (1...
Question 1.Write a SQL statement for each of the following
questions based on the above tables (50 Points).
1) Create “Enrollment” table.
2) Change the credits of “Database” course from 2 to 3.
3) List the course titles (not Course_No) and grades John Doe
had taken.
4) Calculate average of Café Balance, and name new field as
Average Balance.
5) Create a roster for “Database” course (list student ID, last
name, first name, major, and status for each student enrolled...
can someone solve these quick
please and thank you!
sql chapter 4
Query #1: List the company name, contact name, contact title and
the phone number for customers who HAVE NOT put in an order. Use an
outer join.
Query #2: Create a listing displaying the employee first name,
last name and the full name (First name space Last Name) of the
person they report to (Supervisor). This is a self-join. If an
employee does not report to anyone then...
Chapter 8
Python Case study Baseball Team Manager
For this case study,
you’ll use the programming skills that you learn in Murach’s
Python Programming to develop a program that helps a
person manage a baseball team. This program stores the data for
each player on the team, and it also lets the manager set a
starting lineup for each game. After you read chapter 2, you can
develop a simple program that calculates the batting average for a
player. Then,...
148 Chapter 8 Refining the Continuum of Care Case Study Table 8.3 is a set of admission criteria for medical and surgical admissions. Figure 8.10 shows an example of one patient's history and physical report. Use the patient history and physical report (figure 8.10) and the admission criteria (table 8.3) to determine whether this patient meets the criteria fo admission to the hospital. The patient must meet at least one criterion in severity of illness and one crite rion in...
Most questions answered within 3 hours.
-
Where is the error in this code sequence?
String s1 = "Hello";
String s2 = "ello";...
asked 10 months ago -
Financial data for Joel de Paris, Inc., for last year
follow:
Joel de Paris, Inc.
Balance...
asked 10 months ago -
Consider this reaction:
Al2(SO4)3 (aq)+ BaCl3
(aq) Al2Cl6 (aq)- +
3BaSO4(s) . What is the...
asked 10 months ago -
Suppose that Savneet is considering increasing her
recent random sample from 20 car rentals to 40...
asked 10 months ago -
Trucks arrive at an unloading terminal at an average rate of 120
per hour.
Trucks arrive...
asked 10 months ago -
Why are methanol and ethanol completely soluble in water while
octanol is not very little soluble....
asked 10 months ago -
A facilities manager at a university reads in a research report
that the mean amount of...
asked 10 months ago -
When the CuSO4 is rehydrated by adding water to the anhydrous
compound, is this an endothermic...
asked 10 months ago -
A ray of sunlight is passing from diamond into crown glass; the
angle of incidence is...
asked 10 months ago -
A block of mass 0.249 kg is placed on top of a light, vertical
spring of...
asked 10 months ago -
how do the kidneys compensate in the presences of acidosis
a) trigger hyperventilate
b) reserve acid...
asked 10 months ago -
Question 501 pts
The rental rate of capital to the firm increases. Which of the
following...
asked 10 months ago