Scribd
Upload
3 views13 pages

SQL Queries for Employee and Project Analysis

The document discusses various SQL queries that encountered execution issues or errors, detailing the structure and logic of each query. It also includes Python code for data manipulation using psycopg2 and DuckDB, along with performance comparisons between two scripts. Additionally, the document addresses database normalization principles and compares SQL with ORM, while discussing security measures against Denial of Service attacks.

Uploaded by

alexei520402
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd
3 views13 pages

SQL Queries for Employee and Project Analysis

The document discusses various SQL queries that encountered execution issues or errors, detailing the structure and logic of each query. It also includes Python code for data manipulation using psycopg2 and DuckDB, along with performance comparisons between two scripts. Additionally, the document addresses database normalization principles and compares SQL with ORM, while discussing security measures against Denial of Service attacks.

Uploaded by

alexei520402
Copyright
© All Rights Reserved
We take content rights seriously. If you suspect this is your content, claim it here.
Available Formats
Download as PDF, TXT or read online on Scribd

缺少執⾏結果 -2

1.

(a)
SQL 無法執⾏ -5
SELECT [Link], [Link], dp.dp_no
FROM [Link] e
JOIN (
SELECT COUNT(dnumber) AS dl_no, dnumber
FROM dept_locations
GROUP BY dnumber
) dl ON [Link] = [Link]
JOIN (
SELECT COUNT(essn) AS dp_no, essn
FROM dependent
GROUP BY essn
) dp ON [Link] = [Link]
WHERE dl.dl_no > 1;

(b)

