Atelier offert par Guillaume Larocque, professionnel de recherche au CSBQ.

Lien vers la présentation Prezi

Lien vers la présentation simultanée

• PostgreSQL Reference Manual
• Beginning databases with PostgreSQL
• PostgreSQL wiki
• PGSearch pour chercher des sites web en lien avec PostgreSQL.

• Visitez la page de EntrepriseDB et téléchargez la version appropriée pour votre plate-forme. Choisissez la version 64 bits.
• Éxécutez le fichier et suivez les étapes d'installation en acceptant les options par défaut.
• Quand on vous le demande, choisissez un mot de passe approprié pour l'utilisateur postgres. Prenez en note ce mot de passe.

Dans un terminal de commande, tapez:

sudo apt-get install postgresql-9.5 libreoffice libreoffice-sdbc-postgresql

Il est possible que la version de PostgreSQL disponible pour votre distribution soit antérieure à 9.5. Dans ce cas, installez la version disponible.

Pour d'autres distributions, vérifiez cette page.

• Cliquez ici, téléchargez et installez LibreOffice.

Installation de MySQL

Sur Windows et Mac:

• Trouvez PostgreSQL 9.6 dans le menu démarrer/Finder et cliquez sur “SQL shell (psql)”
• Acceptez les paramètres par défaut en cliquant sur ENTER et tapez le mot de passe approprié. Vous ne verrez rien lorsque vous taperez votre mot de passe, c'est normal.

Sur Linux Tapez simplement:

psql

dans un terminal.

Si ça ne fonctionne pas sur Mac ou Linux...

sudo launchctl unload /Library/LaunchDaemons/com.edb.launchd.postgresql-9.6.plist

sudo launchctl load /Library/LaunchDaemons/com.edb.launchd.postgresql-9.6.plist

Autres instructions sous Linux...

     $ sudo -u postgres psql
     postgres=> alter user postgres password 'apassword';
     postgres=> create user yerusername createdb createuser password 'somepass';
     postgres=> create database yerusername owner yerusername;

createuser -P -s -e your_linux_username

psql

SHOW hba_file;

local all postgres peer

local all postgres md5

psql worshop -U your_username -W

Cliquez ici pour télécharger le jeu de données (feuille de calcul en format LibreOffice Calc)

Si vous voulez, vous pouvez créer un nom d'usager autre que 'postgres'. Pour ce faire, tapez la commande suivante dans psql, en remplacant nom_usager et mot_de_passe par des valeurs choisies.

CREATE USER your_username WITH SUPERUSER PASSWORD 'your_password';

Créer une base de données 'atelier'

CREATE DATABASE atelier;

• Ouvrez Libre Office Base et sélectionnez “Nouvelle…base de données”.
• Se connecter à une base existance>PostgreSQL
• Pour l'URL de la source de données, tapez ceci:

dbname=atelier host=localhost port=5432

• Cliquez sur suivant et indiquez le nom d'usager, et choisissez 'mot de passe requis'. Vous pouvez ensuite cliquer sur 'Tester la connexion' pour vous assurer que ça fonctionne.
• Vous pouvez ensuite sauvez l'information de connexion à la base de données sous un fichier sur votre ordinateur, de façon à facilement ré-ouvrir la base de données par la suite.

Se connecter à la base de données

\c atelier

Voir la liste de bases de données disponibles:

\l

Voir la liste des tables:

\d+

Voir les colonnes dans une table

\d nom_de_la_table

Lister tous les usagers

\du

Quitter Postgresql

\q

Formats de colonnes les plus communs

Liste complète des type de données PostgreSQL.

Assurez-vous d'avoir créé la base de données atelier et de vous y être connecté.

\c atelier

Créer la table

CREATE TABLE obs_oiseaux (obs_id SERIAL PRIMARY KEY,
obs_time timestamp,espece text,nombre integer,lat real,"long" real);

Note: nous utilisons les doubles guillements ”“ pour marquer l'utilisation d'un mot réservé, d'un mot avec des caractères spéciaux ou des espaces dans le nom d'une table ou d'une colonne.

