Deptmaster (deptid,deptname, dlocation), coursemaster (deptid, courseid,coursename), strengthmaster (deptid, courseid, maxstudentallow), studentdetails (deptid, courseid, studno, studname);

Question

1) display the department and course where maximum students registered

2) select name, department and course of student whose names begin with 'A'

3) display department name of student having student number 1

Answer 1

Plain

Bash

C++

C#

CSS

Diff

HTML/XML

Java

Javascript

Markdown

PHP

Python

Ruby

SQL

--Q1

SELECT d.deptname, c.coursename

FROM deptmaster d

INNER JOIN coursemaster c

ON d.deptid = c.deptid

INNER JOIN strengthmaster s

ON d.deptid = s.deptid AND c.courseid = s.courseid

WHERE s.maxstudentallow = MAX(SELECT maxstudentallow FROM strengthmaster);

Please schedule a lesson for Q2 and Q3!

Answer 2

Explanation for the answer provided for Question #1 - display the department and course where maximum students registered

The question is asking for two fields to be displayed in the resultset - Department (table = deptmaster, field name = deptname) and Course (table = coursemaster, fieldname = coursename). However, the requirement for the Course field is to display the course with the max number of registered units (table = strenthmaster).

This data is being pulled from 3 tables - hence the joins to three tables. The WHERE statement (WHERE s.maxstudentallow = MAX(SELECT maxstudentallow FROM strengthmaster) is how the resultset is filtered to only list the course with the max number of students.

Responses for your other two questions are:

2) select name, department and course of student whose names begin with 'A'

SELECT sd.studname, d.deptname, c.coursename

FROM deptmaster d

INNER JOIN coursemaster c

ON d.deptid = c.deptid

INNER JOIN studentdetails sd

ON d.deptid = sd.deptid AND c.courseid = sd.courseid

WHERE SUBSTRING(studname,1,1) = 'A'

Explanation - Substring function arguments (Field Name, Start Position, Number of Characters to return). This will return Students in which the first character of the "studname" field starts with "A".

3) display department name of student having student number 1

SELECT d.deptname

INNER JOIN studentdetails sd

ON d.deptid = sd.deptid

WHERE studno = '1'