Project Description: Please write SQL queries to update tables extract the follo
ID: 3726502 • Letter: P
Question
Project Description: Please write SQL queries to update tables extract the following information. Write your queries based on the database you developed in the previous project. For each question, write a SQL query and save it as a “sql" file using a different query name, for example, query1, query2, etc. Please execute these queries sequentially descriptioin (Not Null) Fundamentals of Computer Sys. 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 Spring 2018 Spring 2018 Spring 2018 Fall 2017 Fall 2017 Spring 2017 Spring 2017 4 grad uery 1Update the Courses table by adding another column as shown above uery 2 Retrieve all the courses (description) open in year 2018 uery 3 List the name(s) of all the graduate course(s) taught by Steven Garden uery 4 List the name(s) of instructor(s) who taught course(s) in Spring 2017 uery 5 Retrieve all the courses (as well as grades) Alice Wood has taken, ordered b Query 6 List the name(s) of the faculties who offer(s) more than one courses Query 7List the names and grades of the students who take the course "Fundamentals of Computer Svs.", ordered by gradesExplanation / Answer
Query 1:
Alter table courses add column semester varchar(20);
Query 2:
Select description from courses where semester like '%2018';
Query 3:
Select description from courses inner join faculties on courses.instructor = faculties.faculty_is where level = 'grad' and name = 'Steven Garden';
Query 4:
Select name from courses inner join faculties on courses.instructor = faculties.faculty_is where semster = 'Spring 2017';
Query 5:
Select description ,grade from courses inner join Enroll on Courses.course_id = Enroll.course_id inner join students on students.student_id = Enroll.student_id where students.name = 'Alice Wood' order by grade;
Query 6:
Select name from faculties inner join courses on faculties.faculty_id = courses.instructor group by name having count(course_id) > 1;
Query 7:
Select student.name ,grade from courses inner join Enroll on Courses.course_id = Enroll.course_id inner join students on students.student_id = Enroll.student_id wheredescription = 'Fundamentals of Computer Sys.' order by grade;
Query 8:
Select name from faculties inner join courses on faculties.faculty_id = courses.instructor group by name having level = 'grad' and year(date_of_birth) > 1979;
Query 9:
Select name from students inner join Enroll on Enroll.student_id = students.student_id inner join courses on courses.course_id = Enroll.course_id where grade = 'B' and level = 'ugrad';
Query 10:
Select name ,max(grade) from Students inner join Enroll on Students.student_id = Enroll.student_id group by Students.name order by name;
Query 11:
Select count(grade) from Enroll inner join Students on Enroll.student_id = Students.student_id inner join Courses on Courses.course_id = Enroll.course_id group by course_id having Students.name ='Alice Wood' order by grade;
Query 12:
Select name,datediff(yy,date_of_birth,getdate()) as Age from Students order by Age ;
Query 13:
Create Table title(
Instr int NOT NULL,
AP varchar(50),
Prof varchar(50),
primary key(Instr));
Related Questions
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.