MySQL/Language/Exercises

Practicing SELECT

Table `list`

ID	Name	Surname	FlatHave	FlatWant
1	Shantanu	Oak	Goregaon
2	Shantanu	Oak	Andheri
3	Shantanu	Oak		Dadar
4	Ram	Joshi		Goregaon
5	Shyam	Sharma		Andheri
6	Ram	Naik	Sion
7	Samir	Shah	Parle
8	Ram	Joshi	Dadar
9	Shyam	Sharma	Dadar

Exercise I - Questions

• Who has a flat in "Goreagon" and who wants to buy one?

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.

• Who has a flat in "Parle" and who wants to buy one?
• Where does "Shantanu Oak" own the flats and where does he want to buy one?
• How many entries have been recorded so far?
• How many flats are there for sale?
• What are the names of our clients?
• How many clients do we have?
• List the customers whose name start with "S"?
• Rearrange the list Alphabetically sorted.

Exercise I - Answers

• select * from list where FlatHave = "Goregaon" or FlatWant = "Goregaon";
• select * from list where FlatHave = "Parle" or FlatWant = "Parle";
• select * from list where Name = "Shantanu" and Surname = "Oak";
• select count(*) from list;
• select count(FlatHave) from list where FlatHave is not null;
• select distinct Name, Surname from list;
• select count(distinct Name, surname) from list;
• select * from list where Name like "S%";
• select Surname, Name, FlatHave, FlatWant from list order by Name;

Table `grades`

ID	Name	Math	Physics	Literature
1	John	68	37	54
2	Jim	96	89	92
3	Bill	65	12	57
4	Jeri	69	25	82

Exercise II - Questions

• A list of all students who scored over 90 on his or her math paper?
• A list of all students who scored more than 85 in all subjects?
• Declare Results: Print the results of all students with result column.
• Find out total marks of all the students.
• What are the average marks of the class for each subject?
• What are the minimum marks in Math?
• What are the maximum marks in Math?
• Who got the highest marks in Math?

Exercise II - Answers

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);

Examples

Finding Duplicates

 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

Remove duplicate entries

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);