GNU Sqltutora web based interactive tutorial of Structured Query Language (SQL)
Aleš Cepek and Jan Pytel
Faculty of Civil Engineering, Czech Technical University in PragueDepartment of Mapping and Cartography
26–28 February, 2009
The FIG Commission 2 and the Austrian Society for Surveying and Geoinformation (OVG) Workshop Navigating the Future of Surveying Educationat Federal Office of Metrology and Surveying (BEV), Vienna, Austria
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 1 / 24
Contents
1 Introduction
2 Examples
3 Evaluation
4 Strategy for random question selection
5 TODO List
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 2 / 24
Introduction
SQLtutor is an interactive online web based tool for teaching and examiningstudents’ knowledge of SQL
introduction of new study branch geoinformaticsintroduction to relational databases and SQL languagebackground for spatial queries in GIS and other subjectsPostgreSQL selected as a main RDBMS in our curricula
Structured Query Language (SQL)relatively simple language (at least on the basic level)no need of previous background and knowledge of DBideal candidate for computer based learning
SQLtutor — an web based interactive tutorialproject started in August 2007presented on a faculty seminarfirst experimental course with master degree. studentsofficial status of GNU software in January 2009
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 3 / 24
GNU Sqltutor
home page http://www.gnu.org/software/sqltutorCVS repository http://savannah.gnu.org/projects/sqltutorSqltutor online http://sqltutor.fsv.cvut.cz
License GNU General Public License v3 or laterSqltutor manual available online in several formats (HTML, ASCII, Info, dvi,
PostScript, PDF)
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 4 / 24
Opening Dialog Page
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 5 / 24
Online Manual
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 6 / 24
Tutorial Datasets
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 7 / 24
Running a Tutorial
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 8 / 24
Table Data (part 1)
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 9 / 24
Table Data (part 2)
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 10 / 24
A First Try
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 11 / 24
A Better Guess
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 12 / 24
A Correct Answer
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 13 / 24
Setting Help in Opening Page
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 14 / 24
Help Button
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 15 / 24
More Examples
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 16 / 24
More Examples
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 17 / 24
More Examples
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 18 / 24
Closing the Test
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 19 / 24
Final Score
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 20 / 24
Evaluation
evaluation =
⌊( ∑correct ansvers
points
)× number of correct answers
number of all questions asked
⌋
evaluation grade90 A75 B60 C45 D30 E
less than 30 F
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 21 / 24
Distribution of Questions by Points
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 22 / 24
TO-DO list
some formal adjustments of source codes to be fully compliant with GNUcoding standardspossible redesign of the Sqltutor database schema to have better supportfor multilingual tutorialbuild a large and reliable English tutorial SQL with datasets that can begenerally understood even outside the English speaking countriesanalyze the new algorithm for random selection of questions, implementa better strategy if needed,possible new parameters for final evaluationa tutorial for PostGIS spatial enhancement of PostgreSQL database
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 23 / 24
Thank you for your attention
Aleš Cepek and Jan Pytel ( Faculty of Civil Engineering, Czech Technical University in Prague Department of Mapping and Cartography )GNU Sqltutor 26–28 February, 2009 24 / 24