{[ promptMessage ]}

Bookmark it

{[ promptMessage ]}

Update workson set hours 50 where essn 999887777 and

Info iconThis preview shows pages 10–12. Sign up to view the full content.

View Full Document Right Arrow Icon
UPDATE WORKS_ON SET HOURS = '5.0' WHERE ESSN= '999887777' AND PNO= 10 8.16 Specify the following queries in SQL on the database schema of Figure 1.2. (a) Retrieve the names of all senior students majoring in 'COSC' (computer science). (b) Retrieve the names of all courses taught by professor King in 85 and 86.
Background image of page 10

Info iconThis preview has intentionally blurred sections. Sign up to view the full version.

View Full Document Right Arrow Icon
(c) For each section taught by professor King, retrieve the course number, semester, year, and number of students who took the section. (d) Retrieve the name and transcript of each senior student (Class=5) majoring in COSC. Transcript includes course name, course number, credit hours, semester, year, and grade for each course completed by the student. (e) Retrieve the names and major departments of all straight A students (students who have a grade of A in all their courses). (f) Retrieve the names and major departments of all students who do not have any grade of A in any of their courses. Answers: (a) SELECT Name FROM STUDENT WHERE Major='COSC' (b) SELECT CourseName FROM COURSE, SECTION WHERE COURSE.CourseNumber=SECTION.CourseNumber AND Instructor='King' AND (Year='85' OR Year='86') Another possible SQL query uses nesting as follows: SELECT CourseName FROM COURSE WHERE CourseNumber IN ( SELECT CourseNumber FROM SECTION WHERE Instructor='King' AND (Year='85' OR Year='86') ) (c) SELECT CourseNumber, Semester, Year, COUNT(*) FROM SECTION, GRADE_REPORT WHERE Instructor='King' AND SECTION.SectionIdentifier=GRADE_REPORT.SectionIdentifier GROUP BY CourseNumber, Semester, Year (d) SELECT Name, CourseName, C.CourseNumber, CreditHours, Semester, Year, Grade FROM STUDENT ST, COURSE C, SECTION S, GRADE_REPORT G WHERE Class=5 AND Major='COSC' AND ST.StudentNumber=G.StudentNumber AND G.SectionIdentifier=S.SectionIdentifier AND S.CourseNumber=C.CourseNumber (e) SELECT Name, Major FROM STUDENT WHERE NOT EXISTS ( SELECT * FROM GRADE_REPORT WHERE StudentNumber= STUDENT.StudentNumber AND NOT(Grade='A')) (f) SELECT Name, Major FROM STUDENT WHERE NOT EXISTS ( SELECT * FROM GRADE_REPORT WHERE StudentNumber= STUDENT.StudentNumber AND Grade='A' ) 8.17 Write SQL update statements to do the following on the database schema shown in Figure 1.2. (a) Insert a new student <'Johnson', 25, 1, 'MATH'> in the database. (b) Change the class of student 'Smith' to 2.
Background image of page 11
(c) Insert a new course <'Knowledge Engineering','COSC4390', 3,'COSC'>. (d) Delete the record for the student whose name is 'Smith' and student number is 17. Answers: (a) INSERT INTO STUDENT VALUES ('Johnson', 25, 1, 'MATH') (b) UPDATE STUDENT SET CLASS = 2 WHERE Name='Smith' (c) INSERT INTO COURSE VALUES ('Knowledge Engineering','COSC4390', 3,'COSC') (d) DELETE FROM STUDENT WHERE Name='Smith' AND StudentNumber=17 8.18 no answer provided
Background image of page 12
This is the end of the preview. Sign up to access the rest of the document.

{[ snackBarMessage ]}

Page10 / 12

UPDATE WORKSON SET HOURS 50 WHERE ESSN 999887777 AND...

This preview shows document pages 10 - 12. Sign up to view the full document.

View Full Document Right Arrow Icon bookmark
Ask a homework question - tutors are online