<?php
    
-- 1. ข้อมูลชั้นปีนักศึกษาที่มากกว่า 4
-- qry 1 test 22/10/2554
    SELECT *
    FROM
    (
        SELECT 
        studentId
        ,acadYear
        ,semester
        ,studentStatus
        ,GPA
        ,GPAX
        ,passStatus
        , (
            SELECT count( studentId ) +1 AS npass
            FROM bnct_reg.`StudentStatus` ss2
            WHERE ss.studentId = ss2.studentId
                AND (
                    ss2.acadYear < ss.acadYear
                OR (
                    ss2.acadYear = ss.acadYear
                AND ss2.semester < ss.semester
                    )
                )
                AND ss2.passStatus
                IN ( 1, 2, 3 )
            ) AS sy
        FROM bnct_reg.`StudentStatus` ss
        WHERE 1 =1
        ORDER BY ss.studentId
    )ss
    WHERE sy > 4
-- result qry 1 : 0 row

--  2 - ค้นหาแต่ละนักศึกษา แต่ละปี มีการประมวลผลผ่านมากกว่า 1 ครั้ง
--  qry 2 test 22/10/2554
        select * from (
            SELECT studentId,acadYear
            ,SUM(case when ss2.passStatus IN ( 1, 2, 3 ) then 1 else 0 end ) as pass
            FROM bnct_reg.`StudentStatus` ss2
            GROUP BY studentId,acadYear
        )ss where ss.pass > 1
-- result qry 2 : 1 row
    studentId   acadYear    pass
    143         2548        2

    -- extra ค้นหา แต่ละนักศึกษา แต่ละปี ไม่มีการประมวลผลผ่าน
--  qry 2.1 test 
-- pass studentStatus = 4
        select * from (
            SELECT studentId,acadYear
            ,SUM(case when ss2.passStatus IN ( 1, 2, 3 ) then 1 else 0 end ) as pass
            ,SUM(case when ss2.passStatus > 3 then 1 else 0 end ) as more
            FROM bnct_reg.`StudentStatus` ss2
            WHERE (studentId,acadYear) NOT IN
                (SELECT studentId,acadYear
                FROM bnct_reg.`StudentStatus` 
                where studentStatus > 2 OR GPA=0 group by studentId,acadYear
                )
            GROUP BY studentId,acadYear
        )ss where ss.pass = 0 AND ss.more != 0

-- result qry 2.1 : ? row
    studentId acadYear pass passStatus>3
    357     2551    0   1
    402     2551    0   1
    415     2551    0   1

-- -----------------------------------

-- 3 - ค้นหา อาจารย์ ประจำและอาจารย์พิเศษ
-- จากข้อมูล สันนิษฐาน ว่ารหัสอาจารย์ประจำ=291... และอาจารย์พิเศษ=294...(แต่ไม่สนใจ)
--  qry 3 test 22/10/2554
    select officerId,officerCode,officerName,officerSurname,ofSitId
    from bnct_reg.Officer 
    where ofSitId = 1
    
-- result qry 3 : 216 row ( ofSitId = 1 : 66 row , ofSitId = 2 : 150 row )

    -- 3.1 - ค้นหาข้อมูลบุคลากร ที่ชื่อสกุลซ้ำกัน 
    --  qry 3.1 test 28/10/2554
    select *
    from
    (
    SELECT     `officerId`,officerName,`officerSurname` FROM `Officer` WHERE 1
    )of1 inner join (
    SELECT     `officerId`,officerName,`officerSurname` FROM `Officer` WHERE 1
    )of2 on  of1.officerId != of2.officerId AND of1.officerName = of2.officerName AND of1.officerSurname= of2.officerSurname
    -- result qry 3.1 : 2 row
officerId     officerCode     officerName     officerSurname     officerId     officerCode     officerName     officerSurname
    175     2945172     นรีเวช     รพ.วชิระภูเก็ต     112     2945101     นรีเวช     รพ.วชิระภูเก็ต
    112     2945101     นรีเวช     รพ.วชิระภูเก็ต     175     2945172     นรีเวช     รพ.วชิระภูเก็ต
-- -----------------------------------

-- 4 - ค้นหา อาคาร ห้อง 
--  qry 4 test 22/10/2554
    SELECT *
    FROM bnct_reg.Room rm
    INNER JOIN bnct_reg.Building bd ON bd.buildingId = rm.buildingId
    ORDER BY bd.buildingId, rm.buildingId
-- result qry 4 : Room 111 row , Building 34 row
-- -----------------------------------

-- 5 - ค้นหา หอพัก
--  qry 5 test 22/10/2554
    SELECT *
    FROM `Dom`
-- result qry 5 : 66 row
-- -----------------------------------

-- ? - ค้นหา
--  qry ? test day
-- result qry ? : ? row
-- -----------------------------------
?>