Are we allowed to look up details that are not specified in a given problem (e.g., the ID number of a given person or movie) so that we can use them in our queries?
No. The only literal values that your queries may include are ones that are specified in the text of the problem that you are solving.
I’m having trouble with one of the problems. Do you have any suggestions on how I should proceed?
First, make sure that you are following the rules of thumb that we discussed in lecture.
Next, try looking at the example queries in the notes and labs for queries that solve a problem that is similar to the one that you’re stuck on, and use those queries as models.
In particular:
The solutions to the practice problems from pages 122-127 of the coursepack are in the Lectures folder for 9/18 on Blackboard.
The solutions to problems 2 and 3 from page 121 are in the Lectures folder for 9/20 on Blackboard.
Also, when writing join conditions, don’t forget that these are based on the foreign keys and primary keys in the relevant tables. Consult the schema of the tables to remind yourself of what the keys are.
For one of my queries, it seems like I am getting too many results. Do you have any suggestions?
Make sure that you have enough join conditions (for N tables, you
need N-1 join conditions), and that each table in the FROM clause
is part of at least one of the join conditions.
For some of the SQL problems, it seems like we could either use
a SELECT command with multiple tables in the FROM clause
(i.e., a join of some sort) or a SELECT command with one or more
subquery. Do you have a preference about which approach we use?
For the purposes of PS 1, we would recommend using multiple tables
in the FROM clause whenever possible so that you can get the
necessary practice with this type of query. However, there may be
one or more problems for which a subquery is unavoidable, and you
should of course use one in such cases.
I’m not getting any results at all for one of my queries, and I don’t understand why. Do you have any suggestions?
If you are performing pattern matching, make sure that you use the
LIKE operator, rather than =. Also, make sure that you include the
necessary wildcard characters (% and/or _).
SQLite seems to allow me to combine a column name with an aggregate in the SELECT clause – even when I’m not grouping by that column. Is it okay if I do this in one of my answers?
No! You are correct that SQLite allows this, but it is not standard SQL. You should write a query that follows the rules for SQL mentioned in the lecture notes, so that it would work on any relational DBMS. In particular, you may want to consider using a subquery to obtain the result that you’re looking for.
I’m having trouble with problem 11. Do you have any additional hints?
One possible model for this problem is found in the lecture notes
on LEFT OUTER JOIN. In those notes, we considered the problem of
finding the ID and the major(s) of every student enrolled in a
course, including enrolled students with no major. In order to
include students without a major, we needed to use a LEFT OUTER
JOIN, so that students in the Enrolled table who didn’t appear in
the MajorsIn table would still be included in the result. You need
to do something similar in problem 11 to ensure that 2023 movies
that have not won any of the Oscars in the database are
still included in the result.
I’m having trouble with problem 12. Do you have any additional hints?
A good model for this problem is practice problem 5 on page 123 – the one in which we are looking for all students who are not enrolled in a course. In that case, we used a subquery to find the ids of students who are enrolled in a course, and then we used the NOT IN operator to find students whose ids were not in the result of the subquery. You will need to do something similar here.
Another good model is question 2 from the extra practice problems on page 125.
In SQL, we specify an explicit join condition for an outer join. Are we allowed to do that in relational algebra?
Yes. In the same way that you can do a regular theta join that includes an explicit join condition, you can also do an outer join with an explicit join condition.
I’m stuck on 16.3. Do you have any hints?
Yes! Here are three hints:
Make sure to use the hint that is already in the problem:
Hint: Take advantage of the set difference operator (-).
Make sure to take into account the fact that the relational-algebra query is supposed to produce a set of ids, rather than a count.
You may find it helpful to consult Lab 2. There is a problem that we solved both using SQL and relational algebra in which the relational-algebra version also requires the use of the set difference operator.
Last updated on September 23, 2024.