2

I have Student and Course Table:

Student(Roll_no(primary), Name, Course_id(foreign))
Course(Course_no(primary), Course_name)

How will I retrieve:

a. The table with the details of Courses in which more than 2 students got admitted.

b. The Course table along with a count column.

What I tried for the first problem:

 SELECT * FROM Course WHERE(SELECT COUNT(*) FROM Student GROUP BY Course_no WHERE COUNT(*) > 2);

I'm not sure what I did, but that didn't work. I am new to Database System .

Sample: Student Table

    +---------+----------+----------+-----------+
    | Roll_no | Name     | Semester | Course_no |
    +---------+----------+----------+-----------+
    |       1 | a        |        3 |       101 |
    |       2 | b        |        5 |       101 |
    |       3 | c        |        3 |       101 |
    |       4 | c        |        3 |       101 |
    |       5 | d        |        3 |       101 |
    |       7 | b        |        4 |       102 |
    +---------+----------+----------+-----------+

Course Table

+-----------+-------------+
| Course_no | Course_name |
+-----------+-------------+
|       101 | BCA         |
|       102 | BSC         |
+-----------+-------------+

For the First Part I expect a table like:

+-----------+-------------+
| Course_no | Course_name |
+-----------+-------------+
|       101 | BCA         |
+-----------+-------------+

For the Second part I expect a table like 

+-----------+----------------------+
| Course_no | Course_name |Total   |
+-----------+-------------+--------+
|       101 | BCA         |5       |
|       102 | BSC         |1       |
+-----------+-------------+--------+
Raymond Nijland
  • 11,488
  • 2
  • 22
  • 34
gaufler
  • 165
  • 1
  • 9

1 Answers1

2

First Part:

You need to use JOIN and HAVING

SELECT c.Course_no,c.Course_name
FROM Course c 
INNER JOIN Student s on c.Course_no = s.Course_id
GROUP BY c.Course_no,c.Course_name
HAVING count(1) > 2

sqlfiddle:http://sqlfiddle.com/#!9/387386/3

Second part:

You need to JOIN and GROUP BY COUNT

SELECT c.Course_no,c.Course_name,count(1) 'totle'
FROM Course c 
INNER JOIN Student s on c.Course_no = s.Course_id
GROUP BY c.Course_no,c.Course_name

sqlfiddle:http://sqlfiddle.com/#!9/bad7e3/1

D-Shih
  • 44,943
  • 6
  • 31
  • 51
  • What does count(1) mean ? Is it similar to count(*) ? – gaufler Apr 28 '18 at 12:54
  • 1
    @rgo `count(1)` is the same with `COUNT(*)` on this question. – D-Shih Apr 28 '18 at 12:57
  • If It was an another number but 2 would still count(1) be same as count(*) for this case ? – gaufler Apr 28 '18 at 13:02
  • i disagree that `COUNT(1)` is the same as `COUNT(*)` https://stackoverflow.com/questions/2710621/count-vs-count1-vs-countpk-which-is-better/2710703#2710703 – Raymond Nijland Apr 28 '18 at 13:03
  • @RaymondNijland Thank for your refer,But I think the optimizer recognizes it for what it https://stackoverflow.com/questions/1221559/count-vs-count1-sql-server – D-Shih Apr 28 '18 at 13:07
  • That link you have provided is SQL Server not MySQL – Raymond Nijland Apr 28 '18 at 13:18
  • If I query till 'SELECT c.Course_no,c.Course_name FROM Course c INNER JOIN Student s on c.Course_no = s.Course_id GROUP BY c.Course_no,c.Course_name' Then I get the result as the course sample table. Is it that the size of each row is hidden as a temporary value ? After introducing the having clause I get the required result. How is count working here. – gaufler Apr 28 '18 at 13:18
  • @rgo `count` will counting after group by clause .You can take a look at Query order of execution.:) https://sqlbolt.com/lesson/select_queries_order_of_execution – D-Shih Apr 28 '18 at 15:02