ASSIGNMENT 6 – These problems are to be done by hand.
Hand-write and scan in, or type your solutions where indicated on these pages.
In this assignment you will use the following tables (relations):
SYMBOLS: σ (Selection) Π (Projection) ⋈ (Join) ^ (And)
NOTE THAT WHEN A PROBLEM OR EXAMPLE USES A RANGE VARIABLE BELOW, IT IS THE FIRST LETTER OF THE TABLE NAME.
PROBLEM 1:
Using the given tables & data, show, in table form, the results of the following alge
a query:
Π sid, gpa (SENIORS)
ANSWER:
What is the SQL statement you would use to do this?
ANSWER:
PROBLEM 2:
Using the given tables & data, show, in table form, the results of the following alge
a query:
Π creditsSoFar (SENIORS)
ANSWER:
QUESTION: Do you have three rows or four? Why?
ANSWER:
What is the SQL statement you would use to do this?
ANSWER:
PROBLEM 3:
Show, in table form, the results of the following alge
a query:
JUNIORS U SENIORS
ANSWER:
What is the SQL statement you would use to do this?
ANSWER:
PROBLEM 4:
Show, in table form, the results of the following alge
a query:
σgpa > 3.5 (SENIORS)
ANSWER:
What is the SQL statement you would use to do this?
ANSWER:
PROBLEM 5:
Problem 5A:
Show, in table form, the results of the following alge
a query:
SENIORS ⋈s.sid = t.sid TAKEN
ANSWER:
What is the SQL statement you would use to do this?
ANSWER:
Problem 5B:
Show, in table form, the results of the following alge
a query:
JUNIORS ⋈s.sid = t.sid TAKEN
ANSWER:
What is the SQL statement you would use to do this?
ANSWER:
PROBLEM 6:
Show, in table form, the results of the following alge
a query:
SENIORS ⋈s.sid = t.sid TAKEN
UNION
JUNIORS ⋈s.sid = t.sid TAKEN
ANSWER:
What is the SQL statement you would use to do this?
ANSWER:
PROBLEM 7:
Write the alge
a query for this statement: Find the names of students who have taken the course with the id number of 502.
ANSWER:
Show the results in tabular form:
ANSWER:
What is the SQL statement you would use to do this?
ANSWER:
1 point extra credit: Can you think of a different way to do this? (To get the extra credit, it must be substantially different, not a simple moving around of the text.)
ALTERNATIVE ANSWER:
PROBLEM 8:
Write the alge
a query for this statement: Find the names of students who have taken CMPSC 465.
ANSWER:
Show the results in tabular form:
ANSWER:
What is the SQL statement you would use to do this?
ANSWER:
PROBLEM 9:
Write the alge
a query for this statement: Find the courses (e.g., ENGL 202C) taken by anyone with a GPA greater than or equal to 3.9
ANSWER:
Show the results in tabular form (two columns):
ANSWER:
What is the SQL statement you would use to do this?
ANSWER:
PROBLEM 10:
Write the alge
a query for this statement: Find the name of all students who have taken at least one class.
ANSWER:
Show the results in tabular form:
ANSWER:
What is the SQL statement you would use to do this?
ANSWER:
ONE POINT EXTRA CREDIT - PROBLEM 11:
Write the alge
a query for this statement: Find the name of students who have taken at least one of ENG 202C, PHYS 211.
ANSWER:
Show the results in tabular form:
ANSWER:
What is the SQL statement you would use to do this?
ANSWER: