Using the following database table description, create the following SQL queries
ID: 3701005 • Letter: U
Question
Using the following database table description, create the following SQL queries
WRITE THE FOLLOWING SQL QUERIES:
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
k) For this query three tables are joined namely Rater, Rating and Restaurant. In where clause highest rating is given to filter out the result.
SELECT ra.name, ra.join_date, ra.reputation, res.Name, rt.Date
FROM Rater ra
INNER JOIN Rating rt
ON rt.UserID = ra.UserID
INNER JOIN Restaurant res
ON res.RestaurantID = rt.RestaurantID
WHERE rt.Food = 5 AND rt.Mood = 5;
l) For this three tables are joined namely Rater, Rating and Restaurant. In where clause highest rating is given to filter out the result.
SELECT ra.name, ra.reputation, res.Name, rt.Date
FROM Rater ra
INNER JOIN Rating rt
ON rt.UserID = ra.UserID
INNER JOIN Restaurant res
ON res.RestaurantID = rt.RestaurantID
WHERE rt.Food = 5 AND rt.Mood = 5;
m) Five tables are joined to get the desired result. In where clause reataurant name given to filter the specifice reaturant rating.
SELECT ra.name, ra.reputation, ri.comment, m.name, m.price
FROM Rater ra
INNER JOIN Rating rt
ON rt.UserID = ra.UserID
INNER JOIN Restaurant res
ON res.RestaurantID = rt.RestaurantID
INNER JOIN MenuItem m
ON m.RestaurantID = res.RestaurantID
INNER JOIN RatingItem ri
ON ri.ItemID = m.IremID
WHERE res.Name = "Ma Cuisine";
n) Table Rater and Rating are joined. In where clause subquery is used to compare the rating with the rater named 'John'.
SELECT ra.name, ra.email
FROM Rater ra
INNER JOIN Rating rt
ON rt.UserID = ra.UserID
WHERE (rt.Price+rt.Food+rt.Mood+rt.Staff) < (SELECT r.Price+r.Food+r.Mood+r.Staff
FROM Rater rg
INNER JOIN Rating r
ON r.UserID = rg.UserID
WHERE rg.name = 'John');
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.