CS145 - Spring 2003
Introduction to Databases
Online (OTC) Account Information
Please email course staff at
cs145@cs.stanford.edu for
further assistance with your OTC account. To have your password
reset, just send your account login (the cs145_user one) with your request.
You should never send a password, and staff should never ask for it.
(Keep the last line in mind; it is often true of good security systems
in the real world.)
Laboratory 2 Questions and Answers
- Because of an error in OTC, scores were not recorded for students
who made submissions for Lab 2 before 12:50am, Fri 18 Apr 2003.
This error is reportedly fixed, and scores for submission
are beginning to appear, so please try submitting now to have
a score recorded in OTC for a grade.
Also, please save your SQL queries before you submit, just in case.
(Saving your previous queries also makes it easier for you to try the
lab again later.)
Sorry, again, for the inconvenience.
- The labs do not return certificates, so for this assignment,
you do not need to save the Web page you receive after you submit.
- The labs are a little lenient about accepting SQL as correct.
Thank you to Austin and Tyrone Yu for finding and submitting queries that
are marked correct, but shouldn't be. If you encounter such queries,
please email them to CS145 staff (after you claim those points in OTC,
if you wish).
- The labs are overly strict about rejecting valid SQL, possibly
even SQL that correctly answers a question. See the questions and
answers below for some known problems, and how (sadly) we can't fix them.
- Why doesn't foo = NULL or foo <> NULL work?
SQL uses three-valued logic which propagates NULLs. In SQL, neither
NULL = NULL nor NULL <> NULL evaluate to true, so you need something
like IS NULL or IS NOT NULL to actually ask whether an expression is NULL.
- Is OTC broken? I get the error
ERROR: Io exception: End of TNS data channel when I submit!
Yes, it is; apparently, some queries (valid SQL) trigger an error in
Oracle that breaks OTC's connection to it. Unfortunately, we have no
way to work around or fix the Oracle behind OTC, so please try rewording
your SQL to see if that makes Oracle happy. Sorry for the trouble; the
OTC developers are aware of the problem. One developer indicates that
this error can be triggered if you have a query with a subquery
in both FROM and WHERE clauses, and both subqueries use GROUP BY.
- Is Oracle broken? The database reports messages with strange error codes
for correct SQL.
See if the Basic SQL Features section of
this
page document applies to your query. There may be bits of SQL that
Oracle doesn't like.
- Why does a query such as
SELECT ... FROM (SELECT...) Table
WHERE attr <= ALL (SELECT...FROM Table...)
cause an error in Oracle (table or view does not exist)?
This question has been forwarded to the OTC developers.
Of course, also make sure you have spelled
everything correctly, and checked the Basic SQL Features section of
the last FAQ.
Among other possibilities, you might not be able to reference tables
defined outside a subquery from inside the subquery's FROM line.
- Why does correct SQL cause an error in Oracle (
missing right
parenthesis) when it's obviously not missing a right parenthesis?
Sometimes, subqueries are unable to access attributes or tables outside
the subquery, and a surprising (obviously incorrect) error message comes
out. If you're getting an obviously incorrect error message out of Oracle,
it's a sign that Oracle is unhappy for some reason probably not your
fault. Unfortunately, we can only ask you to try to rewrite your query
in hopes of making Oracle happy; simplifying a query may help.
CS145 Web pages -
email the staff