---
tags: DB
---

# Week 3

## E 3-4 (a, b, c)


### A

SELECT DISTINCT P.pNo, P.pName
FROM Product AS P, Browse AS B, Member AS M
WHERE M.name='馬英九' AND
    P.pNo = B.pNo AND
    B.mId = M.mId;

### B

SELECT DISTINCT M.mId, P.pNo, P.pName
FROM Product AS P, Browse AS B, Member AS M
WHERE M.name='馬英九' AND
    P.pNo = B.pNo AND
    B.mId = M.mId AND
    P.pNo NOT IN (
        SELECT pNo
        FROM Record, Transaction
        WHERE M.mId = Transaction.transMid AND
        Transaction.tNo = Record.tNo
    );

### C

SELECT M.mId, name 
FROM Member AS M
WHERE M.mId NOT IN (
    SELECT DISTINCT T.transmId AS mId
    FROM Record AS R, Transaction AS T, Product AS P
    WHERE  R.tNo = T.tNo AND
        R.pNo = P.pNo AND
        P.unitPrice > 500
)

## Q2


SELECT 課程編號, 課程名稱
FROM 課程
WHERE 開課科系='資訊管理學系'