-- Create a database
CREATE DATABASE dbName;

-- Insert data inside a table
INSERT INTO table_name (column1, column2, ...);

-- Drop table
DROP TABLE table_name;

-- Drop Database
DROP DATABASE dbName;

-- Used to add, delete or modify columns, is also used to add and drop constraints on an existing table
ALTER TABLE table_name ADD column_name datatype;
ALTER TABLE table_name DROP COLUMN column_name;
ALTER TABLE table_name ALTER COLUMN column_name datatype;

-- If a row already exists just do nothing about it
ON CONFLICT DO NOTHING
-- Insert if does not exists and update if does not.
ON CONFLICT (column_name) DO UPDATE SET column_name = 'something';

-- Updating directly, use WHERE clause to filter what you want to update
UPDATE table_name SET column_name = 'something' WHERE title = 'something';
-- You can add RETURNING clause to specify which columns do you want to see after run the query
UPDATE ingredients SET image = 'watermelon.jpg' WHERE title = 'watermelon' RETURNING id, title, image;
-- RETURNING * -> it will return all the columns

-- It will delete every image with the different.jpg attribute
DELETE FROM ingredients
WHERE image='different.jpg'
RETURNING *;

-- Select will basic search in your dabatase
SELECT * FROM ingredients;  -- it will return all the data

-- You can specify the limit that you want to request
SELECT * FROM ingredients LIMIT 10; 

-- Used to filter only those data that match with the condition specified
SELECT * FROM ingredients WHERE CONCAT(title, type) ILIKE $2;
-- $ is used with WHERE clause to indicate a placeholder for a value that will
-- be provided when the query is executed.
-- At this condition, $2 placeholder represents the second value that will be provided 
-- when the query is executed. This value will be concatenated with the title and type columns to form the ILIKE comparison.

-- With limit we can return only five records
SELECT id, title, image
FROM ingredients
LIMIT 5;

-- But let's imagine that you need to get the next five records, in this case, we can use OFFSET to skip that many rows before beginning return  rows
SELECT id, title, image
FROM ingredients
LIMIT 5
OFFSET 5;

-- It will return 5 ingredients but starting from 6 because we are skipping the number 5 with offset clause

-- We can use order by to order the data
SELECT * FROM ingredients ORDER BY title;

-- We can order by DESC, it will return by descending and ascending too.
SELECT * FROM ingredients ORDER BY id DESC;

-- It used in "WHERE" clause to search for a specified pattern in a column
SELECT * FROM ingredients WHERE title LIKE 'b%';

-- Function LOWER() and UPPER() will return transform the data on that function
SELECT * FROM ingredients WHERE LOWER(title) LIKE '%BANANA%'; -- return: banana

-- ILIKE is used when you have upper strings inside the input, but whatever the way that want search, it always convert to lower.
SELECT * FROM ingredients WHERE title ILIKE '%bAnAnA%'; -- return: banana

-- If you add bAnAnA with "LIKE" clause it will not find, because there are uppercase strings.
-- By default postgres is sensitive case, which means that the data should be exactly equals the result.
-- Based on that, they created "ILIKE" where is used in insensitive cases, you can put uppercase strings but it always read as lower.

-- CONCAT FUNCTION
SELECT CONCAT('OI', 'CARAI');

return:
 concat
---------
 OICARAI