# MySQL/Language/Exercises

## Practicing SELECTEdit

### 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 - QuestionsEdit

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

• 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?
• Rearrange the list Alphabetically sorted.

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

 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 - QuestionsEdit

• 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?

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 AVG(math), AVG(physics), AVG(literature) 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);
```

## ExamplesEdit

### Finding DuplicatesEdit

``` 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 entriesEdit

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