MySQL/Language/Exercises

From Wikibooks, open books for an open world
< MySQL‎ | Language
Jump to: navigation, search

Practicing SELECT[edit]

Table `list`[edit]

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[edit]

  • 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[edit]

  • 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`[edit]

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[edit]

  • 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[edit]

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[edit]

Finding Duplicates[edit]

 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[edit]

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