Using the following database table description, create the following SQL queries
ID: 3703407 • Letter: U
Question
Using the following database table description, create the following SQL queries
1) Find the names, types and emails of the raters that provide the most diverse ratings. Display this information together with the restaurants names and the ratings. For example, Jane Doe may have rated the Food at the Imperial Palace restaurant as a 1 on 1 January 2015, as a 5 on 15 January 2015, and a 3 on 4 February 2015. Clearly, she changes her mind quite often.
2)Find the total number of rating for each restaurant, for each rater. That is, the data should be grouped by the restaurant, the specific raters and the numeric ratings they have received.
3)Display the details of the restaurants that have not been rated in January 2015. That is, you should display the name of the restaurant together with the phone number and the type of food.
4)Find the names and opening dates of the restaurants that obtained Staff rating that is lower than any rating given by rater X. Order your results by the dates of the ratings. (Here, X refers to any rater of your choice.)
5) List the details of the Type Y restaurants that obtained the highest Food rating. Display the restaurant name together with the name(s) of the rater(s) who gave these ratings. (Here, Type Y refers to any restaurant type of your choice, e.g. Indian or Burger.)
6)Provide a query to determine whether Type Y restaurants are “more popular” than other restaurants. (Here, Type Y refers to any restaurant type of your choice, e.g. Indian or Burger.) Yes, this query is open to your own interpretation!
Rater(UserlD, email, name, join-date, type, reputation,.) The join-date is used to show when this rater first joined the website. The name field corresponds to an alias such as SuperSizeMe. Type refers to the type of rater (blog, online, food critic) and reputation takes a value between 1 and 5. The value of this field is based on the number of people who found this rater's opinion helpful, and the default value is 1 (lowest). Rating(UserlD Date, Price, Food, Mood, Staff, Comments, ..., RestaurantID) e Price, Food, Mood and Staff attributes may take a value between 1 (low) to 5 (high). comments field is reserved for free text and will be used, in future, for sentiment analysis. Note that UserlD and RestaurantID are foreign keys. Restaurant RestaurantID, Name, Type, URL, ...) This relation contains general information about a restaurant and is useful in the case where a restaurant chain has many locations. The type attribute contains details about the cuisine, such as Italian, Indian, Middle Eastern, and so on. Location(LocationlD, first-open-date, manager-name, phone-number, street-address, hour-open, hour-close , ..., RestaurantlD This relation contains the location-specific data, such as the manager's details, the phone number, the address, and so on. Note that RestaurantID is the foreign key. This design assumes that the restaurant opens and closes at the same time every day; you may modify this design if you wish. Menultem(ltemID, name, type, category, description, price, ..., RestaurantID)_ Here we include the item name, as on the menu, the category (starter, main, desert) as well as the type (food or beverage). RestaurantiD is the foreign key Ratingltem(UserlD, Date, ItemID, rating, comment, ....) A rater may explicitly select the menu item, and add a specific rating between 1 (low) to 5 (high) and a free text comment. All menu items should be selected from a listExplanation / Answer
1)
SELECT
TMP.name,
TMP.type,
TMP.email,
TMP.Rname
MAX(RatingDistance) as MaxRatingDistance
FROM (SELECT
P1.name,
P1.type,
P1.email,
@(MAX(R1.Rating) - MIN(R1.Rating)) AS RatingDistance,
Q1.Name AS Rname
FROM Rating R1
INNER JOIN Restaurant Q1 ON Q1.RestaurantID = R1. RestaurantID
INNER JOIN Rater P1 ON P1.UserID = PM1.UserID
GROUP BY P1.name, Q1.Name) AS TMP
GROUP BY TMP.name, TMP.RName
ORDER BY MaxRatingDistance DESC;
2)
SELECT
PM.UserID,
PM.RestaurantID,
count(PM.Food)
FROM Rating PM
GROUP BY (PM.RestaurantID, PM.UserID);
3)
SELECT
M.Name,
M.Type
FROM Restaurant M
WHERE M.RestaurantID IN (SELECT M2.RestaurantID
FROM Restaurant M2, Rating PM2
WHERE M2.RestaurantID = PM2. RestaurantID
AND '2016-01-01' > PM2.Date);
4)
SELECT
P.name,
Q.first-open-date
FROM Restaurant P
INNER JOIN Location Q ON P.RestaurantID = P.RestaurantID
INNER JOIN Rating R ON P. RestaurantID = R. RestaurantID
WHERE R.Staff <
(SELECT R1.Price
FROM Rating R1
INNER JOIN Rater P1 ON P1.UserID = R1.UserID
WHERE P1.name = 'X'
AND R1.RestaurantID = R.RestaurantID)
ORDER BY R.Date;
SELECT
TMP.name,
TMP.type,
TMP.email,
TMP.Rname
MAX(RatingDistance) as MaxRatingDistance
FROM (SELECT
P1.name,
P1.type,
P1.email,
@(MAX(R1.Rating) - MIN(R1.Rating)) AS RatingDistance,
Q1.Name AS Rname
FROM Rating R1
INNER JOIN Restaurant Q1 ON Q1.RestaurantID = R1. RestaurantID
INNER JOIN Rater P1 ON P1.UserID = PM1.UserID
GROUP BY P1.name, Q1.Name) AS TMP
GROUP BY TMP.name, TMP.RName
ORDER BY MaxRatingDistance DESC;
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.