-- ITEC 3335 Fall 2019 HW #2.

-- Q1. Show the first name, last name, major, minor and credits of every
-- student who has 30 or more credits.

SELECT DISTINCT fname, lname, major, minor, credits
FROM student
WHERE credits >= 30;

-- Q2. Show the code, name, and number of faculty of
-- every department in the following format.

SELECT DISTINCT deptCode AS code,
    deptName AS department,
    numFaculty AS `Number of faculty`
FROM department;

-- Q3. Show the first name, last name and major department name of 
-- every student minoring in ITEC in the following manner.
SELECT DISTINCT s.fname, s.lname,
    d.deptName AS major
FROM student AS s, department AS d
WHERE s.major = d.deptCode
AND s.minor = 'ITEC';

--  Q4. Show the student name and her advisor name of 
--  every student who has a faculty advisor from the department
--  'CSCI' or 'ITEC' in the following manner.
SELECT DISTINCT s.fname, s.lname,
    f.fname AS `faculty's first name`,
    f.lname AS `faculty's last name`
FROM student AS s, faculty AS f
WHERE s.advisor = f.facId
AND (f.deptCode = 'CSCI' or f.deptCode = 'ITEC');

-- Q5. Show the student name, her advisor name, her
-- major department name and credits of every student who
-- has completed 50 or less credits in the following manner.
SELECT DISTINCT s.fname, s.lname,
    f.fname AS `faculty's first name`,
    f.lname AS `faculty's last name`,
    d.deptName AS major,
    s.credits
FROM student AS s, faculty AS f, department AS d
WHERE s.advisor = f.facId
AND s.major = d.deptCode
AND s.credits <= 50;

-- Q6. Show the student id, together with the name, semester, year 
-- and grade of 'ITEC' course taken in the following manner.
SELECT DISTINCT e.stuId,
    co.name AS course,
    c.semester, c.year, e.grade
FROM enroll AS e, class AS c, course AS co
WHERE e.classId = c.classId
AND c.courseId = co.courseId
AND co.rubric = 'ITEC';