Data Schema is the table which contains all the data types of another table.
1. Add column in schema table for the main table by using ALTER TABLE... ADD
ALTER TABLE facts
ADD awesomeness integer; # have to mention datatype
2. Delete column from schema table by using ALTER TABLE... DROP COLUMN...
ALTER TABLE facts DROP COLUMN awesomeness;
3. Create table by using CREATE TABLE statement:
CREATE TABLE factbook.leaders(
id integer PRIMARY KEY,
name text,
country text);
4. Relations between TABLES:
CREATE TABLE factbook.leaders(
id integer PRIMARY KEY,
name text,
country integer, #have to create country column before it is associated to a foreign column
worth float,
FOREIGN KEY(country) REFERENCES facts(id) # set a foreign column which is come from another column # The value country column comes from a foreign column
);
5. Also,we can use INNER JOIN to make querying across foreign relationsships, create a table that combines both column and values according to their common(related) column:
SELECT * from states
INNER JOIN facts
ON states.country == facts.id;
6. Also,beside INNER JOIN there are LEFT JOIN and RIGHT JOIN:
select * from landmarks left outer join facts on facts.id == landmarks.country
