In class exercises - Class #3 SQL DDL

Part I - Basic table creation

This section illustrates how to create tables with basic constraints, insert hardcoded values (INSERT INTO VALUES), add columns (ALTER TABLE ADD), and update values (UPDATE - SET).

1. Create schema called world

CREATE SCHEMA world AUTHORIZATION postgres;

2. Create table for continents

CREATE TABLE world.continents (
cid varchar(2) PRIMARY KEY,
un varchar(3) UNIQUE,
cname text NOT NULL,
area_sqkm integer
);

3. Insert data into continents table

INSERT INTO world.continents 
VALUES ('AF', '002', 'Africa', 30370000),
('AN', '010', 'Antarctica', 14000000),
('AS', '142', 'Asia', 44579000),
('EU', '150', 'Europe', 10180000),
('NA', '003', 'North America', 24709000),
('OC', '009', 'Oceania', 8600000),
('SA', '005', 'South America', 	0);

4. Insert missing data for South America

UPDATE world.continents
SET area_sqkm = 17840000
WHERE cid = 'SA';

5. Add a column for square miles

ALTER TABLE world.continents
ADD COLUMN area_sqmi integer;

6. Fill in the area column

UPDATE world.continents
SET area_sqmi = ROUND(area_sqkm * 0.386,-2)

Part II - Populate table with values in another table, create view

In this section you create a second table, populate the first table with data from this second one, drop the second table (ALTER TABLE DROP) and create a VIEW.

7. Create table for continent population

CREATE TABLE world.cont_pop (
cid varchar(2) PRIMARY KEY,
cname text,
pop bigint
);

8. Insert data into population table

INSERT INTO world.cont_pop
VALUES ('AF', 'Africa', 1287920000),
('AN', 'Antarctica', 4490),
('AS', 'Asia', 4545133000),
('EU', 'Europe', 742648000),
('NA', 'North America', 587615000),
('OC', 'Oceania', 41261000),
('SA', 'South America', 428240000);

9. Alter the continents table to add a population column

ALTER TABLE world.continents
ADD COLUMN pop bigint;

10. Insert population data from pop table into continents table

UPDATE world.continents AS c
SET pop = p.pop
FROM world.cont_pop AS p
WHERE c.cid=p.cid;

ALT

UPDATE world.continents
SET pop=(
SELECT pop
FROM world.cont_pop
WHERE world.cont_pop.cid=world.continents.cid);

11. Drop the continents population table

DROP TABLE world.cont_pop;

12. Do A SQL query to calculate the population density per square mile

SELECT cid, cname, (pop / area_sqmi) AS popdens
FROM world.continents;

13. Turn this query into a view

CREATE VIEW world.cont_density AS
SELECT cid, cname, (pop / area_sqmi) AS popdens
FROM world.continents;

Part III - Import data into a table, keys, creating tables from existing tables

In this section you'll use the COPY command or the pgadmin interface to import data into a blank table you create. You'll see how a foreign key works, learn to DELETE records, and try two methods for creating a table from a query: directly from the query with CREATE TABLE AS and by loading data from a query into a blank table with INSERT INTO SELECT.

14. Create table for continental GDP

CREATE TABLE world.cont_gdp (
un varchar(3) PRIMARY KEY,
cid varchar(2) REFERENCES world.continents(cid),
cname text NOT NULL,
gdp numeric(4,1)
);

15. Insert values into gdp table

COPY world.cont_gdp
FROM 'C:\Users\UNAME\Desktop\continent_gdp.txt'
WITH DELIMITER AS ',' CSV HEADER;

ALT

Use pgAdmin Import

Contents of file:

un,cid.cname,gdp
"002","AF","Africa",2.3
"010","AN","Antarctica",
"142","AS","Asia",30.2
"150","EU","Europe",21.8
"003","NA","North America",23.6
"009","OC","Oceania",1.6
"005","SA","South America",3.6

16. Attempt to alter the gpd table by adding a record

INSERT INTO world.cont_gdp
VALUES ('000', 'AT', 'Atlantis', 1.0);

17. Attempt to alter the population table by adding a record

INSERT INTO world.continents
VALUES ('AT', '000', 'Atlantis', 0,0,0);

18. Delete record

DELETE FROM world.continents
WHERE cid='AT';

19. Create table using Create Table As

CREATE TABLE world.test1 AS
SELECT c.cid, c.cname, g.gdp, 
(g.gdp*1000000000000)/c.pop AS gpd_percap
FROM world.continents AS c, world.cont_gdp AS g
WHERE c.cid = g.cid

20. Create table using create and insert

CREATE TABLE world.test2 (
cid varchar(2) PRIMARY KEY,
cname text,
gdp_percap integer
);

INSERT INTO world.test2 (cid, cname, gdp_percap)
SELECT c.cid, c.cname, (g.gdp*1000000000000)/c.pop
FROM world.continents AS c, world.cont_gdp AS g
WHERE c.cid = g.cid;