Instructions: Read all the instructions below carefully before you start working on the assignment, and before
you make a submission.
• The accepted formats for your submission are: pdf, txt, and java. More details below.
• You can either write your solutions in the tex file (then build to pdf) or by writing your solution by hand
or using your preferred editor (then convert to pdf). However, you are encouraged to write your solutions
in the tex file. If you decide not to write your answer in tex, it is your responsibility to make sure you
write your name and ID on the submission file.
• If you use the tex file, make sure you edit line 28 to add your name and ID. Only write your solution and
do not change anything else in the tex file. If you do, you will be penalized.
• All questions in this assignment use the university schema discussed in class (available on Brightspace
under Resources ! University Toy Database), unless otherwise stated.
• For SQL questions, upload a text file with your queries in the format shown in the file \template.txt”
uploaded on culearn. An example submission is in the file \sample.txt”. You will be penalized if the
format is incorrect or there is no text file submission.
• For programming questions, upload your .java file.
Consider the following DDL statements:
create table takes
primary key (ID, course_id, sec_id, semester, year),
foreign key (course_id,sec_id, semester, year) references section
on delete cascade,
foreign key (ID) references student
on delete cascade
create table section
check (semester in (‘Fall’, ‘Winter’, ‘Spring’, ‘Summer’)),
check (year > 1701 and year < 2100),
primary key (course_id, sec_id, semester, year),
foreign key (course_id) references course
on delete cascade,
foreign key (building, room_number) references classroom
on delete set null
Now, consider the following SQL query:
select course_id, semester, year, sec_id, avg (tot_cred)
from takes natural join student
where year = 2017
group by course_id, semester, year, sec_id
having count (ID) >= 2;
Will appending natural join section in the from clause change the returned result? Explain why?
Q 2: (2 points)
Write an SQL query using the university schema to find the names of each instructor who has never taught a
course at the university. Do this using no subqueries and no set operations.
Q 3: (2 points)
Rewrite the following query to replace the natural join with an inner join with using condition:
from section natural join classroom;
本网站支持淘宝 支付宝 微信支付 paypal等等交易。如果不放心可以用淘宝交易！
E-mail: firstname.lastname@example.org 微信:itcsdx