Postgres

Read full here

Relationships

1. One to Many
2. Many to One
3. One to One
4. Many to Many

Create table

CREATE TABLE cities (
  city_name VARCHAR(50),
  country VARCHAR(50),
  population INTEGER
);

Insert data into the table

INSERT INTO cities (city_name, country, population)
VALUES
("Colombo", "Sri Lanka", 234000);

Updating data inside the table

UPDATE cities SET population = 50000 WHERE city_name = 'Colombo';

Delete a row from a table

DELETE FROM cities WHERE city_name = 'Colombo';

Delete table

DROP TABLE cities;

Adding primary key

CREATE TABLE users (id SERIAL PRIMARY KEY, user_name VARCHAR(50));

Database for photo-sharing application

1. users
2. photos
3. comments
4. likes

Joins

producing values by merging some rows from different tables.

Four kinds of joins

1. Inner join ( default join — can use without inner keyword ) — keeping only rows that match up with other tables rows.

select name, title from photos join comments on photos.id = comments.photo_id;

2. Left outer join — keep every row from the left side table

select name, title from photos left join comments on photos.id = comments.photo_id;

3. Right outer join — keep every row from the right side table

select name, title from photos right join comments on photos.id = comments.photo_id;

4. Full join — keep all rows for both tables.

select name, title from photos full join comments on photos.id = comments.photo_id;

Three-way join

select name, title, username from photos join comments on photos.id = comments.photo_id
join users on users.id = photos.user_id AND users.id = comments.user_id;

Aggregation

looks at many rows and calculates a single value.