刷题sql

2019-01-16 本文已影响0人 mumupluto

t 175. Combine Two Tables
t 176. Second Highest Salary
t 181. Employees Earning More Than Their Managers
t 182. Duplicate Emails
t 183. Customers Who Never Order
t 196. Delete Duplicate Emails ???????????????????
t 197. Rising Temperature
t 595. Big Countries
t 596. Classes More Than 5 Students
t 620. Not Boring Movies
t 627. Swap Salary

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

Solution 1

Select FirstName, LastName, City, State
From Person P left join Address A
On P.PersonId = A.PersonId

'''
# regardless if there is an address for each of those people
# so left join would solve this
'''

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                 |
+---------------------+

My solution

Select max(Salary) as SecondHighestSalary
From Employee
Where Salary < (Select max(Salary) From Employee)

# should use () !!!

Good solution

SELECT DISTINCT
    Salary AS SecondHighestSalary
FROM
    Employee
ORDER BY Salary DESC
LIMIT 1 OFFSET 1

However, this solution will be judged as 'Wrong Answer' if there is no such second highest salary since there might be only one record in this table. To overcome this issue, we can take this as a temp table.

Select 
    (Select distinct Salary 
    From Employee
    Order by Salary Desc
    Limit 1 offset 1) as SecondHighestSalary

Another way to solve the 'NULL' problem is to use IFNULL funtion as below.

SELECT
    IFNULL(
      (SELECT DISTINCT Salary
       FROM Employee
       ORDER BY Salary DESC
        LIMIT 1 OFFSET 1),
    NULL) AS SecondHighestSalary

'''
IFNULL(expr1,expr2) 
如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。
IFNULL()返回一个数字或字符串值，取决于它被使用的上下文环境。 
'''

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      |
+----------+

Solution

Select E.Name as Employee
From Employee E left join Employee Em on E.ManagerId = Em.Id
Where E.Salary > Em.Salary

'''
TableA A left join TableB B on A.sth = B.sth
'''

182. Duplicate Emails

Write a SQL query to find all duplicate emails in a table named Person.

+----+---------+
| Id | Email   |
+----+---------+
| 1  | a@b.com |
| 2  | c@d.com |
| 3  | a@b.com |
+----+---------+
For example, your query should return the following for the above table:

+---------+
| Email   |
+---------+
| a@b.com |
+---------+
Note: All emails are in lowercase.

Solution 1: check Other's way

Select DISTINCT Email
From Person
Group By Email
Having count(Id) > 1

Solution 2: My method then

Select distinct P.Email as Email
From Person P left join Person Ps on P.Email = Ps.Email
Where P.Id != Ps.Id

183. Customers Who Never Order

Suppose that a website contains two tables, the Customers table and the Orders table. 
Write a SQL query to find all customers who never order anything.

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       |
+-----------+

Solution 1

Select C.Name as Customers
From Customers C left join Orders O on C.Id = O.CustomerId
Group by C.Id
Having count(O.CustomerId) = 0

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.

See the solution

Delete P
From Person P, Person Ps
Where P.Email = Ps.Email and P.Id > Ps.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 |
+----+

Solution 1

Select Wth.Id
From Weather W left join Weather Wth 
on DATEDIFF(W.RecordDate, Wth.RecordDate) =  - 1
Where W.Temperature < Wth.Temperature

'''
 you cannot use W.RecordDate = Wth.RecordDate -1 because they are "date" type
'''

595. Big Countries

There is a table World

+-----------------+------------+------------+--------------+---------------+
| name            | continent  | area       | population   | gdp           |
+-----------------+------------+------------+--------------+---------------+
| Afghanistan     | Asia       | 652230     | 25500100     | 20343000      |
| Albania         | Europe     | 28748      | 2831741      | 12960000      |
| Algeria         | Africa     | 2381741    | 37100000     | 188681000     |
| Andorra         | Europe     | 468        | 78115        | 3712000       |
| Angola          | Africa     | 1246700    | 20609294     | 100990000     |
+-----------------+------------+------------+--------------+---------------+

A country is big if it has an area of bigger than 3 million square km 
or a population of more than 25 million.
Write a SQL solution to output big countries' name, population and area.
For example, according to the above table, we should output:

+--------------+-------------+--------------+
| name         | population  | area         |
+--------------+-------------+--------------+
| Afghanistan  | 25500100    | 652230       |
| Algeria      | 37100000    | 2381741      |
+--------------+-------------+--------------+

Solution 1

Select name, population, area
From World
Where population > 25000000 or area > 3000000

596. Classes More Than 5 Students

There is a table courses with columns: student and class

Please list out all classes which have more than or equal to 5 students.

For example, the table:

+---------+------------+
| student | class      |
+---------+------------+
| A       | Math       |
| B       | English    |
| C       | Math       |
| D       | Biology    |
| E       | Math       |
| F       | Computer   |
| G       | Math       |
| H       | Math       |
| I       | Math       |
+---------+------------+

Should output:
+---------+
| class   |
+---------+
| Math    |
+---------+
Note:
The students should not be counted duplicate in each course.

Solution 1

Select class
From courses
Group By class
Having count(distinct student) >= 5

'''
# add distinct student in case there is duplicate data
'''

620. Not Boring Movies

X city opened a new cinema, many people would like to go to this cinema. 
The cinema also gives out a poster indicating the movies’ ratings and descriptions.
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.

For example, table cinema:

+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   1     | War       |   great 3D   |   8.9     |
|   2     | Science   |   fiction    |   8.5     |
|   3     | irish     |   boring     |   6.2     |
|   4     | Ice song  |   Fantacy    |   8.6     |
|   5     | House card|   Interesting|   9.1     |
+---------+-----------+--------------+-----------+
For the example above, the output should be:
+---------+-----------+--------------+-----------+
|   id    | movie     |  description |  rating   |
+---------+-----------+--------------+-----------+
|   5     | House card|   Interesting|   9.1     |
|   1     | War       |   great 3D   |   8.9     |
+---------+-----------+--------------+-----------+

Solution 1

Select id, movie, description, rating
From cinema
Where id % 2 = 1 and description != "boring" 
Order By rating DESC

'''
## Order By xxx DESC
## id % 2 = 1   -----> mod(id, 2) = 1xi
'''

627. Swap Salary

# Given a table salary, such as the one below, 
that has m=male and f=female values. 
Swap all f and m values (i.e., change all f values to m and vice versa) 
with a single update query and no intermediate temp table.

For example:
| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | m   | 2500   |
| 2  | B    | f   | 1500   |
| 3  | C    | m   | 5500   |
| 4  | D    | f   | 500    |

After running your query, the above salary table should have the following rows:

| id | name | sex | salary |
|----|------|-----|--------|
| 1  | A    | f   | 2500   |
| 2  | B    | m   | 1500   |
| 3  | C    | f   | 5500   |
| 4  | D    | m   | 500    |

Solution 1

update salary set sex = If( sex = "m", "f", "m")

Solution 2

update salary set sex = Case when sex = "m" then "f" else "m" end

'''
# Reflections: there are two ways to solve this problem
## 1) use "if": IF([condition], [T value], [F value])

## 2) use "case when": Case when [condition] THEN [T value] ELSE [F value] END
'''

刷题sql

175. Combine Two Tables

176. Second Highest Salary

181. Employees Earning More Than Their Managers

182. Duplicate Emails

183. Customers Who Never Order

196. Delete Duplicate Emails

197. Rising Temperature

595. Big Countries

596. Classes More Than 5 Students

620. Not Boring Movies

627. Swap Salary

猜你喜欢

热点阅读