fetching required data using joins in mysql -


my tables :

mysql> select * professor; +-------+--------+--------+--------+------+ | empid | name   | status | salary | age  | +-------+--------+--------+--------+------+ |     1 | arun   |      1 |   2000 |   23 | |     2 | benoy  |      0 |   3000 |   25 | |     3 | chacko |      1 |   1000 |   36 | |     4 | divin  |      0 |   5000 |   32 | |     5 | edwin  |      1 |   2500 |   55 | |     7 | george |      0 |   1500 |   46 | +-------+--------+--------+--------+------+ 6 rows in set (0.00 sec)  mysql> select * works; +----------+-------+---------+ | courseid | empid | classid | +----------+-------+---------+ |        1 |     1 |      10 | |        2 |     2 |       9 | |        3 |     3 |       8 | |        4 |     4 |      10 | |        5 |     5 |       9 | |        6 |     1 |       9 | |        2 |     3 |      10 | |        2 |     1 |       7 | |        4 |     2 |       6 | |        2 |     4 |       6 | |        2 |     5 |       2 | |        7 |     5 |       6 | |        3 |     5 |       2 | |        6 |     4 |      10 | +----------+-------+---------+ 14 rows in set (0.00 sec)  mysql> select * course; +----------+------------+--------+ | courseid | coursename | points | +----------+------------+--------+ |        1 | maths      |      5 | |        2 | science    |      1 | |        3 | english    |      6 | |        4 | social     |      4 | |        5 | malayalam  |     20 | |        6 | arts       |     25 | |        7 | biology    |     20 | +----------+------------+--------+ 7 rows in set (0.00 sec) 

question :

return name(s) of professor(s) taught number of courses in class 10

query tried :

select professor.name,works.courseid,works.empid,works.classid professor inner join works on professor.empid=works.empid works.classid=10 group works.courseid 

i know imcomplete/incorrect. pls me required result.

select      professor.name, count(works.courseid)     works inner join     professor on          professor.empid = works.empid      work.classid = 10 group     professor.name  order count(works.courseid) desc limit 1 

Comments

Popular posts from this blog

php - regexp cyrillic filename not matches -

c# - OpenXML hanging while writing elements -

sql - Select Query has unexpected multiple records (MS Access) -