requêts SQL a partie d'un problème en anglais Fermé

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. 

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 !

halo_quebec · Answer

Essaye sa pour le #1

SELECT PROJECTID, COUNT(BATCHNO)
FROM REACTION
GROUP BY PROJECT ID;

yayator · Answer

Je suis pas très doué en SQL mais je crois que du coup on prend pas tout les PROJECTID, il y en a dans d'autres tables

yayator · Answer

J'ai essayé ça mais je sais pas si c'est bon. Pouvez vous me donner un avis ?

   1.

      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;


   2.

      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;


   3.

      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;


   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.


SELECT BATCHNO

FROM BIOLOGICAL_DATA D, SAMPLE_DATA S

WHERE B. ANALYTICALID = S. ANALYTICALID

AND S.PURITY > 50

AND S.QUANTITY > 10.


   5.

      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

Requêts SQL a partie d'un problème en anglais

3 réponses

Discussions similaires