Insérer une nouvelle ligne dans la table

INSERT INTO obs_oiseaux (obs_time,espece,nombre,lat,"long") VALUES ('2012-06-19 12:31:16','Sturnus vulgaris',40,45.3522,-73.7930);

Notez que nous n'avons pas entré le champ obs_id puisqu'il est entré automatiquement avec le type de caractère SERIAL, tel que spécifié lors de la création de la table.

Pour visualiser la table

SELECT * FROM obs_oiseaux;

Question 1 Reproduisez la table suivante dans PostgreSQL, nommez la 'Acteur' et visualisez là dans PostgreSQL.

Note: quand on entre du texte, il faut doubler les ' dans le texte ('') pour éviter que PostgreSQL ne les utilise comme marqueur de fin de l'entrée texte.

Téléchargez les fichiers suivants sur votre ordinateur (utilisez le bouton de droite pour cliquer sur le lien… sauvergarder sous). Lakes.csv species_acro.csv lakes_species.csv

Sous Windows: copiez ces trois fichiers vers un dossier nommé 'bdlibre' dans le répertoire C: (i.e. C:/bdlibre). Sous Mac ou Linux: copiez ces trois fichiers vers un dossier facilement accessible (e.g. /home/votrenome/bdlibre). Modifiez les commandes plus bas en remplaçant "C:/bdlibre" avec le chemin vers ce dossier.

Créer une table contenant l'information environnementale de chaque lac. - Lakes.

CREATE TABLE lakes (lake_id SERIAL PRIMARY KEY,numero INT, lake_name text, 
province text, latitude DEC(10,5),longitude DEC(10,5), number_of_species INT, ecoprov VARCHAR(50), 
ecozone VARCHAR(50), gss DEC(10,2),gse DEC(10,2), gsl DEC(10,2), gdd10 DEC(10,2), egdd DEC(10,2), 
mean_ele DEC(10,2), pe_ann_p DEC(10,2), totp_ann DEC(10,2), srann_me DEC(10,2), shann_me DEC(10,2), 
tmax_ann DEC(10,2), tmean_an DEC(10,2), vpann_me DEC(10,2));

MySQL

CREATE TABLE lakes (lake_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY,numero INT, lake_name text, 
province text, latitude DEC(10,5),longitude DEC(10,5), number_of_species INT, ecoprov VARCHAR(50), 
ecozone VARCHAR(50), gss DEC(10,2),gse DEC(10,2), gsl DEC(10,2), gdd10 DEC(10,2), egdd DEC(10,2), 
mean_ele DEC(10,2), pe_ann_p DEC(10,2), totp_ann DEC(10,2), srann_me DEC(10,2), shann_me DEC(10,2), 
tmax_ann DEC(10,2), tmean_an DEC(10,2), vpann_me DEC(10,2));

Charger le fichier CSV dans cette table.

COPY lakes FROM 'C:/bdlibre/lakes.csv' WITH csv HEADER DELIMITER AS ',';

Si cette commande vous donne un message 'access denied' sous Windows

Si cette commande vous donne un message d'erreur 'access denied' sur Mac ou Linux

MySQL

LOAD DATA LOCAL INFILE 'C:/bdlibre/lakes.csv' INTO TABLE lakes FIELDS TERMINATED BY ',' 
ENCLOSED BY '"' LINES TERMINATED BY '\n'   IGNORE 1 LINES;

mysql -u root -p --local-infile=1;

Créez la table contentant les présences de espèces pour chaque lac et charger le fichier CSV dans cette table.- lakes_species

CREATE TABLE lakes_species (lake_id INT NOT NULL, species_id INT NOT NULL);
COPY lakes_species FROM 'C:/bdlibre/lakes_species.csv' WITH csv HEADER DELIMITER AS ',';

MySQL

