You need a SQL Sandbox!

SQL- A guide to keep that programming sharp, test new ideas and improve flow, or to reduce your learning curve!

On MacOS, we cover installing an excellent Open Source IDE for SQL DBeaver Community, Open Source Database Software DuckDB, load open source data over https into our database, and end with SQL questions that have a statistical flavor on penguins. DuckDB removes the overhead of server and treats a file as a table.

Install DBeaver Community SQL IDE

Start a terminal window and run:

    brew install --cask dbeaver-community

Note: Control-Shift-F auto formats the SQL.

Install DuckDB

Also on the command line, run the following after changing to the correct path that is applicable to your machine, i.e. /Users/chris/.

    brew install duckd
    # DuckDB does not place any directories in the $HOME directory
    mkdir /Users/chris/.duckdb 

    # To create a persistent database
    # use .exit to quit
    duckdb /Users/chris/.duckdb/bokeh.duckdb

DuckDB (for a “serverless” Database)

Import data about penguins types, Adélie¹, Chinstrap², and Gentoo ³.

1. Open DBeaver
2. Click the plug to add a new Database connection
3. Select DuckDB, note: if prompted download driver.
4. Select Next
5. Enter a file name or path + filename,
   1. This will become the name of your database file. Enter the same file path as entered in the install DuckDB section.
6. Select Finish and START CODING!

Import Data

 /*
 Import data
 source: [Environmental Data Initiative](https://portal.edirepository.org/nis/home.jsp)
 --note: in duckdb multi statements, each statement needs to end in a ;
 */
 --DROP TABLE IF EXISTS main.penguins;
CREATE TABLE main.penguins AS 
(
SELECT * FROM READ_CSV("https://pasta.lternet.edu/package/data/eml/knb-lter-pal/219/3/002f3893385f710df69eeebe893144ff")
UNION BY NAME
SELECT * FROM READ_CSV("https://pasta.lternet.edu/package/data/eml/knb-lter-pal/220/7/e03b43c924f226486f2f0ab6709d2381")
UNION BY NAME
SELECT * FROM READ_CSV("https://pasta.lternet.edu/package/data/eml/knb-lter-pal/221/8/fe853aa8f7a59aa84cdd3197619ef462")
);

The penguins table should now have 344 records across 3 species.

Check for invariants

SELECT
	count(*) AS total_cnts
	, SUM(CASE 
	WHEN sex = 'FEMALE' THEN 1 ELSE 0 END) AS female_ind
	, SUM(CASE 
	WHEN sex = 'MALE' THEN 1 ELSE 0 END) AS male_ind
FROM MAIN.PENGUINS 

Looks like the number of female penguins is approximately the same number as the male penguins, which supports the assumptions that samples should be random and is an invariant metric check for this experiment. The total counts also show the table loaded with 344 rows.

Species and Flipper Sizes

DuckDB Functions I have not seen functions for kurtosis or skewness; however, these functions exist in Duck. As a reminder skewness is the measure of asymmetry of the distribution around its mean. Kurtosis is the measure of the tailedness of the distribution. Window Functions

What is the average flipper length by Species and the standard deviation of the flippers? Which Species do you think has a statistically different Flipper size than another?

SELECT
Species
, AVG("Flipper Length (mm)") AS avg_flipper_length_mm
, count(*) AS cnts
, stddev_pop("Flipper Length (mm)") AS stdev_pop
, stddev_samp("Flipper Length (mm)") AS stdev_samp
FROM
MAIN.PENGUINS
GROUP BY
Species

Rank

See what happens with a rank when there is a tie for body mass, more than 3x3 records are returned. Then see the impact of changing to row number and dense rank.

WITH cte_rank AS ( 
SELECT 
Species 
, studyName 
, "Sample Number" 
, "Body Mass (g)"
, rank() OVER (PARTITION BY Species ORDER BY "Body Mass (g)") AS rank
FROM main.penguins 
)
SELECT 
*
FROM cte_rank 
WHERE RANK <= 3

Row number always returns 9 records when filtered to less than or equal to 3. Depending on the table sort the row numbering for an associated sample number could be different. To have consistent results, sorting by a guaranteed column like sample number would be good practice.

WITH cte_rank AS ( 
SELECT 
Species 
, studyName 
, "Sample Number" 
, "Body Mass (g)"
, ROW_NUMBER () OVER (PARTITION BY Species ORDER BY "Body Mass (g)") AS rank
FROM main.penguins 
)
SELECT 
*
FROM cte_rank 
WHERE RANK <= 3

Dense rank provides continuous values, ex. no skipping 2 to get to 3, though multiple values of the same rank are allowed. Therefore, even more records are returned in ties when using dense rank.

WITH cte_rank AS ( 
SELECT 
Species 
, studyName 
, "Sample Number" 
, "Body Mass (g)"
, DENSE_RANK  () OVER (PARTITION BY Species ORDER BY "Body Mass (g)") AS rank
FROM main.penguins 
)
SELECT 
*
FROM cte_rank 
WHERE RANK <= 3  

Reminders

1. Upper Case key words
2. Use Indentation and separate columns on individual lines, one thought one line!
3. In case you divide by zero, put a NULLIF in the denominator.