w3resource

SQL Challenges-1: Rising Sulfur Dioxide

SQL Challenges-1: Exercise-8 with Solution

From the following table, write a SQL query to find all dates' city ID with higher pollution compared to its previous dates (yesterday). Return city ID, date and pollution.

Input:

Table: so2_pollution

Structure:

FieldTypeNullKeyDefaultExtra
city_idint(11)YES
datedateYES
so2_amtint(11)YES

Data:

city_iddateso2_amt
7012015-10-155
7022015-10-167
7032015-10-179
7042018-10-1815
1052015-10-1914

Sample Solution:

SQL Code:

CREATE TABLE IF NOT EXISTS so2_pollution (city_id int, date date, so2_amt int);
TRUNCATE TABLE so2_pollution;
INSERT INTO so2_pollution (city_id, date, so2_amt) VALUES ('701', '2015-10-15', '5');
INSERT INTO so2_pollution (city_id, date, so2_amt) VALUES ('702', '2015-10-16', '7');
INSERT INTO so2_pollution (city_id, date, so2_amt) VALUES ('703', '2015-10-17', '9');
INSERT INTO so2_pollution (city_id, date, so2_amt) VALUES ('704', '2018-10-18', '15');
INSERT INTO so2_pollution (city_id, date, so2_amt) VALUES ('705', '2015-10-19', '14');
SELECT * FROM so2_pollution;
 
SELECT so2_pollution.city_id AS 'City ID'
FROM so2_pollution
        JOIN
so2_pollution p ON DATEDIFF(so2_pollution.date, p.date) = 1
        AND so2_pollution.so2_amt > p.so2_amt;

Sample Output:

City ID|
-------|
    702|
    703|

Solution-1:

SELECT p2.city_id FROM so2_pollution p1, so2_pollution p2
WHERE p2.date = adddate(p1.date,1)
AND p1.so2_amt < p2.so2_amt

SQL Code Editor:


Contribute your code and comments through Disqus.

Previous: Remove Duplicate Emails.
Next: Highest Sale Amount.



SQL: Tips of the Day

How to select the nth row in a SQL database table?

Basically, PostgreSQL and MySQL supports the non-standard:

SELECT...
LIMIT y OFFSET x 

Oracle, DB2 and MSSQL supports the standard windowing functions:

SELECT * FROM (
  SELECT
    ROW_NUMBER() OVER (ORDER BY key ASC) AS rownumber,
    columns
  FROM tablename
) AS foo
WHERE rownumber <= n

Database: SQL

Ref: https://bit.ly/3zPxcD8