<jupyter_start><jupyter_text>Introduction to Databases*This lesson is taken and/or adapted from the Software Carpentry Databases and SQL Workshop http://swcarpentry.github.io/sql-novice-survey/   [licence https://creativecommons.org/licenses/by/4.0/]*Databases are collections of data organized in way that allows for efficient search and retrieval.  They are used extensively in business applications (e.g. for inventory, customer information), and also in scientific research.Storing data in simple text files works well, but you will need to create tools to analyze the data.Spreadsheets are good for small and simple tasks.  (Amazon.com does not store all of its customer data in a spreadsheet!)Databases provide built-in search and analysis tools.  Depending on the database manager (programs like  Oracle, IBM DB2, PostgreSQL, MySQL, Microsoft Access, and SQLite), database files have different formats, but most programs can export databases to different formats.  We will be using SQLight, which is included in the software package for the course.  Like most database managers, it uses the structured query language (SQL) and its own particular commands to interact with the database.Databases are arranged as tables. Each table has columns (also known as fields) that describe the data, and rows (also known as records) which contain the data. The Data SetThe .csv files in the **data** directory contain measurements taken by a team of researchers during the 1920's and 1930's in the *oceanic pole of inaccessibility*, the point in the oceans furthest from land.* person.csv: Lists the researchers.  Columns are person identifier, first name and family name.* site.csv: Lists locations where data were collected.  Columns are site name, latitude and longitude.* visited.csv: Lists what site was visited when. Columns are experiment id number, site code name and date.* survey.csd: Lists readings taken.  Columns are experiment id number, person identifier, type of measurement taken (salinity, temperature and radiation level) and recorded value.Take a moment to browse through the data files through the command line, using *cat*, *less* etc.  You may notice that some data is missing. ExercisesCan you use *grep* to find the missing data?  Hint:  ",,"  "^,"  ",$"<jupyter_code>pwd
cd data
cat person.csv
cat site.csv
cat visited.csv
cat survey.csv<jupyter_output>619,dyer,rad,9.82
619,dyer,sal,0.13
622,dyer,rad,7.8
622,dyer,sal,0.09
734,pb,rad,8.41
734,lake,sal,0.05
734,pb,temp,-21.5
735,pb,rad,7.22
735,,sal,0.06
735,,temp,-26.0
751,pb,rad,4.35
751,pb,temp,-18.5
751,lake,sal,0.1
752,lake,rad,2.19
752,lake,sal,0.09
752,lake,temp,-16.0
752,roe,sal,41.6
837,lake,rad,1.46
837,lake,sal,0.21
837,roe,sal,22.5
844,roe,rad,11.25<jupyter_text>Information about who collected what data, and where, and what the values of the measurements are, is spread out over the four files.  To answer questions like "What is the average salinity reading in 1930?", it is useful to have all the data entered into a database.  This can be accomplished with the following.<jupyter_code>cd ..
ls
!sqlite3 survey.db ".read bin/create-db.sql"
ls<jupyter_output>Databases.ipynb  [34mbin[m[m/             [34mdata[m[m/            survey.db<jupyter_text>We now have the data stored in SQLite database called **survey.db.**  The commands used to create the database are contained in the file **create-db.sql** in the **bin** directory.<jupyter_code>cat bin/create-db.sql<jupyter_output>-- Create database to be used for learners.
-- The data for the database are available as CSV files.
-- For more information, see https://www.sqlite.org/cli.html#csv

-- Generate tables.
create table Person (id text, personal text, family text);
create table Site (name text, lat real, long real);
create table Visited (id text, site text, dated text);
create table Survey (taken integer, person text, quant text, reading real);

-- Import data.
.mode csv
.import data/person.csv Person
.import data/site.csv Site
.import data/survey.csv Survey
.import data/visited.csv Visited

-- Convert empty strings to NULLs.
UPDATE Visited SET dated = null WHERE dated = '';
UPDATE Survey SET person = null WHERE person = '';<jupyter_text>From the file, we can infer that SQLite has imported our data files into *tables* called *Person*, *Site*, *Survey* and *Visited*, names chosen by the programmer to reflect the names of the data files and their contents.  The last part of the script has ensured that missing data in the csv format is represented as *null*.The columns have been given descriptive headers and an indication of the type of data the entries hold. For example, the first column of *Site* is called "name" and is interpreted as text, while the second column is called "lat" (for latitude) and contains real numbers.  Selecting DataWe would like to query our database. On the command line enter```$sqlite3 survey.db```You should see```SQLite version 3.13.0 2016-05-18 10:57:30Enter ".help" for usage hints.Cannot read termcap database;using dumb terminal settings.sqlite>```which means we have an entered an interactive SQLite shell that is ready to receive commands from us..quit or .exit or Ctrl-D should quit the program and return us to the bash prompt..help will give us a list of SQLite commands.tables gives a list of data tables.  Try it.```sqlite> .tablesPerson   Site     Survey   Visited```**NOTE:** SQLite commands are specific to SQLite and begin with a "." (period). Regular SQL commands do not require a preceeding period, but do need to be terminated with a ";" (semicolon). We will now use an SQL command to view the *personal* and *family* columns of the *Person* table(What happens if you forget the ; at the end?)```sqlite> SELECt perSonal, FamilY FrOM PersoN;William|DyerFrank|PabodieAnderson|LakeValentina|RoerichFrank|Danforth```This illustrates that SQL is not case sensitive.  We can choose a convention for when capitals are used, e.g.```SELECT - capitals for SQL commandspersonal - lowercase for headersPerson - first letter capitalized for tables```To make the output look nicer and to include head labels, enter```.mode column.header on```and try```sqlite> SELECT personal, family FROM Person;personal    family    ----------  ----------William     Dyer      Frank       Pabodie   Anderson    Lake      Valentina   Roerich   Frank       Danforth```We can view all of the data with```sqlite> SELECT * FROM Person;id          personal    family    ----------  ----------  ----------dyer        William     Dyer      pb          Frank       Pabodie   lake        Anderson    Lake      roe         Valentina   Roerich   danforth    Frank       Danforth ```The number of times a column is listed is up to us.```sqlite> SELECT id, family, id, personal, id FROM Person;id          family      id          personal    id        ----------  ----------  ----------  ----------  ----------dyer        Dyer        dyer        William     dyer      pb          Pabodie     pb          Frank       pb        lake        Lake        lake        Anderson    lake      roe         Roerich     roe         Valentina   roe       danforth    Danforth    danforth    Frank       danforth``` Key points*    A relational database stores information in tables, each of which has a fixed set of columns and a variable number of records.*    A database manager is a program that manipulates information stored in a database.*    We write queries in a specialized language called SQL to extract information from databases.*    Use SELECT… FROM… to get values from a database table.*    SQL is case-insensitive (but data is case-sensitive). Exercises* Write a query that selects only site names from the Site table.* Confirm what the headings are from the *Survey* table and list the columns corresponding to the type of measurement and the corresponding value reported.  Sorting and Removing DuplicatesWe wish to see all the different quanties measured.```sqlite> SELECT quant FROM Survey;        quant     ----------rad       sal       rad       sal       rad       sal       temp      rad       sal       temp      rad       temp      sal       rad       sal       temp      sal       rad       sal       sal       rad ```That's a little bit confusing.  Let's see only unique entries.```sqlite> SELECT DISTINCT quant FROM Survey;quant     ----------rad       sal       temp```Ok, so indeed we only have radiation, salinity and temperature.We can also order the output.  For example, let's look at the *Person* table, sorting by *id*.  By default, if ORDER is used, sorting is ascending.```sqlite> SELECT * FROM Person ORDER BY id; id          personal    family    ----------  ----------  ----------danforth    Frank       Danforth  dyer        William     Dyer      lake        Anderson    Lake      pb          Frank       Pabodie   roe         Valentina   Roerich ```We can choose to display in descending order as well.```sqlite> SELECT * FROM Person ORDER BY id DESC;id          personal    family    ----------  ----------  ----------roe         Valentina   Roerich   pb          Frank       Pabodie   lake        Anderson    Lake      dyer        William     Dyer      danforth    Frank       Danforth ```We can sort by multiple columns, e.g., ascending first according to the *taken* column, and then (when the *taken* value is the same), descedning by the *person* column.```sqlite> SELECT taken, person, quant FROM Survey ORDER BY taken ASC, person DESC;taken       person      quant     ----------  ----------  ----------619         dyer        rad       619         dyer        sal       622         dyer        rad       622         dyer        sal       734         pb          rad       734         pb          temp      734         lake        sal       735         pb          rad       735                     sal       735                     temp      751         pb          rad       751         pb          temp      751         lake        sal       752         roe         sal       752         lake        rad       752         lake        sal       752         lake        temp      837         roe         sal       837         lake        rad       837         lake        sal       844         roe         rad ```Looking at the data, it is not immediately obvious which scientists were involved in the different kinds of measurement.  Let's select distinct measurement-scientist pairs, and sort first by measurement type and then by scientist.```sqlite> SELECT DISTINCT quant, person FROM Survey ORDER BY quant ASC, person ASC;quant       person    ----------  ----------rad         dyer      rad         lake      rad         pb        rad         roe       sal                   sal         dyer      sal         lake      sal         roe       temp                  temp        lake      temp        pb ``` Key points* 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.* 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. Exercises* Display all actual measurements taken (in the *Survey* table).  Order first by the quantity measured, and then by value recorded.* Write a query that selects distinct dates from the Visited table.* Write a query that displays the full names of the scientists in the Person table, ordered by family name.  FilteringOne feature of using databases is the ability to *filter* data.  Let's display the entries in the *Visited* table pertaining to the "DR-1" site.  We do this using the WHERE clause in our query. ```sqlite> SELECT * FROM Visited WHERE site='DR-1';id          site        dated     ----------  ----------  ----------619         DR-1        1927-02-08622         DR-1        1927-02-10844         DR-1        1932-03-22```SQLite first determines which rows match the WHERE clause (site='DR-1'), and then SELECTS the appropriate columns to display.  Thus, we do not need to display the column on which we are filtering, e.g.```sqlite> SELECT id FROM Visited WHERE site='DR-1';id        ----------619       622       844 ```As in an *if* statement, we can combine boolean operators to refine the filter.  Here, we select all the times site DR-1 was visited before 1932.  ```sqlite> SELECT * FROM Visited WHERE site='DR-1' AND dated<'1932-01-01'; id          site        dated     ----------  ----------  ----------619         DR-1        1927-02-08622         DR-1        1927-02-10```**Note**: Database managers in general have special data types to represent dates.  This is not the case for SQLight, for which dates can be expressed as text (in the ISO-8601 standard format “YYYY-MM-DD HH:MM:SS.SSSS”), real numbers (the number of days since November 24, 4714 BCE), or integers (the number of seconds since midnight, January 1, 1970).Let's say we want to find measurements take either by <NAME> or <NAME>.```sqlite> SELECT * FROM Survey WHERE person='lake' OR person='roe';taken       person      quant       reading   ----------  ----------  ----------  ----------734         lake        sal         0.05      751         lake        sal         0.1       752         lake        rad         2.19      752         lake        sal         0.09      752         lake        temp        -16.0     752         roe         sal         41.6      837         lake        rad         1.46      837         lake        sal         0.21      837         roe         sal         22.5      844         roe         rad         11.25 ```Rather than using the OR, we can accomplish the same using IN.```sqlite> SELECT * FROM Survey WHERE person IN ('lake', 'roe');taken       person      quant       reading   ----------  ----------  ----------  ----------734         lake        sal         0.05      751         lake        sal         0.1       752         lake        rad         2.19      752         lake        sal         0.09      752         lake        temp        -16.0     752         roe         sal         41.6      837         lake        rad         1.46      837         lake        sal         0.21      837         roe         sal         22.5      844         roe         rad         11.25 ```We can combine AND and OR, but be mindful of order of operations.  The following means "salinity measured by Lake", or any measurement made by Roerich.```sqlite> SELECT * FROM Survey WHERE quant='sal' AND person='lake' OR person='roe';taken       person      quant       reading   ----------  ----------  ----------  ----------734         lake        sal         0.05      751         lake        sal         0.1       752         lake        sal         0.09      752         roe         sal         41.6      837         lake        sal         0.21      837         roe         sal         22.5      844         roe         rad         11.25 ```Compared to "salinity measured by either Lake or Roerich"```sqlite> SELECT * FROM Survey WHERE quant='sal' AND (person='lake' OR person='roe');taken       person      quant       reading   ----------  ----------  ----------  ----------734         lake        sal         0.05      751         lake        sal         0.1       752         lake        sal         0.09      752         roe         sal         41.6      837         lake        sal         0.21      837         roe         sal         22.5  ```Partial matching through wildcards can be done using LIKE. The following looks for any *site* that begins with DR.  The % character acts like * in the bash shell.```sqlite> SELECT * FROM Visited WHERE site LIKE 'DR%';id          site        dated     ----------  ----------  ----------619         DR-1        1927-02-08622         DR-1        1927-02-10734         DR-3        1930-01-07735         DR-3        1930-01-12751         DR-3        1930-02-26752         DR-3                  844         DR-1        1932-03-22```We can also combine DISTINCT with WHERE.```sqlite> SELECT DISTINCT person, quant FROM Survey WHERE person='lake' OR person='roe';person      quant     ----------  ----------lake        sal       lake        rad       lake        temp      roe         sal       roe         rad ```**Remark**: We can see already how a query might grow to become difficult for a person to easily decipher.  This process of starting with simple queries and growing them into complex ones, making sure that the correct result occurs at each step, is **the** way to obtain working, complex queries.  For very large databases (millions of entries), this kind of development would take a long time, so it is best to work on a small subset of data. Key points*   Use WHERE to specify conditions that records must meet in order to be included in a query’s results.*  Use AND, OR, and NOT to combine tests.*   Filtering is done on whole records, so conditions can use fields that are not actually displayed.*  Write queries incrementally. Exercises* We want to find all sites within 48 degrees of the equator (equator has latitude zero).  How would you fix the following incorrect query?``` SELECT * FROM Site WHERE (lat > -48) OR (lat < 48);```* Find all records in *Survey* with salinity lying outside the range of 0.0 to 1.0.  Calculating New ValuesLet's say that after examining the data, you conclude that all the radiation levels need to be corrected up by 5%.  Rather than editing the data itself, calculations can be done "on the fly".  We can multiply the *reading* record from each row by 1.05 with the following query. ```sqlite> SELECT 1.05 * reading FROM Survey WHERE quant='rad';1.05 * reading--------------10.311        8.19          8.8305        7.581         4.5675        2.2995        1.533         11.8125 ```The usual arithmetical operations and some functions are available.  Functionality depends on database manager.  The following converts reported temperatures from Fahrenheit to Celsius, rounding to two decimal places.```sqlite> SELECT taken, round(5*(reading-32)/9, 2) FROM Survey WHERE quant='temp';         taken       round(5*(reading-32)/9, 2)----------  --------------------------734         -29.72                    735         -32.22                    751         -28.06                    752         -26.67  ```The heading for the calculated column is a little clumsy.  This can be fixed with AS.```sqlite> SELECT taken, round(5*(reading-32)/9, 2) AS Celsius FROM Survey WHERE quant='temp';taken       Celsius   ----------  ----------734         -29.72    735         -32.22    751         -28.06    752         -26.67  ```Arithmetic using different records in the same rowcan be accomplished as in the following.  (Granted, this particular operation makes little sense.```sqlite> SELECT taken, reading, taken*reading FROM Survey WHERE quant='rad';taken       reading     taken*reading----------  ----------  -------------619         9.82        6078.58      622         7.8         4851.6       734         8.41        6172.94      735         7.22        5306.7       751         4.35        3266.85      752         2.19        1646.88      837         1.46        1222.02      844         11.25       9495.0```Strings (text) from different columns can also be "added" using the concatenation operator ||.```sqlite> SELECT personal, family FROM Person;...sqlite> SELECT personal || family FROM Person;...sqlite> SELECT personal || " " || family  FROM Person;personal || " " || family-------------------------<NAME>             <NAME>            <NAME>            <NAME>        <NAME> ```Two useful functions for manipulating strings are * the "in-string" function instr(X, Y), which returns the 1-based index of the first occurrence of string Y in string X, or 0 if Y does not exist in X. * the substring function substr(X, I, [L]), which returns the substring of X starting at index I, with an optional length L.We will use these functions to change the names of locations DR-1 and DR-3 to reflect their longitude.```sqlite> SELECT name, long  FROM Site WHERE name LIKE "DR%";...SELECT name, instr(name,'-'), long  FROM Site WHERE name LIKE "DR%";...SELECT name, substr(name,1,instr(name,'-')), long  FROM Site WHERE name LIKE "DR%";...  as above with substr(name,1,instr(name,'-')-1)...  substr(name,1,instr(name,'-')-1)|| long...  substr(name,1,instr(name,'-')-1)||CAST(long AS INTEGER)name        substr(name,1,instr(name,'-')-1)||CAST(long AS INTEGER)  long      ----------  -------------------------------------------------------  ----------DR-1        DR-128                                                   -128.57   DR-3        DR-126                                                   -126.72 ```The UNION operator combines the results of two queries.```sqlite> SELECT * FROM Person WHERE id='dyer' UNION SELECT * FROM Person WHERE id='roe';id          personal    family    ----------  ----------  ----------dyer        William     Dyer      roe         Valentina   Roerich ``` Key points*  Queries can do the usual arithmetic operations on values.*  Use UNION to combine the results of two or more queries. Exercises* It turns out that Valentina Roerich was reporting salinity as percentages. Write a query that returns all of her salinity measurements from the Survey table with the values divided by 100.* Use UNION and the above result to write a query that returns all the salinity readings, but with Roerich's readings corrected as above. (Not equal is "!=") The output should be as follows.```taken       reading   ----------  ----------619         0.13      622         0.09      734         0.05      751         0.1       752         0.09      752         0.416     837         0.21      837         0.225  ```* Write a query on the *Site* table that prints out all the sites, but where DR-1 and DR-3 appear simply as DR.  Missing Data and NULLMissing data in databases are represented as *null*, which means "nothing here".  *null* is not an empty string, False or zero.Let's look at the *Sites* table.  We see that the date in one of the dates is missing.```sqlite> SELECT * FROM Visited;id          site        dated     ----------  ----------  ----------619         DR-1        1927-02-08622         DR-1        1927-02-10734         DR-3        1930-01-07735         DR-3        1930-01-12751         DR-3        1930-02-26752         DR-3                  837         MSK-4       1932-01-14844         DR-1        1932-03-22```If the *null* date had some sort of value, we might expect it to occur before 1930...```sqlite> SELECT * FROM Visited WHERE dated<'1930-01-01';id          site        dated     ----------  ----------  ----------619         DR-1        1927-02-08622         DR-1        1927-02-10```or parhaps after 1930?  No again.  In the union of the above two results, record  752 is missing.```sqlite> SELECT * FROM Visited WHERE dated>='1930-01-01';id          site        dated     ----------  ----------  ----------734         DR-3        1930-01-07735         DR-3        1930-01-12751         DR-3        1930-02-26837         MSK-4       1932-01-14844         DR-1        1932-03-22```If *null* is "don't know", then "don't know" = 1930.  *null*+1 = *null* also.  In fact, null=null is also "don't know" (*null*), so neither is the following will work either.```sqlite> SELECT * FROM Visited WHERE dated=NULL;sqlite> SELECT * FROM Visited WHERE dated!=NULL; ```So *null* is a special case that is handled with IS.```sqlite> SELECT * FROM Visited WHERE dated IS NULL;id          site        dated     ----------  ----------  ----------752         DR-3  ```and conversely,```sqlite> SELECT * FROM Visited WHERE dated IS NOT NULL;id          site        dated     ----------  ----------  ----------619         DR-1        1927-02-08622         DR-1        1927-02-10734         DR-3        1930-01-07735         DR-3        1930-01-12751         DR-3        1930-02-26837         MSK-4       1932-01-14844         DR-1        1932-03-22```Now suppose we want to find all salinities that were not recorded by Anderson Lake.  We might write a query like,```sqlite> SELECT * FROM Survey WHERE quant='sal' AND person!='lake';taken       person      quant       reading   ----------  ----------  ----------  ----------619         dyer        sal         0.13      622         dyer        sal         0.09      752         roe         sal         41.6      837         roe         sal         22.5  ```but this omits the record where the *person* is *null*, i.e. unknown.  To include this record, we might write```sqlite> SELECT * FROM Survey WHERE quant='sal' AND (person!='lake' OR person IS NULL);taken       person      quant       reading   ----------  ----------  ----------  ----------619         dyer        sal         0.13      622         dyer        sal         0.09      735                     sal         0.06      752         roe         sal         41.6      837         roe         sal         22.5  ``` Key points*  Databases use a special value called NULL to represent missing information.*  Almost all operations on NULL produce NULL.*  Queries can test for NULLs using IS NULL and IS NOT NULL. Exercises* What do you expect the following query to do?```SELECT * FROM Visited WHERE dated IN ('1927-02-08', NULL);```What does it do?* Some database designers prefer to use a sentinel value to mark missing data rather than null. For example, they will use the date “0000-00-00” to mark a missing date, or -1.0 to mark a missing salinity or radiation reading (since actual readings cannot be negative). What does this simplify? What burdens or risks does it introduce?  Maybe the next section will help inform this discussion.  AggregationAggregation functions take a whole set of values as input.  For example, let's look at the dates of site visits.```sqlite> SELECT dated FROM Visited;dated     ----------1927-02-081927-02-101930-01-071930-01-121930-02-26          1932-01-141932-03-22```To find the earliest date, we use the min() function.  It considers all dates selected from Visited.```sqlite> SELECT min(dated) FROM Visited;min(dated)----------1927-02-08```...similarly for the latest date.```sqlite> SELECT max(dated) FROM Visited;max(dated)----------1932-03-22```The avg() function returns the mean of a set of values, in this case all salinities.```sqlite> SELECT avg(reading) FROM Survey WHERE quant='sal';avg(reading)    ----------------7.20333333333333```count() gives us the number of values considered.```sqlite> SELECT count(reading) FROM Survey WHERE quant='sal';count(reading)--------------9 ```sum() gives the sum of values```sqlite> SELECT sum(reading) FROM Survey WHERE quant='sal';sum(reading)------------64.83 ```We know that (the two) salinity readings greater than 1 are improperly recorded, so we can filter them out.```sqlite> SELECT min(reading), max(reading) FROM Survey WHERE quant='sal' AND reading<=1.0;min(reading)  max(reading)------------  ------------0.05          0.21 ```Let's confirm how many good readings we have.```sqlite> SELECT person, count(*) FROM Survey WHERE quant='sal' AND reading<=1.0;person      count(*)  ----------  ----------lake        7   ```If these functions are called when there is no data, they return *null*.  In the following case, there is no quantity measured called 'missing', so no data is passed on to the aggregation functions.```sqlite> SELECT person, max(reading), sum(reading) FROM Survey WHERE quant='missing';person      max(reading)  sum(reading)----------  ------------  ------------                                      ```*null* values are ingored by aggregation functions.  Passing *null* to these functions does not cause an error.```sqlite> SELECT min(dated) FROM Visited;min(dated)----------1927-02-08```This is handy, because otherwise we would have to always be careful to filter out *null* records, as in the following.```sqlite> SELECT min(dated) FROM Visited WHERE dated IS NOT NULL;min(dated)----------1927-02-08```Suppose we want to find the average radiation reading from each person.  The query below may not produce what we desire.```sqlite> SELECT person, count(reading), round(avg(reading), 2)   ...> FROM  Survey   ...> WHERE quant='rad';person      count(reading)  round(avg(reading), 2)----------  --------------  ----------------------roe         8               6.56   ```It reports the average over all radiation measurements, and reports 'roe' as the associated person... likely because 'roe' was the last person queried.Instead, we can specify the person as follows,```sqlite> SELECT person, count(reading), round(avg(reading), 2)   ...> FROM  Survey   ...> WHERE quant='rad'   ...> AND   person='dyer';person      count(reading)  round(avg(reading), 2)----------  --------------  ----------------------dyer        2               8.81 ```but we would need to this for every person individually.  This would be tedious.  Instead, we can use the feature GROUP BY, which accomplishes what we want.```sqlite> SELECT   person, count(reading), round(avg(reading), 2)   ...> FROM     Survey   ...> WHERE    quant='rad'   ...> GROUP BY person;person      count(reading)  round(avg(reading), 2)----------  --------------  ----------------------dyer        2               8.81                  lake        2               1.82                  pb          3               6.66                  roe         1               11.25     ```We can group results by more than one field.  E.g. by person and by measured quantity.```sqlite> SELECT   person, quant, count(reading), round(avg(reading), 2)   ...> FROM     Survey   ...> GROUP BY person, quant;person      quant       count(reading)  round(avg(reading), 2)----------  ----------  --------------  ----------------------            sal         1               0.06                              temp        1               -26.0                 dyer        rad         2               8.81                  dyer        sal         2               0.11                  lake        rad         2               1.82                  lake        sal         4               0.11                  lake        temp        1               -16.0                 pb          rad         3               6.66                  pb          temp        2               -20.0                 roe         rad         1               11.25                 roe         sal         2               32.05 ```And to make things even nicer, we can sort by multiple fields as well.```sqlite> SELECT   person, quant, count(reading), round(avg(reading), 2)   ...> FROM     Survey   ...> WHERE    person IS NOT NULL   ...> GROUP BY person, quant   ...> ORDER BY person, quant;person      quant       count(reading)  round(avg(reading), 2)----------  ----------  --------------  ----------------------dyer        rad         2               8.81                  dyer        sal         2               0.11                  lake        rad         2               1.82                  lake        sal         4               0.11                  lake        temp        1               -16.0                 pb          rad         3               6.66                  pb          temp        2               -20.0                 roe         rad         1               11.25                 roe         sal         2               32.05 ```Looking more closely, this query:*   selected records from the Survey table where the person field was not null;*   grouped those records into subsets so that the person and quant values in each subset were the same;*   ordered those subsets first by person, and then within each sub-group by quant; and*   counted the number of records in each subset, calculated the average reading in each, and chose a person and quant value from each (it doesn’t matter which ones, since they’re all equal). Key points*   Use aggregation functions to combine multiple values.*  Aggregation functions ignore null values.*   Aggregation happens after filtering.*   Use GROUP BY to combine subsets separately.*   If no aggregation function is specified for a field, the query may return an arbitrary value for that field. Exercises* How many temperature readings did Frank Pabodie record, and what was their average value?* The average of a set of values is the sum of the values divided by the number of values. Does this mean that the avg function returns 2.0 or 3.0 when given the values 1.0, null, and 5.0?* We want to get the deviation of each radiation reading from the average radiation reading.  We try the following.```SELECT reading - avg(reading) FROM Survey WHERE quant='rad';```What actually happens and why?  Combining DataAs it stands, all our data is held in four separate tables.  This is more efficient in general than having a single big table with all fields, because we avoid repeating facts in our database.  For example, once we have estabished the location of a site (latitude and longitude) in the *Site* table, there is no real need to record the location for every record in *Survey*.But what if we require, for example, that each reported measurement be accompanied by the date, longitude and latitude?We need to combine records in *Site* and *Visited* with those in *Survey*.  This can be accomplished with JOIN.  Let's explore how this works by considering the *Site* and *Visited* tables.```sqlite> SELECT * FROM Site;name        lat         long      ----------  ----------  ----------DR-1        -49.85      -128.57   DR-3        -47.15      -126.72   MSK-4       -48.87      -123.4  ``````sqlite> SELECT * FROM Visited;id          site        dated     ----------  ----------  ----------619         DR-1        1927-02-08622         DR-1        1927-02-10734         DR-3        1930-01-07735         DR-3        1930-01-12751         DR-3        1930-02-26752         DR-3                  837         MSK-4       1932-01-14844         DR-1        1932-03-22```We see that there are 3 sites and 8 different visits to these sites.  We want to write a query that will report the longitude and latitude for each entry in *Visited*. Notice that both *name* in *Site* and *site* in *Visited* both correspond to the name of the sampling site.  The JOIN operation will take each record (row) in *Site* and combine it with each record (row) in *Visited*, yielding 3\*8 = 24 records.  That's not what we want, but it is a step in the right direction.```sqlite> SELECT * FROM Site JOIN Visited;name        lat         long        id          site        dated     ----------  ----------  ----------  ----------  ----------  ----------DR-1        -49.85      -128.57     619         DR-1        1927-02-08DR-1        -49.85      -128.57     622         DR-1        1927-02-10DR-1        -49.85      -128.57     734         DR-3        1930-01-07DR-1        -49.85      -128.57     735         DR-3        1930-01-12DR-1        -49.85      -128.57     751         DR-3        1930-02-26DR-1        -49.85      -128.57     752         DR-3                  DR-1        -49.85      -128.57     837         MSK-4       1932-01-14DR-1        -49.85      -128.57     844         DR-1        1932-03-22DR-3        -47.15      -126.72     619         DR-1        1927-02-08DR-3        -47.15      -126.72     622         DR-1        1927-02-10DR-3        -47.15      -126.72     734         DR-3        1930-01-07DR-3        -47.15      -126.72     735         DR-3        1930-01-12DR-3        -47.15      -126.72     751         DR-3        1930-02-26DR-3        -47.15      -126.72     752         DR-3                  DR-3        -47.15      -126.72     837         MSK-4       1932-01-14DR-3        -47.15      -126.72     844         DR-1        1932-03-22MSK-4       -48.87      -123.4      619         DR-1        1927-02-08MSK-4       -48.87      -123.4      622         DR-1        1927-02-10MSK-4       -48.87      -123.4      734         DR-3        1930-01-07MSK-4       -48.87      -123.4      735         DR-3        1930-01-12MSK-4       -48.87      -123.4      751         DR-3        1930-02-26MSK-4       -48.87      -123.4      752         DR-3                  MSK-4       -48.87      -123.4      837         MSK-4       1932-01-14MSK-4       -48.87      -123.4      844         DR-1        1932-03-22```Many records in this joined set of tables do not make sense.  We only want to keep records where *name* is the same as *site*.  Records with information about two different sites need to be filtered out.  This we do using ON instead of WHERE.```sqlite> SELECT * FROM Site JOIN Visited ON Site.name=Visited.site;name        lat         long        id          site        dated     ----------  ----------  ----------  ----------  ----------  ----------DR-1        -49.85      -128.57     619         DR-1        1927-02-08DR-1        -49.85      -128.57     622         DR-1        1927-02-10DR-1        -49.85      -128.57     844         DR-1        1932-03-22DR-3        -47.15      -126.72     734         DR-3        1930-01-07DR-3        -47.15      -126.72     735         DR-3        1930-01-12DR-3        -47.15      -126.72     751         DR-3        1930-02-26DR-3        -47.15      -126.72     752         DR-3                  MSK-4       -48.87      -123.4      837         MSK-4       1932-01-14```The notation used in *Site.name* allows us to refer to fields in different tables unambiguously.Now let's say we want to report latitude, longitude and date.  We can use the . notation.```sqlite> SELECT Site.lat, Site.long, Visited.dated   ...> FROM   Site JOIN Visited   ...> ON     Site.name=Visited.site;lat         long        dated     ----------  ----------  -----------49.85      -128.57     1927-02-08-49.85      -128.57     1927-02-10-49.85      -128.57     1932-03-22-47.15      -126.72               -47.15      -126.72     1930-01-07-47.15      -126.72     1930-01-12-47.15      -126.72     1930-02-26-48.87      -123.4      1932-01-14```JOIN allows us to combine data from more than two tables.  The following takes data from three.```sqlite> SELECT Site.lat, Site.long, Visited.dated, Survey.quant, Survey.reading   ...> FROM   Site JOIN Visited JOIN Survey   ...> ON     Site.name=Visited.site   ...> AND    Visited.id=Survey.taken   ...> AND    Visited.dated IS NOT NULL;lat         long        dated       quant       reading   ----------  ----------  ----------  ----------  -----------49.85      -128.57     1927-02-08  rad         9.82      -49.85      -128.57     1927-02-08  sal         0.13      -49.85      -128.57     1927-02-10  rad         7.8       -49.85      -128.57     1927-02-10  sal         0.09      -47.15      -126.72     1930-01-07  rad         8.41      -47.15      -126.72     1930-01-07  sal         0.05      -47.15      -126.72     1930-01-07  temp        -21.5     -47.15      -126.72     1930-01-12  rad         7.22      -47.15      -126.72     1930-01-12  sal         0.06      -47.15      -126.72     1930-01-12  temp        -26.0     -47.15      -126.72     1930-02-26  rad         4.35      -47.15      -126.72     1930-02-26  sal         0.1       -47.15      -126.72     1930-02-26  temp        -18.5     -48.87      -123.4      1932-01-14  rad         1.46      -48.87      -123.4      1932-01-14  sal         0.21      -48.87      -123.4      1932-01-14  sal         22.5      -49.85      -128.57     1932-03-22  rad         11.25 ```We can successfully combine corresponding records in different tables because of *primary keys* and *foreign keys*.A *primary key* is a value, or combination of values, that uniquely identify a record. For the *Person* table, any field, even the row number, could be a primary key because all the first and family names are unique.```sqlite> SELECT rowid, * FROM Person;rowid       id          personal    family    ----------  ----------  ----------  ----------1           dyer        William     Dyer      2           pb          Frank       Pabodie   3           lake        Anderson    Lake      4           roe         Valentina   Roerich   5           danforth    Frank       Danforth```Your student number is a primary key in MUN's student database!A *foreign key* is a value, or combination of values, that uniquely identifies records in another table.  E.g.,```sqlite> SELECT * FROM Survey;taken       person      quant       reading   ----------  ----------  ----------  ----------619         dyer        rad         9.82      619         dyer        sal         0.13      622         dyer        rad         7.8       622         dyer        sal         0.09      734         pb          rad         8.41      734         lake        sal         0.05      734         pb          temp        -21.5     735         pb          rad         7.22      735                     sal         0.06      735                     temp        -26.0     751         pb          rad         4.35      751         pb          temp        -18.5     751         lake        sal         0.1       752         lake        rad         2.19      752         lake        sal         0.09      752         lake        temp        -16.0     752         roe         sal         41.6      837         lake        rad         1.46      837         lake        sal         0.21      837         roe         sal         22.5      844         roe         rad         11.25  ```*Survey.person* is a foreign key linking records in *Survey* to records in *Person*. Key points*   Use JOIN to combine data from two tables.*   Use table.field notation to refer to fields when doing joins.*   Every fact should be represented in a database exactly once.*   A join produces all combinations of records from one table with records from another.*   A primary key is a field (or set of fields) whose values uniquely identify the records in a table.*   A foreign key is a field (or set of fields) in one table whose values are a primary key in another table.*   We can eliminate meaningless combinations of records by matching primary keys and foreign keys between tables.*    The most common join condition is matching keys. Exercises* Write a query that lists all radiation readings from the DR-1 site.* Describe in your own words what the following query produces:```SELECT Site.name FROM Site JOIN VisitedON Site.lat='1932-01-01';```  Data HygieneThe relational model together with JOIN allows us to store access data efficiently.  There are 4 rules on how to best store data.1. Every value should be atomic.  This means that values should not be composed of parts that we might want to use separately.  For example, first and family names should be stored separately.  If we were to store a name like Eloise St. Cyr as a single value, we might run into trouble trying to extract the family name.2. Every record (row) should have a unique primary key.  This could be a serial number that otherwsie has no meaning, a unique field (like *id* in *Person*) or a combination of fields (*taken*, *person*, *quant*) for *Survey* records.3. No redundant information.  For example, we could elimate the *Site* table and rewrite *Visited* as below.   Here the latitudes and longitudes are needlessly repeated when measurements are taken at the same location.  Problem is, if we needed to update one of the location's latitude, we would need to make corrections in a number of places, and we might not be able to determine unambiguously where to make the changes.    ```    sqlite> SELECT id, lat, long, dated  FROM Visited JOIN Site ON Visited.site=Site.name;    id          lat         long        dated         ----------  ----------  ----------  ----------    619         -49.85      -128.57     1927-02-08    622         -49.85      -128.57     1927-02-10    734         -47.15      -126.72     1930-01-07    735         -47.15      -126.72     1930-01-12    751         -47.15      -126.72     1930-02-26    752         -47.15      -126.72                   837         -48.87      -123.4      1932-01-14    844         -49.85      -128.57     1932-03-22    ```    4. Store units of values.  For example, some salinities were expressed as percentage and thus appeared to be erroneous. Key points*  Every value in a database should be atomic.*  Every record should have a unique primary key.*  A database should not contain redundant information.*  Units and similar metadata should be storedwith the data. Exercises* Which of the following are atomic values? Which are not? Why?    * New Zealand    * 87 Turing Avenue    * January 25, 1971    * the XY coordinate (0.5, 3.3)* What is the primary key in this table? I.e., what value or combination of values uniquely identifies a record?| latitude   |longitude  |  date    |   temperature ||----------- | :------: | :-------: | ------------: || 57.3        | -22.5   | 2015-01-09  | -14.2 |  Creating and Modifying DataWe have been so far querying existing data.  Now we will create, delete and modify data.The first command to learn is CREATE TABLE.  It's argument is the table name and the name and types of its columns.  The commands used to create the tables in our data base were.```CREATE TABLE Person(id text, personal text, family text);CREATE TABLE Site(name text, lat real, long real);CREATE TABLE Visited(id integer, site text, dated text);CREATE TABLE Survey(taken integer, person text, quant real, reading real);```SQLite types include* INTEGER a signed integer * REAL a floating point number * TEXT a character string * BLOB a “binary large object”, such as an imageBooleans are represented by 0 (False) and 1 (True).Other database managers may have special types for geographic location, time, Booleans, etc.A more thorough table creation might include restriction on values, and information about keys,```CREATE TABLE Survey(    taken   integer not null, -- where reading taken    person  text,             -- may not know who took it    quant   real not null,    -- the quantity measured    reading real not null,    -- the actual reading    primary key(taken, quant),    foreign key(taken) references Visited(id),    foreign key(person) references Person(id));```To delete a table, use DROP TABLE.  E.g. to delete the *Survey* table:```DROP TABLE Survey;```Once a table exists, we can insert data into it.  Let's insert some data into the *Site* table.```sqlite> SELECT * FROM Site;name        lat         long      ----------  ----------  ----------DR-1        -49.85      -128.57   DR-3        -47.15      -126.72   MSK-4       -48.87      -123.4 sqlite> DROP TABLE Site;sqlite> DROP TABLE Site;Error: no such table: Sitesqlite> CREATE TABLE Site(name text, lat real, long real);sqlite> SELECT * FROM Site;sqlite> INSERT INTO Site values('DR-1', -49.85, -128.57);sqlite> INSERT INTO Site values('DR-3', -47.15, -126.72);sqlite> INSERT INTO Site values('MSK-4', -48.87, -123.40);sqlite> SELECT * FROM Site;                               name        lat         long      ----------  ----------  ----------DR-1        -49.85      -128.57   DR-3        -47.15      -126.72   MSK-4       -48.87      -123.4```We can also insert values into tables from existing tables.```sqlite> CREATE TABLE JustLatLong(lat text, long text);sqlite> SELECT lat, long FROM Site;lat         long      ----------  -----------49.85      -128.57   -47.15      -126.72   -48.87      -123.4    sqlite> INSERT INTO JustLatLong SELECT lat, long FROM Site;sqlite> SELECT *  FROM JustLatLong;    lat         long      ----------  -----------49.85      -128.57   -47.15      -126.72   -48.87      -123.4    sqlite> sqlite> .tablesJustLatLong  Person       Site         Survey       Visited ```We can also change existing values.```sqlite> UPDATE Site SET lat=-47.87, long=-122.40 WHERE name='MSK-4';sqlite> SELECT * FROM Site;name        lat         long      ----------  ----------  ----------DR-1        -49.85      -128.57   DR-3        -47.15      -126.72   MSK-4       -47.87      -122.4```Deleting an entry is also possible, but must be done with caution.  For example, we can delete <NAME> form the *Person* table easily enough (he did not take any data).```sqlite> SELECT * FROM Person;id          personal    family    ----------  ----------  ----------dyer        William     Dyer      pb          Frank       Pabodie   lake        Anderson    Lake      roe         Valentina   Roerich   danforth    Frank       Danforth sqlite> DELETE FROM Person WHERE id = 'danforth';sqlite> SELECT * FROM Person;                    id          personal    family    ----------  ----------  ----------dyer        William     Dyer      pb          Frank       Pabodie   lake        Anderson    Lake      roe         Valentina   Roerich ```However, deleting from the *Person* table someone who took data, like <NAME>, would render the database inconsistent, since the *Survey* table would contain entries referring to him, when he no longer exists!This problem is called referential integrity: we need to ensure that all references between tables can always be resolved correctly. One way to do this is to delete all the records that use 'lake' as a foreign key before deleting the record that uses it as a primary key. If our database manager supports it, we can automate this using cascading delete. However, this technique is outside the scope of this lecture. Hybrid StorageMany applications use a hybrid storage model instead of putting everything into a database: the actual data (such as astronomical images) is stored in files, while the database stores the files’ names, their modification dates, the region of the sky they cover, their spectral characteristics, and so on. This is also how most music player software is built: the database inside the application keeps track of the MP3 files, but the files themselves live on disk. Key points*  Use CREATE and DROP to create and delete tables.*  Use INSERT to add data.*  Use UPDATE to modify existing data.*  Use DELETE to remove data.*  It is simpler and safer to modify data when every record has a unique primary key.*  Do not create dangling references by deleting records that other records refer to. Exercise* Write an SQL statement to replace all uses of *null* in Survey.person with the string 'unknown'.  Writing Out Query Results to FilesSo far we have just watched the output from our queries scroll down our screen.  What if we wish to save them to a file? There are a number of ways to do this.Let's start with the query```sqlite> SELECT id, site FROM Visited WHERE dated IS NOT NULL;id          site      ----------  ----------619         DR-1      622         DR-1      734         DR-3      735         DR-3      751         DR-3      837         MSK-4     844         DR-1 ```The output, as usual, is to the screen.  We can change the output to a file via, run the query again, and change the output back to the screen (standard output) using the SQLite command *.output* as follows.```sqlite> .output visitsWithDates.datsqlite> SELECT id, site FROM Visited WHERE dated IS NOT NULL;sqlite> .output stdout sqlite> SELECT id, site FROM Visited WHERE dated IS NOT NULL;id          site      ----------  ----------619         DR-1      622         DR-1      734         DR-3      735         DR-3      751         DR-3      837         MSK-4     844         DR-1 ```We should see the file *visitsWithDates.dat* now in the current directory.<jupyter_code>ls
!cat visitsWithDates.dat<jupyter_output>id          site      
----------  ----------
619         DR-1      
622         DR-1      
734         DR-3      
735         DR-3      
751         DR-3      
837         MSK-4     
844         DR-1<jupyter_text>Another way to do this is to run the query through sqlite3 form the command line.<jupyter_code>!sqlite3 survey.db "SELECT id, site FROM Visited WHERE dated IS NOT NULL;"<jupyter_output>619|DR-1
622|DR-1
734|DR-3
735|DR-3
751|DR-3
837|MSK-4
844|DR-1<jupyter_text>The formating isn't quite what we are used to.  We can add flags to fix that. Let's also redirect output to a file.  [Remember, ! means that what follows is executed in the bash shell, so we could do all this directly on the command line.]<jupyter_code>!sqlite3 survey.db -column -header "SELECT id, site FROM Visited WHERE dated IS NOT NULL;" > visitsWithDates2.dat
cat visitsWithDates2.dat<jupyter_output>id          site      
----------  ----------
619         DR-1      
622         DR-1      
734         DR-3      
735         DR-3      
751         DR-3      
837         MSK-4     
844         DR-1<jupyter_text>Or we can save all of our commands in a file.  Let's use an editor to put the following into a file into a file called query.txt.```.mod column.headers onSELECT id, site FROM Visited WHERE dated IS NOT NULL;```<jupyter_code>!sqlite3 survey.db ".read query.txt" > visitsWithDates3.dat
cat visitsWithDates3.dat<jupyter_output>id          site      
----------  ----------
619         DR-1      
622         DR-1      
734         DR-3      
735         DR-3      
751         DR-3      
837         MSK-4     
844         DR-1<jupyter_text>Python and DatabasesFinally, we'll take a look at how we can interact with an SQLite database from python.  Of course, there is a library we will have to import, but the steps are fairly self explanatory.<jupyter_code>import sqlite3
connection = sqlite3.connect("survey.db")
cursor = connection.cursor() # The cursor lets us act as though we are at the sqlite3 prompt
cursor.execute("SELECT Site.lat, Site.long FROM Site;")
results = cursor.fetchall()
for r in results:
    print(r)
cursor.close()
connection.close()
print(results)<jupyter_output>[(-49.85, -128.57), (-47.15, -126.72), (-47.87, -122.4)]<jupyter_text>Queries in real applications will often depend on values provided by users. For example, this function takes a user’s ID as a parameter and returns the corresponding name.<jupyter_code>def get_name(database_file, person_id):
    query = "SELECT personal || ' ' || family FROM Person WHERE id='" + person_id + "';"

    connection = sqlite3.connect(database_file)
    cursor = connection.cursor()
    cursor.execute(query)
    results = cursor.fetchall()
    cursor.close()
    connection.close()

    return results[0][0]
get_name('survey.db','pb')
print("full name for dyer:", get_name('survey.db', 'dyer'))<jupyter_output>full name for dyer: <NAME><jupyter_text>We use string concatenation on the first line of this function to construct a query containing the user ID we have been given. This seems simple enough, but it is possible for malicious strings to be passed in that may damage the database, a so-called "SQL injection attack".An alternative to string concatenation is to use a *prepared statement*, which is just a SQL query template.<jupyter_code>def get_name(database_file, person_id):
    query = "SELECT personal || ' ' || family FROM Person WHERE id=?;"

    connection = sqlite3.connect(database_file)
    cursor = connection.cursor()
    cursor.execute(query, [person_id])
    results = cursor.fetchall()
    cursor.close()
    connection.close()

    return results[0][0]
print("full name for dyer:", get_name('survey.db', 'dyer'))<jupyter_output>full name for dyer: <NAME><jupyter_text>Key points*  General-purpose languages have libraries for accessing databases.*  To connect to a database, a program must use a library specific to that database manager.*  These libraries use a connection-and-cursor model.*  Programs can read query results in batches or all at once.*  Queries should be written using parameter substitution, not string formatting. Exercises* Write a Python program that creates a new database in a file called original.db containing a single table called Pressure, with a single field called reading, and inserts 100,000 random numbers between 10.0 and 25.0. How long does it take this program to run? How long does it take to run a program that simply writes those random numbers to a file?* Write a Python program that creates a new database called backup.db with the same structure as original.db and copies all the values greater than 20.0 from original.db to backup.db. Which is faster: filtering values in the query, or reading everything into memory and filtering in Python?<jupyter_code>import random
import time
!rm original.db

n=100000
s1=time.time()
connection = sqlite3.connect('original.db')
cursor = connection.cursor()
cursor.execute("CREATE TABLE Pressure(reading real)")
for i in range(1,n+1):
    x=random.uniform(10.0,25.0)
    cursor.execute("INSERT INTO Pressure values(?);",[x])
cursor.close()
connection.commit() #saves changes (all those insertions)
connection.close()
s2=time.time()
print(s2-s1)

n=100000
s1=time.time()
f = open('workfile.txt', 'w')
for i in range(1,n+1):
    x=random.uniform(10.0,25.0)
    f.write(str(x)+'\n')
f.close()
s2=time.time()
print(s2-s1)
!rm backup.db

s1=time.time()
connection_b = sqlite3.connect('backup.db')
cursor_b = connection_b.cursor()
cursor_b.execute("CREATE TABLE Pressure(reading real)")

connection = sqlite3.connect('original.db')
cursor = connection.cursor()

cursor.execute("SELECT reading FROM Pressure WHERE reading>20.0;")
result = cursor.fetchall()

for r in result:
    cursor_b.execute("INSERT INTO Pressure values(?);",[r[0]])

cursor.close()
connection.close()
cursor_b.close()
connection_b.commit() #saves changes (all those insertions)
connection_b.close()
s2=time.time()
print(s2-s1)
s1=time.time()
f = open('workfile-backup.txt', 'w')
g = open('workfile.txt','r')

lines=g.readlines()
for s in lines:
    x=float(s)
    if (x>20.0):
        f.write(str(x)+'\n')
f.close()
g.close()
s2=time.time()
print(s2-s1)
# This code uses executemany to write to the backup database... hopefully it is faster.
!rm backup.db

s1=time.time()
connection_b = sqlite3.connect('backup.db')
cursor_b = connection_b.cursor()
cursor_b.execute("CREATE TABLE Pressure(reading real)")

connection = sqlite3.connect('original.db')
cursor = connection.cursor()

cursor.execute("SELECT reading FROM Pressure WHERE reading>20.0;")
result = cursor.fetchall()

cursor_b.executemany("INSERT INTO Pressure values(?);",result)

cursor.close()
connection.close()
cursor_b.close()
connection_b.commit() #saves changes (all those insertions)
connection_b.close()
s2=time.time()
print(s2-s1)<jupyter_output>0.16982293128967285
{'fraction_non_alphanumeric': 0.11061878509969601, 'fraction_numerical': 0.061500678379445964, 'mean_word_length': 1.8392822313243613, 'pattern_counts': {'":': 0, '<': 50, '<?xml version=': 0, '>': 161, 'https://': 2, 'lorem ipsum': 0, 'www.': 1, 'xml': 0}, 'pii_count': 0, 'substrings_counts': 1, 'word_list_counts': {'cursed_substrings.json': 65, 'profanity_word_list.json': 0, 'sexual_word_list.json': 0, 'zh_pornsignals.json': 0}}
{'chain_length': '11', 'dir': 'jupyter-structured-clean-dedup', 'id': '43332', 'license': 'CC-BY-4.0', 'n_tokens_mistral': 17567, 'n_tokens_neox': 15168, 'n_words': 6720, 'path': 'lectures/Databases.ipynb', 'repo_name': 'CMSC6950/CMSC6950.github.io', 'size': '58311'}