This will show some points to remember in SQL or to reference when needed

1. There are 2 constraints which might help

CEHCK
EXCLUDES

-- CHECK
CREATE TABLE table_a
(
  age INTEGER CHECK (age > 20),
  parents_age INTEGER CHECK (age < parents_age)
)

2. Insert from another table

INSERT INTO table_new(col_1, col_2)
  SELECT col_1, col_2 
  FROM table_old

3. Use the SERIAL as the datatype which will automatically increase the value in each insert. Best suitable for primary key.

Some notes on SERIAL:

It is the integer itself
It can store the negative values too
But it will start incrementing only from 1
You can have duplicates there
It doesn't care about the number like 1, 2, 3 automatic but you add 1123 after 3, then in the next one will be 4.
It will skip the count if the insert command failed before.
- Let's say we have 1, 2, 3 as rows and now we are attempting to insert 4th but in 4th time because of some constraint we failed in inserting. So we went and corrected the error, after it the successfull insertion, it will show 5 as the number.

4. The JOIN with UPDATE

UPDATE table_a
SET table_a.col_1 = table_b.col1
  FROM table_b
  WHERE table_a.col = table_b.col

The reason why there was no JOIN keyword ↑ there, is because we are using FROM keyword only once. So the join happens automatically.

The table_a is automatically referenced when we wrote UPDATE table_a so the only requirement was to add another table, which we did with FROM table_b

5. Return the affected rows with the UPDATE command

UPDATE table_a
SET col = value
RETURNING col_1, col_2

6. Delete and RETURN the rows which were deleted

DELETE FROM table
WHERE col = value
RETURNING col_1, col_2

7. RENAME the table

ALTER TABLE table_name_old
RENAME TO table_name_new

8. RENAME the column

ALTER TABLE table_name
RENAME COLUMN col_old TO col_new

9. Might wanna use COALESCE

Evaluates the arguments in order and always returns first non-null value from the defined argument list

It is like "fill na" in pandas.

SELECT COALESCE(column, 0) 
FROM table;

Here, it will return the 0 if there is NULL else, it will return the value (non-null) itself. Easy! BUTT here is a Catch!

This COALESCE takes n number of arguments. As we have passed only 2 in our example (COALESCE(col, 0)), we can pass n number of arguments and then "it will return the first non-null value" from the argument list.

COALESCE(
[     1,     2, null, null,     5],
[ null,     2,     3, null,     5],
[     1,     2,     3,     4, null]
)

Returns: [1, 2, 3, 4, 5]

I think, you got this!

10. CAST things up

Either by:

CAST(col AS dtype)

OR by (only for PostgreSQL):

col :: dtype

11. NULLIF

This is so much useful to handle the potential errors. It is kind of "TRY CATCH"... but kind of.

Here,

col_A	col_B
1	11
2	0
33	12
44	0
55	1

If we run,

SELECT 
col_A / col_B
FROM table

Then, it will give an error of not divisible by zero. So, we can "handle it" by NULLIF

SELECT 
col_A / NULLIF(col_B, 0)
FROM table

NULLIF returns NULL if both of the arguments matched. Otherwise it will return the value itself.

Here, when the value is 0, it will return NULL so the resulting value will be NULL for that division instead of that error. "WE COULD HAVE USED - CASE" here, but it will be too long and boring... so we used NULLIF.

AayushSameerShah/easy_points_for_sql.md