queries.sql

-- Empty patient queries
    
    SELECT *
    FROM `isb-cgc-bq.TARGET.clinical_gdc_current`
    WHERE submitter_id IN (
    SELECT DISTINCT case_barcode
    FROM `isb-cgc-bq.TARGET.biospecimen_gdc_current`
    WHERE project_short_name IN ('TARGET-ALL-P1', 'TARGET-ALL-P2')
    AND (sample_type IN ('01', '02', '06'))
)
    ORDER BY submitter_id

                SELECT *
    FROM `isb-cgc-bq.TARGET.clinical_gdc_current`
    WHERE submitter_id IN (
    SELECT DISTINCT case_barcode
    FROM `isb-cgc-bq.TARGET.biospecimen_gdc_current`
    WHERE project_short_name IN ('TARGET-ALL-P3')
    AND (sample_type IN ('01', '02', '06'))
)
    ORDER BY submitter_id

                SELECT *
    FROM `isb-cgc-bq.TARGET.clinical_gdc_current`
    WHERE submitter_id IN (
    SELECT DISTINCT case_barcode
    FROM `isb-cgc-bq.TARGET.biospecimen_gdc_current`
    WHERE project_short_name IN ('TARGET-AML')
    AND (sample_type IN ('01', '02', '06'))
)
    ORDER BY submitter_id
    
-- Empty mutation queries

    SELECT Hugo_Symbol, Entrez_Gene_Id, Center, NCBI_Build, Chromosome, Start_Position, End_Position, Strand, Variant_Classification, Variant_Type, Reference_Allele, Tumor_Seq_Allele1, Tumor_Seq_Allele2, dbSNP_RS, dbSNP_Val_Status, sample_barcode_tumor, sample_barcode_normal, Match_Norm_Seq_Allele1, Match_Norm_Seq_Allele2, Tumor_Validation_Allele1, Tumor_Validation_Allele2, Match_Norm_Validation_Allele1, Match_Norm_Validation_Allele2, Verification_Status, Validation_Status, Mutation_Status, Sequencing_Phase, Sequence_Source, Validation_Method, Score, BAM_File, Sequencer, HGVSp_Short, t_ref_count, t_alt_count, n_alt_count, n_ref_count
    FROM `isb-cgc-bq.TARGET.masked_somatic_mutation_hg38_gdc_current`
    WHERE sample_barcode_tumor IN (
    SELECT sample_barcode 
    FROM `isb-cgc-bq.TARGET.biospecimen_gdc_current`
    WHERE project_short_name IN ('TARGET-CCSK')
    AND (sample_type IN ('01', '02', '06'))
)
    ORDER BY sample_barcode_tumor, Chromosome, Start_Position, End_Position
    
        SELECT Hugo_Symbol, Entrez_Gene_Id, Center, NCBI_Build, Chromosome, Start_Position, End_Position, Strand, Variant_Classification, Variant_Type, Reference_Allele, Tumor_Seq_Allele1, Tumor_Seq_Allele2, dbSNP_RS, dbSNP_Val_Status, sample_barcode_tumor, sample_barcode_normal, Match_Norm_Seq_Allele1, Match_Norm_Seq_Allele2, Tumor_Validation_Allele1, Tumor_Validation_Allele2, Match_Norm_Validation_Allele1, Match_Norm_Validation_Allele2, Verification_Status, Validation_Status, Mutation_Status, Sequencing_Phase, Sequence_Source, Validation_Method, Score, BAM_File, Sequencer, HGVSp_Short, t_ref_count, t_alt_count, n_alt_count, n_ref_count
    FROM `isb-cgc-bq.TARGET.masked_somatic_mutation_hg38_gdc_current`
    WHERE sample_barcode_tumor IN (
    SELECT sample_barcode 
    FROM `isb-cgc-bq.TARGET.biospecimen_gdc_current`
    WHERE project_short_name IN ('TARGET-OS')
    AND (sample_type IN ('01', '02', '06'))
)
    ORDER BY sample_barcode_tumor, Chromosome, Start_Position, End_Position
    
        SELECT Hugo_Symbol, Entrez_Gene_Id, Center, NCBI_Build, Chromosome, Start_Position, End_Position, Strand, Variant_Classification, Variant_Type, Reference_Allele, Tumor_Seq_Allele1, Tumor_Seq_Allele2, dbSNP_RS, dbSNP_Val_Status, sample_barcode_tumor, sample_barcode_normal, Match_Norm_Seq_Allele1, Match_Norm_Seq_Allele2, Tumor_Validation_Allele1, Tumor_Validation_Allele2, Match_Norm_Validation_Allele1, Match_Norm_Validation_Allele2, Verification_Status, Validation_Status, Mutation_Status, Sequencing_Phase, Sequence_Source, Validation_Method, Score, BAM_File, Sequencer, HGVSp_Short, t_ref_count, t_alt_count, n_alt_count, n_ref_count
    FROM `isb-cgc-bq.TARGET.masked_somatic_mutation_hg38_gdc_current`
    WHERE sample_barcode_tumor IN (
    SELECT sample_barcode 
    FROM `isb-cgc-bq.TARGET.biospecimen_gdc_current`
    WHERE project_short_name IN ('TARGET-RT')
    AND (sample_type IN ('01', '02', '06'))
)
    ORDER BY sample_barcode_tumor, Chromosome, Start_Position, End_Position