Scribd
Upload
12 views6 pages

Dbms Assignment 1

The document outlines an SQL assignment by Madhura Khalekar, which involves creating a schema and a table named 'pollutionstats' to store air quality data for various cities. It includes inserting ten records, creating an index on the 'category' column, and defining a view for cities with an AQI greater than 150. Additionally, it details ten SQL queries executed on the table to retrieve and analyze the pollution data.

Uploaded by

36Atharva Naik
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
12 views6 pages

Dbms Assignment 1

The document outlines an SQL assignment by Madhura Khalekar, which involves creating a schema and a table named 'pollutionstats' to store air quality data for various cities. It includes inserting ten records, creating an index on the 'category' column, and defining a view for cities with an AQI greater than 150. Additionally, it details ten SQL queries executed on the table to retrieve and analyze the pollution data.

Uploaded by

36Atharva Naik
Copyright
© © All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

DBMS ASSIGNMENT 1:

“SQL Queries”:
To Create a Schema, a Table, a View, an Index. Consequently run 10
Queries on it.

Name: Madhura Khalekar


Roll no: 25
TE AIDS

To create a table:
USE pollution_by_cities;
CREATE TABLE pollutionstats (
pollutionstats_id INT PRIMARY KEY,
city VARCHAR(100),
country VARCHAR(50),
AQI INT,
category VARCHAR(50)
);
To Insert Values:
INSERT INTO pollutionstats (pollutionstats_id,city,country,AQI,category)
VALUES
(1, 'Kinhasa', 'Democratic Republic of Congo' , 155, 'Unhealthy'),
(2, 'Manama', 'Bahrain' , 152, 'Unhealthy'),
(3, 'Jakarta', 'Indonesia' , 152, 'Unhealthy'),
(4, 'Dhaka', 'Bangladesh' , 117, 'Unhealthy for sensitive groups'),
(5, 'Dubai', 'United Arab Emirates' , 117, 'Unhealthy for sensitive groups'),
(6, 'Sao Paulo', 'Brazil' , 108, 'Unhealthy for sensitive groups'),
(7, 'Jerusalem', 'Israel' , 103, 'Unhealthy for sensitive groups'),
(8, 'Shanghai', 'China' , 99, 'Moderate'),
(9, 'Chengdu', 'China' , 98, 'Moderate'),
(10, 'Tel Aviv', 'Israel' , 86, 'Moderate');

To Create an Index:
CREATE INDEX idx_category ON pollutionstats(category);

To create a View:
CREATE VIEW most_polluted AS
SELECT city, AQI, category
FROM pollutionstats
WHERE AQI > 150;
Running 10 Queries as follows:

1) SELECT * FROM pollutionstats;

2) SELECT COUNT(*) FROM pollutionstats;

3) SELECT * FROM pollutionstats WHERE AQI < 100;

4) SELECT * FROM pollutionstats WHERE country = 'China';


5) SELECT * FROM pollutionstats WHERE pollutionstats_id <= 5;

6) SELECT * FROM pollutionstats WHERE category = 'Unhealthy for sensitive


groups';

7) SELECT COUNT(*) FROM pollutionstats WHERE AQI > 100;

8) SELECT AVG(AQI) AS avg_aqi FROM pollutionstats;


9) SELECT * FROM pollutionstats ORDER BY AQI DESC;

10) SELECT * FROM most_polluted;

List of all executed statements:

You might also like