Summary of SQL Jobs (without group by using the With As,exists implementation classification)

Once the database job title is as follows:

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 has identical names, but should is distinguishable by date of birth. [1 Mark]

2. Define an SQL constraint this 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 this medals can only be gold, silve R or Bronze. Note:while It can happen (in the case of ties) that multiple gold or silver medals is handed out for the same event, an Athlete cannot gain multiple medals for the same event. [3 marks]

4. Write a SQL query returning all nations (nationalities of athletes) that won at least 2 gold medals in 2018. Don't use the aggregation (GROUP by). Note:each Nation satisfying the criteria should being 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]

For the first 3 to ask, nothing is to build a table and add restrictions

CREATE TABLEolympicevent (NameVARCHAR( -),   Year int, Descriptiontext,  CONSTRAINTPk_olympiceventPRIMARY KEY(Name, Year));CREATE TABLEathlete (NameVARCHAR( -), dateOfBirth date, GenderChar( -)CHECK(Gender='male' ORGender= 'female'), nationalitytext,  CONSTRAINTPk_athletePRIMARY KEY(Name,dateofbirth));CREATE TABLEcompetes (Athlete_nameVARCHAR( -), dateOfBirth date, Olympicevent_nameVARCHAR( -), Olympicevent_yearint, medalCHAR( -)CHECK(Medal= 'Gold' ORMedal= 'Silver' ORMedal= 'Bronze'),  CONSTRAINTFk_atheleteFOREIGN KEY(Athlete_name,dateofbirth)REFERENCESathlete (Name,dateofbirth),CONSTRAINTFk_olympiceventFOREIGN KEY(olympicevent_name,olympicevent_year)REFERENCESOlympicevent (Name, Year));

and add data to it.

Athlete:

Olympicevent table:

Competes table:

It is not difficult to use Group by after the 5th question.

SELECT athlete. Nationality,Count(1as fromWHERE=GROUP by  having Count (1) >= 2;

The results are as follows

The 4th question does not allow group by to ask the teacher and not to use aggregate functions such as Sum count

I wrote the following code

 withMedal_nationality as (    SELECTAthlete_name, Athlete.dateofbirth, Olympicevent_year, Olympicevent_name, nationality fromathlete, competesWHEREAthlete. Name=Competes. Athlete_name andAthlete. dateOfBirth=competes. dateOfBirth)SELECT DISTINCTNationality frommedal_nationality T1WHERE EXISTS(  SELECT *  frommedal_nationality T2WHERET1.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));

Use with as to select Medal_nationality to contain Athlete_name, Athlete.dateofbirth, Olympicevent_year, Olympicevent_name, Nationality 5 fields, and then uses exists to select only the values that return true, provided that the nationality is equal, but two persons cannot be the same person (see question 1), and the final result is:

Do not use GROUP by at the end of the need to filter out the data it is finished _ (: З"∠) _

Summary of SQL Jobs (without group by using the With As,exists implementation classification)