With the database in file organization.sql (attribute postal_address added) :
/ For each following question, give the SQL query which computes what is asked. Fill the database with data that allows you to verify that the query is working. Some data must satisfy the request and some not. Justify in the comments the relevance of the test data set.
the last name and the first name of members born in 1999 ;
the name and the phone number of members who live in Créteil ;
for each role, the role description and the number of members with this role ;
for each role, the role description and the date of birth of the youngest members ;
/ the name and the email address of members whose role is moniteur and who are the oldest ;
the activity name and the numbers of members who practise the activity for activities with at least 10 members registered ;
the name, the date of birth and the email of the youngest members who practise judo ;
the names and the referrer names when the tutor is older than the member.
B. Enrich the database by adding attributes or tables.
Explain the choices.
Fill in the new fields with data.
Write 6 SQL queries using the new structure and comment in natural language on the role of the queries.
Put in a zip archive the SQL file which contains the database and its contents as well as the SQL file with the queries for question A and the SQL file with the queries for question B and upload the zip file. Do not forget to put the names of the students in each file.
Comments