175 | Combine Two Tables |
Table: Person
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| PersonId | int |
| FirstName | varchar |
| LastName | varchar |
+-------------+---------+
PersonId is the primary key column for this table.
Table: Address
+-------------+---------+
| Column Name | Type |
+-------------+---------+
| AddressId | int |
| PersonId | int |
| City | varchar |
| State | varchar |
+-------------+---------+
AddressId is the primary key column for this table.
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:
FirstName, LastName, City, State
开始直接用了where WA了 看了一下要输出null的情况 也就是address表可能对应不上person表 所以用左连接来做
# Write your MySQL query statement below
select firstname,lastname,city,state
from person left join
address on person.personid=address.personid
176 | Second Highest Salary |
Write a SQL query to get the second highest salary from the Employee
table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the query should return 200
as the second highest salary. If there is no second highest salary, then the query should return null
.
+---------------------+
| SecondHighestSalary |
+---------------------+
| 200 |
+---------------------+
有点坑 又是null的条件 这是题解 IFNULL语句进行判断
SELECT IFNULL(
(SELECT DISTINCT Salary
FROM Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1),
NULL) AS SecondHighestSalary
181 | Employees Earning More Than Their Managers |
The Employee
table holds all employees including their managers. Every employee has an Id, and there is also a column for the manager Id.
+----+-------+--------+-----------+
| Id | Name | Salary | ManagerId |
+----+-------+--------+-----------+
| 1 | Joe | 70000 | 3 |
| 2 | Henry | 80000 | 4 |
| 3 | Sam | 60000 | NULL |
| 4 | Max | 90000 | NULL |
+----+-------+--------+-----------+
Given the Employee
table, write a SQL query that finds out employees who earn more than their managers. For the above table, Joe is the only employee who earns more than his manager.
+----------+
| Employee |
+----------+
| Joe |
+----------+
# Write your MySQL query statement below
select X.Name as 'Employee'
from Employee X,Employee Y
where X.salary>Y.salary and X.ManagerID=y.id
177 | Nth Highest Salary |
Write a SQL query to get the nth highest salary from the Employee
table.
+----+--------+
| Id | Salary |
+----+--------+
| 1 | 100 |
| 2 | 200 |
| 3 | 300 |
+----+--------+
For example, given the above Employee table, the nth highest salary where n = 2 is 200
. If there is no nth highest salary, then the query should return null
.
+------------------------+
| getNthHighestSalary(2) |
+------------------------+
| 200 |
+------------------------+
Seen this question in a real interview before?
CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INT
BEGIN
DECLARE M INT;
SET M=N-1;
RETURN (
# Write your MySQL query statement below.
select ifnull((
select distinct salary
from employee
order by salary desc
limit m,1),null)
);
END
183 | Customers Who Never Order |
Table: Customers
.
+----+-------+
| Id | Name |
+----+-------+
| 1 | Joe |
| 2 | Henry |
| 3 | Sam |
| 4 | Max |
+----+-------+
Table: Orders
.
+----+------------+
| Id | CustomerId |
+----+------------+
| 1 | 3 |
| 2 | 1 |
+----+------------+
Using the above tables as example, return the following:
+-----------+
| Customers |
+-----------+
| Henry |
| Max |
+-----------+
# Write your MySQL query statement below
SELECT NAME AS 'Customers'
FROM CUSTOMERS
WHERE CUSTOMERS.ID NOT IN
(SELECT CUSTOMERID
FROM ORDERS,CUSTOMERS
WHERE CUSTOMERID=CUSTOMERS.ID)
196 | Delete Duplicate Emails |
Write a SQL query to delete all duplicate email entries in a table named Person
, keeping only unique emails based on its smallest Id.
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
| 3 | john@example.com |
+----+------------------+
Id is the primary key column for this table.
For example, after running your query, the above Person
table should have the following rows:
+----+------------------+
| Id | Email |
+----+------------------+
| 1 | john@example.com |
| 2 | bob@example.com |
+----+------------------+
Note:
Your output is the whole Person
table after executing your sql. Use delete
statement.
# Write your MySQL query statement below
delete p1
from person p1,person p2
where p1.email=p2.email and p1.id>p2.id
197 | Rising Temperature |
Given a Weather
table, write a SQL query to find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
+---------+------------------+------------------+
| Id(INT) | RecordDate(DATE) | Temperature(INT) |
+---------+------------------+------------------+
| 1 | 2015-01-01 | 10 |
| 2 | 2015-01-02 | 25 |
| 3 | 2015-01-03 | 20 |
| 4 | 2015-01-04 | 30 |
+---------+------------------+------------------+
For example, return the following Ids for the above Weather
table:
+----+
| Id |
+----+
| 2 |
| 4 |
+----+
# Write your MySQL query statement below
SELECT P1.ID
FROM WEATHER P1,WEATHER P2
WHERE P1.TEMPERATURE>P2.TEMPERATURE AND DATEDIFF(P1.RecordDate,P2.RecordDate)=1
180 | Consecutive Numbers |
Write a SQL query to find all numbers that appear at least three times consecutively.
+----+-----+ | Id | Num | +----+-----+ | 1 | 1 | | 2 | 1 | | 3 | 1 | | 4 | 2 | | 5 | 1 | | 6 | 2 | | 7 | 2 | +----+-----+
For example, given the above Logs
table, 1
is the only number that appears consecutively for at least three times.
+-----------------+ | ConsecutiveNums | +-----------------+ | 1 | +-----------------+
# Write your MySQL query statement below
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