!NF 282 Final 2

SELECT s.* a.* d.* FROM stephensow1_final_Student s,
    stephensow1_final_Advisor a, stephensow1_final_Dept d
    WHERE STU_NUM = 123984 AND s.Dept_Code = d.Dept_Code
    AND s.Advisor_ID = a.Advisor_ID AND a.Dept_Code = d.Dept_Code;

SELECT COUNT(s.Stu_Major) AS Num_Students, s.Stu_Major, d.College_Name
    FROM stephensow1_final_Student s, stephensow1_final_Dept d
    WHERE s.Dept_Code = d.Dept_Code
    GROUP BY Stu_Major, College_Name;

SELECT d.Dept_Name, COUNT(CASE s.Stu_Class WHEN 'Freshman' THEN 1 ELSE NULL END) AS Num_Fresh,
    COUNT(CASE s.Stu_Class WHEN 'Sophomore' THEN 1 ELSE NULL END) AS Num_Soph,
    COUNT(CASE s.Stu_Class WHEN 'Junior' THEN 1 ELSE NULL END) AS Num_Junior,
    COUNT(CASE s.Stu_Class WHEN 'Senior' THEN 1 ELSE NULL END) AS Num_Senior
    FROM stephensow1_final_Student s, stephensow1_final_Dept d
    WHERE s.Dept_Code = d.Dept_Code
    GROUP BY Dept_Name;

SELECT s.Stu_Num, s.Stu_LName, s.Stu_Major, d.Dept_Name, a.Advisor_LName
    FROM stephensow1_final_Student s, stephensow1_final_Dept d,
    stephensow1_final_Advisor a WHERE s.Dept_Code = d.Dept_Code
    AND s.Advisor_ID = a.Advisor_ID AND a.Dept_Code = d.Dept_Code
    AND s.Stu_GPA > 3.5 AND d.College_Name LIKE 'Business Admin'
    ORDER BY s.Stu_GPA ASC;

SELECT s.Stu_LName, s.Stu_Major, a.Advisor_LName
    FROM stephensow1_final_Student s, stephensow1_final_Advisor a
    WHERE s.Advisor_ID = a.Advisor_ID;

SELECT d.College_Name, COUNT(CASE WHEN s.Stu_GPA >= 3.5 THEN 1 ELSE NULL END) AS Num_DeansList
    FROM stephensow1_final_Student s, stephensow1_final_Dept d
    WHERE s.Dept_Code = d.Dept_Code
    GROUP BY College_Name;