foods.sql from Allen and Owens (2010) - download sourcecode.Download xyz.sql here from Moodle.
Q: What type of file is this?
Tip: open it and have a look!
Task: Start SQLite so that tables of the xyz.sql database will be shown with header and columns.
Tip: In these examples, it is assumed that the file xyz.sql is in the same directory as the executable, or at least that you’re starting SQLite in the same directory. Otherwise, you have to add the path to the file so that it can be found.
There are different ways to load the database, e.g.: from the SQLite sub-shell with .read, after opening a database (for permanency):
marcus@linux:$ sqlite3 -column -header
sqlite> .open xyz.db
sqlite> .read xyz.sqlYou can also load it into a new or existing database on the system shell and then start the sub-shell:
marcus@linux:$ sqlite3 xyz.db < xyz.sql
marcus@linux:$ sqlite3 -column -header xyz.dbOr by using the -init option from the system shell:
marcus@linux:$ sqlite3 -column -header -init xyz.sql
-- Loading resources from xyz.sql
SQLite version 3.22.0 2018-01-22 18:45:57
Enter ".help" for usage hints.
sqlite>In this last example, we’re working in a transient database (no permanency after leaving the SQLite sub-shell).
Check the database and the tables once you’re on the SQLite sub-shell:
sqlite> .database
main: /home/marcus/xyz.db
sqlite> .tables
x y zYour first step should always be to try to understand the data. They’re in tables, neatly organized, and you know:
Understanding the data means looking at the tables to identify the data types of the attributes and - more importantly - their meaning.
If you have more than one table, you also want to understand how the tables relate to one another. There is a systematic way to do this - the entity relationship diagram. The foods.sql database has this simple diagram. The database xyz.sql only has one existing relation, because one attribute (a) is shared between tables x and z.
We’re going to go through the different steps of the SELECT pipeline as introduced in class - please check the mindmap for details.
To get help, use online resources. These include forums such as stackoverflow.com or free tutorials such as tutorialspoint. Of course, you can also ask a colleague or me, if you like (preferably via our FAQ Moodle forum).
An example: in class, we talked about the SQLite function max(). When applying it to the foods.name column, the result is:
sqlite> select max(name) from foods;
Yoo HooI did not understand this result at first, because I thought that max() requires a numerical argument. But a quick search for the definition reveals:
The max() aggregate function returns the maximum value of all values
in the group. The maximum value is the value that would be returned
last in an ORDER BY on the same column. Aggregate max() returns NULL
if and only if there are no non-NULL values in the group.The entry foods.name = 'Yoo Hoo' is the last item in the group (namely, in the whole column) because it begins with a ‘Y’. So max() in this case simply orders the entries alphabetically and returns the last item of the ordered list.
Below are all instructions and results from the demo using the xyz.sql sample database.
sqlite> select * from x;
a b
---------- ----------
1 Alice
2 Bob
3 Charlie
sqlite> select * from y;
c d
---------- ----------
1 3.14159
1 2.71828
2 1.61803
sqlite> select * from z;
a e
---------- ----------
1 100
1 150
3 300
9 900 .nullvaluesqlite> .show
echo: off
eqp: off
explain: auto
headers: on
mode: column
nullvalue: ""
output: stdout
colseparator: "|"
rowseparator: "\n"
stats: off
width:
filename: xyz.db
sqlite> .nullvalue [NULL]sqlite> select * FROM x;
a b
---------- ----------
1 Alice
2 Bob
3 Charlie
sqlite> SELECT d, d*d AS dSquared FROM y;
d dSquared
---------- ------------
3.14159 9.8695877281
2.71828 7.3890461584
1.61803 2.6180210809sqlite> SELECT * FROM x JOIN y;
a b c d
---------- ---------- ---------- ----------
1 Alice 1 3.14159
1 Alice 1 2.71828
1 Alice 2 1.61803
2 Bob 1 3.14159
2 Bob 1 2.71828
2 Bob 2 1.61803
3 Charlie 1 3.14159
3 Charlie 1 2.71828
3 Charlie 2 1.61803
sqlite> SELECT * FROM x CROSS JOIN y;
a b c d
---------- ---------- ---------- ----------
1 Alice 1 3.14159
1 Alice 1 2.71828
1 Alice 2 1.61803
2 Bob 1 3.14159
2 Bob 1 2.71828
2 Bob 2 1.61803
3 Charlie 1 3.14159
3 Charlie 1 2.71828
3 Charlie 2 1.61803
sqlite> SELECT * FROM x,y;
a b c d
---------- ---------- ---------- ----------
1 Alice 1 3.14159
1 Alice 1 2.71828
1 Alice 2 1.61803
2 Bob 1 3.14159
2 Bob 1 2.71828
2 Bob 2 1.61803
3 Charlie 1 3.14159
3 Charlie 1 2.71828
3 Charlie 2 1.61803 sqlite> SELECT * FROM x JOIN y ON a = c;
a b c d
---------- ---------- ---------- ----------
1 Alice 1 2.71828
1 Alice 1 3.14159
2 Bob 2 1.61803
sqlite> SELECT * FROM x JOIN z ON x.a = z.a;
a b a e
---------- ---------- ---------- ----------
1 Alice 1 100
1 Alice 1 150
3 Charlie 3 300
sqlite> SELECT * FROM x JOIN z USING (a);
a b e
---------- ---------- ----------
1 Alice 100
1 Alice 150
3 Charlie 300 sqlite> SELECT * FROM x JOIN z USING (e);
Error: cannot join using column e - column not present in both tables
sqlite> SELECT * FROM x NATURAL JOIN z;
a b e
---------- ---------- ----------
1 Alice 100
1 Alice 150
3 Charlie 300
sqlite> SELECT * FROM x NATURAL JOIN y;
a b c d
---------- ---------- ---------- ----------
1 Alice 1 3.14159
1 Alice 1 2.71828
1 Alice 2 1.61803
2 Bob 1 3.14159
2 Bob 1 2.71828
2 Bob 2 1.61803
3 Charlie 1 3.14159
3 Charlie 1 2.71828
3 Charlie 2 1.61803 sqlite> SELECT * FROM x LEFT OUTER JOIN y;
a b c d
---------- ---------- ---------- ----------
1 Alice 1 3.14159
1 Alice 1 2.71828
1 Alice 2 1.61803
2 Bob 1 3.14159
2 Bob 1 2.71828
2 Bob 2 1.61803
3 Charlie 1 3.14159
3 Charlie 1 2.71828
3 Charlie 2 1.61803
sqlite> SELECT * FROM x LEFT OUTER JOIN z USING (a);
a b e
---------- ---------- ----------
1 Alice 100
1 Alice 150
2 Bob [NULL]
3 Charlie 300 sqlite> SELECT * FROM x JOIN y ON x.a = y.c LEFT OUTER JOIN z ON y.c = z.a;
a b c d a e
---------- ---------- ---------- ---------- ---------- ----------
1 Alice 1 2.71828 1 100
1 Alice 1 2.71828 1 150
1 Alice 1 3.14159 1 100
1 Alice 1 3.14159 1 150
2 Bob 2 1.61803 [NULL] [NULL]
sqlite> SELECT * FROM x JOIN y ON x.a = y.c;
a b c d
---------- ---------- ---------- ----------
1 Alice 1 2.71828
1 Alice 1 3.14159
2 Bob 2 1.61803 sqlite> SELECT * FROM x AS x1 JOIN x AS x2 ON x1.a = x2.a;
a b a b
---------- ---------- ---------- ----------
1 Alice 1 Alice
2 Bob 2 Bob
3 Charlie 3 Charlie
sqlite> SELECT * FROM x AS x1 JOIN x AS x2 ON x1.a +1 = x2.a;
a b a b
---------- ---------- ---------- ----------
1 Alice 2 Bob
2 Bob 3 Charlie sqlite> SELECT * FROM x WHERE b = 'Alice';
a b
---------- ----------
1 Alice
sqlite> SELECT * FROM x WHERE b = 'ALICE';
sqlite>
sqlite> SELECT * FROM y WHERE d BETWEEN 1.0 AND 3.0;
c d
---------- ----------
1 2.71828
2 1.61803
sqlite> SELECT c,d, c+d AS sum FROM y WHERE sum < 4.0;
c d sum
---------- ---------- ----------
1 2.71828 3.71828
2 1.61803 3.61803 sqlite> SELECT a, COUNT(a) AS count FROM z GROUP BY a;
a count
---------- ----------
1 2
3 1
9 1
sqlite> SELECT a, SUM(e) AS total FROM z GROUP BY a;
a total
---------- ----------
1 250
3 300
9 900
sqlite> SELECT a, SUM(e), COUNT(e),
...> SUM(e)/COUNT(e) AS expr, AVG(e) AS agg
...> FROM z GROUP BY a;
a SUM(e) COUNT(e) expr agg
---------- ---------- ---------- ---------- ----------
1 250 2 125 125.0
3 300 1 300 300.0
9 900 1 900 900.0
sqlite> SELECT a, SUM(e) AS total FROM z GROUP BY a HAVING total > 500;
a total
---------- ----------
9 900 sqlite> SELECT * FROM y ORDER BY d;
c d
---------- ----------
2 1.61803
1 2.71828
1 3.14159
sqlite> SELECT * FROM y ORDER BY d DESC;
c d
---------- ----------
1 3.14159
1 2.71828
2 1.61803
sqlite> SELECT * FROM y ORDER BY d DESC LIMIT 2;
c d
---------- ----------
1 3.14159
1 2.71828
sqlite> SELECT * FROM y ORDER BY d DESC LIMIT 2 OFFSET 1;
c d
---------- ----------
1 2.71828
2 1.61803
sqlite> SELECT * FROM y ORDER BY d DESC LIMIT 1,2;
c d
---------- ----------
1 2.71828
2 1.61803
sqlite> .q