Consider a database consisting of the following five tables. Attributes that are underlined have been

designated the primary key of their respective tables, and fields with identical names in different

tables can be safely assumed to be foreign keys.

Students(SID, Name, Major)

Projects(PID, Project Name)

Courses(CID, Course Name)

Members(PID, SID)

Enrollments(CID, SID)

The Project Name and Course Name fields are specified as UNIQUE, and all fields are required except

for Major, which may be NULL (to represent undeclared).

1

Write each of the following queries in a separate file titled Query[N].sql, where [N] is the number of

the query as enumerated. Each query is worth 10 points. Follow strictly the order of fields to be

returned. There are restrictions on the constructs that you can use for some of the queries. We have

not provided you with any sample data for this part of the assignment, so it is suggested that you

devise a way to test your scripts to ensure that they are correct. In all instances, do not duplicate

rows in your results.

1. Write a query that finds the list of the IDs of all students who are enrolled in (EECS442 and

EECS445 and EECS492) or (EECS482 and EECS486) or (EECS281). The results should be

sorted in ascending order. Note that "EECS442" is a course title and that there is no space

between the department abbreviation and the course number. This query should be

completed without views or nested queries; correct implementations of this query that

utilize views or nested queries will receive half credit.

2. Write a query that finds the IDs and names of all CS majors who are enrolled in at least one

CS-heavy course. A CS-heavy course is defined as one in which strictly fewer than 50 non-CS

majors are enrolled; this includes 0 non-CS majors. The results should be sorted in descending

order by ID. Do not return duplicates. A student whose major is CS will have the VARCHAR2

value "CS" for their Major field, while a non-CS student will have something else. Remember

that the Major field can be NULL.

3. Write a query that finds the IDs and names of all students with at least one project partner

who is enrolled in (EECS482 or EECS483) and (EECS484 or EECS485) and (EECS280). Do not

return duplicates. The results should be sorted in descending order by the students' names.

This query should be completed without views or set operations (MINUS, UNION,

INTERSECT); correct implementations of this query that utilize views or set operations

will receive half credit.

4. Create a view called StudentPairs with two columns, SID1 and SID2. The contents of this view

should be all pairs of IDs of two students who are enrolled in at least one common class but

are not already partners on any project. You should report each student pair exactly once. The

lower ID should be SID1, and the higher ID should be SID2. The contents of the view do not

need to be sorted.