1. What is in the database

Read and relationship of the data

SELECT company.name, tag_type.tag, tag_type.type
  FROM company
  	   -- Join to the tag_company table
       INNER JOIN tag_company
       ON company.id = tag_company.company_id
       -- Join to the tag_type table
       INNER JOIN tag_type
       ON tag_company.tag = tag_type.tag
  -- Filter to most common type
  WHERE type='cloud';

Coalesce

coalesce()fuction = specifying a defauly or backup value when a column contains NULL values

• checks arguments in order and returns the first non - NULL value if one exists.
  • coalesce(NULL, 1, 2) = 1
  • coalesce(NULL, NULL) = NULL
  • coalesce(2, 3, NULL) = 2
• fortune 400 data
  • industry continas missing values
  • the commmon industry

-- Use coalesce
SELECT coalesce(industry, sector, 'Unknown') AS industry2,
       -- Don't forget to count!
       count(*) 
  FROM fortune500 
-- Group by what? (What are you counting by?)
 GROUP BY industry2
-- Order results to see most common first
 ORDER BY count DESC
-- Limit results to get just the one value you want
 LIMIT 1;

SELECT company_original.name, title, rank
  -- Start with original company information
  FROM company AS company_original
       -- Join to another copy of company with parent
       -- company information
	   LEFT JOIN company AS company_parent
       ON company_original.parent_id = company_parent.id 
       -- Join to fortune500, only keep rows that match
       INNER JOIN fortune500 
       -- Use parent ticker if there is one, 
       -- otherwise original ticker
       ON coalesce(company_parent.ticker, 
                   company_original.ticker) = 
             fortune500.ticker
 -- For clarity, order by rank
 ORDER BY rank;

Column types and constraints

• Foreign key: value that exists in the referenced column, or NULL
• primary key: unique, not NULL
• unique: values must al be different except for NULL
• Not null: Nut not allowed must have a value
• check constraint: conditions on the values
  • column1 ? 0
  • columnA ? columnB

Data types

common

• Numeric
• Character
• Data