Q1)
DELIMITER //
CREATE TRIGGER changing_no
AFTER INSERT ON purchase
FOR EACH ROW
BEGIN
DECLARE arts_id VARCHAR(6);
DECLARE P_AMOUNT INT;
SELECT art_id INTO arts_id FROM purchase WHERE order_id = NEW.order_id;
SELECT amount INTO P_AMOUNT FROM art_order WHERE order_id = NEW.order_id;
IF P_AMOUNT >0 THEN
UPDATE art SET availability ='No' WHERE art_id = arts_id;
END IF;
END //
[AFTER THAT EXECUTE THIS]
INSERT INTO art (art_id, art_description, art_type,
artist_id,availability,gallery_id,art_tittle,art_price,likes,created_date,age)
VALUES ('ART016', 'Abstract sculpture', 'sculpture', 'A001', 'Yes','G002','Ancient
Ruins',850,20,'2022-02-18',1);
INSERT INTO art_order (order_id, amount,order_description,order_time) VALUES
('ORD011', 850,'Order for sculpture','2023-09-10 10:00:00');
INSERT INTO purchase (order_id, cust_id, art_id) VALUES ('ORD011', 'C002',
'ART016');
[AFTER THAT EXECUTE THIS]
SELECT availability FROM art WHERE art_id = 'ART016';
-----------------------------------------------------------------------------------
-----
Q2)
DELIMITER //
CREATE TRIGGER giving_rate
AFTER INSERT ON exhibited
FOR EACH ROW
BEGIN
DECLARE t_likes INT;
DECLARE t_arts INT;
DECLARE artistID varchar(4);
DECLARE rating INT;
SELECT artist_id INTO artistID from art WHERE art_id = NEW.art_id;
SELECT SUM(likes) INTO t_likes FROM art WHERE artist_id = artistID;
SELECT COUNT(*) INTO t_arts FROM art WHERE artist_id = artistID;
IF t_arts > 0 THEN
SET rating = (t_likes / t_arts);
ELSE
SET rating = 0;
END IF;
UPDATE artist SET ratings = rating WHERE artist_id = artistID;
END //
DELIMITER ;
[AFTER THAT EXECUTE THIS]
INSERT INTO exhibited (ex_id, art_id) VALUES ('EX010', 'ART016');
[AFTER THAT EXECUTE THIS]
select * from artist;
-----------------------------------------------------------------------------------
----------
Q3)
DELIMITER //
CREATE PROCEDURE RankArtistsBasedOnLikesWithCursors()
BEGIN
DECLARE done INT DEFAULT 0;
DECLARE artistId, contactNo VARCHAR(10);
DECLARE fName, lName, loc VARCHAR(50);
DECLARE uName, pass VARCHAR(20);
DECLARE rating INT;
DECLARE gen ENUM('M', 'F', 'O');
DECLARE totalLikes INT DEFAULT 0;
DECLARE cur CURSOR FOR SELECT * FROM artist;
DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = 1;
DROP TEMPORARY TABLE IF EXISTS TempRanking;
CREATE TEMPORARY TABLE TempRanking (
artist_id VARCHAR(10),
artist_rank INT
);
OPEN cur;
label: LOOP
FETCH cur INTO artistId, fName, lName, contactNo, rating, uName, pass, loc,
gen;
SELECT SUM(likes) INTO totalLikes FROM art WHERE artist_id = artistId;
INSERT INTO TempRanking VALUES (artistId, totalLikes);
IF done = 1 THEN
LEAVE label;
END IF;
END LOOP;
CLOSE cur;
SELECT * FROM TempRanking ORDER BY artist_rank DESC;
END //
[AFTER THAT EXECUTE THIS]
call RankArtistsBasedOnLikesWithCursors();
-----------------------------------------------------------------------------------
--------
Q4)
[Load the new database and its tables given in the question given]
[politics database]
WITH RECURSIVE Subordinates AS (
SELECT p.PoliticianID, p.Name, p.Position
FROM Politicians p
WHERE p.Name = 'Politician A' AND p.Position = 'Senator'
UNION ALL
SELECT p2.PoliticianID, p2.Name, p2.Position
FROM Politicians p2
JOIN PoliticalRelationships pr ON p2.PoliticianID = pr.ChildPoliticianID
JOIN Subordinates s ON pr.ParentPoliticianID = s.PoliticianID
)
SELECT PoliticianID, Name, Position FROM Subordinates;
-----------------------------------------------------------------------------------
-----------
[MAKE SURE YOU ARE USING THE DATABSE (ART GALLERY) ]
Q5)
CREATE USER Jack;
GRANT SELECT ON art TO Jack;
COMMIT;
[After that execute this]
SHOW GRANTS FOR Jack;
Q6)
CREATE USER Levi;
GRANT SELECT ON art TO Levi;
GRANT INSERT, SELECT, UPDATE, DELETE ON purchase TO Levi;
COMMIT;
[After that execute this]
SHOW GRANTS FOR Levi;
Q7)
CREATE USER Smith;
GRANT SELECT, INSERT, UPDATE, DELETE ON exhibition TO Smith;
GRANT SELECT, INSERT, UPDATE, DELETE ON exhibited TO Smith;
GRANT SELECT, INSERT, UPDATE, DELETE ON art TO Smith;
COMMIT;
[After that execute this]
SHOW GRANTS FOR Smith;
Q8)
CREATE USER 'A012'@'localhost' IDENTIFIED BY 'password';
GRANT SELECT, INSERT, UPDATE, DELETE ON november.art TO 'A012'@'localhost';
[After that execute this]
SHOW GRANTS FOR 'A012'@'localhost';
[After that execute this]
REVOKE ALL PRIVILEGES ON november.* FROM 'A012'@'localhost';
Q9)
CREATE USER 'guest_user'@'localhost' IDENTIFIED BY 'password';
GRANT ALL PRIVILEGES ON (your database name).* TO 'guest_user'@'localhost';
[After that execute this]
SHOW GRANTS FOR 'guest_user'@'localhost';
[Now run the below query]
REVOKE ALL PRIVILEGES ON (your database name).* FROM 'guest_user'@'localhost';
[After that execute this]
SHOW GRANTS FOR 'guest_user'@'localhost';
