Courses description (Not Null) Fundamentals of Computer Svs Software Engineering
ID: 3725846 • Letter: C
Question
Courses description (Not Null) Fundamentals of Computer Svs Software Engineering I Computer Programming I Introduction to Algorithms Operating Systems Software Design Advanced Database course id level instructor semester (PK) (Not Null) (FK) ugrad ugrad ugrad grad grad grad Spring 2018 Spring 2018 Spring 2018 Fall 2017 Fall 2017 Spring 2017 Spring 2017 4 uery 1Update the Courses table by adding another column as shown above Query 2Retrieve all the courses (description) open in year 2018 uerv 3 List the name(s) of all the graduate coursels) taught by Steven Garden Query 4List the name(s) of instructor(s) who taught course(s) in Spring 2017 uerv 5 Retrieve all the courses (as well as grades) Alice Wood has taken, ordered by grades Query 7List the names and grades of the students who take the course "Fundamentals of Query 8List the name(s) of the faculties who offer(s) at least one graduate course and is (are) Query9List the name(s) of the student(s) who get(s) grade B for at least one undergraduate Query 10List the name(s) of the student(s) and the highest grade for each student. (ASCII uerv 6 List the name(s) of the faculties who offer(s) more than one courses Computer Sys,", ordered by grades born after 1979 courseExplanation / Answer
Query1:
*. alter table courses add semester varchar2(25);
* update courses set semester='Spring 2018' where course_id in (1,2,3);
* update courses set semester='Fall 2017' where course_id in (4,5);
* update courses set semester='Spring 2017' where course_id in (6,7);
Query 2:
* select description from courses where semester='Spring 2018';
Query 3:
select description from courses c,faculties f where c.instructor=f.faculty_id and f.name='Steven Garden' and level='grad';
Query 4:
select name from courses c,faculties f where c.instructor=f.faculty_id and semester='Spring 2017';
Query 5:
select description,grade from students s,courses c,enroll e where s.student_id=e.student_id and c.course_id=e.course_id and s.name='Alice Wood' order by grade;
Query 6:
select f.name from faculties f,courses c where f.faculty_id=c.instructor group by f.faculty_id,f.name having count(*)>1;
Query 7:
select name,grade from students s,courses c,enroll e where s.student_id=e.student_id and c.course_id=e.course_id and c.description='Fundamentals of Computer Sys' order by grade;
Query 8:
select f.name from faculties f,courses c where f.faculty_id=c.instructor and to_char(date_of_birth,'yyyy')>1979 and level='grad' group by f.faculty_id,f.name having count(*)>=1;
Query 9:
select s.name from students s,courses c,enroll e where s.student_id=e.student_id and c.course_id=e.course_id and grade='B' and level='ugrad' group by s.student_id,s.name having count(*)>=1;
Query 10:
select distinct s.name from students s1 inner join enroll e1 on s1.student_id=e1.student_id where e1.grade=(select max(e2.grade) from students s2 inner join enroll e2 where s2.student_id=e2.student_id and s2.student_id=e1.student_id);
Query 11:
select count(grade) from students s,enroll e where s.student_id=e.student_id and name='Alice Wood' group by s.student_id;
Query 12:
select name,age from students order by age;
Query 13:
create table title(
name varchar2(30) primary key,
abbrevation varchar2(10)
);