StoredProcedure.sql

-- 1) Select cell lines by Cancer Type
DROP PROCEDURE IF EXISTS SelectCellLinesByCancerType;
DELIMITER $$

CREATE PROCEDURE SelectCellLinesByCancerType (IN c_type VARCHAR(45)) 
BEGIN
    SELECT *
    FROM cell_lines
    WHERE CANCER_TYPE = c_type;
END $$

DELIMITER ;

-- Test the procedure
SET @cancerType = "HNSC"; -- 
CALL SelectCellLinesByCancerType(@cancerType);






-- 2) Get cell line count by Histology
DROP PROCEDURE IF EXISTS GetCellCountByHistology ;
DELIMITER $$

CREATE PROCEDURE GetCellCountByHistology (IN histologyName VARCHAR(45), OUT count INT)
BEGIN
    SELECT COUNT(c.id) INTO count
    FROM cell_lines c
    JOIN histology h ON c.HISTOLOGY_ID = h.HISTOLOGY_ID
    WHERE h.HISTOLOGY = histologyName;
END $$

DELIMITER ;

-- Test the procedure
CALL GetCellCountByHistology("glioma", @line_cnt);
SELECT @line_cnt;




-- 3) Calculate average Z-Score for a specific Drug
DROP PROCEDURE IF EXISTS Calculate_Avg_ZScore;
DELIMITER $$

CREATE PROCEDURE Calculate_Avg_ZScore(IN DrugNameInput VARCHAR(255), INOUT AvgScore FLOAT)
BEGIN
    -- Initialize the parameter to 0
    SET AvgScore = 0;

    -- Calculate the average Z-Score for the drug
    SELECT AVG(ds.Z_SCORE) INTO AvgScore
    FROM drug_sensitivity ds
    JOIN drugs d ON ds.DRUG_ID = d.DRUG_ID
    WHERE d.DRUG_NAME = DrugNameInput;

    -- If no data found, ensure AvgScore remains 0 (or null check)
    IF AvgScore IS NULL THEN
        SET AvgScore = 0;
    END IF;
END$$

DELIMITER ;

-- Test the procedure

CALL Calculate_Avg_ZScore("AGK2", @score_val); -- Example Drug Name
SELECT @score_val AS AverageZScore;