I don't get the logic for the query 3 as below, and hope someone could give me some idea. For the query 3, SELECT ID, NAME, AGE, SALARY FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY < 20000);
The sub-query would find out the result where the salary < 20000 first, and that is what query2 shown as below. And then the parent query would find out the result where using all the age's record from the table COMPANY(total of 7 record: 18,19,22,23,24,29,37) to compare with the age's result from sub-query(total of 4 record: 18,19,23,29) and then show the greater record based on age.
I expect the result should only show the ID 7 only like below, since only this record is met the condition. The greater age from the result of sub-query(query 2) is 29, so only this record the age is over 29.
ID NAME AGE SALARY
7 Vicky 37 32500.0
Unfortunately my expectation is not met, and it show me the result like query 3 as below.
I hope to understand the logic how its work for query 3, and hope someone could assist.
1.sqlite> SELECT ID, NAME, AGE, SALARY FROM COMPANY;
ID NAME AGE SALARY
1 John 24 21000.0
2 Davy 22 20000.0
3 Kenny 19 9700.0
4 Henry 23 13555.0
5 Sam 18 17000.0
6 Ray 29 8000.0
7 Vicky 37 32500.0
2.sqlite> SELECT ID, NAME, AGE, SALARY FROM COMPANY WHERE SALARY < 20000;
ID NAME AGE SALARY
3 Kenny 19 9700.0
4 Henry 23 13555.0
5 Sam 18 17000.0
6 Ray 29 8000.0
3.sqlite> SELECT ID, NAME, AGE, SALARY FROM COMPANY WHERE AGE > (SELECT AGE FROM COMPANY WHERE SALARY < 20000);
ID NAME AGE SALARY
1 John 24 21000.0
2 Davy 22 20000.0
4 Henry 23 13555.0
6 Ray 29 8000.0
7 Vicky 37 32500.0
Aucun commentaire:
Enregistrer un commentaire