Відео

Cross apply mean cross join??

I didn’t get the defining the months part …. Plz would u please explain why should we do that..

How much salary will a fresher get for SQL?

Hi Sir , Please let me know this will work or not with cte as(select *, case when type="deposit" then amount else -(amount) end as total, row_number() over(partition by transaction_date order by (select null)) rn from trans) select transaction_id,type,amount,transaction_date,sum(total) over(order by transaction_date ,rn) as running_total from cte;

Logic is important, If you have logic then you can write query Well Done !

Please provide more scenario based interview questions

Superb explanation 👌 👏 👍

Another approach - select c as cust_id , max(origin) as origin, max(final_destination) as final_destination from ( select cust_id as c, case when origin not in (select destination from flights where cust_id = c) then origin end origin, case when destination not in (select origin from flights where cust_id = c) then destination end final_destination from flights) t group by cust_id

Sir u are a nice person

In the Oracle Database.. We don't have that function ( least () , greatest() , what should I do now..?

Oracle with rs as ( SELECT dt,sales,((sales/lag(sales,1) over (order by dt))-1)*100 sale_per from salesvar_tbl ) select dt,sales,sale_per from rs where sale_per>0 or sale_per is null;

My approach select cust_id, max(case when (cust_id,origin) not in (select cust_id,destination from flights group by cust_id,destination) then origin end) as origin , max(case when (cust_id,destination) not in (select cust_id,origin from flights group by cust_id,origin) then destination end) as destination from flights group by cust_id;

SELECT ORIGIN,DESTINATION FROM (SELECT*,ROW_NUMBER() OVER (ORDER BY (SELECT NULL)) AS RNUM FROM ROUTE) A WHERE RNUM NOT IN (2);

Thanks for bringing a valuable question. Actually this question is a leetcode DSA question which was solved in ankitbansal youtube channel before 6 months. Link: ua-cam.com/video/XssFpKFSNFw/v-deo.html Since you have used only 1 method to solve i.e. using self joins. But he explained another method also along with self joins method using "union all", "cte", "case stat" and "windows function". I like the second method. Logic used to solve using 2nd method: - First uinion all the start & end locations - then use count(*) of those start & end locations - then only filter where count=1 using max/min & case statement to ignore null values. keep it up. Bring such good questions. already liked the video.

-- Solution using approach unbounded preceding WITH brand_vw AS ( SELECT *, LAST_VALUE(category IGNORE NULLS) OVER (ORDER BY id ROWS BETWEEN UNBOUNDED PRECEDING AND CURRENT ROW) AS new_category FROM brands ) SELECT new_category, brand_name FROM brand_vw ORDER BY id;

WITH cineamas_seat_stats AS ( SELECT *, LAG(free,1,0) OVER(ORDER BY seat_id ASC) AS Prev_Seat_Availability, LEAD(free,1,0) OVER(ORDER BY seat_id ASC) AS Next_Seat_Availability, FROM practiceDB1.cinema_tbl ORDER BY 1 ) SELECT seat_id, FROM cineamas_seat_stats WHERE free = 1 AND (prev_Seat_Availability = 1 OR Next_Seat_Availability = 1) ;

bro how to apply for sql job roles? is it possible for 2022 batch as a fresher?

SIMPLE SOLUTION: select cust_id, min(case when (cust_id,origin) not in (select cust_id,destination from flights) then origin end) as origin, max(case when (cust_id,destination) not in (select cust_id,origin from flights) then destination end) as final_destination from flights group by cust_id order by cust_id

Amazing...please bring complex questions like this❤

If I want to make it where the output will be.. customerid, origin, destination, via via column will contains the values of the cities other than source and destination. Like for customerid = 2, via will be Pune For customerid = 1, via = Hyderabad, Kochi

with cte as(select *,dayname(date(id)) as day_name, case when lead(dayname(id)) over(partition by empid,month(id) order by date(id)) in ('saturday','sunday') then lead(id) over(partition by empid,month(id) order by date(id)) end as next_working_time from emp_tbl) select empid,sum(timestampdiff(hour,id,next_working_time)) from cte group by empid

Great question ::: My approach please let me know this will work or not with cte as (select *,case when col2> col3 then col2 else col3 end as max from tbl_maxval) select col1,max as maxval from cte;

My approach using UNION - -- Approach 1: Using union SELECT * FROM happiness_tbl WHERE country = 'India' UNION ALL SELECT * FROM happiness_tbl WHERE country = 'Srilanka' UNION ALL SELECT * FROM happiness_tbl WHERE country NOT IN ('India', 'Srilanka') ORDER BY CASE WHEN country = 'India' THEN 1 WHEN country = 'Srilanka' THEN 2 ELSE 3 END;

Nice

My solution alter table studnts_tbl add column grade varchar(50); update studnts_tbl set grade = case when marks >=80 then "excellent" when marks >=60 and marks<80 then "v good" when marks >=35 and marks <60 then "good" else "poor" end;

SELECT * FROM TABLEA UNION ALL SELECT * FROM TABLEB WHERE EMPID=3; Another approach ! Thank you! your videos helping me to learn SQL better !

