Cacher is the code snippet organizer for pro developers

1. A database is a structure that recognizes relations among the stored records (belongs_to and has_many)

2. Every row in a database table has a number called a 'primary key' for that table

3. Relationships are formed and by a belongs_to and has_many if the belongs_to table has a column called something_id

4. That record then belongs to that something from another table

---

Creating a Table

A table is always plural, because it contains many records!

CREATE TABLE cats (
  id INTEGER PRIMARY KEY, -- used only in sqlite
  name TEXT,
  age INTEGER,
  breed TEXT); -- always end using a semicolon

• A cat belongs_to an owner and an owner has_many cats, so in the cats table, create a column for it's owner:

CREATE TABLE owners (
  name TEXT);

ALTER TABLE cats
ADD COLUMN owner_id INTEGER;

-- Inserting records in to the database:
INSERT INTO cats (id, name, age, breed)
VALUES (1, "Maru", 3, "Scottish Fold");

-- Record Associations
UPDATE table SET column_name = some_value
WHERE record = some_value;

UPDATE cats SET owner_id = 1
WHERE name = "Maru";

-- Deleting records
DELETE FROM table_name
WHERE column_name IS some_value;

-- Using like, similar to using WHERE
-- It is used to compare similar values
SELECT *
FROM table_name
WHERE column_name LIKE 'he_lo';
-- '-' is a wildcard character and means you can substitue any individual character in place without breaking the pattern.
-- '%' is a wildcard character that matches zero or more missing letters in the pattern
-- 'A%' returns records that BEGIN with 'A'
-- '%a' returns records that END with 'a'
-- '%man%' returns records that contain man within it

---

SQL JOINS

• inner join: will select ALL rows from BOTH tables as long as there is a match between the specified columns of each table

SELECT column_names
FROM first_table
INNER JOIN second_table
ON first_table.column_name = second_table.column_name;
-- How can you join one table to another? What connects them? The foreign key connects a table to another table

LEFT OUTER JOIN = LEFT JOIN

• Will return all the records in the left table (even if no match was made for it in the right table), and those that matched the ON condition

SELECT column_names
FROM first_table
LEFT JOIN second_table
ON first_table.column_name = second_table.column_name;

• RIGHT OUTER JOINS & FULL OUTER JOINS are not provided by SQLite but are provided by PSQL
• RIGHT JOIN will return all records on the RIGHT table and those that matched the ON condition

SELECT column_names
FROM first_table
RIGHT JOIN second_table
ON first_table.column_name = second_table.column_name;

• FULL OUTER JOIN query returns the combined results of both a LEFT and RIGHT OUTER JOIN returning data from both the left and right tables

---

Common DB queries

• Querying a certain record:

SELECT column_names
FROM table_name
WHERE column_name = some_value;

• Ordering records by a certain condition:

SELECT column_names
FROM table_name
ORDER BY column_name DESC; -- ASC is default

-- You can limit the above search by adding this line to the end of the query:
LIMIT number;

• Searching for a record between a certain value:

SELECT column_names
FROM table_name
WHERE condition
BETWEEN value1 AND value2;

• Using IS

SELECT * -- all columns
FROM table_name
WHERE column_name IS NULL;

---

Aggregate Functions

• Aggregate functions are SQL statements that retrieve minimum, maximum, average or sum values from a column's values retrieving a single result!!

COUNT

SELECT count(column_name)
FROM table_name
WHERE column_name = some_value;

GROUP BY

• Groups your results by a given column, for columns with identical data:

SELECT column_names, COUNT(column_name)
FROM table_name
GROUP BY column_name

SELECT

SELECT name FROM cats;

-- is equivalent to:

SELECT cats.name FROM cats;

• Useful for getting data back from two different tables:

SELECT cats.name, dogs.name
FROM cats, dogs;

• Using regex to remove a certain part of text

select p.sku, replace(p.subspec_type, 'Fsi::Sub', '') as product_type, k.ext_height, k.ext_width, k.ext_depth
from fsi_products p
inner join fsi_specifications k
on p.id = k.product_id
where is_series = false

• Keeping the format when using an array for WHERE

select *
from comments
where id in (1,3,2,4)
order by position(id::text in '1,3,2,4')

---

Reset ID Count in PGADMIN

-- Used to find out the sequence name (table_name, pk)
select pg_get_serial_sequence('fealights', 'id');
-- Reset the auto-increment. Next record will begin with 1
ALTER SEQUENCE fealights_id_seq RESTART WITH 1;