o
- Practice on babynames2014
- Create babynames1980
- Inner JOIN babynames
- Left JOIN babynames
- (which names are not in it at all?)
- Subqueries
- LEFT JOIN top 10 versus not top 10
- Create aggregate table of all names
-
Create aggregate
- fill in year
- can you do it with just one table?
- Create genderizer (option to do it with subqueries)
The florida salaries database
# basic queries
# sub-queries
# Full on analysis
draft
- Querying the baby database of 2014 babynames
- by like of names
- number of girls/boys
- Creating a table of new babynames
- Adding a column
- Importing a file
- Updating the year
- Import another file
- Update the year
- Add an index
- Aggregate analysis
- Average per year
- Max/min per year
- Names that have fallen in popularity
- Creating the boys/girls tables
- Creating genderizer
- Doing joins
- Doing self joins
- Doing self joins and aggregates
- Exploring the Florida Salary data
Create and import table data
#
Creating and importing table data
How to create a table
How to import data into a table
Indexing and updating table data
How to add a column to a table
How to update a column's values
How to index columns on a table for faster searching
How to create a new column and add a uppercased version of an existing column to it, and then index that column.
A review of basic SQL
Creating new tables from existing tables
- How to create a table from babynames containing just the boy names
- How to create a table from babynames containing just the girl names
Doing Inner Joins
- All names that were recorded for boys and girls in that year
- All names belonging to at least 50 girls and 50 boys
- Ranking names in order of near parity between boys and girls
Doing Left Joins
- All names that were given to girls but not to boys
- All names that were given to boys but not to girls
- All names that were given exclusively to girls and to at least 100+ girls
- All names that were given exclusively to boys and to at least 100+ boys
Creating a gendered name of tables
This is a combination of all the past lessons
- Insert all names exclusive to girls
- Insert all names exclusive to boys
- Insert all names, non-exclusive, and contain a calculation
- Uppercase the name, and then index the new table
Joining university salary data with the gendered names data
- How many men and women work at Florida's state universities?
- Of the top 100 highest paid employees at Florida universities, how many are many men and how are women?
- Of the top 1000 highest paid employees at Florida universities, how many are many men and how are women?
-
Of the 020000 lowest paid employees at Florida universities, how many are many men and how are women?
- What is the estimated gender breakdown of professors at Florida's public universities
- What is the estimated gender breakdown at Florida's public universities between professors and adjuncts?
-
What is the average salary gap by position?
- How many names are not in the baby names data?
Aggregate analysis
- Average by position and gender
- Custom breakpoints
- All positions having