final output 2

SQL> SELECT s.*, a.*, d.* FROM stephensow1_final_Student s,
  2     stephensow1_final_Advisor a, stephensow1_final_Dept d
  3     WHERE STU_NUM = 123984 AND s.Dept_Code = d.Dept_Code
  4     AND s.Advisor_ID = a.Advisor_ID AND a.Dept_Code = d.Dept_Code;

STU_NU STU_LNAME        STU_MAJOR           STU_GPA  STU_HOURS STU_CLASS  ADVIS
------ ---------------- ---------------- ---------- ---------- ---------- -----
DEPT_ ADVIS ADVISOR_LNAME    ADVI ADVISOR_BLDG
----- ----- ---------------- ---- --------------------------------
ADVISOR_PHONE    DEPT_ DEPT_ DEPT_NAME                        DEPT_PHONE
---------------- ----- ----- -------------------------------- ----------------
COLLEGE_NAME
--------------------------------
123984 Thomas           CIT                     2.5         93 Senior     005
CS    005   Lewis            5132 Griffin
1603             CS    CS    Computer Science                 5234
Informatics


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

NUM_STUDENTS STU_MAJOR        COLLEGE_NAME
------------ ---------------- --------------------------------
           2 Marketing        Business Admin
           1 Statistics       Arts & Sciences
           2 BIS              Informatics
           2 Accounting       Business Admin
           1 CIT              Informatics

SQL>
SQL> SELECT d.Dept_Name, COUNT(CASE s.Stu_Class WHEN 'Freshman' then 1 else null end) AS Num_Fresh,
  2     COUNT(CASE s.Stu_Class WHEN 'Sophomore' then 1 else null end) AS Num_Soph,
  3     COUNT(CASE s.Stu_Class WHEN 'Junior' then 1 else null end) AS Num_Junior,
  4     COUNT(CASE s.Stu_Class WHEN 'Senior' then 1 else null end) AS Num_Senior
  5     FROM stephensow1_final_Student s, stephensow1_final_Dept d
  6     WHERE s.Dept_Code = d.Dept_Code
  7     GROUP BY Dept_Name;

DEPT_NAME                         NUM_FRESH   NUM_SOPH NUM_JUNIOR NUM_SENIOR
-------------------------------- ---------- ---------- ---------- ----------
Accounting                                0          1          1          0
Mathematics                               0          0          1          0
Computer Science                          0          0          0          1
Business Informatics                      0          0          2          0
Marketing                                 0          0          0          2

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

STU_NU STU_LNAME        STU_MAJOR        DEPT_NAME
------ ---------------- ---------------- --------------------------------
ADVISOR_LNAME
----------------
211343 Stephanos        Accounting       Accounting
Grastand


SQL>
SQL> SELECT s.Stu_LName, s.Stu_Major, a.Advisor_LName
  2     FROM stephensow1_final_Student s, stephensow1_final_Advisor a
  3     WHERE s.Advisor_ID = a.Advisor_ID;

STU_LNAME        STU_MAJOR        ADVISOR_LNAME
---------------- ---------------- ----------------
Stephanos        Accounting       Grastand
Smith            Accounting       Grastand
Jones            Marketing        Gentry
Ortiz            Marketing        Tillery
McKulski         Statistics       Chen
Thomas           CIT              Lewis
Thompson         BIS              Zang
Meyers           BIS              Goh

8 rows selected.

SQL>
SQL> SELECT d.College_Name, COUNT(CASE WHEN s.Stu_GPA >= 3.5 then 1 else null end) AS Num_DeansList
  2     FROM stephensow1_final_Student s, stephensow1_final_Dept d
  3     WHERE s.Dept_Code = d.Dept_Code
  4     GROUP BY College_Name;

COLLEGE_NAME                     NUM_DEANSLIST
-------------------------------- -------------
Business Admin                               1
Informatics                                  0
Arts & Sciences                              1