SQLize | PHPize | SQLtest

CREATE TABLE orders ( order_id INT NOT NULL, user_id INT NOT NULL, r_id INT NOT NULL, amount DECIMAL(10, 2) NOT NULL, order_date DATE NOT NULL, partner_id INT NOT NULL, delivery_time INT NOT NULL, delivery_rating INT NULL, restaurant_rating INT NULL ); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1001, 1, 1, 550.00, '2022-05-10', 1, 25, 5, 3); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1002, 1, 2, 415.00, '2022-05-26', 1, 19, 5, 2); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1003, 1, 3, 240.00, '2022-06-15', 5, 29, 4, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1004, 1, 3, 240.00, '2022-06-29', 4, 42, 3, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1005, 1, 3, 220.00, '2022-07-10', 1, 58, 1, 4); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1006, 2, 1, 950.00, '2022-06-10', 2, 16, 5, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1007, 2, 2, 530.00, '2022-06-23', 3, 60, 1, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1008, 2, 3, 240.00, '2022-07-07', 5, 33, 4, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1009, 2, 4, 300.00, '2022-07-17', 4, 41, 1, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1010, 2, 5, 650.00, '2022-07-31', 1, 67, 1, 4); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1011, 3, 1, 450.00, '2022-05-10', 2, 25, 3, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1012, 3, 4, 180.00, '2022-05-20', 5, 33, 4, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1013, 3, 2, 230.00, '2022-05-30', 4, 45, 3, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating ) VALUES (1014, 3, 2, 230.00, '2022-06-11', 2, 55, 1, 2); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1015, 3, 2, 230.00, '2022-06-22', 3, 21, 5, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1016, 4, 4, 300.00, '2022-05-15', 3, 31, 5, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1017, 4, 4, 300.00, '2022-05-30', 1, 50, 1, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1018, 4, 4, 400.00, '2022-06-15', 2, 40, 3, 5); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1019, 4, 5, 400.00, '2022-06-30', 1, 70, 2, 4); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1020, 4, 5, 400.00, '2022-07-15', 3, 26, 5, 3); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1021, 5, 1, 550.00, '2022-07-01', 5, 22, 2, NULL); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1022, 5, 1, 550.00, '2022-07-08', 1, 34, 5, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1023, 5, 2, 645.00, '2022-07-15', 4, 38, 5, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1024, 5, 2, 645.00, '2022-07-21', 2, 58, 2, 1); INSERT INTO orders (order_id, user_id, r_id, amount, order_date, partner_id, delivery_time, delivery_rating, restaurant_rating) VALUES (1025, 5, 2, 645.00, '2022-07-28', 2, 44, 4, NULL); CREATE TABLE partner ( partner_id INT NOT NULL, partner_name VARCHAR(50) NOT NULL ); INSERT INTO partner (partner_id, partner_name) VALUES (1, 'Suresh'); INSERT INTO partner (partner_id, partner_name) VALUES (2, 'Amit'); INSERT INTO partner (partner_id, partner_name) VALUES (3, 'Lokesh'); INSERT INTO partner (partner_id, partner_name) VALUES (4, 'Kartik'); INSERT INTO partner (partner_id, partner_name) VALUES (5, 'Gyandeep'); create table order_data( id int, order_id int, f_id int ); INSERT INTO order_data (id, order_id, f_id) VALUES (1, 1001, 1), (2, 1001, 3), (3, 1002, 4), (4, 1002, 3), (5, 1003, 6), (6, 1003, 3), (7, 1004, 6), (8, 1004, 3), (9, 1005, 7), (10, 1005, 3), (11, 1006, 1), (12, 1006, 2), (13, 1006, 3), (14, 1007, 4), (15, 1007, 3), (16, 1008, 6), (17, 1008, 3), (18, 1009, 8), (19, 1009, 9), (20, 1010, 10), (21, 1010, 11), (22, 1010, 6), (23, 1011, 1), (24, 1012, 8), (25, 1013, 4), (26, 1014, 4), (27, 1015, 4), (28, 1016, 8), (29, 1016, 9), (30, 1017, 8), (31, 1017, 9), (32, 1018, 10), (33, 1018, 11), (34, 1019, 10), (35, 1019, 11), (36, 1020, 10), (37, 1020, 11), (38, 1021, 1), (39, 1021, 3), (40, 1022, 1), (41, 1022, 3), (42, 1023, 3), (43, 1023, 4), (44, 1023, 5), (45, 1024, 3), (46, 1024, 4), (47, 1024, 5), (48, 1025, 3), (49, 1025, 4), (50, 1025, 5); CREATE TABLE food ( f_id INT NOT NULL, f_name VARCHAR(100) NOT NULL, type VARCHAR(50) NOT NULL ); INSERT INTO food (f_id, f_name, type) VALUES (1, 'Non-veg Pizza', 'Non-veg'), (2, 'Veg Pizza', 'Veg'), (3, 'Choco Lava cake', 'Veg'), (4, 'Chicken Wings', 'Non-veg'), (5, 'Chicken Popcorn', 'Non-veg'), (6, 'Rice Meal', 'Veg'), (7, 'Roti meal', 'Veg'), (8, 'Masala Dosa', 'Veg'), (9, 'Rava Idli', 'Veg'), (10, 'Schezwan Noodles', 'Veg'), (11, 'Veg Manchurian', 'Veg'); CREATE TABLE menu ( menu_id INT NOT NULL, r_id INT NOT NULL, f_id INT NOT NULL, price DECIMAL(10, 2) NOT NULL ); INSERT INTO menu (menu_id, r_id, f_id, price) VALUES (1, 1, 1, 450), (2, 1, 2, 400), (3, 1, 3, 100), (4, 2, 3, 115), (5, 2, 4, 230), (6, 2, 5, 300), (7, 3, 3, 80), (8, 3, 6, 160), (9, 3, 7, 140), (10, 4, 6, 230), (11, 4, 8, 180), (12, 4, 9, 120), (13, 5, 6, 250), (14, 5, 10, 220), (15, 5, 11, 180); CREATE TABLE restaurant ( r_id INT NOT NULL, r_name VARCHAR(100) NOT NULL, cuisine VARCHAR(50) NOT NULL ); INSERT INTO restaurant (r_id, r_name, cuisine) VALUES (1, 'dominos', 'Italian'), (2, 'kfc', 'American'), (3, 'box8', 'North Indian'), (4, 'Dosa Plaza', 'South Indian'), (5, 'China Town', 'Chinese'); CREATE TABLE [user] ( user_id INT NOT NULL, name VARCHAR(100) NOT NULL, email VARCHAR(100) NOT NULL, password VARCHAR(50) NOT NULL ); INSERT INTO [user] (user_id, name, email, password) VALUES (1, 'Nitish', 'nitish@gmail.com', 'p252h'), (2, 'Khushboo', 'khushboo@gmail.com', 'hxn9b'), (3, 'Vartika', 'vartika@gmail.com', '9hu7j'), (4, 'Ankit', 'ankit@gmail.com', 'lkko3'), (5, 'Neha', 'neha@gmail.com', '3i7qm'), (6, 'Anupama', 'anupama@gmail.com', '46rdw2'), (7, 'Rishabh', 'rishabh@gmail.com', '4sw123'); --order_data, partner, order, food, menu, restaurant, user select top 2 * from [user]; select top 2 * from restaurant; select top 2 * from menu; select top 2 * from food; select top 2 * from [orders]; select top 2 * from partner; select top 2 * from order_data; --select the customer who have never order select name from [user] where user_id not in(select user_id from orders) --find the average price/dish select f.f_name ,cast( avg(m.price) as decimal (10,2)) price_per_dish from menu m inner join food f on f.f_id=m.f_id group by f.f_name order by price_per_dish; --find the top most busy restaurant each month with cte as( select format(o.order_date, 'yyyy-MM') date, max(r.r_name) rname , count(o.order_id) total_order , row_number() over( partition by format(o.order_date, 'yyyy-MM') order by count (o.order_id) desc) rn from restaurant r inner join orders o on o.r_id=r.r_id group by format(o.order_date, 'yyyy-MM') ,o.r_id ) select * from cte where rn=1; --fill null value with average rating in order_data table select * into new_order from orders; alter table new_order alter column restaurant_rating float; update t1 set t1.restaurant_rating=t2.nr from new_order t1 inner join ( select r_id, cast(isnull(1.00*sum(restaurant_rating)/count(restaurant_rating) ,0) as decimal (10,2)) nr from new_order group by r_id) t2 on t1.r_id=t2.r_id where t1.restaurant_rating is null ; select t1.*, t2.restaurant_rating rr from new_order t1 inner join orders t2 on t1.order_id=t2.order_id; --restaurant monthly sale is greater than xammount select format (o.order_date, 'yyyy-MM') date, r.r_name, sum(o.amount) total_sale from orders o inner join restaurant r on r.r_id=o.r_id group by format (o.order_date, 'yyyy-MM'),r.r_name having sum(amount)>1000 order by date,total_sale desc; --show all customer_details during a period for a particular person(let ankit) with cte as( select o.order_id,f.type, string_agg(f.f_name,',') food_ate from new_order o inner join order_data d on o.order_id=d.order_id inner join food f on f.f_id=d.f_id group by o.order_id, f.type) select u.name,p.partner_name,r.r_name,c.type ,c.food_ate, o.amount, o.delivery_rating,o.restaurant_rating from orders o inner join [user] u on u.user_id=o.user_id inner join restaurant r --to get restaurant_name on r.r_id=o.r_id inner join cte c --food ate on o.order_id=c.order_id inner join partner p --for partner on o.partner_id=p.partner_id where u.name='Ankit' and o.order_date between '2022-06-10' and '2022-07-10' order by o.order_date ,o.amount desc; --find the restaurant max no of repatativee customer name with cte as(select r_id, user_id, count(user_id) repeat, sum(restaurant_rating)/count (restaurant_rating) rate ,dense_rank() over(partition by r_id order by count(user_id) desc) rn from new_order group by r_id , user_id having count(user_id)>1 ) select r.r_name, u.name , cte.repeat, cte.rate from cte inner join [user] u on u.user_id=cte.user_id inner join restaurant r on cte.r_id=r.r_id order by cte.r_id, cte.rn; --find the no of loyal customer in restaurant select top 1 r.r_name,count(o.cnt) loyal_customer from restaurant r inner join (select r_id,user_id, count(user_id) cnt from orders group by r_id, user_id having count (user_id)>1) o on o.r_id=r.r_id group by r.r_name order by loyal_customer desc ; --mom growth select date,iif(lag=0,total_amount,100.00*(total_amount-lag) /lag) MOM from (select format (order_date, 'yyyy-MM') date, sum(amount) total_amount,lag(sum(amount) , 1) over(order by format (order_date, 'yyyy-MM') ) lag from orders group by format (order_date, 'yyyy-MM') ) k; --custmer fav food with cte as( select u.name,f.f_name, count(f_name) no_oforders, dense_rank() over(partition by u.name order by count (f.f_name) desc) rn from order_data od inner join orders o on o.order_id=od.order_id inner join food f on f.f_id=od.f_id inner join [user] u on o.user_id=u.user_id group by u.name, f.f_name) select name, f_name from cte where rn=1; --most paired food with cte as( select f1.f_name food1,f2.f_name food2 , count(*) paired_order, dense_rank() over(order by count(*) desc) rn from order_data o1 inner join order_data o2 on (o1.order_id=o2.order_id) and (o1.f_id<>o2.f_id) and(o1.f_id>o2.f_id) inner join food f1 on (o1.f_id =f1.f_id) inner join food f2 on o2.f_id=f2.f_id group by f1.f_name,f2.f_name) select * from cte where rn=1; with cte as( select o.order_id, f.f_name from order_data o inner join food f on o.f_id=f.f_id ), cte2 as( select t1.f_name food1, t2.f_name food2, count(*) paired_order, dense_rank() over(order by count (*) desc) rn from cte t1 inner join cte t2 on (t1.order_id=t2.order_id) and(t1.f_name<>t2.f_name) and (t1.f_name>t2.f_name) group by t1.f_name, t2.f_name ) select * from cte2 where rn=1; select * from order_data; --select * from orders; --select * from food;