CREATE TABLE lakes_species (lake_id INT NOT NULL, species_id varchar(25) NOT NULL);
LOAD DATA LOCAL INFILE 'C:/bdlibre/lakes_species.csv' INTO TABLE lakes_species 
FIELDS TERMINATED BY ',' ENCLOSED BY '"' LINES TERMINATED BY '\n'   IGNORE 1 LINES;

Pour la troisième table, nous pourrions utiliser cette commande:

Commande

CREATE TABLE species_acro (species_id INT NOT NULL, full_name varchar(100), short_name varchar(25));
COPY species_acro FROM 'C:/bdlibre/species_acro.csv' WITH csv HEADER DELIMITER AS ',';

Par contre, nous allons utiliser LibreOffice pour se familiariser avec l'interface.

• Ouvrez le fichier species_acro.csv avec LibreOffice Calc (Dans Windows, trouvez LibreOffice Calc dans le menu démarrer). Spécifiez que le fichier est séparé par des virgules.
• Sélectionnez la feuille de calcul en entier avec CTRL-A.
• Ouvrez LibreOffice Base et allez à Edition…coller. Nommez la table “public.species_acro”. Choisissez d'utiliser le nom des colonnes comme entêtes pour la table. Ne spécifiez pas la clé primaire pour l'instant.
• Suivez les étapes… Déplacez les colonnes à droite. Ensuite, spécifiez le format de données (vérifiez le format des données dans le fichier csv). Cliquez sur le bouton de droite sur la colonne 'species_id' et sélectionnez 'Clé Primaire'. Spécifiez int [int4] pour species_id, text [varchar], 200 caractères pour full_name et text [varchar], 30 caractères pour short_name.

Fonctions mathématiques et d'aggrégation (GROUP BY)

Liste complète

Sélection conditionnelle pour utilisation dans une clause 'WHERE'

Ordre des opérations dans une requête 'SELECT':

SELECT columns FROM tables WHERE conditions JOIN GROUP BY columns HAVING condition ORDER BY columns;

Sélectionnez toutes les lignes de la table Lakes

SELECT * FROM lakes;

Sélectionner toutes les lignes, mais n'afficher que les noms des lacs et la province

SELECT lake_name, province FROM lakes;

Voir tous les noms de provinces différents

SELECT DISTINCT province from lakes;

Voir tous le noms de provinces, utiliser une alias (renommer cette colonne dans les résultats) et trier par ordre descendant de nom.

SELECT DISTINCT province as "province name" FROM lakes ORDER BY province DESC;

Voir l'année de naissance de chaque acteur

SELECT prenom, nom_de_famille, EXTRACT(year from date_naissance) FROM acteur;

Voir les initiales de chaque acteur

SELECT prenom, nom_de_famille, concat(substr(prenom,1,1),' ',substr(nom_de_famille,1,1)) as initiales FROM acteur;

Sélectionner tous les lacs du Québec où la température annuelle moyenne est en bas de -5 C.

SELECT lake_name, tmean_an FROM lakes WHERE province='QUEBEC' AND tmean_an<-5;

Ou, pour compter le nombre de lacs

SELECT count(*) FROM lakes WHERE province='QUEBEC' AND tmean_an<-5;
autre alternative:
SELECT count(lake_id) FROM lakes WHERE province='QUEBEC' AND tmean_an<-5;

Question 1 - Combien de lacs en Colombie-Britannique reçoivent plus de 3000 mm de précipitation (colonne totp_ann)?

réponse

SELECT count(*) FROM lakes WHERE totp_ann>3000 AND province='BRITISH COLUMBIA';

Question 2 - Quel est l'altitude moyenne (colonne mean_ele) de tous les lacs dans l'écozone 'Montane Cordillera' (utilisez la fonction avg())?

réponse

SELECT avg(mean_ele) FROM lakes WHERE ecozone='Montane Cordillera';

Note: Le symbole % est utilisé pour remplacer le début ou la fin d'une entrée textuelle.
Sélectionnez tous les noms de lacs qui contiennent le mot 'Small'

SELECT lake_name FROM lakes WHERE lake_name like '%Small%';

