This is a query with an idea like in MySQL count multiple columns and sum the total occurrence
SELECT
drawno,
SUM(CASE WHEN FIRST = 0 THEN 1 ELSE 0 END) zero,
SUM(CASE WHEN FIRST = 1 THEN 1 ELSE 0 END) one,
SUM(CASE WHEN FIRST = 2 THEN 1 ELSE 0 END) two,
SUM(CASE WHEN FIRST = 3 THEN 1 ELSE 0 END) three,
SUM(CASE WHEN FIRST = 4 THEN 1 ELSE 0 END) four,
SUM(CASE WHEN FIRST = 5 THEN 1 ELSE 0 END) five,
SUM(CASE WHEN FIRST = 6 THEN 1 ELSE 0 END) six,
SUM(CASE WHEN FIRST = 7 THEN 1 ELSE 0 END) seven,
SUM(CASE WHEN FIRST = 8 THEN 1 ELSE 0 END) eight,
SUM(CASE WHEN FIRST = 9 THEN 1 ELSE 0 END) nine
FROM
(
SELECT
first
,drawno
FROM table1
UNION ALL
SELECT
second
,drawno
FROM table1
UNION ALL
SELECT
third
,drawno
FROM table1
UNION ALL
SELECT
fourth
,drawno
FROM table1
) AS t GROUP BY drawno;
Two queries, this and from first answer:
http://sqlfiddle.com/#!2/2eb93d/2
You already found the answer to this problem. It all has to do with the order the grants appear.
Let's go back to your first grants display
+----------------------------------------------------------------------------------------------------------------+
| Grants for user@% |
+----------------------------------------------------------------------------------------------------------------+
| GRANT USAGE ON *.* TO 'user'@'%' IDENTIFIED BY PASSWORD '*XXXXXXXXXXXXXXXXXXXXXX' |
| GRANT SELECT ON `schema_%`.* TO 'user'@'%'; |
| GRANT SELECT, INSERT, UPDATE, DELETE ON `schema_user`.* TO 'user'@'%' |
+----------------------------------------------------------------------------------------------------------------+
Here is how the grants are stored:
- Information from
GRANT USAGE is stored in mysql.user
- Information from the other two lines are stored in
mysql.db
To see this order, please run this query:
SELECT * FROM mysql.db WHERE db='schema_user' AND user='user'\G
This will display those grants in whatever order it was entered.
When you deleted the grants and reinserted them using
REVOKE SELECT ON `schema_%`.* FROM user;
GRANT SELECT ON `schema_%`.* TO user;
This reverses the order in the mysql.db table. To prove that, just rerun
SELECT * FROM mysql.db WHERE db='schema_user' AND user='user'\G
after doing the REVOKE and GRANT.
UPDATE 2017-08-02 13:20 EDT
In your comment, you said
I didn't knew of the mysql.db existence... But yes, it looks like it's the order; I suspected that already; but is this the expected behaviour? I would expect MySQL to "merge" the permissions so I didn't have to worry about order, since the grants are made by a SP automatically in here.
The problem here is MySQL will not merge them. For the sake of continuity, you should avoid using wildcards at the database level if your want to manage the grants of a single user. In that instance, you will have to manage the grants of every single user the same way.
Best Answer
NOTE: I have no idea what 'GATE Mock Test Paper' is, or if that's supposed to have a bearing on the answer, so fwiw ...
As others have pointed out, technically all of the answers could be true ... though this will depend on the RDBMS (ie, flavor of SQL) as well as any configs/settings.
If the intention is to pick answers that are ANSI compliant, then I'd say 'III' is the correct answer since all non-aggregates should be part of the
group by. Then again, if you start looking at some of the nitty-gritty ANSI details you'll find this requirement can sometimes be relaxed.As you'll find out, most RDBMS products don't enforce such a strict standard: all non-aggregates being a member of the
group byclauseAs Michael Kutz has mentioned, without 'A' and/or 'B' in the select/projection list, answers 'I' and 'II' will provide some confusing answers. [And if you ever find yourself working in a real world RDBMS environment you'll find developers writing these types of queries all the time, but then not being able to describe what it is they're asking for let alone being able to explain the results they end up with.]
Again, answer 'III' is the only one I'd say can be clearly explained ... what I'm looking for ... the results I end up with.