View.sql

-- 1. Cell Line and Tissue Classification
CREATE OR REPLACE VIEW view_cell_line_classifications AS
SELECT 
    cl.CELL_LINE_NAME, 
    cl.SAMPLE_NAME, 
    tcga.TCGA_LABEL, 
    tcga.TCGA_DEFINITION,
    s.SITE
FROM cell_lines cl
LEFT JOIN tcga_tissue_classification tcga ON cl.TCGA_LABEL = tcga.TCGA_LABEL
LEFT JOIN site s ON cl.SITE_ID = s.SITE_ID;

# To call the view:
SELECT * FROM view_cell_line_classifications;

-- 2. Drug Mechanisms and Biological Pathways
CREATE OR REPLACE VIEW view_drug_mechanisms AS
SELECT 
    d.DRUG_NAME, 
    t.TARGET_NAME, 
    p.PATHWAY_NAME
FROM drugs d
JOIN drug_targets dt ON d.DRUG_ID = dt.DRUG_ID
JOIN targets t ON dt.TARGET_ID = t.TARGET_ID
JOIN targets_pathways tp ON t.TARGET_ID = tp.TARGET_ID
JOIN pathways p ON tp.PATHWAY_ID = p.PATHWAY_ID;

# To call the view:
SELECT * FROM view_drug_mechanisms;

-- 3. Distribution of Cell Lines by Anatomical Site
CREATE OR REPLACE VIEW view_site_cell_count AS
SELECT 
    s.SITE, 
    COUNT(cl.id) AS TotalCellLines
FROM site s
JOIN cell_lines cl ON s.SITE_ID = cl.SITE_ID
GROUP BY s.SITE
ORDER BY TotalCellLines DESC;

# To call the view:
SELECT * FROM view_site_cell_count;

-- 4. Drug Sensitivity and Potency Results
CREATE OR REPLACE VIEW view_drug_sensitivity_results AS
SELECT 
    ds.NLME_CURVE_ID,
    cl.CELL_LINE_NAME,
    d.DRUG_NAME,
    ds.LN_IC50,
    ds.Z_SCORE,
    ss.SITE_NAME AS Screening_Site
FROM drug_sensitivity ds
JOIN cell_lines cl ON ds.COSMIC_ID = cl.COSMIC_ID
JOIN drugs d ON ds.DRUG_ID = d.DRUG_ID
LEFT JOIN screening_site ss ON ds.SCREENING_SITE_ID = ss.SCREENING_SITE_ID;

# To call the view with a filter for high sensitivity:
SELECT * FROM view_drug_sensitivity_results 
WHERE Z_SCORE < -2;

-- 5. Genetic Instability (MSI) Status by Cancer Type
CREATE OR REPLACE VIEW view_cancer_msi_summary AS
SELECT 
    cl.CANCER_TYPE,
    msi.MSI_TYPE,
    COUNT(*) AS Total_Samples
FROM cell_lines cl
JOIN microsatellite_instability_data msi ON cl.MSI_ID = msi.MSI_ID
GROUP BY cl.CANCER_TYPE, msi.MSI_TYPE;

# To call the view:
SELECT * FROM view_cancer_msi_summary;