本次加拿大代写是数据库管理系统相关的一个assignment

 

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.

Q 1:

Consider the following DDL statements:

create table takes

(ID varchar(5),
course_id varchar(8),
sec_id varchar(8),
semester varchar(6),
year numeric(4,0),
grade varchar(2),
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
(course_id varchar(8),
sec_id varchar(8),
semester varchar(6)
check (semester in (‘Fall’, ‘Winter’, ‘Spring’, ‘Summer’)),
year numeric(4,0)
check (year > 1701 and year < 2100),
building varchar(15),
room_number varchar(7),
time_slot_id varchar(4),
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:

select *
from section natural join classroom;

---

程序代写代做C/C++/JAVA/安卓/PYTHON/留学生/PHP/APP开发/MATLAB

---

本网站支持淘宝 支付宝 微信支付  paypal等等交易。如果不放心可以用淘宝交易！

E-mail: itcsdx@outlook.com  微信:itcsdx

---

如果您使用手机请先保存二维码，微信识别。如果用电脑，直接掏出手机果断扫描。