Please complete the following setup tasks before attempting the assignment Conne

Question

Please complete the following setup tasks before attempting the assignment

Connect to MySQL through the command line prompt to DO THIS ASSIGNMENT

Next, please answer the questions:

1) How many total rows are in the data set?.

2) Write the SQL that returns just the name, inspection_type, score, and violation for all restaurants that had a score less than 50.

3) How many rows of restaurants had a score of at least 60 and had “High Risk” risk categories for their inspections? What was the lowest? Include the SQL

4) How many unique restaurants had a violation that mentioned “vermin”? Include the SQL.

5) How many unique violations did “King Ling” have? Include the SQL for your solution.

------ - - -- - - -- -- - - ------------------------------------ ---------- -------------------- ------- ---- --- --- -- - --------------

use This script BELOW to answer above questions:

Create the database

Create the schema (a single table)

Type “show tables” (this will return a single table, called business)

Type “desc business” (this will show the attribute/column types)

The instructor will go over the meaning of each column

Explanation / Answer

NOTE: If you have any issues with the queries given, please comment and i will revert back within 24 hours.

1) How many total rows are in the data set?
Ans)
select
count(*) as total
from
business;

2) Write the SQL that returns just the name, inspection_type, score, and violation for all restaurants that had a score less than 50.
Ans)
select
name, inspection_type, score, violation
from
business
where score < 50;

3) How many rows of restaurants had a score of at least 60 and had “High Risk” risk categories for their inspections?
Ans)
select
count(*) as number_of_restaurants
from
business
where
score >= 60 and risk_category = 'High Risk';

What was the lowest? Include the SQL
Ans)
select
min(score) as Lowest_score
from
business
where
score >= 60 and risk_category = 'High Risk';

4) How many unique restaurants had a violation that mentioned “vermin”? Include the SQL.
Ans)
select
distinct name
from
business
where
violation like '%vermin%';

For getting unique count, we can use query like:
select
count(distinct name)
from
business
where
violation like '%vermin%';

5) How many unique violations did “King Ling” have? Include the SQL for your solution.
Ans)
select
distinct violation
from
business
where name = 'King Ling';

For getting unique count, we can use query like:
select
count(distinct violation)
from
business
where name = 'King Ling';

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

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