Writing the 4 basic SQL Queries

[Sascha]

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

Источник:

Пожалуйста Авторизируйтесь или Зарегистрируйтесь для просмотра скрытого текста.