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