I'm making test application where users will input their select queries in input box and the application have to compare them if they are identical with "correct answer" query.

"Correct answer" query is in database, and the comparable select is inputed by user.

I read about EXCEPT and INTERSECT but they don't exist in MySQL.

For example I have this database structure: db pic

Question from the application:

  1. List the first name, last name and date of birth of all female students whose first name is Yana but their last name is not Taylor.

correct answer-

SELECT FIRSTNAME, LASTNAME, DATEOFBIRTH 
FROM PERSON 
JOIN STUDENTS ON PERSONID=STUDENTID
WHERE GENDER=’F’ AND FIRSTNAME=’YANA’ AND LASTNAME<>’TAYLOR’

I want to check that the returned result is identical. wrong answer will be MySQLquery for example with aliased columns:

SELECT FIRSTNAME as first_name, LASTNAME as last_name, DATEOFBIRTH as date_of_birth ....

or even like statement (if it returns more results):

WHERE GENDER=’F’ AND FIRSTNAME LIKE '%YANA%' AND LASTNAME<>'TAYLOR'

My question is how to compare results between two queries that:

  • Their returned row count is the same
  • Their column names are equal
  • It's from the same table
  • And maybe return some indication for true or false that they are identical?
  • If result is the same, you can hardly check the table just from data - someone might do select 'aaa' as FIRSTNAME, 'bbb' as LASTNAME ... UNION select ... - no table used but results are the same. For that you might need at least some simple parser. (Even using EXPLAIN on the query is not 100% imho, because EXPLAIN shows table aliases, but it would catch this counterexample at least). And comparing the data itself would be quite a challenge in sql - you might use some natural joins and similar stuff, but handling it in app code seems much simpler. – jkavalik Aug 25 '15 at 19:42
  • Why not give your students instructions that aliases and wild-cards are not to be used? – Vérace Aug 25 '15 at 19:52
  • @Vérace because this is just example question, there will be more different variations – mwebber Aug 25 '15 at 20:16
  • Will you be teaching about the other datatypes, such as YEAR and TINYINT? – Rick James Sep 6 '15 at 23:22

pt-table-checksum and its friends are very good at validating that the data is (or is not) the same.

Your Answer

 
discard

By clicking "Post Your Answer", you acknowledge that you have read our updated terms of service, privacy policy and cookie policy, and that your continued use of the website is subject to these policies.

Not the answer you're looking for? Browse other questions tagged or ask your own question.