In this assignment, you are required to write SQL queries and relational algebra statements.
Posted: Mon May 09, 2022 6:24 am
In this assignment, you are required to write SQL
queries and relational algebra statements.
In this assignment, you are required to write SQL queries and relational algebra statements. A) Please answer the questions according to the relations below. User TCNumber Phone Number Name Surname e-mail Birth Date About Me Gender Street City Postcode EcoPoint Number of Trips InvitedByEmail Rating UserDriver TCNumber Brand Color km year Fueltype Invited Friends TCNoOfInviter e-mail InviterEmail PeopleToBeNotified TCNumber Phone1 Phone2 Phone3 User Comment TCNumberCommenter TCNumberCommented Comment
TravelPlan Travelin PostedBy Commuter Type DateOfPost Departure Destination DepartureDate Departure Time Cost 1 Duration NumberOfAvailableSeats TravelType Gender Preference IfVerified
2. Give an expression in relational algebra for each of the following queries. You need to provide the answer in accordance with the provided template. a) Find the drivers (name) who have a rating less than 3 and travelled from İstanbul to Ankara. T1€ IlTravelid (Departure=İstanbul, Destination=Ankara (TravelPlan)) T2+ PrcNumber (Tt Postedby (o Departure=İstanbul, Destination=Ankara (TravelPlan))) b) Find the user (name) with the highest rating who travelled only as a passenger and commented on at least three users. T1€(TtTCNumberUser)-( TitCNumber User Driver) 2 T26 (TITCNumber (T1 "TCnumber=PostedByTravelPlan) c) Find the users who have travelled both as a driver and passenger at different times. Tlfnt Number (UserDriver) T2€ PTCNumber (Tt postedBy (OcommuterType=Driver TravelPlan))
B) Please answer the questions according to the relational schema below. USER Uid name surname RECIPE rid title uid type RECIPE_RATING rid uid date rating comment COMMENT_RATING rid uid by uid rating date RECIPE_OF_DAY rid date Write SQL queries for the following queries. a. Which recipe/s has/have received the highest number of ratings from users until now? b. Which recipe/s has/have received the highest number of ratings of which are lower than 2 (out of 5) in average from users until now? C. Find the users who rated at least one of the “recipes of the day" as 5. d. Find the users who has given a rating value of 5 to all the recipes that have been selected as "recipe of the day".
queries and relational algebra statements.
- In This Assignment You Are Required To Write Sql Queries And Relational Algebra Statements 1 (86.07 KiB) Viewed 20 times
TravelPlan Travelin PostedBy Commuter Type DateOfPost Departure Destination DepartureDate Departure Time Cost 1 Duration NumberOfAvailableSeats TravelType Gender Preference IfVerified
2. Give an expression in relational algebra for each of the following queries. You need to provide the answer in accordance with the provided template. a) Find the drivers (name) who have a rating less than 3 and travelled from İstanbul to Ankara. T1€ IlTravelid (Departure=İstanbul, Destination=Ankara (TravelPlan)) T2+ PrcNumber (Tt Postedby (o Departure=İstanbul, Destination=Ankara (TravelPlan))) b) Find the user (name) with the highest rating who travelled only as a passenger and commented on at least three users. T1€(TtTCNumberUser)-( TitCNumber User Driver) 2 T26 (TITCNumber (T1 "TCnumber=PostedByTravelPlan) c) Find the users who have travelled both as a driver and passenger at different times. Tlfnt Number (UserDriver) T2€ PTCNumber (Tt postedBy (OcommuterType=Driver TravelPlan))
B) Please answer the questions according to the relational schema below. USER Uid name surname RECIPE rid title uid type RECIPE_RATING rid uid date rating comment COMMENT_RATING rid uid by uid rating date RECIPE_OF_DAY rid date Write SQL queries for the following queries. a. Which recipe/s has/have received the highest number of ratings from users until now? b. Which recipe/s has/have received the highest number of ratings of which are lower than 2 (out of 5) in average from users until now? C. Find the users who rated at least one of the “recipes of the day" as 5. d. Find the users who has given a rating value of 5 to all the recipes that have been selected as "recipe of the day".