tolumide-ng · January 16, 2020 13:15 · Feb 21, 2018 · Feb 21, 2018 · Feb 21, 2018 · Feb 21, 2018
diff --git a/postgresql_mac.md b/postgresql_mac.md
@@ -184,3 +184,44 @@ DELETE FROM tablename;
 
 Without a qualification, DELETE will remove all rows from the given table, leaving it empty. The system will not request confirmation before doing this!
 
+## Advanced Features
+
+### Views
+
+```
+CREATE VIEW myview AS
+    SELECT city, temp_lo, temp_hi, prcp, date, location
+        FROM weather, cities
+        WHERE city = name;
+
+SELECT * FROM myview;
+```
+
+Views allow you to encapsulate the details of the structure of your tables, which might change as your application evolves, behind consistent interfaces.
+
+Views can be used in almost any place a real table can be used. Building views upon other views is not uncommon.
+
+### Foreign Keys
+
+maintaining the `referential integrity` of your data
+
+### Transactions
+
+The essential point of a transaction is that it bundles multiple steps into a single, all-or-nothing operation.
+
+### Window Functions
+
+A window function performs a calculation across `a set of table rows` that are somehow related to the current row.
+
+- do not cause rows to become grouped into a single output
+- the rows retain their separate identities
+
+The PARTITION BY clause within OVER divides the rows into groups, or partitions, that share the same values of the PARTITION BY expression(s). For each row, the window function is computed across the rows that fall into the same partition as the current row.
+
+### Inheritance
+
+Inheritance is a concept from object-oriented databases. 
+
+
+
+
diff --git a/postgresql_mac.md b/postgresql_mac.md
@@ -10,7 +10,7 @@ ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
 
 Add alias to zshrc
 
-`` sh
+``` sh
 subl ~/.zshrc # or vim ~/.zshrc
 ```
 

diff --git a/postgresql_mac.md b/postgresql_mac.md
@@ -155,5 +155,32 @@ City with the highest temperature
 ```
 SELECT city FROM weather
     WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
+SELECT city, max(temp_lo)
+    FROM weather
+    WHERE city LIKE 'S%'            -- (1)
+    GROUP BY city
+    HAVING max(temp_lo) < 40;
 ```
 Aggregate max cannot be used in the WHERE clause. (This restriction exists because the WHERE clause determines which rows will be included in the aggregate calculation)
+
+### Updates
+
+You can update existing rows using the UPDATE command. Suppose you discover the temperature readings are all off by 2 degrees after November 28. You can correct the data as follows
+```
+UPDATE weather
+    SET temp_hi = temp_hi - 2,  temp_lo = temp_lo - 2
+    WHERE date > '1994-11-28';
+```
+
+### Deletions
+
+```
+DELETE FROM weather WHERE city = 'Hayward';
+```
+
+```
+DELETE FROM tablename;
+```
+
+Without a qualification, DELETE will remove all rows from the given table, leaving it empty. The system will not request confirmation before doing this!
+
diff --git a/postgresql_mac.md b/postgresql_mac.md
@@ -0,0 +1,159 @@
+## Installing
+
+[Reference](https://gist.github.com/sgnl/609557ebacd3378f3b72)
+
+``` sh
+brew update
+brew install postgresql
+ln -sfv /usr/local/opt/postgresql/*.plist ~/Library/LaunchAgents
+```
+
+Add alias to zshrc
+
+`` sh
+subl ~/.zshrc # or vim ~/.zshrc
+```
+
+At the bottom of the file, create two new aliases to start and stop your postgres server.
+
+``` sh
+alias pg-start="launchctl load ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist"
+alias pg-stop="launchctl unload ~/Library/LaunchAgents/homebrew.mxcl.postgresql.plist"
+```
+
+Run the command: `source ~/.zshrc` to reload your configuration.
+
+- `pg-start` Use this comment to start your database service
+- `pg-stop` stops your database service
+- `psql` Connect to your postgres
+
+## Mini-tutorials
+
+[Reference](https://www.postgresql.org/docs/10/static/tutorial-createdb.html)
+
+### Create Database
+``` sh
+createdb mydb # create mydb database
+dropdb mydb # delete mydb database
+createdb mydb # create mydb database again
+psql mydb # access mydb database
+```
+Now you are in the mydb database
+enter `SELECT current_date;` and `SELECT 2 + 2;`
+
+```
+psql (10.2)
+Type "help" for help.
+
+mydb=# SELECT current_date;
+ current_date
+--------------
+ 2018-02-20
+(1 row)
+
+mydb=# SELECT 2 + 2;
+ ?column?
+----------
+        4
+(1 row)
+```
+
+To get out of psql, type:
+```
+mydb=> \q
+```
+
+Re-enter mydb
+
+```
+psql mydb
+```
+
+### Creating a New Table
+
+```
+CREATE TABLE weather (
+    city            varchar(80),
+    temp_lo         int,           -- low temperature
+    temp_hi         int,           -- high temperature
+    prcp            real,          -- precipitation
+    date            date
+);
+
+CREATE TABLE cities (
+    name            varchar(80),
+    location        point
+);
+```
+
+### Remove Table
+
+```
+DROP TABLE tablename;
+```
+
+### Populating a Table With Rows
+
+#### In Database
+```
+  INSERT INTO weather VALUES ('San Francisco', 46, 50, 0.25, '1994-11-27');
+  INSERT INTO cities VALUES ('San Francisco', '(-194.0, 53.0)');
+  INSERT INTO weather (city, temp_lo, temp_hi, prcp, date)
+    VALUES ('San Francisco', 43, 57, 0.0, '1994-11-29');
+  INSERT INTO weather (date, city, temp_hi, temp_lo)
+    VALUES ('1994-11-29', 'Hayward', 54, 37);
+```
+
+#### Use Copy
+
+```
+COPY weather FROM '/home/user/weather.txt';
+```
+
+### Querying a Table
+
+```
+SELECT * FROM weather;
+SELECT city, (temp_hi+temp_lo)/2 AS temp_avg, date FROM weather;
+SELECT * FROM weather
+    WHERE city = 'San Francisco' AND prcp > 0.0;
+SELECT * FROM weather
+    ORDER BY city;
+SELECT * FROM weather
+    ORDER BY city, temp_lo;
+SELECT DISTINCT city
+    FROM weather;
+```
+
+### Joins Between Tables
+
+#### Inner Join
+```
+SELECT *
+    FROM weather, cities
+    WHERE city = name;
+SELECT *
+    FROM weather INNER JOIN cities ON (weather.city = cities.name);
+SELECT *
+    FROM weather w, cities c
+    WHERE w.city = c.name;
+```
+
+#### Self Join
+
+```
+  SELECT W1.city, W1.temp_lo AS low, W1.temp_hi AS high,
+    W2.city, W2.temp_lo AS low, W2.temp_hi AS high
+    FROM weather W1, weather W2
+    WHERE W1.temp_lo < W2.temp_lo
+    AND W1.temp_hi > W2.temp_hi;
+```
+
+### Aggregate Functions
+
+City with the highest temperature
+```
+SELECT city FROM weather
+    WHERE temp_lo = (SELECT max(temp_lo) FROM weather);
+```
+Aggregate max cannot be used in the WHERE clause. (This restriction exists because the WHERE clause determines which rows will be included in the aggregate calculation)