In the example provided, we will store a list of the world's largest cities in a database.
For the list of cities:
From this, we should take the following actions:
To get started, we can use pg-sql.com
To create a table:
CREATE TABLE cities ( name VARCHAR(50), country VARCHAR(50), population INTEGER, area INTEGER ); -- CREATE successful!
Note:
cities
are lowercase.INSERT INTO cities (name, country, population, area) VALUES ('Tokyo', 'Japan', 38505000, 8223); -- INSERT successful! -- 1 row(s) inserted
You can insert multiple value:
INSERT INTO cities (name, country, population, area) VALUES ('Dehli', 'India', 28125000, 2240), ('Shanghai', 'China', 22125000, 4015), ('Sao Paulo', 'Brazil', 20935000, 3043); -- INSERT successful! -- 3 row(s) inserted
SELECT * FROM cities;
The result:
name | country | population | area |
---|---|---|---|
Tokyo | Japan | 38505000 | 8223 |
Dehli | India | 28125000 | 2240 |
Shanghai | China | 22125000 | 4015 |
Sao Paulo | Brazil | 20935000 | 3043 |
This is about us performing calculations before we pull it out of the table.
For example, what if we want population density (population / area
)?
We can do so like this:
SELECT name, population / area AS density FROM cities;
This gives us the following:
name | density |
---|---|
Tokyo | 4682 |
Dehli | 12555 |
Shanghai | 5510 |
Sao Paulo | 6879 |
Some helpers:
Operator/Function | Does |
---|---|
|| | Join two strings |
CONCAT() | Join two strings |
LOWER() | Gives a lower case string |
LENGTH() | Number of chars in string |
UPPER() | Uppercase |
A list of functions can be found on the official website
Example:
SELECT UPPER(CONCAT(name, ', ', country)) as location FROM cities;