I need help converting this query in english into SQL form and write run them on
ID: 3929401 • Letter: I
Question
I need help converting this query in english into SQL form and write run them on access. Underneath is the data that i am using for conversion.
Query 1
List the first and last names of all students that never got a C grade.
Students
StudentId
FirstName
LastName
Degree
001
Linus
Torvalds
BS
002
Jeff
Bezos
MS
003
Pierre
Omidyar
BS
004
Sergey
Brin
MS
005
Larry
Page
MS
006
Mark
Zuckerberg
BS
Transcripts
StudentId
Term
CourseId
Grade
001
Fall 2014
Math 200
C
001
Spring 2015
CS 300
B
002
Fall 2014
CS 200
A
002
Spring 2015
CS 300
B
002
Fall 2015
CS 400
003
Fall 2014
Math 200
A
003
Spring 2015
CS 300
B
003
Fall 2015
CS 200
004
Fall 2014
CS 400
A
004
Spring 2015
Phys 400
B
005
Spring 2015
Phys 400
B
005
Fall 2015
CS 400
006
Spring 2015
Math 200
C
006
Fall 2015
CS 200
Students
StudentId
FirstName
LastName
Degree
001
Linus
Torvalds
BS
002
Jeff
Bezos
MS
003
Pierre
Omidyar
BS
004
Sergey
Brin
MS
005
Larry
Page
MS
006
Mark
Zuckerberg
BS
Explanation / Answer
Please find the required query below:
SELECT s.FirstName
,s.LastName
FROM Students s
,Transcripts t
WHERE s.StudentId = t.StudentId
AND s.StudentId NOT IN (
SELECT StudentId
FROM Transcripts
WHERE Grade = 'C'
);
Here the sub-query will find all the students id who got grade C in any course. Thus we need to get the first and last names of the students who is not included in the sub-query result.
The su-query returns student ids : 001, 006
thus, the main query will becomes :
SELECT s.FirstName
,s.LastName
FROM Students s
,Transcripts t
WHERE s.StudentId = t.StudentId
AND s.StudentId NOT IN (001,006);
Results:
FirstName LastName Jeff Bezos Pierre Omidyar Sergey Brin Larry PageRelated Questions
drjack9650@gmail.com
Navigate
Integrity-first tutoring: explanations and feedback only — we do not complete graded work. Learn more.