﻿ SQL Challenges-1: Rising Sulfur Dioxide - 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
AND p1.so2_amt < p2.so2_amt
``````

SQL Code Editor:

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