Database Lab #8

Lab period of 8 November 2017

Today will be a short lab, and allow time to catch up. We will continue with client side applications during the final 3 lab periods, the 15^th 23 and 29^th of November.

Last call for an application with select from a join

Not everyone has sent me a program incorporating SQL. Some who did only used a simple query like "select * from students"
I asked for a "flexible" query that normally would involve a join, or a "prepared query", to print an order, something like:

Sale to: Maisie
Fruit    Quantity    Price  Total
orange         1      0.30   0.30
kiwi           2      0.55   1.10
             Grand total     1.40

(from makeorder.py)

Anyway, there will be time today to get caught up with this. Don't forget to email me new or revised programs
Next week: An application to actually record a new order
The last week either a GUI version, or a Web application (HTML lets you define a form quite easily.)

Transactions, take 2

We will give this one more try. This time, using XFER, prepare an SQL statement to withdraw some sickles from GWB. (DONT DO IT YET.) At about 3:05, on my signal, do it (press enter). There may be some small delays, we will see whether the total of "vault" remains constant.

Mine will be:

SELECT xfer(34, 'jense', 'out');

This should return 966 as my new vault, and as a side effect, my pouch will have 34 (as shown in a NOTICE)

Query plans using EXPLAIN

explain select .... will show you the query plan for any query. From it you can deduce the relational algebra "tree", the first operations will be at the bottom, and the last one at the top of the indented list. It will also show you what algorithm is chosen to do something.
Please note how joins are processed. Also note whether indexes are used, where they exist. (eg. Battles has an index on the primary key).
"Scan" means get rows from a table. Sequential is the fastest, Index scan will get the rows in order by primary key.
Finally, there is an estimate of "cost" on each line. The top line will have total cost. (Projection operations are not explicitly shown.)
PGAdmin3 will show the plan, and also a nice graphical display.

Timetable database

Subqueries are in interesting challenge for the query planner. Sometimes they can in fact be transformed into queries using join, which may be chosen as more efficient. Which is better, a subquery or a join? Try a few queries such as:

Finding the (names of) students who are in class at 13:30 on Mondays
Finding the information about professors who teach you
Finding room conflicts (different courses at the same time in the same room). We hope there are not any, but it would take a query to discover them, what would be its plan?

(Optional) You may recall doing a query in the SHIPS database, to find ships that were damaged in one battle and later fought in another battle.
Ships that "lived to fight another day."
There are at least 3 approaches to this query, try to formulate SQL for each, and compare the results:

Use a subquery to find out, for each outcome where result='damaged', if there is some later outcome for that ship.
Join Outcomes with itself using(ship), and look up the dates of the battles, in the where clause
Join the view OB with itself using(ship), and compare dates in the where clause. Note whether the join defining OB is seen in the plan.

Try at least 2 different queries. Which one has the least cost? Why do you think this is?

Please make a text file with the queries and their plans you observed, and some comments. Did you find any surprising??

Finally, submit your text file as usual. .

submit somefile

Back to CSC207 or Lin Jensen