# DB Mid (04/17)
###### tags: `DB`
---
[TOC]
---
<style>
.markdown-body {
min-width: 1200px;
}
u.dot {
text-decoration-style: dotted;
}
</style>
## Useful Refs
- https://hackmd.io/@hami-duck/rk1YP49g9/%2FiU4PYRx4SPm-L_ZlJ-dh0w
- MySQL 標準
- https://www.w3schools.com/sql/default.asp
## 考古 2021
### 1.a
```graphviz
graph PAST_2021_1A {
label = "Past 2021 1.a\n\n";
labelloc="t";
fontsize=26;
layout=dot;
fontname="Sarasa Fixed TC, monospace";
node [fontname="Sarasa Fixed TC, monospace"];
edge [fontname="Sarasa Fixed TC, monospace"];
//
// Entities
//
{
node [shape=box, fontsize=16, style=filled, fillcolor=lightgrey, ordering="out"];
AIRPORT;
FLIGHT;
AIRPLANE;
node [peripheries=2];
FLIGHT_LEG;
LEG_INSTANCE;
FARES;
SEAT_RESERVATION;
};
//
// Relationships
//
{
node [shape=diamond, fontsize=16, style=filled, fillcolor=whitesmoke];
S_DEPARTS; S_ARRIVES;
A_DEPARTS; A_ARRIVES;
node [peripheries=2];
HAS; INSTANCES; OFFERS; PRICES; USES;
}
//
// Attributes
//
{
node [shape=oval, fontsize=14];
AIRPORT -- {
AIRPORT_CODE [label=<<u>AIRPORT_CODE</u>>];
NAME; CITY; STATE;
};
FLIGHT -- {
FLIGHT_NUMBER [label=<<u>FLIGHT_NUMBER</u>>];
AIRLINE; WEEKDAYS;
};
FLIGHT_LEG -- {
LEG_NUMBER [label=<<u class="text-decoration-style: dotted;">LEG_NUMBER</u>>];
};
LEG_INSTANCE -- {
DATE [label=<<u style="text-decoration-style: dotted;">DATE</u>>];
};
FARES -- {
FARE_CODE [label=<<u style="text-decoration-style: dotted;">FARE_CODE</u>>];
AMOUNT; RESTRICTIONS;
};
AIRPLANE -- {
AIRPLANE_ID [label=<<u>AIRPLANE_ID</u>>];
TOTAL_NUMBER_OF_SEATS;
};
SEAT_RESERVATION -- {
SEAT_NUMBER [label=<<u style="text-decoration-style: dotted;">SEAT_NUMBER</u>>];
CUSTOMER_NAME; CUSTOMER_PHONE;
};
S_DEPARTS -- SCHEDULE_DEPARTURE_TIME;
S_ARRIVES -- SCHEDULE_ARRIVAL_TIME;
A_DEPARTS -- DEPARTURE_TIME;
A_ARRIVES -- ARRIVAL_TIME;
};
//
// Participations
//
{
edge [len=1.8];
FLIGHT -- HAS [label="1"];
HAS -- FLIGHT_LEG [label="N", color="black:invis:black"];
FLIGHT_LEG -- INSTANCES [label="1"];
INSTANCES -- LEG_INSTANCE [label="N", color="black:invis:black"];
FLIGHT -- PRICES [label="1"];
PRICES -- FARES [label="N", color="black:invis:black"];
FLIGHT_LEG -- S_DEPARTS [label="N", color="black:invis:black"];
S_DEPARTS -- AIRPORT [label="1"];
FLIGHT_LEG -- S_ARRIVES [label="N", color="black:invis:black"];
S_ARRIVES -- AIRPORT [label="1"];
LEG_INSTANCE -- A_DEPARTS [label="N", color="black:invis:black"];
A_DEPARTS -- AIRPORT [label="1"];
LEG_INSTANCE -- A_ARRIVES [label="N", color="black:invis:black"];
A_ARRIVES -- AIRPORT [label="1"];
LEG_INSTANCE -- USES [label="N", color="black:invis:black"];
USES -- AIRPLANE [label="1"];
LEG_INSTANCE -- OFFERS [label="1"];
OFFERS -- SEAT_RESERVATION [label="N", color="black:invis:black"];
}
//
// Layout
//
{
{rank=same; FLIGHT; PRICES; FARES;}
{rank=same; LEG_INSTANCE; INSTANCES; FLIGHT_LEG; USES;}
edge [style=invis];
SCHEDULE_DEPARTURE_TIME -- AIRPORT;
AIRPLANE_ID -- SEAT_RESERVATION;
}
}
```
<!-- - Entity:
- **`AIRPORT`**: <u>`AIRPORT_CODE`</u>, `NAME`, `CITY`, `STATE`
- **`FLIGHT`**: <u>`FLIGHT_NUMBER`</u>, `AIRLINE`, `WEEKDAYS`
- **[`FLIGHT_LEG`]**: (<u>`FLIGHT_NUMBER`</u>), <u class="dot">`LEG_NUMBER`</u>, (`DEPARTURE_AIRPORT_CODE`, `ARRIVAL_AIRPORT_CODE`), <`SCHEDULE_DEPARTURE_TIME`, `SCHEDULE_ARRIVAL_TIME`>
- **[`LEG_INSTANCE`]**: (<u>`FLIGHT_NUMBER`</u>, <u>`LEG_NUMBER`</u>), <u class="dot">`DATE`</u>, (`AIRPLANE_ID`, `DEPARTURE_AIRPORT_CODE`, `ARRIVAL_AIRPORT_CODE`), <`DEPARTURE_TIME`, `ARRIVAL_TIME`>
- **[`FARES`]**: (<u>`FLIGHT_NUMBER`</u>), <u class="dot">`FARE_CODE`</u>, `AMOUNT`, `RESTRCITIONS`
- **`AIRPLANE`**: <u>`AIRPLANE_ID`</u>, `TOTAL_NUMBER_OF_SEATS`
- **[`SEAT_RESERVATION`]**: (<u>`FLIGHT_NUMBER`</u>, <u>`LEG_NUMBER`</u>, <u>`DATE`</u>), <u class="dot">`SEAT_NUMBER`</u>, `CUSTOMER_NAME`, `CUSTOMER_PHONE` -->
### 1.b
https://www.w3schools.com/sql/sql_primarykey.asp
- composite foreign key
- escape from keywords
- null?
```sql=
-- TOO FUCKING LONG
CREATE TABLE AIRPORT
(
AIRPORT_CODE INT NOT NULL,
NAME VARCHAR(50) NOT NULL,
CITY VARCHAR(50) NOT NULL,
STATE VARCHAR(50) NOT NULL,
CONSTRAINT PK_AIRPORT
PRIMARY KEY (AIRPORT_CODE)
);
CREATE TABLE FLIGHT
(
FLIGHT_NUMBER INT NOT NULL,
AIRLINE VARCHAR(50) NOT NULL,
WEEKDAYS CHAR(7) NOT NULL,
CONSTRAINT PK_FLIGHT
PRIMARY KEY (FLIGHT_NUMBER)
);
CREATE TABLE FLIGHT_LEG
(
FLIGHT_NUMBER INT NOT NULL,
LEG_NUMBER INT NOT NULL,
DEPARTURE_AIRPORT_CODE INT NOT NULL,
SCHEDULE_DEPARTURE_TIME TIME NOT NULL,
ARRIVAL_AIRPORT_CODE INT NOT NULL,
SCHEDULE_ARRIVAL_TIME TIME NOT NULL,
CONSTRAINT PK_FLIGHT_LEG
PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER),
CONSTRAINT FK_FLIGHT_LEG__FLIGHT
FOREIGN KEY (FLIGHT_NUMBER) REFERENCES FLIGHT(FLIGHT_NUMBER),
CONSTRAINT FK_FLIGHT_LEG__AIRPORT__DEPART
FOREIGN KEY (DEPARTURE_AIRPORT_CODE) REFERENCES AIRPORT(AIRPORT_CODE),
CONSTRAINT FK_FLIGHT_LEG__AIRPORT__ARRIVE
FOREIGN KEY (ARRIVAL_AIRPORT_CODE) REFERENCES AIRPORT(AIRPORT_CODE)
);
CREATE TABLE LEG_INSTANCE
(
FLIGHT_NUMBER INT NOT NULL,
LEG_NUMBER INT NOT NULL,
"DATE" DATE NOT NULL,
AIRPLANE_ID INT NOT NULL,
DEPARTURE_AIRPORT_CODE INT NOT NULL,
DEPARTURE_TIME TIME NOT NULL,
ARRIVAL_AIRPORT_CODE INT NOT NULL,
ARRIVAL_TIME TIME NOT NULL,
CONSTRAINT PK_LEG_INSTANCE
PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER, "DATE"),
CONSTRAINT FK_LEG_INSTANCE__FLIGHT_LEG
FOREIGN KEY (FLIGHT_NUMBER, LEG_NUMBER) REFERENCES FLIGHT_LEG(FLIGHT_NUMBER, LEG_NUMBER),
CONSTRAINT FK_LEG_INSTANCE__AIRPLANE
FOREIGN KEY (AIRPLANE_ID) REFERENCES AIRPLANE(AIRPLANE_ID),
CONSTRAINT FK_LEG_INSTANCE__AIRPORT__DEPART
FOREIGN KEY (DEPARTURE_AIRPORT_CODE) REFERENCES AIRPORT(AIRPORT_CODE),
CONSTRAINT FK_LEG_INSTANCE__AIRPORT__ARRIVE
FOREIGN KEY (ARRIVAL_AIRPORT_CODE) REFERENCES AIRPORT(AIRPORT_CODE)
);
CREATE TABLE FARES
(
FLIGHT_NUMBER INT NOT NULL,
FARE_CODE INT NOT NULL,
AMOUNT INT NOT NULL,
RESTRICTIONS VARCHAR(8000) NOT NULL,
CONSTRAINT PK_FARES
PRIMARY KEY (FLIGHT_NUMBER, FARE_CODE),
CONSTRAINT FK_FARES__FLIGHT
FOREIGN KEY (FLIGHT_NUMBER) REFERENCES FLIGHT(FLIGHT_NUMBER),
);
CREATE TABLE AIRPLANE
(
AIRPLANE_ID INT NOT NULL,
TOTAL_NUMBER_OF_SEATS INT NOT NULL,
CONSTRAINT PK_AIRPLANE
PRIMARY KEY (AIRPLANE_ID)
);
CREATE TABLE SEAT_RESERVATION
(
FLIGHT_NUMBER INT NOT NULL,
LEG_NUMBER INT NOT NULL,
"DATE" DATE NOT NULL,
SEAT_NUMBER INT NOT NULL,
CUSTOMER_NAME VARCHAR(50) NOT NULL,
CUSTOMER_PHONE VARCHAR(50) NOT NULL,
CONSTRAINT PK_SEAT_RESERVATION
PRIMARY KEY (FLIGHT_NUMBER, LEG_NUMBER, "DATE", SEAT_NUMBER),
CONSTRAINT FK_SEAT_RESERVATION__LEG_INSTANCE
FOREIGN KEY (FLIGHT_NUMBER, LEG_NUMBER, "DATE") REFERENCES LEG_INSTANCE(FLIGHT_NUMBER, LEG_NUMBER, "DATE")
);
```
### 1.c
- =="For each ..." 要不要選出來？==
https://www.w3schools.com/sql/trysql.asp?filename=trysql_editor
==https://database.guide/5-ways-to-select-rows-with-the-maximum-value-for-their-group-in-sql/==
==https://stackoverflow.com/a/71548342==
```sql=
-- UGLY
SELECT FLIGHT_LEG.FLIGHT_NUMBER AS "the flight number",
FIRST_LEG.SCHEDULE_DEPARTURE_TIME AS "the departure time",
LAST_LEG.SCHEDULE_ARRIVAL_TIME AS "the arrival time"
FROM FLIGHT_LEG AS FIRST_LEG
JOIN FLIGHT_LEG AS LAST_LEG
ON FIRST_LEG.FLIGHT_NUMBER = LAST_LEG.FLIGHT_NUMBER
WHERE FIRST_LEG.LEG_NUMBER =
(SELECT MIN(LEG_NUMBER)
FROM FLIGHT_LEG
WHERE FLIGHT_LEG.FLIGHT_NUMBER = FIRST_LEG.FLIGHT_NUMBER)
AND LAST_LEG.LEG_NUMBER =
(SELECT MAX(LEG_NUMBER)
FROM FLIGHT_LEG
WHERE FLIGHT_LEG.FLIGHT_NUMBER = LAST_LEG.FLIGHT_NUMBER);
```
<!--
SELECT FirstOrder.OrderID,
FirstOrder.ProductID AS FirstProductID,
FirstOrder.Quantity AS FirstQuantity,
LastOrder.ProductID AS LastProductID,
LastOrder.Quantity AS LastQuantity
FROM OrderDetails AS FirstOrder
JOIN OrderDetails AS LastOrder
ON FirstOrder.OrderID = LastOrder.OrderID
WHERE FirstOrder.ProductID =
(SELECT MIN(ProductID)
FROM OrderDetails
WHERE OrderDetails.OrderID = FirstOrder.OrderID)
AND LastOrder.ProductID =
(SELECT MAX(ProductID)
FROM OrderDetails
WHERE OrderDetails.OrderID = LastOrder.OrderID);
-->
### 1.d
```sql=
SELECT LEG_NUMBER AS "leg number",
SUM(TOTAL_NUMBER_OF_SEATS) - SUM(RESERVED_SEATS) AS "the number of available seats"
FROM LEG_INSTANCE
NATURAL JOIN AIRPLANE
NATURAL JOIN
(SELECT FLIGHT_NUMBER, LEG_NUMBER, "DATE", COUNT(SEAT_NUMBER) AS RESERVED_SEATS
FROM LEG_INSTANCE
NATURAL LEFT JOIN SEAT_RESERVATION
GROUP BY FLIGHT_NUMBER, LEG_NUMBER, "DATE")
WHERE FLIGHT_NUMBER IN
(SELECT FLIGHT_LEG.FLIGHT_NUMBER
FROM FLIGHT_LEG
GROUP BY FLIGHT_LEG.FLIGHT_NUMBER
HAVING COUNT(LEG_NUMBER) >= 2)
GROUP BY FLIGHT_NUMBER, LEG_NUMBER;
```
### 1.e
```sql=
-- i do not actually know how to calculate "average fares"
SELECT AVG(AMOUNT) / COUNT(DISTINCT LEG_NUMBER) AS "average Fares"
COUNT(DISTINCT AIRPLANE_ID) AS "number of different planes used in the flight"
FROM LEG_INSTANCE
-- NATURAL JOIN AIRPLANE
NATURAL JOIN FARES
GROUP BY FLIGHT_NUMBER;
```
### 2.a ==TODO==
> - R-A step: Managers and Clerks，這個步驟的主要任務是確認使用者的需求，避免開發出的成果不符合使用者的需求，因此會由負責監督專案的 managers 向使用資料庫的 clerks 詢問、確認 clerks 的需求，以便後續開發。
> - M-D step: Managers, Expert and Local IT，這個步驟如其名，也就是要開始規劃資料模型了，而資料模型的設計取決於用戶的需求與資料的特性，因此雖然 managers 沒有任何電腦科學的背景，但仍要參與此步驟以傳達其他 group 的成員最正確的用戶資訊；Local IT 身為開發與後續維護資料庫的主要 group，必須熟知資料庫的設計，故一定是必須參與設計環節的；最後則是來自諮詢公司的 experts，這邊的 experts 相較於 Local IT 應是資料庫領域的專家，他們參與本步驟的討論能夠即時地指出 local IT 可能構想出的模型設計不良或能再改進之處，良好的設計基礎對於開發與後續維護相當有幫助。
> - D-P step: Managers and Local IT，與上步驟類似，Managers 雖無電腦科學背景，但他們在 R-A step 時通過直面用戶，瞭解了用戶的需求與資料特性，故在此步驟需參與監督，以避免實際動手整理資料的 Local IT 破壞了資料；而 Local IT 如前面所述，是實際動手做事的人，故一定會參與此步驟。
> - T-C step: Managers, Clerks and Local IT，此步驟已是最後的測試與驗證步驟，clerks 身為實際使用資料庫的人，能夠參與本階段能較為有效的檢驗成品是否有問題，而在給予 clerks 測試前，Local IT 和 Managers 應會先進行內部測試。( 可能 Local IT 完成後自己測試一遍，自認沒問題後交由熟悉用戶需求的 Managers 再測試一遍，也沒問題才交付 Clerks 進行最後測試 )
>
> [name=Cloudy]
### 2.b ==TODO==
> - R-A step, M-D step, D-P step。
> - R-A step 部分，若在此部分 managers 沒有正確理解 clerk 的需求，或是在此階段時 clerk 並沒有規劃好未來長遠的需求，就會使得後續的模型設計、資料前處理都受到嚴重的影響，雖然未來再針對需求更新資料庫理論上是可行的，但難度與成本都相當高，也可能造成後續維護困難，使得 DB 無法 work in the long run，故此步驟是關鍵步驟之一。
> - M-D step、D-P step 部分，類似於上步驟所述，不好或錯誤的模型設計或資料前處理對於未來的影響都相當大，而且此類的影響在未來若想修正，都必須付出相當高的代價，故對於資料庫開發來說，此二步驟也是相當關鍵的。
>
> [name=Cloudy]
## 考古 2020
### 1.a
```graphviz
graph PAST_2020_1A {
label = "Past 2020 1.a\n\n";
labelloc="t";
fontsize=26;
layout=dot;
fontname="Sarasa Fixed TC, monospace";
node [fontname="Sarasa Fixed TC, monospace"];
edge [fontname="Sarasa Fixed TC, monospace"];
//
// Entities
//
{
node [shape=box, fontsize=16, style=filled, fillcolor=lightgrey, ordering="out"];
Library_branch;
Book;
Borrower;
node [peripheries=2];
Book_copy;
Loaning;
};
//
// Relationships
//
{
node [shape=diamond, fontsize=16, style=filled, fillcolor=whitesmoke];
node [peripheries=2];
HasVersion;Loans;
IsLoanedTo;
IsLoaned;
}
//
// Attributes
//
{
node [shape=oval, fontsize=14];
Library_branch -- {
branch_id [label=<<u>branch_id</u>>];
branch_name; address;
};
Book -- {
book_id [label=<<u>book_id</u>>];
title; author_name; publisher_name;
};
Book_copy -- {
No_of_copies [label=<<u style="text-decoration-style: dotted;">No_of_copies</u>>];
};
Borrower -- {
card_no [label=<<u>card_no</u>>];
name; b_address [label="address"]; phone_number;
};
};
//
// Participations
//
{
edge [len=1.8];
Book -- HasVersion [label="1"];
HasVersion -- Book_copy [label="N", color="black:invis:black"];
Library_branch -- Loans [label="1"];
Loans -- Loaning [label="N", color="black:invis:black"];
Book_copy -- IsLoanedTo [label="1"];
IsLoanedTo -- Loaning [label="N", color="black:invis:black"];
Loaning -- IsLoaned [label="N", color="black:invis:black"];
IsLoaned -- Borrower [label="1"];
}
//
// Layout
//
{
{rank=same; Library_branch; Book_copy;}
edge [style=invis];
}
}
```
### 1.b
```graphviz
digraph PAST_2020_1b
{
label="Past 2020 1.b\n\n";
labelloc="t";
fontsize=26;
layout=dot;
fontname="Sarasa Fixed TC, monospace";
node [fontname="Sarasa Fixed TC, monospace"];
edge [fontname="Sarasa Fixed TC, monospace"];
{
node [shape=none];
Library_branch [
xlabel=<<b>Library_branch</b>>,
label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
<td port="_head" cellpadding="0" border="0"></td>
<td port="branch_id"><u>branch_id </u></td>
<td port="branch_name">branch_name</td>
<td port="address">address </td>
</tr></table>>
];
Book [
xlabel=<<b>Book</b>>,
label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
<td port="_head" cellpadding="0" border="0"></td>
<td port="book_id"><u>book_id</u></td>
<td port="title">title </td>
<td port="author_name">author_name</td>
<td port="publisher_name">publisher_name </td>
</tr></table>>
];
Book_copy [
xlabel=<<b>Book_copy</b>>,
label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
<td port="_head" cellpadding="0" border="0"></td>
<td port="book_id"><u>book_id</u></td>
<td port="No_of_copies"><u>No_of_copies</u></td>
</tr></table>>
];
Borrower [
xlabel=<<b>Borrower</b>>,
label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
<td port="_head" cellpadding="0" border="0"></td>
<td port="card_no"><u>card_no</u></td>
<td port="name">name</td>
<td port="address">address </td>
<td port="phone_number">phone_number</td>
</tr></table>>
];
Loaning [
xlabel=<<b>Loaning</b>>,
label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
<td port="_head" cellpadding="0" border="0"></td>
<td port="branch_id"><u>branch_id</u> </td>
<td port="book_id"><u>book_id</u></td>
<td port="No_of_copies"><u>No_of_copies</u> </td>
<td port="card_no"><u>card_no</u></td>
</tr></table>>
];
}
{
edge [style=invis];
Library_branch:_head -> Book:_head;
Book:_head -> Book_copy:_head;
Book_copy:_head -> Borrower:_head;
Borrower:_head -> Loaning:_head;
}
graph [splines=polyline];
{
edge [constraint=false];
Book_copy:book_id -> Book:book_id;
Loaning:branch_id -> Library_branch:branch_id;
Loaning:book_id -> Book:book_id;
Loaning:No_of_copies -> Book_copy:No_of_copies;
Loaning:card_no -> Borrower:card_no;
}
}
```
### 2.a
```sql=
SELECT Fname, Lname
FROM EMPLOYEE
WHERE NOT EXISTS
(SELECT *
FROM WORKS_ON
JOIN PROJECT
ON WORKS_ON.Pno = PROJECT.Pnumber
WHERE WORKS_ON.Essn = EMPLOYEE.Super_ssn
AND PROJECT.Dnum = 5);
```
### 2.b
```sql=
SELECT Fname, Lname,
SUM(WORKS_ON.HOURS) AS "total hours on all projects"
FROM EMPLOYEE
JOIN WORKS_ON
ON EMPLOYEE.Ssn = WORKS_ON.Essn
GROUP BY EMPLOYEE.Dno, EMPLOYEE.Ssn, EMPLOYEE.Fname, EMPLOYEE.Lname
HAVING COUNT(WORKS_ON.Pno) >= 3
AND MIN(WORKS_ON.HOURS) >= 2;
```
### 2.c
```sql=
SELECT Lname, Salary, Hours
FROM EMPLOYEE AS E1
JOIN WORKS_ON
ON E1.Ssn = WORKS_ON.Essn
WHERE WORKS_ON.Pno IN
(SELECT Pno
FROM WORKS_ON
GROUP BY Pno
HAVING COUNT(Essn) > 5)
AND Salary >
(SELECT AVG(Salary)
FROM EMPLOYEE AS E2
GROUP BY Sex, Dno
HAVING E1.Sex = E2.Sex
AND E1.Dno = E2.Dno);
```
### 3.a ==TODO==
### 3.b ==TODO==
## 考古 2019
### 1.a
```graphviz
digraph PAST_2019_1a
{
label="Past 2019 1.a\n\n";
labelloc="t";
fontsize=26;
layout=dot;
fontname="Sarasa Fixed TC, monospace";
node [fontname="Sarasa Fixed TC, monospace"];
edge [fontname="Sarasa Fixed TC, monospace"];
{
node [shape=none];
SHIP [
xlabel=<<b>SHIP</b>>,
label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
<td port="_head" cellpadding="0" border="0"></td>
<td port="Sname"><u>Sname</u></td>
<td port="Owner">Owner</td>
<td port="Type">Type</td>
<td port="Home_port_cname">Home_port_cname</td>
<td port="Home_port_pname">Home_port_pname</td>
</tr></table>>
];
SHIP_MOVEMENT [
xlabel=<<b>SHIP_MOVEMENT</b>>,
label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
<td port="_head" cellpadding="0" border="0"></td>
<td port="Sname"><u>Sname</u></td>
<td port="Date"><u>Date</u></td>
<td port="Time"><u>Time</u> </td>
<td port="Longitude">Longitude </td>
<td port="Latitude">Latitude </td>
</tr></table>>
];
SHIP_TYPE [
xlabel=<<b>SHIP_TYPE</b>>,
label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
<td port="_head" cellpadding="0" border="0"></td>
<td port="Type"><u>Type</u></td>
<td port="Tonnage">Tonnage</td>
<td port="Hull">Hull</td>
</tr></table>>
];
PORT [
xlabel=<<b>PORT</b>>,
label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
<td port="_head" cellpadding="0" border="0"></td>
<td port="Cname"><u>Cname</u></td>
<td port="Pname"><u>Pname</u></td>
<td port="Oname">Oname</td>
</tr></table>>
];
PORT_VISIT [
xlabel=<<b>PORT_VISIT</b>>,
label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
<td port="_head" cellpadding="0" border="0"></td>
<td port="Sname"><u>Sname</u></td>
<td port="Cname"><u>Cname</u></td>
<td port="Pname"><u>Pname</u></td>
<td port="Start_date"><u>Start_date</u> </td>
<td port="End_date">End_date</td>
</tr></table>>
];
STATE_COUNTRY [
xlabel=<<b>STATE_COUNTRY</b>>,
label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
<td port="_head" cellpadding="0" border="0"></td>
<td port="Cname"><u>Cname</u></td>
<td port="Continent">Continent </td>
</tr></table>>
];
SEA_OCEAN_LAKE [
xlabel=<<b>SEA_OCEAN_LAKE</b>>,
label=<<table border="0" cellborder="1" cellspacing="0" cellpadding="6"><tr>
<td port="_head" cellpadding="0" border="0"></td>
<td port="Oname"><u>Oname</u></td>
</tr></table>>
];
}
{
edge [style=invis];
SHIP:_head -> SHIP_MOVEMENT:_head;
SHIP_MOVEMENT:_head -> SHIP_TYPE:_head;
SHIP_TYPE:_head -> PORT:_head;
PORT:_head -> PORT_VISIT:_head;
PORT_VISIT:_head -> STATE_COUNTRY:_head;
STATE_COUNTRY:_head -> SEA_OCEAN_LAKE:_head;
}
graph [splines=polyline];
{
edge [constraint=false];
SHIP:Type -> SHIP_TYPE:Type;
SHIP:Home_port_cname -> PORT:Cname;
SHIP:Home_port_pname -> PORT:Pname;
SHIP_MOVEMENT:Sname -> SHIP:Sname;
PORT:Cname -> STATE_COUNTRY:Cname;
PORT:Oname -> SEA_OCEAN_LAKE:Oname;
PORT_VISIT:Sname -> SHIP:Sname;
PORT_VISIT:Cname -> PORT:Cname;
PORT_VISIT:Pname -> PORT:Pname;
}
}
```
### 1.c
```sql=
SELECT Fname, Lname
FROM EMPLOYEE
JOIN WORKS_ON
ON EMPLOYEE.Ssn = Works_ON.Essn
JOIN PROJECT
ON WORKS_ON.Pno = PROJECT.Pnumber
WHERE PROJECT.Dnum IN (3, 4)
GROUP BY Ssn, Fname, Lname
HAVING COUNT(DISTINCT PROJECT.Dnum) = 2;
```
### 1.d
```sql=
SELECT Fname, Lname
FROM EMPLOYEE
JOIN WORKS_ON
ON EMPLOYEE.Ssn = WOKS_ON.Essn
GROUP BY Ssn, Fname, Lname
HAVING SUM(Hours) > 10;
```
### 1.e
```sql=
-- SUPER UGLY
WITH S (Dno, Sum_salary) AS
(SELECT Dno, SUM(Salary)
FROM EMPLOYEE AS Super
WHERE EXISTS
(SELECT *
FROM EMPLOYEE
WHERE Super.Ssn = EMPLOYEE.Super_ssn)
GROUP BY Dno),
N (Dno, Sum_salary) AS
(SELECT Dno, SUM(Salary)
FROM EMPLOYEE
WHERE NOT EXISTS
(SELECT *
FROM S
WHERE EMPLOYEE.Ssn = S.Ssn)
GROUP BY Dno)
SELECT S.Dno,
S.Sum_salary AS "the total amount of salary for supervisor employees"
N.Sum_salary AS "the total amount of salary for non-supervisor employees"
FROM S
JOIN N
ON S.Dno = N.Dno;
```
### 2 ==TODO==
### 3 ==TODO==
## 考古 2018
## Notes
### `SELECT` 組成部分邏輯執行順序
1. `FROM`
2. `WHERE`
3. `GROUP BY`
4. `HAVING`
5. `SELECT`
6. `DISTINCT`
7. `ORDER BY`
8. `TOP/FETCH/OFFSET`
### *"List the various cases where the use of a NULL value would be appropriate."*
### *"Discuss the difficulties for possible DBMSs to enforce key, entity integrity and foreign key constraints, if related tables are located in different sites distributedly."*
### *"Discuss the advantages and disadvantages of different ways of handling View Materialization."*
Sign in with Wallet
Connect another wallet