select * from jomato
/* 1. Create a stored procedure to display the restaurant name, type and cuisine
where the
table booking is not zero.*/
create procedure table_booking as
select restaurantname, restauranttype, CuisinesType from jomato
where tablebooking = 'yes'
exec table_Booking
/*2. Create a transaction and update the cuisine type �Cafe� to �Cafeteria�. Check
the result
and rollback it.*/
Begin transaction
update Jomato
set CuisinesType = 'Cafeteria' where CuisinesType = 'Cafe'
select * from Jomato where CuisinesType = 'Cafeteria'
Rollback Transaction
select * from Jomato
/*3. Generate a row number column and find the top 5 areas with the highest rating
of
restaurants. */
select top 5
DENSE_RANK ()over (order by rating desc) as Row_Number, Area
from Jomato
/*4 . Use the while loop to display the 1 to 50. */
Declare @Count INT
set @Count = 1
while (@Count<=50) begin print @Count
set @Count = @count+1
end;
/* 5. Write a query to Create a Top rating view to store the generated top 5
highest rating of
restaurants. */
create view top_rating as
select top(5) DENSE_RANK ()over (order by rating desc) as
Ranking, * from Jomato
select * from top_rating
/* 6. Create a trigger that give an message whenever a new record is inserted.*/
Create trigger Email_Notification on jomato
after insert as
begin EXEC [Link].sp_send_dbmail
@profile_name = 'divyanshu',
@recipients = 'divyanshu660@[Link]',
@subject = 'New Record Inserted.',
@body = 'A new record has been inserted.',
@importance ='HIGH'
end;
