Assignment Module 5&6

create database assignment3;

use assignment3;

/*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.*/

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

-- 1.Create a stored procedure to display the restaurant name, type and cuisine where the table booking is not zero.

create procedure sp_zero_booking

as

select restaurantName,restaurantType,CuisinesType from jomato 

where tablebooking ='No'

go;

exec sp_zero_booking;

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

-- 2.Create a transaction and update the cuisine type ‘Cafe’ to ‘Cafeteria’. Check the result and rollback it.

--Create a transaction and update the cuisine type ‘Cafe’ to ‘Cafeteria

begin transaction;

update jomato

set cuisinestype='Cafeteria'

where cuisinestype='Cafe';

--Check the result

select * from jomato;

--rollback it

rollback transaction;

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

-- 3.Generate a row number column and find the top 5 areas with the highest rating of restaurants.

with cte as(

select *, ROW_NUMBER()over(order by rating desc,no#of#rating desc) [row_number] from jomato)

select * from cte

where row_number<=5

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

-- 4. Use the while loop to display the 1 to 50.

declare @num int = 1;

while @num<=50

begin

print @num

set @num = @num+1

end;

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

-- 5. Write a query to Create a Top rating view to store the generated top 5 highest rating of restaurants

Create View v_top_five as

with cte as(

select restaurantName,RestaurantType,CuisinesType,rating, ROW_NUMBER()over(order by rating desc) as Top_Rated from jomato)

select * from CTE

where Top_Rated<=5;

select * from v_top_five;

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

-- 6. Create a trigger that give an message whenever a new record is inserted

create trigger trg_insert_jomato

on jomato

after insert

as

begin

declare @newrecord nvarchar(100);

select @newrecord=restaurantname

from inserted;

print('A new record has been inserted into the jomato table. New Restaurant: '+ @newrecord);

end;

insert into jomato(restaurantname)

select 'Udupi veg'

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