This question is ill posed and the listed answer isn't correct. 'and who wants to buy one?' Does this mean wants to buy a flat or wants to buy a flat in Goregaon (which by the way is misspelled in either the question or the table)? The answer is wrong because the question says "AND" and the answer says or. If the question was meant to ask for the names of the people who have a flat in Goregaon AND those who want to buy a flat in Goregaon, then the correct answer to this should be select name, surname from list where flathave="Goregaon" and flatwant="Goregaon"; If the question is meant to ask for names of those who either have OR want a flat in Goregaon, then it would be select name, surname from list where flathave="Goregaon" or flatwant="Goregaon"; If the question is meant to ask for those who have a flat in Goregaon and want to buy a flat, then then answer would be select name, surname from list where flathave="Goregaon" and flatwant<>""; Many of the questions below need revision as well, or the table needs preface information.
Note: many problems have more than one correct solution.
SELECT * FROM grades WHERE math > 90; SELECT name FROM grades WHERE math > 85 AND physics > 85 AND literature > 85; SELECT *, IF( (math <= 35 OR physics <= 35 OR literature <= 35), 'fail', 'pass') AS result FROM grades ORDER BY result DESC; SELECT name, math+physics+literature FROM grades; SELECT AVG(math), AVG(physics), AVG(literature) FROM grades; SELECT MIN(math) FROM grades; SELECT MAX(math) FROM grades; SELECT * FROM grades ORDER BY math DESC LIMIT 1 -- this is good if we have only one guy with top score. SELECT * FROM grades where math=max(math); -- the max function cannot be used after "where". Such usage results in "ERROR 1111 (HY000): Invalid use of group function"
These two will work:
SELECT name, maths FROM grades WHERE maths = (SELECT MAX(maths) from grades); SELECT name, maths FROM grades WHERE maths >= ALL (SELECT MAX(maths) from grades);
SELECT Vendor, ID, Count(1) as dupes FROM table_name GROUP BY Vendor, ID HAVING Count(1) >1 SELECT txt, COUNT(*) FROM dupes GROUP BY txt HAVING COUNT(*) > 1; SELECT id, COUNT( id ) AS cnt, FROM myTable GROUP BY id HAVING cnt > 1
Assume the following table and data.
CREATE TABLE IF NOT EXISTS dupTest (pkey int(11) NOT NULL auto_increment, a int, b int, c int, timeEnter timestamp(14), PRIMARY KEY (pkey)); insert into dupTest (a,b,c) values (1,2,3),(1,2,3),(1,5,4),(1,6,4);
Note, the first two rows contains duplicates in columns a and b. It contains other duplicates; but, leaves the other duplicates alone.
ALTER IGNORE TABLE dupTest ADD UNIQUE INDEX(a,b);
Manage research, learning and skills at NCR Works. Create an account using LinkedIn to manage and organize your omni-channel knowledge. NCR Works is like a shopping cart for information -- helping you to save, discuss and share.