Sélectionnez tous les noms de lacs qui contiennent le mot 'Small' et qui ne sont pas situés en Ontario

SELECT lake_name, province 
FROM lakes
WHERE lake_name like '%Small%' AND province!='ONTARIO';
autre possibilité:
SELECT lake_name, province 
FROM lakes 
WHERE lake_name like '%Small%' AND province NOT LIKE 'ONTARIO';

Question 3 - Quelle est la latitude maximale de tous les lacs dans un Ecozone dont le nom commence par 'Taiga'

réponse

SELECT max(latitude) FROM lakes WHERE ecozone LIKE 'Taiga%';

Question 4 - Combien d'espèces de Daphnia sont-elles listées dans la table species_acro?

réponse

SELECT count(*) FROM species_acro WHERE full_name like 'Daphnia%';

Calculez la température annuelle moyenne de tous les lacs dans chaque province

SELECT province, avg(tmean_an) as mean_an_t
FROM  lakes
GROUP BY province;

Même table, mais triée par ordre de températures croissantes

SELECT province, avg(tmean_an) as mean_an_t
FROM  lakes
GROUP BY province ORDER BY avg(tmean_an);

Question 5 - Quelle province a le lac ayant la température maximale (tmax_ann) la plus élevée?

réponse

SELECT province, max(tmax_ann) as max_temp FROM lakes GROUP BY province ORDER BY max_temp DESC;

Commande 'UPDATE'

Changer toutes les noms de province qui contiennent 'NWT', pour qu'ils aient le même nom ('NWT'). - Faites attention, cette opération modifie la table!

UPDATE lakes
SET province='NWT'
WHERE province like '%NWT%';

Compter le nombre d'espèces dans chaque lac. Afficher le nom du lac et le nombre de chaque espèce.

SELECT lake_name, COUNT(DISTINCT species_id) as num_species FROM lakes, lakes_species 
WHERE lakes.lake_id=lakes_species.lake_id 
GROUP BY lakes.lake_id
ORDER BY num_species DESC;

Même requête, mais on limite l'affichage aux 20 premières entrées.

SELECT lake_name, COUNT(DISTINCT species_id) as num_species FROM lakes, lakes_species 
WHERE lakes.lake_id=lakes_species.lake_id 
GROUP BY lakes.lake_id
ORDER BY num_species DESC LIMIT 20;

Compter le nombre d'espèces dans chaque Ecozone et Ecoprovince.

SELECT ecozone, ecoprov, COUNT(species_id) as num_species FROM lakes, lakes_species 
WHERE lakes.lake_id=lakes_species.lake_id 
GROUP BY ecozone, ecoprov
ORDER BY ecozone, COUNT(species_id);

Question 6 Générer une table qui liste le nom complet de chaque espèce de Daphnia et le nombre de lacs où on retrouve cette espèce.

Réponse

SELECT full_name, count(DISTINCT b.lake_id) as cnt FROM species_acro a, lakes_species b WHERE full_name like 'Daphnia%' AND a.species_id=b.species_id GROUP BY full_name;

Créer une liste de toutes les espèces présentes dans les lacs du Québec, avec les noms complets des espèces et le nombre de lacs dans lesquelles elles sont présentes.

SELECT full_name, count(full_name) 
FROM species_acro,lakes_species,lakes 
WHERE species_acro.species_id=lakes_species.species_id AND  lakes.lake_id=lakes_species.lake_id AND province='QUEBEC' 
GROUP BY full_name;

De façon équivalente…

SELECT full_name, count(full_name) 
FROM lakes
LEFT JOIN lakes_species ON (lakes.lake_id=lakes_species.lake_id)
LEFT JOIN species_acro ON (species_acro.species_id=lakes_species.species_id)
WHERE province='QUEBEC' 
GROUP BY full_name;

Pour toutes les combinaisons possibles de lacs se trouvant dans l'Ecoprovince Baffin Uplands, calculer la différence entre leurs températures annuelles moyennes.

