2.1 Table Basics
To create a table, use CREATE TABLE command.  You can specify
a name for the table and the column data types.
(i.e)
	CREATE TABLE my_first_table (
		first_column text,
		second_column integer
	);

	-this will create a table called 'my_first_table'
	 with two columns, one with data type 'text' and 
	 the other with data type 'integer'
	-note column is comma separated and surrounded by a
	 parentheses

Here is a more realistic table:
(i.e)
	CREATE TABLE products (
		product_no integer,
		name text,
		price numeric 
	);	
	
	-the 'numeric' type can store fractional components

To remove a table, use the DROP TABLE command
(i.e)
	DROP TABLE my_first_table;
	or
	DROP TABLE products;

2.2 System Columns
Note that theses commands are implicitly defined by the system.
Therefore they cannot be used as user-defined columns.  
(oid, tableoid, xmin, cmin, xmax, cmax, ctid)

2.3 Default Values
Note that if no default value is assigned explicitly, then the
null value will be given to it.  Default values are listed after
the column data type.
(i.e)
	CREATE TABLE products (
		product_no integer PRIMARY
		name text,
		price numeric DEFAULT 9.99
	);

2.4 Constriants
SQL allows you to define constraints on columns and tables.  Constraints 
gives you control over the data in your table.  If the user wants to insert
value into a column that violates a constriant, an error is raised.
(i.e)
	CREATE TABLE products (
		product_no integer,
		name text,
		price numeric CHECK (price >0)
	);

	-all prices must have a + value
	-CHECK constraint can be use in several
	 columns

You can also give a constriant a separate name
(i.e)
	CREATE TABLE products (
		product_no integer,
		name text,
		price numeric CONSTRAINT p_price CHECK (price > 0)	
	);

	-'p_price' is the constraint name that you have given, remember
	 you must use the command CONSTRIANT before naming one

Here is an example that uses multiple CHECK constraints in 
various ways 
(i.e)
	CREATE TABLE products (
	     product_no integer,
 	     name text,
  	     price numeric CHECK (price > 0),
   	     discounted_price numeric CHECK (discounted_price > 0),
    	     CHECK (price > discounted_price)
 	);

	-note CHECK constraints do not have to be in a column,
	 as you can see, the last line has a CHECK constraint by itself,
	 which makes sure that price is greater than discounted price
	
Here are two other ways of writting the same thing as above
(i.e)
	CREATE TABLE products (
	     product_no integer,
 	     name text,
  	     price numeric,
   	     CHECK (price > 0),
    	     discounted_price numeric,
     	     CHECK (discounted_price > 0),
             CHECK (price > discounted_price)
 	);

	CREATE TABLE products (
 	    product_no integer,
       	    name text,
   	    price numeric CHECK (price > 0),
    	    discounted_price numeric,
     	    CHECK (discounted_price > 0 AND price > discounted_price)
 	);	

Not Null constraints specifies that a column cannot assume NULL.  Null
constraints simply defines that a column will be defaulted as NULL if 
that ever happens.  In the examples below, just remove the NOT to make it
a NULL constraint.   
(i.e)
	CREATE TABLE products (
 	    product_no integer NOT NULL,
   	    name text NOT NULL,
   	    price numeric
 	);

A column have more than one constraint
(i.e)
	CREATE TABLE products (
 	    product_no integer NOT NULL,
   	    name text NOT NULL,
   	    price numeric NOT NULL CHECK (price > 0)
 	);

Unique constraints ensures that all data contained in a
column is unique with respect to all rows in the table.
(i.e)
	CREATE TABLE example (
  	    a integer,
   	    b integer,
    	    c integer,
     	    UNIQUE (a, c)
 	);

Also you may assign names to unique constraints
(i.e)
	CREATE TABLE products (
 	    product_no integer CONSTRAINT must_be_different UNIQUE,
     	    name text,
   	    price numeric
 	);

Primary Keys are a combination of unique and NOT NULL constraints.  Both
tables accept the same data.  Note that a table can have atmost one
primary key
(i.e)
	CREATE TABLE products (
 	    product_no integer UNIQUE NOT NULL,
   	    name text,
   	    price numeric
 	);

	CREATE TABLE products (
 	    product_no integer PRIMARY KEY,
  	    name text,
   	    price numeric
 	);

