2

I have 3 tables:

Student (Id, Name, Country) 
Course (CrsCode, CrsName, Type, Instructor) 
Results(Id, CrsCode, Grade)

I have to solve below q's by using SQL query. Id and CrsCode are key fields in every table. The Type field specifies the course type, e.g. MATH, STAT, SYSC, TTMG, ELEC, etc.

  1. Find the Id of students who take TTMG or SYSC course.
  2. Find the Id of students who take every course.
  3. Find the id of students who take every TTMG course or every SYSC course.

Below are the sample data for part 3. 1st image is the Course Table and 2nd image is the Results table

enter image description here

enter image description here

I am able to solve the 1st question by using the following SQL query:

SELECT R.ID FROM RESULTS R JOIN COURSE C ON C.CRSCODE = R.CRSCODE WHERE C.TYPE="TTMG" OR C.TYPE ='SYSC

For the 2nd question, I believe we have to again relate 2nd (Course) and 3rd (Results) table in order to get the result. We have to relate a specific case here. We have to consider a case that there is one specific student who is taking all courses(CrsCode) and we have to find the id of that student.

I believe the query will still be the same as in question 1 but this time there will be little bit changes:

SELECT R.ID FROM RESULTS R JOIN COURSE C

I am not including anything after COURSE C because I am not sure the answer after that. Any pointers will be highly appreciated.

1 Answers1

1

Find the Id of students who take every course.

Assuming course table contains all the courses a student can take, you can group by the id column in the results table and check if the count is equal to the row count in course table. 

SELECT ID
FROM RESULTS
GROUP BY ID
HAVING COUNT(DISTINCT CRSCODE) = (SELECT COUNT(*) FROM COURSE)

Edit: Based on OP's comment

Find the id of students who take every TTMG or SYSC course

SELECT r.id
FROM  course c
JOIN RESULTS r on c.CRSCODE=r.CRSCODE
GROUP BY r.ID 
HAVING COUNT(case when type = 'SYSC' then r.CRSCODE end)  = (SELECT COUNT(*) FROM COURSE WHERE type = 'SYSC')
OR COUNT(case when type = 'TTMG' then r.CRSCODE end) = (SELECT COUNT(*) FROM COURSE WHERE type = 'TTMG')

Sample Demo

Vamsi Prabhala
  • 48,685
  • 4
  • 36
  • 58
  • It worked. How about if we dont use GROUP BY ID ? I tried by using `SELECT ID FROM RESULTS HAVING COUNT(DISTINCT CRSCODE) = (SELECT COUNT(*) FROM COURSE)` and it worked. –  Jan 08 '17 at 01:31
  • 1
    no, although mysql allows you to do it..you should `group by` id or you would get one arbitrary id..in your case since you only have 1 such student it may seem right, but try inserting one more such student and you will see the difference. – Vamsi Prabhala Jan 08 '17 at 01:44
  • I have one other question which is little bit related to the question which I have answered at the top. Suppose if we have to `find the id of students who take every TTMG or SYSC course` then how will we proceed ? I have answered `find the id of students who take TTMG or SYSC course`. In it, If the student takes TTMG or SYSC course then we simply use `WHERE C.TYPE="TTMG" OR C.TYPE ='SYSC.` What will be the equation if any student takes every TTMG or SYSC course ? –  Jan 08 '17 at 03:58
  • Suppose there are `3 tables: Student(Id, Name, Country) Course(CrsCode, CrsName, Type, Instructor) Results(Id, CrsCode, Grade)`. We have to `find the id of students who take every TTMG or SYSC course` –  Jan 08 '17 at 04:21
  • Suppose there are `5 courses offered in SYSC and 6 courses offered in TTMG` then we have to `find the id of students who take every TTMG course or take every SYSC course`. –  Jan 08 '17 at 04:36
  • I was going through this question again. `Find the id of students who take every TTMG course or take every SYSC course` I tried in my own way. I was able to get following soln. This query finds the id of those students who are taking every SYSC course `SELECT ID FROM Results GROUP BY ID HAVING COUNT(CrsCode) = (SELECT COUNT(*) FROM COURSE WHERE TYPE = 'SYSC');` And this query finds the id of those students who take every TTMG course `SELECT ID FROM Results GROUP BY ID HAVING COUNT(CrsCode) = (SELECT COUNT(*) FROM COURSE WHERE TYPE = 'TTMG');` –  Jan 09 '17 at 20:48
  • My question is `How can we merge both of these queries?` –  Jan 09 '17 at 20:48
  • I am posting this question separately as well so that everyone can benefit. –  Jan 09 '17 at 21:04
  • @ In this way `SELECT ID FROM Results GROUP BY ID HAVING COUNT(CrsCode) = (SELECT COUNT(*) FROM COURSE WHERE TYPE = 'TTMG' or TYPE= 'SYSC');` ? I tried in this way but there is no output. –  Jan 09 '17 at 21:10
  • no..i showed it clearly in the answer..`SELECT ID FROM RESULTS GROUP BY ID HAVING COUNT(CASE WHEN TYPE = 'SYSC' THEN CRSCODE END) = (SELECT COUNT(*) FROM COURSE WHERE CRSCODE = 'SYSC') OR COUNT(CASE WHEN TYPE = 'TTMG' THEN CRSCODE END) = (SELECT COUNT(*) FROM COURSE WHERE CRSCODE = 'TTMG')` – Vamsi Prabhala Jan 09 '17 at 21:11
  • I am receiving the following for the above query. `Error Code: 1054. Unknown column 'TYPE' in 'having clause' 0.000 sec` –  Jan 09 '17 at 21:20
  • i see..try the edit.. the column names were misplaced..i can post the exact answer if you show some sample data (not only the column names) from all the tables involved. – Vamsi Prabhala Jan 09 '17 at 21:25
  • below are the sample data: `Course` `CrsCode CrsName Type Instructor` 2001 Data Structure TTMG Sachin 2002 Mathematics TTMG Sanjeev 2003 Algorithm TTMG Sourav 2004 Database SYSC Sameer 2005 Electrical SYSC Ramesh `Results` `Id CrsCode Grade` 1 2001 A 1 2002 B 1 2003 C 2 2004 D 2 2005 E I run the query provided by you. The o/p is still blank. –  Jan 09 '17 at 21:51
  • I have edited the question. You can check the sample data. 1st image is the Course table and 2nd image is the Results table. Technically, the answer will be id 1 and 2 fom the results table as these 2 guys are taking every course from SYSC and TTMG departments. –  Jan 09 '17 at 22:03