connect 'jdbc:derby://localhost:1527/studentsDB;create=true;user=manager;password=managerPWD;';

-- by Dr. Alvaro Monge
-- Solutions to Assignment #5 Fall-2006, 
-- SQL statements that retrieve the requested information

--   1. List the e-mail address (i.e. username or handle) 
--      of every [pick a major in your DB here, e.g.: computer science] major

SELECT rtrim(aHandle) || '@csulb.edu' AS "e-mail Address"
  FROM majors INNER JOIN majorsChosen ON mCode=majorCode
              INNER JOIN accounts     ON studentID=aOwnerID
 WHERE mName = 'Computer Science';

-- NOTE: there is no direct relationship between majorsChosen and accounts
--   The statement above uses string concatenation 
--   operator || and also the rtrim() function




--   2. List the names of students who have chosen a major requiring at least 125 units


SELECT sFirstName, sLastName
  FROM majors INNER JOIN majorsChosen ON mCode=majorCode
              INNER JOIN students     ON studentID=SID
 WHERE mUnitsRequired >= 125;
 
 


--   3. Which major(s) has Peter Parker [or pick the full name of one of your students] chosen?




SELECT mCode, mName
  FROM majors INNER JOIN majorsChosen ON mCode=majorCode
              INNER JOIN students     ON studentID=SID
 WHERE sFirstName = 'Peter' AND sLastName = 'Parker';




--   4. List the SID and full name of students who have no accounts
--      To retrieve the necessary tuples, we'll need to use set difference




SELECT SID, sFirstName, sLastName
  FROM students
EXCEPT
SELECT SID, sFirstName, sLastName
  FROM students INNER JOIN accounts ON SID=aOwnerID;


--   5. List the SID, full name, and address of students who have not chosen a major.
--      Again, set difference is needed

SELECT SID, sFirstName, sLastName, sStreetAddress, sCity, sState, sZipCode
  FROM students
EXCEPT
SELECT SID, sFirstName, sLastName, sStreetAddress, sCity, sState, sZipCode
  FROM students INNER JOIN majorsChosen ON studentID=SID;


--   6. [Think about this.... it's not part of the assignment!] 
--   List the SID and full name of students who have chosen more than one major 

-- To be discussed at a later date


disconnect;
exit;