Review of basic SQL
CREATE TABLE cats (
id INTEGER PRIMARY KEY, -- used only in sqlite
name TEXT,
age INTEGER,
breed TEXT); -- always end using a semicolon
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
inner join
: will select ALL rows from BOTH tables as long as there is a match between the
specified columns of each tableSELECT 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
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 conditionSELECT 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 tablesSELECT column_names
FROM table_name
WHERE column_name = some_value;
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;
SELECT column_names
FROM table_name
WHERE condition
BETWEEN value1 AND value2;
IS
SELECT * -- all columns
FROM table_name
WHERE column_name IS NULL;
minimum
, maximum
, average
or sum
values from a column's values retrieving a single result!!SELECT count(column_name)
FROM table_name
WHERE column_name = some_value;
SELECT column_names, COUNT(column_name)
FROM table_name
GROUP BY column_name
SELECT name FROM cats;
-- is equivalent to:
SELECT cats.name FROM cats;
SELECT cats.name, dogs.name
FROM cats, dogs;
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
WHERE
select *
from comments
where id in (1,3,2,4)
order by position(id::text in '1,3,2,4')
-- 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;