對於每一筆上網達成的交易，若其中定價超過500的商品種樣數超過2個的話，則列出其交易編號和所購買定價超過500的商品種樣數。這顯然與Q20不同。

1.
(SELECT mId, name
FROM Member
WHERE address LIKE ‘%台北縣%’ AND
        mId NOT IN (SELECT introducer
                               FROM Member);

5.
(SELECT DISTINCT mId, name
FROM (Member NATURAL JOIN Browse) NATURAL JOIN Product
WHERE pName = ‘蔡依林專輯二’)
EXCEPT
(SELECT DISTINCT mId, name
FROM Member JOIN ((Transaction NATURAL JOIN Record) NATURAL JOIN Product) ON mId = transMid
WHERE pName = ‘蔡依林專輯二’);

1.
SELECT Product.pNo, pName, unitPrice, COUNT(name)
FROM Product LEFT OUTER JOIN Author ON Product.pNo=Author.pNo
GROUP BY Product.pNo, pName, unitPrice;

3.
SELECT SUM(salePrice)
FROM Transaction NATURAL JOIN Record
WHERE to_char(transTime,’yyyymmdd’) = ’200402’

6.
CREATE VIEW Browselnf (mId, name, browseTime, pName)
      AS (SELECT Member.mId, name, browseTime, pName
        FROM Member, Browse, Product
        WHERE Browse.pNo = Product.pNo AND
                       Browse.mId = Member.mId);

7.
GRANT SELECT
ON BrowseInf
TO syhwang;

11.
SELECT Product.pNo, pName, SUM(salePrice)
FROM Product LEFT OUTER JOIN (Record NATURAL JOIN Transaction) AS R ON Product.pNo = R.pNo
WHERE to_char(transTime, ‘yyyymm’) = ‘200410’ AND
        Product.pNo IN
        (SELECT pNo
        FROM Browse
        WHERE to_char(brwoseTime,’yyyymm’) = ‘200410’
        GROUP BY pNo
        HAVING COUNT(DISTINCT mId)> 100 )
GROUP BY Product.pNo, pName;

2.
SELECT tNo, COUNT(pNo)
FROM Record
WHERE tNo IN(
        SELECT tNo
        FROM Record
        GROUP BY tNo
        HAVING SUM(salePrice)>1000)
GROUP BY tNo;
或是
SELECT tNo, COUNT(pNo)
FROM Record
GROUP BY tNo
HAVING SUM(salePrice)>1000;

3.
SELECT mId, name, SUM(salePrice)
FROM Member LEFT OUTER JOIN (Transaction NATURAL JOIN Record)
        ON (mId = transMid AND to_char(transTime, ‘yyyymm’) = ‘200510’)
GROUP BY mId, name;

2.
CREATE ASSERTION Book_need_author
CHECK (NOT EXISTS
        (SELECT *
        FROM Product AS P
        WHERE catalog=’Book’AND
                NOT EXISTS (SELECT *
                        FROM Author
                        WHERE pNo=P.pNo)));

SELECT sId, COUNT(*)
FROM Article
WHERE sId IN (SELECT sId
                FROM Takes NATRUAL JOIN Course
                WHERE courseName = ‘資料庫管理’)
GROUP BY sId;

1.
假設拍賣者姓名為唯一。
SELECT Member.name, Merchandise.bottomPrice, MAX(Bid.price)
FROM Merchandise, Member, Bid
WHERE Merchandise.mId = Member.mId
        AND Merchandise.mId = Bid.merMid
        AND Merchandise.seqNo = Bid.merSeqno
        AND Merchandise.mId = ‘m0002’
        AND Merchandise.seqNo = ‘003’
GROUP BY Member.name, Merchandise.bottomPrice ;

2.
假設沒有叫價過的會員不列出
SELECT mId, totalBid, totalDeal
FROM (SELECT mId, COUNT(*) AS totalBid
        FROM Bid
        WHERE to_char(datetime,’yyyymm’) = ‘200211’
        GROUP BY mId)
        LEFT OUTER JOIN
        (SELECT transMid, COUNT(*) AS totalDeal
        FROM Merchandise
        WHERE to_char(expired,’yyyymm’) = ‘200211’
        GROUP BY transMid)
        ON mId=transMid;

1.
SELECT dId, name, position
FROM Doctor NATURAL JOIN Department
WHERE dName = ‘一般內科’
ORDER BY position;

2.
SELECT dId, name, COUNT(*)
FROM Doctor LEFT OUTER JOIN Registers ON (Doctor.dId = Registers.dId
        AND to_char(startDateTime, ‘yyyymm’) = ‘200604’)
WHERE Doctor.dNo IN
        (SELECT dNo
        FROM Department
        WHERE dName = ‘一般內科’)
GROUP BY dId, name;

3.
SELECT pNo, name, birthday, sex
FROM Patient AS P
WHERE NOT EXISTS
        (SELECT dId
        FROM Doctor NATURAL JOIN Department
        WHERE dName=’一般內科’)
        EXCEPT
        (SELECT dId
        FROM Registers AS R
        WHERE to_char(startDateTime, ‘yyyy’) = ‘2005’ AND R.pNo = P.pNo);

4.
SELECT P.dName, COUNT(*)
FROM Department AS P, Doctor AS D, Registers AS R
WHERE P.dNo = D.dNo AND D.dId = R.dId
AND to_char(R.startDateTime, ‘yyyymm’) = ‘200604’
AND P.dNo IN
        (SELECT dNo
        FROM Doctor
        GROUP BY dNo
        HAVING COUNT(*) > 10);

5.
CREATE TABLE DoctorPatients
(dId CHAR(10) NOT NULL,
totalNum INT);

INSERT INTO DoctorPatients
AS (SELECT dId, COUNT(*)
    FROM Doctor LEFT OUTER JOIN Registers
    ON Doctor.dId = Registers.dId)
    GROUP BY dId);

6.
CREATE TRIGGER Increment
    AFTER INSERT ON Registers
    FOR EACH ROW
    UPDATE DoctorPatients
    SET totalNum = totalNum + 1
    WHERE dId = New.dId;

1.
SELECT depName, COUNT(*)
FROM Department, Patron
WHERE Department.depId=Patron.depId
GROUP BY depName;

2.
SELECT title
FROM Book
WHERE categoryId IN(
        SELECT categoryId
        FROM Patron, Involves
        WHERE Patron.patronId=Involves.patronId
            AND Patron.name=’張三’)
      OR categoryId IN(
        SELECT categoryId
        FROM CallCategory
        WHERE parent IN (
                SELECT categoryId
                FROM Patron, Involves
                WHERE Patron.patronId=Involves.patronId
                    AND Patron.name=’張三’))
      OR categoryId IN(
        SELECT categoryId
        FROM Callcategory
        WHERE parent IN (
            SELECT categoryId
            FROM Callcategory
            WHERE parent IN (
                SELECT categoryId
                FROM Patron,Involves
                WHERE Patron.patronId=Involves.patronId
                    AND Patron.name=’張三’)));