Select p lastName, p firstName

select p.lastName,
p.firstName,
a.city,
a.state
from person as p
left join address as a on p.personId = a.personId

2-Masala
select e.name as Employee
from Employee e
inner join Employee m
on e.managerid = m.id
where e.salary>m.salary


3-Masala
select email from person group By email having count(1)>1

4-Masala
select c.name as Customers
from orders o
right join customers as c on o.customerId = c.id
where o.id is null

5-Masala
with c as(select
*,
row_number() over(partition by email order by id) as rn
from Person)
delete p
from person p
inner join c on c.id = p.id
where c.rn > 1


6-Masala
select w.id
from weather as w
left join weather as h
on w.recorddate = dateadd(day,1,h.recorddate)
where h.temperature < w.temperature
7-Masala
select E.name,
b.bonus
from Employee as E
left join bonus as b on e.empId = b.empId
where b.bonus < 1000 or b.bonus is null

8-Masala
select name

from customer
where referee_id != 2 or referee_id is null

9-Masala.

with cte as(
select count(1) as cnt from orders group by customer_number
),
m as(
select max(cnt) as max from cte
)
select customer_number
from orders as o, m
group by customer_number, m.max
having count(o.order_number) = m.max

10-Masala

select name, population, area

from world where population >=25000000 or area >=3000000;

11-Masala

select class
from courses group by class
having count(student) >= 5
12-Masala.
with cte as(
select distinct o.sales_id from orders as o left join company as c on c.com_id = o.com_id where c.name = 'RED'
)
select s.name
from salesperson as s
left join cte on s.sales_id = cte.sales_id
where cte.sales_id is null
13-masala
select *,
case
when x+y > z and x+z>y and z+y>x then 'Yes' else 'No'
end triangle
from Triangle

14-Masala

with cte as(
select num
from MyNumbers
group by num
having count(num)= 1

union all

select null as num

)
select top 1 num
from cte
order by num desc

15-Masala.

select *
from Cinema where description not like '%boring%' and id %2 = 1
order by rating desc

16-Masala.

update salary

set sex = case when sex = 'f' then 'm' else 'f' end

17-Masala.

select actor_id,
director_id
from ActorDirector
group by actor_id, director_id
having count(timestamp) >= 3

18-Masala.

select p.product_name, s.year, s.price
from product as p
join Sales as s on p.product_id = s.product_id

19-Masala.

select p.project_id,
ROUND(SUM(e.experience_years)*1.0/COUNT(e.employee_id), 2) as average_years
from Employee as e
join Project as p on e.employee_id = p.employee_id
group by p.project_id

20-Masala

with cte as (
select product_id from Sales
where product_id not in (
select product_id from Sales
where not (year(sale_date)=2019
and month(sale_date) between 0 and 4)
)
group by product_id
)

select p.product_id,

p.product_name
from product p
join cte s on p.product_id = s.product_id

21-Masala

select player_id, min(event_date) as first_login
from Activity
group by player_id;

22-Masala

select activity_date as day, count(distinct user_id) as active_users
from Activity
where activity_date between '2019-06-28' And '2019-07-27'
group by activity_date

23-Masala.

select distinct author_id as id
from Views
where author_id = viewer_id
order by id

24-Masala.

25-Masala.

select
query_name,
round(sum(rating * 1.0 /position) * 1.0 /count(1), 2) as quality,
round(sum(case when rating < 3 then 1 else 0 end) * 100.0 / count(1), 2) as poor_query_percentage
from queries
group by
query_name

26-Masala.