Name | Capital | Area | Population | Continent | Currency |
---|---|---|---|---|---|
Denmark | Copenhagen | 43094 | 5484000 | EU | DKK |
Estonia | Tallinn | 45226 | 1291170 | EU | EUR |
Finland | Helsinki | 337030 | 5244000 | EU | EUR |
Norway | Oslo | 324220 | 5009150 | EU | NOK |
Sweden | Stockholm | 449964 | 9555893 | EU | SEK |
… | … | … | … | … | … |
Countries(name,capital,area,population,continent,currency)
createdb countries
countries
.
dropdb countries
countries
and all data it
contains.
psql countries
psql (12.1)
Type "help" for help.
countries=#countries
and starts the SQL interpeter.
CREATE TABLE Countries( name TEXT, capital TEXT, area FLOAT, population INT, continent TEXT, currency TEXT );
INSERT INTO Countries VALUES ('Denmark', 'Copenhagen', 43094, 5484000, 'EU', 'DKK');
SELECT * FROM Countries;
SELECT * FROM Countries WHERE name='Sweden';
SELECT name,population
FROM Countries
WHERE capital='Kingston';
UPDATE Countries SET population=population+1 WHERE name='Finland';
DELETE FROM Countries WHERE name='East Germany';
DELETE FROM Countries WHERE area=0;
DELETE FROM Countries;
CREATE TABLE Countries( name TEXT, capital TEXT, area FLOAT, population INT, continent CHAR(2), currency CHAR(3) );
CREATE TABLE Countries( name TEXT NOT NULL, capital TEXT, area FLOAT NOT NULL, population INT NOT NULL, continent CHAR(2) NOT NULL, currency CHAR(3) );
CREATE TABLE Countries( name TEXT NOT NULL UNIQUE, capital TEXT, area FLOAT NOT NULL, population INT NOT NULL, continent CHAR(2) NOT NULL, currency CHAR(3) );
CREATE TABLE Countries( name TEXT PRIMARY KEY, capital TEXT, area FLOAT NOT NULL, population INT NOT NULL, continent CHAR(2) NOT NULL, currency CHAR(3) );
Countries(name,capital,population,area,currency,continent)
SELECT name,population FROM Countries ORDER BY population;
SELECT name,population FROM Countries ORDER BY population DESC;
SELECT name,population FROM Countries ORDER BY population ASC;
SELECT name,population/area FROM Countries;
SELECT name,population/area AS density FROM Countries;
SELECT name,population/area FROM countries WHERE area>0 ORDER BY population/area DESC LIMIT 10
SELECT continent FROM Countries;
SELECT DISTINCT continent FROM Countries;
SELECT continent FROM Countries GROUP BY continent;
SELECT continent,SUM(population) FROM Countries GROUP BY continent;
SELECT continent,COUNT(countries),SUM(population) FROM Countries GROUP BY continent;
SELECT 'Hello world!';
SELECT 2+3;
SELECT 2+3 AS answer;
SELECT 2+3 AS sum, 2*3 AS product;
SELECT 2+3 WHERE 2+2 = 5;
SELECT ;
WITH Densities AS
(SELECT name,population/area AS density
FROM Countries)
SELECT *
FROM Densities
ORDER BY density DESC
LIMIT 10;
SELECT *
FROM (SELECT name,population/area AS density) AS Densities
ORDER BY density DESC
LIMIT 10