1.1 Adding data (INSERT) into any arbitrary table from the cinema database:

The first query adds information about the director to the table ‘rezisoor’.

INSERT INTO rezisoor(eesnimi, perenimi)
VALUES ('Chris', 'Columbus');

The second query adds informarion about the movie to the table ‘film’ (movie title, genre ID, language, movie durarion, director ID, movie type).

INSERT INTO film(filmNimetus, zanrID, keelID, pikkus, rezisorID, filmtypeID)
VALUES ('Harry Potter', 2, 3, 152, 7, 3);

1.2 Deleting records in the žanr table based on the entered ID:

Procedure for deleting a record from the ‘zanr’ table by ID:

The line after the word ‘BEGIN’ and before the word ‘END’ (SELECT * FROM zanr) means that it will display the current and after the updated state of the ‘zanr’ table.

BEGIN
	SELECT * FROM zanr;
    -- Delete the record from the 'zanr' table where the ID matches deleteID.
    DELETE FROM zanr
    WHERE zanrID=deleteID;
    SELECT * from zanr;
END

The image shows an interface for running a procedure ‘deleteFromGenre’ in a database management system. The interface includes a place to input a parameter, which expects a value of type INT. This value is the identifier of a record in the genre table (‘zanr’) that needs to be deleted. The user should input the corresponding ID in the ‘Value’ field, and then click the ‘Go’ button to execute the procedure.

The second image shows an example before and after, indicating that the required record (genre) has been deleted.

1.3 Deleting a table based on the entered name (the table should not contain a foreign key):

A variable ‘fkCount’ is declared to store the number of foreign keys associated with the table.

A query is executed on information_schema.TABLE_CONSTRAINTS to determine if there are any foreign keys associated with the table.

If there are no foreign keys (fkCount = 0), an SQL query is formed and executed to delete the table.

If there are foreign keys, the procedure returns a message stating that the table cannot be deleted due to the presence of foreign keys.

BEGIN
    DECLARE fkCount INT;
    SELECT COUNT(*)
    INTO fkCount
    FROM information_schema.TABLE_CONSTRAINTS
    WHERE TABLE_SCHEMA = DATABASE() AND TABLE_NAME = tableName AND CONSTRAINT_TYPE = 'FOREIGN KEY';
    -- If there are no foreign keys, delete the table
    IF fkCount = 0 THEN
        -- Preparation and execution of the query to delete the table
        SET @s = CONCAT('DROP TABLE ', tableName);
        PREPARE stmt FROM @s;
        EXECUTE stmt;
        DEALLOCATE PREPARE stmt;
        -- Returning a message about successful deletion.
        SELECT CONCAT('Table ', tableName, ' has been dropped.') AS Result;
    ELSE
        -- Returning a message that the table cannot be deleted
        SELECT CONCAT('Table ', tableName, ' was not dropped because it has foreign keys.') AS Result;
    END IF;
END

1.4 Creating a table with a specified name and basic fields:

A SQL query is created to create a new table, which uses the parameter newTableName to determine the table name.

In the new table, two fields are automatically created: id with auto-increment and type INT, which is a primary key, and name with type VARCHAR(255).

The query is prepared and executed.

A message is returned indicating that the table with the specified name has been created.

BEGIN
    -- Preparing SQL query to create a new table with two fields: 'id' and 'name'
    SET @ddl = CONCAT('CREATE TABLE ', newTableName, ' (',
                      'id INT AUTO_INCREMENT PRIMARY KEY, ',
                      'name VARCHAR(255)',
                      ');');
    -- Execution of the prepared SQL query.
    PREPARE stmt FROM @ddl;
    EXECUTE stmt;
    DEALLOCATE PREPARE stmt;
    -- Returning the result of table creation."
    SELECT CONCAT('Table ', newTableName, ' has been created.') AS Result;
END

2. Add 2 procedures: for updating (UPDATE) and altering (ALTER) the režiser table:

The ‘updateRezisoor’ procedure updates records in the ‘rezisoor’ table, changing the director’s first and last name to new provided values based on the director’s identifier.

The image shows the ‘AddColumnToRezisoor’ procedure, which is for adding a new column to the ‘rezisoor’ table. The procedure takes four parameters: columnName, columnType, columnNullable, and columnDefault, which determine the column name, its type, whether it can be NULL, and the default value.

It checks if a column with the specified name columnName already exists in the rezisoor table using a query to INFORMATION_SCHEMA.COLUMNS. If the column does not exist, the procedure continues to form an SQL query to add a new column. This query takes into account parameters such as the column type columnType, whether the column can be NULL (columnNullable), and the default value (columnDefault), if provided. Then the query is prepared (PREPARE stmt FROM @ddl), executed (EXECUTE stmt), and deallocated (DEALLOCATE PREPARE stmt).

If the column already exists, the procedure returns a warning that a column with that name already exists in the table.

3. Add your own procedure with an action different from the previous ones: Create a universal procedure for adding any column to any table.

The addColumnToTable procedure is for adding a new column to any specified table. It takes four parameters:

• tableName: The name of the table to which the column will be added.
• columnName: The name of the new column.
• dataType: The data type of the new column.
• defaultValue: The default value for the new column.

First, it checks if a column with the specified name columnName exists in the specified table tableName in the current database. If the column does not exist, the procedure forms an SQL query to add the column using the provided data type dataType and default value defaultValue. The SQL query is prepared and executed. If a default value defaultValue is provided, it is added to the SQL query.

After executing the query, the prepared SQL statement is deallocated.