SELECT concat(a.lake_name,'-',b.lake_name) as lakes, a.tmean_an-b.tmean_an as temp_diff 
FROM lakes a, lakes b 
WHERE a.ecoprov='Baffin Uplands' AND b.ecoprov='Baffin Uplands';

Créez une table contenant le fréquence de chaque espèce dans les lacs au sud et au nord du 50e parallèle (latitude).

SELECT a.species_id, count_50south, count_50north
FROM 
 (SELECT species_id, count(d.species_id) as count_50south FROM lakes c, lakes_species d WHERE c.lake_id=d.lake_id AND latitude<=50 GROUP BY species_id) a,
 (SELECT species_id, count(f.species_id) as count_50north FROM lakes e, lakes_species f WHERE e.lake_id=f.lake_id AND latitude>50 GROUP BY species_id) b 
WHERE a.species_id=b.species_id;

Question 7 En utilisant une sous-requête, trouvez l'élévation moyenne (colonne MEAN_ELE) des lacs dans lesquels au moins une espèce de Daphnia est présente.
Réponse 474.627172
Code: SELECT avg(mean_ele) FROM (SELECT DISTINCT a.lake_id, mean_ele FROM lakes a,species_acro b,lakes_species c WHERE b.full_name like 'Daphnia%' AND a.lake_id=c.lake_id AND c.species_id=b.species_id ORDER BY a.lake_id) d;

On vous donne un jeu de données dans le format suivant

On vous dit que des milliers d'autres arbres devront être entrés. Comment pensez-vous construire une base de données pour entreposer cette information?

Créez la table suivante avec la commande 'CREATE TABLE' dans PostgreSQL. Spécifiez le champs espece_id comme la clé primaire avec le type de fichier SERIAL PRIMARY KEY. Vous pouvez ensuite remplir la table avec les valeurs dans Libreoffice Base. Nom de la table: especes_arbres

Créez cette table dans PostgreSQL et créez un formulaire pour la remplir avec le mode ébauche de formulaire, avec des menus déroulants pour sélectionner les espèces et des boutons radios pour la santé des arbres. arbre_id est la clé primaire. Nom de la table: parcelles_arbres

• Dans Windows, téléchargez le pilote PostgreSQLODBC et installez-le sur votre ordinateur.
• Créez vos tables dans PostgreSQL.
• Dans Access, cliquez sur “External data… ODBC database”, et ensuite sélectionnez “Link to the data source by creating a linked table” et choisissez un nouveau nom de DSN. Spécifiez un nom pour le fichier et remplissez l'information pour vous connecter à la base de données. Le serveur est localhost si la base de données est sur votre propre ordinateur. Vous pouvez ensuite choisir les tables que vous voulez lier.
• Ces tables seront ensuite liées avec PostgreSQL et vous pouvez les modifier à partir de Access ou via des formulaire Access.

• Accéder à la présentation Prezi ici.
• Présentation simultanée.

Liens utiles

• Code Academy Site web génial pour apprendre HTML, CSS, PHP, Javascript JQuery, SQL ou autre.
• Beginning PHP and PostgreSQL 8. Livre téléchargeable en e-book. Un peu vieux.

Étapes principales

1. Création d'une interface web avec le code html et CSS.
2. Création d'une base de données pour accueillir les données du formulaire.
3. Création d'un code PHP qui va recevoir les données du formulaire et qui va les envoyer vers la base de données.
4. Enrichissement possible du formulaire avec Javascript/JQuery

Télécharger et installer le serveur WAPP pour Windows, ou MAPP pour Mac.

Pour linux:

sudo apt-get install postgresql-9.5 apache2 php5 phppgadmin php5-pgsql

en modifiant selon la version de postgresql disponible dans votre distribution.

Composantes nécessaires pour un fichier HTML

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">
<html>
<head>
<meta charset="UTF-8">
<title>Titre du document</title>
</head>
<body>
Le contenu du fichier est ici!
</body>
</html>

Création d'un formulaire

Le formulaire de base

<form method="POST" action="fichierphp.php">
<input type="text">
</form>

