PSQL Notes 2


Launch School Notes: Schema, Data, and SQL

There are things I’m learning as I go through the Postgres course of Launch School. This post are notes from,

‘6. NOT NULL and Default Values’


Setting a default value


ALTER TABLE employees ALTER COLUMN department SET DEFAULT 'unassigned';

changing the data


UPDATE employees SET department = 'unassigned' where department is null;

\2. Set the default value of column department to “unassigned”. Then set the value of the department column to “unassigned” for any rows where it has a NULL value. Finally, add a NOT NULL constraint to the department column.


ALTER TABLE employees ALTER COLUMN department SET DEFAULT 'unassigned';
UPDATE employees SET department = 'unassigned' WHERE department IS NULL;
ATLER TABLE employees ALTER COLUMN department SET NOT NULL;

\3. Write the SQL statement to create a table called temperatures to hold the following data:


CREATE TABLE temperatures (
  date DATE NOT NULL,
  low  INTEGER NOT NULL,
  high INTEGER NOT NULL
);

INSERT INTO temperatures (date, low, high) VALUES
  ('2016-03-01', 34, 43),
  ('2016-03-02', 32, 44),
  ('2016-03-03', 31, 47),
  ('2016-03-04', 33, 42),
  ('2016-03-05', 39, 46),
  ('2016-03-06', 32, 43),
  ('2016-03-07', 29, 32),
  ('2016-03-08', 23, 31),
  ('2016-03-09', 17, 28)

I first didn’t add ‘’ ’s around DATE INSERT INTO temperatures (date, low, high) VALUES (2016-03-01, 34, 43);


\5. Write a SQL statement to determine the average (mean) temperature for the days from March 2, 2016 through March 8, 2016.


SELECT (avg(low) + avg(high)) / 2
FROM temperatures
WHERE date >= '2016-03-02' AND date <= '2016-03-08'

SELECT date, (low + high) / 2 as average
FROM temperatures
WHERE date >= '2016-03-02' AND date <= '2016-03-08'

SELECT date, (high + low) / 2 as average
FROM temperatures
WHERE date BETWEEN '2016-03-02' AND '2016-03-08';

I like the BETWEEN keyword


\6. Write a SQL statement to add a new column, rainfall, to the temperatures table. It should store millimeters of rain as integers and have a default value of 0.


ALTER TABLE temperatures INSERT COLUMN rainfall INTEGERS DEFAULT 0;

ALTER TABLE temperatures ADD COLUMN rainfall INTEGER DEFAULT 0;

ALTER TABLE temperatures ADD COLUMN rainfall integer DEFAULT 0

It’s ADD not INSERT for columns, and integer I guess is a method? so it’s lowercase


\7. Each day, it rains one millimeter for every degree the average temperature goes above 35. Write a SQL statement to update the data in the table temperatures to reflect this.


select (high + low) / 2 - 35 as mm from temperatures where rainfall >= 0;

UPDATE temperatures
SET rainfall = (high + low) / 2 - 35;

UPDATE temperatures
SET rainfall = 0
WHERE rainfall < 0;

UPDATE temperatures
   SET rainfall = (high + low) / 2 - 35
 WHERE (high + low) / 2 > 35;

I didn’t realize I should have just rewritten that whole (high + low) / 2 > 35; statement


\8. A decision has been made to store rainfall data in inches. Write the SQL statements required to modify the rainfall column to reflect these new requirements.

1 mm = 0.0393701 inches

change integer to float or decimal(


ALTER TABLE temperatures
ALTER COLUMN tests TYPE numeric(4, 3);

change the value by multiplying by 0.0393701 inches/mm


UPDATE temperatures
   SET rainfall= ((high + low) / 2 - 35) * 0.0393701
 WHERE (high + low) / 2 > 35;

ALTER TABLE temperatures ALTER COLUMN rainfall TYPE numeric(6,3);
UPDATE temperatures SET rainfall = rainfall * 0.039;

I didn’t realize I could change values in line like that col = col * 3