Forum Discussion

fionn3892's avatar
fionn3892
Copper Contributor
Nov 07, 2022

SQL Help

Hi! I am using phpMyAdmin to complete an SQL group project for college and each group member was given a specific task to complete. I have been struggling with my task for days. We were given a music-related database with tables such as albums, songs, song_album etc. My exact task is as follows::: I must create a stored procedure called AddTrack(A, S) where A is an album_id in table albums and S is a song_id in table songs. The procedure should check if A is an album_id existing in table albums and if S is a song_id existing in table songs, and if both conditions are correct, it should add the row (A, S, TN+1) to the table song_album where TN is the highest track_no for album A in table song_album. This is my attempt:

CREATE PROCEDURE AddTrack (A INT, S INT)
MODIFIES SQL DATA
    BEGIN
        DECLARE AChecker INT;
        DECLARE SChecker INT;
        DECLARE TN INT;
        IF EXISTS (SELECT album_id FROM albums WHERE album_id = A)
            THEN SET AChecker = 1 AND 
             SET TN = (SELECT MAX(track_no) FROM song_album WHERE album_id = A);
            ELSE SET AChecker = 0;
        END IF;
        IF EXISTS (SELECT song_id FROM songs WHERE song_id = S)
            THEN SET SChecker = 1;
            ELSE SET SChecker = 0;
        END IF;
        IF AChecker = 1 AND SChecker = 1 THEN
            INSERT INTO song_album VALUES (A, S, TN+1);
        END IF;
    END;

Resources