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

↑Jump back a section

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