Motivation:
|
|
Let's look again at our servlets from last time. They're at
We'll look at
We'll spend some time discussing the Director Update example:
As an exercise, write a servlet that lists all movies whose titles match a form input. Hint: start with the MachineList example!
web.xml file (where?) to add it, unchanged
MovieList.java and change the class name.
web.xml file
Finally, let's look at a cute demo of how servlets differ from CGI scripts, in that they stay in the server. We can have a servlet that is continuously executing in a concurrent thread. For the purposes of this example, the servlet will just be computing prime numbers.
Take a look at it via the WebDB Examples
The SQL standard defines a syntax for nested queries or subqueries. Indeed, that's where the term "Structured Query Language" comes from: so far, our language has been describable by a regular expression, but now we can put queries inside queries, so we'll need context-free grammars to describe the language.
Of course, we don't really care what class of formal language SQL falls into. We care about expressivity. There are queries we can't easily express without this ability. Try the following:
Note the use of the date functions here, so this query continue to work over time. You can look them up at MySQL Reference Manual, 12.5 Date and Time Functions. Note also the removal of the directors with a zero birthdate.
The basic rules for subqueries are:
However, if they return more than one value, you get no results:
Subqueries allow you to use a new operator in the WHERE clause: EXISTS and NOT EXISTS. For example, here's how to find out people who don't have movie credits:
In many cases, a nested query is equivalent to a join. Consider:
List actors who were directed by Clint Eastwood
When this is the case, which should you choose? Considerations:
Try it as an exercise. List all names who don't have a credit. Can you exclude the ones who are directors?
In most cases, you should not care about efficiency. High performance databases will choose an execution plan that is efficient for the query, regardless of how you write it.
How do they do that? We'll discuss that on a later day, but essentially they:
For example, if there is an index in movie on director, it's either O(1) or O(log N) to look up the movies directed by a particular id, but if there's no index, it's O(n), which is huge.
For example, a join of names and movies could be huge, while the number of movies any one person has directed would be much smaller)
Some of those decisions require human assistance, so experienced SQL programmers can intervene and give hints, but we will only be able to introduce the topic in this course.
Yet another way to skin the cat is to use temporary tables. For example, consider the dreaded "bi-coastal" query, that was in two homeworks.
The privileges of creating tables and creating temporary tables are separate. Most times, you will want to deny queries the privilege of creating tables in your database. Indeed, some of you who solved the bi-coastal problem using tables, but I wasn't able to run those queries because I didn't have the privilege of creating tables in your database.
However, it's usually safe to allow someone to create temporary tables.
Temporary tables are dropped when the connection is closed!
We've talked about "joins" and "natural joins":
The fact that there may be more than one shared attribute makes it easy to make a mistake. You're thinking, say, "nm" is the only shared attribute, but it turns out that "addedby" or "birthdate" is also shared and, boom, you get the wrong results and no error message.
Look at the code in pub/mysql/joins.sql. You can load that into your own database to create and fill the tables. Then, try the following queries:
Exercise: Write a query to print actor/movie pairs using natural join.
An outer join is one where if there isn't a match, the row gets listed once. There are three kinds:
(MySQL suggests sticking to left outer joins.) Consider:
Exercise: Write a query to print names and movie id if there is a credit.
You can find out more from MySQL Reference Manual: 13.1.7.1 Join Syntax
When the inner query is independent of the outer query, we can solve the problem using temporary tables and such. Consider the following:
List movies by directors over 50
pub/mysql/movies-with-directors-over-50.sql
Or, even worse
List actors in movies by directors over 50
We can build that up step by step. When I build these up, I like to put the names in the first time, and then remove them when I nest the query.
On the other hand, sometimes the subquery depends on a value in the surrounding query. These are called correlated subqueries. Consider the following variant
List people who direct themselves
pub/mysql/people-who-direct-themselves.sql
Notice how the credit.tt and person.nm in the subquery
come from the surrounding query.
Of course, that query can also be implemented with a join:
pub/mysql/people-who-direct-themselves2.sql
A web search on "SQL nested subqueries tutorial" turns up many links. Here are some that look promising: