626. Exchange Seats

https://leetcode.com/problems/exchange-seats/description/

From the discussion section of LeetCode:
select
if(id < (select count(*) from seat), if(id mod 2=0, id-1, id+1), if(id mod 2=0, id-1, id)) as id, student
from seat
order by id;

--------

/* get all the even numbered rows as odd numbered rows */ SELECT s1.id - 1 as id, s1.student FROM Seat s1 WHERE s1.id MOD 2 = 0 UNION /* get all the odd numbered rows as even numbered rows */ SELECT s2.id + 1 as id, s2.student FROM Seat s2 WHERE s2.id MOD 2 = 1 AND s2.id != (SELECT MAX(id) FROM Seat) /* Just don't get the last row as we will handle it in the next UNION */ UNION /* get the last row if odd and don't change the id value */ SELECT s3.id, s3.student FROM Seat s3 WHERE s3.id MOD 2 = 1 AND s3.id = (SELECT MAX(id) FROM Seat) /* Order the result by id */ ORDER BY id ASC;


-------

select id, case when id%2 = 0 then (select student from seat where id = (i.id-1) ) when id%2 != 0 and id<(select count(student) from seat) then (select student from seat where id = (i.id+1) ) else student end as student from seat i

176. Second Highest Salary

https://leetcode.com/problems/second-highest-salary/description/
select max(Salary) as SecondHighestSalary
from Employee
where Salary < (select max(Salary) from Employee)

select (
  select distinct Salary from Employee order by Salary Desc limit 1 offset 1
) as SecondHighestSalary

196. Delete Duplicate Emails

https://leetcode.com/problems/delete-duplicate-emails/description/
delete p1
from Person p1, Person p2
where p1.Email = p2.Email
and p1.Id > p2.Id

Solution 2.
delete from Person where id not in( select t.id from ( select min(id) as id from Person group by email ) t )

596. Classes More Than 5 Students

https://leetcode.com/problems/classes-more-than-5-students/description/
select class
from courses
group by class
having count(distinct student) > 4

197. Rising Temperature

https://leetcode.com/problems/rising-temperature/description/
select w1.Id
from Weather as w1, Weather as w2
where TO_DAYS(w1.DATE) = TO_DAYS(w2.DATE) + 1
and W1.Temperature > W2.Temperature;
or,
select w1.Id
from Weather as w1, Weather as w2
where DATEDIFF(w1.DATE,w2.DATE) = 1
and W1.Temperature > W2.Temperature;

183. Customers Who Never Order

https://leetcode.com/problems/customers-who-never-order/description/

select C.Name as Customers
from Customers as C
where C.Id not in (select Orders.CustomerId from Orders);

select C.Name as Customers
from Customers as C
left join Orders on C.Id = Orders.CustomerId
where Orders.CustomerId is null

select C.Name as Customers
from Customers as C

where not exists (select CustomerId from Orders where C.Id = Orders.CustomerId);

181. Employees Earning More Than Their Managers

https://leetcode.com/problems/employees-earning-more-than-their-managers/description/
select E.Name as Employee
from Employee as E, Employee as M
where E.ManagerId = M.Id and E.Salary > M.Salary;

175. Combine Two Tables

https://leetcode.com/problems/combine-two-tables/description/
select Person.FirstName, Person.LastName, Address.City, Address.State
from Person
left join Address
on Person.PersonId = Address.PersonId

182. Duplicate Emails

select Email
from Person
group by Email
having count(Email)>1;

SQL notes

SQL Tutorial:
https://www.w3schools.com/sql/default.asp