4. Consider a review website with the following schema, where underlined values

Question

4. Consider a review website with the following schema, where underlined values indicate the primary key. Provide SELECT statements corresponding to the questions below Businesses (ID:int, Name:string, City:string, state:varchar (2)) * Each business has a name, unique ID, and is located in a specific city and state. Users(ID:int, Name: string, Followers:int) * Each users has a name, unique ID, and a number of followers Reviews (bID:int, uID:int, Stars:int, Date:date, useful:int, funny:int, cool:int) * Each user gives a review to a business by assigning a number of stars to it. The review is made on a specific date and a number of others may have found it useful, funny or cool BusinessTypes (bID:int, Type:string) * Each business is of a certain type (e.g., restaurant, construction, dinner SUGGESTION: It could be useful, but not necessary, to create the four tables in MySQL and populate them with data to test your queries on. Be careful to create useful data that isn't misleading since testing alone does not guarantee correctness (evidence f proof! (a) (2 points) How many distinct business types are there? (b) (2 points) How many businesses are in Indiana (IN)? (c) (3 points) How many more or less businesses does Indiana (IN) have compared to Ohio (OH) and Alaska (AK) combined? (d) (8 points) Which users have visited more than 10 distinct states? The output should be ordered (decreasing) by the number of states users visited, where any ties are broken by the user ID (increasing order). Each outputted user should show the ID, name and number of visited states (e) (6 points) How many businesses in Indiana have the word "corn" in their name, but are not identi- fied as a "food" business? Your result should be ordered by business ID and give the ID and name only (f) (10 points) What are the top 5 businesses in West Lafayette, Indiana for dinner (i.e., business type is "dinner")? For this question you will use the average star rating as a measure of quality, where higher number of stars is better. Sort the results by their average rating, breaking any ties by the number of reviews received (decreasing order), then by business ID (increasing order). The output should contain, for each business its ID, name, average number of stars and total number of reviews (g) (10 points) Which Indiana businesses have more than 20 reviews, and all of them are 5 stars? Order the output by the number of reviews received (decreasing order), and business ID (increasing order). Each business displayed should show its ID, name and number of reviews

Explanation / Answer

Solution:

The first four subparts are done as per Chegg guidelines, please repost others.  

1)

a)

SELECT COUNT(DISTINCT bID) FROM BusinessType

b)

SELECT COUNT(DISTINCT ID) FROM Business WHERE City= 'Indiana'

c)

SELECT DIFFERENCE(SELECT COUNT(DISTINCT ID) FROM Business WHERE City= 'Indiana', SELECT COUNT(DISTINCT ID) FROM Business WHERE City= 'Ohio') FROM Business

d)

SELECT User.iD, Businesses.state, COUNT(Businesses.state) FROM Users INNER JOIN Businesses ON Users.ID= Businesses.ID HIAVING COUNT(DISTINCT(Business.states))>10 ORDER BY COUNTBusiness.states), Users.ID

Explanation:

The having clause is used to extract the users who have visited more than 10 states.

e)

SELECT bID, name FROM Businesses INNER JOIN BusinesType ON BusinessType.bID= Businesses.ID WHERE Businesses.name LIKE '%corn%' AND state= 'Indiana' AND BusinessType.Type != 'food' ORDER BY Business.ID

Explanation:

The above query uses LIKE to look for the word corn within the string of a name and the final output is ordered by the ID of the business.

I hope this helps if you find any problem. Please comment below. Don't forget to give a thumbs up if you liked it. :)

Related Questions

Navigate

• Browse (All)
• Browse 4
• Subjects

---

---

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