ACID: atomicity, consistency, isolation, durability.

**\	Id\	Email \	\	1 \	[email protected]\	\	2 \	[email protected]\	\	3 \	[email protected]\	**	Find all duplicate emails in a table named Person.
	*SELECT Email FROM Person GROUP BY Email HAVING COUNT() > 1;**

**\	Id \	Email \	\	1 \	[email protected] \	\	2 \	[email protected] \	\	3 \	[email protected] \	**	Delete all duplicate email entries in a table named Person, keeping only unique emails based on its smallestId.
1-J-3-J and 3-J-1-J (3-J is deleted)	DELETE P1 FROM Person P1 JOIN Person P2 ON P1.Email = P2.Email WHERE P1.Id > P2.Id;

** \	Id \	Name \	Salary \	ManagerId\	\	1 \	Joe \	70000 \	3 \	\	2 \	Henry \	80000 \	4 \	\	3 \	Sam \	60000 \	NULL \	\	4 \	Max \	90000 \	NUL \	**	Find employees earning more than manager.
	SELECT E1.Name AS Employee FROM Employee E1, Employee E2 WHERE E1.ManagerId = E2.Id AND E1.Salary > E2.Salary;
	SELECT E1.Name AS Employee FROM Employee E1 INNER JOIN Employee E2 ON E1.ManagerId = E2.Id WHERE E1.Salary > E2.Salary;

**Customers \	Id \	Name \	\	1 \	Joe \	\	2 \	Henry \	\	3 \	Sam \	\	4 \	Max \	**	**Orders \	Id \	CustomerId \	\	1 \	3 \	\	2 \	1 \	**	Find all customers who never order anything.
		SELECT C.Name AS Customers FROM Customers C WHERE C.IdNOT IN(SELECT CustomerId FROM Orders);
		SELECT A.Name AS Customers FROM Customers A LEFT JOIN Orders B on A.Id = B.CustomerId WHERE B.CustomerId is NULL;

**\	Id(INT)\	Date(DATE)\	Temp(INT)\	\	1 \	2015-01-01\	10 \	\	2 \	2015-01-02\	25 \	\	3 \	2015-01-03\	20 \	\	4 \	2015-01-04\	30 \	**	Find all dates' Ids with higher temperature compared to its previous (yesterday's) dates.
	SELECT wt1.Id FROM Weather wt1,Weather wt2 WHERE wt1.Temp > wt2.Temp AND TO_DAYS(wt1.DATE)-TO_DAYS(wt2.DATE)=1;

LIMIT OFFSET, COUNT (1st row offset is 0);// Limit 1 offset 1

**\	Id \	Salary \	\	1 \	100 \	\	2 \	200 \	\	3 \	300 \	**	Get the second highest salary from theEmployeetable
	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 offset 1) As SecondHighestSalary; //limit 1, 1
Nth Highest	CREATE FUNCTION getNthHighestSalary(N INT) RETURNS INTBEGINDECLARE M INT;SET M = N - 1;RETURN (SELECT DISTINCT Salary FROM Employee ORDER BY Salary DESC LIMIT M, 1);END

**\	Id \	Score \	\	1 \	3.50 \	\	2 \	3.65 \	\	3 \	4.00 \	\	4 \	3.85 \	\	5 \	4.00 \	\	6 \	3.65 \	**	**\	Score \	Rank \	\	4.00 \	1 \	\	4.00 \	1 \	\	3.85 \	2 \	\	3.65 \	3 \	\	3.65 \	3 \	\	3.50 \	4 \	**	Write a SQL query to rank scores. If there is a tie, both have same ranking, the next ranking number is the next consecutive int value.
		*SELECT Score, (SELECT count() FROM (SELECT distinct Score s FROM Scores) tmp WHERE s >= Score) Rank FROM Scores ORDER BY Score DESC;**

**\	Id \	Num \	\	1 \	1 \	\	2 \	1 \	\	3 \	1 \	\	4 \	2 \	\	5 \	1 \	\	6 \	2 \	**	Find all numbers that appear at least three times consecutively.
	select distinct l1.Num As ConsecutiveNums from Logs l1 Inner Join Logs l2 Inner Join Logs l3 on (l1.Num = l2.Num AND l2.num = l3.Num)Where l2.Id - l1.Id = 1 And l3.Id - l2.Id = 1;

**\	Id \	Name \	Salary \	DeptId\	\	1 \	Joe \	70000 \	1 \	\	2 \	Henry \	80000 \	2 \	\	3 \	Sam \	60000 \	2 \	\	4 \	Max \	90000 \	1 \	\	Id \	Name \	\	1 \	IT \	\	2 \	Sales \	**	Find employees who have the highest salary in each departments.
	SELECT D.Name AS Department ,E.Name AS Employee, E.Salary FROM Employee E, Department D,(SELECT DeptId, MAX(Salary) as max FROM Employee GROUP BY DeptId) T WHERE E.DeptId = T.DeptId AND E.Salary = T.max AND E.DepartmentId = D.id

