Writing the 4 basic SQL Queries

1. SELECT

Say we have a simple table where we need to select certain cells (*** in the figure) in a chosen column, based on some condition.

+---------+---------+
| Column1 | Column2 |
+---------+---------+
|  Cell1  |  Cell2  |
|  Cell3  |  Cell4  |
|  ***    |  Cell6  |
|  ***    |  Cell8  |
|  ***    | Cell10  |
+---------+---------+

We would SELECT a column FROM the table WHERE certain conditions are met. Additionally, we may JOIN a foreign_table based ON a foreign_key=primary_key relation, and have a WHERE condition accordingly.

The general syntax (parameterized) would be:

`SELECT h.name
FROM humans h
JOIN animals a ON h.animal_id = a.id
WHERE a.name = $1`,[Human],

You'd add the above sql query in the dots below:

const result = await pool.query<{name:string}>(.....);

where pool is your connector object from a PostgreSQL client like pg.

Array-of-objects to Array-of-dataType :

The returned result object is a json object with many entries in which the useful entry is rows : [..array of objects]. Each object in this array is of type say {name: string}. If we map through this entry using result.rows.map and extract the string, we'll get an array of strings which is what we want. So :

const humans:string[] = result.rows.map(row:{name:string})=>{row.name};

Then you can res.json(humans);

---

2. 📥 INSERT

In SQL, INSERT is used when you want to add new rows to a table — like creating a new human, animal, product, etc.

Let's say we want to add new rows (marked with ***) into this table:

+---------+---------+
| Column1 | Column2 |
+---------+---------+
|  Cell1  |  Cell2  |
|  Cell3  |  Cell4  |
|  ***    |  ***    |
+---------+---------+

You use:

INSERT INTO table_name (column1, column2)
VALUES (value1, value2);

If you want to insert a row in which one of the values is referenced from a foreign table, then,

INSERT INTO humans(name, animal_id)
VALUES(
  'Ram`,
  (SELECT id FROM animals WHERE name='Human')
)
ON CONFLICT (name, animal_id) DO NOTHING

If there's another table say species above animals, then multi-level nesting is also possible:

(SELECT id FROM animals WHERE name = 'X' AND species_id = (SELECT id FROM species WHERE ...))

---

3. ♻️ UPDATE

The UPDATE statement is used in SQL when you want to change existing data in a table — like correcting a spelling mistake, changing an animal assigned to a human, updating a price, etc.

The general format is :

UPDATE humans
SET fav_color = 'Blue'
WHERE name = 'Ram'

Again if there's a primary key in a foreign table containing 'Blue', you can reference that using SELECT instead of hardcoding 'Blue'.

💡 Note: It’s good practice to always include a WHERE clause in UPDATE. Leaving it out will update every row in the table!

---

4. 🗑️ DELETE

The DELETE statement is used to remove rows from a table — for example, removing a record that is no longer needed.

📌 General Syntax (basic)

DELETE FROM table_name
WHERE condition;

💡 Warning: If you leave out the WHERE clause, all rows in your table will be deleted!

Summary Table

Action	SQL Command	Example
SELECT	SELECT ... FROM ... WHERE ...	Get/retrieve data
INSERT	INSERT INTO ... VALUES ...	Add new data
UPDATE	UPDATE ... SET ... WHERE ...	Modify existing data
DELETE	DELETE FROM ... WHERE ...	Remove data