They can also contain more than one column.
(i.e)
	CREATE TABLE example (
 	    a integer,
      	    b integer,
   	    c integer,
    	    PRIMARY KEY (a, c)
 	);

Foreign Keys specify that the values in a column or group of columns
must match values appearing in some row of another table.  
(i.e)
We will have to different tables, which one will reference the other
	CREATE TABLE products (
 	    product_no integer PRIMARY KEY,
    	    name text,
   	    price numeric
 	);

	CREATE TABLE orders (
   	   order_id integer PRIMARY KEY,
    	   product_no integer REFERENCES products (product_no),
  	   quantity integer
 	);

	-we want to ensure that the orders table contains orders of
	 products that actually exist, so we define a reference to
	 the products table

Here is a shorten version of the orders table.
	CREATE TABLE orders (
 	    order_id integer PRIMARY KEY,
   	    product_no integer REFERENCES products,
   	    quantity integer
 	);

A foreign key can also reference a group of columns
(i.e)
	CREATE TABLE t1 (
 	  a integer PRIMARY KEY,
    	  b integer,
   	  c integer,
   	  FOREIGN KEY (b, c) REFERENCES other_table (c1, c2)
 	);

*Note, you can have multiple foreign key constraints in a 
table  

2.6 Modifying Tables
-Add columns
	ALTER TABLE my_table ADD COLUMN description text;
-Remove columns
	ALTER TABLE my_table DROP COLUMN description;
-Adding a Constraint
	ALTER TABLE my_table ADD CHECK (name <> '');
 	ALTER TABLE my_table ADD CONSTRAINT some_name UNIQUE (product_no);
 	ALTER TABLE my_table ADD FOREIGN KEY (product_group_id) REFERENCES product_groups;
-Remove constriants
	ALTER TABLE my_table DROP CONSTRAINT some_name;
-Change default values
	ALTER TABLE my_table ALTER COLUMN price SET DEFAULT 7.77;
	ALTER TABLE my_table ALTER COLUMN price DROP DEFAULT;
-Rename columns
	ALTER TABLE my_table RENAME COLUMN product_no TO product_number;
-Rename Tables
	ALTER TABLE my_table RENAME TO items;

3.1 Inserting Data
Inserting data into rows
	- data is inserted one row at a time.
	- a complete row must be inserted before.
	  inserting data into columns.
	- one can always insert more than one row
	  at a time.
To create a new row, use the INSERT command.
(i.e)
	CREATE TABLE products(
		product_no integer,
		name text,
		price numeric
	);
To insert a row, use the command INSERT INTO command
(i.e)
	INSERT INTO products VALUES(1, 'cheese', 9.99);
	
	-data values are inserted in which the way the columns
	 appear in the table, to avoid that, you can list the
  	 columns explicitly.
(i.e)
	INSERT INTO products (product_no, name, price) VALUES (1,'cheese',9.99)
	or  
	INSERT INTO products (name,price,product_no) VALUES ('cheese',9.99,1)

Columns that do not have values	will be filled with their default
values.
(i.e)
	INSERT INTO products (product_no, name) VALUES (1,'cheese')
	or
	INSERT INTO products VALUES (1, 'cheese');

To insert default values explicitly:
	INSERT INTO products (product_no, name, price) VALUES (1,'cheese',9.99);
	INSERT INTO products DEFAULT VALUES;

Updating Data
You can update indivdual rows, all rows, or a subset of rows in a table.  
Each column can be updated separately.  There are 3 things to watch for
when doing a update.
   1. The name of the table and column to update,
   2. The new value of the column,
   3. Which row(s) to update.

(i.e)
Using the same example as above, we can do various updates on table 
products.
	CREATE TABLE products(
		product_no integer,
		name text,
		price numeric
	);

	UPDATE products SET price = 10 WHERE price > 5;
		(all prices > 5 will be changed to 10)
	or
	UPDATE products SET price = price * 1.10;

	-you can also update more than one column in a table, by listing
	 more than one assignment in the SET clause

Deleting Data
The delete command is similar to the update command.
(i.e)
Using the same table products from above.  This will delete 
all rows with a price that is equal to 10
	DELETE FROM products WHERE price = 10;

The line below will delete all rows in products
	DELETE FROM products;