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 t. 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, dinn 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 proof).

Explanation / Answer

Solution:

The first two subparts have been answered as per Chegg guidelines, please repost others.

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.