# 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.
These two will work:

## Examples
### Remove duplicate entries

Assume the following table and data.
Note, the first two rows contains duplicates in columns a and b. It contains other duplicates; but, leaves the other duplicates alone.