编程代写 1. Create a new course ¡°CS-001¡±, titled ¡°Weekly Seminar¡±, with 3 credit – cscodehelp代写

1. Create a new course ¡°CS-001¡±, titled ¡°Weekly Seminar¡±, with 3 credits. insert into course
values (‘CS-001’, ‘Weekly Seminar’, ‘Comp. Sci.’, 3)
2. Create a section of the course from Problem 13 in Fall 2017, with sec_id of 1, and with the location of this section not yet specified.
insert into section
values (‘CS-001’, 1, ‘Fall’, 2017, null, null, null)
3. Enroll every student in the Comp. Sci. department in the section from Problem 14 insert into takes
select ID, ‘CS-001’, 1, ‘Fall’, 2017, null
from student
where dept name = ‘Comp. Sci.’
4. Delete enrollment in the section from Problem 15 where the student’s ID is 1402. delete from takes
where course_id= ‘CS-001’ and sec_id = 1 and
year = 2017 and semester = ‘Fall’ and ID=1402
5. Delete the course CS-001. What will happen if you run this delete statement without first deleting offerings (sections) of this course?
delete from takes
where course id = ‘CS-001¡®;
delete from section
where course id = ‘CS-001¡®; delete from course
where course id = ‘CS-001¡®;
6. Using the university schema, write an SQL query to find the name and ID of those Accounting students advised by an instructor in the Physics department.
select student.ID, student.name
from student, advisor, instructor where student.ID = advisor.s_ID
and instructor.ID= advisor.i_iD
and student.dept_name= ‘Accounting’ and instructor.dept_name= ‘Physics’;
7. Using the university schema, write an SQL query to find the names of those departments whose budget is higher than that of Geology. List them in alphabetic order.
select X.dept_name
from department as X, department as H
where H.dept_name = ‘Geology’ and X.budget > H.budget order by X.dept_name;
Using the university schema, use SQL to do the following: For each student who has retaken a course at least twice (i.e., the student has taken the course at least three times), show the course ID and the student’s ID.
Please display your results in order of course ID and do not display duplicate rows.
select distinct ID, course_id from takes
group by ID, course_id having count(*) > 2
order by course_id; 10 rows
9. Using the university schema, write an SQL query to find the ID and title of each course in Psychology that has had at least one section with afternoon hours (i.e., ends at or after 12:00pm). (You should eliminate duplicates if any.)
select distinct course.course_id, course.title
from course, section, time_slot
where course.course_id = section.course_id
and section.time_slot_id = time_slot.time_slot_id and time_slot.end_hr >= 12
and course.dept_name = ‘Psychology’; 5 rows
10. Using the university schema, write an SQL query to find the number of students in each section. The result columns should appear in the order ¡°course_id, sec_id, year, semester, num¡±. You do not need to output sections with 0 students.
select course_id, sec_id, `year`, semester, count(*) as num from takes
group by course_id, sec_id, `year`, semester
11. Rewrite the query
from section natural join classroom
without using a natural join but instead using an inner join with a using condition
from section join classroom using (building, room number)
12. Write an SQL query using the university schema to find if there are students
who have never taken a course at the university. Do this using no subqueries and no set operations (use an outer join).
from student left outer join takes using (ID) where course_id is null
13. Create a view tot_credits (year, num_credits), that shows the total number of credits taken by all student in each year.
create view tot_credits(`year`, num_credits) as select `year`, sum(credits)
from takes natural join course
group by `year`;
select * from tot_credits;
14. Using the university schema, write an SQL query to find the ID and name of each instructor who has never given an A grade in any course she or he has taught. (Instructors who have never taught a course trivially satisfy this condition.)
select A.ID, A.`name` from instructor A where NOT EXISTS (select B.ID
from instructor B, teaches, takes
where A.ID = teaches.ID
and teaches.course_id = takes.course_id and teaches.`year` = takes.`year`
and teaches.semester= takes.semester and teaches.sec_id= takes.sec_id
and takes.grade = ‘A ‘);
19 rows. Note: Please make a note of A in last line is followed by a space, since grade has a datatype of varchar(2).
15. (bonus) Rewrite the preceding query, but also ensure that you include only instructors who have given at least one other non-null grade in some course
select distinct instructor.ID, instructor.name from instructor, teaches, takes
where instructor.ID = teaches.ID
and teaches.course_id = takes.course_id and teaches.year = takes.year
and teaches.semester = takes.semester and teaches.sec_id = takes.sec_id
and takes.grade is not null
and instructor.ID in (
select A.ID
from instructor A
where NOT EXISTS
(select B.ID
from instructor B, teaches, takes
where A.ID = teaches.ID
and teaches.course_id = takes.course_id and teaches.`year` = takes.`year`
and teaches.semester= takes.semester and teaches.sec_id= takes.sec_id
and takes.grade = ‘A ‘)
0 rows. Note: since the instructors who taught some courses and gave non-null grades, but never gave ¡®A¡¯, do not exist.