Liste des types d'entrée html

Entrée de texte

<input type="text" name="nom_texte">

Menu déroulant

<select name="nom_select">
<option value="1">Option 1</option>
<option value="2">Option 2</option>
<option value="3">Option 3</option>
</select>

Check box

<input type="checkbox" name="poutine" value="oui"> J'aime la poutine
<input type="checkbox" name="sushis" value="oui"> J'aime les sushis
<input type="checkbox" name="poutine_sushis" value="oui"> J'aime la poutine aux sushis

Bouttons radio

Je suis un(e)
<input type="radio" name="nom_radio" value="h" > Homme
<input type="radio" name="nom_radio" value="f"> Femme

Zone de texte

<textarea name="zone_texte">
</textarea>

Guide de référence CSS

On peut donner un “id” à n'importe quel élément html. Important: il ne peut y avoir qu'un id unique par élément

<input type="text" name="nom_texte" id="nom_texte">

Et on peut y faire référence dans l'entête de style css du fichier html avec le dièse ”#“

<head>
<style>
#nom_texte {
 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>

On peut aussi donner une “class” à n'importe quel élément html. Cette classe peut se répeter sur plusieurs éléments

<input type="text" name="nom_texte1" class="classe_texte">
<input type="text" name="nom_texte2" class="classe_texte">
<input type="text" name="nom_texte3" class="classe_texte">

Et on peut y faire référence dans l'entête de style css du fichier html avec le point ”.“

<head>
<style>
.classe_texte {
 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>

Lien vers phppgadmin

Tous les fichiers doivent commencer par:

<?php
Le code PHP est ici. 
?>

Commentaires

<?php
// Commentaire sur une ligne
/*
Section de commentaires
*/
?>

<?php
// Création de variables
$myvar=5;
$myvar='ceci est du texte';
 
// Création d'un array
$myarray=array('valeur1','valeur2','valeur3');
 
echo $myarray[0];
//valeur1
 
$myarray=[];
$myarray[0]='valeur1';
$myarray[1]='valeur2';
 
// Création d'un array "key-value"
$myarray=array(
'cle1'=>'valeur1',
'cle2'=>'valeur2',
'cle3'=>'valeur3',
);
 
echo $myarray['cle1'];
//valeur1
?>

Boucles et if/else

<?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){
//Faire quelque chose avec $my
?>

Fonctions PostgreSQL dans PHP

Ce fichier PHP est celui qui est spécifié dans le champ “ACTION” du formulaire html.

<?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");
 
// L'array $_POST contient toutes les données du formulaire. Chaque 'clé' de cet array correspond au nom donné à chacun des type d'entrées html. 
$element1=$_POST['nom_element1'];
$element2=$_POST['nom_element2'];
$element1=pg_escape_string($element1);
$element2=pg_escape_string($element2);
 
// Insertion dans la base de données
$query = "INSERT INTO ma_table (colonne1,colonne2) VALUES ($element1,$element2)";
$result = pg_query($query);
// Fermer la connexion
pg_close($dbconn);
echo "Formulaire entré dans la base de données avec succès!";
?>

Accéder à différents types d'éléments

//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']);

Retrouver des éléments dans la base de données

// 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);

Dans R: lors de la première utilisation seulement :

| installer le paquet RMySQL

install.packages('RPostgreSQL')

| Code R

library(RPostgreSQL)
drv <- dbDriver("PostgreSQL")
con <- dbConnect(drv,host="localhost", user="your_username", password="your_password", dbname="atelier");
lakes <- dbGetQuery(con,'SELECT * FROM lakes');

S'assurer de changer le nom d'utilisateur et le mot de passe au besoin.

Générer un graphique à partir des données extraites

lakesqc <- dbGetQuery(con,"SELECT * FROM lakes WHERE province='QUEBEC'")
hist(lakes$tmean_an)

library(dplyr)
src<-src_postgres(dbname="workshop",host="localhost", port="5432",user="your_username",password="your_password")
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=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