create database assignment2
select * from jomato
-- 1. Create a user-defined functions to stuff the Chicken into �Quick Bites�. Eg:
�Quick Chicken Bites�.
Create FUNCTION ChickenStuff (@inputString VARCHAR(MAX))
Returns VARCHAR(MAX)
As
Begin Declare @result VARCHAR(MAX)
Set @result = STUFF(@inputString,7, 7, 'Chicken bites')
Return @result
End
Select dbo.Chickenstuff('Quick Bites')
-- 2. Use the function to display the restaurant name and cuisine type which has
the maximum number of rating.
Create function fn_rating ()
Returns Table Return Select RestaurantName,CuisinesType,Rating from
Jomato
where
Rating = (Select MAX(Rating) from Jomato)
Select * from dbo.fn_rating()
/* 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 5 star rating, �Average� if it is above 3
and below 3.5 and �Bad� if it is below 3 star rating. */
Select *,
case
when rating > 4 then 'Excellent'
when rating > 3.5 and rating < 5 then 'Good'
when rating > 3 and rating < 3.5 then'Average'
when rating < 3 then '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 Rating,
Ceiling(Rating) as Ceil_value,
Floor(rating) as floor_value,
ABS(rating) as Absolute_value
from jomato
--Dispaly current date
Select
GETDATE() as CurrentDate,
Datename(year,getdate()) as Year,
Datename(month,getdate ()) as Month_Name,
Datename(day,getdate()) as day
-- 5. Display the restaurant type and total average cost using rollup
select RestaurantType, RestaurantName,
sum(averagecost) as Totalaverage_cost
from Jomato
group by rollup (RestaurantName,restauranttype)