Interesting question,,, but solution is not dynamic if table is long then it difficult to us.. My approach dynamic solution ...... with cte as(select *,case when ranking = (select max(ranking) from happiness_tbl) then 2 when ranking = (select max(ranking) from happiness_tbl where ranking != (select max(ranking) from happiness_tbl)) then 1 else 3 end as derivedrank from happiness_tbl) select * from cte order by derivedrank; Please let me know this will work or not .....sir ji

One of the best question

Love this question i am solving in mysql i face may difficulty but i enjoy mysql solution with recursive num as(select date_format(curdate(),"%Y-%m-01") as date union all select date_add(date, interval 01 day) from num where month(date_add(date, interval 01 day)) = month(curdate())), cte as(select *,date_format(date,"%W")as weekday from num where date_format(date,"%W") = "Wednesday") select date as 2nd_wednesday from (select *, row_number() over(order by date) rnk from cte) sal where rnk =2;

empID empname salary Deptid Deptname rn 1 kalyan 39000 208 Finance 1 2 raju 35000 208 Finance 2 3 Raju 4500 206 HR 1 4 ramu 40000 206 HR 2 5 kishor 50000 207 IT 1 6 anitha 50000 207 IT 1 7 nikhiltha 42000 207 IT 3 can you give code fo the above table. i have tried using rank function but it is not coming

my answer with cte as (select *,lag(sales) over(order by dt) pv from sales1) select dt,sales,`%v` from (select*,ifnull(round((sales-pv)*100/pv,0),0) as `%v` from cte) sal where `%v` >=0;

Bro.. I am using oracle database... Is there any change in oracle ?

Great question , i solved and post daily sql question on linkdin and also tag you but you can't like or any thing, i think you are not active on linkdin

with cte as(select *,lag(ytd_sales,1,0) over(order by monthnum) amt from sales) select month,ytd_sales, (ytd_sales-amt) periodic_sales from cte;

with cte as ( select count(gender) ra from employee_tbl), hai as(select sum(case when gender = "male" then 1 end) as mal, sum(case when gender = "female" then 1 end) as fem from employee_tbl) select round((mal*100)/ra,0) as male_per, round((fem*100)/ra,0) as female_per from cte,hai; My approach please reply i am right or not sir

With CTE As ( Select *, case when Transaction_Type='deposit' then Transaction_Amount*1 when Transaction_Type='withdrwal' then Transaction_Amount*-1 end as Amount_For_RT from [Transaction Details]) Select * , SUM(CTE.Amount_For_RT)Over(Order by (Select Null) rows between unbounded preceding and current row) as RT from CTE

select e2.ename as employee_name from employees_tbl e1 join employees_tbl e2 on e1.empid =e2.managerid where e2.salary>e1.salary;

select substring_index(email,"@",-1) as domain from customer_tbl; select substr(email,locate("@",email)+1) as domain from customer_tbl; select right(email,(length(email) - locate("@",email))) as domain from customer_tbl; My approach

select max(id) as max_id from (select id from employees group by id having count(*) = 1) sal;

In oracle sql - Select substr(email,instr(email,'@')+1) as domain from customer_tbl;

Sir script de dya kro practice krne ke lye

with cte as ( select *, first_value(salary) over(partition by dept_id order by salary) min , first_value(salary) over(partition by dept_id order by salary desc) max from emps) select dept_id,max(case when salary = min then emp_name end) as min_salary_emp_name, max(case when salary = max then emp_name end) as max_salary_emp_name from cte group by 1;

my solution please let me know this is fine or not with cte as( select *, dense_rank() over(partition by dept_id order by salary) min ,dense_rank() over(partition by dept_id order by salary desc) max from emps) select dept_id,max(case when min = 1 then emp_name end) as min_salary, max(case when max=1 then emp_name end) as max_salary from cte group by 1;

Using full join

By using full join this case will be resolved.

with cte as( select *, case when type='deposit' then 1*amount else -1*amount end as new_amount from transactions_1308) select transaction_id,type,amount,transaction_date, sum(new_amount) over(order by transaction_date rows between unbounded preceding and current row) as running_sum from cte;

Code: with cte as (select *, row_number() over (order by seat_id) rn from cinema_tbl where free = 1), cte2 as ( select seat_id,free,(seat_id - rn ) num from cte), final as ( select seat_id, count(num) over (partition by num) cnt from cte2) select seat_id from final where cnt > 1

Code : with cte as (select *,count(id) over (partition by id order by id) rn from employees) select max(id) id from cte where rn = 1

select lpad('*',12,'*')||''||rpad(CARD_NUMBER,4) as card_number from cards

l = ['A2','B3','C10'] l1 = [] for i in l: s = '' n = '' for j in i: if j.isalpha(): s += j else: n += j l1.append(s*int(n)) print(l1) ---------------------------------------------------------------------------- l1 = [1,3] l2 = [1,3,4] res = [] def fun(lst1,lst2): for i in lst1: if i not in lst2: res.append(i) if len(l1)>len(l2): fun(l1,l2) else: fun(l2,l1) print(res)