A-A+

刷下leetcode的数据库

2016年12月06日 数据库 暂无评论 阅读 88 views 次
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       |
	+-----------+
	我的答案:
	select Name as Customers from Customers
	where Customers.id not in (select CustomerId from Orders)


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 |
	+---------+.
	我的答案:
	select  Email from Person group by Email having count(*) >1

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      |
	+----------+
	我的答案:
	select a.Name as Employee  from Employee a inner join Employee b
	on a.ManagerId = b.Id and a.Salary >b.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
	我的答案:
	select FirstName, LastName, City, State from Person left join Address
	on Person.PersonId = Address.PersonId

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) | Date(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 |
	+----+
	我的答案:
	select a.Id from Weather a
	where a.Temperature >
	(select b.Temperature  from Weather b
	where b.Date = DATE_SUB(a.Date,INTERVAL 1 day)) 

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  |
	+----+------------------+
	我的答案:
	delete a from Person a,Person b
	where a.Email = b.Email
	and a.id > b.id

184. Department Highest Salary
	The Employee table holds all employees. Every employee has an Id, a salary, and
        there is also a column for the department Id.

	+----+-------+--------+--------------+
	| Id | Name  | Salary | DepartmentId |
	+----+-------+--------+--------------+
	| 1  | Joe   | 70000  | 1            |
	| 2  | Henry | 80000  | 2            |
	| 3  | Sam   | 60000  | 2            |
	| 4  | Max   | 90000  | 1            |
	+----+-------+--------+--------------+
	The Department table holds all departments of the company.

	+----+----------+
	| Id | Name     |
	+----+----------+
	| 1  | IT       |
	| 2  | Sales    |
	+----+----------+
	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.

	+------------+----------+--------+
	| Department | Employee | Salary |
	+------------+----------+--------+
	| IT         | Max      | 90000  |
	| Sales      | Henry    | 80000  |
	+------------+----------+--------+
	我的答案:
	select a.Name as Department,b.Name as Employee,b.Salary from Department a,Employee b
	where a.Id = b.DepartmentId
	and b.Salary = (select MAX(c.Salary) from Employee c where a.Id = c.DepartmentId)

 

标签:

给我留言