[SQL]Some Common SQL Query

on

Create a temperature table with these columns id, temperature, time, date. SQL Query to find second highest temperature from the table:
[code language=”sql”] select MAX(temperature) from temperature WHERE temperature NOT IN (select MAX(temperature) from temperature ); [/code]
Another way:
[code language=”sql”] SELECT `temperature` FROM `temperature` ORDER BY `temperature`.`temperature` DESC LIMIT 1,1 [/code]
SQL Query to find maximum temperature of per days:
[code language=”sql”] SELECT MAX(temperature), date FROM `temperature` GROUP BY date; [/code]
SQL Query to find average temperature of per days:
[code language=”sql”] SELECT AVG(temperature), date FROM `temperature` GROUP BY date; [/code]
SQL Query to find maximum temperature of year 2014 or current year:
[code language=”sql”]
SELECT MAX(temperature) FROM `temperature` WHERE date Between ‘2014-01-01’ and ‘2014-12-31’;
SELECT MAX(temperature) FROM `temperature` WHERE date like ‘2014%’;
SELECT MAX(temperature) FROM `temperature` where YEAR(date)=’2014′;
SELECT MAX(temperature), date FROM `temperature` where YEAR(date)=YEAR(CURDATE());
[/code]
SQL Query to find average temperature of night(between 6.00PM to 12.00AM)
[code language=”sql”]
SELECT AVG(temperature) FROM `temperature` WHERE time Between ’18:00:00′ and ’23:59:59′;
[/code]
SQL Query to find current date:
[code language=”sql”] SELECT CURDATE(); [/code]
SQL Query to find current year:
[code language=”sql”] SELECT YEAR(CURDATE()); [/code]

Leave a Reply

Your email address will not be published. Required fields are marked *