Please answer all parts. In SQL TABLES ARE IN SQL 1. List criminals (ID and Name

Question

Please answer all parts. In SQL

TABLES ARE IN SQL

1. List criminals (ID and Name) who have multiple sentences assigned. (You will need to join criminals to sentences and use the Having clause to accomplish.

2. Write a query that will show charge status, the total of each charge status, the average of fine amount plus court fee, and the standard deviation of fine amount plus court fees. Group the results by charge status. Add appropriate aliases for the average total charge and standard deviation columns.

3. Write a query that will show charge status, the variance of fine amount plus court fee, and the lowest and highest total charge in each charge status. Group the results by charge status. Add appropriate aliases for the average total charge and standard deviation columns.

4. List the total number of charges by crime classification and charge status. You will need to join 2 tables to accomplish this. Include a grand total in the results.

5. Do the same query above, but this time add syntax that will allow you to provide a overall total aggregation.

Explanation / Answer

I am guessing the table schemas and making the queries. It would be better and much more accurate if you post the table schemas along with some sample data.

1)
select c.id, c.name from criminals c inner join sentences s on c.id =s.id group by cd.id, c.name having count(*) >1

2)
Select charge_status, sum(charges), avg(fine) as average_fine, stddev(fine) as stddev_fine from charges group by charge_status

3) Need table schemas atleast. Data would help more in understanding the problem.

4) select cc.class, cc.status from criminals c inner join class cc on c.id=cc.id group by class, status

5) Need table schemas to understand better.

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.