I have written the following query:
SELECT FirstName, LastName,(0.8*(Quiz1+Quiz2+Quiz3)+MidTermPoints+1.2*FinalExam) as Score
FROM Students, QuizScores, TestScores
WHERE Students.StudentID = QuizScores.StudentID AND Students.StudentID = TestScores.StudentID AND QuizScores.StudentID = TestScores.StudentID AND Major LIKE '%A%' OR '%C%'
ORDER BY Score DESC
This is suppose to indicate the first name, last name, and score of the course of students who have a major that contains an 'A' or 'C' in it in descending order. I was required to combine three tables (students, quizscores, and testscore) to get the answer. I did something wrong, and not all students with 'A' or 'C' in their majors is showing up when I input my queries into the SQL program.
The condition has two parts. The first part links all of the tables based on StudentID. The second part checks for those students with a Major that matches the given pattern.
The first part can be done with the following:
(Students.StudentID = ...
This solution explains a complex SQL query. The query has string comparison and Boolean expressions.