In class exercises Part I - Create initial table 1. Create schema called world CREATE SCHEMA world AUTHORIZATION postgres; 2. Create table for continents CREATE TABLE world.continents ( caid varchar(2), cnid varchar(3) NOT NULL UNIQUE, cname text, area_sqkm integer, CONSTRAINT pkcaid PRIMARY KEY (caid) ); 3. Insert data into continents table INSERT INTO world.continents VALUES ('AF', '002', 'Africa', 30370000), ('AN', '010', 'Antarctica', 13720000), ('AS', '142', 'Asia', 43820000), ('EU', '150', 'Europe', 10180000), ('NA', '003', 'North America', 24490000), ('OC', '009', 'Oceania', 9008500), ('SA', '005', 'South America', 0); 4. Insert missing data for South America UPDATE world.continents SET area_sqkm = 17840000 WHERE caid = '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 - Create 2nd table, populate first with its values 7. Create table for continent population CREATE TABLE world.cont_pop ( caid varchar(2) PRIMARY KEY, cname text, pop bigint ); 8. Insert data into population table INSERT INTO world.cont_pop VALUES ('AF', 'Africa', 1186178000), ('AN', 'Antarctica', 4490), ('AS', 'Asia', 4393296000), ('EU', 'Europe', 738442000), ('NA', 'North America', 573777000), ('OC', 'Oceania', 39331000), ('SA', 'South America', 418447000); 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.caid=p.caid; ALT UPDATE world.continents SET pop=( SELECT pop FROM world.cont_pop WHERE world.cont_pop.caid=world.continents.caid); 11. Drop the continents population table DROP TABLE world.cont_pop; Part III - Create new table 12. Do A SQL query to calculate the population density per square mile SELECT caid, cname, (pop / area_sqmi) AS popdens FROM world.continents; 13. Turn this query into a view CREATE VIEW world.cont_density AS SELECT caid, cname, (pop / area_sqmi) AS popdens FROM world.continents; 14. Create table for continental GDP CREATE TABLE world.cont_gdp ( cnid varchar(3), cname text, gdp numeric(4,1), CONSTRAINT pkcnid PRIMARY KEY (cnid), CONSTRAINT fkcnid FOREIGN KEY (cnid) REFERENCES world.continents(cnid) ); 15. Insert values into gdp table INSERT INTO world.cont_gdp VALUES ('002', 'Africa', 2.2), ('010', 'Antarctica', NULL), ('142', 'Asia', 25.0), ('150', 'Europe', 19.1), ('003', 'North America', 21.7), ('009', 'Oceania', 1.4), ('005', 'South America', 3.6); 16. Attempt to alter the gpd table by adding a record INSERT INTO world.cont_gdp VALUES ('000', '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 caid='AT'; 19. Create table using Create Table As CREATE TABLE world.test1 AS SELECT c.caid, c.cname, g.gdp, round((g.gdp*1000000000000)/c.pop,0) AS gpd_percap FROM world.continents AS c, world.cont_gdp AS g WHERE c.cnid = g.cnid 20. Create table using create and insert CREATE TABLE world.test2 ( caid varchar(2) PRIMARY KEY, cname text, gdp_percap integer ); INSERT INTO world.test2 (caid, cname, gdp_percap) SELECT c.caid, c.cname, (g.gdp*1000000000000)/c.pop FROM world.continents AS c, world.cont_gdp AS g WHERE c.cnid = g.cnid; Part IV - Import a Table 21. Create database table CREATE TABLE nys.acs2014_reswork ( zcta varchar(5) PRIMARY KEY, zctaname text, lab_civemp integer, lab_civemp_me integer, instate_inco integer, instate_inco_me integer, instate_outco integer, instate_outco_me integer, outstate integer, outstate_me integer); 22. Copy from csv \copy nys.acs2014_reswork FROM 'C:\workspace\weatherdata\acs2014_nys_countywork' WITH DELIMITER AS ’,’ CSV HEADER 23. Verify SELECT * FROM nys.acs2014_reswork; 24. Add comment