Explore BrainMass
Share

A complex SQL query including string comparsion

This content was COPIED from BrainMass.com - View the original, and get the already-completed solution here!

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.

© BrainMass Inc. brainmass.com March 21, 2019, 7:18 pm ad1c9bdddf
https://brainmass.com/computer-science/pattern-matching/complex-sql-query-including-string-comparsion-287609

Solution Preview

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 = ...

Solution Summary

This solution explains a complex SQL query. The query has string comparison and Boolean expressions.

$2.19