---
# System prepended metadata

title: Week 3
tags: [DB]

---

---
tags: DB
---

# Week 3

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

![](https://i.imgur.com/tQ17nsx.png)

### A
```sql
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
```sql
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
```sql
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

![](https://i.imgur.com/rDaDqyr.jpg)

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