--
--    Copyright (c) 2010 Software Gems Pty Ltd
--    Derek Asirvadem  04 Dec 10
--
-- Quick Tutorial on Subqueries/Scalars
--     
-- The SELECTs are a progression: 
--    Please complete each one, and understand it completely
--    before progressing to the next.
--
-- Tested in Sybase ASE 15.0.3, but downgraded for MySQL
--

CREATE TABLE Student (
    StudentId INT      NOT NULL,
    FirstName CHAR(30) NOT NULL,
    LastName  CHAR(30) NOT NULL, 
    CONSTRAINT U_PK
        PRIMARY KEY (StudentId)
    )

CREATE TABLE Course (
    CourseId INT      NOT NULL,
    Name     CHAR(30) NOT NULL, 
    CONSTRAINT U_PK
        PRIMARY KEY (CourseId)
    )

CREATE TABLE StudentCourse (
    StudentId INT      NOT NULL,
    CourseId  INT      NOT NULL,
    Mark      INT      NOT NULL,
        PRIMARY KEY (StudentId, CourseId),
    CONSTRAINT Student_StudCourse_fk 
        FOREIGN KEY (StudentId) 
        REFERENCES Student (StudentId),
    CONSTRAINT Course_StudCourse_fk  
        FOREIGN KEY (CourseId)  
        REFERENCES Course  (CourseId)
    )

INSERT INTO Student VALUES (01, "Fred"  , "Astaire")
INSERT INTO Student VALUES (02, "Ginger", "Rogers")
INSERT INTO Student VALUES (03, "Donald", "Duck")
INSERT INTO Student VALUES (04, "Daisy" , "Duck")
INSERT INTO Student VALUES (05, "Bugs"  , "Bunny")

INSERT INTO Course  VALUES (66, "Dancing/Ballroom")
INSERT INTO Course  VALUES (77, "Math/Algebra")
INSERT INTO Course  VALUES (88, "Art/Cartoon")

INSERT INTO StudentCourse VALUES (01, 66, 97)
INSERT INTO StudentCourse VALUES (02, 66, 98)
INSERT INTO StudentCourse VALUES (03, 66, 45)
INSERT INTO StudentCourse VALUES (04, 66, 55)
INSERT INTO StudentCourse VALUES (05, 66, 60)

INSERT INTO StudentCourse VALUES (01, 77, 66)
INSERT INTO StudentCourse VALUES (02, 77, 74)
INSERT INTO StudentCourse VALUES (03, 77, 80)
INSERT INTO StudentCourse VALUES (04, 77, 76)
INSERT INTO StudentCourse VALUES (05, 77, 78)

INSERT INTO StudentCourse VALUES (01, 88, 89)
INSERT INTO StudentCourse VALUES (02, 88, 65)
INSERT INTO StudentCourse VALUES (03, 88, 93)
INSERT INTO StudentCourse VALUES (04, 88, 94)
INSERT INTO StudentCourse VALUES (05, 88, 95)

--
-- 1. Straight Join
--
SELECT  Name,
        FirstName,
        LastName,
        Mark
    FROM Student       S,
         Course        C,
         StudentCourse SC
    WHERE SC.StudentId = S.StudentId
    AND   SC.CourseId  = C.CourseId
    ORDER BY 1, 4 DESC

--
-- 2. Simple Scalar Subquery for Course
--    Correlated Subquery
--    Substitute for Join
--
SELECT  (SELECT Name
            FROM  Course in_ner
            WHERE in_ner.CourseId = sc.CourseId
            ),
        FirstName,
        LastName,
        Mark
    FROM Student       S,
         StudentCourse SC
    WHERE SC.StudentId = S.StudentId
    ORDER BY 1, 4 DESC

--
-- 2.1. Simple Scalar Subquery for Course
--    Correlated Subquery
--    Substitute for Join
--    Fill in the two pairs of empty brackets with 
--    a simple subquery for Student
--
SELECT  (SELECT Name
            FROM  Course
            WHERE CourseId = SC.CourseId
            ) AS CourseName,
        () AS FirstName,
        () AS LastName,
        Mark
    FROM StudentCourse SC
    ORDER BY 1, 4 DESC

--
-- 3. Simple Scalar Subquery for Course
--    Correlated Subquery
--    Select a Scalar value for each row
--
SELECT  Name,
        FirstName,
        LastName,
        Mark,
        (SELECT MAX(Mark)
            FROM  StudentCourse in_ner
            WHERE in_ner.CourseId = SC.CourseId
            ) AS Highest
    FROM Student       S,
         Course        C,
         StudentCourse SC
    WHERE SC.StudentId = S.StudentId
    AND   SC.CourseId  = C.CourseId
    ORDER BY 1, 4 DESC

--
-- 4. Clever Scalar Subquery for Course
--    Correlated Subquery
--    Select a Scalar value for each row
--
SELECT  Name,
        (SELECT COUNT(*)
            FROM  StudentCourse in_ner
            WHERE in_ner.CourseId  = SC.CourseId
            AND   in_ner.Mark     >= SC.Mark
            ) AS Rank,
        FirstName,
        LastName,
        Mark
    FROM Student       S,
         Course        C,
         StudentCourse sc
    WHERE SC.StudentId = S.StudentId
    AND   SC.CourseId  = C.CourseId
    ORDER BY 1, 2