Database

# Write a SQL solution to output big countries' name, population and area.
select name, population, area
from World
where area > 3000000 or population > 25000000;
--------------------------------------------------------------------------
# Given a table salary, such as the one below, that has m=male and f=female values.
UPDATE salary
SET sex = IF(sex='f','m','f');
--------------------------------------------------------------------------
# Please write a SQL query to output movies with an odd numbered ID and a description that is not 'boring'. Order the result by rating.
select id, movie, description, rating
from cinema
where MOD(id, 2)=1 and description!='boring'
order by rating DESC;
--------------------------------------------------------------------------
# Write a SQL query to find all duplicate emails in a table named Person.

select Email
from (
    select Email, count(*) as nums
    from Person
    group by Email) as stat
where nums > 1;
--------------------------------------------------------------------------
# Write a SQL query for a report that provides the following information for each person in the Person table, regardless if there is an address for each of those people:
select FirstName, LastName, City, State
from Person left join Address
on Person.PersonId = Address.PersonId;
--------------------------------------------------------------------------
#  write a SQL query that finds out employees who earn more than their managers
/*
SELECT
    a.Name AS 'Employee'
FROM
    Employee AS a,
    Employee AS b
WHERE
    a.ManagerId = b.Id
        AND a.Salary > b.Salary;*/

SELECT
     a.NAME AS Employee
FROM Employee AS a JOIN Employee AS b
     ON a.ManagerId = b.Id
     AND a.Salary > b.Salary
;
--------------------------------------------------------------------------
# Write a SQL query to find all customers who never order anything.
select Name as 'Customers'
from Customers
where Customers.Id not in (
select CustomerId as 'Id' from Orders
);
--------------------------------------------------------------------------
# Please list out all classes which have more than or equal to 5 students.
select class
from courses
group by class
having count(distinct (student)) >=5;
--------------------------------------------------------------------------
# write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
select weather.id as 'Id'
from weather
inner join weather w on DATEDIFF(weather.RecordDate, w.RecordDate) = 1
and weather.Temperature > w.Temperature;
--------------------------------------------------------------------------
# Write a SQL query to delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallest Id.

delete p1
from Person p1, Person p2
where p1.Email = p2.Email and p1.Id > p2.Id;
--------------------------------------------------------------------------
# Write a SQL query to get the second highest salary from the Employee table.
select
    (SELECT DISTINCT
            Salary
        FROM
            Employee
        ORDER BY Salary DESC
        LIMIT 1 OFFSET 1) AS SecondHighestSalary
;
--------------------------------------------------------------------------
# Mary wants to change seats for the adjacent students.
# Write your MySQL query statement below
SELECT
    (CASE
        WHEN MOD(id, 2) != 0 AND counts != id THEN id + 1
        WHEN MOD(id, 2) != 0 AND counts = id THEN id
        ELSE id - 1
    END) AS id,
    student
from
    seat,
    (SELECT
        COUNT(*) AS counts
    FROM seat) AS seat_counts
ORDER BY id ASC;
--------------------------------------------------------------------------
# Write a SQL query to rank scores. If there is a tie between two scores, both should have the same ranking. Note that after
# a tie, the next ranking number should be the next consecutive integer value. In other words, there should be no "holes"
# between ranks.
SELECT
  Score,
  @rank := @rank + (@prev <> (@prev := Score)) Rank
FROM
  Scores,
  (SELECT @rank := 0, @prev := -1) init
ORDER BY Score desc

--------------------------------------------------------------------------
# Write a SQL query to find all numbers that appear at least three times consecutively.

SELECT DISTINCT
    l1.Num AS ConsecutiveNums
FROM
    Logs l1,
    Logs l2,
    Logs l3
WHERE
    l1.Id = l2.Id + 1
    AND l2.Id = l3.Id + 1
    AND l1.Num = l2.Num
    AND l2.Num = l3.Num
;
--------------------------------------------------------------------------
# Write a SQL query to find employees who have the highest salary in each of the departments. For the above tables, Max has the highest salary in the IT department and Henry has the highest salary in the Sales department.
SELECT
    Department.name AS 'Department',
    Employee.name AS 'Employee',
    Salary
FROM
    Employee
        JOIN
    Department ON Employee.DepartmentId = Department.Id
WHERE
    (Employee.DepartmentId , Salary) IN
    (   SELECT
            DepartmentId, MAX(Salary)
        FROM
            Employee
        GROUP BY DepartmentId
    )
;
--------------------------------------------------------------------------
# Write a SQL query to get the nth highest salary from the Employee table.

CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
declare M int;
Set M=N-1;
  RETURN (
      # Write your MySQL query statement below.
      select distinct Salary from Employee order by Salary desc limit M, 1
  );
END
--------------------------------------------------------------------------
# Write a SQL query to find employees who earn the top three salaries in each of the department. For the above tables, your SQL query should return the following rows.
SELECT
    d.Name AS 'Department', e1.Name AS 'Employee', e1.Salary
FROM
    Employee e1
        JOIN
    Department d ON e1.DepartmentId = d.Id
WHERE
    3 > (SELECT
            COUNT(DISTINCT e2.Salary)
        FROM
            Employee e2
        WHERE
            e2.Salary > e1.Salary
                AND e1.DepartmentId = e2.DepartmentId
        )
;
--------------------------------------------------------------------------