Differences

This shows you the differences between two versions of the page.

--- onlinedb [2022/03/08 10:44]
qcbs [Useful resources]
+++ onlinedb [2025/04/08 14:34] (current)
qcbs [Exercise 3 - SELECT statement]
@@ Line 5: / Line 5: @@
-[[http://prezi.com/z1ib62c5ybmx/|Link to Prezi presentation]]
+[[https://prezi.com/view/Rd0TlmRUi3P27mbPYECg/|Link to Prezi presentation]]
-{{ ::prezi_opendb_qcbs.pdf |PDF version of the Prezi presentation}}
 ===== Useful resources =====
   * [[https://www.postgresql.org/docs/current/static/reference.html|PostgreSQL Reference Manual]]
+  * [[https://www.postgresqltutorial.com|PostgreSQL Tutorial]]
   * [[http://link.springer.com/book/10.1007%2F978-1-4302-0018-5|Beginning databases with PostgreSQL]]
   * [[https://wiki.postgresql.org/wiki/Main_Page|PostgreSQL wiki]]
-  * [[http://www.pgsql.ru/db/pgsearch/|PGSearch to search PostgreSQL related websites and lists.]]
   * [[https://mystery.knightlab.com/#experienced|SQL Murder Mystery. Solve a murder mystery while practising your SQL skills.]]
   * [[https://pgmodeler.io/download| PgModeler - Tool to design databases and create entity-relation diagrams]]
@@ Line 34: / Line 33: @@
 ===== Accessing PSQL =====
 **On Windows and Mac**:
-  * Find PostgreSQL 10.X in the Start menu and click on "SQL shell (psql)"
+  * Find PostgreSQL 14.X in the Start menu and click on "SQL shell (psql)"
   * In the shell, accept the default values by pressing enter for the username and server name and type the proper Postgres password (chosen above). Note that you will not see anything while typing the password. This is normal.
@@ Line 45: / Line 44: @@
 Stop the postgres server (launch daemon):
 <file>
-sudo launchctl unload /Library/LaunchDaemons/com.edb.launchd.postgresql-9.5.plist
+sudo launchctl unload /Library/LaunchDaemons/com.edb.launchd.postgresql-14.plist
 </file>
   - Edit data/pg_hba.conf and change 'md5' to 'trust' in the 'local' and 'host' lines
@@ Line 51: / Line 50: @@
   - Start the daemon
 <file>
-sudo launchctl load /Library/LaunchDaemons/com.edb.launchd.postgresql-10.plist
+sudo launchctl load /Library/LaunchDaemons/com.edb.launchd.postgresql-14.plist
 </file>
@@ Line 115: / Line 114: @@
 ====== Group discussion ======
-[[http://qcbs.ca/wp-content/uploads/2012/10/Crustacean_plankton_Canada.ods|Click here to download the original spreadsheet]]
+[[http://qcbs.ca/wp-content/uploads/2012/10/Crustacean_plankton_Canada.ods|Click here to download the original spreadsheet (ODS format)]]
+{{ ::crustacean_plankton_canada.xlsx |Click here to download the original spreadsheet (XLSX format)}}
@@ Line 324: / Line 325: @@
 CREATE TABLE species (species_id INT NOT NULL, full_name varchar(255), short_name VARCHAR(100));
 COPY species FROM 'C:/bdlibre/species_acro.csv' WITH csv HEADER DELIMITER AS ',';
+</file>
+===== Exercise 3 - Importing data into SQLite in R =====
+First install the RSQLite package
+<file rsplus>
+install.packages('RSQLite')
+</file>
+To load it in R, you will need to load the DBI package
+<file rsplus>
+library(DBI)
+</file>
+Then, create the file containing the database
+<file rsplus>
+mydb <- dbConnect(RSQLite::SQLite(), "QCBS_Workshop.sqlite")
+</file>
+Set this to where you have downloaded the files
+<file>
+setwd('C:\\User\MyName\Workshop\')
+</file>
+You can now load the CSV files in R as data frames
+<file>
+lakes <- read.csv('lakes.csv')
+lakes_species <- read.csv('lakes_species.csv')
+species_acro <- read.csv('species_acro.csv')
+</file>
+And then load those dataframes into the database
+<file rsplus>
+dbWriteTable(mydb, "lakes", lakes)
+dbWriteTable(mydb, "lakes_species", lakes_species)
+dbWriteTable(mydb, "species_acro", species_acro)
+dbListTables(mydb)
+</file>
+You can then run most queries below with dbGetQuery. For example:
+<file rsplus>
+lake_prairies<-dbGetQuery(mydb, "SELECT * FROM lakes WHERE ecozone='Prairies'")
 </file>
 ===== REFERENCES =====
@@ Line 405: / Line 448: @@
 **Question 1** :?: -  How many lakes in British Columbia receive more than 3000 mm of precipitation (totp_ann column)?
-\\ ++answer| 12 ++ \\
+\\ ++answer| SELECT count(*) FROM lakes WHERE province='BRITISH COLUMBIA' AND totp_ann>3000; 12 ++ \\
-**Question 2** :?: -  What is the average Elevation (mean_ele column) of all lakes in the Montane Cordillera Ecozone (use the avg() operator)?
+**Question 2** :?: -  What is the average Elevation (mean_elev column) of all lakes in the Montane Cordillera Ecozones (use the avg() operator)?
-\\ ++answer| 1364.36 ++ \\
+\\ ++answer| SELECT avg(mean_elev) FROM lakes WHERE ecoprov LIKE '%Montane Cordillera'; 1364.36 ++ \\
 \\
-Note: the % operator is a wildcard used to replace the beginning or the end of a string.
+Note: the % operator is a wildcard used to replace the beginning or the end of a string in a query using LIKE.
 \\
 Select all lake names that contain the word 'Small'
@@ Line 428: / Line 471: @@
 **Question 3** :?: - What is the maximum latitude of all lakes in Ecozones with names that start with 'Taiga'
-\\ ++answer| 68.8 ++ \\
+\\ ++answer| SELECT max(latitude) FROM lakes WHERE ecozone like 'Taiga%'; 68.8 ++ \\
 **Question 4** :?: - How many species of Daphnia are there in the species table?
-\\ ++answer| 17 (21 have the word 'Daphnia' somewhere in their name) ++ \\
+\\ ++answer| SELECT count(*) FROM species_acro WHERE full_name like 'Daphnia%'; 17 (21 have the word 'Daphnia' somewhere in their name) ++ \\
 ===== Exercise 4 - GROUPING =====
@@ Line 450: / Line 493: @@
 **Question 5** :?: -  Which province has the lake with the highest maximum temperature (column tmax_ann)?
-\\ ++answer| BRITISH COLUMBIA 14.60 ++ \\
+\\ ++answer| SELECT province, max(tmax_ann) FROM lakes GROUP BY province ORDER BY max DESC LIMIT 1;
+BRITISH COLUMBIA 14.60 ++ \\
 Update statement:
@@ Line 639: / Line 683: @@
   * In Access, click on External data... ODBC database, and then select "Link to the data source by creating a linked table" and then choose New DSN name. Specify a name for the file, and then fill the information to connect to the database. You can then choose the tables you want to link.
   * Those table will then be linked to PostgreSQL and you can edit then as you would with other Access tables.
-========= Basic concepts for the creation of web forms =========
-  * [[https://prezi.com/xd-ueiomgbj-/basic-concepts-for-the-creation-of-web-forms/|Access the Prezi presentation here]]
-**Useful link**
-  * [[http://www.codecademy.com/|Code Academy]] Great website to learn HTML, CSS, PHP, Javascript, JQuery or other.
-  * [[http://link.springer.com/book/10.1007/978-1-4302-0136-6|Beginning PHP and PostgreSQL 8. ]] Book that can be downloaded in e-book format. A bit outdated.
-**Main steps**
-  - Creation of a web interface with html code and CSS.
-  - Creation of a database to accept the form data.
-  - Creation of PHP code to receive the form data and send it to the database.
-  - Possibility to enrich the form with Javascript/JQuery
-===== Installation of PHP, PostgreSQL, apache and PHPPGAdmin =====
-[[https://bitnami.com/tag/postgresql|Download and install the WAPP server for pour Windows, or MAPP for Mac.]]
-For linux:
-<file>
-sudo apt-get install postgresql-9.5 apache2 php5 phppgadmin php5-pgsql
-</file>
-===== Part 1 - HTML form =====
-Main components needed for an HTML file
-<file html>
-<!DOCTYPE html>
-<html>
-<head>
-<title>Document title</title>
-</head>
-<body>
-The page content is here!
-</body>
-</html>
-</file>
-Creation of a form
-The basic form
-<file html>
-<form method="POST" action="phpfile.php">
-<input type="text">
-</form>
-</file>
-[[http://www.w3schools.com/tags/att_input_type.asp|List of html input types]]
-Text input
-<file html>
-<input type="text" name="name_text">
-</file>
-Dropdown menu
-<file html>
-<select name="select1">
-<option value="1">Option 1</option>
-<option value="2">Option 2</option>
-<option value="3">Option 3</option>
-</select>
-</file>
-Check box
-<file html>
-<input type="checkbox" name="poutine" value="yes"> I like poutine
-<input type="checkbox" name="sushis" value="yes"> I like sushi
-<input type="checkbox" name="poutine_sushis" value="yes"> I like poutine topped with sushi
-</file>
-Radio buttons
-<file html>
-I am a...
-<input type="radio" name="gender" value="m" > Man
-<input type="radio" name="gender" value="w"> Woman
-</file>
-Text area
-<file html>
-<textarea name="text_zone">
-</textarea>
-</file>
-===== Form styling with CSS =====
-[[http://www.w3schools.com/cssref/|CSS reference]]
-You can give an "id" to any html element, but there can only one element per id.
-<file html>
-<input type="text" name="fieldname" id="fieldid">
-</file>
-We can then reference it in the header of the css file with the pound symbol (#)
-<file html>
-<head>
-<style>
-#fieldid {
- width:300px;
- height:30px;
- color:red;
- background-color:#ffff00;
- font-family:verdana,arial;
- font-size:14px;
- font-weight:bold;
- padding:0px 5px 0px 5px; #top right bottom left
-}
-</style>
-</head>
-</file>
-We can also give a class to any html element. This class can be repeated for multiple elements.
-<file html>
-<input type="text" name="name_text1" class="class_text">
-<input type="text" name="name_text2" class="class_text">
-<input type="text" name="name_text3" class="class_text">
-</file>
-Et on peut y faire référence dans l'entête de style css du fichier html avec le point "."
-We can then reference it in the style section of the html header with the "."
-<file html>
-<head>
-<style>
-.class_text {
- width:300px;
- height:30px;
- color:red;
- background-color:#ffff00;
- font-family:verdana,arial;
- font-size:14px;
- font-weight:bold;
- padding:0px 5px 0px 5px; #top right bottom left
-}
-</style>
-</head>
-</file>
-====== Part 2 - Creation of the associated database ======
-[[https://secure.qcbs.ca/systeme/phppgadmin/|Link to phppgadmin]]
-====== Part 3 - PHP language basics ======
-All php commands must be contained within
-<file php>
-<?php
-PHP code is here
-?>
-</file>
-Comments
-<file php>
-<?php
-// Comments in a line
-/*
-Comments section
-*/
-?>
-</file>
-<file php>
-<?php
-// Creation of variables
-$myvar=5;
-$myvar='this is text';
-// Creation of an array
-$myarray=array('value1','value2','value3');
-echo $myarray[0];
-//value1
-$myarray=[];
-$myarray[0]='value1';
-$myarray[1]='value2';
-// Creation of a "key-value" array
-$myarray=array(
-'key1'=>'key1',
-'key2'=>'key2',
-'key3'=>'key3',
-);
-echo $myarray['key1'];
-//value1
-?>
-</file>
-Loops and if/else
-<file php>
-<?php
-if ($f==5){
-//Do something
-}else{
-//Do something else
-}
-for ( $i=0;​$ i<10;$i++){
-echo 'i='.$i;
-}
-//i=0
-//i=1
-//...
-//i=9
-foreach ( $myarray as$ my){
-//Do something here with $my
-?>
-</file>
-====== Part 4 - Insertion into PostgreSQL via PHP ======
-[[http://php.net/manual/en/book.pgsql.php|PostgreSQL functions in PHP]]
-This PHP file is the one which is specified in the "ACTION" part of the <form> field
-<file php>
-<?php
-// Connexion and selection of the database
-$dbconn = pg_connect("host=localhost dbname=database user=username password=my_password");
-// The $_POST contains all the data from the form. Each 'key' in this array corresponds to the name given to each html element.
- $element1=$ _POST['name_element1'];
- $element2=$ _POST['name_element2'];
- $element1=pg_escape_string($ element1);
- $element2=pg_escape_string($ element2);
-// Inserted into the databse
- $query = "​INSERT INTO ma_table (column1,​column2) VALUES ($ element1,$element2)";
- $result = pg_query($ query);
-// Close the connexion
-pg_close($dbconn);
-echo "Form successfully entered into the database|";
-?>
-</file>
-Accéder à différents types d'éléments
-<file php>
-//Check boxes
-foreach ( $_POST['​checkbox'​] as$ check){
-   //Faire quelque chose ici avec $check
-}
-//Convertir les valeurs en liste séparée par des virgules
- $check=implode(',',​$ _POST['checkbox']);
-</file>
-Retrouver des éléments dans la base de données
-<file php>
-// Connexion et sélection de la base de données
-$dbconn = pg_connect("host=localhost dbname=base_donnees user=nom_usager password=mot_de_passe");
-$query="SELECT * FROM nom_table";
-// Insertion dans la base de données
- $result = pg_query($ query);
-while ( $t=pg_fetch_array($ result)){
-	echo  $t['​prenom'​].'​ '​.$ t['nom_famille'];
-}
-// Fermer la connexion
-pg_close($dbconn);
-</file>
 ====== Linking R with PostgreSQL ======
@@ Line 935: / Line 714: @@
 lakes <- tbl(src, "lakes") # Define lakes table
 lakes_qc<-filter(lakes, province  %=% 'QUEBEC') # Select lakes in Quebec
-prov_tmean<-summarise(group_by(lakes, province), mean(tmean_an)) # Mean annual temperature per province
+prov_tmean<- lakes |> group_by(province) |> summarise(mean(tmean_an)) # Mean annual temperature per province
-prov_tmean=collect(prov_tmean) # Transfer result to standard R data frame
+prov_tmean <- collect(prov_tmean) # Transfer result to standard R data frame
-lakes_qc2<-tbl(src, sql("SELECT * FROM lakes WHERE province='QUEBEC'")) #Perform any SQL statement
+lakes_qc2 <- tbl(src, sql("SELECT * FROM lakes WHERE province='QUEBEC'")) #Perform any SQL statement
 </file>