USE MS SQL SERVER! make sure queries run with dummy data.

In this assignment, you will implement several queries and triggers in SQL for the Yelp Edmonton database created in Assignment $3.$ Here are once more the database tables:

$```$

business $=($business id$,$ name, address, city, postal$\_$code, stars, review$\_$count$)$

checkin $=($checkin id$,$ business$\_$id$,$ date$)$

tip $=($tip id$,$ user$\_$id$,$ business$\_$id$,$ date, compliment$\_$count$)$

review $=($review id$,$ user$\_$id$,$ business$\_$id$,$ stars, useful, funny, cool, date$)$

user$\_$yelp $=($user id$,$ name, review$\_$count, yelping$\_$since, useful, funny, cool, fans, average$\_$stars$)$

friendship $=($user id$,$ friend$)$

$```$

Assignment $4\mathrm{.}1(50$ marks$)$

Formulate the following queries in SQL Server SQL:

a$)$ Select the name$($s$)$ of the user$($s$)$ who have received the highest number of compliments on their tips. $(12$ marks$)$

Note: You need to count the compliments for each user and identify the user$($s$)$ with the maximum compliments.

b$)$ Select the names of businesses that have received more than five reviews and have an average rating of $4$ stars or higher. Include only businesses that are located in 'Edmonton'. $(12$ marks$)$

c$)$ Select the average number of stars of all 'Best Buy' locations that were reviewed by a user named 'John' and have any review $($not necessarily John's review$)$ that was voted as 'useful' atmost thrice.

$(13$ marks$)$

Note: The result must be a decimal number representing the average stars of all Best Buy stores that meet the criteria.

d$)$ Select the number of businesses that have received reviews from two friends, where one friend gave a review marked as "useful" and the other friend gave a review marked as "funny." $(13$ marks$)$

Assignment $4\mathrm{.}2(50$ marks$)$

Formulate the following integrity constraints as triggers in SQL Server SQL:

a$)$ When a new tip is registered, update the total number of tips for the corresponding

business. If a tip is submitted by a user who has already tipped for that business, only the

latest tip from that user should be considered for counting. Use the tip table's existing

columns to derive the count. $(35$ marks$)$

b$)$ Implement a rule that users can only leave a review for a business if they have at least one

friend who has also reviewed that business. This rule should only apply to new reviews and

should not affect any existing reviews already recorded in the database. $(15$ marks$)$