\n\nComment\nThis tutorial is significantly based on the Carpentries Databases and SQL lesson, which is licensed CC-BY 4.0.
\nAbigail Cabunoc and Sheldon McKay (eds): “Software Carpentry: Using Databases and SQL.” Version 2017.08, August 2017,\ngithub.com/swcarpentry/sql-novice-survey, https://doi.org/10.5281/zenodo.838776
\nAdaptations have been made to make this work better in a GTN/Galaxy environment.
\n
\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-1", "source": [ "# This preamble sets up the sql \"magic\" for jupyter. Use %%sql in your cells to write sql!\n", "!python3 -m pip install ipython-sql sqlalchemy\n", "!wget -c http://swcarpentry.github.io/sql-novice-survey/files/survey.db\n", "import sqlalchemy\n", "engine = sqlalchemy.create_engine(\"sqlite:///survey.db\")\n", "%load_ext sql\n", "%sql sqlite:///survey.db\n", "%config SqlMagic.displaycon=False" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-2", "source": "Agenda\nIn this tutorial, we will cover:
\n
A relational database\nis a way to store and manipulate information.\nDatabases are arranged as table.\nEach table has columns (also known as fields) that describe the data,\nand rows (also known as records) which contain the data.
\nWhen we are using a spreadsheet,\nwe put formulas into cells to calculate new values based on old ones.\nWhen we are using a database,\nwe send commands\n(usually called queries)\nto a database manager:\na program that manipulates the database for us.\nThe database manager does whatever lookups and calculations the query specifies,\nreturning the results in a tabular form\nthat we can then use as a starting point for further queries.
\nQueries are written in a language called Structured Query Language (SQL),\nSQL provides hundreds of different ways to analyze and recombine data.\nWe will only look at a handful of queries,\nbut that handful accounts for most of what scientists do.
\n\n\n\nMany database managers — Oracle,\nIBM DB2, PostgreSQL, MySQL, Microsoft Access, and SQLite — understand\nSQL but each stores data in a different way,\nso a database created with one cannot be used directly by another.\nHowever, every database manager\ncan import and export data in a variety of formats like .csv, SQL,\nso it is possible to move information from one to another.
\n
Before we get into using SQL to select the data, let’s take a look at the tables of the database we will use in our examples:
\nPerson: people who took readings.
\n| id | \npersonal | \nfamily | \n
|---|---|---|
| dyer | \nWilliam | \nDyer | \n
| pb | \nFrank | \nPabodie | \n
| lake | \nAnderson | \nLake | \n
| roe | \nValentina | \nRoerich | \n
| danforth | \nFrank | \nDanforth | \n
Site: locations where readings were taken.
\n| name | \nlat | \nlong | \n
|---|---|---|
| DR-1 | \n-49.85 | \n-128.57 | \n
| DR-3 | \n-47.15 | \n-126.72 | \n
| MSK-4 | \n-48.87 | \n-123.4 | \n
Visited: when readings were taken at specific sites.
\n| id | \nsite | \ndated | \n
|---|---|---|
| 619 | \nDR-1 | \n1927-02-08 | \n
| 622 | \nDR-1 | \n1927-02-10 | \n
| 734 | \nDR-3 | \n1930-01-07 | \n
| 735 | \nDR-3 | \n1930-01-12 | \n
| 751 | \nDR-3 | \n1930-02-26 | \n
| 752 | \nDR-3 | \nNone | \n
| 837 | \nMSK-4 | \n1932-01-14 | \n
| 844 | \nDR-1 | \n1932-03-22 | \n
Survey: the actual readings. The field quant is short for quantitative and indicates what is being measured. Values are rad, sal, and temp referring to ‘radiation’, ‘salinity’ and ‘temperature’, respectively.
| taken | \nperson | \nquant | \nreading | \n
|---|---|---|---|
| 619 | \ndyer | \nrad | \n9.82 | \n
| 619 | \ndyer | \nsal | \n0.13 | \n
| 622 | \ndyer | \nrad | \n7.8 | \n
| 622 | \ndyer | \nsal | \n0.09 | \n
| 734 | \npb | \nrad | \n8.41 | \n
| 734 | \nlake | \nsal | \n0.05 | \n
| 734 | \npb | \ntemp | \n-21.5 | \n
| 735 | \npb | \nrad | \n7.22 | \n
| 735 | \nNone | \nsal | \n0.06 | \n
| 735 | \nNone | \ntemp | \n-26.0 | \n
| 751 | \npb | \nrad | \n4.35 | \n
| 751 | \npb | \ntemp | \n-18.5 | \n
| 751 | \nlake | \nsal | \n0.1 | \n
| 752 | \nlake | \nrad | \n2.19 | \n
| 752 | \nlake | \nsal | \n0.09 | \n
| 752 | \nlake | \ntemp | \n-16.0 | \n
| 752 | \nroe | \nsal | \n41.6 | \n
| 837 | \nlake | \nrad | \n1.46 | \n
| 837 | \nlake | \nsal | \n0.21 | \n
| 837 | \nroe | \nsal | \n22.5 | \n
| 844 | \nroe | \nrad | \n11.25 | \n
Notice that three entries — one in the Visited table,\nand two in the Survey table — don’t contain any actual\ndata, but instead have a special None entry:\nwe’ll return to these missing values.
For now,\nlet’s write an SQL query that displays scientists’ names.\nWe do this using the SQL command SELECT,\ngiving it the names of the columns we want and the table we want them from.\nOur query and its output look like this:
The semicolon at the end of the query\ntells the database manager that the query is complete and ready to run.\nWe have written our commands in upper case and the names for the table and columns\nin lower case,\nbut we don’t have to:\nas the example below shows,\nSQL is case insensitive.
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-5", "source": [ "%%sql\n", "SeLeCt FaMiLy, PeRsOnAl FrOm PeRsOn;" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-6", "source": "You can use SQL’s case insensitivity to your advantage. For instance,\nsome people choose to write SQL keywords (such as SELECT and FROM)\nin capital letters and field and table names in lower\ncase. This can make it easier to locate parts of an SQL statement. For\ninstance, you can scan the statement, quickly locate the prominent\nFROM keyword and know the table name follows. Whatever casing\nconvention you choose, please be consistent: complex queries are hard\nenough to read without the extra cognitive load of random\ncapitalization. One convention is to use UPPER CASE for SQL\nstatements, to distinguish them from tables and column names. This is\nthe convention that we will use for this lesson.
\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-7", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-8", "source": "Question: Is a personal and family name column a good design?\nIf you were tasked with designing a database to store this same data, is storing the name data in\nthis way the best way to do it? Why or why not?
\nCan you think of any names that would be difficult to enter in such a schema?
\n\n👁 View solution
\n\nNo, it is generally not. There are a lot of falsehoods that programmers believe about names.\nThe situation is much more complex as you can read in that article, but names vary wildly and\ngenerally placing constraints on how names are entered is only likely to frustrate you or your\nusers later on when they need to enter data into that database.
\nIn general you should consider using a single text field for the name and allowing users to\nspecify them as whatever they like (if it is a system with registration), or asking what they\nwish to be recorded (if you are doing this sort of data collection).
\nIf you are doing scientific research, you might know that names are generally very poor\nidentifiers of a single human, and in that case consider recording their\nORCiD which will help you reference that individual when you are\npublishing later.
\nThis is also a good time to consider what data you really need to collect. If you are working\nin the EU under GDPR, do you really need their full legal name? Is that necessary? Do you have a\nplan for ensuring that data is correct when publishing, if any part of their name has changed\nsince?
\n
While we are on the topic of SQL’s syntax, one aspect of SQL’s syntax\nthat can frustrate novices and experts alike is forgetting to finish a\ncommand with ; (semicolon). When you press enter for a command\nwithout adding the ; to the end, it can look something like this:
SELECT id FROM Person\n...>\n...>\nThis is SQL’s prompt, where it is waiting for additional commands or\nfor a ; to let SQL know to finish. This is easy to fix! Just type\n; and press enter!
Now, going back to our query,\nit’s important to understand that\nthe rows and columns in a database table aren’t actually stored in any particular order.\nThey will always be displayed in some order,\nbut we can control that in various ways.\nFor example,\nwe could swap the columns in the output by writing our query as:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-9", "source": [ "%%sql\n", "SELECT personal, family FROM Person;" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-10", "source": "or even repeat columns:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-11", "source": [ "%%sql\n", "SELECT id, id, id FROM Person;" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-12", "source": "As a shortcut,\nwe can select all of the columns in a table using *:
\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-15", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-16", "source": "Question: Selecting Site Names\nWrite a query that selects only the
\nnamecolumn from theSitetable.\n👁 View solution
\n\n\nSELECT name FROM Site;\n\n\n
\n\n \n\n\nname \n\n \nDR-1 \n\n \nDR-3 \n\n \n\nMSK-4 \n
\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-17", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-18", "source": "Question: Query Style\nMany people format queries as:
\n\nSELECT personal, family FROM person;\nor as:
\n\nselect Personal, Family from PERSON;\nWhat style do you find easiest to read, and why?
\n
In beginning our examination of the Antarctic data, we want to know:
\nTo determine which measurements were taken at each site,\nwe can examine the Survey table.\nData is often redundant,\nso queries often return redundant information.\nFor example,\nif we select the quantities that have been measured\nfrom the Survey table,\nwe get this:
This result makes it difficult to see all of the different types of\nquant in the Survey table. We can eliminate the redundant output to\nmake the result more readable by adding the DISTINCT keyword to our\nquery:
If we want to determine which visit (stored in the taken column)\nhave which quant measurement,\nwe can use the DISTINCT keyword on multiple columns.\nIf we select more than one column,\ndistinct sets of values are returned\n(in this case pairs, because we are selecting two columns):
Notice in both cases that duplicates are removed\neven if the rows they come from didn’t appear to be adjacent in the database table.
\nOur next task is to identify the scientists on the expedition by looking at the Person table.\nAs we mentioned earlier,\ndatabase records are not stored in any particular order.\nThis means that query results aren’t necessarily sorted,\nand even if they are,\nwe often want to sort them in a different way,\ne.g., by their identifier instead of by their personal name.\nWe can do this in SQL by adding an ORDER BY clause to our query:
| id | \npersonal | \nfamily | \n
|---|---|---|
| danfort | \nFrank | \nDanforth | \n
| dyer | \nWilliam | \nDyer | \n
| lake | \nAnderson | \nLake | \n
| pb | \nFrank | \nPabodie | \n
| roe | \nValentina | \nRoerich | \n
By default, when we use ORDER BY,\nresults are sorted in ascending order of the column we specify\n(i.e.,\nfrom least to greatest).
We can sort in the opposite order using DESC (for “descending”):
\n\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-27", "source": [ "%%sql\n", "SELECT * FROM person ORDER BY id DESC;" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-28", "source": "While it may look that the records are consistent every time we ask for them in this lesson, that is because no one has changed or modified any of the data so far. Remember to use
\nORDER BYif you want the rows returned to have any sort of consistent or predictable order.
(And if we want to make it clear that we’re sorting in ascending order,\nwe can use ASC instead of DESC.)
In order to look at which scientist measured quantities during each visit,\nwe can look again at the Survey table.\nWe can also sort on several fields at once.\nFor example,\nthis query sorts results first in ascending order by taken,\nand then in descending order by person\nwithin each group of equal taken values:
This query gives us a good idea of which scientist was involved in which visit,\nand what measurements they performed during the visit.
\nLooking at the table, it seems like some scientists specialized in\ncertain kinds of measurements. We can examine which scientists\nperformed which measurements by selecting the appropriate columns and\nremoving duplicates.
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-31", "source": [ "%%sql\n", "SELECT DISTINCT quant, person FROM Survey ORDER BY quant ASC;" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-32", "source": "\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-33", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-34", "source": "Question: Finding Distinct Dates\nWrite a query that selects distinct dates from the
\nVisitedtable.\n👁 View solution
\n\n\nSELECT DISTINCT dated FROM Visited;\n\n\n
\n\n \n\n\ndated \n\n \n1927-02-08 \n\n \n1927-02-10 \n\n \n1930-01-07 \n\n \n1930-01-12 \n\n \n1930-02-26 \n\n \n\n \n \n1932-01-14 \n\n \n\n1932-03-22 \n
\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-35", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-36", "source": "Question: Displaying Full Names\nWrite a query that displays the full names of the scientists in the
\nPersontable,\nordered by family name.\n👁 View solution
\n\n\nSELECT personal, family FROM Person ORDER BY family ASC;\n\n\n
\n\n \n\n\npersonal \nfamily \n\n \nFrank \nDanforth \n\n \nWilliam \nDyer \n\n \nAnderson \nLake \n\n \nFrank \nPabodie \n\n \n\nValentina \nRoerich \n
\n\n\nIf you are someone with a name which falls at the end of the alphabet, you’ve likely been\npenalised for this your entire life. Alphabetically sorting names should always be looked at\ncritically and through a lens to whether you are fairly reflecting everyone’s contributions,\nrather than just the default sort order.
\nThere are many options, either by some metric of contribution that everyone could agree on, or\nbetter, consider random sorting, like the GTN uses with our Hall of Fame\npage where we intentionally order randomly to tell contributors that no one persons\ncontributions matter more than anothers.
\n\n\nThe evidence provided in a variety of studies leaves no doubt that an\nalphabetical author ordering norm disadvantages researchers with\nlast names toward the end of the alphabet. There is furthermore con-\nvincing evidence that researchers are aware of this and that they\nreact strategically to such alphabetical discrimination, for example\nwith their choices of who to collaborate with. See Weber 2018 for more.
\n
\n\n\nWhen you are sorting things in SQL, you need to be aware of something called collation which can\naffect your results if you have values that are not the letters A-Z. Collating is the process of\nsorting values, and this affects many human languages when storing data in a database.
\nHere is a Dutch example. In the old days their alphabet contained a
\nÿwhich was later replaced\nwithij, a digraph of two characters squished together. This is commonly rendered asij\nhowever, two separate characters, due to the internet and widespread use of keyboards featuring\nmainly ascii characters. However, it is still the 25th letter of their alphabet.\nsqlite> create table nl(value text);\nsqlite> insert into nl values ('appel'), ('beer'), ('index'), ('ijs'), ('jammer'), ('winkel'), ('zon');\nsqlite> select * from nl order by value;\nappel\nbeer\nindex\nijs\njammer\nwinkel\nzon\nFind a dutch friend and ask them if this is the correct order for this list. Unfortunately it\nisn’t. Even though it is
\nijas two separate characters, it should be sorted as if it wasijor\nÿ, beforez. Like so: appel, beer, index, jammer, winkel, ijs, zonWhile there is not much you can do about it now (you’re just beginning!) it is something you\nshould be aware of. When you later need to know about this, you will find the term ‘collation’\nuseful, and you’ll find the procedure is different for every database engine.
\n
One of the most powerful features of a database is\nthe ability to filter data,\ni.e.,\nto select only those records that match certain criteria.\nFor example,\nsuppose we want to see when a particular site was visited.\nWe can select these records from the Visited table\nby using a WHERE clause in our query:
The database manager executes this query in two stages.\nFirst,\nit checks at each row in the Visited table\nto see which ones satisfy the WHERE.\nIt then uses the column names following the SELECT keyword\nto determine which columns to display.
This processing order means that\nwe can filter records using WHERE\nbased on values in columns that aren’t then displayed:
![SQL Filtering in Action]`(../../images/carpentries-sql/sql-filter.svg)
\nWe can use many other Boolean operators to filter our data.\nFor example,\nwe can ask for all information from the DR-1 site collected before 1930:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-41", "source": [ "%%sql\n", "SELECT * FROM Visited WHERE site = 'DR-1' AND dated < '1930-01-01';" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-42", "source": "\n\n\nMost database managers have a special data type for dates.\nIn fact, many have two:\none for dates,\nsuch as “May 31, 1971”,\nand one for durations,\nsuch as “31 days”.\nSQLite doesn’t:\ninstead,\nit stores dates as either text\n(in the ISO-8601 standard format “YYYY-MM-DD HH:MM:SS.SSSS”),\nreal numbers\n(Julian days, the number of days since November 24, 4714 BCE),\nor integers\n(Unix time, the number of seconds since midnight, January 1, 1970).\nIf this sounds complicated,\nit is,\nbut not nearly as complicated as figuring out\nhistorical dates in Sweden.
\n
\n\n\nStoring the year as the last two digits causes problems in databases, and is part of what caused\nY2K. Be sure to use the databases’ built in\nformat for storing dates, if it is available as that will generally avoid any major issues.
\nSimilarly there is a “Year 2038 problem”,\nas the timestamps mentioned above that count seconds since Jan 1, 1970 were running out of space\non 32-bit machines. Many systems have since migrated to work around this with 64-bit timestamps.
\n
If we want to find out what measurements were taken by either Lake or Roerich,\nwe can combine the tests on their names using OR:
Alternatively,\nwe can use IN to see if a value is in a specific set:
We can combine AND with OR,\nbut we need to be careful about which operator is executed first.\nIf we don’t use parentheses,\nwe get this:
which is salinity measurements by Lake,\nand any measurement by Roerich.\nWe probably want this instead:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-49", "source": [ "%%sql\n", "SELECT * FROM Survey WHERE quant = 'sal' AND (person = 'lake' OR person = 'roe');" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-50", "source": "We can also filter by partial matches. For example, if we want to\nknow something just about the site names beginning with “DR” we can\nuse the LIKE keyword. The percent symbol acts as a\nwildcard, matching any characters in that\nplace. It can be used at the beginning, middle, or end of the string\nSee this page on wildcards for more information:
Finally,\nwe can use DISTINCT with WHERE\nto give a second level of filtering:
But remember:\nDISTINCT is applied to the values displayed in the chosen columns,\nnot to the entire rows as they are being processed.
\n\n\nWhat we have just done is how most people “grow” their SQL queries.\nWe started with something simple that did part of what we wanted,\nthen added more clauses one by one,\ntesting their effects as we went.\nThis is a good strategy — in fact,\nfor complex queries it’s often the only strategy — but\nit depends on quick turnaround,\nand on us recognizing the right answer when we get it.
\nThe best way to achieve quick turnaround is often\nto put a subset of data in a temporary database\nand run our queries against that,\nor to fill a small database with synthesized records.\nFor example,\ninstead of trying our queries against an actual database of 20 million Australians,\nwe could run it against a sample of ten thousand,\nor write a small program to generate ten thousand random (but plausible) records\nand use that.
\n
\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-55", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-56", "source": "Question: Fix This Query\nSuppose we want to select all sites that lie within 48 degrees of the equator.\nOur first query is:
\n\nSELECT * FROM Site WHERE (lat > -48) OR (lat < 48);\nExplain why this is wrong,\nand rewrite the query so that it is correct.
\n\n👁 View solution
\n\nBecause we used
\nOR, a site on the South Pole for example will still meet\nthe second criteria and thus be included. Instead, we want to restrict this\nto sites that meet both criteria:\nSELECT * FROM Site WHERE (lat > -48) AND (lat < 48);\n
\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-57", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-58", "source": "Question: Finding Outliers\nNormalized salinity readings are supposed to be between 0.0 and 1.0.\nWrite a query that selects all records from
\nSurvey\nwith salinity values outside this range.\n👁 View solution
\n\n\nSELECT * FROM Survey WHERE quant = 'sal' AND ((reading > 1.0) OR (reading < 0.0));\n\n\n
\n\n \n\n\ntaken \nperson \nquant \nreading \n\n \n752 \nroe \nsal \n41.6 \n\n \n\n837 \nroe \nsal \n22.5 \n
\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-59", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-60", "source": "Question: Matching Patterns\nWhich of these expressions are true?
\n\n
\n- \n
'a' LIKE 'a'- \n
'a' LIKE '%a'- \n
'beta' LIKE '%a'- \n
'alpha' LIKE 'a%%'- \n
'alpha' LIKE 'a%p%'\n👁 View solution
\n\n\n
\n- True because these are the same character.
\n- True because the wildcard can match zero or more characters.
\n- True because the
\n%matchesbetand theamatches thea.- True because the first wildcard matches
\nlphaand the second wildcard matches zero characters (or vice versa).- True because the first wildcard matches
\nland the second wildcard matchesha.
\n\n\nBut what about if you don’t care about if it’s
\nALPHAoralphain the database, and you are\nusing a language that has a notion of case (unlike e.g. Chinese, Japenese)?Then you can use the
\nILIKEoperator for ‘case Insensitive LIKE’.\nfor example the following are true:\n
\n- \n
'a' ILIKE 'A'- \n
'AlPhA' ILIKE '%lpha'
After carefully re-reading the expedition logs,\nwe realize that the radiation measurements they report\nmay need to be corrected upward by 5%.\nRather than modifying the stored data,\nwe can do this calculation on the fly\nas part of our query:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-61", "source": [ "%%sql\n", "SELECT 1.05 * reading FROM Survey WHERE quant = 'rad';" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-62", "source": "When we run the query,\nthe expression 1.05 * reading is evaluated for each row.\nExpressions can use any of the fields,\nall of usual arithmetic operators,\nand a variety of common functions.\n(Exactly which ones depends on which database manager is being used.)\nFor example,\nwe can convert temperature readings from Fahrenheit to Celsius\nand round to two decimal places:
As you can see from this example, though, the string describing our\nnew field (generated from the equation) can become quite unwieldy. SQL\nallows us to rename our fields, any field for that matter, whether it\nwas calculated or one of the existing fields in our database, for\nsuccinctness and clarity. For example, we could write the previous\nquery as:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-65", "source": [ "%%sql\n", "SELECT taken, round(5 * (reading - 32) / 9, 2) as Celsius FROM Survey WHERE quant = 'temp';" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-66", "source": "We can also combine values from different fields,\nfor example by using the string concatenation operator ||:
But of course that can also be solved by simply having a single name field which avoids other\nissues.
\n\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-69", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-70", "source": "Question: Fixing Salinity Readings\nAfter further reading,\nwe realize that Valentina Roerich\nwas reporting salinity as percentages.\nWrite a query that returns all of her salinity measurements\nfrom the
\nSurveytable\nwith the values divided by 100.\n👁 View solution
\n\n\nSELECT taken, reading / 100 FROM Survey WHERE person = 'roe' AND quant = 'sal';\n\n\n
\n\n \n\n\ntaken \nreading / 100 \n\n \n752 \n0.416 \n\n \n\n837 \n0.225 \n
\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-71", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-72", "source": "Question: Unions\nThe
\nUNIONoperator combines the results of two queries:\nSELECT * FROM Person WHERE id = 'dyer' UNION SELECT * FROM Person WHERE id = 'roe';\n\n\n
\n\n \n\n\nid \npersonal \nfamily \n\n \ndyer \nWilliam \nDyer \n\n \n\nroe \nValentina \nRoerich \nThe
\nUNION ALLcommand is equivalent to theUNIONoperator,\nexcept thatUNION ALLwill select all values.\nThe difference is thatUNION ALLwill not eliminate duplicate rows.\nInstead,UNION ALLpulls all rows from the query\nspecifics and combines them into a table.\nTheUNIONcommand does aSELECT DISTINCTon the results set.\nIf all the records to be returned are unique from your union,\nuseUNION ALLinstead, it gives faster results since it skips theDISTINCTstep.\nFor this section, we shall use UNION.Use
\nUNIONto create a consolidated list of salinity measurements\nin which Valentina Roerich’s, and only Valentina’s,\nhave been corrected as described in the previous challenge.\nThe output should be something like:\n\n
\n\n \n\n\ntaken \nreading \n\n \n619 \n0.13 \n\n \n622 \n0.09 \n\n \n734 \n0.05 \n\n \n751 \n0.1 \n\n \n752 \n0.09 \n\n \n752 \n0.416 \n\n \n837 \n0.21 \n\n \n\n837 \n0.225 \n\n👁 View solution
\n\n\nSELECT taken, reading FROM Survey WHERE person != 'roe' AND quant = 'sal' UNION SELECT taken, reading / 100 FROM Survey WHERE person = 'roe' AND quant = 'sal' ORDER BY taken ASC;\n
\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-73", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-74", "source": "Question: Selecting Major Site Identifiers\nThe site identifiers in the
\nVisitedtable have two parts\nseparated by a ‘-‘:\nSELECT DISTINCT site FROM Visited;\n\n\n
\n\n \n\n\nsite \n\n \nDR-1 \n\n \nDR-3 \n\n \n\nMSK-4 \nSome major site identifiers (i.e. the letter codes) are two letters long and some are three.\nThe “in string” function
\ninstr(X, Y)\nreturns the 1-based index of the first occurrence of string Y in string X,\nor 0 if Y does not exist in X.\nThe substring functionsubstr(X, I, [L])\nreturns the substring of X starting at index I, with an optional length L.\nUse these two functions to produce a list of unique major site identifiers.\n(For this data,\nthe list should contain only “DR” and “MSK”).\n👁 View solution
\n\n\nSELECT DISTINCT substr(site, 1, instr(site, '-') - 1) AS MajorSite FROM Visited;\n
Real-world data is never complete — there are always holes.\nDatabases represent these holes using a special value called null.\nnull is not zero, False, or the empty string;\nit is a one-of-a-kind value that means “nothing here”.\nDealing with null requires a few special tricks\nand some careful thinking.
By default, the Python SQL interface does not display NULL values in its output, instead it shows None.
To start,\nlet’s have a look at the Visited table.\nThere are eight records,\nbut #752 doesn’t have a date — or rather,\nits date is null:
Null doesn’t behave like other values.\nIf we select the records that come before 1930:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-77", "source": [ "%%sql\n", "SELECT * FROM Visited WHERE dated < '1930-01-01';" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-78", "source": "we get two results,\nand if we select the ones that come during or after 1930:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-79", "source": [ "%%sql\n", "SELECT * FROM Visited WHERE dated >= '1930-01-01';" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-80", "source": "we get five,\nbut record #752 isn’t in either set of results.\nThe reason is that\nnull<'1930-01-01'\nis neither true nor false:\nnull means, “We don’t know,”\nand if we don’t know the value on the left side of a comparison,\nwe don’t know whether the comparison is true or false.\nSince databases represent “don’t know” as null,\nthe value of null<'1930-01-01'\nis actually null.\nnull>='1930-01-01' is also null\nbecause we can’t answer to that question either.\nAnd since the only records kept by a WHERE\nare those for which the test is true,\nrecord #752 isn’t included in either set of results.
Comparisons aren’t the only operations that behave this way with nulls.\n1+null is null,\n5*null is null,\nlog(null) is null,\nand so on.\nIn particular,\ncomparing things to null with = and != produces null:
produces no output, and neither does:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-83", "source": [ "%%sql\n", "SELECT * FROM Visited WHERE dated != NULL;" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-84", "source": "To check whether a value is null or not,\nwe must use a special test IS NULL:
or its inverse IS NOT NULL:
Null values can cause headaches wherever they appear.\nFor example,\nsuppose we want to find all the salinity measurements\nthat weren’t taken by Lake.\nIt’s natural to write the query like this:
\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-89", "source": [ "%%sql\n", "SELECT * FROM Survey WHERE quant = 'sal' AND person != 'lake';" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-90", "source": "but this query filters omits the records\nwhere we don’t know who took the measurement.\nOnce again,\nthe reason is that when person is null,\nthe != comparison produces null,\nso the record isn’t kept in our results.\nIf we want to keep these records\nwe need to add an explicit check:
We still have to decide whether this is the right thing to do or not.\nIf we want to be absolutely sure that\nwe aren’t including any measurements by Lake in our results,\nwe need to exclude all the records for which we don’t know who did the work.
\nIn contrast to arithmetic or Boolean operators, aggregation functions\nthat combine multiple values, such as min, max or avg, ignore\nnull values. In the majority of cases, this is a desirable output:\nfor example, unknown values are thus not affecting our data when we\nare averaging it. Aggregation functions will be addressed in more\ndetail in the next section.
\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-93", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-94", "source": "Question: Sorting by Known Date\nWrite a query that sorts the records in
\nVisitedby date,\nomitting entries for which the date is not known\n(i.e., is null).\n👁 View solution
\n\n\nSELECT * FROM Visited WHERE dated IS NOT NULL ORDER BY dated ASC;\n\n\n
\n\n \n\n\nid \nsite \ndated \n\n \n619 \nDR-1 \n1927-02-08 \n\n \n622 \nDR-1 \n1927-02-10 \n\n \n734 \nDR-3 \n1930-01-07 \n\n \n735 \nDR-3 \n1930-01-12 \n\n \n751 \nDR-3 \n1930-02-26 \n\n \n837 \nMSK-4 \n1932-01-14 \n\n \n\n844 \nDR-1 \n1932-03-22 \n
\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-95", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-96", "source": "Question: NULL in a Set\nWhat do you expect the following query to produce?
\n\nSELECT * FROM Visited WHERE dated IN ('1927-02-08', NULL);\nWhat does it actually produce?
\n\n👁 View solution
\n\nYou might expect the above query to return rows where dated is either ‘1927-02-08’ or NULL.\nInstead it only returns rows where dated is ‘1927-02-08’, the same as you would get from this\nsimpler query:
\n\nSELECT * FROM Visited WHERE dated IN ('1927-02-08');\nThe reason is that the
\nINoperator works with a set of values, but NULL is by definition\nnot a value and is therefore simply ignored.If we wanted to actually include NULL, we would have to rewrite the query to use the IS NULL condition:
\n\nSELECT * FROM Visited WHERE dated = '1927-02-08' OR dated IS NULL;\n
\n\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "id": "cell-97", "source": [ "%%sql\n", "-- Try solutions here!" ], "cell_type": "code", "execution_count": null, "outputs": [ ], "metadata": { "attributes": { "classes": [ "sql" ], "id": "" } } }, { "id": "cell-98", "source": "\n", "cell_type": "markdown", "metadata": { "editable": false, "collapsed": false } }, { "cell_type": "markdown", "id": "final-ending-cell", "metadata": { "editable": false, "collapsed": false }, "source": [ "# Key Points\n\n", "- A relational database stores information in tables, each of which has a fixed set of columns and a variable number of records.\n", "- A database manager is a program that manipulates information stored in a database.\n", "- We write queries in a specialized language called SQL to extract information from databases.\n", "- Use SELECT... FROM... to get values from a database table.\n", "- SQL is case-insensitive (but data is case-sensitive).\n", "- The records in a database table are not intrinsically ordered: if we want to display them in some order, we must specify that explicitly with ORDER BY.\n", "- The values in a database are not guaranteed to be unique: if we want to eliminate duplicates, we must specify that explicitly as well using DISTINCT.\n", "- Use WHERE to specify conditions that records must meet in order to be included in a query's results.\n", "- Use AND, OR, and NOT to combine tests.\n", "- Filtering is done on whole records, so conditions can use fields that are not actually displayed.\n", "- Write queries incrementally.\n", "- Queries can do the usual arithmetic operations on values.\n", "- Use UNION to combine the results of two or more queries.\n", "- Databases use a special value called NULL to represent missing information.\n", "- Almost all operations on NULL produce NULL.\n", "- Queries can test for NULLs using IS NULL and IS NOT NULL.\n", "\n# Congratulations on successfully completing this tutorial!\n\n", "Please [fill out the feedback on the GTN website](https://training.galaxyproject.org/training-material/topics/data-science/tutorials/sql-basic/tutorial.html#feedback) and check there for further resources!\n" ] } ] }Question: Pros and Cons of Sentinels\nSome database designers prefer to use\na sentinel value\nto mark missing data rather than
\nnull.\nFor example,\nthey will use the date “0000-00-00” to mark a missing date,\nor -1.0 to mark a missing salinity or radiation reading\n(since actual readings cannot be negative).\nWhat does this simplify?\nWhat burdens or risks does it introduce?