FACULTY STUDENT SCHEDULES FACULTY CLASSES HH ClassID Title S

FACULTY STUDENT SCHEDULES FACULTY CLASSES HH ClassID Title StaffID CPK classstatus FX P01 Status Tenured StartDate StartTime STUDENTS FACULTY SUBJECTS Monday schedule StudentiD StaffID STAFF CPK ProficiencyRating Wednesday Schedule SittFirstName Friday schedule SttCity SUBJECTS CLASSROOMS SttZipCode StudBirth Date n CategoryID FK Building Code FK SubiectPreReq STUDENTCLASS STATUS SubjoctEstClassSize BUILDINGS CATEGORIES BuldingCode PK MAJORS DevatorAccess FACULTY CATEGORIE DEPARTMENTS 1. Show all faculty information. last name. 2. Show student major, student first name and student 3. Count all students. highest number of students 4. show all information of class(es) has FLV Player

Solution

Question-1(Faculty Information):-

----------------------------------------------

Result : First Name , Last Name , Title, Status,Tentured

select s.StfFirstName,s.StfLastName,f.Title,f.status,f.Tentured from Faculty f , Staff s where s.StaffID = f.StaffID

Question-2(Show Student Major , Student First NAme and Student Last NAme):-

-------------------------------------------------------------------------------------------------------------

select s.StudFirstName, s.StudLastName,m.Major from Students s ,Majors m where s.StudentMajor = m.MajorId

Question-3(Count all Students):-

---------------------------------------------

select count(*) as Count from Students

Question-4(show all information about classess that have highest number of students):-

-------------------------------------------------------------------------------------------------------------------

select c.CalssID,s.SubjectName,count(*) as Total_Students from

Calsses c , Subjects s, Student_Schedules ss, Student s

where c.SubjectID = s.SubjectId and ss.CalssID = c.CalssID and ss.StudentId = s.StudentID

group by c.CalssID,s.SubjectName

having count(*) >= (

                                     select count(*) as Total from Calsses c , Student_Schedules ss, Student s

                                     where ss.CalssID = c.CalssID and ss.StudentId = s.StudentID

                                      group by c.CalssID order by Total DESC LIMIT 1)

Question-4(show number of students for each major):-

-------------------------------------------------------------------------------------------------------------------

select m.MajorID,m.Major , count(*) as Total_Students

from Majors m , Students s

where s.StudentMajor = m.MajorId

group by m.MajorID,m.Major

Question-7(Both Staff and Faculty Title , First NAme ...):-

-----------------------------------------------------------------

select f.Title,s.StfFirstName,s.StfLastName,s.Salary from Faculty f , Staff s where s.StaffID = f.StaffID

Question-8(Subject and Number of classes in each Subject):-

-----------------------------------------------------------------------------------------------------

select s.SubjectId,s.SubjectName,count(*) Total_Calsses from Subjects s , Calsses c

where c.SubjectId = s.SubjectID

group by s.SubjectId,s.SubjectName

Question-12(Find Classes with no students):-

--------------------------------------------------------------------

select c.ClassId from Classes c, Student_Schedules ss, Students s

where ss.ClassId = c.ClassID and ss.StudentID = s.StudentID

group by c.ClassId

having count(*) =0