Australian Employee DATAbase by detailed occupation, location and year (2002-14); (plus Sole Traders)

The ATO (Australian Tax Office) made a dataset openly available (see links) showing all the Australian Salary and Wages (2002, 2006, 2010, 2014) by detailed occupation (around 1,000) and over 100 SA4 regions. Sole Trader sales and earnings are also provided. This open data (csv) is now packaged into a database (*.sql) with 45 sample SQL queries (backupSQL[date]_public.txt).<br>See more description at related Figshare #datavis record. <br>#dataTotals - Salary and WagesYearWorkers (M)Earnings ($B) 20028.5285<br>20069.4372<br>201010.2481<br>201410.3584<br><br><br>#dataTotal - Sole TradersYearWorkers (M)Sales ($B)Earnings ($B)20020.9611320061.0881920101.11122620141.19630<br>#links See ATO request for data at ideascale link below.See original csv open data set (CC-BY) at data.gov.au link below.This database was used to create maps of change in regional employment - see Figshare link below (m9.figshare.4056282).<br>#packageThis file package contains a database (analysing the open data) in SQL package and sample SQL text, interrogating the DB. DB name: test. There are 20 queries relating to Salary and Wages.<br>#analysisThe database was analysed and outputs provided on Nectar(.org.au) resources at: http://118.138.240.130.This is only resourced for max 1 year, from July 2016, so will expire in June 2017. Hence the filing here. The sample home page is provided here (and pdf), but not all the supporting files, which may be packaged and added later. Until then all files are available at the Nectar URL.<br>#installIMPORT: DB SQL dump e.g. test_2016-12-20.sql (14.8Mb)1.Started MAMP on OSX.1.1 Go to PhpMyAdmin2. New Database: 3. Import: Choose file: test_2016-12-20.sql -&gt; Go (about 15-20 seconds on MacBookPro 16Gb, 2.3 Ghz i5)4. four tables appeared: jobTitles 3,208 rows | salaryWages 209,697 rows | soleTrader 97,209 rows | stateNames 9 rowsplus views e.g. deltahair, Industrycodes, states5. Run test query under **#; Sum of Salary by SA4 e.g. 101 $4.7B, 102 $6.9B<br>#sampleSQLselect sa4,<br>(select sum(count) from salaryWageswhere year = '2014' and sa4 = sw.sa4) as thisYr14,<br>(select sum(count) from salaryWageswhere year = '2010' and sa4 = sw.sa4) as thisYr10,<br>(select sum(count) from salaryWageswhere year = '2006' and sa4 = sw.sa4) as thisYr06,<br>(select sum(count) from salaryWageswhere year = '2002' and sa4 = sw.sa4) as thisYr02<br>from salaryWages sw<br>group by sa4order by sa4