**\	Id \	Name \	Salary \	DepartmentId \	\	1 \	Joe \	70000 \	1 \	\	2 \	Henry \	80000 \	2 \	\	3 \	Sam \	60000 \	2 \	\	4 \	Max \	90000 \	1 \	\	5 \	Janet \	69000 \	1 \	\	6 \	Randy \	85000 \	1 \	\	Id \	Name \	\	1 \	IT \	\	2 \	Sales \	**	**Find employees who earn the top three salaries in each of the department. \	Department \	Employee \	Salary \	\	IT \	Max \	90000 \	\	IT \	Randy \	85000 \	\	IT \	Joe \	70000 \	\	Sales \	Henry \	80000 \	\	Sales \	Sam \	60000 \	**
	SELECT D.Name AS Department, E.Name AS Employee, E.Salary AS SalaryFROM Employee E, Department DWHERE (SELECT COUNT(DISTINCT(Salary)) FROM EmployeeWHERE DepartmentId = E.DepartmentId AND Salary > E.Salary) < 3AND E.DepartmentId = D.IdORDER by E.DepartmentId, E.Salary DESC;

Foreign key: primary key of other table

CREATE TABLE table_name (column_1 datatype Primary Key, column_2 datatype, ...);

ALTER TABLE table_name ADD column datatype;

INSERT INTO table_name (column_1, column_2) VALUES (value_1, 'value_2');

DELETE FROM table_name WHERE some_column = some_value;

UPDATE table_name SET some_column = some_value WHERE some_column = some_value;

	**\	Id \	Client_Id \	Driver_Id \	City_Id \	Status \	Request_at\	\	1 \	1 \	10 \	1 \	completed \	2013-10-01\	\	2 \	2 \	11 \	1 \	cancelled_by_driver\	2013-10-01\	\	3 \	3 \	12 \	6 \	completed \	2013-10-01\	\	4 \	4 \	13 \	6 \	cancelled_by_client\	2013-10-01\	\	5 \	1 \	10 \	1 \	completed \	2013-10-02\	\	6 \	2 \	11 \	6 \	completed \	2013-10-02\	\	7 \	3 \	12 \	6 \	completed \	2013-10-02\	\	8 \	2 \	12 \	12 \	completed \	2013-10-03\	\	9 \	3 \	10 \	12 \	completed \	2013-10-03\	\	10 \	4 \	13 \	12 \	cancelled_by_driver\	2013-10-03\	**
**\	Users_Id \	Banned \	Role \	\	1 \	No \	client \	\	2 \	Yes \	client \	\	3 \	No \	client \	\	4 \	No \	client \	\	10 \	No \	driver \	\	11 \	No \	driver \	\	12 \	No \	driver \	\	13 \	No \	driver \	**	**Find the cancellation rate of requests made by unbanned clients between Oct 1, 2013 and Oct 3, 2013. Expected Result: \	Day \	Cancellation Rate \	\	2013-10-01 \	0.33 \	\	2013-10-02 \	0.00 \	\	2013-10-03 \	0.50 \	**
	SELECT Request_at as Day,ROUND(COUNT(IF(Status != 'completed', TRUE, NULL)) / COUNT(*), 2) AS 'Cancellation Rate' FROM TripsWHERE (Request_at BETWEEN '2013-10-01' AND '2013-10-03')AND Client_id NOT IN (SELECT Users_Id FROM Users WHERE Banned = 'Yes')GROUP BY Request_at;

http://stackoverflow.com/questions/26552976/sql-how-to-select-the-most-recent-timestamp-by-values
CREATE TABLE events (
  id INT,
  sensor INT,
  event_type INT,
  time datetime
  );

INSERT INTO events VALUES (0,2,4,'2012-06-08 12:13:14');
INSERT INTO events VALUES (1,3,4,'2012-06-08 13:13:14');
INSERT INTO events VALUES (2,2,4,'2012-06-08 12:15:14');
INSERT INTO events VALUES (3,1,6,'2012-06-08 15:13:14');

What is the "best" way to retrieve the most recent event added by sensor? so the result wold be like this (note that id 2 is displayed and not id 0 because id 2 is most recent:
The result will be like this:
ID   sensor
3      1
1      3
2      2

select * from events where time in
 (SELECT max (time) FROM events group by sensor_id, event_type)
Order by time desc;

**SELECT COUNT | AVG | MAX | MIN | SUM (column_name) | ROUND(column_name, integer)
FROM table_name GROUP BY column_name;

SELECT DISTINCT column_name(s) FROM table_name
WHERE column_name BETWEEN value_1 AND value_2 OR column_name LIKE ‘%a_bc%’

ORDER BY column_name ASC|DESC; default is ASC
LIKE: _ 1; % >=0; non-case sensitive

BETWEEN: filter the result set within a certain range. The values can be numbers, text or dates.

INNER JOIN, default JOIN, NULL values are used to fill in the columns from the right table.

SELECT column_name(s) FROM table_1 JOIN | OUTER | LEFT | RIGHT JOIN table_2
ON table_1.column_name = table_2.column_name;

My-SQL-Query-Examples

results matching ""

No results matching ""