select major
from cs1555.student;

-- Slide 6
INSERT INTO STUDENT (SID, Name)
VALUES (165, 'Susan Jones');

select major, count(class) As NoStudents
from cs1555.student
group by major
order by major;

-- Slide 12
SELECT SID AS Potatoes
FROM STUDENT;

-- Slide 22
SELECT FALSE;

-- slide 23 NULL oracle
SELECT SID,
       CASE
         WHEN Major IS NULL THEN 'undecided'
         WHEN Major = 'CS' THEN 'good choice'
         ELSE 'recruit'
         END AS Strategy
FROM STUDENT
WHERE CLASS = 2;


-- 26 and after 47
SELECT S.SID, MAX(Grade) AS LG
FROM STUDENT S
       JOIN ENROLL E on S.sid = E.sid
WHERE S.Major = 'CS'
GROUP BY S.SID;

-- 33
  (SELECT SID FROM STUDENT WHERE Major = 'CS')
  UNION
  (SELECT SID FROM STUDENT WHERE Major = 'Math');

  (SELECT SID FROM STUDENT)
  EXCEPT
  (SELECT SID FROM STUDENT WHERE Major = 'Math');


--34
SELECT SID || ':' || name as IDNAME
FROM STUDENT;

--37
SELECT Name
FROM STUDENT
WHERE name LIKE E'____an&%';

--43
SELECT S.SID,
       (SELECT DISTINCT MAX(Grade)
        FROM ENROLL E
        WHERE E.SID = S.SID) AS LG
FROM STUDENT S
WHERE S.Major = 'CS';

--48
SELECT SID
FROM STUDENT
WHERE Major IN ('CS');


--48
SELECT SID
FROM STUDENT
WHERE Major IN ('CS');

--50
SELECT S.SID
FROM STUDENT S
WHERE NOT EXISTS(SELECT S.SID
                 FROM ((SELECT SID
                        FROM STUDENT
                        WHERE Major = 'CS')
                       UNION ALL
                       (SELECT SID FROM STUDENT WHERE Major = 'Math')
                      ) A
                 WHERE S.SID = SID
                 GROUP BY S.SID
                 HAVING count(*) > 1
  );

--53
SELECT S.SID,
       (1 + (SELECT COUNT(*)
             FROM ENROLL E
             WHERE E.CID = 'CS1555'
               AND E.grade > S.grade)
         )
         AS
         Rank
FROM ENROLL S
WHERE S.CID = 'CS1555';

--57
SELECT *
FROM STUDENT
  FETCH FIRST 5 ROWS ONLY;


--59
SELECT *
FROM enroll
ORDER BY grade ASC
  OFFSET 3 ROW
  FETCH NEXT 3 ROWS ONLY;

-- 61
SELECT *
FROM (
       SELECT *,
              ROW_NUMBER() OVER (ORDER BY sid)
       FROM cs1555.student
     ) x
WHERE ROW_NUMBER <= 3;

--67
UPDATE STUDENT
SET Name ='Kathy Jones'
WHERE SID = 165;

