Assignment Module 3&4

create database assignment2

use assignment2

select * from jomato

--------------------------------------------------------------------------------------------------------

/* Dataset: Jomato

 About the dataset:

 You work for a data analytics company, and your client is a food delivery platform similar to

 Jomato. They have provided you with a dataset containing information about various

 restaurants in a city. Your task is to analyze this dataset using SQL queries to extract valuable

 insights and generate reports for your client. */

----------------------------------------------------------------------------------------------------- 

 --Tasks to be performed:

 -- 1.Create a user-defined functions to stuff the Chicken into ‘Quick Bites’. Eg: ‘Quick Chicken Bites’

 create function fn_modify_restaurant_type(

 @oldtype nvarchar(100),

 @newtype nvarchar(100)

 )

 returns table

 as

 return

(

select

Orderid,

        case

            when RestaurantType = @oldtype then @newtype

            else RestaurantType

        END AS RestaurantType,

        RestaurantName,

        Rating,

        No#of#Rating,

        AverageCost,

        OnlineOrder,

        TableBooking,

        CuisinesType,

        Area,

        LocalAddress,

        [Delivery Time]

from jomato

);

select * from fn_modify_restaurant_type('Quick Bites','Quick Chicken Bites')

--------------------------------------------------------------------------------------------------------------

-- 2. Use the function to display the restaurant name and cuisine type which has the maximum number of rating

create function fn_max_rated ()

returns table

as

return

select RestaurantName,CuisinesType,rating from jomato 

where rating=(select max(rating)from jomato);

select * from fn_max_rated();

-------------------------------------------------------------------------------------------------------------

/* 3. Create a Rating Status column to display the rating as ‘Excellent’ if it has more the 4

 start rating, ‘Good’ if it has above 3.5 and below 4 star rating, ‘Average’ if it is above 3

 and below 3.5 and ‘Bad’ if it is below 3 star rating. */

select restaurantname,Cuisinestype,Rating,

case

when rating>4 then 'Excellent'

when rating between 3.5 and 4 then 'Good'

else 'Bad'

end

as [Rating status]

from jomato;

--------------------------------------------------------------------------------------------------------

/* 4. Find the Ceil, floor and absolute values of the rating column and display the current

 date and separately display the year, month_name and day */

select restaurantname,rating,

CEILING(rating) [Ceil],

FLOOR(rating) [Floor],

ABS(rating) [Aboslute],

convert(nvarchar(15),getdate(),105) as [Current date],

year(getdate()) [Year],

DATENAME(month,getdate())[Month name],

DAY(getdate()) [Day]

from jomato;

---------------------------------------------------------------------------------------------------------

 -- 5.Display the restaurant type and total average cost using rollup

select 

restauranttype,

sum(averagecost) as [total average cost]

from jomato

group by restauranttype with rollup;

-----------------------------------------------------------------------------------------------------------