175.
SELECT Person.FirstName, Person.LastName, Address.City, Address.State
FROM Person LEFT JOIN Address
ON Person.PersonId = Address.PersonId;
176.
SELECT MAX(Salary) AS SecondHighestSalary
FROM Employee
WHERE Salary
NOT IN (SELECT MAX(Salary) FROM Employee);
SELECT
( SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT 1, 1)
SecondHighestSalary;
177.
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
SET N=N-1;
RETURN (
SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT N,1
);
END
178. (*)
SELECT
S1.Score,
Count(DISTINCT S2.Score) rank
FROM
Scores S1 INNER JOIN
Scores S2
ON S1.Score <= S2.Score
GROUP BY
S1.id
ORDER BY
S1.Score DESC;
180.
SELECT
DISTINCT Num ConsecutiveNums
FROM
Logs L1
WHERE
Num=(SELECT Num FROM Logs WHERE Id=L1.Id+1)
AND
Num=(SELECT Num FROM Logs WHERE Id=L1.Id+2);
181.
SELECT E1.Name Employee
FROM Employee E1 INNER JOIN Employee E2
ON E1.ManagerId = E2.Id
WHERE E1.Salary > E2.Salary;
182.
SELECT Email
FROM Person
GROUP BY Email
HAVING Count(Email) > 1;
183.
SELECT Name Customers
FROM Customers
WHERE Id NOT IN
(SELECT CustomerId FROM Orders);
SELECT C1.Name Customers
FROM Customers C1 LEFT JOIN Orders O1
ON C1.Id = O1.CustomerId
WHERE O1.CustomerId IS null;