Project Description: Please write SQL queries to update tables extract the follo

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 grades

Explanation / Answer

ScreenShot

----------------------------------------------------------------------------------------------------------------------------------------------------------

Quries:-

--Query 1  

Alter table query use to add a column in the existing table
ALTER TABLE dbo.Courses ADD semester VARCHAR(50) NOT NULL  

------------------------------------------------------------------------------------------------------------------------------

--Query 2    (select the courses in the year 2018)
Select description from dbo.Courses where Semester like '%2018'   

---------------------------------------------------------------------------------------------------------------------------------

--Query 3( select courses taught by Steven using join of course,instructor and faculties tables)
Select Courses.Description from dbo.Courses INNER JOIN dbo.Faculties               
ON Courses.Instructor = Faculties.faculty_Id Where Faculties.name = 'Steven Garden' and Courses.level = 'grad'  

-----------------------------------------------------------------------------------------------------------------------------------------------------------

--Query 4    (to get instructors name in 2017 spring by joining faculties and courses)
Select faculties.Name from dbo.faculties INNER JOIN dbo.Courses ON faculties.faculty_id = Courses.Instructor               
   Where Courses.semester = 'Spring 2017'          

------------------------------------------------------------------------------------------------------------------------------------------------------------

--Query 5  
Select Courses.Description, Enroll.Grade               
   from dbo.Courses INNER JOIN dbo.Enroll ON Courses.Course_id = Enroll.Course_ID              
   INNER JOIN dbo.Students ON Enroll.Student_Id = Students.student_id              
   where Students.name = 'Alice Wood' Order by 2           

-------------------------------------------------------------------------------------------------------------------------------------

--Query 6  
Select Max(faculties.Name) As Name from dbo.Faculties INNER JOIN Courses on Faculties.faculty_id = Courses.Instructor               
   group by Courses.Instructor having Count(Courses.Instructor)>1   

------------------------------------------------------------------------------------------------------------------------------------------------

--Query 7  
Select Students.name, Enroll.Grade               
   from dbo.Students INNER JOIN dbo.Enroll ON Enroll.Student_Id = Students.student_id              
   INNER JOIN dbo.Courses ON Courses.Course_id = Enroll.Course_ID              
   where Courses.description = 'Fundamentals of Computer Sys.' Order by 2              

------------------------------------------------------------------------------------------------------------------------------------------------------------
--Query 8  
Select Max(faculties.Name) As Name from dbo.Faculties INNER JOIN Courses on Faculties.faculty_id = Courses.Instructor               
   where Courses.level = 'grad' and Year(faculties.date_of_birth) > 1979              
   group by Courses.Instructor              

-------------------------------------------------------------------------------------------------------------------------------------
--Query 9  
Select Students.name              
   from dbo.Students inner join dbo.Enroll on enroll.student_id = students.student_id              
   inner join dbo.courses on courses.course_id = enroll.course_id              
   where enroll.grade = 'B'              

-----------------------------------------------------------------------------------------------------------------------------------------------------------

Note:-

I am using SQL server2017

space restriction issue to upload screenshots

if any queries, let me know
       
       

           

Related Questions

Navigate

• Browse (All)
• Browse P
• Subjects

---

---

Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.