Requêts SQL a partie d'un problème en anglais
yayator
-
yayator -
yayator -
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 !
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 !
A voir également:
- Requêts SQL a partie d'un problème en anglais
- Creer un groupe whatsapp a partir d'un autre groupe - Guide
- Nbcar en anglais - Guide
- Clavier en anglais - Guide
- Corbeille en anglais - Guide
- Pourquoi waze est en anglais ✓ - Forum Huawei
3 réponses
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
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
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
