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

Question

. 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, dinne) 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!). (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 I have compared to Ohio OH and

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 #
• Subjects

---

---

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