SQLize | PHPize | SQLtest

------+Ankiit---- create table celeb( name varchar(30)); insert into celeb values ('virat kohli'), ('Narendra damodar modi') , ('salman'); select charindex(celebs, ' ') from celeb; /* --fetch 3rd higest salary -- fetch lowest sal if department count(*) <3 CREATE TABLE Employee ( emp_id int NULL, emp_name varchar(50) NULL, salary int NULL, manager_id int NULL, emp_age int NULL, dep_id int NULL, dep_name varchar(20) NULL, gender varchar(10) NULL ); Insert into Employee values (1,'Ankit', 14300,4,39,100,'Analytics','Female'), (2,'Mohit', 14000,5,48,200,'IT','Male'), (3,'Vikas', 12100,4,37,100,'Analytics','Female'), (4,'Rohit', 7260,2,16,100,'Analytics','Female'), (5,'Mudit', 15000,6,55,200,'IT','Male'), (6,'Agam', 15600,2,14,200,'IT','Male'), (7,'Sanjay', 12000,2,13,200,'IT','Male'), (8,'Ashish',7200,2,12,200,'IT','Male'), (9,'Mukesh', 7000,6,51,300,'HR','Male'), (10,'Rakesh',8000,6,50,300,'HR','Male'), (11,'Akhil',4000,1,31,500,'Ops','Male'); with cte as( select *,row_number() over( partition by dep_name order by salary desc ) rn, count(*) over( partition by dep_name order by (select null) rows between unbounded preceding and unbounded following) cnt from employee ) --select * from cte order by dep_name, rn;/* select * from cte where ( case when cnt>=3 and rn=3 then 1 when cnt=2 and rn=2 then 1 when cnt=1 and rn=1 then 1 end) =1; with cte as( select *,row_number() over( partition by dep_name order by salary) rn, count(*) over( partition by dep_name) cnt from employee ) select * from cte where rn=3 or(cnt<3 and rn=cnt ); --fetch the record when 3 consecutive stock_quantity less than 50 --output_tabl>supplier_id,product_id, first_date, no_of&record create table stock( supplier_id int, product_id int , stock_quantity int , record_date date ) INSERT INTO stock (supplier_id, product_id, stock_quantity, record_date) VALUES (1, 1, 60, '2022-01-01'), (1, 1, 40, '2022-01-02'), (1, 1, 35, '2022-01-03'), (1, 1, 45, '2022-01-04'), (1, 1, 51, '2022-01-06'), (1, 1, 55, '2022-01-09'), (1, 1, 25, '2022-01-10'), (1, 1, 48, '2022-01-11'), (1, 1, 45, '2022-01-15'), (1, 1, 38, '2022-01-16'), (1, 2, 45, '2022-01-08'), (1, 2, 40, '2022-01-09'), (2, 1, 45, '2022-01-06'), (2, 1, 55, '2022-01-07'), (2, 2, 45, '2022-01-08'), (2, 2, 48, '2022-01-09'), (2, 2, 35, '2022-01-10'), (2, 2, 52, '2022-01-15'), (2, 2, 23, '2022-01-16'); --best with cte as(select *, dateadd(day, -1*row_number() over(partition by supplier_id, product_id order by record_date),record_date) grp from stock where stock_quantity<50) select supplier_id,product_id, min(record_date) first_date, count(*) as no_of_record from cte group by supplier_id, product_id, grp having count(*) >1; --first attempt with cte as(select *, iif(stock_quantity<50, 0,stock_quantity) base_quantity from stock) , cte2 as(select *, row_number() over(partition by supplier_id, product_id order by base_quantity) - day(record_date) as k from cte ) select supplier_id, product_id,min(record_date) d,count(*) from cte2 where base_quantity=0 group by supplier_id, product_id,k having count(*)>1; --give start to a movie create table movies(id int primary key, genre varchar(20), title varchar(50) ); --Create reviews table CREATE TABLE reviews ( movie_id INT, rating DECIMAL(3,1), FOREIGN KEY (movie_id) REFERENCES movies(id) ); --Insert sample data into movies table INSERT INTO movies (id, genre, title) VALUES (1, 'Action', 'The Dark Knight'), (2, 'Action', 'Avengers: Infinity War'), (3, 'Action', 'Gladiator'), (4, 'Action', 'Die Hard'), (5, 'Action', 'Mad Max: Fury Road'), (6, 'Drama', 'The Shawshank Redemption'), (7, 'Drama', 'Forrest Gump'), (8, 'Drama', 'The Godfather'), (9, 'Drama', 'Schindler"s List'), (10, 'Drama', 'Fight Club'), (11, 'Comedy', 'The Hangover'), (12, 'Comedy', 'Superbad'), (13, 'Comedy', 'Dumb and Dumber'), (14, 'Comedy', 'Bridesmaids'), (15, 'Comedy', 'Anchorman: The Legend of Ron Burgundy'); --Insert sample data into reviews table INSERT INTO reviews (movie_id, rating) VALUES (1, 4.5), (1, 4.0), (1, 5.0), (2, 4.2), (2, 4.8), (2, 3.9), (3, 4.6), (3, 3.8), (3, 4.3), (4, 4.1), (4, 3.7), (4, 4.4), (5, 3.9), (5, 4.5), (5, 4.2), (6, 4.8), (6, 4.7), (6, 4.9), (7, 4.6), (7, 4.9), (7, 4.3), (8, 4.9), (8, 5.0), (8, 4.8), (9, 4.7), (9, 4.9),(9, 4.5), (10, 4.6), (10, 4.3), (10, 4.7), (11, 3.9), (11, 4.0), (11, 3.5), (12, 3.7), (12, 3.8), (12, 4.2), (13, 3.2), (13, 3.5), (13, 3.8), (14, 3.8), (14, 4.0), (14, 4.2), (15, 3.9), (15, 4.0), (15, 4.1),(9, 4.5), (10, 4.6), (10, 4.3), (10, 4.7), (11, 3.9), (11, 4.0), (11, 3.5), (12, 3.7), (12, 3.8), (12, 4.2), (13, 3.2), (13, 3.5), (13, 3.8), (14, 3.8), (14, 4.0), (14, 4.2), (15, 3.9), (15, 4.0), (15, 4.1); select genre,title ,title,rating--iif(rating=5, '*****',iif(rating=4,'****',iif(rating=3,'***'))) ,case when rating=5 then '*****' when rating =4 then '****' when rating=3 then'***' when rating =2 then '**' else '*' end rating from (select m.genre,m.title,round(avg(r.rating), 0) rating, row_number() over(partition by m.genre order by avg(r.rating) desc) rnk from movies m inner join reviews r on m.id=r.movie_id group by m.genre,m.title) k where rnk=1; select genre,title ,title,rating, replicate ('*', rating) from (select m.genre,m.title,round(avg(r.rating), 0) rating, row_number() over(partition by m.genre order by avg(r.rating) desc) rnk from movies m inner join reviews r on m.id=r.movie_id group by m.genre,m.title) k where rnk=1; -->bom-del is different from del-bom route -->fetch route having maximum no of ticket_count CREATE TABLE tickets ( airline_number VARCHAR(10), origin VARCHAR(3), destination VARCHAR(3), oneway_round CHAR(1), ticket_count INT ); INSERT INTO tickets (airline_number, origin, destination, oneway_round, ticket_count) VALUES ('DEF456', 'BOM', 'DEL', 'O', 150), ('GH1789', 'DEL', 'BOM', 'R', 50), ('JKL012', 'BOM', 'DEL', 'R', 75), ('MNO345', 'DEL', 'NYC', 'O', 200), ('PQR678', 'NYC', 'DEL', 'O', 180), ('STU901', 'NYC', 'DEL', 'R', 60), ('ABC123', 'DEL', 'BOM', 'O', 100), ('VWX234', 'DEL', 'NYC', 'R', 90); --select * from tickets; /*select least(origin, destination), greatest (origin, destination) ,sum(ticket_count) from tickets group by least(origin, destination), greatest (origin, destination) order by sum(ticket_count) desc; */ select origin, destination, sum(ticket_count) s from tickets group by origin, destination order by s desc; select origin, destination, sum(iif(oneway_round='r', 2*ticket_count,ticket_count)) s from tickets group by origin, destination order by s desc; select top 1 origin, destination , sum(ticket_count) s from ( select origin, destination, ticket_count from tickets union all select destination, origin ,ticket_count from tickets where oneway_round='r') k group by origin, destination order by s desc; --removee duplicate data keeping first CREATE TABLE city_distance ( distance INT, source VARCHAR(512), destination VARCHAR(512) ); delete from city_distance; INSERT INTO city_distance(distance, source, destination) VALUES ('100', 'New Delhi', 'Panipat'); INSERT INTO city_distance(distance, source, destination) VALUES ('200', 'Ambala', 'New Delhi'); INSERT INTO city_distance(distance, source, destination) VALUES ('150', 'Bangalore', 'Mysore'); INSERT INTO city_distance(distance, source, destination) VALUES ('150', 'Mysore', 'Bangalore'); INSERT INTO city_distance(distance, source, destination) VALUES ('250', 'Mumbai', 'Pune'); INSERT INTO city_distance(distance, source, destination) VALUES ('250', 'Pune', 'Mumbai'); INSERT INTO city_distance(distance, source, destination) VALUES ('2500', 'Chennai', 'Bhopal'); INSERT INTO city_distance(distance, source, destination) VALUES ('2500', 'Bhopal', 'Chennai'); INSERT INTO city_distance(distance, source, destination) VALUES ('60', 'Tirupati', 'Tirumala'); INSERT INTO city_distance(distance, source, destination) VALUES ('80', 'Tirumala', 'Tirupati'); select * from city_distance; --sol 1(without order) with cte as ( select *,row_number () over (partition by greatest (source, destination),least(source,destination),distance order by (select null) ) rn from city_distance) select * from cte where rn=1 order by rn; --sol2(with order) with cte as ( select *, row_number() over(order by(select null)) rn from city_distance ) select a.source, a.destination, a.distance from cte a left join cte b on a.source=b.destination and a.destination =b.source and a.distance=b.distance where b.source is null or a.rn<b.rn; CREATE TABLE Orders ( [orderid] INT, [productid] INT, [timestamp] DATETIME2 ) INSERT INTO Orders VALUES (10300,1005,'2024-03-04 16:55:00'), (10303,1005,'2024-03-05 08:25:00'), (10302,1009,'2024-03-04 13:37:00'), (10304,1006,'2024-03-04 11:10:00'), (10301,1007,'2024-03-04 15:26:00'), (10305,1008,'2024-03-04 17:15:00'), (10306,1007,'2024-03-04 19:05:00'), (10340,1008,'2024-03-04 17:17:00'), (10307,1009,'2024-03-04 17:09:00'), (10341,1010,'2024-03-04 18:17:00'), (10347,1011,'2024-03-04 19:09:00'); --select datepart (hour,timestamp) day_name from orders; with cte as( select * , datename (weekday,timestamp) day_name, case when datepart(hour, timestamp) >= 05 and datepart(hour,timestamp) < 12 then 'morning' when datepart(hour, timestamp) >= 12 and datepart(hour, timestamp) < 17 then 'afternoon' else 'evening' end day_time from orders) select day_time, day_name, count(orderid) tc , row_number() over(partition by day_time order by count(orderid)) from cte group by day_time, day_name; --percentage increase current month vs previois cumulative sum CREATE TABLE covid_cases ( record_date DATE PRIMARY KEY, cases_count INT ); insert into covid_cases values ('2021-01-01',66), ('2021-01-02',41), ('2021-01-03',54), ('2021-01-04',68), ('2021-01-05',16), ('2021-01-06',90), ('2021-01-07',34), ('2021-01-08',84),('2021-01-09',71), ('2021-01-10',14),('2021-01-11',48),('2021-01-12',72), ('2021-01-13',55); --select * from covid_cases ; with cte as( select datename(weekday, record_date) day_name , count(cases_count) tcpm from covid_cases group by datename(weekday, record_date) ), cte2 as (select day_name, (1.00*tcpm-lag(tcpm,1,tcpm) over(order by (select null)))/tcpm incra from cte) select *, sum(tcpm) over (order by (select null ) ROWS BETWEEN UNBOUNDED PRECEDING AND 1 PRECEDING) from cte;