SELECT
[Link],
[Link],
FLOOR(AVG(EXTRACT(YEAR FROM AGE(DATE '2024-10-
01', [Link]::DATE)))::numeric) AS
average_age ⽂
字
FROM
Department
JOIN
Employee ON [Link] = [Link]
GROUP BY
[Link],
[Link]
HAVING
COUNT([Link]) > 1;
1
(c) SQL 無法執⾏ -5

WITH ProjectParticipation AS (
SELECT
[Link],
[Link],
COUNT(DISTINCT [Link]) AS participant_count,
AVG(NULLIF([Link], 'NULL')::decimal) AS
average_hours
FROM
PROJECT p
LEFT JOIN
WORKS_ON w ON [Link] = [Link]
GROUP BY
[Link],
[Link]
),

RankedCounts AS (
SELECT
DISTINCT participant_count
FROM
ProjectParticipation
ORDER BY
participant_count DESC
LIMIT 1 OFFSET 1
)

SELECT
[Link],
[Link],
pp.average_hours
FROM
2
ProjectParticipation pp
JOIN
RankedCounts rc ON pp.participant_count =
rc.participant_count
ORDER BY
[Link];

(d) 結果錯誤 -5

WITH ProjectParticipation AS (
SELECT
[Link],
[Link],
COUNT(DISTINCT [Link]) AS participant_count
FROM
PROJECT p
LEFT JOIN
WORKS_ON w ON [Link] = [Link]
GROUP BY
[Link],
[Link]
),
SecondMostParticipatedCount AS (
SELECT
DISTINCT participant_count
FROM
ProjectParticipation
ORDER BY
participant_count DESC
LIMIT 1 OFFSET 1
),
SecondMostParticipatedProjects AS (
SELECT

3
Pnumber,
Pname
FROM
ProjectParticipation
WHERE
participant_count = (SELECT
participant_count FROM SecondMostParticipatedCount)
)
SELECT
[Link],
[Link],
CASE WHEN [Link] >= 40000 THEN 'yes' ELSE
'no' END AS Salary_Above_40k,
d.Mgr_ssn AS Department_Manager_SSN,
[Link] AS Manager_Lname
FROM
EMPLOYEE e
JOIN
WORKS_ON w ON [Link] = [Link]
JOIN
SecondMostParticipatedProjects p ON [Link] =
[Link]
JOIN
DEPARTMENT d ON [Link] = [Link]
JOIN
EMPLOYEE m ON d.Mgr_ssn = [Link];

(e) SQL 無法執⾏ -5

SELECT
[Link],
COALESCE(COUNT(DISTINCT [Link]), 0) AS
project_count,

4
COALESCE(SUM(NULLIF([Link], 'NULL')::NUMERIC),
0) AS total_hours,
COALESCE(COUNT(DISTINCT [Link]), 0) AS
location_count
FROM
EMPLOYEE e
LEFT JOIN
WORKS_ON w ON [Link] = [Link] AND
NULLIF([Link], 'NULL')::NUMERIC > 0
LEFT JOIN
PROJECT p ON [Link] = [Link]
GROUP BY
[Link];

(f) SQL 無法執⾏ -5

SELECT
[Link],
COALESCE(COUNT(DISTINCT [Link]), 0) AS
project_count
FROM
EMPLOYEE e
LEFT JOIN
EMPLOYEE s ON [Link] = NULLIF(s.super_ssn,
'NULL')::NUMERIC
LEFT JOIN
WORKS_ON w ON [Link] = [Link]
WHERE
[Link] IS NULL
GROUP BY
[Link];

2.

(a)
5
import psycopg2

import pandas as pd

import duckdb

conn_pg = [Link](

host="localhost",

database="postgres",

user="postgres",

password="040207"

cur = conn_pg.cursor()

subscriptions_query = "SELECT * FROM subscriptions"

state_changes_query = "SELECT * FROM state_changes"

answers_query = "SELECT * FROM answers"

subscriptions_df = pd.read_sql(subscriptions_query,
conn_pg)

state_changes_df = pd.read_sql(state_changes_query,
conn_pg)

answers_df = pd.read_sql(answers_query, conn_pg)

conn_pg.close()

6
con = [Link]()

[Link]('subscriptions', subscriptions_df)

[Link]('state_changes', state_changes_df)

[Link]('answers', answers_df)

query = '''

WITH FilteredAnswers AS (

SELECT *

FROM answers

WHERE CreatedAt >= '2021-05-01 00:00:00'

),

MergedData AS (

SELECT [Link], [Link], [Link],


[Link], [Link], [Link],
[Link] AS CreatedAt_Answer, [Link]

FROM FilteredAnswers fa

LEFT JOIN subscriptions s

ON [Link] = [Link]

SELECT AnswerID, UserID, QuestionID, MissionID,


IsCorrect, CostTime, CreatedAt_Answer AS CreatedAt,
EndedAt

FROM MergedData

7
WHERE CreatedAt_Answer >= EndedAt;

'''

result_df = [Link](query).fetchdf()

print([Link](query).fetchdf())

(b)

query = '''

WITH FilteredAnswers AS (

SELECT *

FROM answers

WHERE CreatedAt >= '2021-05-01 00:00:00'

),

MergedData AS (

SELECT [Link], [Link], [Link],


[Link], [Link], [Link],
[Link] AS CreatedAt_Answer, [Link]

FROM FilteredAnswers fa

LEFT JOIN subscriptions s

ON [Link] = [Link]

),

PostCancellationAnswers AS (

SELECT AnswerID, UserID, QuestionID, MissionID,


IsCorrect, CostTime, CreatedAt_Answer AS CreatedAt,

8
EndedAt

FROM MergedData

WHERE CreatedAt_Answer >= EndedAt

SELECT

UserID,

AVG(CostTime) AS AvgCostTime,

AVG(CAST(IsCorrect AS FLOAT)) AS AccuracyRate

FROM PostCancellationAnswers

GROUP BY UserID;

'''

result_df = [Link](query).fetchdf()

filtered_df = result_df[result_df['AvgCostTime'] <=


50]

median_cost_time =
filtered_df['AvgCostTime'].median()

median_accuracy_rate =
filtered_df['AccuracyRate'].median()

[Link](figsize=(10, 6))

9
[Link](filtered_df['AvgCostTime'],
filtered_df['AccuracyRate'], color='blue',
alpha=0.5)

[Link](x=median_cost_time, color='red',
linestyle='--', label=f'Median Cost Time:
{median_cost_time:.2f}s')

[Link](y=median_accuracy_rate, color='green',
linestyle='--', label=f'Median Accuracy Rate:
{median_accuracy_rate:.2%}')

[Link]('Average Time per Question (seconds)')

[Link]('Accuracy Rate (%)')

[Link]('Scatter Plot of Average Time per


Question vs. Accuracy Rate')

[Link]()

[Link]()

10
(c)

在我的電腦上第一個程式的執行時間是 1.4 秒，第二個程式則是 0.04


秒。顯然第二個程式要快許多並且我認爲相當合理，因為它在合併之
前就篩選掉了不符合條件的資料，減少了程式實際需要處理篩選的資
料量

3.

(a)

1NF: All multi-valued attributes are seperated in to independent tuples


(滿足)
2NF: All nonprime attribute fully depend on the primary key (滿足)
3NF: No transitive dependency exists (滿足)
BCNF: No prime attribue depends on any attributes that do not form a
superkey(滿足)
Check_out_time不需要設為PK -1
4NF: No MVD exists (滿足) Customer_to_visit應該拆出來成獨立的資料表 -3

(b)

完成後會同時符合 2NF 和 3NF。首先 2NF 的問題本就不存在


⼀個⼯程師可在⼀天內對多位顧客諮詢，只是對每⼀位只會⼀次，所以CustomerID也要是PK -1
11
將 Fee 從表中拆出，因爲會導致 transitive functional dependency
𝑋(EngineerID, ConsultingDate) → 𝑍(serviceType) and 𝑍 → 𝑌(Fee)

(c)

1NF: 符合，若有不同報價則會存為新 tuple。

2NF: 符合，所有 nonprime attribute fully depend on the primary key。

3NF: 符合，把尺寸和名稱分別儲存以避免 transitive functional


dependency。

BCNF: 符合，所有 LHS attributes 都是 superkey。

4NF: 符合，MVD 不存在。

-0 4.

(a)

SQL:

優點：直接操作資料庫，具備高效性能、靈活性，可以運用複雜查詢
12
和優化技術。

缺點：需要熟悉 SQL 語法，對大型專案容易產生維護成本，跨資料庫


系統的可移植性低。

ORM:

優點：提供面向物件的操作，減少手寫 SQL 的機會，增強可讀性和可


維護性，並支援跨資料庫系統。

缺點：效能相對較差，對複雜查詢有時不夠靈活，可能生成不夠優化
的 SQL。

(b)

Denial of Service (DoS)


這類攻擊旨在讓資料庫資源耗盡，導致無法提供正常服務。攻擊者可
能通過大量無效請求或觸發資源密集型查詢來使伺服器過載。
防範措施：

限制查詢資源使用，例如設置查詢超時或結果集大小限制。

使用流量控制和負載平衡來防止過載。

(c)

前端與後端指的是應用程式的邏輯劃分，前端處理用戶界面，後端處
理業務邏輯與資料。

client 端與 server 端則是部署架構上的區別，client 是執行應用的裝


置（如瀏覽器），server 是提供資源的伺服器。

13

You might also like