You can not select more than 25 topics
Topics must start with a letter or number, can include dashes ('-') and can be up to 35 characters long.
40 lines
1.0 KiB
40 lines
1.0 KiB
-- CREATE
|
|
CREATE TABLE CITY
|
|
(
|
|
ID INT PRIMARY KEY,
|
|
NAME VARCHAR(255) NOT NULL,
|
|
COUNTRYCODE VARCHAR(3) NOT NULL,
|
|
DISTRICT VARCHAR(255) NOT NULL,
|
|
POPULATION INT NOT NULL
|
|
);
|
|
|
|
-- INSERT
|
|
INSERT INTO CITY (ID, NAME, COUNTRYCODE, DISTRICT, POPULATION)
|
|
VALUES (1, 'Tokyo', 'JPN', 'Tokyo', 13929286),
|
|
(2, 'Delhi', 'IN', 'Delhi', 16787941),
|
|
(3, 'Shanghai', 'CN', 'Shanghai', 24183300),
|
|
(4, N'São Paulo', 'BR', N'São Paulo', 12106920),
|
|
(5, 'Mumbai', 'IN', 'Maharashtra', 12442373),
|
|
(6, 'Cairo', 'EG', 'Cairo', 9500000),
|
|
(7, 'Dhaka', 'BD', 'Dhaka', 8906039),
|
|
(8, 'Mexico City', 'MX', 'Mexico City', 9209944),
|
|
(9, 'Beijing', 'CN', 'Beijing', 21542000),
|
|
(10, 'Osaka', 'JPN', 'Osaka Prefecture', 8839469);
|
|
|
|
-- QUERY
|
|
|
|
SELECT ROUND(AVG(POPULATION), 0) AS average_population
|
|
FROM CITY;
|
|
|
|
SELECT SUM(POPULATION)
|
|
FROM CITY
|
|
WHERE COUNTRYCODE = 'JPN';
|
|
|
|
|
|
SELECT MAX(POPULATION) - MIN(POPULATION) AS population_difference
|
|
FROM CITY;
|
|
|
|
|
|
|
|
DROP TABLE CITY;
|