SQL a partir d'un sujet en anglais

Question

Bonjour,

J'ai quelque requêtes à faire et je dois dire que j'ai un peu du
mal. (en plus le sujet est en anglais, si quelqu'un pouvait me donner
un petit coup de pouce. Il faut absolument que je réussisse avant minuit....

Database Schema

TABLE PROJECT

     PROJECTID                   CHARACTER(10) (Primary Key)

      PROJECT_NAME       CHARACTER(40)

TABLE COMPOUND

      COMPID                  	     CHARACTER(15) (Primary Key)

      STRUCTURE                 BLOB

TABLE REACTION

      REACTIONID               INTEGER (Primary Key)

      COMPID                           CHARACTER(15)

      BATCHNO                     CHARACTER(35)(Unique Key)

      PROJECTID                  CHARACTER(10)

      LIBRARYNAME          CHARACTER(35)

TABLE BIOLOGICAL_DATA

      COMPID                              CHARACTER(15)

      BATCHNO                          CHARACTER(35)

      TESTID                                 NUMBER

      PROJECTID                      CHARACTER(10)

      NUM_VALUE                   NUMBER

      ANALYTICALID           INTEGER

      REACTIONID         INTEGER

      BIODATAID                     INTEGER (Primary Key)

TABLE SAMPLE_DATA

      ANALYTICALID                  INTEGER (Primary Key)

      REACTIONID                         INTEGER  

      PROJECTID                            CHARACTER(10)

      BARCODE                                CHARACTER(35)

      CONTAINERID                     CHARACTER(35)

      WELL                                             NUMBER

      QUANTITY                               NUMBER

      UNIT                                             CHARACTER(10)

      PURITY                                      NUMBER

Questions:

1.)Write the SQL statement that provides a list of all projects (PROJECTID) and the total number of batches for each.

2.)Write the SQL statement that provides a list of all projects (PROJECTID) and the total number of biological results for each.

3.)Write the SQL statement that provides a list of all the
compounds (COMPID) and the total number of batches and samples for
each.

4.)Write the SQL statement that provides a list of all the batches
(BATCHNO) that has an average purity > 50 for samples with a
quantity > 10.

5.)Write the SQL statement that provides a list of all the batches (BATCHNO) with no biological data.

Merci d'avance !

Yayator · Answer

J'ai essayé ça mais je sais pas si c'est bon. Pouvez vous me donner un avis ?

Write the SQL statement that provides a list of all projects (PROJECTID) and the total number of batches for each.

SELECT PROJECTID, COUNT(BATCHNO) FROM REACTION GROUP BY PROGECTID;

Write the SQL statement that provides a list of all projects (PROJECTID) and the total number of biological results for each.

SELEST PROJECTID, COUNT(BIODATAID) FROM SAMPLE_DATA GROUP BY PRIJECTID;

Write the SQL statement that provides a list of all the compounds (COMPID) and the total number of batches and samples for each.

SELECT COMPID, COUNT(BATCHNO), COUNT(ANALYTICALID) FROM BIOLOGICAL_DATA D, SAMPLE_DATA S WHERE B. ANALYTICALID = S. ANALYTICALID GROUP BY COMPID;

Write the SQL statement that provides a list of all the batches (BATCHNO) that has an average purity > 50 for samples with a quantity > 10.

SELECT BATCHNO FROM BIOLOGICAL_DATA D, SAMPLE_DATA S WHERE B. ANALYTICALID = S. ANALYTICALID AND S.PURITY > 50 AND S.QUANTITY > 10.

Write the SQL statement that provides a list of all the batches (BATCHNO) with no biological data.

SELECT BATCHNO FROM REACTION R, BIOLOGICAL_DATA D WHERE R. BATCHNO <> D. BATCHNO

SQL a partir d'un sujet en anglais

1 réponse

Votre réponse

Discussions similaires