SQL notes

SQL Notes

What are database tables, column, and rows? What's the purpose of each?

  Table is a collection of data organized into columns and rows.

  Rows are usually one record. Usually horizontal.
  
  Columns are usually vertical, and contain similar information about multiple records.


How's a database similar and different from a spreadsheet?
  
  Databases can... uh... do... more... things...? And aren't necessarily arranged for visual understanding like spreadsheets are?
  
What's the meaning and purpose of SELECT? What about WHERE?

  No idea.
  
What's a foreign key? What's it used for?

  A foreign key is a key from a related table, used to establish the relationship between the two tables.
  
  
Notes:
Excel/database
  Excel is considered the world's most popular programming language
  Excel is not really a database; you can put multiple types of data in a column, for example
  The power of a database is in linking data across different tables, Excel isn't very good at that
  
Database
  Can refer to database engine: a program that runs on a computer and facilitates the use of a database.
    (examples: MySQL, Postgres, SQLite, etc)
  Can also refer to an instance running on the engine
  We will install Postgres at some point, we can have several databases running on Postgres
    (will probably use Postgres for our project)
  
Database Instance
  Collection of data
  Made of tables
          tables are made of columns and rows
  Tables are like Excel spreadsheets, but rigid; you can't just put any kind of data anywhere you want to.
    Columns: have a name, and a datatype, possibly a size
      Name: usually has to conform to a naming convention
        Rails naming convention: snake_case
      Datatype: integer, varchar, datetime
        integer - requires a size
        varchar - requires a size
        datetime
        extended datatypes
          text - only use when it's necessary (flexible size, but slow)
          autoincrement - for holding a piece of data that should be unique across all records (example: id)
          
    Rows: a record of data, conforming to the column definitions
    
Primary key: used to uniquely identify a record within a single table
  Unique within one table
  Can be re-used between tables (can totally have two tables with two different, unrelated records that both have an ID of 1)
  Generated by the engine
  Usually used with an autoincrement column
  Typically referred to as an autoincrement primary key
  Always(!!!) use one
  Has no domain meaning (IE don't use email or SSN)
    (so it has no need to change, ever!)
  
SQL Queries
  SQL language is "declarative" language
    (So you tell it what results  you want, how to get it back is up to the  engine)
    (Contrast with Ruby, which is "imperitive" language, where you have to tell it how to get the results you want)
  Issue a single statement
    get a result
  Statements come in three types
    SELECT - get back existing data (80+% of all queries, so most engines are optimized for select queries)
      SELECT <columns> FROM <table>; (note: select statements end in semi-colun)
        example select first_name from users;
        
      WHERE - used to add conditions to SELECT statements
        example select first_name from users where id="1";
        
      another example
        Select first_name, last_name
        From users
        Where first_name LIKE 'B%'
        AND last_name LIKE 'B%';
        
    INSERT - add new data/change existing data (< 5% of all queries)
      INSERT INTO <tablename>
      (col1, col2)
      VALUES (val1, val2);
      
      Example:
        Insert Into users
        (first_name, last_name)
        VALUES ("Rachel", "Warbelow");
        
    UPDATE - Easy to  fuck up - you might overwrite all your data for those columns
      UPDATE tablename
      col1 = val1,
      col2 = val2
      WHERE condition;
      
      Example:
      Update users
      first_name = "Rachel"
      WHERE id="6";
      
    DELETE - remove data (< 1% of all queries)
  
  Foreign key
    Convention for connecting  one record to another/many
    
    Example 
      Users
      id first_name
      1  "Rachel"
      2  "Jorge"
      
      Orders
      id  total user_id
      1   $5    2
      2   $12   2
      3   $10   1
    
    In the above example, the user_id on the Orders table is a foreign key for the Users table.
    
    This is where plurality/one_to_many, one_to_one etc comes into play.