對sql作業的總結（不用group by 通過with as，exists實現分類）

一次資料庫作業 題目如下：

Consider the following SQL table definitions:

CREATE TABLE OlympicEvent (
Name text,
Year int,
Description text
);
CREATE TABLE Athlete (
Name text,
DateOfBirth date,
Gender char,
Nationality text
);

1. Define sensible key constraints for these tables in SQL. Note: An olympic event (such as 200m sprint) can take place in multiple years. Different athletes may have identical names, but should be distinguishable by date of birth. [1 mark]

2. Define an SQL constraint that restricts Gender to sensible values. [1 mark]

3. Create the SQL definition for a table Competes recording medals won by athletes competing in Olympic events. Define sensible key and foreign key constraints, as well as a constraint which ensures that medals can only be gold, silver or bronze. Note: While it can happen (in the case of ties) that multiple gold or silver medals are handed out for the same event, an athlete cannot gain multiple medals for the same event.[3 marks] 

4. Write an SQL query returning all nations (nationalities of athletes) that won at least 2 gold medals in 2018. Do not use aggregation (GROUP BY). Note: Each nation satisfying the criteria should be listed only once.[3 marks]

5. Express the same query using aggregation. Submit your answers as pdf file via stream. Include your name and student ID.[2 marks] 

對於前3問來講，無非就是建表再加限制

CREATE TABLE OlympicEvent (
  Name VARCHAR(20),
  Year int,
  Description text,
  CONSTRAINT pk_OlympicEvent PRIMARY KEY (Name,Year)
);
CREATE TABLE Athlete (
  Name VARCHAR(20),
  DateOfBirth date,
  Gender char(20) CHECK (Gender ='male' OR  Gender = 'female'),
  Nationality text,
  CONSTRAINT pk_Athlete PRIMARY KEY  (Name,DateOfBirth)
);
CREATE TABLE Competes (
  Athlete_name VARCHAR(20),
  DateOfBirth date,
  OlympicEvent_name VARCHAR(20),
  OlympicEvent_year int,
  Medal CHAR(20) CHECK (Medal = 'gold' OR  Medal = 'silver' OR  Medal = 'bronze'),
  CONSTRAINT fk_Athelete FOREIGN KEY (Athlete_name,DateOfBirth) REFERENCES Athlete(Name,DateOfBirth),
  CONSTRAINT fk_OlympicEvent FOREIGN KEY (OlympicEvent_name,OlympicEvent_year) REFERENCES OlympicEvent(Name, Year)
);

然後往裡面加數據

athlete：

olympicEvent表：

competes表：

第5問的話 聯表後再用group by 也不是很難

SELECT athlete.Nationality ,count(1) as sum_medals FROM athlete,competes WHERE competes.Athlete_name = athlete.Name GROUP BY athlete.Nationality HAVING count(1)>=2;

結果如下

第4問 題目中不讓用group by 問了問老師 也不讓用sum count之類的聚集函數 

我寫的代碼如下

WITH medal_nationality AS (
    SELECT athlete_name, athlete.dateofbirth, olympicevent_year, olympicevent_name, nationality FROM athlete, competes
    WHERE athlete.Name = competes.Athlete_name AND athlete.DateOfBirth = competes.DateOfBirth
)

SELECT DISTINCT nationality FROM medal_nationality t1
WHERE EXISTS(
  SELECT * FROM medal_nationality t2
  WHERE t1.nationality = t2.nationality AND
        (t1.athlete_name, t1.dateofbirth, t1.olympicevent_name, t1.olympicevent_year) <>
        (t2.athlete_name, t2.dateofbirth, t2.olympicevent_name, t2.olympicevent_year)
);

 利用with as 將medal_nationality選出來 包含 athlete_name, athlete.dateofbirth, olympicevent_year, olympicevent_name, nationality 5個欄位，後用exists來選出只返回true的值，條件是必須國籍相等，但兩個人不能是同一個人（見問題1），最後結果為：

算是不用group by 最後也把需要的數據篩選出來了吧 總結完畢 _(:з」∠)_